In che modo Looker genera SQL

Se provi a utilizzare Looker da uno sfondo SQL, probabilmente ti interessa sapere come Looker genera SQL. In pratica, Looker è uno strumento che genera query SQL e le invia a fronte di una connessione al database. Looker formula le query SQL in base a un progetto LookML che descrive la relazione tra tabelle e colonne nel database. Comprendere il modo in cui Looker genera le query permette di comprendere meglio come il codice LookML si traduce in query SQL efficienti.

Ogni parametro LookML controlla alcuni aspetti di come Looker genera SQL, modificando la struttura, i contenuti o il comportamento della query. In questa pagina vengono descritti i principi di come Looker genera SQL, ma non tratta in dettaglio tutti gli elementi LookML. Per informazioni sui parametri LookML, consulta la pagina della documentazione Riferimento rapido LookML.

Visualizzazione della query

In un Look o in un'esplorazione salvati, puoi utilizzare la scheda SQL nella sezione Dati per vedere cosa invia Looker al database per recuperare i dati. Puoi anche utilizzare i link in basso per visualizzare la query in SQL Runner o per visualizzare il piano dettagliato del database per la query. Per ulteriori informazioni 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, vedi il post della scheda How to optimize SQL with EXPLAIN.

Forma canonica di una query di Looker

Le query SQL di Looker hanno sempre la seguente forma.

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 si fa riferimento nella formula riportata sopra. Le espressioni di filtro sono specificate in Looker dall'utente per dare forma alle query ad hoc. Le espressioni di filtro possono anche essere dichiarate direttamente nel LookML per essere applicate a tutte le query.

Componenti di base di una query Looker

Tutte le query di Looker sono rappresentate da questi parametri fondamentali applicati a un progetto LookML, come mostrato 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 destinazione
  • explore: il nome della query Explore (Esplora) in cui viene compilata la clausola SQL FROM
  • Campi: i parametri dimension e measure da includere nella query, che completano la clausola SQL SELECT
  • filter: espressioni di filtro Looker da applicare a zero o più campi, che completano le clausole SQL WHERE e HAVING
  • Ordina la query: il campo in base al quale effettuare l'ordinamento e all'ordinamento, che completa 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 delle query con Looker: nell'SQL generato, nell'URL che rappresenta la query, nell'API Looker e così via.

Le visualizzazioni specificate dalle clausole LEFT JOIN? Le clausole JOIN vengono compilate in base alla struttura del modello LookML, che specifica il modo in cui le viste 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 si uniscono, perché queste informazioni vengono codificate nel modello. Si tratta di uno dei vantaggi più potenti offerti da Looker per gli utenti aziendali.

Una query di esempio e il codice SQL risultante

Creiamo una query in Looker per dimostrare come viene generata secondo il pattern sopra indicato. Considera un negozio di e-commerce con tabelle per monitorare gli utenti e gli ordini. I campi e le relazioni nella tabella sono visualizzati di seguito.

Cerchiamo il numero di ordini (ORDERS Count) raggruppati per stato (UTENTI) e filtrati in base alla data di creazione dell'ordine (ORDERS Created Date).

Di seguito è riportato il risultato della query nella pagina Explore (Esplora) di Looker.

Se fai clic sulla scheda SQL, viene mostrato l'SQL generato ed eseguito da Looker.

Nota la somiglianza con la formula canonica di cui sopra. SQL di Looker presenta 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>

Sperimenta con altre query in Looker per dimostrare a te stesso che la struttura delle query è sempre la stessa.

Esecuzione di codice SQL non elaborato nell'SQL Runner di Looker

Looker include una funzionalità denominata SQL Runner in cui puoi eseguire qualsiasi SQL che preferisci 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 indagare o giocare con la query.

Le query SQL non elaborate eseguite in SQL Runner generano lo stesso set di risultati

Se 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 di query nell'URL

Dopo aver eseguito una query in Looker, puoi esaminare l'URL di condivisione espansa per vedere i componenti di base di una query di Looker. Inizia selezionando Condividi dal menu a forma di ingranaggio di Esplora:

Se inizi da un look, fai clic sul link Esplora da qui per aprire la query in un'esplorazione.

Viene visualizzata la finestra Condividi URL, in cui è mostrato l'URL espanso:

L'URL fornisce informazioni sufficienti per ricreare la query. Ad esempio, per un URL di condivisione espanso come il seguente:

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

L'URL fornisce le seguenti informazioni:

modello e_thelook
esplorazione events
per eseguire query 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 Looker struttura le strutture JOIN

Nell'SQL query sopra, vedrai che orders Explore è presente nella clausola FROM principale e le viste unite nelle clausole LEFT JOIN. I join di Looker possono essere scritti in molti modi diversi, come illustrato in dettaglio nella pagina sull'utilizzo dei join in LookML.

I blocchi SQL specificano clausole SQL personalizzate

Non tutti gli elementi di una query Looker sono generati automaticamente. A un certo punto, il modello dei 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 di 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 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 il Riferimento 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 a differenza di SQL. Tuttavia, dopo la sostituzione, la stringa risultante è pura SQL, 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, come le funzioni MySQL CONCAT e DATEDIFF in questo caso. 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
  }
}

Esempio di blocco SQL 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});;
}