Come Looker genera SQL

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 destinazione
  • explore: il nome della query to Explore che compila la clausola FROM di SQL
  • Campi: i parametri dimension e measure da includere nella query, che compilano la clausola SQL SELECT
  • filter: espressioni di filtro Looker da applicare a zero o più campi, che completano le clausole SQL WHERE e HAVING
  • 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(&#42;) 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});;
}