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 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.
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
Préparation du chargement des données
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.
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
SELECT COUNT(1)
FROM dbo.StageProduct
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
Utilisons maintenant la même technique pour charger une autre table, cette fois en enregistrant toutes les erreurs qui peuvent se produire.
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
Utilisation d’une instruction CREATE TABLE AS (CTAS)
CREATE TABLE dbo.DimProduct
WITH
(
DISTRIBUTION = HASH(ProductAltKey),
CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS ProductKey,
ProductID AS ProductAltKey,
ProductName,
ProductCategory,
Color,
Size,
ListPrice,
Discontinued
FROM dbo.StageProduct;
SELECT ProductKey,
ProductAltKey,
ProductName,
ProductCategory,
Color,
Size,
ListPrice,
Discontinued
FROM dbo.DimProduct;
L’expression CREATE TABLE AS SELECT (CTAS) a plusieurs utilisations, notamment :
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).
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;
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.
ALTER INDEX ALL ON dbo.DimProduct REBUILD;
CREATE STATISTICS customergeo_stats ON dbo.DimCustomer (GeographyKey);
Supprimer des ressources Azure
Si vous avez terminé d’explorer Azure Synapse Analytique, vous devez supprimer les ressources que vous avez créées pour éviter des coûts Azure inutiles.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.