Le tabelle derivate offrono un mondo di possibilità analitiche avanzate, ma l'approccio, l'implementazione e la risoluzione dei problemi possono essere un'impresa ardua. Questo libro di ricette contiene i casi d'uso più popolari delle tabelle derivate in Looker.
Questa pagina contiene i seguenti esempi:
- Costruzione di un tavolo tutti i giorni alle 03:00
- Aggiunta di nuovi dati a una tabella di grandi dimensioni
- Utilizzo delle funzioni delle finestre SQL
- Creare colonne derivate per i valori calcolati
- Strategie di ottimizzazione
- Utilizzare le PDT per testare le ottimizzazioni
UNIONdue tabelle- Somma di una somma (dimensionalizzazione di una misura)
- Tabelle di aggregazione con riconoscimento aggregato
Risorse delle tabelle derivate
Questi libri di ricette presuppongono una conoscenza introduttiva di LookML e delle tabelle derivate. Dovresti essere in grado di effettuare visualizzazioni e modificare il file del modello. Per un ripasso su uno di questi argomenti, consulta le seguenti risorse:
- Tabelle derivate
- Termini e concetti di LookML
- Creazione di tabelle derivate native
- Riferimento parametro
derived_table - Memorizzazione nella cache delle query e ricreazione delle PDT con i gruppi di dati
Creazione di un tavolo ogni giorno alle 03:00
I dati in questo esempio vengono forniti ogni giorno alle 2:00. I risultati di una query su questi dati saranno gli stessi indipendentemente dal fatto che venga eseguita alle 03: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 tuo 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 ricreare 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 della vista e specifica il nome del gruppo di dati da 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 che Looker crea aggiungendo dati aggiornati alla tabella, invece di ricreare la tabella nella sua interezza.
L'esempio successivo si basa sull'esempio della tabella orders per mostrare come viene creata la tabella in modo incrementale. Ogni giorno vengono forniti nuovi dati sugli ordini che 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 cui è 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 arrivo in ritardo.
Utilizzo delle funzioni finestra SQL
Alcuni dialetti di database supportano le funzioni di finestra, in particolare per creare numeri di sequenza, chiavi primarie, totali in esecuzione e cumulativi e altri utili calcoli multiriga. Una volta eseguita la query principale, tutte le dichiarazioni derived_column vengono eseguite in un passaggio separato.
Se il dialetto del database supporta le funzioni finestra, puoi utilizzarlo 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 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. Dovresti 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 parametri derived_column per specificare colonne che non esistono nell'esplorazione del parametro explore_source. Ogni parametro derived_column ha un parametro sql che specifica come creare il valore.
Il calcolo di sql può utilizzare qualsiasi colonna da te specificata utilizzando i parametri column. Le colonne derivate non possono includere funzioni aggregate, ma possono includere calcoli che possono essere eseguiti su una singola riga della tabella.
In questo esempio viene creata 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, devi ottimizzarle utilizzando le seguenti strategie, supportate dal tuo dialetto:
Ad esempio, per aumentare la persistenza, puoi impostare la PDT in modo che venga ricreata quando viene attivato il gruppo di dati orders_datagroup e poi aggiungere indici su customer_id e 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 tuo dialetto), Looker ti avvisa che devi farlo per migliorare le prestazioni delle query.
Utilizzo delle PDT per testare le ottimizzazioni
Puoi utilizzare le PDT per testare diverse opzioni di indicizzazione, distribuzione e altre opzioni di ottimizzazione senza bisogno di un'ampia assistenza da parte degli sviluppatori DBA o ETL.
Considera un caso in cui hai una tabella, ma vuoi testare indici diversi. Il codice 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 prova e confronta i risultati. Se i risultati sono positivi, puoi chiedere al tuo 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 l'insieme 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. Ci sono considerazioni sulle prestazioni da tenere presenti quando si utilizza UNION rispetto a UNION ALL, poiché il server di database deve svolgere ulteriori attività per rimuovere le righe duplicate.
Effettuare una somma di una somma (dimensionalizzazione 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 eseguire il raggruppamento solo in base ai campi non aggregati (rappresentati in Looker come dimensioni).
Per eseguire il raggruppamento in base a un aggregato (ad esempio per prendere la somma di una somma), devi "dimensionalizzare" una misura. Un modo per farlo è utilizzare una tabella derivata, che crea di fatto una sottoquery dell'aggregato.
Partendo da un'esplorazione, Looker può generare LookML per tutta o la maggior parte della tabella derivata. Crea un'esplorazione e seleziona tutti i campi che vuoi includere nella tabella derivata. Quindi, per generare il codice LookML della tabella derivata nativa (o basata su SQL), segui questi passaggi:
Fai clic sul menu a forma di ingranaggio di Esplora e seleziona Ottieni LookML.
Per visualizzare il LookML per la creazione di una tabella derivata nativa per l'esplorazione, fai clic sulla scheda Tabella derivata.
Copia il file LookML.
Ora che hai copiato il LookML generato, incollalo in un file di visualizzazione seguendo questi passaggi:
In Modalità di sviluppo, vai ai file di progetto.
Fai clic sul segno + in cima all'elenco dei file del progetto nell'IDE di Looker e seleziona Crea visualizzazione. In alternativa, per creare il file all'interno della cartella, fai clic sul menu di una cartella e seleziona Crea visualizzazione.
Imposta il nome della visualizzazione su un nome significativo.
Facoltativamente, modifica i nomi delle colonne, specifica le colonne derivate e aggiungi filtri.
Tabelle di aggregazione con consapevolezza aggregata
In Looker, potresti spesso incontrare set di dati o tabelle di grandi dimensioni che, per funzionare bene, richiedono tabelle di aggregazione o aggregazione.
Grazie alla consapevolezza aggregata di Looker, puoi pre-costruire tabelle aggregate su vari livelli di granularità, dimensionalità e aggregazione e puoi informare Looker su come utilizzarle nelle esplorazioni esistenti. Le query utilizzeranno quindi queste tabelle di aggregazione dove Looker lo ritiene appropriato, senza alcun input utente. In questo modo si riducono le dimensioni delle query, i tempi di attesa e l'esperienza utente migliore.
Di seguito è riportata un'implementazione molto semplice in un modello Looker per dimostrare quanto possa essere semplice rilevare i dati aggregati. Data una tabella ipotetica dei voli nel database con una riga per ogni periodo di pubblicazione registrato tramite la FAA, puoi modellare questa tabella in Looker con una propria vista e una propria esplorazione. Di seguito è riportato il LookML per 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 la utilizzerà automaticamente per rispondere alle query. Per una procedura dettagliata più dettagliata sull'awareness aggregata, consulta il tutorial per creare awareness.