Création de tables d’entrepôt de données
Maintenant que vous comprenez les principes architecturaux de base d’un schéma d’entrepôt de données relationnel, nous allons voir comment créer un entrepôt de données.
Création d’un pool SQL dédié
Pour créer un entrepôt de données relationnel dans Azure Synapse Analytics, vous devez créer un pool SQL dédié. La façon la plus simple de procéder dans un espace de travail Azure Synapse Analytics existant consiste à utiliser la page Gérer d’Azure Synapse Studio :
Lors de l’approvisionnement d’un pool SQL dédié, vous pouvez spécifier les paramètres de configuration suivants :
- Le nom unique du pool SQL dédié
- Le niveau de performances du pool SQL (de DW100c à DW30000c) qui détermine le coût par heure du pool en cours d’exécution
- Point de départ : un pool vide ou une base de données existante restaurée à partir d’une sauvegarde
- Classement du pool SQL, qui détermine les règles d’ordre de tri et de comparaison de chaînes de la base de données (non modifiable une fois créé)
Une fois que vous avez créé un pool SQL dédié, vous pouvez contrôler son état d’exécution sur la page Gérer de Synapse Studio et l’interrompre lorsqu’il n’est pas nécessaire pour éviter les coûts inutiles.
Lorsque le pool est en cours d’exécution, vous pouvez l’explorer sur la page Données et créer des scripts SQL à exécuter en son sein.
Éléments à prendre en compte pour la création de tables
Pour créer des tables dans le pool SQL dédié, il faut utiliser l’instruction Transact-SQL CREATE TABLE (ou parfois CREATE EXTERNAL TABLE). Les options spécifiques dépendent du type de table créé :
- Tables de faits
- Tables de dimension
- Tables de mise en lots
Notes
L’entrepôt de données est composé de tables de faits et de dimension, comme indiqué précédemment. Les tables de mise en lots sont souvent utilisées dans le cadre du processus de chargement de l’entrepôt de données pour ingérer des données provenant de systèmes sources.
Lors de la conception d’un modèle de schéma en étoile pour les jeux de données de taille réduite ou moyenne, vous pouvez utiliser votre base de données préférée, par exemple Azure SQL. Pour les jeux de données plus volumineux, il peut s’avérer utile d’implémenter votre entrepôt de données dans Azure Synapse Analytics au lieu de SQL Server. Il est important de comprendre certaines différences clés lors de la création de tables dans Synapse Analytics.
Contraintes d’intégrité des données
Les pools SQL dédiés dans Synapse Analytics ne prennent pas en charge les contraintes de clé étrangère ni d’unicité que l’on retrouve dans d’autres systèmes de bases de données relationnelles comme SQL Server. Par conséquent, les travaux utilisés pour charger des données doivent maintenir le caractère unique et l’intégrité référentielle des clés, sans compter pour cela sur les définitions de table de la base de données.
Conseil
Pour plus d’informations sur les contraintes des pools SQL dédiés Azure Synapse Analytics, consultez Clé primaire, clé étrangère et clé unique avec un pool SQL dédié dans Azure Synapse Analytics.
Index
Bien que les pools SQL dédiés Synapse Analytics prennent en charge les index cluster comme dans SQL Server, le type d’index par défaut est columnstore cluster. Ce type d’index offre un avantage significatif en matière de performances lors de l’interrogation de grandes quantités de données dans un schéma d’entrepôt de données classique. Il doit être utilisé dans la mesure du possible. Il arrive toutefois que certaines tables incluent des types de données qui ne peuvent pas se trouver dans un index columnstore cluster (par exemple VARBINARY(MAX)), auquel cas un index cluster peut être employé à la place.
Conseil
Pour plus d’informations sur l’indexation dans des pools SQL dédiés Azure Synapse Analytics, consultez Index sur les tables de pools SQL dédiés dans Azure Synapse Analytics.
Distribution
Les pools SQL dédiés Azure Synapse Analytics utilisent une architecture de traitement massivement parallèle (MPP, Massively Parallel Processing), par opposition à l’architecture de multitraitement symétrique (SMP, Symmetric Multi-Processing) utilisée dans la plupart des systèmes de base de données OLTP. Dans un système MPP, les données d’une table sont distribuées pour être traitées sur un pool de nœuds. Synapse Analytics prend en charge les types de distribution suivants :
- Hachage : une valeur de hachage déterministe est calculée pour la colonne spécifiée et utilisée pour affecter la ligne à un nœud de calcul.
- Tourniquet (round-robin) : les lignes sont distribuées uniformément sur tous les nœuds de calcul.
- Réplication : une copie de la table est stockée sur chaque nœud de calcul.
C’est souvent le type de la table qui détermine son option de distribution.
| Type de la table | Option de distribution recommandée |
|---|---|
| Dimension | Utilisez la distribution répliquée pour les petites tables afin d’éviter le brassage des données lors de la jointure à des tables de faits distribuées. Si les tables sont trop volumineuses pour être stockées sur chaque nœud de calcul, utilisez la distribution par hachage. |
| Fact | Utilisez la distribution de hachage avec l’index columnstore cluster pour distribuer des tables de faits entre les nœuds de calcul. |
| Préproduction | Utilisez la distribution tourniquet (round-robin) pour les tables de mise en lots afin de répartir uniformément les données entre les nœuds de calcul. |
Conseil
Pour plus d’informations sur les stratégies de distribution des tables dans Azure Synapse Analytics, consultez Aide pour concevoir des tables distribuées à l’aide d’un pool SQL dédié dans Azure Synapse Analytics.
Création de tables de dimension
Lorsque vous créez une table de dimension, veillez à ce que la définition de table inclue des clés de substitution et des clés secondaires, ainsi que des colonnes correspondant aux attributs de la dimension à utiliser pour regrouper les agrégations. Il est souvent plus simple de se servir d’une colonne IDENTITY pour générer automatiquement une clé de substitution incrémentielle (sinon, vous devez générer des clés uniques chaque fois que vous chargez des données). L’exemple suivant montre une instruction CREATE TABLE pour une table de dimension DimCustomer hypothétique.
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Notes
Si vous le souhaitez, vous pouvez créer un schéma spécifique servant d’espace de noms pour vos tables. Dans cet exemple est utilisé le schéma par défaut, dbo.
Si vous envisagez d’utiliser un schéma en flocon (dans lequel les tables de dimension sont liées les unes aux autres), incluez la clé de la dimension parent dans la définition de la table de dimension enfant. Par exemple, le code SQL suivant permet de déplacer les informations relatives à l’adresse géographique de la table DimCustomer dans une table de dimension DimGeography distincte :
CREATE TABLE dbo.DimGeography
(
GeographyKey INT IDENTITY NOT NULL,
GeographyAlternateKey NVARCHAR(10) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
GeographyKey INT NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Tables de dimension de temps
La plupart des entrepôts de données incluent une table de dimension de temps qui permet d’agréger des données par plusieurs niveaux hiérarchiques d’intervalle de temps. À titre d’illustration, l’exemple suivant crée une table DimDate dotée d’attributs liés à des dates spécifiques.
CREATE TABLE dbo.DimDate
(
DateKey INT NOT NULL,
DateAltKey DATETIME NOT NULL,
DayOfMonth INT NOT NULL,
DayOfWeek INT NOT NULL,
DayName NVARCHAR(15) NOT NULL,
MonthOfYear INT NOT NULL,
MonthName NVARCHAR(15) NOT NULL,
CalendarQuarter INT NOT NULL,
CalendarYear INT NOT NULL,
FiscalQuarter INT NOT NULL,
FiscalYear INT NOT NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Conseil
Un modèle courant lors de la création d’une table de dimension pour les dates consiste à utiliser la date numérique au format JJMMAAAA ou AAAAMMJJ comme clé de substitution entière, et la date du type de données DATE ou DATETIME comme clé secondaire.
Création de tables de faits
Les tables de faits incluent les clés de chacune des dimensions auxquelles elles sont associées, ainsi que les attributs et les mesures numériques des événements et observations spécifiques à analyser.
L’exemple de code suivant crée une table de faits hypothétique nommée FactSales, associée à plusieurs dimensions par le biais de colonnes clés (date, client, produit et magasin).
CREATE TABLE dbo.FactSales
(
OrderDateKey INT NOT NULL,
CustomerKey INT NOT NULL,
ProductKey INT NOT NULL,
StoreKey INT NOT NULL,
OrderNumber NVARCHAR(10) NOT NULL,
OrderLineItem INT NOT NULL,
OrderQuantity SMALLINT NOT NULL,
UnitPrice DECIMAL NOT NULL,
Discount DECIMAL NOT NULL,
Tax DECIMAL NOT NULL,
SalesAmount DECIMAL NOT NULL
)
WITH
(
DISTRIBUTION = HASH(OrderNumber),
CLUSTERED COLUMNSTORE INDEX
);
Création de tables de mise en lots
Les tables de mise en lots sont utilisées comme stockage temporaire pour les données à mesure qu’elles sont chargées dans l’entrepôt de données. Un modèle classique consiste à structurer la table de façon à rendre aussi efficace que possible l’ingestion de données de leur source externe (souvent des fichiers d’un lac de données) vers la base de données relationnelle, puis à utiliser des instructions SQL pour charger les données des tables de mise en lots dans les tables de dimension et de fait.
L’exemple de code suivant crée une table de mise en lots pour les données de produit qui seront finalement chargées dans une table de dimension :
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 tables externes
Dans certains cas, si les données à charger se trouvent dans des fichiers dotés d’une structure appropriée, il peut se révéler plus efficace de créer des tables externes qui font référence à l’emplacement du fichier. De cette façon, les données peuvent être lues directement dans les fichiers sources au lieu d’être chargées dans le magasin relationnel. L’exemple suivant montre comment créer une table externe qui fait référence à des fichiers situés dans le lac de données associé à l’espace de travail Synapse :
-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO
-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
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
(
DATA_SOURCE = StagedFiles,
LOCATION = 'products/*.parquet',
FILE_FORMAT = ParquetFormat
);
GO
Notes
Pour plus d’informations sur l’utilisation de tables externes, consultez Utilisation de tables externes avec Synapse SQL dans la documentation d’Azure Synapse Analytics.