Come Looker genera SQL

Se hai esperienza con SQL, probabilmente ti incuriosisce il modo in cui 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. Se comprendi come Looker genera le query, capirai meglio come il codice LookML si traduce in query SQL efficienti.

Ogni parametro LookML controlla un aspetto della generazione di SQL da parte di Looker, modificando la struttura, i contenuti o il comportamento della query. Questa pagina descrive i principi di generazione di SQL da parte di Looker, ma non copre in dettaglio tutti gli elementi LookML. La pagina di documentazione Guida di riferimento rapida di LookML è un buon punto di partenza per informazioni sui parametri LookML.

Visualizzare la 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 visualizzare il piano di esecuzione della query del database.

Per saperne di più su SQL Runner, consulta la pagina di documentazione 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 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 viste a cui viene fatto riferimento nella query SQL. Le espressioni di filtro vengono specificate in Looker dall'utente per modellare le query ad hoc. Le espressioni di filtro possono essere dichiarate direttamente in LookML per essere applicate a tutte le query.

Componenti fondamentali di una query 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 destinazione
• explore: il nome dell'esplorazione da interrogare, che compila la clausola SQL FROM
• Campi: i parametri dimension e measure da includere nella query, che compilano la clausola SQL SELECT
• filter: espressioni di filtro di Looker da applicare a zero o più campi, che compilano le clausole SQL WHERE e HAVING
• 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 delle query con Looker, ad esempio nell'SQL generato, nell'URL che rappresenta la query e nell'API Looker.

Che cosa succede alle visualizzazioni specificate dalle clausole LEFT JOIN? Le clausole JOIN vengono compilate in base alla struttura del modello LookML, che specifica come le viste vengono unite alle esplorazioni. Quando crea query SQL, Looker include le clausole JOIN solo quando necessario. Quando gli utenti creano una query in Looker, non devono specificare come unire le tabelle, perché queste informazioni sono codificate nel modello, uno dei vantaggi più potenti di Looker per gli utenti aziendali.

Una query di esempio e l'SQL risultante

Creiamo una query in Looker per mostrare come viene generata in base al pattern precedente. Considera un negozio di e-commerce con 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)

Troviamo il numero di ordini (Conteggio ORDINI) raggruppati per stato (Stato UTENTI) 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 della query canonica. L'SQL di Looker mostra alcune caratteristiche del codice generato automaticamente (ad esempio, COALESCE(users.state,'') AS "_g1"), ma si adatta sempre alla formula.

Prova altre query in Looker per dimostrare che la struttura della 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 SQL sulle connessioni ai 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 sperimentare la query.

Le query SQL non elaborate eseguite in SQL Runner producono lo stesso insieme 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.

Nota :se le impostazioni di connessione specificano un fuso orario del database, Looker emetterà un SET TIMEZONE al momento della connessione, il che potrebbe influire sui risultati.

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 Looker. Inizia selezionando Condividi dal menu a forma di ingranaggio di Esplora per aprire il menu Condividi URL.

Nota :se inizi da un Look, fai clic sul link Esplora da qui del Look per aprire la query in un'esplorazione.

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 da interrogare e visualizzare	fields=users.state,users.count
campo e ordine di ordinamento	sorts=users.count+desc
campi e valori dei filtri	f[users.created_year]=2020

Come Looker struttura i JOIN

Nella query di esempio precedente, nota che orders Explore viene visualizzato nella clausola FROM principale e le visualizzazioni unite vengono visualizzate nelle clausole LEFT JOIN. Le unioni di Looker possono essere scritte in molti modi diversi, che sono spiegati in dettaglio 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 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 espressioni SQL complete.

Il parametro del blocco SQL più comune è sql, utilizzato nelle definizioni di dimensioni e metriche. Il parametro sql specifica una clausola SQL per fare riferimento a una colonna sottostante o per eseguire una funzione di aggregazione. 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 a LookML.

Esempio di blocchi SQL per dimensioni e misure

Il seguente esempio di codice fornisce alcuni esempi di blocchi SQL per dimensioni e metriche. L'operatore di sostituzione LookML ($) fa sì che queste dichiarazioni sql sembrino 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 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.

Esempio di blocco SQL per le tabelle derivate

Le tabelle derivate utilizzano anche un blocco SQL per specificare la query che deriva la tabella. Ecco 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
  }
}

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});;
}