Questa pagina è rivolta a chiunque tenti di utilizzare LookML per creare un'esplorazione in Looker. La pagina sarà più facile da comprendere se hai dimestichezza con SQL, in particolare se conosci la differenza tra join interni ed esterni. Per una spiegazione concisa delle differenze tra le unioni interne ed esterne, consulta questo articolo di w3schools sulle unioni SQL.
Looker può essere un potente motore SQL per la tua azienda. La modellazione astratta in LookML consente ai team di dati e IT di creare regole generali sempre vere, liberando gli analisti aziendali dalla necessità di creare query sempre corrette anche se il team di dati non ha mai previsto la necessità di queste query. Il fattore chiave di questa funzionalità è l'algoritmo degli aggregati simmetrici, che risolve un problema a livello di settore con i join SQL. Tuttavia, per sfruttare l'algoritmo è necessario eseguire correttamente due operazioni: le chiavi principali devono essere precise in ogni visualizzazione contenente una misura (in genere tutte) e i parametri relationship devono essere corretti in ogni join.
Chiavi primarie
In molti modi, comprendere la chiave primaria di una tabella è essenzialmente come comprendere che cos'è la tabella e cosa si può fare con essa. L'unica cosa che deve essere vera è che la colonna (o l'insieme di colonne concatenate) scelta come chiave primaria non deve avere valori ripetuti.
Il parametro relationship
Ora che hai verificato le chiavi principali, puoi determinare il valore corretto per il parametro relationship della join. Lo scopo del parametro relationship è indicare a Looker se chiamare gli aggregati simmetrici quando il join viene scritto in una query SQL. Un possibile approccio in questo caso sarebbe dire a Looker di invocarli sempre, in modo da produrre sempre risultati accurati. Tuttavia, questo comporta un costo in termini di rendimento, pertanto è meglio utilizzare gli aggregati simmetrici con giudizio.
La procedura per determinare il valore corretto è leggermente diversa tra le unioni interne ed esterne.
Unioni interne
Ad esempio, supponiamo di avere una tabella degli ordini con una chiave primaria order_id:
| order_id | quantità | customer_id |
|---|---|---|
| 1 | $ 25,00 | 1 |
| 2 | $ 50,00 | 1 |
| 3 | $ 75,00 | 2 |
| 4 | $ 35,00 | 3 |
Supponiamo di avere anche una tabella di clienti con una chiave primaria customer_id:
| customer_id | first_name | last_name | visite |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
Puoi unire queste tabelle in base al campo customer_id, presente in entrambe le tabelle. Questa unione viene rappresentata in LookML nel seguente modo:
explore: orders {
join: customers {
type: inner
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
}
Il risultato di questa unione LookML può essere rappresentato come una singola tabella unita, come segue:
| order_id | quantità | customer_id | customer_id | first_name | last_name | visite |
|---|---|---|---|---|---|---|
| 1 | $ 25,00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | $ 50,00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | $ 75,00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $ 35,00 | 3 | 3 | Wilbur | Wright | 4 |
La relazione many_to_one qui si riferisce al numero di volte in cui un valore del campo di join (customer_id) è rappresentato in ogni tabella. Nella tabella orders (tabella a sinistra), un singolo ID cliente è rappresentato più volte (in questo caso, si tratta del cliente con l'ID 1, presente in più righe).
Nella tabella customers (la tabella a destra), ogni ID cliente è rappresentato una sola volta poiché customer_id è la chiave primaria della tabella. Pertanto, i record nella tabella orders potrebbero avere molte corrispondenze per un singolo valore nella tabella customers. Se customer_id non fosse univoco in ogni riga della tabella customers, la relazione sarebbe many_to_many.
Per determinare il valore corretto della relazione in modo programmatico, controlla le chiavi principali:
- Inizia scrivendo
many_to_manycome relazione. Se le chiavi principali sono corrette, i risultati saranno sempre accurati perché Looker attiverà sempre l'algoritmo di aggregazione simmetrica e applicherà l'accuratezza. Tuttavia, poiché l'algoritmo complica le query e aumenta il tempo di esecuzione, è consigliabile provare a modificare uno o entrambi i lati inoneanzichémany. - Esamina il campo o i campi nella clausola
sql_ondella tabella a sinistra. Se il campo o i campi formano la chiave primaria della tabella a sinistra, puoi modificare il lato sinistro del parametrorelationshipinone. In caso contrario, in genere deve rimaneremany. Per informazioni su un caso speciale, consulta la sezione Aspetti da considerare più avanti in questa pagina. - Successivamente, esamina il campo o i campi che rappresentano la tabella corretta nella clausola
sql_on. Se il campo o i campi formano la chiave primaria della tabella a destra, puoi modificare il lato destro inone.
È buona norma scrivere la frase sql_on iniziando dalla tabella di sinistra, rappresentata sul lato sinistro del segno di uguale, e dalla tabella di destra, che si trova sul lato destro. L'ordine delle condizioni nel parametro sql_on non è importante, a meno che non sia pertinente al dialetto SQL del database. Anche se il parametro sql_on non richiede di ordinare i campi in questo modo, organizzare le condizioni sql_on in modo che i lati sinistro e destro del segno di uguale corrispondano al modo in cui il parametro relationship viene letto da sinistra a destra può aiutarti a determinare la relazione. Ordinare i campi in questo modo può anche aiutarti a capire più facilmente, a colpo d'occhio, a quale tabella esistente nell'esplorazione stai unendo la nuova tabella.
Unioni esterne
Per le unioni esterne, devi anche tenere presente che potrebbe verificarsi un fanout quando vengono aggiunti record null durante l'unione. Questo è particolarmente importante perché le unioni esterne a sinistra sono l'impostazione predefinita in Looker. Sebbene i record null non influiscano su somme o medie, influiscono sul modo in cui Looker esegue una misura di type: count. Se non lo fai correttamente, i record null verranno conteggiati (il che non è auspicabile).
In un join esterno completo, è possibile aggiungere record null a entrambe le tabelle se la chiave di join è priva di valori esistenti nell'altra tabella. Questo è illustrato nell'esempio seguente, che coinvolge una tabella orders:
| order_id | quantità | customer_id |
|---|---|---|
| 1 | $ 25,00 | 1 |
| 2 | $ 50,00 | 1 |
| 3 | $ 75,00 | 2 |
| 4 | $ 35,00 | 3 |
Per l'esempio, supponiamo di avere anche la seguente tabella customers:
| customer_id | first_name | last_name | visite |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
Una volta unite, la tabella unita può essere rappresentata come segue:
| order_id | quantità | customer_id | customer_id | first_name | last_name | visite |
|---|---|---|---|---|---|---|
| 1 | $ 25,00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | $ 50,00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | $ 75,00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $ 35,00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
Come in un join interno, la relazione tra le chiavi principali delle tabelle è many_to_one. Tuttavia, il record nullo aggiunto impone la necessità di aggregati simmetrici anche nella tabella a sinistra. Pertanto, devi modificare il parametro relationship in many_to_many, perché l'esecuzione di questa unione interrompe i conteggi nella tabella a sinistra.
Se questo esempio fosse stato un join esterno sinistro, la riga con valore null non sarebbe stata aggiunta e il record cliente aggiuntivo sarebbe stato eliminato. In questo caso, il rapporto sarà comunque many_to_one. Si tratta del valore predefinito di Looker perché si presume che la tabella di base definisca l'analisi. In questo caso, stai analizzando gli ordini, non i clienti. Se la tabella dei clienti fosse a sinistra, la situazione sarebbe diversa.
Unioni multilivello
In alcune esplorazioni, la tabella di base si unisce a una o più viste che, a loro volta, devono essere unite a una o più viste aggiuntive. Nell'esempio riportato di seguito, ciò significa che una tabella verrà unita alla tabella dei clienti. In questi casi, è meglio esaminare solo la singola unione scritta durante la valutazione del parametro relationship. Looker comprende quando una distribuzione in cascata a valle influisce su una query anche se la visualizzazione interessata non è nella join che ha effettivamente creato la distribuzione.
In che modo Looker mi aiuta?
In Looker sono disponibili meccanismi per garantire che il valore della relazione sia corretto. Uno è un controllo dell'univocità della chiave primaria. Ogni volta che è presente un fanout e sono necessari aggregati simmetrici per calcolare una misura, Looker controlla l'unicità della chiave primaria utilizzata. Se non è univoco, verrà visualizzato un errore al momento dell'esecuzione della query (tuttavia, non esiste un errore dello strumento di convalida di LookML per questo).
Inoltre, se non è possibile per Looker gestire un fanout (in genere perché non è indicata una chiave primaria), non verranno visualizzate misure nell'esplorazione da quella visualizzazione. Per correggere il problema, è sufficiente designare un campo come chiave primaria per consentire alle misure di essere inserite nell'esplorazione.
Aspetti da considerare
Supporto dei dialetti per gli aggregati simmetrici
Looker può connettersi ad alcuni dialetti che non supportano gli aggregati simmetrici. Puoi visualizzare un elenco dei dialetti e del relativo supporto per gli aggregati simmetrici nella pagina della documentazione di symmetric_aggregates.
Caso speciale
La sezione Unione interna all'inizio di questa pagina afferma che, per determinare il valore corretto della relazione, devi esaminare il campo o i campi presenti nella clausola sql_on della tabella a sinistra: "Se il campo o i campi formano la chiave primaria della tabella a sinistra, puoi modificare il lato sinistro del parametro relationship in one. In caso contrario, in genere deve rimanere many". Questo vale a meno che la tabella non contenga più colonne che non contengono record ripetuti. In questo caso, puoi trattare qualsiasi colonna di questo tipo come se fosse una chiave primaria durante la formulazione della relazione, anche se non è la colonna designata primary_key: yes.
Può essere utile assicurarsi che sia presente una sorta di regola software che garantisca che l'affermazione nel paragrafo precedente rimanga sempre vera per la colonna che hai designato. In questo caso, trattala come tale e prendi nota della sua proprietà speciale nel file della vista per consentire ad altri di farvi riferimento in futuro (completa con il link a SQL Runner per dimostrarlo). Tieni presente, però, che Looker confermerà la verità dell'unicità implicita quando un campo è designato come chiave primaria, ma non farà lo stesso per gli altri campi. Non verrà semplicemente invocato l'algoritmo degli aggregati simmetrici.