Transformer des fichiers avec un pool SQL serverless

Les analystes de données utilisent souvent SQL pour interroger des données à des fins d’analyse et de génération de rapports. Les ingénieurs de données peuvent également utiliser SQL pour manipuler et transformer des données, souvent dans le cadre d’un pipeline d’ingestion de données ou d’un processus d’extraction, transformation et chargement (ETL).

Dans cet exercice, vous allez utiliser un pool SQL serverless dans Azure Synapse Analytics pour transformer des données dans des fichiers.

Cet exercice devrait prendre environ 30 minutes.

Avant de commencer

Vous avez besoin d’un abonnement Azure dans lequel vous avez un accès administratif.

Provisionner un espace de travail Azure Synapse Analytics

Vous aurez besoin d’un espace de travail Azure Synapse Analytics avec accès à Data Lake Storage. Vous pouvez utiliser le pool SQL serverless intégré pour interroger des fichiers dans le lac de données.

Dans cet exercice, vous allez utiliser la combinaison d’un script PowerShell et d’un modèle ARM pour approvisionner un espace de travail Azure Synapse Analytics.

Connectez-vous au portail Azure à l’adresse https://portal.azure.com.
Utilisez le bouton [>_] à droite de la barre de recherche, en haut de la page, pour créer un environnement Cloud Shell dans le portail Azure, puis sélectionnez un environnement PowerShell et créez le stockage si vous y êtes invité. Cloud Shell fournit une interface de ligne de commande dans un volet situé en bas du portail Azure, comme illustré ici :

Remarque : si vous avez créé un shell cloud qui utilise un environnement Bash, utilisez le menu déroulant en haut à gauche du volet Cloud Shell pour le remplacer par PowerShell.
Notez que vous pouvez redimensionner le volet Cloud Shell en faisant glisser la barre de séparation en haut du volet. Vous pouvez aussi utiliser les icônes — , ◻ et X situées en haut à droite du volet pour réduire, agrandir et fermer le volet. Pour plus d’informations sur l’utilisation d’Azure Cloud Shell, consultez la documentation Azure Cloud Shell.

Dans le volet PowerShell, entrez les commandes suivantes pour cloner ce référentiel :

 rm -r dp-203 -f
 git clone https://github.com/MicrosoftLearning/dp-203-azure-data-engineer dp-203

Une fois que le référentiel a été cloné, entrez les commandes suivantes pour accéder au dossier de cet exercice et exécutez le script setup.ps1 qu’il contient :
```
 cd dp-203/Allfiles/labs/03
 ./setup.ps1
```
Si vous y êtes invité, choisissez l’abonnement à utiliser (uniquement si vous avez accès à plusieurs abonnements Azure).
Quand vous y êtes invité, entrez un mot de passe approprié à définir pour votre pool Azure Synapse SQL.

Remarque : veillez à mémoriser ce mot de passe.
Attendez que le script se termine. Cela prend généralement environ 10 minutes, mais dans certains cas, cela peut prendre plus de temps. Pendant que vous attendez, consultez l’article CETAS avec Synapse SQL dans la documentation Azure Synapse Analytics.

Interroger des données dans des fichiers

Le script configure un espace de travail Azure Synapse Analytics et un compte Stockage Azure pour héberger le lac de données, puis charge certains fichiers de données dans le lac de données.

Afficher les fichiers dans le lac de données

Une fois le script terminé, dans le portail Azure, accédez au groupe de ressources dp203-xxxxxxx qu’il a créé, puis sélectionnez votre espace de travail Synapse.
Dans la page Vue d’ensemble de votre espace de travail Synapse, dans la carte Ouvrir Synapse Studio, sélectionnez Ouvrir pour ouvrir Synapse Studio dans un nouvel onglet de navigateur. Connectez-vous si vous y êtes invité.
Sur le côté gauche de Synapse Studio, utilisez l’icône ›› pour développer le menu. Cela permet d’afficher les différentes pages de Synapse Studio qui vous permettront de gérer les ressources et d’effectuer des tâches d’analytique de données.
Dans la page Données, affichez l’onglet Lié et vérifiez que votre espace de travail inclut un lien vers votre compte de stockage Azure Data Lake Storage Gen2, qui doit avoir un nom similaire à synapsexxxxxxx (Primary - datalakexxxxxxx).
Développez votre compte de stockage et vérifiez qu’il contient un conteneur de système de fichiers nommé files.
Sélectionnez le conteneur files, et constatez qu’il contient un dossier nommé sales. Ce dossier contient les fichiers de données que vous allez interroger.
Ouvrez le dossier sales et le dossier csv qu’il contient, puis observez que ce dossier contient des fichiers .csv correspondant à trois années de données de vente.
Cliquez avec le bouton droit sur l’un des fichiers et sélectionnez Aperçu pour afficher les données qu’il contient. Notez que les fichiers contiennent une ligne d’en-tête.
Fermez l’aperçu, puis utilisez le bouton ↑ pour revenir au dossier sales.

Utiliser SQL pour interroger des fichiers CSV

Sélectionnez le dossier csv, puis, dans la liste Nouveau script SQL de la barre d’outils, cliquez sur Sélectionner les 100 premières lignes.
Dans la liste Type de fichier, sélectionnez Format texte, puis appliquez les paramètres pour ouvrir un nouveau script SQL qui interroge les données du dossier.
Dans le volet Propriétés du Script SQL 1 créé, remplacez le nom par Interroger les fichiers CSV de ventes et modifiez les paramètres de résultat pour afficher Toutes les lignes. Ensuite, dans la barre d’outils, sélectionnez Publier pour enregistrer le script et utilisez le bouton Propriétés (qui ressemble à _*) à droite de la barre d’outils pour masquer le volet Propriétés.

Passez en revue le code SQL qui a été généré, qui doit ressembler à ceci :

 -- This is auto-generated code
 SELECT
     TOP 100 *
 FROM
     OPENROWSET(
         BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/csv/**',
         FORMAT = 'CSV',
         PARSER_VERSION='2.0'
     ) AS [result]

Ce code utilise OPENROWSET pour lire les données des fichiers CSV dans le dossier sales et récupère les 100 premières lignes de données.

Dans ce cas, les fichiers de données incluent les noms de colonnes dans la première ligne. Modifiez donc la requête pour ajouter un paramètre HEADER_ROW = TRUE à la clause OPENROWSET, comme illustré ici (n’oubliez pas d’ajouter une virgule après le paramètre précédent) :

 SELECT
     TOP 100 *
 FROM
     OPENROWSET(
         BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/csv/**',
         FORMAT = 'CSV',
         PARSER_VERSION='2.0',
         HEADER_ROW = TRUE
     ) AS [result]

Dans la liste Se connecter à, vérifiez que l’option Intégré est cochée : cela représente le pool SQL intégré qui a été créé avec votre espace de travail. Ensuite, dans la barre d’outils, utilisez le bouton ▷ Exécuter pour exécuter le code SQL, puis passez en revue les résultats, qui doivent ressembler à ceci :

SalesOrderNumber	SalesOrderLineNumber	OrderDate	CustomerName	EmailAddress	Élément	Quantité	UnitPrice	TaxAmount
SO43701	1	2019-07-01	Christy Zhu	christy12@adventure-works.com	Mountain-100 Silver, 44	1	3399.99	271,9992
…	…	…	…	…	…	…	…	…

Publiez les modifications apportées à votre script, puis fermez le volet de script.

Transformer les données à l’aide d’instructions CREATE EXTERNAL TABLE AS SELECT (CETAS)

Un moyen simple d’utiliser SQL pour transformer des données dans un fichier et conserver les résultats dans un autre fichier consiste à utiliser une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS). Cette instruction crée une table basée sur les demandes d’une requête, mais les données de la table sont stockées en tant que fichiers dans un lac de données. Les données transformées peuvent ensuite être interrogées via la table externe ou accessibles directement dans le système de fichiers (par exemple, pour inclusion dans un processus en aval pour charger les données transformées dans un entrepôt de données).

Créer une source de données et un format de fichier externes

En définissant une source de données externe dans une base de données, vous pouvez l’utiliser pour référencer l’emplacement du lac de données auquel vous souhaitez stocker des fichiers pour des tables externes. Un format de fichier externe vous permet de définir le format de ces fichiers (Parquet ou CSV, par exemple). Pour utiliser ces objets pour travailler avec des tables externes, vous devez les créer dans une base de données autre que la base de données master par défaut.

Dans Synapse Studio, dans la page Développer, dans le menu +, sélectionnez Script SQL.

Dans le volet du nouveau script, ajoutez le code suivant (en remplaçant datalakexxxxxxx par le nom de votre compte Data Lake Storage) pour créer une base de données et y ajouter une source de données externe.

 -- Database for sales data
 CREATE DATABASE Sales
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;
    
 Use Sales;
 GO;
    
 -- External data is in the Files container in the data lake
 CREATE EXTERNAL DATA SOURCE sales_data WITH (
     LOCATION = 'https://datalakexxxxxxx.dfs.core.windows.net/files/'
 );
 GO;
    
 -- Format for table files
 CREATE EXTERNAL FILE FORMAT ParquetFormat
     WITH (
             FORMAT_TYPE = PARQUET,
             DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
         );
 GO;

Modifiez les propriétés du script pour remplacer son nom par Create Sales DB et publiez-le.
Vérifiez que le script est connecté au pool SQL Built-in et à la base de données master, puis exécutez-le.
Revenez à la page Données et utilisez le bouton ↻ en haut à droite de Synapse Studio pour actualiser la page. Affichez ensuite l’onglet Espace de travail dans le volet Données, qui affiche à présent une liste de bases de données SQL. Développez cette liste pour vérifier que la base de données Sales a été créée.
Développez la base de données Sales, son dossier External Resources et le dossier External data sources sous celui-ci pour afficher la source de données externe sales_data que vous avez créée.

Créer une table externe

Dans Synapse Studio, dans la page Développer, dans le menu +, sélectionnez Script SQL.

Dans le volet du nouveau script, ajoutez le code suivant pour récupérer et agréger des données à partir des fichiers de ventes CSV à l’aide de la source de données externe. Notez que le chemin BULK est relatif à l’emplacement de dossier auquel la source de données est définie :

 USE Sales;
 GO;
    
 SELECT Item AS Product,
        SUM(Quantity) AS ItemsSold,
        ROUND(SUM(UnitPrice) - SUM(TaxAmount), 2) AS NetRevenue
 FROM
     OPENROWSET(
         BULK 'sales/csv/*.csv',
         DATA_SOURCE = 'sales_data',
         FORMAT = 'CSV',
         PARSER_VERSION = '2.0',
         HEADER_ROW = TRUE
     ) AS orders
 GROUP BY Item;

Exécutez le script. Les résultats doivent ressembler à ceci :

Produit	ItemsSold	NetRevenue
AWC Logo Cap	1063	8791.86
…	…	…

Modifiez le code SQL pour enregistrer les résultats de la requête dans une table externe, comme suit :

 CREATE EXTERNAL TABLE ProductSalesTotals
     WITH (
         LOCATION = 'sales/productsales/',
         DATA_SOURCE = sales_data,
         FILE_FORMAT = ParquetFormat
     )
 AS
 SELECT Item AS Product,
     SUM(Quantity) AS ItemsSold,
     ROUND(SUM(UnitPrice) - SUM(TaxAmount), 2) AS NetRevenue
 FROM
     OPENROWSET(
         BULK 'sales/csv/*.csv',
         DATA_SOURCE = 'sales_data',
         FORMAT = 'CSV',
         PARSER_VERSION = '2.0',
         HEADER_ROW = TRUE
     ) AS orders
 GROUP BY Item;

Exécutez le script. Cette fois, il n’y a pas de sortie, mais le code doit avoir créé une table externe en fonction des résultats de la requête.
Nommez le script Créer une table ProductSalesTotals et publiez-le.
Dans la page Données, sous l’onglet Espace de travail, affichez le contenu du dossier External tables de la base de données SQL Sales pour vérifier qu’une table nommée ProductSalesTotals a été créée.
Dans le menu … de la table ProductSalesTotals, sélectionnez Nouveau script SQL > Sélectionner les 100 premières lignes. Exécutez ensuite le script résultant et vérifiez qu’il retourne les données de ventes de produits agrégées.
Sous l’onglet Fichiers contenant le système de fichiers de votre lac de données, affichez le contenu du dossier Sales (actualisez la vue si nécessaire) et vérifiez qu’un dossier productsales a été créé.
Dans le dossier productsales, observez qu’un ou plusieurs fichiers portant des noms similaires à ABC123DE—-.parquet ont été créés. Ces fichiers contiennent les données de ventes de produits agrégées. Pour le confirmer, vous pouvez sélectionner l’un des fichiers et utiliser le menu Nouveau script SQL > Sélectionner les 100 premières lignes pour l’interroger directement.

Encapsuler une transformation de données dans une procédure stockée

Si vous devez fréquemment transformer des données, vous pouvez utiliser une procédure stockée pour encapsuler une instruction CETAS.

Dans Synapse Studio, dans la page Développer, dans le menu +, sélectionnez Script SQL.

Dans le volet du nouveau script, ajoutez le code suivant pour créer une procédure stockée dans la base de données Sales qui agrège les ventes par année et enregistre les résultats dans une table externe :

 USE Sales;
 GO;
 CREATE PROCEDURE sp_GetYearlySales
 AS
 BEGIN
     -- drop existing table
     IF EXISTS (
             SELECT * FROM sys.external_tables
             WHERE name = 'YearlySalesTotals'
         )
         DROP EXTERNAL TABLE YearlySalesTotals
     -- create external table
     CREATE EXTERNAL TABLE YearlySalesTotals
     WITH (
             LOCATION = 'sales/yearlysales/',
             DATA_SOURCE = sales_data,
             FILE_FORMAT = ParquetFormat
         )
     AS
     SELECT YEAR(OrderDate) AS CalendarYear,
             SUM(Quantity) AS ItemsSold,
             ROUND(SUM(UnitPrice) - SUM(TaxAmount), 2) AS NetRevenue
     FROM
         OPENROWSET(
             BULK 'sales/csv/*.csv',
             DATA_SOURCE = 'sales_data',
             FORMAT = 'CSV',
             PARSER_VERSION = '2.0',
             HEADER_ROW = TRUE
         ) AS orders
     GROUP BY YEAR(OrderDate)
 END

Exécutez le script pour créer la procédure stockée.
Sous le code que vous venez d’exécuter, ajoutez le code suivant pour appeler la procédure stockée :
```
 EXEC sp_GetYearlySales;
```
Sélectionnez uniquement l’instruction EXEC sp_GetYearlySales; que vous venez d’ajouter et utilisez le bouton ▷ Exécuter pour l’exécuter.
Sous l’onglet Fichiers contenant le système de fichiers de votre lac de données, affichez le contenu du dossier sales (actualisez la vue si nécessaire) et vérifiez qu’un dossier yearlysales a été créé.
Dans le dossier yearlysales, observez qu’un fichier Parquet contenant les données de ventes annuelles agrégées a été créé.
Revenez au script SQL et réexécutez l’instruction EXEC sp_GetYearlySales;. Observez qu’une erreur se produit.

Même si le script supprime la table externe, le dossier contenant les données n’est pas supprimé. Pour réexécuter la procédure stockée (par exemple, dans le cadre d’un pipeline de transformation de données planifiée), vous devez supprimer les anciennes données.
Revenez à l’onglet Fichiers et affichez le dossier sales. Sélectionnez ensuite le dossier yearlysales et supprimez-le.
Revenez au script SQL et réexécutez l’instruction EXEC sp_GetYearlySales;. Cette fois, l’opération réussit et un nouveau fichier de données est généré.

Supprimer les ressources Azure

Si vous avez fini d’explorer Azure Synapse Analytics, vous devriez supprimer les ressources que vous avez créées afin d’éviter des coûts Azure inutiles.

Fermez l’onglet du navigateur Synapse Studio et revenez dans le portail Azure.
Dans le portail Azure, dans la page Accueil, sélectionnez Groupes de ressources.
Sélectionnez le groupe de ressources dp203-xxxxxxx pour votre espace de travail Synapse Analytics (et non le groupe de ressources managé) et vérifiez qu’il contient l’espace de travail Synapse et le compte de stockage pour votre espace de travail.
Au sommet de la page Vue d’ensemble de votre groupe de ressources, sélectionnez Supprimer le groupe de ressources.
Entrez le nom du groupe de ressources dp203-xxxxxxx pour confirmer que vous souhaitez le supprimer, puis sélectionnez Supprimer.

Après quelques minutes, le groupe de ressources de l’espace de travail Azure Synapse et le groupe de ressources managé de l’espace de travail qui lui est associé seront supprimés.