Questa pagina è scritta per chiunque tenti di utilizzare LookML per creare un'esplorazione in Looker. La pagina sarà più facile da capire se conosci bene l'SQL, in particolare se comprendi la differenza tra join interno ed esterno. Per una spiegazione concisa delle differenze tra i join interno ed esterno, consulta questo articolo di w3schools sui join SQL.
Looker ha la capacità di essere un potente motore SQL per la tua azienda. La modellazione astratta in LookML consente ai team IT e dedicati ai dati di creare regole generali sempre vere, consentendo ai business analyst di creare query in libertà che sono sempre corrette, anche se il team dedicato ai dati non ha mai previsto la loro necessità. Il driver principale di questa funzionalità è l'algoritmo di aggregazione simmetrica, che risolve un problema a livello di settore con i join SQL. Tuttavia, per poter utilizzare l'algoritmo, è necessario eseguire due operazioni: le chiavi primarie devono essere precise in ogni vista che contiene una misura (in genere tutte) e i parametri relationship devono essere corretti in ogni join.
Chiavi primarie
Per molti versi, comprendere la chiave primaria di una tabella equivale fondamentalmente a capire che cos'è la tabella e come utilizzarla. 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 primarie, puoi determinare il valore corretto del parametro relationship del join. Lo scopo del parametro relationship è indicare a Looker se richiamare i dati aggregati simmetrici quando il join viene scritto in una query SQL. Un possibile approccio potrebbe essere dire a Looker di invocarli sempre, il che produrrebbe sempre risultati accurati. Tuttavia, poiché ciò ha un costo in termini di prestazioni, è preferibile utilizzare i dati aggregati simmetrici con giudizio.
Il processo per determinare il valore corretto è leggermente diverso tra i join interno ed esterno.
Join interni
Ad esempio, supponiamo che tu abbia una tabella di ordini con 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 | Beatrice | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
Puoi unire queste tabelle nel campo customer_id, presente in entrambe le tabelle. Questo join viene 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 | Beatrice | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
La relazione many_to_one in questo caso si riferisce al numero di volte in cui un valore del campo join (customer_id) è rappresentato in ogni tabella. Nella tabella orders (quella a sinistra), un singolo ID cliente è rappresentato molte volte (in questo caso, si tratta del cliente con l'ID 1, presente in più righe).
Nella tabella customers (quella a destra), ogni ID cliente è rappresentato una sola volta, poiché customer_id è la chiave primaria di quella tabella. Di conseguenza, 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.
Puoi seguire questi passaggi per determinare il valore della relazione corretto in modo programmatico controllando le chiavi primarie:
- Inizia scrivendo
many_to_manycome relazione. Se le chiavi primarie sono corrette, i risultati saranno sempre precisi 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 cambiare uno o entrambi i lati inoneanziché inmany. - Dai un'occhiata al campo o ai campi presenti nella clausola
sql_ondella tabella a sinistra. Se il campo o i 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. - Ora controlla il campo o i campi che rappresentano la tabella a destra nella clausola
sql_on. Se il campo o i campi costituiscono la chiave primaria della tabella destra, puoi modificare il lato destro inone.
La best practice prevede di scrivere la frase sql_on iniziando con la tabella sinistra, rappresentata sul lato sinistro del segno uguale, e la tabella 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 tuo 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 distinguere più facilmente a colpo d'occhio quale tabella esistente nell'esplorazione stai per unire la nuova tabella.
join esterni
Per i join esterni, devi anche tenere presente che potrebbe verificarsi un fanout quando durante il join vengono aggiunti record nulli. 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 la misurazione di type: count. Se questa operazione non è corretta, i record null verranno conteggiati (il che è indesiderato).
In un outer join completo, è possibile aggiungere record null a entrambe le tabelle se nella relativa chiave di join mancano valori esistenti nell'altra tabella. Ciò è illustrato nell'esempio seguente, che riguarda una tabella orders:
| order_id | quantità | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
Ad esempio, supponiamo di avere anche la seguente tabella customers:
| customer_id | first_name | last_name | visite |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Beatrice | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
Una volta unite queste tabelle, 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 | Beatrice | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
Proprio come in un join interno, la relazione tra le chiavi primarie delle tabelle è many_to_one. Tuttavia, il record null 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 questa unione interrompe i conteggi nella tabella a sinistra.
Se questo esempio fosse stato un left outer join, la riga null non sarebbe stata aggiunta e il record del cliente aggiuntivo sarebbe stato eliminato. In questo caso, la relazione sarà comunque many_to_one. Questa è l'impostazione predefinita di Looker perché si presume che sia la tabella di base a definire l'analisi. In questo caso, stai analizzando gli ordini, non i clienti. Se il tavolo dei clienti fosse a sinistra, la situazione sarebbe diversa.
Join multilivello
In alcune esplorazioni, la tabella di base viene unita a una o più viste che, a loro volta, devono essere unite a una o più viste aggiuntive. Nell'esempio qui riportato, una tabella verrebbe unita a quella clienti. In queste situazioni, è preferibile osservare solo il singolo join scritto durante la valutazione del parametro relationship. Looker è in grado di capire quando un fanout downstream influisce su una query anche se la visualizzazione interessata non fa parte del join che ha effettivamente creato il fanout.
In che modo Looker mi aiuta?
In Looker esistono meccanismi che aiutano a garantire che il valore della relazione sia corretto. Uno è il controllo dell'unicità della chiave primaria. Ogni volta che è presente un fanout e sono necessari aggregati simmetrici per calcolare una misura, Looker controlla l'univocità della chiave primaria utilizzata. Se non è univoco, verrà visualizzato un errore in fase di esecuzione della query (tuttavia, per questo non è presente alcun errore di convalida LookML).
Inoltre, se Looker non può gestire un fanout (di solito perché non viene indicata alcuna chiave primaria), nell'esplorazione da quella visualizzazione non apparirà alcuna misura. Per risolvere il problema, è sufficiente designare un campo come chiave primaria per consentire alle tue misure di entrare nell'esplorazione.
Aspetti da considerare
Supporto del dialetto per gli aggregati simmetrici
Looker può connettersi con alcuni dialetti che non supportano gli aggregati simmetrici. Puoi visualizzare un elenco dei dialetti e il loro supporto per i dati aggregati simmetrici nella pagina della documentazione di symmetric_aggregates.
Caso speciale
La sezione Inner join di questa pagina indica che, per determinare il valore di relazione corretto, devi esaminare il campo o i campi che si trovano nella clausola sql_on della tabella a sinistra: "Se il campo o i campi costituiscono la chiave primaria della tabella a sinistra, puoi modificare il lato sinistro del parametro relationship in one. In caso contrario, di solito deve rimanere come many". Ciò è vero, a meno che la tua tabella non contenga più colonne in cui non sono presenti 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 esista un tipo di regola del software che garantisca che l'affermazione nel paragrafo precedente rimanga sempre vera per la colonna designata. In tal caso, procedi e consideralo come tale e prendi nota della sua proprietà speciale nel file di visualizzazione per fare riferimento ad altri utenti in futuro (completa il link SQL Runner per dimostrarlo). Tieni presente, tuttavia, che Looker confermerà l'autenticità dell'univocità implicita quando un campo viene designato come chiave primaria, ma non per gli altri campi. Semplicemente non richiama l'algoritmo degli aggregati simmetrici.