In che modo Looker genera codice SQL

Se usi Looker in background, probabilmente ti interesserà sapere in che modo Looker genera l'SQL. Fondamentalmente, Looker è uno strumento che genera query SQL e le invia a una connessione a un database. Looker formula query SQL sulla base di un progetto LookML che descrive la relazione tra tabelle e colonne nel database. Se comprendi in che modo Looker genera le query, capirai meglio in che modo il codice LookML si traduce in query SQL efficienti.

Ogni parametro LookML controlla alcuni aspetti del modo in cui Looker genera l'SQL, modificando la struttura, il contenuto o il comportamento della query. Questa pagina descrive i principi di come Looker genera SQL, ma non tratta in dettaglio tutti gli elementi LookML. La pagina della documentazione di riferimento rapido di LookML è un buon punto di partenza per ottenere 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 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 visualizzare il piano di spiegazione del database per la query.

Per saperne 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 con SQL Runner, consulta il post della scheda Community su come ottimizzare SQL con EXPLAIN.

Forma canonica di una query di Looker

Le query SQL di Looker hanno 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 modellare 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 query di esempio 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 dell'esplorazione su cui eseguire la query, che compila la clausola FROM SQL
  • Campi: i parametri dimension e measure da includere nella query, che completano la clausola SELECT SQL
  • 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 eseguire l'ordinamento e l'ordinamento che compila la clausola ORDER BY SQL

Questi parametri sono esattamente gli elementi specificati da un utente quando crea una query nella pagina Esplora di Looker. Questi stessi elementi compaiono 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.

E per quanto riguarda le viste 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 vengono unite alle esplorazioni. Quando crea query SQL, Looker include le clausole JOIN solo quando richiesto. Quando gli utenti creano una query in Looker, non devono specificare come le tabelle si uniscono, perché queste informazioni sono codificate nel modello, uno dei più potenti vantaggi di Looker per gli utenti aziendali.

Una query di esempio e il codice SQL risultante

Creiamo una query in Looker per dimostrare come viene generata la query in base al pattern precedente. Prendi in considerazione un negozio di e-commerce che ha un database con due tabelle, orders e users, 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 (ORDERS Count) raggruppati per stato (USERS State) e filtrati in base alla data di creazione dell'ordine (ORDERS Created Date) in un'esplorazione di Looker.

Una tabella di dati Esplora mostra il conteggio degli ordini raggruppati per stato utente per gli ordini effettuati negli ultimi 30 giorni.

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. Looker SQL presenta alcuni tratti del codice generato automaticamente (ad esempio COALESCE(users.state,'') AS "_g1"), ma si adatta 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à denominata SQL Runner, che consente di eseguire qualsiasi tipo di SQL sulle connessioni al database che hai impostato in Looker.

Poiché ogni query generata da Looker genera un comando SQL completo e funzionale, puoi utilizzare SQL Runner per esaminare la query o provarla.

Le query SQL non elaborate 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.

Analisi 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 su cui eseguire query e visualizzare fields=users.state,users.count
ordina i campi sorts=users.count+desc
filtri e valori f[users.created_year]=2020

In che modo Looker struttura i JOIN

Nella query di esempio precedente, nota che l'esplorazione orders viene visualizzata nella clausola FROM principale e le viste unite 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 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 creatore di modelli 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 forma. ad esempio sql_always_where, sql_on e sql_table_name. Per ulteriori informazioni su ciascun parametro, consulta la 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 ($) rende queste dichiarazioni sql apparentemente diverse da SQL. Tuttavia, una volta avvenuta 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 in questo esempio, i blocchi SQL possono utilizzare funzioni supportate dal database sottostante (in questo caso le funzioni MySQL CONCAT e DATEDIFF). Il codice utilizzato nei blocchi SQL deve corrispondere al dialetto SQL utilizzato dal database.

Blocco SQL di esempio per le tabelle derivate

Le tabelle derivate usano 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
  }
}

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