Utilizzo dei join in LookML

I join ti consentono di collegare viste diverse in modo da poter esplorare i dati di più viste contemporaneamente e vedere la correlazione tra le diverse parti dei dati.

Ad esempio, il database potrebbe includere le tabelle order_items, orders e users. Puoi utilizzare i join per esplorare i dati di tutte le tabelle contemporaneamente. Questa pagina spiega i join in LookML, inclusi i parametri di join specifici e i pattern di unione.

I join iniziano con un'esplorazione

I join sono definiti nel file del modello per stabilire la relazione tra un'esplorazione e una vista. I join collegano una o più visualizzazioni a una singola esplorazione, direttamente o tramite un'altra visualizzazione unita.

Considera due tabelle di database: order_items e orders. Dopo aver generato le viste per entrambe le tabelle, dichiarane una o più nel parametro explore nel file del modello:

explore: order_items { ... }

Quando esegui una query dall'esplorazione order_items, order_items viene visualizzato nella clausola FROM dell'SQL generato:

SELECT ...
FROM order_items

Puoi partecipare a ulteriori informazioni sull'esplorazione order_items. Ad esempio, puoi utilizzare il seguente LookML di esempio per unire la vista orders all'esplorazione order_items:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

Il LookML mostrato in precedenza realizza due risultati. Innanzitutto, puoi vedere i campi di orders e order_items nel selettore campi Esplora:

In secondo luogo, il LookML descrive come unire orders e order_items. Questo codice LookML si traduce nel codice SQL seguente:

SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id

Questi parametri LookML sono descritti in modo più dettagliato nelle sezioni seguenti.

Suggerimento per il team di Chat:gli utenti chiedono più spesso informazioni sull'errore di convalida, "Campo sconosciuto o non accessibile", che può essere causato da un join mancante. Per ulteriori informazioni, consulta Errore: campo sconosciuto o inaccessibile.

Parametri di join

Per eseguire l'unione vengono utilizzati quattro parametri principali: join, type, relationship e sql_on.

Passaggio 1: avvia l'esplorazione

Innanzitutto, crea l'esplorazione order_items:

explore: order_items { ... }

Passaggio 2: join

Per unire una tabella, devi prima dichiarare la tabella in una vista. In questo esempio, supponiamo che orders sia una vista esistente nel tuo modello.

Poi, utilizza il parametro join per dichiarare che vuoi unire la vista orders all'esplorazione order_items:

explore: order_items {
  join: orders { ... }
}

Passaggio 3: type

Valuta il tipo di join da eseguire. Looker supporta LEFT JOIN, INNER JOIN, FULL OUTER JOIN e CROSS JOIN. Questi corrispondono ai valori parametro type di left_outer, inner, full_outer e cross.

explore: order_items {
  join: orders {
    type: left_outer
  }
}

Il valore predefinito di type è left_outer.

Passaggio 4: relationship

Definisci una relazione di join tra l'esplorazione order_items e la vista orders. Dichiarare correttamente la relazione di un join è importante affinché Looker possa calcolare misure accurate. La relazione viene definita da esplorazione order_items alla vista orders. Le opzioni possibili sono one_to_one, many_to_one, one_to_many e many_to_many.

In questo esempio, possono esserci più articoli per un singolo ordine. La relazione tra l'esplorazione order_items e la vista orders è many_to_one:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
  }
}

Se non includi un parametro relationship nel join, il valore predefinito di Looker sarà many_to_one.

Per ulteriori suggerimenti su come definire correttamente il parametro relationship per un join, consulta l'articolo Come usare correttamente il parametro relationship.

Passaggio 5: sql_on

Dichiara come unire la tabella order_items e la tabella orders insieme al parametro sql_on o foreign_key.

Suggerimento: in genere consigliamo di utilizzare il parametro sql_on, poiché può fare tutto ciò che può fare il parametro foreign_key, ma il parametro sql_on è generalmente più facile da capire.

Il parametro sql_on è equivalente alla clausola ON nell'SQL generato per una query. Con questo parametro, puoi dichiarare quali campi devono essere abbinati per eseguire il join:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

Puoi anche scrivere join più complessi. Ad esempio, potresti voler partecipare solo agli ordini con id maggiore di 1000:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
  }
}

Consulta la documentazione sugli operatori di sostituzione per scoprire di più sulla sintassi ${ ... } in questi esempi.

Passaggio 6: test

Verifica che questo join funzioni come previsto andando all'esplorazione Elementi dell'ordine. Dovresti vedere i campi sia di order_items che di orders.

Per saperne di più su come testare le modifiche LookML in un'esplorazione, consulta Modifica e convalida di LookML.

Accesso tramite un'altra visualizzazione

Puoi partecipare a una vista a un'esplorazione tramite un'altra vista. Nell'esempio di parametri di join, hai eseguito il join di orders a order_items tramite il campo order_id. Potremmo anche voler unire i dati di una vista chiamata users all'esplorazione order_items, anche se non condividono un campo comune. A questo scopo, unisciti tramite la vista orders.

Utilizza il parametro sql_on o il parametro foreign_key per unire la vista users alla vista orders, anziché all'esplorazione order_items. A tale scopo, definisci correttamente l'ambito del campo da orders a orders.user_id.

Di seguito è riportato un esempio di utilizzo del parametro sql_on:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

Partecipazione a una visualizzazione più volte

Una vista users contiene dati sia per gli acquirenti che per i venditori. Per unire i dati di questa vista in order_items, ma farlo separatamente per acquirenti e venditori, puoi unire users due volte, con nomi diversi, utilizzando il parametro from.

Il parametro from consente di specificare la vista da utilizzare in un join, assegnandogli un nome univoco. Ad esempio:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: buyers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.buyer_id} = ${buyers.id} ;;
  }
  join: sellers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.seller_id} = ${sellers.id} ;;
  }
}

In questo caso, solo i dati degli acquirenti vengono uniti come buyers, mentre solo i dati del venditore vengono uniti come sellers.

Nota: la vista users deve ora essere indicata con i nomi con alias buyers e sellers nel join.

Limitare i campi di un join

Il parametro fields consente di specificare quali campi vengono trasferiti da un join a un'esplorazione. Per impostazione predefinita, tutti i campi di una visualizzazione vengono importati una volta uniti. Tuttavia, potresti voler utilizzare solo un sottoinsieme di campi.

Ad esempio, quando orders viene unito a order_items, potresti voler portare solo i campi shipping e tax tramite il join:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [shipping, tax]
  }
}

Puoi anche fare riferimento a un insieme di campi, ad esempio [set_a*]. Ogni insieme viene definito all'interno di una vista utilizzando il parametro set. Supponi di avere definito il seguente insieme nella vista orders:

set: orders_set {
  fields: [created_date, shipping, tax]
}

Puoi scegliere di visualizzare solo questi tre campi quando aggiungi orders a order_items:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [orders_set*]
  }
}

Aggregati simmetrici

Looker utilizza una funzionalità chiamata "aggregati simmetrici" per calcolare correttamente le aggregazioni (come somme e medie), anche quando i join generano un fan-out. Gli aggregati simmetrici sono descritti più in dettaglio in Comprensione degli aggregati simmetrici. Il problema del fanout risolto dai fanout simmetrici viene spiegato nel post della scheda Community The problem of SQL fanouts (Il problema dei fanout SQL).

Le chiavi primarie sono obbligatorie

Per fare in modo che le misure (aggregazioni) arrivino tramite i join, devi definire le chiavi primarie in tutte le viste coinvolte nel join.

Per farlo, aggiungi il parametro primary_key alla definizione del campo della chiave primaria in ogni vista:

dimension: id {
  type: number
  primary_key: yes
}

Nota: per gestire correttamente le misure unite, Looker si basa sulla specifica di una chiave primaria in cui i valori sono completamente univoci e non NULL. Se i tuoi dati non contengono una chiave primaria, valuta se la concatenazione di più campi può generare una chiave primaria di valori completamente univoci e non NULL. Se la tua chiave primaria non è univoca o contiene valori NULL e la query include dati che rivelano questi problemi, Looker restituisce un errore come descritto nella pagina Best practice Errore: valore/chiave primaria non univoco (o sql_distinct_key), overflow di valori o collisione durante il calcolo della somma.

Dialetti SQL supportati

Affinché Looker supporti gli aggregati simmetrici nel tuo progetto Looker, è necessario che anche il dialetto del tuo database supporti questi aggregati. La tabella seguente mostra quali dialetti supportano gli aggregati simmetrici nell'ultima release di Looker:

Dialetto	Supportata?
Valanga Actia	Sì
Amazon Athena	Sì
Amazon Aurora MySQL	Sì
Amazon Redshift	Sì
Druid Apache	No
Apache Druid 0.13 o versioni successive	No
Apache Druid 0.18 o versioni successive	No
Apache Hive 2.3 o versioni successive	No
Apache Hive 3.1.2 o versioni successive	No
Apache Spark 3 e versioni successive	Sì
ClickHouse	No
Cloudera Impala 3.1 o versioni successive	Sì
Cloudera Impala 3.1 o versioni successive con driver nativo	Sì
Cloudera Impala con driver nativo	No
DataVirtuality	Sì
Databricks	Sì
Denodo 7	Sì
Denodo 8	Sì
Dremio	No
Dremio 11+	Sì
Exasol	Sì
Firebolt	Sì
SQL precedente di Google BigQuery	Sì
SQL standard di Google BigQuery	Sì
Google Cloud PostgreSQL	Sì
Google Cloud SQL	Sì
Google Spanner	Sì
Greenplum	Sì
HyperSQL	No
Netezza di IBM	Sì
MariaDB	Sì
PostgreSQL Microsoft Azure	Sì
Database SQL di Microsoft Azure	Sì
Microsoft Azure Synapse Analytics	Sì
Microsoft SQL Server 2008 e versioni successive	Sì
Microsoft SQL Server 2012 o versioni successive	Sì
Microsoft SQL Server 2016	Sì
Microsoft SQL Server 2017 o versioni successive	Sì
MongoBI	No
MySQL	Sì
MySQL 8.0.12 o versioni successive	Sì
Oracle	Sì
Oracle ADWC	Sì
PostgreSQL 9.5 e versioni successive	Sì
PostgreSQL pre-9.5	Sì
PrestoDB	Sì
PrestoSQL	Sì
SAP HANA 2 o versioni successive	Sì
SingleStore	Sì
SingleStore 7+	Sì
Snowflake	Sì
Teradata	Sì
Trino	Sì
Vettoriale	Sì
Vertica	Sì

Se il tuo dialetto non supporta gli aggregati simmetrici, fai attenzione quando esegui i join in Looker, poiché alcuni tipi di join possono comportare aggregazioni imprecise (come somme e medie). Questo problema e le relative soluzioni alternative sono descritti in modo molto dettagliato nel post della scheda Community Il problema dei fanout SQL.

Scopri di più sui join

Per scoprire di più sui parametri di join in LookML, consulta la documentazione di riferimento per join.