Support formation Microsoft Azure

Support formation Microsoft Azure

Labs 203 | Azure Date Engineer

Lab 09 | Charger des données dans un entrepôt de données relationnel

Gonzague Ducos

Dans cet exercice, vous allez charger des données dans un pool SQL dédié.

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 Analytique

Vous aurez besoin d’un espace de travail Azure Synapse Analytique avec accès au stockage du lac de données et d’un pool SQL dédié hébergeant un entrepôt 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.

Cloud Shell peut être redimensionné en faisant glisser la barre de séparation en haut du volet ou à l’aide des 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 passer au dossier de cet exercice, puis exécutez le script setup.ps1 qu’il contient :

cd dp-203/Allfiles/labs/09

./setup.ps1

Si vous y êtes invité, choisissez l’abonnement que vous souhaitez utiliser (cette option 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

Stratégies de chargement de données pour un pool SQL dédié dans Azure Synapse Analytique⁠

dans la documentation Azure Synapse Analytique.

Préparation du chargement des 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, révélant ainsi 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.

Sur la page Gérer, sous l’onglet Pools SQL, sélectionnez la ligne du pool SQL dédié sql xxxxxxx, qui héberge l’entrepôt de données pour cet exercice, et utilisez son icône ▷ pour le démarrer. confirmer que vous souhaitez le reprendre lorsque vous y êtes invité.

La reprise de la piscine peut prendre quelques minutes. Vous pouvez utiliser le bouton ↻ Actualiser pour vérifier périodiquement son état. L’état s’affichera En ligne lorsqu’il sera prêt. Pendant que vous attendez, suivez les étapes ci-dessous pour afficher les fichiers de données que vous allez charger.

Sur 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 (principal).

Sélectionnez le conteneur de fichiers et notez qu’il contient un dossier nommé data. Ce dossier contient les fichiers de données que vous allez charger dans l’entrepôt de données.

Ouvrez le dossier data et observez qu’il contient .csv fichiers de données sur les clients et les produits.

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, vous pouvez donc sélectionner l’option d’affichage des en-têtes de colonne.

Revenez à la page Gérer et vérifiez que votre pool SQL dédié est en ligne.

Charger des tables d’entrepôt de données

Examinons quelques approches basées sur SQL pour charger des données dans l’entrepôt de données.

Sur la page Données, sélectionnez l’onglet Espace de travail.

Développez Base de données SQL et sélectionnez votre base de données sqlxxxxxxx. Ensuite, dans son menu ..., sélectionnez Nouveau script SQL > Script vide.

Vous disposez maintenant d’une page SQL vierge, qui est connectée à l’instance pour les exercices suivants. Vous allez utiliser ce script pour explorer plusieurs techniques SQL que vous pouvez utiliser pour charger des données.

Charger des données à partir d’un lac de données à l’aide de l’instruction COPY

Dans votre script SQL, entrez le code suivant dans la fenêtre.

SELECT COUNT(1)

FROM dbo.StageProduct

Dans la barre d’outils, utilisez le bouton ▷ Exécuter pour exécuter le code SQL et vérifier qu’il y a 0 ligne actuellement dans la table StageProduct.

Remplacez le code par l’instruction COPY suivante (en remplaçant datalakexxxxxx par le nom de votre data lake) :

COPY INTO dbo.StageProduct

(ProductID, ProductName, ProductCategory, Color, Size, ListPrice, Discontinued)

FROM 'https://datalakexxxxxx.blob.core.windows.net/files/data/Product.csv'

WITH

(

FILE_TYPE = 'CSV',

MAXERRORS = 0,

IDENTITY_INSERT = 'OFF',

FIRSTROW = 2 --Skip header row

);

SELECT COUNT(1)

FROM dbo.StageProduct

Exécutez le script et examinez les résultats. 11 lignes doivent avoir été chargées dans la table StageProduct.

Utilisons maintenant la même technique pour charger une autre table, cette fois en enregistrant toutes les erreurs qui peuvent se produire.

Remplacez le code SQL dans le volet de script par le code suivant, en remplaçant datalakexxxxxx par le nom de votre lac de données dans les deux FROM et le ERRORFILE Clauses:

COPY INTO dbo.StageCustomer

(GeographyKey, CustomerAlternateKey, Title, FirstName, MiddleName, LastName, NameStyle, BirthDate,

MaritalStatus, Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren, NumberChildrenAtHome, EnglishEducation,

SpanishEducation, FrenchEducation, EnglishOccupation, SpanishOccupation, FrenchOccupation, HouseOwnerFlag,

NumberCarsOwned, AddressLine1, AddressLine2, Phone, DateFirstPurchase, CommuteDistance)

FROM 'https://datalakexxxxxx.dfs.core.windows.net/files/data/Customer.csv'

WITH

(

FILE_TYPE = 'CSV'

,MAXERRORS = 5

,FIRSTROW = 2 -- skip header row

,ERRORFILE = 'https://datalakexxxxxx.dfs.core.windows.net/files/'

);

Exécutez le script et examinez le message qui s’affiche. Le fichier source contient une ligne avec des données non valides, de sorte qu’une ligne est rejetée. Le code ci-dessus spécifie un maximum de 5 erreurs, donc une seule erreur n’aurait pas dû empêcher le chargement des lignes valides. Vous pouvez afficher les lignes qui ont été chargées en exécutant la requête suivante.

SELECT *

FROM dbo.StageCustomer

Sous l’onglet files, affichez le dossier racine de votre lac de données et vérifiez qu’un nouveau dossier nommé _rejectedrows a été créé (si vous ne voyez pas ce dossier, dans le menu Plus, sélectionnez Actualiser pour actualiser la vue).

Ouvrez le dossier _rejectedrows et le sous-dossier spécifique à la date et à l’heure qu’il contient, et notez que les fichiers portant des noms similaires à QID123_1_2. Error.txt et QID123_1_2. Row.Txt ont été créés. Vous pouvez cliquer avec le bouton droit sur chacun de ces fichiers et sélectionner Aperçu pour afficher les détails de l’erreur et la ligne qui a été rejetée. L’utilisation de tables intermédiaires vous permet de valider ou de transformer des données avant de les déplacer ou de les utiliser pour les ajouter ou les augmenter dans des tables de dimension existantes. L’instruction COPY fournit une technique simple mais performante que vous pouvez utiliser pour charger facilement des données à partir de fichiers dans un lac de données dans des tables intermédiaires et, comme vous l’avez vu, identifier et rediriger les lignes non valides.

Utilisation d’une instruction CREATE TABLE AS (CTAS)

Revenez au volet de script et remplacez le code qu’il contient par le code suivant :

CREATE TABLE dbo.DimProduct

WITH

(

DISTRIBUTION = HASH(ProductAltKey),

CLUSTERED COLUMNSTORE INDEX

)

SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS ProductKey,

ProductID AS ProductAltKey,

ProductName,

ProductCategory,

Color,

Size,

ListPrice,

Discontinued

FROM dbo.StageProduct;

Exécutez le script, qui crée une table nommée DimProduct à partir des données de produit intermédiaires qui utilise ProductAltKey comme clé de distribution de hachage et qui possède un index columnstore en cluster.

Utilisez la requête suivante pour afficher le contenu de la nouvelle table DimProduct :

SELECT ProductKey,

ProductAltKey,

ProductName,

ProductCategory,

Color,

Size,

ListPrice,

Discontinued

FROM dbo.DimProduct;

L’expression CREATE TABLE AS SELECT (CTAS) a plusieurs utilisations, notamment :

Redistribution de la clé de hachage d’une table pour l’aligner sur d’autres tables afin d’améliorer les performances des requêtes.

Attribution d’une clé de substitution à une table de transfert en fonction de valeurs existantes après l’exécution d’une analyse delta.

Création rapide de tables agrégées à des fins de rapport.

Combinez les instructions INSERT et UPDATE pour charger une table de dimensions qui change lentement

La table DimCustomer prend en charge les dimensions à changement lent de type 1 et de type 2, où les modifications de type 1 entraînent une mise à jour sur place d’une ligne existante et les modifications de type 2 entraînent la création d’une nouvelle ligne pour indiquer la dernière version d’une instance d’entité de dimension particulière. Le chargement de cette table nécessite une combinaison d’instructions INSERT (pour charger de nouveaux clients) et d’instructions UPDATE (pour appliquer des modifications de type 1 ou 2).

Dans le volet de requête, remplacez le code SQL existant par le code suivant :

INSERT INTO dbo.DimCustomer ([GeographyKey],[CustomerAlternateKey],[Title],[FirstName],[MiddleName],[LastName],[NameStyle],[BirthDate],[MaritalStatus],

[Suffix],[Gender],[EmailAddress],[YearlyIncome],[TotalChildren],[NumberChildrenAtHome],[EnglishEducation],[SpanishEducation],[FrenchEducation],

[EnglishOccupation],[SpanishOccupation],[FrenchOccupation],[HouseOwnerFlag],[NumberCarsOwned],[AddressLine1],[AddressLine2],[Phone],

[DateFirstPurchase],[CommuteDistance])

SELECT *

FROM dbo.StageCustomer AS stg

WHERE NOT EXISTS

(SELECT * FROM dbo.DimCustomer AS dim

WHERE dim.CustomerAlternateKey = stg.CustomerAlternateKey);

-- Type 1 updates (change le nom, email, ou téléphone sur place)

UPDATE dbo.DimCustomer

SET LastName = stg.LastName,

EmailAddress = stg.EmailAddress,

Phone = stg.Phone

FROM DimCustomer dim inner join StageCustomer stg

ON dim.CustomerAlternateKey = stg.CustomerAlternateKey

WHERE dim.LastName <> stg.LastName OR dim.EmailAddress <> stg.EmailAddress OR dim.Phone <> stg.Phone

-- Type 2 updates (des changements d'adresses déclanche une nouvelle entrée)

INSERT INTO dbo.DimCustomer

SELECT stg.GeographyKey,stg.CustomerAlternateKey,stg.Title,stg.FirstName,stg.MiddleName,stg.LastName,stg.NameStyle,stg.BirthDate,stg.MaritalStatus, stg.Suffix,stg.Gender,stg.EmailAddress,stg.YearlyIncome,stg.TotalChildren,stg.NumberChildrenAtHome,stg.EnglishEducation,stg.SpanishEducation,stg.FrenchEducation, stg.EnglishOccupation,stg.SpanishOccupation,stg.FrenchOccupation,stg.HouseOwnerFlag,stg.NumberCarsOwned,stg.AddressLine1,stg.AddressLine2,stg.Phone, stg.DateFirstPurchase,stg.CommuteDistance

FROM dbo.StageCustomer AS stg

JOIN dbo.DimCustomer AS dim

ON stg.CustomerAlternateKey = dim.CustomerAlternateKey

AND stg.AddressLine1 <> dim.AddressLine1;

Exécutez le script et examinez le résultat.

Effectuer l’optimisation post-chargement

Après avoir chargé de nouvelles données dans l’entrepôt de données, il est recommandé de reconstruire les index de table et de mettre à jour les statistiques sur les colonnes fréquemment interrogées.

Remplacez le code dans le volet de script par le code suivant :