Le tabelle derivate aprono un mondo di possibilità di analisi avanzate, ma possono essere difficili da affrontare, implementare e risolvere i problemi. Questo libro di ricette contiene i casi d'uso più comuni delle tabelle derivate in Looker.
Questa pagina contiene i seguenti esempi:
- Preparare un tavolo ogni giorno alle 03:00
- Aggiunta di nuovi dati a una tabella di grandi dimensioni
- Utilizzo delle funzioni finestra SQL
- Creare colonne derivate per i valori calcolati
- Strategie di ottimizzazione
- Utilizzare le PDT per testare le ottimizzazioni
UNIONdue tabelle- Eseguire la somma di una somma (definizione della dimensione di una misura)
- Aggregare le tabelle con il riconoscimento degli aggregati
Risorse tabella derivate
Questi libri di ricette presuppongono che tu abbia una conoscenza introduttiva di LookML e delle tabelle derivate. Saper creare visualizzazioni e modificare il file del modello. Se vuoi un riepilogo su uno di questi argomenti, consulta le seguenti risorse:
- Tabelle derivate
- Termini e concetti di LookML
- Creare tabelle derivate native
- Riferimento al parametro
derived_table - Memorizzazione nella cache delle query e ricreazione delle PDT con i gruppi di dati
Creare un tavolo ogni giorno alle 03:00
I dati di questo esempio sono disponibili ogni giorno alle 02:00. I risultati di una query su questi dati saranno gli stessi indipendentemente dal fatto che venga eseguita alle 3:00 o alle 21:00. Pertanto, ha senso creare la tabella una volta al giorno e consentire agli utenti di estrarre i risultati da una cache.
Se includi il gruppo di dati nel file del modello, puoi riutilizzarlo con più tabelle ed esplorazioni. Questo gruppo di dati contiene un parametro sql_trigger_value che indica al gruppo di dati quando attivare e ricostruire la tabella derivata.
Per altri esempi di espressioni trigger, consulta la documentazione di sql_trigger_value.
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
Aggiungi il parametro datagroup_trigger alla definizione di derived_table nel file di visualizzazione e specifica il nome del gruppo di dati che vuoi utilizzare. In questo esempio, il gruppo di dati è standard_data_load.
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
Aggiunta di nuovi dati a una tabella di grandi dimensioni
Una PDT incrementale è una tabella derivata permanente creata da Looker aggiungendo nuovi dati alla tabella, anziché ricrearla nella sua interezza.
L'esempio seguente si basa sull'esempio della tabella orders per mostrare come la tabella viene creata in modo incrementale. I nuovi dati sugli ordini vengono inviati ogni giorno e possono essere aggiunti alla tabella esistente quando aggiungi un parametro increment_key e un parametro increment_offset.
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
Il valore increment_key è impostato su created_at, ovvero l'incremento di tempo per il quale è necessario eseguire query sui dati aggiornati e aggiungerli alla PDT in questo esempio.
Il valore increment_offset è impostato su 3 per specificare il numero di periodi di tempo precedenti (con la granularità della chiave di incremento) che vengono ricostruiti per tenere conto dei dati in ritardo.
Utilizzo delle funzioni finestra SQL
Alcuni dialetti di database supportano le funzioni finestra, in particolare per creare numeri di sequenza, chiavi primarie, totali correnti e cumulativi e altri utili calcoli multiriga. Dopo l'esecuzione della query principale, tutte le dichiarazioni derived_column vengono eseguite in un passaggio separato.
Se il dialetto del database supporta le funzioni finestra, puoi utilizzarle nella tabella derivata nativa. Crea un parametro derived_column con un parametro sql contenente la funzione finestra. Quando fai riferimento ai valori, devi utilizzare il nome della colonna come definito nella tabella derivata nativa.
L'esempio seguente mostra come creare una tabella derivata nativa che includa le colonne user_id, order_id e created_time. Devi quindi utilizzare una colonna derivata con una funzione finestra SQL ROW_NUMBER() per calcolare una colonna contenente il numero di sequenza dell'ordine di un cliente.
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
Creazione di colonne derivate per i valori calcolati
Puoi aggiungere i parametri derived_column per specificare le colonne che non esistono in Esplora del parametro explore_source. Ogni parametro derived_column ha un parametro sql che specifica come costruire il valore.
Nel calcolo del sql può essere utilizzato qualsiasi colonna specificata mediante i parametri column. Le colonne derivate non possono includere funzioni di aggregazione, ma possono includere calcoli che possono essere eseguiti su una singola riga della tabella.
Questo esempio crea una colonna average_customer_order, che viene calcolata dalle colonne lifetime_customer_value e lifetime_number_of_orders nella tabella derivata nativa.
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
Strategie di ottimizzazione
Poiché le PDT sono archiviate nel tuo database, ti consigliamo di ottimizzarle utilizzando le seguenti strategie, supportate dal tuo dialetto:
Ad esempio, per aggiungere la persistenza, puoi impostare la ricostruzione della PDT quando viene attivato il gruppo di dati orders_datagroup e poi aggiungere indici sia su customer_id che su first_order, come mostrato di seguito:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Se non aggiungi un indice (o un equivalente per il dialetto), Looker ti avvisa che devi farlo per migliorare le prestazioni delle query.
Utilizzare i PDT per testare le ottimizzazioni
Puoi utilizzare i PDT per testare diverse indicizzazioni, distribuzioni e altre opzioni di ottimizzazione senza richiedere un'assistenza eccessiva da parte dei tuoi DBA o degli sviluppatori ETL.
Considera un caso in cui hai una tabella, ma vuoi testare indici diversi. Il tuo LookML iniziale per la vista potrebbe avere il seguente aspetto:
view: customer {
sql_table_name: warehouse.customer ;;
}
Per testare le strategie di ottimizzazione, puoi utilizzare il parametro indexes per aggiungere indici a LookML, come mostrato di seguito:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
Esegui una query sulla vista una volta per generare la PDT. Poi esegui le query di test e confronta i risultati. Se i risultati sono positivi, puoi chiedere al team DBA o ETL di aggiungere gli indici alla tabella originale.
UNION due tabelle
Puoi eseguire un operatore SQL UNION o UNION ALL in entrambe le tabelle derivate, se il tuo dialetto SQL lo supporta. Gli operatori UNION e UNION ALL combinano i set di risultati di due query.
Questo esempio mostra l'aspetto di una tabella derivata basata su SQL con un UNION:
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
L'istruzione UNION nel parametro sql produce una tabella derivata che combina i risultati di entrambe le query.
La differenza tra UNION e UNION ALL è che UNION ALL non rimuove le righe duplicate. Quando utilizzi UNION anziché UNION ALL, devi tenere presenti alcune considerazioni sul rendimento, in quanto il server di database deve eseguire un'operazione aggiuntiva per rimuovere le righe duplicate.
Somma di una somma (definizione della dimensione di una misura)
Come regola generale in SQL (e, di conseguenza, in Looker), non puoi raggruppare una query in base ai risultati di una funzione aggregata (rappresentata in Looker come misure). Puoi raggruppare solo in base a campi non aggregati (rappresentati in Looker come dimensioni).
Per raggruppare in base a un valore aggregato (ad esempio per calcolare la somma di una somma), devi "dimensionalizzare" una misura. Un modo per farlo è utilizzare una tabella derivata, che crea in modo efficace una sottoquery dell'aggregato.
Partendo da un'esplorazione, Looker può generare LookML per tutta o per la maggior parte della tua tabella derivata. Basta creare un'esplorazione e selezionare tutti i campi da includere nella tabella derivata. Per generare il codice LookML della tabella derivata nativa (o basata su SQL), segui questi passaggi:
Fai clic sul menu a forma di ingranaggio dell'esplorazione e seleziona Genera LookML.
Per visualizzare il codice LookML per la creazione di una tabella derivata nativa per l'esplorazione, fai clic sulla scheda Tabella derivata.
Copia il codice LookML.
Ora che hai copiato il LookML generato, incollalo in un file di vista seguendo questi passaggi:
In modalità sviluppo, vai ai file del progetto.
Fai clic sul segno + in cima all'elenco dei file di progetto nell'IDE di Looker e seleziona Crea vista. In alternativa, per creare il file all'interno della cartella, fai clic sul menu di una cartella e seleziona Crea vista.
Assegna un nome significativo alla visualizzazione.
Se vuoi, puoi modificare i nomi delle colonne, specificare le colonne derivate e aggiungere filtri.
Tabelle di aggregazione con riconoscimento degli aggregati
In Looker potresti incontrare set di dati o tabelle molto grandi che, per avere prestazioni ottimali, richiedono tabelle o riepiloghi di aggregazione.
Con la consapevolezza aggregata di Looker, puoi pre-costruire tabelle aggregate con vari livelli di granularità, dimensionalità e aggregazione e puoi indicare a Looker come utilizzarle all'interno di esplorazioni esistenti. Le query utilizzeranno quindi queste tabelle di aggregazione dove Looker ritiene appropriato, senza alcun input utente. Ciò ridurrà le dimensioni delle query, ridurrà i tempi di attesa e migliorerà l'esperienza utente.
Di seguito è riportata un'implementazione molto semplice in un modello Looker per dimostrare quanto può essere leggera la consapevolezza aggregata. Data una tabella ipotetica dei voli nel database con una riga per ogni volo registrato tramite FAA, puoi modellare questa tabella in Looker con viste ed esplorazioni dedicate. Di seguito è riportato il codice LookML di una tabella aggregata che puoi definire per l'esplorazione:
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
Con questa tabella aggregata, un utente può eseguire query sull'esplorazione flights e Looker utilizzerà automaticamente la tabella aggregata per rispondere alle query. Per una procedura dettagliata più dettagliata sull'analisi aggregata, consulta il tutorial sull'awareness aggregata.