Support formation Microsoft Azure

Pages

Labs 203 | Azure Date Engineer

Lab 03 | Transformer des fichiers à l’aide d’un pool SQL serverless

Gonzague Ducos

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

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

Cet exercice devrait durer environ 30 minutes.

Avant de commencer

Vous aurez besoin d’un

abonnement Azure⁠

dans lequel vous disposez d’un accès de niveau administratif.

Approvisionner un espace de travail Azure Synapse Analytics

Vous aurez besoin d’un espace de travail Azure Synapse Analytics avec accès au stockage du lac de données. 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 une combinaison d’un script PowerShell et d’un modèle ARM pour provisionner 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 Cloud Shell dans le portail Azure, en sélectionnant un environnement PowerShell et en créant un stockage si vous y êtes invité. Cloud Shell fournit une interface de ligne de commande dans un volet situé au bas du portail Azure, comme illustré ici :

⁠

Remarque : Si vous avez déjà créé un Cloud Shell 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 la coque du nuage en faisant glisser la barre de séparation en haut du volet ou en utilisant les icônes —, ◻ et X 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 le référentiel 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 que vous souhaitez utiliser (cela ne se produira que si vous avez accès à plusieurs abonnements Azure).

Lorsque vous y êtes invité, entrez un mot de passe approprié à définir pour votre pool SQL Azure Synapse. Remarque : N’oubliez pas ce mot de passe !

Attendez que le script soit terminé - 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 provisionne un espace de travail Azure Synapse Analytics et un compte de 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 du 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, ce qui révèle les différentes pages de Synapse Studio que vous utiliserez pour gérer les ressources et effectuer des tâches d’analyse 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 (Principal - 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 notez 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, et observez que ce dossier contient des fichiers .csv pour trois ans 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, sélectionnez Sélectionner les 100 premières lignes.

Dans la liste Type de fichier, sélectionnez Format de 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 qui est créé, remplacez le nom Fichiers CSV Query Sales 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 à 🗏*) à l’extrémité droite de la barre d’outils pour masquer le volet Propriétés.

Examinez le code SQL qui a été généré, qui doit être similaire à 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 des colonnes dans la première ligne ; modifiez donc la requête pour ajouter un HEADER_ROW = TRUE à la fonction OPENROWSET, comme indiqué 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 à, assurez-vous que l’option Intégré est sélectionnée, car elle 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 et examinez les résultats, qui doivent ressembler à ceci :

⁠

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

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

Une méthode 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 sous forme de 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 être incluses dans un processus en aval visant à charger les données transformées dans un entrepôt de données).

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

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 où vous souhaitez stocker les fichiers des tables externes. Un format de fichier externe vous permet de définir le format de ces fichiers, par exemple, Parquet ou CSV. Pour utiliser ces objets avec des tables externes, vous devez les créer dans une base de données autre que la base de données maître par défaut.

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

Dans le volet Nouveau script, ajoutez le code suivant (en remplaçant datalakexxxxxxx par le nom de votre compte de stockage Data Lake) 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 qu’il s’appelle Create Sales DB et publiez-le.

Assurez-vous que le script est connecté au pool SQL Intégré 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, où une liste de bases de données SQL est désormais affichée. 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 Ressources externes et le dossier Sources de données externes en dessous pour afficher la source de données externe sales_data que vous avez créée.

Créer une table externe

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

Dans le nouveau volet de script, ajoutez le code suivant pour récupérer et agréger les données des fichiers de vente CSV à l’aide de la source de données externe, en notant que le chemin d’accès BULK est relatif à l’emplacement du dossier sur lequel 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 devraient ressembler à ceci :

Produit

ArticlesVendu

Revenus nets

Casquette avec logo AWC

1063

8791.86

…

There are no rows in this table

⁠

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

CREATE EXTERNAL TABLE ProductSalesTotals

WITH (

LOCATION = 'sales/productsales/',

DATA_SOURCE = sales_data,

FILE_FORMAT = ParquetFormat

)

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 basée sur les résultats de la requête.

Nommez le script Creer la table ProductSalesTotals et publiez-la.

Sur la page de données, dans l’onglet Espace de travail, affichez le contenu du dossier Tables externes de la base de données SQL Sales pour vérifier qu’une nouvelle 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 renvoie les données agrégées de vente de produits.

Dans l’onglet files contenant le système de fichiers de votre lac de données, affichez le contenu du dossier sales (en actualisant la vue si nécessaire) et vérifiez qu’un nouveau 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 agrégées sur les ventes de produits. Pour le prouver, 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 la transformation des données dans une procédure stockée

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

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

Dans le nouveau volet de 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

BEGIN

-- drop existing table

IF EXISTS (

SELECT * FROM sys.external_tables

WHERE name = 'YearlySalesTotals'