Le tabelle derivate aprono un mondo di possibilità di analisi avanzate, ma possono essere difficili da affrontare, implementare e risolvere i problemi. Questo ricettario contiene i casi d'uso più comuni delle tabelle derivate in Looker.
Questa pagina contiene i seguenti esempi:
- Creazione di una tabella ogni giorno alle 03:00
- Aggiunta di nuovi dati a una tabella di grandi dimensioni
- Utilizzare le 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 aggregato
Risorse delle tabelle derivate
Questi cookbook presuppongono che tu abbia una conoscenza introduttiva di LookML e delle tabelle derivate. Dovresti essere in grado di creare visualizzazioni e modificare il file del modello. Per ripassare 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
Creazione di una tabella ogni giorno alle 3:00
I dati in questo esempio vengono inviati ogni giorno alle 2: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 di attivazione, 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 visualizzazione 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 creata da Looker aggiungendo nuovi dati alla tabella anziché ricostruirla 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, che in questo esempio è l'incremento di tempo per cui è necessario eseguire query sui dati aggiornati e aggiungerli alla PDT.
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 principali, totali correnti e cumulativi e altri calcoli utili su più righe. Dopo l'esecuzione della query principale, eventuali 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 include 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 costruire il valore.
Il calcolo di sql può utilizzare qualsiasi colonna 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.
Questo esempio crea una colonna average_customer_order, 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 database, devi ottimizzarle utilizzando le seguenti strategie, come supportato 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 tuo dialetto), Looker ti avvisa che devi farlo per migliorare il rendimento 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 sviluppatori ETL.
Considera un caso in cui hai una tabella, ma vuoi testare indici diversi. Il codice LookML iniziale per la visualizzazione potrebbe essere simile al seguente:
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 il PDT. Quindi, 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 UNION o UNION ALL SQL 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 di una misura come dimensione)
Come regola generale in SQL e, per estensione, in Looker, non puoi raggruppare una query in base ai risultati di una funzione di aggregazione (rappresentata in Looker come misure). Puoi raggruppare solo per 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.
A partire da un'esplorazione, Looker può generare il codice LookML per tutte o la maggior parte delle tabelle derivate. 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 visualizzazione seguendo questi passaggi:
In modalità di sviluppo, vai ai file di progetto.
Fai clic sul segno + nella parte superiore dell'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.
Assegna un nome significativo alla visualizzazione.
Se vuoi, puoi modificare i nomi delle colonne, specificare le colonne derivate e aggiungere filtri.
Tabelle aggregate con consapevolezza aggregata
In Looker, potresti spesso trovare set di dati o tabelle molto grandi che, per essere performanti, richiedono tabelle di aggregazione o aggregazioni.
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 nelle esplorazioni esistenti. Le query utilizzeranno quindi queste tabelle di riepilogo dove Looker ritiene opportuno, senza alcun input utente. In questo modo, ridurrai le dimensioni delle query, i tempi di attesa e migliorerai 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 la FAA, puoi modellare questa tabella in Looker con la relativa visualizzazione ed esplorazione. 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 sulla notorietà aggregata, consulta il tutorial sulla notorietà aggregata.