azure
Support formation Microsoft Azure
azure
Support formation Microsoft Azure
Labs 203 | Azure Date Engineer

Lab 04 | Analyser les données dans une base de données de lac

Gonzague Ducos
Azure Synapse Analytics vous permet de combiner la flexibilité du stockage de fichiers dans un lac de données avec les capacités de requête de schéma structuré et SQL d'une base de données relationnelle grâce à la possibilité de créer une base de données de lac. Une base de données de lac est un schéma de base de données relationnelle défini sur un data lake de fichiers de données qui permet de séparer le stockage des données du calcul utilisé pour l'interroger. Les bases de données de lac combinent les avantages d'un schéma structuré qui inclut l'appui aux types de données, aux relations et à d'autres caractéristiques que l'on ne trouve généralement que dans les systèmes de base de données relationnels, avec la souplesse du stockage de données dans des fichiers qui peuvent être utilisés indépendamment d'un magasin de base de données relationnel. Essentiellement, la base de données de lac "superpose" un schéma relationnel sur des fichiers dans des dossiers dans le lac de données.
Cet exercice doit durer environ 45 minutes.

Avant de commencer

Vous aurez besoin d'un
abonnement Azure
⁠
dans lequel vous avez un accès au niveau administratif.

Fournir un espace de travail Azure Synapse Analytics

Pour soutenir une base de données lacustres, vous avez besoin d'un espace de travail Azure Synapse Analytics avec accès au stockage des lacs de données. Il n'est pas nécessaire de disposer d'un pool SQL dédié, car vous pouvez définir la base de données lacustre à l'aide du pool SQL sans serveur intégré. En option, vous pouvez également utiliser un pool Spark pour travailler avec des données dans la base de données du lac.
Dans cet exercice, vous utiliserez une combinaison d'un script PowerShell et d'un modèle ARM pour fournir un espace de travail Azure Synapse Analytics.
Se connecter au
portail Azure
⁠
à l'adresse suivante: https://portal.azure.com.
Utilisez le bouton droite de la barre de recherche en haut de la page pour créer un nouveau Cloud Shell dans le portail Azure, en sélectionnant un environnement PowerShell et en créant un stockage si on vous y est demandé. Le shell cloud fournit une interface de ligne de commande dans une vitre au bas du portail Azure, comme indiqué ici: ​
⁠
⁠
Remarque : Si vous avez déjà créé une coque en nuage qui utilise un environnement Bash, utilisez le menu déroulant en haut à gauche du volet coque en nuage pour le changer en PowerShell.
Notez que vous pouvez redimensionner l'enveloppe en faisant glisser la barre de séparation au sommet de la vitre, ou en utilisant les icônes ', ' et X en haut à droite de la vitre pour minimiser, maximiser et fermer la vitre. Pour plus d'informations sur l'utilisation de l'Azure Cloud Shell, voir la
documentation Azure Cloud Shell
⁠
.
Dans la fenêtre PowerShell, entrez les commandes suivantes pour cloner ce repo:
rm -r dp-203 -f
git clone https://github.com/MicrosoftLearning/dp-203-azure-data-engineer dp-203
Après que le repo a été cloné, entrez les commandes suivantes pour changer au dossier pour cet exercice et exécuter le script setup.ps1 qu'il contient:
cd dp-203/Allfiles/labs/04
./setup.ps1
Si vous y êtes invité, choisissez l'abonnement que vous souhaitez utiliser (cela n'aura lieu que si vous avez accès à plusieurs abonnements Azure).
Lorsque vous y êtes invité, saisissez un mot de passe approprié à définir pour votre pool Azure Synapse SQL. ​Remarque : Assurez-vous de vous souvenir de ce mot de passe.
Attendez que le script soit complet - cela prend généralement environ 10 minutes, mais dans certains cas peut prendre plus de temps. Pendant que vous attendez, passez en revue
Base de données de lac
⁠
et
Modèles de base de données de lac
⁠
dans la documentation Azure Synapse Analytics.

Modifier les autorisations de conteneurs

Une fois le script de déploiement terminé, dans le portail Azure, allez dans le groupe de ressources qu'il a créé, et remarquez que ce groupe de ressources contient votre espace de travail Synapse, un compte de stockage pour votre lac de données, et un pool Apache Spark.
Sélectionnez le compte de stockage pour votre lac de données nommé datalakexxxxxxx ​
⁠
⁠
Dans le conteneur datalakexxxxxx, sélectionnez le dossier files ​
⁠
⁠
Dans le dossier files, vous remarquerez que la méthode d'authentification est listée comme Clé d’accès (passez au compte d'utilisateurs Entra). Cliquez sur ceci pour changer le compte d'utilisateurs Entra. ​
⁠
⁠

Créer une base de données sur les lacs

Une base de données lacustres est un type de base de données que vous pouvez définir dans votre espace de travail, et qui fonctionne à l'aide du pool SQL sans serveur intégré.
Sélectionnez votre espace de travail Synapse et dans sa page Vue d'ensemble, dans la carte Studio Open, sélectionnez Ouvrir pour ouvrir Synapse Studio dans un nouvel onglet du navigateur; en signant si vous y êtes invité.
Sur le côté gauche de Synapse Studio, utilisez le bouton ›› pour étendre du menu - cela révèle les différentes pages dans Synapse Studio que vous utiliserez pour gérer les ressources et effectuer des tâches d'analyse des données.
Sur la page Données, consultez l'onglet Lien et vérifiez que votre espace de travail inclut un lien vers votre compte de stockage Azure Data Lake Storage Gen2.
Sur la page Données, revenez à l'onglet Espace de travail et notez qu'il n'y a pas de bases de données dans votre espace de travail.
Dans le menu +, sélectionnez Base de données Lake pour ouvrir un nouvel onglet dans lequel vous pouvez concevoir votre schéma de base de données (en acceptant les conditions d'utilisation des modèles de base de données si vous y êtes invité).
Dans le volet Propriétés de la nouvelle base de données, modifier le Nom en RetailDB et vérifier que la propriété Dossier d'entrée est automatiquement mise à jour en files/RetailDB. Laissez Format de données en tant que Texte délimité (vous pouvez également utiliser le format Parquet, et vous pouvez remplacer le format de fichier pour les tables individuelles - nous utiliserons des données délimitées par des virgules dans cet exercice.)
En haut du volet RetailDB, sélectionnez Publish pour sauvegarder la base de données jusqu'à présent.
Dans le volet Données de gauche, voyez l'onglet Linked. Puis élargissez Azure Data Lake Storage Gen2 et le store primaire datalakexxxxxxx de votre espace de travail synapsexxxxxxx, et sélectionnez le système de fichiers files ; qui contient actuellement un dossier nommé synapse.
Dans l'onglet files qui s'est ouvert, utilisez le bouton + Nouveau dossier pour créer un nouveau dossier nommé RetailDB - il s'agira du dossier d'entrée pour les fichiers de données utilisés par les tables dans votre base de données.

Créer une table

Maintenant que vous avez créé une base de données lacustre, vous pouvez définir son schéma en créant des tables.

Définir le schéma de la table

Revenez à l'onglet RetailDB pour la définition de votre base de données, et dans la liste + Table, sélectionnez Personnalisé, et notez qu'une nouvelle table intitulée Table 1 est ajoutée à votre base de données.
Avec Table 1 sélectionnée, dans l'onglet Général sous le canvas de conception de la base de données, changer la propriété Nom par Customer.
Élargir la section Paramètres de stockage pour la table et noter que le tableau sera stocké sous forme de texte délimité dans le dossier fichiers/RetailDB/Customer dans la mémoire de données par défaut pour votre espace de travail Synapse.
Sur l'onglet Colonnes, notez que par défaut, la table contient une colonne nommée colonne 1. Modifier la définition de colonne pour tenir compte des propriétés suivantes:
Name
Keys
Description
Nullability
Data type
Format / Length
CustomerId
PK 🗹
Unique customer ID
🗆
long
There are no rows in this table
⁠
Dans le menu + Colonne, sélectionnez Nouvelle colonne et modifiez la nouvelle définition de colonne pour ajouter une colonne FirstName au tableau comme suit:
Name
Keys
Description
Nullability
Data type
Format / Length
CustomerId
PK 🗹
Unique customer ID
🗆
long
FirstName
PK 🗆
Customer first name
🗆
string
256
There are no rows in this table
⁠
Ajouter plus de nouvelles colonnes jusqu'à ce que la définition du tableau ressemble à ceci:
Name
Keys
Description
Nullability
Data type
Format / Length
CustomerId
PK 🗹
Unique customer ID
🗆
long
FirstName
PK 🗆
Customer first name
🗆
string
256
LastName
PK 🗆
Customer last name
🗹
string
256
EmailAddress
PK 🗆
Customer email
🗆
string
256
Phone
PK 🗆
Customer phone
🗹
string
256
There are no rows in this table
⁠
Lorsque vous avez ajouté toutes les colonnes, publiez à nouveau la base de données pour sauvegarder les modifications.
Dans le volet Données à gauche, revenez à l'onglet Workspace de sorte que vous pouvez voir la base de données de lac RetailDB. Puis développez-le et rafraîchissez son dossier Tables pour voir la table Customer nouvellement créé.

Charger les données dans le chemin de stockage de la table

Dans le volet principal, rallez à l'onglet files, qui contient le système de fichiers avec le dossier RetailDB. Ouvrez ensuite le dossier RetailDB et créez un nouveau dossier nommé Customer. C'est là que la table Customer obtiendra ses données.
Ouvrez le nouveau dossier Customer, qui doit être vide.
Téléchargez le fichier de données client.csv à partir de
https://raw.githubusercontent.com/MicrosoftLearning/dp-203-azure-data-engineer/master/Allfiles/labs/04/data/customer.csv
⁠
et enregistrez-le dans un dossier sur votre ordinateur local (peut importe l'endroit). Ensuite, dans le dossier Customer de Synapse Explorer, utilisez le bouton "Télécharger" pour télécharger le fichier client.csv dans le dossier RetailDB/Customer dans votre lac de données. ​Note : Dans un scénario de production réel, vous créeriez probablement un pipeline pour ingérer des données dans le dossier pour les données de la table. Nous le téléchargeons directement dans l'interface utilisateur Synapse Studio dans cet exercice pour l'opportunité.
Dans le volet Données de gauche, dans l'onglet Espace de travail, dans le menu ... pour la table Customer, sélectionnez Nouveau script SQL et Sélectionner les 100 premières lignes. Ensuite, dans le nouveau volet SQL script 1 qui s'est ouvert, assurez-vous que le pool SQL Intégré est connecté, et utilisez le bouton ▷ Run pour exécuter le code SQL. Les résultats devraient inclure les 100 premières lignes du table Customer, sur la base des données stockées dans le dossier sous-jacent dans le lac de données.
Fermez l'onglet SQL script 1, en jetant vos modifications.

Créer une table à partir d'un modèle de base de données

Comme vous l'avez vu, vous pouvez créer les tables dont vous avez besoin dans votre base de données lake à partir de rien. Cependant, Azure Synapse Analytics fournit également de nombreux modèles de base de données basés sur des charges de base de données et des entités communes que vous pouvez utiliser comme point de départ pour votre schéma de base de données.

Définir le schéma de la table

Dans le volet principal, revenez à la vitre RetailDB, qui contient votre schéma de base de données (qui ne contient actuellement que la table Customer).
Dans le menu + Table, sélectionnez A partir d’un modèle. Puis dans la page Ajouter à partir du modèle, sélectionnez Retail et cliquez sur Continuer.
Dans la page Ajouter à partir du modèle (Retail), attendez que la liste des tables soit remplit, puis développez Product et sélectionnez RetailProduct. Puis cliquez sur Ajouter. Cela ajoute une nouvelle table basée sur le modèle RetailProduct à votre base de données.
Dans le volet RetailDB, sélectionnez la nouvelle table RetailProduct. Ensuite, dans le panneau sous le canevas de conception, sur l'onglet Général, changez le nom en Product et vérifiez que les paramètres de stockage de la table spécifient les fichiers de dossiers d’entrée files/RetailDB/Product.
Dans l'onglet Colonnes pour la table Product, notez que la table inclut déjà un grand nombre de colonnes héritées du modèle. Il y a plus de colonnes que nécessaire pour cette table, donc vous aurez besoin d'en supprimer.
Sélectionnez la case à cocher à côté de Nom pour sélectionner toutes les colonnes, puis désélectionnez les colonnes suivantes (que vous devez conserver):
ProductId
ProductName
IntroductionDate
ActualAbandonmentDate
ProductGrossWeight
ItemSku
Dans la barre d'outils de la fenêtre des colonnes, sélectionnez Supprimer pour supprimer les colonnes sélectionnées. Ceci doit vous laisser avec les colonnes suivantes :
Name
Keys
Description
Nullability
Data type
Format / Length
ProductId
PK 🗹
The unique identifier of a Product.
🗆
long
ProductName
PK 🗆
The name of the Product…
🗹
string
128
IntroductionDate
PK 🗆
The date that the Product was introduced for sale.
🗹
date
YYYY-MM-DD
ActualAbandonmentDate
PK 🗆
The actual date that the marketing of the product was discontinued…
🗹
date
YYY-MM-DD
ProductGrossWeight
PK 🗆
The gross product weight.
🗹
decimal
18,8
ItemSku
PK 🗆
The Stock Keeping Unit identifier…
🗹
string
20
There are no rows in this table
⁠

Ajouter une nouvelle colonne intitulée ListPrice au tableau comme indiqué ici:
Name
Keys
Description
Nullability
Data type
Format / Length
ProductId
PK 🗹
The unique identifier of a Product.
🗆
long
ProductName
PK 🗆
The name of the Product…
🗹
string
128
IntroductionDate
PK 🗆
The date that the Product was introduced for sale.
🗹
date
YYYY-MM-DD
ActualAbandonmentDate
PK 🗆
The actual date that the marketing of the product was discontinued…
🗹
date
YYY-MM-DD
ProductGrossWeight
PK 🗆
The gross product weight.
🗹
decimal
18,8
ItemSku
PK 🗆
The Stock Keeping Unit identifier…
🗹
string
20
ListPrice
PK 🗆
The product price.
🗆
decimal
18,2
There are no rows in this table
⁠
Lorsque vous avez modifié les colonnes comme indiqué ci-dessus, publiez à nouveau la base de données pour sauvegarder les modifications.
Dans le volet Données à gauche, revenez à l'onglet Workspace de sorte que vous pouvez voir la base de données du lac RetailDB. Ensuite, utilisez le menu ... pour son dossier Tables pour rafraîchir la vue et voir la table Product nouvellement créé.

Chargement des données dans le chemin de stockage de la table

Dans le volet principal, revenez à l'onglet files, qui contient le système de fichiers, et naviguez jusqu'au dossier Fichier/RetailDB, qui contient actuellement le dossier Customer pour la table que vous avez précédemment créée.
Dans le dossier RetailDB, créez un nouveau dossier nommé Product. C'est là que le tableau des produits obtiendra ses données.
Ouvrez le nouveau dossier produit, qui devrait être vide.
Téléchargez le fichier de données product.csv à partir de
https://raw.githubusercontent.com/MicrosoftLearning/dp-203-azure-data-engineer/master/master/labs/04/data/product.csv
⁠
et enregistrez-le dans un dossier sur votre ordinateur local (elle n'a pas d'importance). Ensuite, dans le dossier produit de l'Explorateur Synapse Explorer, utilisez le bouton Télécharger pour télécharger le fichier product.csv dans le dossier RetailDB/Product dans votre lac de données.
Dans le volet Données de gauche, dans l'onglet Espace de travail, dans le menu ... pour la table Product, sélectionnez Nouveau script SQL et Sélectionnez les 100 premières lignes. Ensuite, dans le nouveau volet SQL script 1 qui s'est ouvert, assurez-vous que le pool SQL Intégré est connecté, et utilisez le bouton Run pour exécuter le code SQL. Les résultats devraient inclure les 100 premières lignes du tableau de produit, sur la base des données stockées dans le dossier sous-jacent dans le lac de données.
Fermez l'onglet SQL script 1, en jetant vos modifications.

Créer une table à partir des données existantes

Jusqu'à présent, vous avez créé des tables et ensuite les avoir peuplées de données. Dans certains cas, vous pouvez déjà avoir des données dans un lac de données à partir duquel vous souhaitez dériver une table.

Télécharger les données

Dans le volet principal, revenez à l'onglet files, qui contient le système de fichiers, et naviguez vers le dossier fichiers/RetailDB, qui contient actuellement les dossiers Customer et Product pour les tables que vous avez précédemment créées.
Dans le dossier RetailDB, créez un nouveau dossier nommé SalesOrder.
Ouvrez le nouveau dossier SalesOrder, qui devrait être vide.
Téléchargez le fichier de données salesorder.csv à partir de
https://raw.githubusercontent.com/MicrosoftLearning/dp-203-azure-data-engineer/master/master/labs/04/data/salesorder.csv
⁠
et enregistrez-le dans un dossier sur votre ordinateur local (peu importe où). Ensuite, dans le dossier SalesOrder de Synapse Explorer, utilisez le bouton ⤒ Télécharger pour télécharger le fichier salesorder.csv dans le dossier RetailDB/SalesOrder dans votre lac de données.

Créer une table

Dans le volet principal, revenez à la fenêtre RetailDB, qui contient votre schéma de base de données (qui contient actuellement les tables Customer et Product).
Dans le menu + Table, sélectionnez A partir du lac de données. Ensuite, dans le volet Créer une table à partir du lac de données, spécifiez les options suivantes:
Nom du tableau externe : SalesOrder
Service lié : Sélectionner synapse xxxxxxx -WorkspaceDefautStorage (datalake xxxxxxx)
Fichier d'entrée du dossier: files/RetailDB/SalesOrder
Continuez jusqu'à la page suivante, puis créez le tableau avec les options suivantes:
Type de fichier : CSV
Terminateur de terrain : Valeur par défaut (virgule ,)
Première ligne : Laisser Déduire les noms de colonne non sélectionnés.
Délimiteur de chaîne : Par défaut (chaîne vide)
Utiliser le type par défaut : Type par défaut (true, false)
Longueur de chaîne max. : 4000
Lorsque la table a été créé, notez qu'il comprend des colonnes nommées C1, C2 et ainsi de suite, et que les types de données ont été déduits des données dans le dossier. Modifier les définitions de la colonne comme suit :
Name
Keys
Description
Nullability
Data type
Format / Length
SalesOrderId
PK 🗹
The unique identifier of an order.
🗆
long
OrderDate
PK 🗆
The date of the order.
🗆
timestamp
yyyy-MM-dd
LineItemId
PK 🗹
The ID of an individual line item.
🗆
long
CustomerId
PK 🗆
The customer.
🗆
long
ProductId
PK 🗆
The product.
🗆
long
Quantity
PK 🗆
The order quantity.
🗆
long
There are no rows in this table
⁠
Note : La table contient un enregistrement pour chaque article commandé, et comprend une clé primaire composite composée de SalesOrderId et LineItemId.
Dans l'onglet Relations pour la table SalesOrder, dans la liste Relations, sélectionnez Vers la table, puis définissez la relation suivante :
From table
From column
To table
To column
Customer
CustomerId
SalesOrder
CustomerId
There are no rows in this table
⁠
Ajouter une deuxième relation Vers la table avec les paramètres suivants :
From table
From column
To table
To column
Product
ProductId
SalesOrder
ProductId
There are no rows in this table
⁠
La capacité à définir des relations entre les tables contribue à imposer une intégrité référentielle entre les entités de données connexes. Il s'agit d'une caractéristique commune des bases de données relationnelles qui, autrement, seraient difficiles à appliquer aux fichiers dans un lac de données.
Publier à nouveau la base de données pour sauvegarder les modifications.
Dans le volet Données à gauche, revenez à l'onglet Workspace de sorte que vous pouvez voir la base de données du lac RetailDB. Ensuite, utilisez le menu ... pour son dossier Tables pour rafraîchir la vue et voir la table de SalesOrder nouvellement créée.

Travail avec les tables de lac

Maintenant que vous avez des tableaux dans votre base de données, vous pouvez les utiliser pour travailler avec les données sous-jacentes.

Requêtes de table en SQL

Dans Synapse Studio, sélectionnez la page Développement.
Dans le volet Développer, dans le menu +, sélectionnez Script SQL.
Dans le nouveau volet script SQL 1, assurez-vous que le script est connecté au pool SQL intégré et dans la liste de la base de données utilisateur, sélectionnez RetailDB.
Inscrire le code SQL suivant :
SELECT o.SalesOrderID, c.EmailAddress, p.ProductName, o.Quantity
FROM SalesOrder AS o
JOIN Customer AS c ON o.CustomerId = c.CustomerId
JOIN Product AS p ON o.ProductId = p.ProductId
Utilisez le bouton ▷ Run pour exécuter le code SQL.
Les résultats montrent les détails de la commande avec les informations sur les clients et le produit.
Fermez le volet Script SQL 1, en jetant vos modifications.

Insérer des données à l'aide de Spark

Dans le volet Développement, dans le menu +, sélectionnez Notebook.
Dans le nouveau volet Notebook 1, joignez le notebook au pool Spark sparkxxxxxxx.
Inscrire le code suivant dans la cellule du bloc-notes vide :
%%sql
INSERT INTO `RetailDB`.`SalesOrder` VALUES (99999, CAST('2022-01-01' AS TimeStamp), 1, 6, 5, 1)
Utilisez le bouton ▷ sur la gauche de la cellule pour l'exécuter et attendre qu'il finisse de fonctionner. Notez qu'il faudra un certain temps pour démarrer le pool Spark.
Utilisez le bouton + Code pour ajouter une nouvelle cellule au bloc-notes.
Entrez le code suivant dans la nouvelle cellule:
%%sql
SELECT * FROM `RetailDB`.`SalesOrder` WHERE SalesOrderId = 99999
Utilisez le bouton ▷ à gauche de la cellule pour l'exécuter et vérifiez qu'une ligne pour les commandes 99999 a été insérée dans la table SalesOrder.
Fermez le volet Bloc-notes 1, arrêtez la session Spark et rejetez vos modifications.

Supprimer les ressources Azure

Si vous avez fini d'explorer Azure Synapse Analytics, vous devez supprimer les ressources que vous avez créées pour éviter les coûts Azure inutiles.
Fermez l'onglet Synapse Studio et retournez sur le portail Azure.
Sur le portail Azure, sur la page d'accueil, sélectionnez Groupes de ressources.
Sélectionnez le xxxxxxxgroupe de ressources dp203-xxxxxxx pour votre espace de travail Synapse Analytics (et non le groupe de ressources géré), et vérifiez qu'il contient l'espace de travail Synapse, le compte de stockage et le pool Spark pour votre espace de travail.
En haut de la page Vue d'ensemble pour votre groupe de ressources, sélectionnez Supprimer le groupe de ressources.
Saisissez le nom du groupe de ressources dp203-XXXXXXX pour confirmer que vous souhaitez le supprimer, et sélectionnez Supprimer.
Après quelques minutes, votre groupe de ressources Azure Synapse d'espace de travail et le groupe de ressources de l'espace de travail géré associé à celui-ci seront supprimés.
Avant de commencer
Fournir un espace de travail Azure Synapse Analytics
Modifier les autorisations de conteneurs
Créer une base de données sur les lacs
Créer une table
Définir le schéma de la table
Charger les données dans le chemin de stockage de la table
Créer une table à partir d'un modèle de base de données
Définir le schéma de la table
Chargement des données dans le chemin de stockage de la table
Créer une table à partir des données existantes
Télécharger les données
Créer une table
Travail avec les tables de lac
Requêtes de table en SQL
Insérer des données à l'aide de Spark
Supprimer les ressources Azure
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.