Support formation Microsoft Azure

Support formation Microsoft Azure

Labs 203 | Azure Date Engineer

Lab 02 | Interroger des fichiers à l’aide d’un pool SQL serverless

Gonzague Ducos

SQL est probablement le langage le plus utilisé dans le monde pour travailler avec des données. La plupart des analystes de données maîtrisent l’utilisation des requêtes SQL pour récupérer, filtrer et agréger les données, le plus souvent dans les bases de données relationnelles. Alors que les entreprises tirent de plus en plus parti du stockage de fichiers évolutif pour créer des lacs de données, SQL reste souvent le choix privilégié pour interroger les données. Azure Synapse Analytique fournit des pools SQL serverless qui vous permettent de découpler le moteur de requêtes SQL du stockage de données et d’exécuter des requêtes sur des fichiers de données dans des formats de fichiers courants tels que le texte délimité et Parquet.

Cet atelier prendra environ 40 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 Analytique

Vous aurez besoin d’un espace de travail Azure Synapse Analytique 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 Analytique.

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 manuellement les commandes suivantes pour cloner ce référentiel :

rm -r dp203 -f

git clone https://github.com/MicrosoftLearning/dp-203-azure-data-engineer dp203

Une fois le référentiel cloné, entrez les commandes suivantes pour accéder au dossier de cet atelier et exécuter le script setup.ps1 qu’il contient :

cd dp203/Allfiles/02

./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

Pool SQL serverless dans Azure Synapse Analytique⁠

dans la documentation Azure Synapse Analytique.

Interroger des données dans des fichiers

Le script provisionne un espace de travail Azure Synapse Analytique 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 dp500-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’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 (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 ne contiennent pas de ligne d’en-tête, vous pouvez donc désélectionner l’option d’affichage des en-têtes de colonne.

Fermez l’aperçu, puis utilisez le bouton ↑ pour revenir au dossier sales.

Dans le dossier sales, ouvrez le dossier json et observez qu’il contient des exemples de commandes client dans des fichiers .json . Prévisualisez l’un de ces fichiers pour voir le format JSON utilisé pour une commande client.

Fermez l’aperçu, puis utilisez le bouton ↑ pour revenir au dossier sales.

Dans le dossier sales, ouvrez le dossier parquet et observez qu’il contient un sous-dossier pour chaque année (2019-2021), dans chacun duquel un fichier nommé orders.snappy.parquet contient les données de commande de cette année.

Revenez au dossier sales afin de voir les dossiers csv, json et parquet.

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 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 SQL script 1 qui est créé, remplacez le nom par Requete CSV 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 l’ensemble 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 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.

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 :

SO45347

2020-01-01

Clarence Raji

⁠

clarence35@adventure-works.com⁠

⁠

Route-650 Noir, 52

699.0982

…

There are no rows in this table

⁠

Notez que les résultats se composent de colonnes nommées C1, C2, etc. Dans cet exemple, les fichiers CSV n’incluent pas les en-têtes de colonne. Bien qu’il soit possible d’utiliser les données à l’aide des noms de colonnes génériques qui ont été attribués ou par position ordinale, il sera plus facile de comprendre les données si vous définissez un schéma tabulaire. Pour ce faire, ajoutez une clause WITH à la fonction OPENROWSET comme illustré ici (en remplaçant datalakexxxxxxx par le nom de votre compte de stockage Data Lake), puis réexécutez la requête :

SELECT

    TOP 100 *

FROM

    OPENROWSET(

        BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/csv/',

        FORMAT = 'CSV',

        PARSER_VERSION='2.0'

)

WITH (

SalesOrderNumber VARCHAR(10) COLLATE Latin1_General_100_BIN2_UTF8,

SalesOrderLineNumber INT,

OrderDate DATE,

CustomerName VARCHAR(25) COLLATE Latin1_General_100_BIN2_UTF8,

EmailAddress VARCHAR(50) COLLATE Latin1_General_100_BIN2_UTF8,

Item VARCHAR(30) COLLATE Latin1_General_100_BIN2_UTF8,

Quantity INT,

UnitPrice DECIMAL(18,2),

TaxAmount DECIMAL (18,2)

) AS [result]

Maintenant, les résultats ressemblent à ceci :

SalesOrderNumber

SalesOrderLineNumber

OrderDate

CustomerName

EmailAddress

Item

Quantity

UnitPrice

TaxAmount

SO45347

2020-01-01

Clarence Raji

clarence35@adventure-works.com

Route-650 Noir, 52

699.10

55.93

…

There are no rows in this table

⁠

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

Utiliser SQL pour interroger des fichiers Parquet

Bien que CSV soit un format facile à utiliser, il est courant dans les scénarios de traitement de Big Data d’utiliser des formats de fichiers optimisés pour la compression, l’indexation et le partitionnement. L’un des formats les plus courants est le parquet.

Dans l’onglet files qui contient le système de fichiers de votre lac de données, revenez au dossier sales afin de voir les dossiers csv, json et parquet.

Sélectionnez le dossier Parquet, 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 Parquet, puis appliquez les paramètres pour ouvrir un nouveau script SQL qui interroge les données du dossier. Le script doit ressembler à ceci :

-- This is auto-generated code

SELECT

TOP 100 *

FROM

OPENROWSET(

BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/parquet/**',

FORMAT = 'PARQUET'

) AS [result]

Exécutez le code et notez qu’il retourne les données de commande client dans le même schéma que les fichiers CSV que vous avez explorés précédemment. Les informations de schéma sont incorporées dans le fichier Parquet, de sorte que les noms de colonne appropriés sont affichés dans les résultats.

Modifiez le code comme suit (en remplaçant datalakexxxxxxx par le nom de votre compte de stockage Data Lake), puis exécutez-le.

SELECT YEAR(OrderDate) AS OrderYear,

COUNT(*) AS OrderedItems

FROM

OPENROWSET(

BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/parquet/**',

FORMAT = 'PARQUET'

) AS [result]

GROUP BY YEAR(OrderDate)

ORDER BY OrderYear

Notez que les résultats incluent le nombre de commandes pour les trois années - le caractère générique utilisé dans le chemin d’accès BULK fait en sorte que la requête renvoie des données à partir de tous les sous-dossiers.

Les sous-dossiers reflètent les partitions dans les données Parquet, qui est une technique souvent utilisée pour optimiser les performances des systèmes qui peuvent traiter plusieurs partitions de données en parallèle. Vous pouvez également utiliser des partitions pour filtrer les données.

Modifiez le code comme suit (en remplaçant datalakexxxxxxx par le nom de votre compte de stockage Data Lake), puis exécutez-le.

SELECT YEAR(OrderDate) AS OrderYear,

COUNT(*) AS OrderedItems

FROM

OPENROWSET(

BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/parquet/year=*/',

FORMAT = 'PARQUET'

) AS [result]

WHERE [result].filepath(1) IN ('2019', '2020')

GROUP BY YEAR(OrderDate)

ORDER BY OrderYear

Examinez les résultats et notez qu’ils ne comprennent que les chiffres des ventes de 2019 et 2020. Ce filtrage est réalisé en incluant un caractère générique pour la valeur du dossier de partition dans le chemin BULK (year=*) et une clause WHERE basée sur la propriété filepath des résultats renvoyés par OPENROWSET (qui dans ce cas a l’alias [result]).

Nommez votre script Requete Parquet Sales et publiez-la. Fermez ensuite le volet de script.

Utiliser SQL pour interroger des fichiers JSON

JSON est un autre format de données populaire, donc il est utile pour pouvoir interroger des fichiers .json dans un pool SQL serverless.

Dans l’onglet files contenant le système de fichiers de votre lac de données, revenez au dossier sales afin de voir les dossiers csv, json et parquet.

Sélectionnez le dossier json, 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. Le script doit ressembler à ceci :

-- This is auto-generated code

SELECT

TOP 100 *

FROM

OPENROWSET(

BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/json/',

FORMAT = 'CSV',

        PARSER_VERSION = '2.0'

) AS [result]

Le script est conçu pour interroger des données délimitées par des virgules (CSV) plutôt que JSON, vous devez donc apporter quelques modifications avant qu’il ne fonctionne correctement.

Modifiez le script comme suit (en remplaçant datalakexxxxxxx par le nom de votre compte de stockage Data Lake) pour :

Supprimez le paramètre de version de l’analyseur.

Ajoutez des paramètres pour les terminaisons de champ, les champs entre guillemets et les terminaisons de ligne avec le code de caractère 0x0b.

Formatez les résultats sous la forme d’un champ unique contenant la ligne de données JSON sous la forme d’une chaîne NVARCHAR(MAX).

SELECT

TOP 100 *

FROM

OPENROWSET(

BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/json/',

FORMAT = 'CSV',

FIELDTERMINATOR ='0x0b',

FIELDQUOTE = '0x0b',

ROWTERMINATOR = '0x0b'

) WITH (Doc NVARCHAR(MAX)) as rows

Exécutez le code modifié et observez que les résultats incluent un document JSON pour chaque commande.

Modifiez la requête comme suit (en remplaçant datalakexxxxxxx par le nom de votre compte de stockage Data Lake) afin qu’elle utilise la fonction JSON_VALUE pour extraire des valeurs de champ individuelles à partir des données JSON.

SELECT JSON_VALUE(Doc, '$.SalesOrderNumber') AS OrderNumber,

JSON_VALUE(Doc, '$.CustomerName') AS Customer,

Doc

FROM

OPENROWSET(

BULK 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/json/',

FORMAT = 'CSV',

FIELDTERMINATOR ='0x0b',

FIELDQUOTE = '0x0b',

ROWTERMINATOR = '0x0b'

) WITH (Doc NVARCHAR(MAX)) as rows

Nommez votre script Requête JSON Sales et publiez-la. Fermez ensuite le volet de script.

Accéder à des données externes dans une base de données

Jusqu’à présent, vous avez utilisé la fonction OPENROWSET dans une requête SELECT pour récupérer des données à partir de fichiers dans un lac de données. Les requêtes ont été exécutées dans le contexte de la base de données master de votre pool SQL serverless. Cette approche convient pour une exploration initiale des données, mais si vous envisagez de créer des requêtes plus complexes, il peut être plus efficace d’utiliser la fonctionnalité PolyBase de Synapse SQL pour créer des objets dans une base de données qui référencent l’emplacement de données externe.

Créer une source de données externe

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ù les fichiers sont stockés.

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.

CREATE DATABASE Sales

COLLATE Latin1_General_100_BIN2_UTF8;

GO;

Use Sales;

GO;

CREATE EXTERNAL DATA SOURCE sales_data WITH (

LOCATION = 'https://datalakexxxxxxx.dfs.core.windows.net/files/sales/'

);

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 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.

Dans le menu ... de la base de données Sales, sélectionnez Nouveau script SQL > Script vide. Ensuite, dans le nouveau volet de script, entrez et exécutez la requête suivante :

SELECT *

FROM

OPENROWSET(

BULK 'csv/*.csv',

DATA_SOURCE = 'sales_data',

FORMAT = 'CSV',

        PARSER_VERSION = '2.0'

) AS orders

La requête utilise la source de données externe pour se connecter au lac de données, et la fonction OPENROWSET n’a plus besoin de référencer que le chemin d’accès relatif aux fichiers .csv.

Modifiez le code comme suit pour interroger les fichiers Parquet à l’aide de la source de données.

SELECT *

FROM

OPENROWSET(

BULK 'parquet/year=*/*.snappy.parquet',

DATA_SOURCE = 'sales_data',

FORMAT='PARQUET'

) AS orders

WHERE orders.filepath(1) = '2019'

Avant de commencer

Approvisionner un espace de travail Azure Synapse Analytique

Interroger des données dans des fichiers