Se utilizzi Looker dopo aver utilizzato SQL, probabilmente ti starai chiedendo in che modo Looker genera SQL. Fondamentalmente, Looker è uno strumento che genera query SQL e le invia a una connessione al database. Looker formula query SQL in base a un progetto LookML che descrive la relazione tra tabelle e colonne nel database. Comprendendo in che modo Looker genera query, potrai capire meglio in che modo il codice LookML viene tradotto in query SQL efficienti.
Ogni parametro LookML controlla un aspetto del modo in cui Looker genera SQL, modificando la struttura, i contenuti o il comportamento della query. Questa pagina descrive i principi di come Looker genera SQL, ma non tratta in dettaglio tutti gli elementi di LookML. La pagina di documentazione Riferimento rapido di LookML è un buon punto di partenza per informazioni sui parametri di 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 recuperare 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 vedere il piano di spiegazione del database per la query.
Per saperne di più su SQL Runner, consulta la pagina della documentazione Nozioni di base su SQL Runner. Per saperne di più sull'ottimizzazione di una query utilizzando SQL Runner, consulta il post della scheda Community 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 viene fatto riferimento nella query SQL. Le espressioni di filtro vengono specificate in Looker dall'utente per creare query ad hoc. Le espressioni di filtro possono essere dichiarate anche direttamente in LookML per essere applicate a tutte le query.
Componenti fondamentali di una query di Looker
Tutte le query di Looker sono rappresentate da questi parametri fondamentali applicati a un progetto LookML, come mostrato nella query di esempio precedente.
Looker utilizza i seguenti parametri per generare una query SQL completa:
model: il nome del modello LookML di destinazione, che specifica il database di destinazioneexplore: il nome dell'esplorazione da sottoporre a query, che compila la clausolaFROMSQL- Campi: i parametri
dimensionemeasureda includere nella query, che compilano la clausolaSELECTdi SQL filter: espressioni di filtro di Looker da applicare a zero o più campi, che compilano le clausoleWHEREeHAVINGdi SQL- Ordine di ordinamento: il campo in base al quale eseguire l'ordinamento e l'ordine di ordinamento, che compila la clausola
ORDER BYdi SQL
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 delle query con Looker, ad esempio nel codice SQL generato, nell'URL che rappresenta la query e nell'API Looker.
Che dire delle 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 si uniscono alle esplorazioni. Quando crea query SQL, Looker include le clausole JOIN solo se necessario. Quando gli utenti creano una query in Looker, non devono specificare come vengono unite le tabelle, perché queste informazioni sono codificate nel modello, uno dei vantaggi più importanti di Looker per gli utenti aziendali.
Una query di esempio e il relativo codice SQL
Creiamo una query in Looker per dimostrare come viene generata in base al pattern precedente. Prendiamo ad esempio un negozio di e-commerce con un database con due tabelle, orders e users, per monitorare gli utenti e gli 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) |
Cerchiamo il numero di ordini (Conteggio ordini) raggruppati per stato (Stato utente) e filtrati in base alla data di creazione dell'ordine (Data creazione ordini) in un'esplorazione di Looker.
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
Tieni presente la somiglianza con la formula di query canonica. Il codice SQL di Looker presenta alcuni tratti del codice generato automaticamente (ad esempio COALESCE(users.state,'') AS "_g1"), ma si adatta sempre alla formula.
Fai esperimenti con altre query in Looker per verificare che la struttura della query sia sempre la stessa.
Eseguire SQL non elaborato in SQL Runner di Looker
Looker include una funzionalità chiamata SQL Runner che ti consente di eseguire qualsiasi comando 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 esaminare o utilizzare la query.
Le query SQL non elaborate eseguite in SQL Runner producono lo stesso insieme di risultati. Se il codice SQL contiene errori, SQL Runner evidenzierà la posizione del primo errore nel comando SQL e includerà la posizione dell'errore nel messaggio di errore.
Esaminare i componenti della query nell'URL espanso
Dopo aver eseguito una query in Looker, puoi esaminare l'URL espanso per visualizzare i componenti fondamentali di una query di Looker. Per iniziare, seleziona 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
| modello | e_thelook |
| esplorazione | events |
| campi su cui eseguire query e da visualizzare | fields=users.state,users.count |
| campo di ordinamento e ordine | sorts=users.count+desc |
| campi e valori dei filtri | f[users.created_year]=2020 |
Come Looker struttura le unioni
Nella query di esempio precedente, tieni presente che l'esplorazione orders viene visualizzata nella clausola FROM principale e le visualizzazioni unite nelle clausole LEFT JOIN. Le unioni di Looker possono essere scritte in molti modi diversi, come spiegato più dettagliatamente nella pagina Utilizzare le 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 di dati deve fornire dettagli specifici per consentire a Looker di accedere alle tabelle sottostanti e calcolare i valori derivati. In LookML, i blocchi SQL sono snippet di codice SQL forniti dal modellatore dei dati, che vengono utilizzati da Looker per sintetizzare 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 di aggregazione. In genere, tutti i parametri LookML che iniziano con sql_ richiedono 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 pagina Documentazione di riferimento di LookML.
Blocchi SQL di esempio per dimensioni e misure
Il seguente esempio di codice fornisce alcuni esempi di blocchi SQL per dimensioni e misure. L'operatore di sostituzione LookML ($) fa sembrare queste dichiarazioni sql ingannevolmente diverse da 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 di questo esempio, i blocchi SQL possono utilizzare le funzioni supportate dal database sottostante (ad esempio le funzioni MySQL CONCAT e DATEDIFF in questo caso). Il codice utilizzato nei blocchi SQL deve corrispondere al dialetto SQL utilizzato dal database.
Blocco SQL di esempio per le tabelle derivate
Anche le tabelle derivate utilizzano un blocco SQL per specificare la query da cui viene derivata la tabella. Questo è un esempio di tabella derivata basata su SQL:
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 ti consentono di limitare i dati disponibili per una query inserendo un blocco SQL nelle clausole WHERE o HAVING di SQL. 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});;
}