Giunzioni nelle viste metriche

I join nelle visualizzazioni delle metriche arricchiscono i dati di origine con attributi di tabelle correlate. Supportano join diretti tra una tabella dei fatti e le tabelle delle dimensioni (schema a stella), join a più passaggi tra tabelle delle dimensioni normalizzate (schema a fiocco di neve) e join uno-a-molti che aggregano i dati provenienti dalle tabelle correlate. Per impostazione predefinita, tutti i join sono molti a uno, quindi ogni riga di origine corrisponde al massimo a una riga nella tabella collegata.

Join dello schema a stella

In uno schema a stella, source è la tabella dei fatti e si collega a una o più tabelle delle dimensioni utilizzando un LEFT OUTER JOIN. Le visualizzazioni delle metriche si uniscono alle tabelle dei fatti e delle dimensioni necessarie per la query specifica, in base ai campi e alle misure selezionati.

Specificare le colonne join usando una on clausola (espressione booleana) o una using clausola (nomi di colonna condivisa). Il join deve basarsi su una relazione da molti a uno. In caso di molti-a-molti, il motore seleziona la prima riga corrispondente dalla tabella delle dimensioni unita.

Nell'esempio seguente la orders tabella dei fatti viene unita alla tabella delle customer dimensioni con una on clausola , che accetta un'espressione booleana:

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)

Quando le colonne join hanno lo stesso nome in entrambe le tabelle, usare una using clausola anziché una on clausola . La clausola using accetta un array di nomi di colonna presenti sia nella tabella di origine sia nella tabella combinata. Nessun set di dati nel samples catalogo include tabelle che condividono un nome di colonna join, pertanto nell'esempio seguente vengono usati nomi di tabella segnaposto e di colonna per illustrare la sintassi:

joins:
  - name: customer
    source: catalog.schema.customer
    using:
      - customer_id

Join dello schema Snowflake

Uno schema snowflake estende uno schema star normalizzando le tabelle delle dimensioni e collegandole alle sottodimensioni. Verrà creata una struttura di unione a più livelli.

Per definire uno schema snowflake:

1. Creare una visualizzazione metrica.
2. Aggiungere join di primo livello (schema star).
3. Eseguire un join con altre tabelle delle dimensioni.
4. Rendere disponibili gli attributi annidati aggiungendo campi nella visualizzazione.

Nell'esempio seguente viene usato il set di dati TPC-H per illustrare uno schema snowflake che mostra la gerarchia geografica degli ordini. L'esempio unisce la tabella degli ordini ai clienti, quindi alle nazioni (paesi o aree geografiche) e infine alle aree geografiche (continenti). Il set di dati TPC-H è disponibile nel catalogo samples nell'area di lavoro Azure 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à del join

Il campo cardinality in un join controlla la relazione tra la tabella di origine e la tabella collegata. Questo campo determina come il motore elabora le misure che fanno riferimento alle colonne della tabella unita.

La tabella seguente confronta le due cardinalità supportate:

Giunzioni da molti a uno

Molti-a-uno è la cardinalità predefinita. Ogni riga nella sorgente corrisponde al massimo a una riga della tabella risultante dal join, quindi la tabella risultante funge da tabella di ricerca per le dimensioni. È possibile omettere il campo cardinality per join molti-a-uno, oppure specificare cardinality: many_to_one esplicitamente.

Sia i campi sia le misure possono fare riferimento alle colonne di una join molti-a-uno usando la notazione con il punto (ad esempio, customer.c_name).

Dichiara vincoli di join con rely

L'impostazione rely.at_most_one_match: true dichiara che il join non ha alcun fan-out sul lato "uno":

• In un join di tipo molti-a-uno, ogni riga di origine corrisponde ad al massimo una riga nella tabella unita.
• In un join uno-a-molti ogni riga unita corrisponde al massimo a una riga di origine.

Questa dichiarazione consente al motore di ignorare i join non necessari e ridurre i dati analizzati, in particolare per le query che applicano filtri ai campi della tabella collegata. Databricks consiglia di impostare rely su entrambe le cardinalità quando il vincolo è soddisfatto.

L'esempio seguente collega orders a customer con rely abilitato:

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)

Vedere Ottimizzare i join con rely per il riferimento completo del campo rely.

Join uno-a-molti

Impostare cardinality: one_to_many per consentire a una singola riga di origine di essere associata a più righe nella tabella risultante dal join. Ciò trasforma quella tabella in una fonte di fatti che il motore aggrega in modo indipendente al livello di granularità di origine.

Un join uno-a-molti consente a un'unica vista metrica di misurare dati fattuali a diversi livelli di granularità, ad esempio ordini per cliente o eventi per account, senza duplicare le righe di origine nei risultati della query. L'origine funge da colonna vertebrale dimensionale: ogni entità viene visualizzata esattamente una volta, indipendentemente dal numero di righe corrispondenti presenti nella tabella unita.

Esempio di join uno-a-molti

L'esempio seguente usa customer come sorgente e unisce orders a cardinality: one_to_many. Un many_to_one join con nation fornisce il campo nation_name. Qualifica il lato sorgente di ogni condizione di join con source. in modo che il riferimento venga ricondotto alla tabella sorgente della vista metrica. Entrambi i join impostati rely.at_most_one_match: true: nel nation join asserisce che ogni cliente ha al massimo una nazione e al orders join asserisce che ogni ordine appartiene al massimo un cliente. Vedi Dichiarare vincoli di join con 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)

In questa vista, customer_count conta le righe nella tabella di origine customer , mentre order_count e total_order_revenue aggregano le righe dal orders ramo. Un cliente con due ordini restituisce un order_count valore pari a 2 mentre customer_count rimane 1, che conferma che le righe di origine non sono duplicate. Un cliente senza ordini viene ancora visualizzato nei risultati, con un valore order_count pari a 0 e un oggetto NULLtotal_order_revenue.

Join uno-a-molti annidati

Per misurare i fatti che sono due o più livelli al di sotto dell'origine, annidare join uno-a-molti. Tutti i join in un sottoalbero uno-a-molti devono condividere la stessa cardinalità, quindi un padre uno-a-molti non può avere un figlio molti-a-uno. Fare riferimento a una colonna in un join annidato tramite il relativo percorso completo con punti attraverso i nomi dei join.

L'esempio seguente annida lineitem in orders in modo che una singola vista a granularità cliente possa contare sia gli ordini sia le righe d'ordine:

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)

Le misure fanno riferimento alle colonne nidificate con il relativo percorso completo tramite i nomi di join, ad esempio orders.lineitem.l_extendedprice, perché lineitem è raggiungibile solo tramite orders. Usare count(distinct orders.o_orderkey) anziché un semplice count per il conteggio degli ordini: ogni ordine si suddivide in più voci di riga, quindi un semplice conteggio conterebbe un ordine una volta per ogni voce di riga.

Join uno-a-molti affiancate

Definire più join uno-a-molti allo stesso livello per misurare le origini dei fatti indipendenti da una singola visualizzazione. Il motore aggrega separatamente i join fratelli e poi li combina, per cui le rispettive righe non si moltiplicano mai tra loro. Gli elementi di pari livello al livello superiore possono combinare liberamente le cardinalità, quindi un join sulla dimensione many_to_one e un join sui fatti one_to_many possono coesistere allo stesso livello.

L'esempio seguente usa nation come sorgente e aggiunge due rami indipendenti uno a molti, customer e 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 misura customers_per_supplier divide due aggregazioni indipendenti dopo che il motore combina ciascuna al livello di granularità della query. È possibile combinare misure provenienti da origini diverse con aritmetica, ma una singola funzione di aggregazione deve fare riferimento a colonne di una sola origine.

Collegare più tabelle dei fatti con una tabella ponte

Una vista metrica modella una singola tabella dei fatti unita alle tabelle delle dimensioni. Per combinare le misure di due o più tabelle dei fatti con granularità diverse, definire una tabella ponte che elenchi le combinazioni valide delle dimensioni condivise dai fatti, direttamente nella source vista metrica. Ad esempio, il samples.tpch fatto lineitem di spedizione (granularità: riga d'ordine) e il fatto partsupp di fornitura (granularità: componente e fornitore) condividono entrambe le dimensioni componente e fornitore.

Un bridge rende esplicito il set di combinazioni di dimensioni valide, in modo che i risultati delle query rimangano prevedibili. La visualizzazione metrica restituisce solo le combinazioni dichiarate valide anziché dedurrle per ogni query. Impostare cardinality: one_to_many per ogni join tra fatti in modo che il motore aggreghi ogni fatto in modo indipendente rispetto al bridge condiviso, senza generare fan-out né doppi conteggi.

Per creare il bridge, definirlo come una query SQL nella vista metrica source, unire ogni tabella dei fatti a esso in base alle colonne condivise, quindi definire i campi sulle colonne della dimensione condivisa e le misure su ciascun fatto. Utilizzare un oggetto CROSS JOIN quando ogni combinazione delle dimensioni condivise è valida:

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)

Una misura su una tabella dei fatti conta solo i record i cui valori delle dimensioni condivise vengono visualizzati nel bridge. Le combinazioni che il bridge non include non contribuiscono ai risultati.

Quando vuoi solo le combinazioni che si verificano effettivamente, sostituisci source con un UNION (o FULL OUTER JOIN) delle coppie distinte di ciascun fatto, in modo che ciascun fatto contribuisca con i propri membri. joins, fieldse measures rimangono invariati:

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

Restrizioni di join da uno a molti

• I campi non possono fare riferimento a un join uno-a-molti: un campo deve corrispondere a un solo valore per ciascuna riga di origine. Poiché una colonna uno-a-molti può avere più valori per riga di origine, non è possibile usarla in una fields definizione. Per usare una colonna di questo tipo come campo, imposta quella tabella come origine dati e unisci l'origine dati originale come join many_to_one.
• Una singola aggregazione non può estendersi su origini: ogni funzione di aggregazione deve fare riferimento a colonne da un'origine. L'aritmetica tra i risultati di due aggregazioni è consentita, ad esempio count(orders.o_orderkey) / count(*), ma una singola funzione non può combinare colonne da due origini.
• Un sottoalbero di join non può mescolare cardinalità: tutti i discendenti di un join uno-a-molti devono anch'essi essere uno-a-molti e tutti i discendenti di un join molti-a-uno devono essere molti-a-uno. Solo gli elementi di pari livello superiore possono combinare cardinalità.

Risorse aggiuntive

• Visualizzazioni delle metriche del modello
• Tecniche avanzate per le visualizzazioni delle metriche
• Informazioni di riferimento sulla sintassi YAML per la visualizzazione delle metriche
• Esercitazione: Creare una vista metrica con join e modellazione dei dati