La distribution des tables sur SQL Data Warehouse

SQL Data Warehouse a été abordé dans les grandes lignes dans un article précédent. Ce service PaaS permet d’avoir un système distribué pour la partie calcul et stockage sur un entrepôt de données décisionnel. Le but de ce post est de revenir plus en détail sur cette notion de distribution.

Pour rappel, une table peut être distribuée selon les modèles suivant :

  • Hachage
  • Tourniquet
  • Réplication

Idéalement, les tables d’historisation et de faits devraient être distribuées sur plusieurs nœuds de calculs pour exploiter au mieux les capacités de SQL Data Warehouse. De manière plus pragmatique, quelle option de distribution est à privilégier pour limiter les déplacements de données via le  DMS (Data Mouvement Service) pour la résolution d’une requête ? Voici quelques éléments de réponse.

Hachage

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - hash-distributed-table La distribution des tables sur SQL Data Warehouse

Une fonction de hash détermine la distribution des données sur les nœuds de calcul. Le Hachage est une bonne option, dans les cas suivants :

  • Tables volumineuses
  • Porter sur les colonnes qui font l’objet de jointures et/ou de groupements
  • Avoir une répartition uniforme des lignes, le cas échéant cela créé un goulot d’étranglement sur les nœuds où sont concentrées les données

L’éligibilité d’une colonne comme clé de hash doit se faire sur cette base :

  • Pas ou peu de valeurs NULL
  • Colonne de hash ne faisant pas l’objet de mise à jour
  • S’assurer que le nombre de valeurs distinct pour cette colonne de hash est supérieur ou égale à 60, soit le nombre de distributions
  • Répartition de cette colonne de hash non asymétrique
  • Colonne de hash utilisée dans les jointures et/ou groupements
  • Relever le nombre de tables portant aussi la colonne éligible comme clé de hash et les aligner autant que possible

Tourniquet

Les données sont réparties de manière égale sur différents nœuds de calcul, à tour de rôle. Le Touniquet est une bonne option, dans les cas suivants :

  • Tables de staging (dédiée au chargement)
  • Tables volumineuses non éligible au hachage
  • Dimensions dont le nombre de lignes ne dépasse pas un segment d’index columnstore (SQL Data Warehouse créé  un index clutered columnstore par défaut)

Les mouvements de données sont requis pour toutes les opérations mais ce modèle permet une distribution équitable.

Réplication

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - replicated-table La distribution des tables sur SQL Data Warehouse

Chaque nœud de calcul dispose d’une copie des données.

  • Petites tables (moins de 2Go)
  • Dimensions dont le nombre de lignes ne dépasse pas un segment d’index columnstore
  • Avec ou sans jointure, prédicats d’égalité ou d’inégalité

Ce modèle de distribution n’occasionne pas de déplacement de données. En revanche le stockage requis peut être relativement important, selon la formule qui suit : taille de la table répliquée multipliée par le nombre de nœuds de calcul.

Analyse d’une table pour le choix d’une distribution

Pour cette analyse, nous allons nous appuyer sur la base de données AdventureWorkDW et plus spécifiquement sur la table dbo.FactInternetSales. Pour rappel, le nombre de distribution est de 60.

Dans l’optique d’une distribution par hachage, commençons par lister les colonnes pour la table dbo.FactInternetSales. Seules les ProductKey et CustomerKey seront retenues.

Faisons une projection de la distribution par hachage avec la colonne ProductKey :

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - dist_customerkey La distribution des tables sur SQL Data Warehouse

Faisons maintenant une projection de la distribution par hachage avec la colonne CustomerKey :

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - dist_productkey La distribution des tables sur SQL Data Warehouse

On observe que dans le cas d’une distribution de hachage en passant par la colonne ProductKey, on a une distribution asymétrique des données contrairement à la colonne CustomerKey où les lignes sont réparties beaucoup plus équitablement.

Autre pendant à prendre en compte pour une distribution de hachage, c’est l’alignement des tables annexes.

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - distrib_align_table La distribution des tables sur SQL Data Warehouse

Et enfin qu’aurait donner une simple distribution par tourniquet ? Une répartition garantie uniforme sur la base des 60 distributions.

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - dist_tourniquet La distribution des tables sur SQL Data Warehouse

Il n’y a pas de conclusion à tirer, l’objectif de cette démonstration étant de donner quelques éléments d’analyse pour faire un choix de distribution pertinent.

Informations de distribution

Cette requête permet de connaitre le type de distribution sur chaque table :

Dans un premier temps, la création de la vue dbo.vTablesSizes devrait nous permettre d’extraire de manière moins verbeuse d’autres informations…

Telles que le nombre de lignes présentes dans chaque distribution pour la table dbo.FactInternetSales :

Et en bonus, les informations de volumétrie basiques :

 

Auteur

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - cartoon_250px-150x150 La distribution des tables sur SQL Data Warehouse
Sarah Bessard
Experte SQL Server Prod/Etude avec un bonus sur la BI, speaker aux SQL Saturday et Journées SQL Server et enfin formatrice Orsys, je dispose d'une vision d'ensemble des systèmes d'information, d'une appétence pour l'industrialisation et d'un souci permanent de la performance.
Posts liés

Leave a Reply

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

error: Contenu protégé !