Pools SQL dédiés dans Azure Synapse Analytics
Comme toutes les bases de données relationnelles, un entrepôt de données contient des tables dans lesquelles les données que vous souhaitez analyser sont stockées.
Ces tables sont organisées dans un schéma optimisé pour la modélisation multidimensionnelle, dans lequel des mesures numériques associées aux événements, appelées faits, peuvent être agrégées par les attributs des entités associées à plusieurs dimensions.
Concevoir un schéma d’entrepôt de données
Tables d’un entrepôt de données
Modèle courant pour les entrepôts de données relationnelles : définir un schéma avec 2 types de tables :
Tables de dimension
Décrivent les entités métier Contiennent des colonnes pour les attributs d’une entité Contient une colonne “clé unique” qui identifie de manière unique chaque ligne de la table En fait, on aura 2 clés :
Une clé de substitution spécifique à l’entrepôt de données et qui identifie de manière unique chaque ligne de la table de dimension dans l’entrepôt de données (généralement, un nombre entier incrémenté). Une clé secondaire, souvent une clé naturelle ou métier, utilisée pour identifier une instance spécifique d’une entité dans le système source transactionnel à partir duquel l’enregistrement d’entité provient (par exemple, un code de produit ou un ID client). Pourquoi utiliser deux clés ?
Si plusieurs sources, entrainerait des doublons. Clés numériques fonctionnent généralement mieux dans les requêtes Les attributs d’entités peuvent changer au fil du temps, on veut conserver un enregistrement pour chaque instance d’une entité à plusieurs instants Inclure une table de dimension qui représente le temps.
La granularité la plus faible (appelée grain) d’une dimension de temps peut représenter des heures (à l’heure, à la seconde, à la milliseconde, à la nanoseconde, ou encore moins), ou des dates Tables de faits
Stockent des détails ou des événements Contient des colonnes pour les valeurs numériques qui peuvent être agrégées par dimensions Contient des colonnes clés qui référencent des clés uniques dans les tables de dimension associées Conceptions de schémas d’entrepôts de données
Dans une base de donnée transactionnelle utilisée dans les applications métier, les données sont normalisées pour réduire la duplication. Dans un entrepôt de données, les données de dimension sont généralement dénormalisées pour réduire le nombre de jointures requises pour interroger les données. Un entrepôt de données est organisé comme un schéma en étoile, dans lequel une table de faits est directement liée aux tables de dimension.
Les attributs d’une entité peuvent être utilisés pour agréger des mesures dans des tables de faits sur plusieurs niveaux hiérarchiques.
Lorsqu’une entité comporte un grand nombre de niveaux d’attribut hiérarchique ou que certains attributs peuvent être partagés par plusieurs dimensions (par exemple, les clients et les magasins ont une adresse géographique), on applique une normalisation aux tables de dimension et on crée un schéma en flocon.
Création de tables d’entrepôt de données
Création d’un pool SQL dédié
Espace de travail Azure Synapse Analytics > Gérer 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éé) Éléments à prendre en compte pour la création de tables
Pour créer des tables : CREATE TABLE (ou parfois CREATE EXTERNAL TABLE). Tables de mise en lots : 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 : 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. Voici les différences. 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 (FOREIGN KEY) ni d’unicité (UNIQUE) : 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.
Index
Pools SQL dédiés Synapse Analytics prend en charge les index cluster comme dans SQL Server.
Pour les pools SQL dédiés Synapse Analytics, le type d’index par défaut est columnstore cluster car avantage significatif en matière de performances lors de l’interrogation de grandes quantités de données.
Distribution
Architecture :
Pools SQL dédiés Azure Synapse Analytics utilisent une Systèmes de base de données OLTP : architecture de multitraitement symétrique (SMP, Symmetric Multi-Processing). 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.
Création de tables de dimension
Inclure clés de substitution et des clés secondaires + colonnes correspondant aux attributs de la dimension. Utiliser IDENTITY pour gérer automatiquement une clé de substitution incrémentielle. 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
);
Si schéma en flocon, inclure la clé de la dimension parent dans la définition de la table de dimension enfant :
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
Clés :
JJMMAAAA ou AAAAMMJJ comme clé de substitution entière Date du type de données DATE ou DATETIME comme clé secondaire 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
);
Création de tables de faits
Incluent :
les clés de chacune des dimensions auxquelles elles sont associées les attributs et les mesures numériques des événements et observations spécifiques à analyser 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.
Modèle classique :
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 Utiliser des instructions SQL pour charger les données des tables de mise en lots dans les tables de dimension et de fait. 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, table externe :
-- 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,