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 i join interni ed esterni, consulta l'articolo di w3schools sui join 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 poter sfruttare l'algoritmo è necessario eseguire due operazioni correttamente: le chiavi primarie devono essere accurate in ogni vista che contiene 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 capire 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) che scegli 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 richiamare gli aggregati simmetrici quando il join viene scritto in una query SQL. Un possibile approccio in questo caso sarebbe dire a Looker di richiamarli sempre, il che produrrà sempre risultati accurati. Tuttavia, ciò ha un costo in termini di prestazioni, quindi è meglio utilizzare gli aggregati simmetrici con accortezza.
La procedura per determinare il valore corretto è leggermente diversa tra le unioni interne ed esterne.
Inner join
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 |
Supponi di avere anche una tabella di clienti con 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. Questo join verrà rappresentato 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 questo join 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 di quella 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:
- Per iniziare, scrivi
many_to_manycome relazione. Finché le chiavi primarie sono corrette, questo produrrà sempre risultati accurati, perché Looker attiverà sempre l'algoritmo di aggregazione simmetrica e applicherà l'accuratezza. Tuttavia, poiché l'algoritmo complica le query e aggiunge tempo di esecuzione, è utile provare a modificare uno o entrambi i lati inoneanzichémany. - Esamina il campo o i campi nella clausola
sql_ondella tabella a sinistra. Se uno o più campi costituiscono 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 uno o più campi costituiscono la chiave primaria della tabella a destra, puoi modificare il lato destro inone.
È buona prassi 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.
Outer join
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é i left outer join 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. Di conseguenza, devi modificare il parametro relationship in many_to_many, perché l'esecuzione di questo join interrompe i conteggi nella tabella a sinistra.
Se questo esempio fosse un left outer join, la riga null non sarebbe stata aggiunta e il record del cliente in più sarebbe stato eliminato. In questo caso, la relazione sarà comunque many_to_one. Questa è l'impostazione predefinita 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 sulla 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. In questo esempio, ciò significa che una tabella viene unita alla tabella dei clienti. In questi casi, è meglio esaminare solo la singola unione scritta durante la valutazione del parametro relationship. Looker comprenderà quando un fanout downstream interessa una query anche se la vista interessata non si trova nel join che ha effettivamente creato il fanout.
In che modo Looker mi aiuta?
In Looker esistono meccanismi per garantire che il valore della relazione sia corretto. Uno è il controllo dell'univocità della chiave primaria. Ogni volta che è presente un fanout e sono necessari aggregati simmetrici per calcolare una misura, Looker verifica 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 Looker non è in grado di gestire un fan-out (di solito perché non viene indicata alcuna chiave primaria), in quella vista non verrà visualizzata alcuna misura nell'esplorazione. 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 un many." Ciò è vero 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 attiva una sorta di regola software che garantisca che l'affermazione nel paragrafo precedente rimanga sempre vera per la colonna designata. In tal caso, procedi come tale e prendi nota della sua proprietà speciale nel file di visualizzazione in modo che altri utenti possano farvi riferimento in futuro (completa con il link SQL Runner per dimostrarlo). Tuttavia, tieni presente che Looker confermerà la verità dell'univocità implicita quando un campo viene designato come chiave primaria, ma non funzionerà nello stesso modo per altri campi. Semplicemente non richiama l'algoritmo degli aggregati simmetrici.