Exercice : charger des données dans Azure SQL Database

10 minutes

Lorsque vous chargez des données en masse, elles doivent provenir d’un emplacement. Dans Azure, il est courant de stocker ou de vider des données du stockage Blob Azure. Le stockage Blob est optimisé pour le stockage de grandes quantités de données non structurées à un coût relativement faible.

Dans ce scénario, Adventure Works Cycles reçoit les données retournées par les magasins en fonction du numéro d’identification du magasin. Ces données sont stockées dans des fichiers .dat qui sont ensuite envoyés dans le stockage Blob Azure. Une fois les données dans Stockage Blob, Azure SQL a besoin d’un moyen d’y accéder. Pour cela, vous pouvez créer une source de données externe qui a accès au compte de stockage. Vous pouvez contrôler l’accès à ce compte de stockage via Microsoft Entra ID, une autorisation de clé partagée ou une signature d’accès partagé (SAS).

Dans cet exercice, vous explorez un scénario de chargement en bloc de données à partir du Stockage Blob Azure dans Azure SQL Database. L’approche utilise des signatures d’accès partagé et T-SQL.

Il existe deux options pour effectuer cet exercice :

sqlcmd dans Azure Cloud Shell
Notebooks SQL dans Azure Data Studio

Les deux exercices contiennent les mêmes commandes et le même contenu : vous pouvez ainsi choisir l’option que vous préférez.

Option 1 : `sqlcmd` dans Azure Cloud Shell

sqlcmd est un outil en ligne de commande qui vous permet d’interagir avec SQL Server et Azure SQL en utilisant la ligne de commande. Dans cet exercice, vous utilisez sqlcmd dans l’instance PowerShell d’Azure Cloud Shell. sqlcmd est installé par défaut : il est donc facile à utiliser à partir d’Azure Cloud Shell.

En raison de la façon dont Azure Cloud Shell pour Bash est configuré, vous devez d’abord changer le mode du terminal en exécutant la commande suivante dans Azure Cloud Shell.
```
TERM=dumb
```
Exécutez la commande suivante dans le terminal intégré après avoir modifié le nom et le mot de passe de votre serveur.
```
sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
```

Créez une table et un schéma pour y charger les données. Ce processus est du code T-SQL simple. Maintenant que vous êtes connecté à votre base de données, exécutez le script suivant dans le terminal :

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

Conseil

Une entrée s’affiche après les instructions T-SQL. Cela représente chaque ligne de l’entrée T-SQL. Par exemple, la commande précédente se termine par 26. Assurez-vous de sélectionner Entrée après ces lignes.

Vous savez que la commande est terminée quand vous voyez à nouveau 1>. Ceci indique que sqlcmd est prêt pour la première ligne de votre entrée T-SQL suivante.

Ensuite, créez une clé principale :

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

Une clé principale est nécessaire pour créer une valeur DATABASE SCOPED CREDENTIAL, car le stockage Blob n’est pas configuré pour autoriser l’accès public (anonyme). Les informations d’identification font référence au compte de stockage d’objets Blob. La partie données spécifie le conteneur pour les données retournées par le magasin.

Utilisez une signature d’accès partagé comme une identité qu’Azure SQL sait interpréter. Le secret est le jeton SAS que vous pouvez générer à partir du compte de stockage Blob. Dans cet exemple, le jeton SAS pour un compte de stockage auquel vous n’avez pas accès est fourni pour vous permettre d’accéder seulement aux données retournées par les magasins.
```
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
```

Créez une source de données externe pour le conteneur :

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

Insérez en bloc l’un des fichiers de retour du magasin. Exécutez le script suivant et, pendant qu’il s’exécute, passez en revue les commentaires :

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

Vérifiez le nombre de lignes qui ont été insérées dans la table :
```
SELECT COUNT(*) FROM DataLoad.store_returns;
GO
```
Si tout s’est exécuté correctement, vous voyez normalement que 2807797 a été retourné.

Ce code est un exemple simple de la façon d’insérer des données depuis le stockage Blob dans Azure SQL Database. Si vous voulez réexécuter l’exercice, exécutez le code suivant pour réinitialiser ce que vous avez fait :

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

Option 2 : Notebooks SQL dans Azure Data Studio

Pour cette activité, vous utilisez le notebook appelé LoadData.ipynb. Vous pouvez le trouver dans \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata sur votre appareil. Ouvrez ce fichier dans Azure Data Studio pour effectuer cet exercice, puis revenez ici.

Si vous ne parvenez pas à effectuer l’exercice pour une raison quelconque, vous pouvez passer en revue les résultats dans le fichier du notebook correspondant sur GitHub.

Continuer

Option 1 : sqlcmd dans Azure Cloud Shell

Option 2 : Notebooks SQL dans Azure Data Studio

Commentaires

Option 1 : `sqlcmd` dans Azure Cloud Shell