Una tabella derivata è una query i cui risultati vengono utilizzati come se la tabella derivata fosse una tabella fisica nel database. Una tabella derivata nativa è basata su una query da te definita utilizzando i termini LookML. È diversa da una tabella derivata basata su SQL, che si basa su una query da te definita con i termini SQL. Rispetto alle tabelle derivate basate su SQL, le tabelle derivate native sono molto più facili da leggere e comprendere durante la modellazione dei dati. Per saperne di più, consulta la sezione Tabelle derivate native e basate su SQL della pagina della documentazione Tabelle derivate in Looker.
Entrambe le tabelle derivate native e basate su SQL vengono definite in LookML utilizzando il parametro derived_table a livello di vista. Tuttavia, con le tabelle derivate native, non è necessario creare una query SQL. Utilizza invece il parametro explore_source per specificare l'esplorazione su cui basare la tabella derivata, le colonne desiderate e altre caratteristiche desiderate.
Puoi anche fare in modo che Looker crei il LookML della tabella derivata da una query SQL Runner, come descritto nella pagina della documentazione Utilizzo di SQL Runner per creare tabelle derivate.
Utilizzare un'esplorazione per iniziare a definire le tabelle derivate native
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, segui questi passaggi:
Seleziona il menu a forma di ingranaggio Esplora azioni e seleziona Ottieni LookML.
Fai clic sulla scheda Tabella derivata per visualizzare il LookML per la creazione di una tabella derivata nativa per l'esplorazione.
Copia il file LookML.
Ora che hai copiato il LookML generato, incollalo in un file di visualizzazione:
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, puoi fare clic sul menu di una cartella e selezionare Crea visualizzazione dal menu per creare il file all'interno della cartella.
Imposta il nome della visualizzazione su un nome significativo.
Facoltativamente, modifica i nomi delle colonne, specifica le colonne derivate e aggiungi filtri.
Quando utilizzi una misura pari a
type: countin un'esplorazione, la visualizzazione etichetta i valori risultanti con il nome della vista anziché la parola Conteggio. Per evitare confusione, plurali il nome della visualizzazione. Puoi modificare il nome della vista selezionando Mostra nome campo completo in Serie nelle impostazioni di visualizzazione oppure utilizzando il parametroview_labelcon una versione plurale del nome della vista.
Definizione di una tabella derivata nativa in LookML
Se utilizzi tabelle derivate dichiarate in SQL o LookML nativi, l'output di una query di derived_table è una tabella con un set di colonne. Quando la tabella derivata viene espressa in SQL, i nomi delle colonne di output sono impliciti dalla query SQL. Ad esempio, la seguente query SQL avrà le colonne di output user_id, lifetime_number_of_orders e lifetime_customer_value:
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
In Looker, una query è basata su un'esplorazione, include campi di misura e dimensione, aggiunge eventuali filtri applicabili e può anche specificare un ordinamento. Una tabella derivata nativa contiene tutti questi elementi oltre ai nomi di output per le colonne.
Il seguente esempio semplice produce una tabella derivata con tre colonne: user_id, lifetime_customer_value e lifetime_number_of_orders. Non è necessario scrivere manualmente la query in SQL: Looker la crea per te utilizzando l'esplorazione order_items specificata e alcuni dei campi dell'esplorazione (order_items.user_id, order_items.total_revenue e order_items.order_count).
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
Utilizzo delle istruzioni include per abilitare il riferimento ai campi
Nel file di visualizzazione della tabella derivata nativa, utilizza il parametro explore_source per puntare a un'esplorazione e definire le colonne desiderate e altre caratteristiche desiderate per la tabella derivata nativa. Dal momento che stai puntando a un'esplorazione dal file di visualizzazione della tabella derivata nativa, devi anche includere il file contenente la definizione dell'esplorazione. Le esplorazioni sono in genere definite all'interno di un file del modello, ma nel caso delle tabelle derivate native è più semplice creare un file separato per l'esplorazione utilizzando l'estensione del file .explore.lkml, come descritto nella documentazione per la creazione di file di esplorazione. In questo modo, nel file di visualizzazione tabella derivata nativo puoi includere un singolo file di esplorazione e non l'intero file del modello. In questo caso:
- Il file di visualizzazione della tabella derivata nativa deve includere il file dell'esplorazione. Ad esempio:
include: "/explores/order_items.explore.lkml"
- Il file dell'esplorazione deve includere i file della vista necessari. Ad esempio:
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- Il modello deve includere il file dell'esplorazione. Ad esempio:
include: "/explores/order_items.explore.lkml"
I file delle esplorazioni ascolteranno la connessione del modello in cui sono inclusi. Tieni presente questo aspetto quando includi file di esplorazione nei modelli configurati con una connessione diversa dal modello principale del file di esplorazione. Se lo schema per l'inclusione della connessione del modello è diverso da quello della connessione del modello padre, possono verificarsi errori nelle query.
Definizione delle colonne delle tabelle derivate native
Come mostrato nell'esempio precedente, utilizza column per specificare le colonne di output della tabella derivata.
Specificare i nomi delle colonne
Per la colonna user_id, il nome della colonna corrisponde al nome del campo specificato nell'esplorazione originale.
Spesso, nella tabella di output vorrai utilizzare un nome di colonna diverso da quello dei campi nell'esplorazione originale. L'esempio precedente ha generato un calcolo del lifetime value da parte dell'utente utilizzando l'esplorazione order_items. Nella tabella di output, total_revenue è in realtà il lifetime_customer_value di un cliente.
La dichiarazione column supporta la dichiarazione di un nome di output diverso dal campo di immissione. Ad esempio, il codice seguente indica a Looker di "creare una colonna di output denominata lifetime_value nel campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nomi di colonna impliciti
Se il parametro field non viene inserito nella dichiarazione di una colonna, si presume che sia <explore_name>.<field_name>. Ad esempio, se hai specificato explore_source: order_items,
column: user_id {
field: order_items.user_id
}
è equivalente a
column: user_id {}
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 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.
L'esempio seguente produce la stessa tabella derivata dell'esempio precedente, tranne per il fatto che viene aggiunta una colonna average_customer_order calcolata, 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: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
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 desiderata. Quando fai riferimento ai valori, devi utilizzare il nome della colonna definito nella tabella derivata nativa.
L'esempio seguente crea una tabella derivata nativa che include le colonne user_id, order_id e created_time. Quindi, utilizzando una colonna derivata con una funzione finestra SQL ROW_NUMBER(), calcola 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
}
}
Aggiunta di filtri a una tabella derivata nativa
Supponiamo che tu voglia creare una tabella derivata del valore di un cliente negli ultimi 90 giorni. Vuoi gli stessi calcoli che hai effettuato nell'esempio precedente, ma vuoi includere solo gli acquisti degli ultimi 90 giorni.
Devi semplicemente aggiungere un filtro a derived_table in base alle transazioni effettuate negli ultimi 90 giorni. Il parametro filters per una tabella derivata utilizza la stessa sintassi utilizzata per creare una misura filtrata.
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
I filtri verranno aggiunti alla clausola WHERE quando Looker scrive l'SQL per la tabella derivata.
Inoltre, puoi utilizzare il sottoparametro dev_filters di explore_source con una tabella derivata nativa. Il parametro dev_filters ti consente di specificare i filtri che Looker applica solo alle versioni di sviluppo della tabella derivata, il che significa che puoi creare versioni più piccole e filtrate della tabella da eseguire l'iterazione e il test senza dover creare la tabella completa dopo ogni modifica.
Il parametro dev_filters agisce in combinazione con il parametro filters per far sì che tutti i filtri vengano applicati alla versione di sviluppo della tabella. Se dev_filters e filters specificano i filtri per la stessa colonna, dev_filters ha la precedenza per la versione di sviluppo della tabella.
Per ulteriori informazioni, vedi Lavorare più velocemente in modalità di sviluppo.
Utilizzo di filtri basati su modelli
Puoi utilizzare bind_filters per includere filtri basati su modelli:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Equivale all'utilizzo del seguente codice in un blocco sql:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field è il campo a cui viene applicato il filtro. to_field deve essere un campo del explore_source sottostante.
from_field specifica il campo da cui ottenere il filtro, se esiste un filtro in fase di runtime.
Nell'esempio bind_filters precedente, Looker utilizzerà qualsiasi filtro applicato al campo filtered_lookml_dt.filter_date e lo applicherà al campo users.created_date.
Puoi anche utilizzare il sottoparametro bind_all_filters di explore_source per passare tutti i filtri di runtime da un'esplorazione a una sottoquery nativa della tabella derivata. Per saperne di più, consulta la pagina della documentazione relativa al parametro explore_source.
Ordinamento e limitazione delle tabelle derivate native
Se vuoi, puoi anche ordinare e limitare le tabelle derivate:
sorts: [order_items.count: desc]
limit: 10
Ricorda che un'esplorazione potrebbe mostrare le righe in un ordine diverso rispetto all'ordinamento sottostante.
Conversione delle tabelle derivate native in fusi orari diversi
Puoi specificare il fuso orario per la tabella derivata nativa utilizzando il sottoparametro timezone:
timezone: "America/Los_Angeles"
Quando utilizzi il sottoparametro timezone, tutti i dati basati sul tempo nella tabella derivata nativa verranno convertiti nel fuso orario specificato. Consulta la pagina della documentazione relativa ai valori di timezone per un elenco dei fusi orari supportati.
Se non specifichi un fuso orario nella definizione della tabella derivata nativa, quest'ultima non eseguirà alcuna conversione del fuso orario per i dati basati sul tempo, mentre per i dati basati sul tempo verrà utilizzato per impostazione predefinita il fuso orario del database.
Se la tabella derivata nativa non è persistente, puoi impostare il valore del fuso orario su "query_timezone" in modo da utilizzare automaticamente il fuso orario della query attualmente in esecuzione.