Se provi a Looker in un background SQL, probabilmente ti interesserà sapere in che modo Looker genera SQL. In pratica, Looker è uno strumento che genera query SQL e le invia tramite una connessione di database. Looker formula query SQL basate su un progetto LookML che descrive la relazione tra tabelle e colonne nel database. Comprendere il modo in cui Looker genera le query ti consentirà di comprendere meglio come il codice LookML si traduce in query SQL efficienti.
Ogni parametro LookML controlla alcuni aspetti del modo in cui Looker genera SQL, modificando la struttura, i contenuti o il comportamento della query. Questa pagina descrive i principi di SQL di Looker, ma non tratta in dettaglio tutti gli elementi LookML. Per informazioni dettagliate, consulta la pagina della documentazione relativa al riferimento LookML.
Visualizzazione della query
In un look o in un'esplorazione salvati, puoi utilizzare la scheda SQL nella sezione Dati per vedere i dati inviati da Looker al database per recuperare i dati. Puoi anche usare i link in basso per visualizzare la query in SQL Runner o per visualizzare il piano esplicativo del database. Per scoprire di più su SQL Runner, consulta la pagina della documentazione relativa alle nozioni di base su SQL Runner. Per ulteriori informazioni sull'ottimizzazione di una query utilizzando SQL Runner, consulta l'articolo del Centro assistenza Come ottimizzare SQL con EXPLAIN.
Forma canonica di una query di Looker
Le query SQL di Looker hanno sempre il seguente formato.
SELECT
<dimension>, <dimension>, ...
<measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>
Il progetto LookML definisce tutte le dimensioni, le misure, le esplorazioni e le visualizzazioni a cui si fa riferimento nella formula sopra. Le espressioni di filtro sono specificate dall'utente in Looker per definire le query ad hoc. Le espressioni di filtro possono essere dichiarate anche direttamente in LookML per essere applicate a tutte le query.
Componenti di base di una query di Looker
Tutte le query di Looker sono rappresentate da questi parametri fondamentali applicati a un progetto LookML, come illustrato nella formula precedente.
Looker utilizza i seguenti parametri per generare una query SQL completa:
model
: il nome del modello LookML da scegliere come target, che specifica il database di destinazioneexplore
: il nome della query to Explore che compila la clausolaFROM
di SQL- Campi: i parametri
dimension
emeasure
da includere nella query, che compilano la clausola SQLSELECT
filter
: espressioni di filtro Looker da applicare a zero o più campi, che completano le clausole SQLWHERE
eHAVING
- Ordinare: il campo in base al quale ordinare e l'ordine in cui viene compilata la clausola SQL
ORDER BY
.
Questi parametri sono proprio gli elementi specificati da un utente durante la creazione di una query nella pagina Explore (Esplora) di Looker. Questi stessi elementi vengono visualizzati in tutte le modalità di esecuzione delle query con Looker: nell'SQL generato, nell'URL che rappresenta la query, nell'API Looker e così via.
Cosa sono le viste specificate dalle clausole LEFT JOIN
? Le clausole JOIN
vengono compilate in base alla struttura del modello LookML, che specifica come le visualizzazioni si uniscono alle esplorazioni. Quando crei query SQL, Looker include clausole JOIN
solo quando necessario. Quando gli utenti creano una query in Looker, non devono specificare il modo in cui le tabelle vengono unite, poiché queste informazioni sono codificate nel modello: uno dei vantaggi più potenti di Looker per gli utenti aziendali.
Una query di esempio e il codice SQL risultante
Costruiamo una query in Looker per dimostrare come viene generata la query in base al pattern riportato sopra. Valuta un negozio di e-commerce con tabelle per monitorare gli utenti e gli ordini. I campi e le relazioni tra le tabelle sono visualizzati di seguito.
Troviamo il numero di ordini (Numero ordini) raggruppati per stato (Stato UTENTI) e filtrati in base alla data di creazione dell'ordine (Data creazione ordini).
Di seguito è visualizzato il risultato della query nella pagina Explore di Looker.
Se fai clic sulla scheda SQL, vengono visualizzate le query SQL generate ed eseguite da Looker.
Osserva la somiglianza con la formula canonica riportata sopra. SQL di Looker mostra alcuni tratti del codice generato automaticamente (ad es. COALESCE(users.state,'') AS "_g1"
), ma si adatta sempre alla formula.
SELECT
<dimension>,<dimension>,...
<measure>,<measure>,...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>,<dimension>,<dimension>,...
ORDER BY <dimension> | <measure>
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
LIMIT <limit>
Prova altre query in Looker per dimostrare che la struttura delle query è sempre la stessa.
Esecuzione di SQL non elaborato in SQL Runner di Looker
Looker include una funzionalità denominata SQL Runner che ti consente di eseguire il codice SQL che preferisci con le connessioni ai database configurate in Looker.
Dal momento che ogni query generata da Looker ha come risultato un comando SQL completo e funzionale, puoi utilizzare SQL Runner per indagare o eseguire l'analisi.
Le query SQL non elaborate eseguite in SQL Runner generano lo stesso set di risultati
Se l'SQL contiene errori, SQL Runner evidenzia la posizione del primo errore nel comando SQL e include la posizione dell'errore nel messaggio di errore.
Esaminare i componenti della query nell'URL
Dopo aver eseguito una query in Looker, puoi esaminare l'URL di condivisione espanso per visualizzare i componenti fondamentali di una query di Looker. Inizia selezionando Condividi dal menu a forma di ingranaggio Esplora:
Se inizi in Look, fai clic sul link Esplora da qui di Look per aprire la query in un'esplorazione.
A questo punto, viene visualizzata la finestra Condividi URL, che mostra l'URL espanso:
Ad esempio, la query precedente restituisce il seguente URL di condivisione espansa:
https://docsexamples.dev.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
L'URL fornisce informazioni sufficienti per ricreare la query:
modello | e_thelook |
esplora | events |
per eseguire query e visualizzare | fields=users.state,users.count |
ordina campo | sorts=users.count+desc |
filtrare campi e valori | f[users.created_year]=2020 |
In che modo Looker struttura le connessioni
Nella query SQL sopra, puoi notare che l'esplorazione orders
è presente nella clausola FROM
principale e le viste unite nelle clausole LEFT JOIN
. Le unioni di Looker possono essere scritte in molti modi diversi, spiegate in modo più dettagliato nella pagina Utilizzo delle unioni in LookML.
I blocchi SQL specificano clausole SQL personalizzate
Non tutti gli elementi di una query di Looker sono generati automaticamente. A un certo punto, il modello dei dati deve fornire dettagli specifici affinché Looker possa accedere alle tabelle sottostanti e calcolare i valori derivati. In LookML, i blocchi SQL sono snippet di codice SQL forniti dal modello dei dati, che Looker utilizza per sintetizzare le espressioni SQL complete.
Il parametro di blocco SQL più comune è sql
, utilizzato nelle definizioni di dimensione e misurazione. Il parametro sql
specifica una clausola SQL che fa riferimento a una colonna sottostante o esegue una funzione aggregata. In generale, tutti i parametri LookML che iniziano con sql_
prevedono un'espressione SQL di qualche tipo. Ad esempio: sql_always_where
, sql_on
e sql_table_name
. La documentazione di riferimento LookML fornisce dettagli su ciascun parametro.
Esempi di blocchi SQL per dimensioni e misure
Di seguito sono riportati alcuni esempi di blocchi SQL per dimensioni e misure. L'operatore di sostituzione LookML ($) fa sì che queste dichiarazioni sql
appaiano in modo ingannevole a differenza di SQL. Tuttavia, dopo la sostituzione, la stringa risultante è SQL puro, che Looker inserisce nella clausola SELECT
della query.
dimension: id {
primary_key: yes
sql: ${TABLE}.id ;; # Specify the primary key, id
}
measure: average_cost {
type: average
value_format: "0.00"
sql: ${cost} ;; # Specify the field that you want to average
# The field 'cost' is declared elsewhere
}
dimension: name {
sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
type: number
sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}
Come mostrato nelle ultime due dimensioni precedenti, i blocchi SQL possono utilizzare funzioni supportate dal database sottostante (ad esempio, le funzioni MySQL CONCAT
e DATEDIFF
). Il codice utilizzato nei blocchi SQL deve corrispondere al dialetto SQL utilizzato dal database.
Esempio di blocco SQL per le tabelle derivate
Le tabelle derivate utilizzano anche un blocco SQL per specificare la query che deriva la tabella. Di seguito è riportato un esempio:
view: user_order_facts {
derived_table: {
sql:
SELECT
user_id
, COUNT(*) as lifetime_orders
FROM orders
GROUP BY 1 ;;
}
# later, dimension declarations reference the derived column(s)…
dimension: lifetime_orders {
type: number
}
}
Blocco SQL di esempio per filtrare un'esplorazione
I parametri LookML sql_always_where
e sql_always_having
consentono di limitare i dati disponibili per una query inserendo un blocco SQL nelle clausole SQL WHERE o HAVING. In questo esempio, l'operatore di sostituzione LookML ${view_name.SQL_TABLE_NAME}
viene utilizzato per fare riferimento a una tabella derivata:
explore: trips {
view_label: "Long Trips"
# This will ensure that we only see trips that are longer than average!
sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}