Se apprendi Looker con un background di SQL, probabilmente ti interessa scoprire come Looker genera SQL. Fondamentalmente, Looker è uno strumento che genera query SQL e le invia tramite una connessione al database. Looker formula query SQL in base a un progetto LookML che descrive la relazione tra tabelle e colonne del database. Comprendere il modo in cui Looker genera le query ti consentirà di capire meglio in che modo il tuo codice LookML si traduce in query SQL efficienti.
Ogni parametro LookML controlla alcuni aspetti del modo in cui Looker genera SQL, alterando la struttura, il contenuto o il comportamento della query. Questa pagina descrive i principi su come Looker genera SQL, ma non tratta in dettaglio tutti gli elementi LookML. La pagina della documentazione Riferimento rapido per LookML è un buon punto di partenza per informazioni sui parametri LookML.
Visualizzazione della query
In un Look salvato o in un'esplorazione, puoi utilizzare la scheda SQL nel riquadro Dati per vedere cosa invia Looker al database per ottenere i dati. Puoi anche utilizzare i link Apri in SQL Runner e Spiega in SQL Runner nella parte inferiore della scheda SQL per visualizzare la query in SQL Runner o per consultare il piano esplicativo del database per la query.
Per saperne di più su SQL Runner, consulta la pagina della documentazione relativa alle nozioni di base di SQL Runner. Per ulteriori informazioni sull'ottimizzazione di una query utilizzando SQL Runner, consulta il post della community Come ottimizzare SQL con EXPLAIN.
Forma canonica di una query di Looker
Le query SQL di Looker assumono sempre il formato seguente.
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 viste a cui viene fatto riferimento nella query SQL. Le espressioni di filtro vengono specificate in Looker dall'utente per definire query ad hoc. Le espressioni di filtro possono anche essere dichiarate 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 mostrato nella precedente query di esempio.
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 dell'esplorazione su cui eseguire la query, che compila la clausola SQLFROM
- Campi: i parametri
dimension
emeasure
da includere nella query, che compilano la clausola SQLSELECT
filter
: espressioni di filtro di Looker da applicare a zero o più campi che completano le clausole SQLWHERE
eHAVING
.- Ordinamento: il campo in base al quale ordinare e l'ordinamento che compila la clausola SQL
ORDER BY
Questi parametri sono esattamente gli elementi che un utente specifica quando crea una query nella pagina Esplora di Looker. Questi stessi elementi vengono visualizzati in tutte le modalità di esecuzione di query con Looker: nell'SQL generato, nell'URL che rappresenta la query, nell'API Looker e così via.
E le viste specificate dalle clausole LEFT JOIN
? Le clausole JOIN
vengono compilate in base alla struttura del modello LookML, che specifica in che modo le viste vengono unite alle esplorazioni. Durante la creazione di query SQL, Looker include clausole JOIN
solo quando richiesto. Quando gli utenti creano una query in Looker, non devono specificare in che modo le tabelle vengono unite, perché queste informazioni sono codificate nel modello, uno dei vantaggi più importanti di Looker per gli utenti aziendali.
Una query di esempio e il codice SQL risultante
Creiamo una query in Looker per dimostrare in che modo viene generata in base al pattern precedente. Prendi in considerazione un negozio di e-commerce che dispone di un database con due tabelle, ordini e utenti, per monitorare utenti e ordini.
orders |
---|
id INT |
created_at DATETIME |
users_id INT |
status VARCHAR(255) |
traffic_source VARCHAR(15) |
users |
---|
id INT |
email VARCHAR(255) |
first_name VARCHAR(255) |
last_name VARCHAR(255) |
created_at DATETIME |
zip INT |
country VARCHAR(255) |
state VARCHAR(255) |
city VARCHAR(255) |
age INT |
traffic_source VARCHAR(15) |
In un'esplorazione di Looker, troviamo il numero di ordini (ORDERS Count) raggruppati per stato (USERS State) e filtrati in base alla data di creazione dell'ordine (ORDERS Created Date).
Per visualizzare la query SQL generata ed eseguita da Looker, fai clic sulla scheda SQL nel riquadro Dati.
SELECT COALESCE(users.state, ' ') AS "_g1",
users.state AS 'users.state',
COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id
WHERE
orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500
Nota la somiglianza con la formula di query canonica. L'SQL di Looker mostra alcuni tratti del codice generato automaticamente (ad esempio COALESCE(users.state,'') AS "_g1"
), ma corrisponde sempre alla formula.
Sperimenta con più query in Looker per dimostrare a te stesso che la struttura delle query è sempre la stessa.
Esecuzione di SQL non elaborato in SQL Runner di Looker
Looker include una funzionalità chiamata SQL Runner, che ti consente di eseguire qualsiasi codice SQL sulle connessioni al database che hai configurato in Looker.
Poiché ogni query generata da Looker genera un comando SQL completo e funzionale, puoi utilizzare SQL Runner per analizzare o utilizzare la query.
Le query SQL non elaborate che vengono eseguite in SQL Runner producono lo stesso set di risultati. Se l'SQL contiene errori, SQL Runner evidenzierà la posizione del primo errore nel comando SQL e includerà la posizione dell'errore nel messaggio di errore.
Esame dei componenti della query nell'URL espanso
Dopo aver eseguito una query in Looker, puoi esaminare l'URL espanso per vedere i componenti di base di una query di Looker. Inizia selezionando Condividi dal menu a forma di ingranaggio di Esplora per aprire il menu Condividi URL.
L'URL espanso fornisce informazioni sufficienti per ricreare la query. Ad esempio, questo esempio di URL espanso fornisce le seguenti informazioni:
https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
model | e_thelook |
esplorazione | events |
campi da interrogare e visualizzare | fields=users.state,users.count |
Ordina campo e ordine | sorts=users.count+desc |
campi e valori filtro | f[users.created_year]=2020 |
In che modo le strutture Looker si uniscono
Nella query di esempio precedente, nota che l'esplorazione orders
viene visualizzata nella clausola FROM
principale e le viste unite vengono visualizzate nelle clausole LEFT JOIN
. I join di Looker possono essere scritti in molti modi diversi, come spiegato più dettagliatamente nella pagina Utilizzo dei join 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 creatore di dati, che Looker utilizza per sintetizzare le espressioni SQL complete.
Il parametro di blocco SQL più comune è sql
, utilizzato nelle definizioni di dimensioni e misure. Il parametro sql
specifica una clausola SQL per fare riferimento a una colonna sottostante o per eseguire 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
. Per ulteriori informazioni su ciascun parametro, consulta la documentazione di riferimento a LookML.
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 apparire queste dichiarazioni sql
in modo ingannevole rispetto a SQL. Tuttavia, una volta eseguita la sostituzione, la stringa risultante è SQL pura, 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 in questo esempio, i blocchi SQL possono utilizzare funzioni supportate dal database sottostante (come le funzioni MySQL CONCAT
e DATEDIFF
in questo caso). Il codice che utilizzi nei blocchi SQL deve corrispondere al dialetto SQL utilizzato dal database.
Blocco SQL di esempio per le tabelle derivate
Le tabelle derivate utilizzano anche un blocco SQL per specificare la query che genera 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});;
}