azure
Support formation Microsoft Azure
azure
Support formation Microsoft Azure
DP-203

icon picker
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
);
GO

Charger des tables de dimension

Utilisation d’une instruction CREATE TABLE AS (CTAS)

CREATE TABLE dbo.DimProduct
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
)
AS
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
)
AS
-- 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
GO
--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 Smith
999 Main St
Seattle
20190101
20230105
False
2996
Jo Smith
1234 9th Ave
Boston
20230106
True
There are no rows in this table
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.