Una tabella derivata è una query i cui risultati vengono utilizzati come se fosse una tabella fisica nel database. Una tabella nativa derivata si basa su una query che definisci utilizzando i termini LookML. Questa tabella è diversa da una tabella derivata basata su SQL, che si basa su una query che definisci con i termini SQL. Rispetto alle tabelle derivate basate su SQL, le tabelle native derivate sono molto più facili da leggere e comprendere quando modella i tuoi dati. Per ulteriori informazioni, consulta la sezione Tabelle derivate native e tabelle derivate basate su SQL nella pagina Tabelle derivate in Looker.
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 native derivate non è necessario creare una query SQL. Puoi invece utilizzare 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 la tabella LookML derivata da una query SQL Runner, come descritto nella pagina della documentazione sull'uso di SQL Runner per creare tabelle derivate.
Utilizzo dell'esplorazione per iniziare a definire le tabelle native derivate
A partire da un'esplorazione, Looker può generare LookML per tutta o gran parte della tabella derivata. Ti basta creare un'esplorazione e selezionare tutti i campi che vuoi includere nella tabella derivata. Quindi, per generare la tabella nativa derivata da LookML:
Fai clic sul menu a forma di ingranaggio di Explore e seleziona Get LookML.
Fai clic sulla scheda Tabella derivata per visualizzare il codice LookML per la creazione di una tabella nativa derivata per l'esplorazione.
Copia il LookML.
Ora che hai copiato il LookML generato, incollalo in un file di vista:
In modalità di sviluppo, vai ai tuoi file di progetto.
Fai clic sul segno + nella parte superiore dell'elenco dei file del progetto nell'IDE di Looker e seleziona Create View (Crea vista). In alternativa, puoi fare clic sul menu di una cartella e selezionare Crea vista dal menu per creare il file all'interno della cartella.
Imposta il nome della visualizzazione su un valore significativo.
Facoltativamente, puoi modificare i nomi delle colonne, specificare le colonne derivate e aggiungere filtri.
Quando utilizzi una misura di
type: countin un'esplorazione, la visualizzazione etichetta i valori risultanti con il nome della visualizzazione anziché con la parola Conteggio. Per evitare confusione, ti consigliamo di pluralizzare il nome della vista, selezionando Mostra nome campo completo in Serie nelle impostazioni di visualizzazione oppure di utilizzareview_labelcon una versione pluralizzata del nome della vista.
Definizione di una tabella derivata nativa in LookML
Se utilizzi tabelle derivate dichiarate in SQL o LookML nativo, l'output di una query derived_table&s è una tabella con un set di colonne. Quando la tabella derivata è espressa in SQL, i nomi delle colonne di output sono impliciti per la query SQL. Ad esempio, la query SQL seguente 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 Explore, include campi di misurazione e di dimensioni, aggiunge eventuali filtri applicabili e può anche specificare un ordinamento. Una tabella nativa derivata contiene tutti questi elementi più i nomi di output per le colonne.
Il seguente esempio restituisce una tabella derivata con tre colonne: user_id, lifetime_customer_value e lifetime_number_of_orders. Non è necessario scrivere manualmente la query in SQL, ma Looker crea invece la query per te utilizzando il campo Explore order_items specificato e alcuni dei campi Explore (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 attivare i campi di riferimento
Nel file della tabella nativa derivata, utilizza il parametro explore_source per puntare a un'esplorazione e definire le colonne e le altre caratteristiche desiderate per la tabella nativa derivata. Poiché stai puntando a un'esplorazione dall'interno del file di visualizzazione della tabella nativa derivata, devi anche includere il file contenente la definizione di Explore. Le esplorazioni sono generalmente definite all'interno di un file modello, ma nel caso di tabelle native derivate è più ordinato creare un file separato per l'esplorazione utilizzando l'estensione del file .explore.lkml, come descritto nella documentazione per la creazione di file di Esplora. In questo modo, nel file nativo della visualizzazione tabella derivata puoi includere un singolo file Esplora e non l'intero file modello. In tal caso:
- Il file di visualizzazione della tabella nativa derivata deve includere il file di Explore (Esplora). Ad esempio:
include: "/explores/order_items.explore.lkml" - Il file Explore (Esplora) deve includere i file di cui ha bisogno. Ad esempio:
include: "/views/order_items.view.lkml"
include: "/views/users.view.lkml" - Il modello deve includere il file Explore (Esplora). Ad esempio:
include: "/explores/order_items.explore.lkml"
I file di ascolto ascolteranno la connessione del modello in cui sono inclusi. Tieni presente questo fatto quando includi file di Explore (Esplora) nei modelli configurati con una connessione diversa dal modello principale di Esplora file. Se lo schema per la connessione del modello incluso è diverso dallo schema per la connessione del modello principale, può causare errori di query.
Definizione delle colonne delle tabelle native derivate
Come mostrato nell'esempio riportato sopra, utilizzi 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'originale Explore.
Spesso, è consigliabile che nella tabella di output il nome di una colonna sia diverso da quello dei campi nell'esplorazione originale. Nell'esempio riportato sopra, stiamo generando un calcolo del lifetime value per utente utilizzando la funzionalità Esplora di order_items. Nella tabella di output, total_revenue è 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 dice: "Crea una colonna di output denominata lifetime_value dal campo order_items.total_revenue".
column: lifetime_value {
field: order_items.total_revenue
}
Nomi di colonna impliciti
Se il parametro field non viene inserito in una dichiarazione di 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
}
equivale a
column: user_id {}
Creazione di colonne derivate per i valori calcolati
Puoi aggiungere parametri derived_column per specificare colonne che non esistono nel parametro explore_source Explore (Esplora). Ogni parametro derived_column ha un parametro sql che specifica come creare il valore.
Il calcolo sql può utilizzare qualsiasi colonna che hai specificato 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 aggiunge una colonna average_customer_order calcolata, che viene calcolata dalle colonne lifetime_customer_value e lifetime_number_of_orders nella tabella nativa derivata.
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 delle finestre SQL
Alcuni dialetti del database supportano le funzioni delle finestre, in particolare per creare numeri di sequenza, chiavi primarie, totali in esecuzione e cumulativi e altri calcoli multiriga utili. Una volta eseguita la query principale, qualsiasi dichiarazione derived_column viene eseguita in un pass separato.
Se il dialetto del database supporta le funzioni di finestra, puoi utilizzarle nella tabella nativa derivata. Crea un parametro derived_column con un parametro sql che contenga la funzione di finestra desiderata. Quando fai riferimento ai valori, devi utilizzare il nome della colonna definito nella tabella nativa derivata.
L'esempio seguente crea una tabella nativa derivata che include le colonne user_id, order_id e created_time. Quindi, utilizzando una colonna derivata con una funzione di finestra SQL ROW_NUMBER(), calcola una colonna che contiene 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
}
}
Aggiungere filtri a una tabella nativa derivata
Supponiamo di voler creare una tabella derivata del valore di un cliente negli ultimi 90 giorni. Vogliamo gli stessi calcoli che abbiamo eseguito sopra, ma vogliamo includere solo gli acquisti degli ultimi 90 giorni.
Abbiamo appena aggiunto un filtro alla derived_table che filtra per le transazioni degli ultimi 90 giorni. Il parametro filters per una tabella derivata utilizza la stessa sintassi che utilizzi 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 il codice SQL per la tabella derivata.
Inoltre, puoi utilizzare il sottoparametro dev_filters di explore_source con una tabella nativa derivata. Il parametro dev_filters consente di specificare i filtri che Looker applica solo alle versioni di sviluppo della tabella derivata, il che significa che puoi creare versioni filtrate e più piccole della tabella per eseguire l'iterazione e il test senza attendere la generazione dell'intera tabella dopo ogni modifica.
Il parametro dev_filters agisce insieme al parametro filters in modo 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 i filtri basati su modelli:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Essenzialmente viene utilizzato come codice seguente in un blocco sql:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field è il campo a cui si applica il filtro. to_field deve essere un campo del campo explore_source sottostante.
from_field specifica il campo da cui ottenere il filtro, se è presente un filtro in fase di esecuzione.
Nell'esempio bind_filters precedente, Looker applica l'eventuale filtro applicato al campo filtered_lookml_dt.filter_date e applica il filtro al campo users.created_date.
Puoi anche utilizzare il sottoparametro bind_all_filters di explore_source per passare tutti i filtri di runtime da una sottoquery Esplora a una tabella nativa derivata. Per ulteriori informazioni, consulta la pagina della documentazione relativa al parametro explore_source.
Ordinamento e limitazione delle tabelle native derivate
Se vuoi, puoi anche ordinare e limitare le tabelle derivate:
sorts: [order_items.count: desc]
limit: 10
Ricorda che un'esplorazione può visualizzare le righe in un ordine diverso da quello dell'ordinamento sottostante.
Convertire tabelle derivate native in fusi orari diversi
Puoi specificare il fuso orario per la tabella nativa derivata utilizzando il sottoparametro timezone:
timezone: "America/Los_Angeles"
Quando utilizzi il sottoparametro timezone, tutti i dati basati sul tempo nella tabella nativa derivata verranno convertiti nel fuso orario specificato. Consulta la pagina della documentazione sui valori timezone per un elenco dei fusi orari supportati.
Se non specifichi un fuso orario nella definizione della tabella derivata nativa, la tabella nativa derivata non eseguirà alcuna conversione di fuso orario sui dati basati sul tempo, ma i dati basati sul tempo verranno impostati automaticamente sul fuso orario del database.
Se la tabella nativa derivata non è permanente, puoi impostare il valore del fuso orario su "query_timezone" per utilizzare automaticamente il fuso orario della query attualmente in esecuzione.