Constructions ponctuelles (PIT) et Join-Trees

Constructions ponctuelles (PIT) et Join-Trees


Snowflake continue d’établir la norme pour données dans le cloud en supprimant la nécessité d’effectuer des tâches de maintenance sur votre plateforme de données et en vous donnant la liberté de choisir votre méthodologie de modèle de données pour le cloud. Vous attendez les mêmes capacités relationnelles pour votre modèle de données que n’importe quelle autre plate-forme et Snowflake vous aide à les fournir.

Les modèles Data Vault ne sont pas conçus pour être utilisés par des outils de Business Intelligence (BI), ils sont conçus pour automatisation et agilité tout en permettant auditabilité; apporter des modifications à un modèle Data Vault ne détruit pas le modèle existant ; au contraire, il l’augmente. Afin de simplifier et d’améliorer l’interrogation d’un modèle Data Vault, nous expliquerons pourquoi vous pourriez envisager de créer des tables Point-in-Time (PIT) et Bridge.

Catalogue de blogs,

  1. Magasin immuable, dates de fin virtuelles
  2. Tableaux de bord Snowsight pour Data Vault
  3. Constructions ponctuelles et arborescences de jointure
  4. Interroger de très GRANDES tables satellites
  5. Flux et tâches sur les vues
  6. INSERT multi-table conditionnel et où l’utiliser
  7. Politiques d’accès aux lignes + Multi-Tenancy
  8. Verrouillage du moyeu sur Snowflake
  9. Entrepôts virtuels et rétrofacturation

Rappel des types de table Data Vault :

Diagramme, texte Description générée automatiquement

Et maintenant, nous allons présenter deux Nouveau types de table à nos séries qui sont ne pas nécessairement des tables Data Vault, mais sont des constructions conçues pour extraire efficacement les données de Data Vault. Ces tableaux n’offrent pas la agilité, automatisationet historique d’audit nous attendons de Data Vault ; plutôt, ces tables sont jetable et conçu pour soutenir magasins d’information sous forme de vues.

U7nJTDnhsCUIrLl cJsIRIH6EE3kAXcEGAiJOKiPzn1Vc 5hy58pMAOt NAdc2y3v198DzZUEHhyBczdaj7P1jcimGHl7Tj9Acw 1wskBPIw hkIRy3LP4SMpRHRgA1pDgstTTIibG FViG0GvX95w

Nous allons étendre l’animation d’orchestration que nous avons étendue dans le précédent article de blog avec une table PIT et des magasins d’informations pris en charge

nsZKwoGObsyIZEcUqcILCI6I96r8BPXtdSwwiqYd6FerQu4mSXOxrWyYvzQoNOM1KyzmrOGVv8yeGh10RJP4AvEtSaE7AEerAwyFu4ZrsdbG6tRczd52 HeRVb8eoIW9zsvedsL nKo

Automatisation et orchestration du coffre-fort de données

Plus de tableaux ? Pourquoi sont-ils nécessaires ?

Compte tenu de tous les attributs nécessaires d’un objet métier ou unité de travailles tables satellites capturent les données de l’objet métier Etat ou la relation Etat. Cependant, une table satellite de voûte brute est la source spécifique, et une table hub peut avoir plusieurs sources, chaque système source automatisant les processus métier dans lesquels le modèle Data Vault stocke les contexte historique. Pour joindre des données d’état provenant de plusieurs sources autour du concentrateur ou de la table de liens, l’utilisateur est forcé pour écrire une logique complexe pour unifier les données afin de servir la couche du magasin d’informations. Si cette logique SQL est déployée une fois sur le Data Vault, alors la logique sera (bien qu’un modèle reproductible) exécutée à chaque requête sur cette vue. C’est là qu’un tableau d’aide à la requête tel qu’un PIT est nécessaire, et il est nécessaire pour deux raisons :

  • Simplifier la requête SQL entre les tables satellites en présentant les clés des tables satellites pertinentes pour instantané à un moment donné pour le période de déclaration obligatoire.
  • Utiliser la technologie de la plate-forme pour réaliser ce qu’on appelle un « requête optimisée en étoile” en informant la plateforme que le tableau PIT doit être traité comme un fait table, tandis que les tables satellites sont traitées comme dimension les tables.

Explorons-les un peu plus en détail.

Simplification des requêtes SQL
v2krFQ2rpLFajnwWX2k7L 6lCj9nL73Gm52Fk6J3QS3mIrb6qY9 cljImifd8gzvGXXb8l2ky9T M5KY3

Modèle Data Vault avec une table d’assistance aux requêtes

La clé (pardonnez le jeu de mots) pour comprendre l’implémentation d’une table PIT pour interroger un Data Vault est de comparer l’implémentation à la façon dont on concevrait des faits et des dimensions dans un Kimball maquette. Dans la modélisation Kimball, si un enregistrement dans une table de dimension n’est pas disponible pour un enregistrement de faits, un enregistrement de dimension par défaut est référencé à la place (par exemple, référence à un enregistrement arrivé en retard).

Le même principe de conception est prévu avec une table PIT—chaque table satellite contient un enregistrement fantôme qui n’a aucune valeur et n’est lié à aucun objet métier ou à quoi que ce soit d’autre de valeur. Au lieu de cela, l’enregistrement fantôme est utilisé pour compléter une équi-jointure pour une table PIT. En d’autres termes, si pour un instantané ponctuel, l’enregistrement dans une table satellite adjacente n’existe pas encore, la table PIT référencera cet enregistrement fantôme pour cet objet métier jusqu’à ce qu’un enregistrement commence à apparaître pour ce satellite. pour l’objet métier. Cela ne veut pas dire que l’enregistrement dans Data Vault arrive en retard ; à la place, pour cette date d’instantané, il n’y a pas de contexte historique pour cet objet métier encore. Un PIT peut être utilisé à la place d’une table hub ou d’une table de liens selon les données que vous devez unifier.

Si l’enregistrement fantôme n’était pas dans la conception, une requête sur le Data Vault s’appuierait sur un mélange de jointures internes, gauches et droites SQL, ce qui serait tout simplement trop complexe pour résoudre une requête simple ! La même conception s’applique aux faits et aux dimensions, l’interrogation des faits et des dimensions doit s’appuyer sur une dimension de date, une table de faits centrale et d’autres dimensions pertinentes se joignant via une seule clé de jointure entre des tables ressemblant à une étoile. Alors que dans un modèle Kimball, le filtre de date est appliqué uniquement à la dimension de date, le filtre de date dans Data Vault est la date de l’instantané dans la table PIT elle-même.

Pour illustrer comment ce qui précède est implémenté dans une table PIT, supposons que nous n’avons que une objet métier et ajouter en conséquence des enregistrements à deux satellites contributeurs pour cette clé métier. Notez que le premier enregistrement dans le satellite est l’enregistrement fantôme (« 000 »); il n’appartient à aucun objet métier mais est là pour compléter l’équi-jointure pour tout objets métiers.

QEukXhrXulJIvtTdvpsS 6jDl12ookVO4XwPmcOglIPXdQY7zpgqizcit7dC2S1eRtiguLG11M4SF9Doh3qCEg 7wfs6k5cAnwHCJVEW4ZyKk3UdxUp0UwIWeeVIUES81B

Instantané des clés de hachage et des dates de chargement

La table PIT agit comme une carte de hachage, avec des points de référence indiquant où trouver l’enregistrement approprié dans chaque table satellite et quelles dates de chargement sont applicables pour cette date d’instantané. L’utilisation d’un résumé de hachage comme clé de distribution est très pratique pour un système qui en dépend.

Les faits et les dimensions utilisent un substitut séquence key au lieu de ce que nous avons fait avec la construction PIT illustrée ci-dessus, en ce sens que nous avons utilisé des clés de hachage de substitution et des horodatages de date de chargement. Mais, en fait, nous pouvons obtenir le même résultat dans Data Vault en définissant une colonne d’auto-incrémentation lors de la création de table pour chaque satellite et en utilisant cette colonne temporelle dans la table PIT à la place.

Reprenons l’illustration ci-dessus, mais utilisons plutôt la clé de séquence de substitution.

o4JD8CQgp7l1hteIRAuxdWU5q9SId9crdrTCMrBGREnzehMR9g3IAbZ7KC9Ryvvbpnmp8Zb9Twcn F Oh

Instantané des identifiants de séquence

Une clé de séquence unique équivaudra à combiner des clés de hachage avec des dates de chargement, l’avantage de cette approche mise à jour est que nous utilisons une seule colonne d’entiers à la place et donc les jointures seront plus rapides lors du regroupement de grandes tables satellites dans la condition de jointure. La construction de la table PIT devient une table de numéros de séquence uniquement, ponctuelle, car les identifiants de séquence eux-mêmes ont une signification temporelle. Peu importe si une table satellite environnante n’a pas encore d’enregistrement pour cet objet métier, la même condition d’équi-jointure est utilisée. Voici un exemple de pseudo-code :

select pit.BusinessKey
         , sat1.attributes…
         , sat2.attributes…
         , sat3.attributes…
         , sat4.attributes…
from pit_cardaccount_daily pit
inner join sat_card_masterfile sat1
on pit.sat_card_masterfile_sid = sat1.dv_sid
inner join sat_card_transaction sat2
on pit. sat_card_transaction_sid = sat2.dv_sid
inner join sat_card_balancecategories sat3
on pit. sat_card_balancecategories_sid = sat3.dv_sid
inner join sat_bv_cardsummary sat4
on pit. sat_bv_cardsummary_sid = sat4.dv_sid

Ensuite, comment Snowflake peut optimiser vos requêtes PIT.

Requête optimisée en étoile

Étant donné que les statistiques de métadonnées sur tous les objets Snowflake sont toujours à jour, l’optimiseur de requêtes de Snowflake reconnaîtra immédiatement la table PIT centrale dans une jointure multitable comme la plus grande table. Lorsqu’une telle requête est reconnue, l’optimiseur utilise une stratégie de jointure SQL appelée Arborescence de jointure droite-profond. Voici comment ça fonctionne:

Fj20Mea6Oj1kXRp5TrwYN Vu hnilv9pAJAB MTIEW3Zeg Qh8P0UkBj2opmFVBis7PC C49OmW0PmTc5Fb6xFaa9rha47wLJWjQ 9HdJuZj3Iu5 7GFIUp11Y eijaZe49GrraRFMrXwFqPlsDZiQ

1) Profond à gauche, 2) Profond à droite, 3) Arbre touffu

  1. L’arborescence de jointure profonde à gauche utilise un boucle imbriquée pour joindre des tables. Le résultat de la première jointure est utilisé comme table de base pour sonder la table suivante, qui produit une table intermédiaire interne jusqu’à ce que toutes les conditions de jointure soient satisfaites. Pour un mélange de types de jointure et de tailles variables et sans table centrale pour agir comme pièce maîtresse de la requête de jointure, ce type de jointure peut être plus optimal (voir bit.ly/3rQLz57).
  2. L’arborescence de jointure profonde à droite utilise un jointure de hachage pour joindre des tables. Les tables de hachage internes sont créées dans un construire phase qui sert à sonde la grande table centrale pour les matchs. Cette jointure a besoin un prédicat d’équi-jointure (voir bit.ly/3EMwoPS).
  3. Les arbres touffus contiennent un mélange de ce qui précède.

Les stratégies de jointure nécessitent que les statistiques de métadonnées soient à jour pour que l’optimiseur décide quelle est la meilleure stratégie, et puisque les statistiques d’objet de Snowflake sont toujours à jour, vous pouvez être sûr que Snowflake choisira la bonne stratégie de jointure pour votre requête. La façon dont vous identifiez qu’une arborescence de jointure profonde à droite s’est produite consiste à observer la forme de votre plan de requête dans le profileur de requête dans Snowsight.

nKOFoyqZDdFL4DZdJEoP9RfkW3rDMTOIYrNGt0wPh3JkjKiHR aWIMXJjjeGbVZWtNaJ85GLO4M7fzQBmi5L3Nw9S

Arborescence de jointure droite-profond

Pour les petits volumes, le plan de jointure peut ne pas avoir d’importance, mais sur des requêtes beaucoup plus volumineuses, le gain de performances des requêtes peut être assez important. Par exemple:

Screen Shot 2022 08 18 at 4.58.29 PM

Avec les statistiques du tableau ci-dessus, les statistiques ci-dessous sont produites lors de la jonction des quatre tableaux satellites pour déployer un magasin d’information virtuel.

Description de la chronologie générée automatiquement avec un niveau de confiance faible

1) Sans PIT, 2) Avec un PIT utilisant HashKeys & LoadDates, 3) Avec un PIT utilisant Seq-ids

  1. Sans table PIT, le temps d’exécution de la requête était de près de 8 minutes et demie ! Notez le volume de données numérisées sur le réseau.
  2. Une table PIT avec des clés de hachage et des dates de chargement a réduit le temps de requête à 20 secondes avec beaucoup moins de trafic réseau, soit une économie de plus de 8 minutes.
  3. Une table PIT avec des identifiants de séquence a réduit de moitié les numéros d’octets ci-dessus et a réduit de 5 secondes supplémentaires le temps de requête.

REMARQUE : Tous les tests ci-dessus ont été effectués avec RESULTCACHE désactivé, un entrepôt virtuel XSMALL qui a été vidé entre les tests d’exécution. Les résultats sont cohérents.

Partie 1 sur 2 sur l’optimisation des requêtes

Reconnaissant que Snowflake a un OLAP moteur de requête est la première étape pour comprendre comment concevoir efficace modèles de données et identifier quand les tables d’assistance aux requêtes peuvent être utilisées pour améliorer les performances que vous obtenez de votre Data Vault sur Snowflake.

Dans le prochain article de blog, nous discuterons d’une autre technique qui améliorera considérablement les temps de requête pour les très grandes tables satellites (ceux qui ont les yeux perçants ont peut-être vu cette technique déjà utilisée dans la requête optimisée en étoile ci-dessus). Jusqu’à la prochaine fois!

Références:

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.