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.
CREATETABLE dbo.StageProduct
(
ProductID NVARCHAR(10)NOTNULL,
ProductName NVARCHAR(200)NOTNULL,
ProductCategory NVARCHAR(200)NOTNULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMALNOTNULL,
Discontinued BITNOTNULL
)
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.
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)NOTNULL,
ProductName NVARCHAR(10)NOTNULL,
...
)
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)
CREATETABLE dbo.DimProduct
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
)
AS
SELECTROW_NUMBER()OVER(ORDERBY 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.
CREATETABLE 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
UNIONALL
-- Existing rows
SELECT dim.ProductBusinessKey,
dim.ProductName,
dim.ProductCategory,
dim.Color,
dim.Size,
dim.ListPrice,
dim.Discontinued
FROM dbo.DimProduct AS dim
WHERENOTEXISTS
(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).
INSERTINTO 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 grainselon lequel vous souhaitez agréger les données au fil du temps.
-- Create a temporary table for the dates we need
CREATETABLE#TmpStageDate (DateVal DATE NOT NULL)
-- Populate the temp table with a range of dates
DECLARE@StartDateDATE
DECLARE@EndDateDATE
SET@StartDate='2019-01-01'
SET@EndDate='2023-12-31'
DECLARE@LoopDate=@StartDate
WHILE@LoopDate<=@EndDate
BEGIN
INSERTINTO#TmpStageDate VALUES
(
@LoopDate
)
SET@LoopDate= DATEADD(dd,1,@LoopDate)
END
-- Insert the dates and calculated attributes into the dimension table
INSERTINTO dbo.DimDate
SELECT CAST(CONVERT(VARCHAR(8), DateVal,112)asINT),-- date key
DateVal,--date alt key
Day(DateVal)-- day number of month
--, other derived temporal fields as required
FROM#TmpStageDate
GO
--Drop temporary table
DROPTABLE#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