Indietro

Prossima

Esercizio - Caricare dati nel database SQL di Azure

Completato

Quando si esegue il caricamento in blocco di dati, esso deve avere una posizione di origine. In Azure è prassi comune archiviare o eseguire il dump dei dati in Archiviazione BLOB di Azure. L'archiviazione BLOB è ottimizzata per l'archiviazione di grandi quantità di dati non strutturati a un costo relativamente basso.

In questo scenario, Adventure Works Cycles riceve i dati restituiti dall'archivio in base al numero di identificazione dell'archivio. Questi dati vengono archiviati in file DAT, di cui viene quindi eseguito il push nell'archivio BLOB di Azure. Quando i dati si trovano nell'archivio BLOB, SQL di Azure deve potervi accedere. A tale scopo, è possibile creare un'origine dati esterna che abbia accesso all'account di archiviazione. È possibile controllare l'accesso all'account di archiviazione tramite Microsoft Entra ID, l'autorizzazione della chiave condivisa o una firma di accesso condiviso.

In questo esercizio si esplorerà uno scenario per il caricamento in blocco dei dati dall'archivio BLOB di Azure nel database SQL di Azure. L'approccio usa T-SQL e firme di accesso condiviso.

Per completare l'esercizio sono disponibili due opzioni:

• sqlcmd in Azure Cloud Shell
• Notebook di SQL in Azure Data Studio

Entrambi gli esercizi includono gli stessi comandi e lo stesso contenuto, quindi è possibile scegliere l'opzione preferita.

Opzione 1: sqlcmd in Azure Cloud Shell

sqlcmd è uno strumento da riga di comando che consente di interagire con SQL Server e SQL di Azure usando la riga di comando. In questo esercizio si userà sqlcmd nell'istanza di PowerShell di Azure Cloud Shell. sqlcmd viene installato per impostazione predefinita, quindi è facile usarlo da Azure Cloud Shell.

1. A causa del modo in cui è configurato Azure Cloud Shell per Bash, prima di tutto è necessario modificare la modalità terminale eseguendo il comando seguente in Azure Cloud Shell.

   TERM=dumb
   

2. Eseguire il comando seguente nel terminale integrato dopo aver modificato il nome del server e la password.

   sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
   

3. Creare una tabella e uno schema in cui caricare i dati. Questo processo è semplice T-SQL. Eseguire lo script seguente nel terminale, ora che si è connessi al database:

   IF SCHEMA_ID('DataLoad') IS NULL
   EXEC ('CREATE SCHEMA DataLoad')
   CREATE TABLE DataLoad.store_returns
   (
       sr_returned_date_sk             bigint,
       sr_return_time_sk               bigint,
       sr_item_sk                      bigint,
       sr_customer_sk                  bigint,
       sr_cdemo_sk                     bigint,
       sr_hdemo_sk                     bigint,
       sr_addr_sk                      bigint,
       sr_store_sk                     bigint,
       sr_reason_sk                    bigint,
       sr_ticket_number                bigint,
       sr_return_quantity              integer,
       sr_return_amt                   float,
       sr_return_tax                   float,
       sr_return_amt_inc_tax           float,
       sr_fee                          float,
       sr_return_ship_cost             float,
       sr_refunded_cash                float,
       sr_reversed_charge              float,
       sr_store_credit                 float,
       sr_net_loss                     float
   );
   GO
   

   Suggerimento

   Dopo le istruzioni T-SQL verrà visualizzata una voce numerica. Rappresenta ogni riga della voce T-SQL. Il comando precedente, ad esempio, terminerà con 26. Assicurarsi di selezionare INVIO dopo queste righe.

   Si saprà che il comando è stato completato quando viene visualizzato di nuovo 1>, che indica che sqlcmd è pronto per la prima riga della voce T-SQL successiva.

4. Creare quindi una chiave master:

   CREATE MASTER KEY 
   ENCRYPTION BY PASSWORD='MyComplexPassword00!';
   GO
   

5. Una chiave master è necessaria per creare un valore DATABASE SCOPED CREDENTIAL, perché l'archivio BLOB non è configurata per consentire l'accesso pubblico (anonimo). Le credenziali fanno riferimento all'account di archiviazione BLOB. La parte dei dati specifica il contenitore per i dati restituiti dall'archivio.

   Si usa una firma di accesso condiviso come identità che Azure SQL sa interpretare. Il segreto è il token di firma di accesso condiviso che è possibile generare dall'account di archiviazione BLOB. In questo esempio, viene fornito il token di firma di accesso condiviso per un account di archiviazione a cui non si ha accesso e quindi è possibile accedere solo ai dati restituiti dall'archivio.

   CREATE DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/]
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
   SECRET = 'st=2020-09-28T22%3A05%3A27Z&se=2030-09-29T22%3A05%3A00Z&sp=rl&sv=2018-03-28&sr=c&sig=52WbuSIJCWyjS6IW6W0ILfIpqh4wLMXmOlifPyOetZI%3D';
   GO
   

6. Creare un'origine dati esterna per il contenitore:

   CREATE EXTERNAL DATA SOURCE dataset
   WITH
   (
       TYPE = BLOB_STORAGE,
       LOCATION = 'https://azuresqlworkshopsa.blob.core.windows.net/data',
       CREDENTIAL = [https://azuresqlworkshopsa.blob.core.windows.net/data/]
   );
   GO
   

7. Inserire in blocco uno dei file restituiti dall'archivio. Eseguire lo script seguente e, durante il completamento, esaminare i commenti:

   SET NOCOUNT ON -- Reduce network traffic by stopping the message that shows the number of rows affected
   BULK INSERT DataLoad.store_returns -- Table you created in step 3
   FROM 'dataset/store_returns/store_returns_1.dat' -- Within the container, the location of the file
   WITH (
   DATA_SOURCE = 'dataset' -- Using the external data source from step 6
   ,DATAFILETYPE = 'char'
   ,FIELDTERMINATOR = '\|'
   ,ROWTERMINATOR = '\|\n'
   ,BATCHSIZE=100000 -- Reduce network traffic by inserting in batches
   , TABLOCK -- Minimize number of log records for the insert operation
   );
   GO
   

8. Controllare il numero di righe inserite nella tabella:

   SELECT COUNT(*) FROM DataLoad.store_returns;
   GO
   

   Se tutto è stato eseguito correttamente, dovrebbe essere restituito 2807797.

Il codice rappresenta un semplice esempio di come inserire dati da un archivio BLOB nel database SQL di Azure. Per ripetere l'esercizio, eseguire il codice seguente per reimpostare le operazioni eseguite:

DROP EXTERNAL DATA SOURCE dataset;
DROP DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/];
DROP TABLE DataLoad.store_returns;
DROP MASTER KEY;
GO

Opzione 2: Notebook di SQL in Azure Data Studio

Per questa attività verrà usato il notebook denominato LoadData.ipynb. È possibile trovarlo in \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata nel dispositivo in uso. Aprire il file in Azure Data Studio per completare questo esercizio, quindi tornare qui.

Se non è possibile completare l'esercizio per qualsiasi motivo, è possibile esaminare i risultati nel file del notebook corrispondente in GitHub.

Continua