Jointures dans les vues de métriques
Les jointures dans les vues de métriques enrichissent vos données sources avec des attributs provenant de tables associées. Ils prennent en charge les jointures directes d'une table de faits à des tables de dimension (schéma en étoile), les jointures à plusieurs sauts entre des tables de dimension normalisées (schéma en flocon de neige) et les jointures un-à-plusieurs qui agrègent les faits de tables associées. By default, toutes les jointures sont plusieurs-à-un, de sorte que chaque ligne source correspond au maximum à une ligne dans la table jointe.
Jointures de schéma en étoile
Dans un schéma en étoile, le source est la table de faits et s'associe à une ou plusieurs tables de dimensions à l'aide d'un LEFT OUTER JOIN. Les vues de métriques joignent les tables de faits et de dimensions nécessaires pour la query spécifique, en fonction des champs et des mesures sélectionnés.
Spécifiez les colonnes de jointure à l'aide d'une clause on (expression booléenne) ou d'une clause using (noms de colonnes partagés). La jointure doit suivre une relation plusieurs-à-un. En cas de relations plusieurs-à-plusieurs, le moteur sélectionne la première ligne correspondante de la table de dimension jointe.
L'exemple suivant joint la table de faits orders à la table de dimension customer avec une clause on, qui prend une expression booléenne :
version: 1.1
source: samples.tpch.orders
joins:
# The on clause supports a Boolean expression
- name: customer
source: samples.tpch.customer
on: source.o_custkey = customer.c_custkey
fields:
# Field referencing a join column using dot notation
- name: Customer name
expr: customer.c_name
- name: Customer market segment
expr: customer.c_mktsegment
measures:
# Measure referencing a join column
- name: Total revenue
expr: SUM(o_totalprice)
- name: Order count
expr: COUNT(1)
Lorsque les colonnes de jointure ont le même nom dans les deux tables, utilisez une clause using au lieu d'une clause on. La clause using prend un tableau de noms de colonne qui existent à la fois dans la table source et la table jointe. Aucun dataset du catalogue samples ne contient de tables qui partagent un nom de colonne de jointure. Par conséquent, l'exemple suivant utilise des noms de tables et de colonnes de remplissage pour illustrer la syntaxe :
joins:
- name: customer
source: catalog.schema.customer
using:
- customer_id
Dans une clause on, source fait référence à la table source de la vue de métrique et la jointure name fait référence aux colonnes de la table jointe. Par exemple, source.o_custkey = customer.c_custkey joint la colonne o_custkey de la table source à la colonne c_custkey de la table customer. Si aucun préfixe n'est fourni, la référence est default la table jointe.
Jointures de schémas Snowflake
Un schéma en flocon de neige étend un schéma en étoile en normalisant les tables de dimension et en les connectant à des sous-dimensions. Ceci crée une structure de jointure multi-niveaux.
Pour définir un schéma en flocon de neige :
- Créer une vue métrique.
- Ajouter des jointures de premier niveau (schéma en étoile).
- Joindre avec d'autres tables de dimensions.
- Rendez les attributs imbriqués disponibles en ajoutant des champs dans votre vue.
L'exemple suivant utilise le dataset TPC-H pour illustrer un schéma en flocon de neige montrant la hiérarchie géographique des commandes. L'exemple joint la table des commandes aux clients, puis à leurs nations (pays ou régions), et enfin à leurs régions (continents). Le dataset TPC-H est disponible dans le catalogue samples de votre workspace Databricks.
source: samples.tpch.orders
joins:
- name: customer
source: samples.tpch.customer
on: source.o_custkey = customer.c_custkey
joins:
- name: nation
source: samples.tpch.nation
on: customer.c_nationkey = nation.n_nationkey
joins:
- name: region
source: samples.tpch.region
on: nation.n_regionkey = region.r_regionkey
fields:
- name: clerk
expr: o_clerk
- name: customer
expr: customer
comment: returns the full customer row as a struct
- name: customer_name
expr: customer.c_name
- name: nation
expr: customer.nation
- name: nation_name
expr: customer.nation.n_name
Cardinalité de jointure
Le champ cardinality d'une jointure contrôle la relation entre la table source et la table jointe. Ce champ détermine la façon dont le moteur traite les mesures qui référencent des colonnes de la table jointe.
Le tableau suivant compare les deux cardinalités prises en charge :
Propriété |
|
|
|---|---|---|
Lignes correspondantes par ligne source | Au maximum un | Zéro ou plus |
Utilisation typique | Recherche de dimension | Expansion des faits |
Autorisé dans | Oui | Non |
Autorisé dans | Oui | Oui |
Jointures de plusieurs à un
Plusieurs à un est la cardinalité default. Chaque ligne de la source correspond à une seule ligne au maximum dans la table jointe, de sorte que la table jointe agit comme une recherche de dimension. Vous pouvez omettre le champ cardinality pour les jointures plusieurs-à-un, ou indiquer cardinality: many_to_one explicitement.
Les champs et les mesures peuvent tous deux référencer des colonnes d'une jointure plusieurs-à-un en utilisant la notation par points (par exemple, customer.c_name).
Déclarer des contraintes de jointure avec rely
Le paramètre rely.at_most_one_match: true déclare que la jointure n'a pas d'expansion côté « un » :
- Dans une jointure plusieurs-à-un, chaque ligne source correspond à une seule ligne au maximum dans la table jointe.
- Lors d'une jointure un-à-plusieurs, chaque ligne jointe correspond au maximum à une seule ligne source.
Cette déclaration permet au moteur d'ignorer les jointures inutiles et de réduire les données analysées, en particulier pour les query qui filtrent sur des champs de la table jointe. Databricks recommande de définir rely sur les deux cardinalités lorsque la contrainte est valide.
Définissez at_most_one_match: true uniquement lorsque la relation est réellement valide. Cette propriété n'est pas validée au moment de l'exécution. Si le côté asserté produit un fan-out, les mesures renvoient des résultats incorrects.
L'exemple suivant joint orders à customer avec rely activé :
version: 1.1
source: samples.tpch.orders
joins:
- name: customer
source: samples.tpch.customer
on: source.o_custkey = customer.c_custkey
rely:
at_most_one_match: true
fields:
- name: Customer name
expr: customer.c_name
- name: Customer market segment
expr: customer.c_mktsegment
measures:
- name: Total revenue
expr: SUM(o_totalprice)
- name: Order count
expr: COUNT(1)
Consultez Optimiser les jointures avec rely pour la référence complète du champ rely.
Jointures un-à-plusieurs
Définissez cardinality: one_to_many pour permettre à une seule ligne source de correspondre à plusieurs lignes de la table jointe. Cela transforme cette table en une source de faits que le moteur agrège indépendamment au niveau du grain de la source.
Les jointures un-à-plusieurs nécessitent Databricks Runtime 18.1 ou une version ultérieure et la version 1.1 de la spécification YAML. Voir Disponibilité de la fonctionnalité Vue métrique.
Une jointure un-à-plusieurs permet à une seule vue métrique de mesurer des faits qui existent à différents niveaux de granularité, tels que les commandes par client ou les événements par compte, sans dupliquer les lignes source dans les résultats de query. La source agit comme la colonne vertébrale dimensionnelle : chaque entité apparaît exactement une fois, quel que soit le nombre de lignes correspondantes dans la table jointe.
Exemple de jointure un à plusieurs
L'exemple suivant utilise customer comme source et joint orders à cardinality: one_to_many. Une jointure many_to_one à nation fournit le champ nation_name. Qualifiez le côté source de chaque condition de jointure avec source. afin que la référence se résolve en table source de la vue de métrique. Les deux jointures définissent rely.at_most_one_match: true: sur la jointure nation, elle affirme que chaque client a au plus une nation, et sur la jointure orders, elle affirme que chaque commande appartient à au plus un client. Consultez Déclarer les contraintes de jointure avec rely.
version: 1.1
source: samples.tpch.customer
joins:
- name: nation
source: samples.tpch.nation
on: nation.n_nationkey = source.c_nationkey
rely:
at_most_one_match: true
- name: orders
source: samples.tpch.orders
on: orders.o_custkey = source.c_custkey
cardinality: one_to_many
rely:
at_most_one_match: true
fields:
- name: customer_name
expr: c_name
- name: nation_name
expr: nation.n_name
measures:
- name: customer_count
expr: count(*)
- name: order_count
expr: count(orders.o_orderkey)
- name: total_order_revenue
expr: sum(orders.o_totalprice)
Dans cette vue, customer_count compte les lignes de la table source customer, tandis que order_count et total_order_revenue agrègent les lignes de la Branch orders. Un client avec deux commandes retourne un order_count de 2 alors que customer_count reste 1, ce qui confirme que les lignes source ne sont pas dupliquées. Un client sans commandes apparaît toujours dans les résultats, avec un order_count de 0 et un NULL total_order_revenue.
Jointures imbriquées un-a-à-plusieurs
Pour mesurer les faits qui sont deux niveaux ou plus en dessous de la source, imbriquez les jointures un-à-plusieurs. Toutes les jointures dans un sous-arbre un-à-plusieurs doivent partager la même cardinalité, donc un parent un-à-plusieurs ne peut pas avoir un enfant plusieurs-à-un. Référencez une colonne dans une jointure imbriquée avec son chemin d’accès complet via les noms de jointure.
L'exemple suivant imbrique lineitem sous orders afin qu'une vue au niveau du client unique puisse compter à la fois les commandes et les postes :
version: 1.1
source: samples.tpch.customer
joins:
- name: orders
source: samples.tpch.orders
on: orders.o_custkey = source.c_custkey
cardinality: one_to_many
joins:
- name: lineitem
source: samples.tpch.lineitem
on: lineitem.l_orderkey = orders.o_orderkey
cardinality: one_to_many
fields:
- name: customer_name
expr: c_name
measures:
- name: order_count
expr: count(distinct orders.o_orderkey)
- name: line_item_count
expr: count(orders.lineitem.l_linenumber)
- name: total_line_revenue
expr: sum(orders.lineitem.l_extendedprice)
Les mesures référencent les colonnes imbriquées avec leur chemin de points complet via les noms de jointure, tels que orders.lineitem.l_extendedprice, car lineitem n'est accessible que via orders. Utilisez count(distinct orders.o_orderkey) plutôt qu'un simple count pour le comptage des commandes : chaque commande se répartit en plusieurs postes, donc un simple comptage compterait une commande une seule fois par poste.
Jointures un-à-plusieurs
Définissez plusieurs jointures un-à-plusieurs au même niveau pour mesurer des sources de faits indépendantes à partir d’une vue unique. Le moteur agrège les jointures homologues séparément puis les fusionne, de sorte que leurs lignes ne se multiplient jamais entre elles. Les éléments frères de niveau supérieur peuvent librement mélanger les cardinalités, de sorte qu'une jointure de dimension many_to_one et une jointure de faits one_to_many peuvent coexister au même niveau.
L’exemple suivant utilise nation comme source et ajoute deux indépendantes un-à-plusieurs Branch, customer et supplier:
version: 1.1
source: samples.tpch.nation
joins:
- name: customer
source: samples.tpch.customer
on: customer.c_nationkey = source.n_nationkey
cardinality: one_to_many
- name: supplier
source: samples.tpch.supplier
on: supplier.s_nationkey = source.n_nationkey
cardinality: one_to_many
fields:
- name: nation_name
expr: n_name
measures:
- name: customer_count
expr: count(customer.c_custkey)
- name: supplier_count
expr: count(supplier.s_suppkey)
- name: customers_per_supplier
expr: count(customer.c_custkey) / count(supplier.s_suppkey)
La mesure customers_per_supplier divise deux agrégations indépendantes après que le moteur a combiné chacune d’entre elles au niveau de la granularité de la query. Vous pouvez combiner des mesures de différentes sources avec l’arithmétique, mais une seule fonction d’agrégation doit référencer des colonnes provenant d’une seule source.
Connecter plusieurs tables de faits avec une table de pont.
Une vue de métrique modélise une seule table de faits jointe à des tables de dimensions. Pour combiner des mesures de deux tables de faits ou plus qui sont à des granularités différentes, définissez un pont qui énumère les combinaisons valides des dimensions partagées par les faits, directement dans le source de la vue de métrique. Par exemple, le fait de livraison samples.tpch lineitem (granularité : ligne de commande) et le fait d'approvisionnement partsupp (granularité : pièce et fournisseur) partagent tous deux les dimensions de pièce et de fournisseur.
Un pont rend l'ensemble des combinaisons de dimensions valides explicite, afin que les résultats de la query restent prévisibles. La vue métrique renvoie uniquement les combinaisons que vous déclarez valides, plutôt que de les inférer pour chaque query. Définissez cardinality: one_to_many sur chaque jointure de faits afin que le moteur agrège chaque fait indépendamment par rapport au pont partagé, sans démultiplication ni double comptage.
Pour construire le pont, définissez-le comme une query SQL dans la vue de métrique source, joignez-y chaque table de faits sur ses colonnes partagées, puis déclarez les champs sur les colonnes de dimension partagées et les mesures sur chaque fait. Utilisez un CROSS JOIN lorsque chaque combinaison des dimensions partagées est valide :
version: 1.1
source: SELECT * FROM samples.tpch.part CROSS JOIN samples.tpch.supplier
filter: s_suppkey IN (11315, 42920) AND p_partkey IN (30419, 80418)
joins:
- name: lineitem
source: samples.tpch.lineitem
on: source.p_partkey = lineitem.l_partkey AND source.s_suppkey = lineitem.l_suppkey
cardinality: one_to_many
- name: partsupp
source: samples.tpch.partsupp
on: source.p_partkey = partsupp.ps_partkey AND source.s_suppkey = partsupp.ps_suppkey
cardinality: one_to_many
fields:
- name: part_name
expr: p_name
- name: part_brand
expr: p_brand
- name: part_type
expr: p_type
- name: part_size
expr: p_size
- name: manufacturer
expr: p_mfgr
- name: supplier_name
expr: s_name
measures:
- name: lineitem_count
expr: COUNT(lineitem.*)
- name: total_quantity_sold
expr: SUM(lineitem.l_quantity)
- name: gross_revenue
expr: SUM(lineitem.l_extendedprice)
- name: net_revenue
expr: SUM(lineitem.l_extendedprice * (1 - lineitem.l_discount))
- name: distinct_orders
expr: COUNT(DISTINCT lineitem.l_orderkey)
- name: available_quantity
expr: SUM(partsupp.ps_availqty)
- name: avg_supply_cost
expr: AVG(partsupp.ps_supplycost)
- name: total_supply_value
expr: SUM(partsupp.ps_availqty * partsupp.ps_supplycost)
Une mesure sur une table de faits ne compte que les enregistrements dont les valeurs de dimension partagées apparaissent dans le pont. Les combinaisons que le pont n'inclut pas ne contribuent pas aux résultats.
Lorsque vous ne voulez que les combinaisons qui se produisent réellement, échangez le source contre un UNION (ou FULL OUTER JOIN) des paires distinctes de chaque fait afin que chaque fait contribue à ses membres. Les joins, fields et measures restent les mêmes :
source: |
SELECT DISTINCT l_partkey AS p_partkey, l_suppkey AS s_suppkey FROM samples.tpch.lineitem
UNION
SELECT DISTINCT ps_partkey AS p_partkey, ps_suppkey AS s_suppkey FROM samples.tpch.partsupp
Restrictions de jointure un-à-plusieurs
- Les champs ne peuvent pas faire référence à une jointure un-à-plusieurs : Un champ doit correspondre à une seule valeur par ligne source. Étant donné qu'une colonne un-à-plusieurs peut avoir plusieurs valeurs par ligne source, vous ne pouvez pas l'utiliser dans une définition
fields. Pour utiliser une telle colonne comme champ, faites de cette table la source et joignez la source originale en tant que jointuremany_to_oneà la place. - Une seule agrégation ne peut pas couvrir plusieurs sources : Chaque fonction d'agrégation doit référencer des colonnes provenant d'une seule source. L'arithmétique entre les résultats de deux agrégations est autorisée, comme
count(orders.o_orderkey) / count(*), mais une seule fonction ne peut pas combiner des colonnes de deux sources. - Un sous-arbre de jointure ne peut pas mélanger les cardinalités : Tous les descendants d'une jointure un-à-plusieurs doivent également être un-à-plusieurs, et tous les descendants d'une jointure plusieurs-à-un doivent être plusieurs-à-un. Seuls les éléments frères de niveau supérieur peuvent mélanger les cardinalités.