Support formation Microsoft Azure

Support formation Microsoft Azure

DP-203

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

Gonzague Ducos

Chargement des données est effectué dans le cadre d’un processus de traitement par lots périodique (quotidien, hebdomadaire ou mensuel).

Création de tables de mise en lots

Créer des schémas distincts pour les tables de mise en lots avec un nom significatif, par exemple lots, pour que les architectes et les utilisateurs comprennent la finalité du schéma.

CREATE TABLE dbo.StageProduct

(

ProductID NVARCHAR(10) NOT NULL,

ProductName NVARCHAR(200) NOT NULL,

ProductCategory NVARCHAR(200) NOT NULL,

Color NVARCHAR(10),

Size NVARCHAR(10),

ListPrice DECIMAL NOT NULL,

Discontinued BIT NOT NULL

)

WITH

(

DISTRIBUTION = ROUND_ROBIN,

CLUSTERED COLUMNSTORE INDEX

);

Utilisation de la commande COPY

Approche recommandée pour charger des tables de mise en lots en raison de son débit haute performance.

COPY INTO dbo.StageProduct

(ProductID, ProductName, ...)

FROM 'https://mydatalake.../data/products*.parquet'

WITH

(

FILE_TYPE = 'PARQUET',

MAXERRORS = 0,

IDENTITY_INSERT = 'OFF'

);

Utilisation de tables externes

Si les données à charger sont stockées dans des fichiers dotés d’une structure appropriée, il peut s’avérer plus efficace de créer des tables externes qui référencent l’emplacement des fichiers.

CREATE EXTERNAL TABLE dbo.ExternalStageProduct

(

ProductID NVARCHAR(10) NOT NULL,

ProductName NVARCHAR(10) NOT NULL,

...

)

WITH

(

DATE_SOURCE = StagedFiles,

LOCATION = 'folder_name/*.parquet',

FILE_FORMAT = ParquetFormat

);

Charger des tables de dimension

Utilisation d’une instruction CREATE TABLE AS (CTAS)

CREATE TABLE dbo.DimProduct

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

)

SELECT ROW_NUMBER() OVER(ORDER BY ProdID) AS ProdKey,

ProdID as ProdAltKey,

ProductName,

ProductCategory,

Color,

Size,

ListPrice,

Discontinued

FROM dbo.StageProduct;

Est pas possible d’utiliser IDENTITY afin de générer une valeur entière unique pour la clé de substitution dans CTAS.

CREATE TABLE dbo.DimProductUpsert

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

)

-- New or updated rows

SELECT stg.ProductID AS ProductBusinessKey,

stg.ProductName,

stg.ProductCategory,

stg.Color,

stg.Size,

stg.ListPrice,

stg.Discontinued

FROM dbo.StageProduct AS stg

UNION ALL

-- Existing rows

SELECT dim.ProductBusinessKey,

dim.ProductName,

dim.ProductCategory,

dim.Color,

dim.Size,

dim.ListPrice,

dim.Discontinued

FROM dbo.DimProduct AS dim

WHERE NOT EXISTS

( SELECT *

FROM dbo.StageProduct AS stg

WHERE stg.ProductId = dim.ProductBusinessKey

);

RENAME OBJECT dbo.DimProduct TO DimProductArchive;

RENAME OBJECT dbo.DimProductUpsert TO DimProduct;

Technique efficace pour fusionner les données de dimension nouvelles et déjà existantes, l’absence de prise en charge des colonnes IDENTITY signifie qu’il est difficile de générer une clé de substitution.

Utilisation d’une instruction INSERT

Approche plus simple que CTAS, mais fonctionne si les données mises en lots ne contiennent que des enregistrements de nouvelles entités de dimension (et non des mises à jour d’entités existantes).

INSERT INTO dbo.DimCustomer

SELECT CustomerNo AS CustAltKey,

CustomerName,

EmailAddress,

Phone,

StreetAddress,

City,

PostalCode,

CountryRegion

FROM dbo.StageCustomers

Charger des tables de dimension de temps

Tables de dimension de temps : stockent un enregistrement pour chaque intervalle de temps en fonction du grain selon lequel vous souhaitez agréger les données au fil du temps.

-- Create a temporary table for the dates we need

CREATE TABLE #TmpStageDate (DateVal DATE NOT NULL)

-- Populate the temp table with a range of dates

DECLARE @StartDate DATE

DECLARE @EndDate DATE

SET @StartDate = '2019-01-01'

SET @EndDate = '2023-12-31'

DECLARE @LoopDate = @StartDate

WHILE @LoopDate <= @EndDate

BEGIN

INSERT INTO #TmpStageDate VALUES

(

@LoopDate

)

SET @LoopDate = DATEADD(dd, 1, @LoopDate)

END

-- Insert the dates and calculated attributes into the dimension table

INSERT INTO dbo.DimDate

SELECT CAST(CONVERT(VARCHAR(8), DateVal, 112) as INT), -- date key

DateVal, --date alt key

Day(DateVal) -- day number of month

--, other derived temporal fields as required

FROM #TmpStageDate

--Drop temporary table

DROP TABLE #TmpStageDate

L’écriture du script correspondant en SQL peut prendre du temps dans un pool SQL dédié. Il peut être plus efficace de préparer les données dans Microsoft Excel ou un script externe, et de les importer à l’aide de l’instruction COPY.

Charger des dimensions variables à évolution lente

Types de dimension variable à évolution lente

Type 0

Les données de dimension de type 0 ne peuvent pas être changées. Toutes les tentatives de modification échouent.

DateKey

DateAltKey

Jour

Month

Année

20230101

01-01-2023

Dimanche

Janvier

2023

There are no rows in this table

⁠

Type 1

L’enregistrement de dimension est mis à jour sur place.

StoreKey

StoreAltKey

StoreName

123

EH199J

Magasin High Street Magasin Town Central

There are no rows in this table

⁠

Type 2

Changement apporté à une dimension entraîne l’insertion d’une nouvelle ligne de dimension

CustomerKey

CustomerAltKey

Nom

Adresse

City

DateFrom

DateTo

IsCurrent

1211

⁠

jo@contoso.com⁠

⁠

Jo Smith

999 Main St

Seattle

20190101

20230105

False

2996

⁠

jo@contoso.com⁠

⁠

Jo Smith

1234 9th Ave

Boston

20230106

True

There are no rows in this table

⁠

Combinaison des instructions INSERT et UPDATE

Implémenter des mises à jour de type 1 et de type 2

Une clé de substitution incrémentielle basée sur une colonne IDENTITY identifie chaque ligne + une clé de substitution ayant la valeur la plus élevée pour une clé secondaire donnée indique l’instance la plus récente ou « actuelle » de l’entité de dimension associée à cette clé secondaire.

Inclure :

une colonne booléenne pour signaler l’instance active actuelle d’une dimension variable

OU utiliser des champs DateTime pour désigner les périodes actives de chaque version de l’instance de dimension.

Dans ces cas, la logique d’une modification de type 2 doit inclure une opération INSERT d’une nouvelle ligne de dimension et une opération UPDATE permettant de marquer la ligne actuelle comme inactive.

-- New Customers

INSERT INTO dbo.DimCustomer

SELECT stg.*

FROM dbo.StageCustomers AS stg

WHERE NOT EXISTS

(SELECT * FROM dbo.DimCustomer AS dim

WHERE dim.CustomerAltKey = stg.CustNo)

-- Type 1 updates (name)

UPDATE dbo.DimCustomer

SET CustomerName = stg.CustomerName

FROM dbo.StageCustomers AS stg

WHERE dbo.DimCustomer.CustomerAltKey = stg.CustomerNo;