Una tabella derivata è una query i cui risultati vengono utilizzati come se fosse una tabella fisica nel database. Una tabella derivata nativa si basa su una query che definisci utilizzando i termini LookML. È diversa dalla tabella derivata basata su SQL, che si basa su una query definita con i termini SQL. Rispetto alle tabelle derivate basate su SQL, sono molto più facili da leggere e comprendere quando si modellano i tuoi dati. Per saperne di più, consulta la sezione Tabelle derivate native e tabelle derivate basate su SQL della pagina della documentazione 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. Utilizza invece il parametro explore_source per specificare il metodo di esplorazione su cui basare la tabella derivata, le colonne desiderate e altre caratteristiche desiderate.
Puoi anche chiedere a Looker di creare la tabella LookML derivata da una query SQL Runner, come descritto nella pagina della documentazione Utilizzo di SQL Runner per la creazione di tabelle derivate.
Utilizzo di un'esplorazione per iniziare a definire le tabelle native derivate
A partire da un'esplorazione, Looker può generare LookML per tutta o la maggior parte della tua 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 della tabella:
Seleziona il menu a forma di ingranaggio Esplora azioni, quindi seleziona GetML.
Fai clic sulla scheda Tabella derivata per visualizzare il LookML per creare una tabella derivata nativa per Explore.
Copia il codice LookML.
Ora che hai copiato il codice LookML generato, incollalo in un file di visualizzazione:
In modalità di sviluppo, vai ai tuoi file di progetto.
Fai clic sul segno + in cima all'elenco dei file di progetto nell'IDE di Looker e seleziona 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 qualcosa di 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é 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 utilizzando unaview_labelcon una versione pluralizzata del nome vista.
Definizione di una tabella derivata nativa in LookML
Se utilizzi tabelle derivate dichiarate in SQL o LookML nativo, l'output di una query di derived_table è una tabella con un insieme di colonne. Quando la tabella derivata è espressa in SQL, i nomi della colonna di output sono impliciti rispetto alla query SQL. Ad esempio, la query SQL riportata di seguito 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 Explore, include campi di misurazione e dimensione, aggiunge eventuali filtri applicabili e può anche specificare un ordinamento. Una tabella derivata nativa contiene tutti questi elementi più i nomi di output per le colonne.
L'esempio semplice riportato di seguito genera 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 la crea per conto tuo utilizzando la specifica Explore order_items e alcuni 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 abilitare i campi di riferimento
Nel file di visualizzazione della tabella derivata nativa, utilizzi il parametro explore_source per esplorare un'esplorazione e definire le colonne desiderate e altre caratteristiche desiderate per la tabella derivata nativa. Poiché stai puntando a un'esplorazione dall'interno del file di visualizzazione della tabella nativa derivata, devi anche includere il file contenente la definizione dell'esplorazione. Le esplorazioni di solito vengono definite all'interno di un file modello, ma nel caso di tabelle derivate native è più pulito creare un file separato per l'esplorazione utilizzando l'estensione del file .explore.lkml, come descritto nella documentazione per la creazione di file Explore. In questo modo, nel file nativo della visualizzazione della tabella derivata puoi includere un singolo file di esplorazione e non l'intero file del modello. In tal caso:
- Il file di visualizzazione della tabella derivata nativa deve includere il file Esplora. Ad esempio:
include: "/explores/order_items.explore.lkml" - Il file Esplora deve includere i file delle viste 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 ascoltano la connessione del modello in cui sono inclusi. Tieni presente questo aspetto quando includi file di Explore (Esplora) nei modelli configurati con una connessione diversa dal modello principale del file Explore. Se lo schema per la connessione del modello include diverso da quello della connessione del modello principale, può causare errori nelle query.
Definizione delle colonne delle tabelle derivate native
Come indicato nell'esempio riportato sopra, utilizzi column per specificare le colonne di output della tabella derivata.
Specifica dei nomi di colonna
Per la colonna user_id, il nome della colonna corrisponde al nome del campo specificato nell'esplorazione originale.
Spesso, nella tabella di output è preferibile assegnare un nome diverso da quello dei campi nell'esplorazione originale. Nell'esempio precedente, viene generato un calcolo del lifetime value per utente utilizzando la funzionalità Esplora di order_items. Nella tabella di output, total_revenue è un lifetime_customer_value del cliente.
La dichiarazione column supporta la dichiarazione di un nome di output diverso dal campo di immissione. Ad esempio, il codice riportato di seguito indica "Crea una colonna di output denominata lifetime_value dal campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nomi di colonne 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 delle 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 sql può utilizzare qualsiasi colonna specificata utilizzando i parametri column. Le colonne derivate non possono includere funzioni aggregate, ma possono includere calcoli eseguibili su un'unica 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 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 a più righe. Una volta eseguita la query principale, tutte le dichiarazioni derived_column vengono eseguite in un pass separato.
Se il dialetto del tuo database supporta le funzioni delle finestre, puoi utilizzarle nella tabella nativa derivata. Crea un parametro derived_column con un parametro sql che contenga la funzione finestra desiderata. Quando fai riferimento ai valori, devi utilizzare il nome della colonna come 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 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
}
}
Aggiunta di 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 semplicemente aggiunto un filtro alla derived_table che filtra le transazioni negli 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 l'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 filtri che Looker applica solo alle versioni di sviluppo della tabella derivata, il che significa che puoi creare versioni filtrate più piccole della tabella da iterare e testare senza attendere l'intera tabella dopo ogni modifica.
Il parametro dev_filters agisce in combinazione con il parametro filters in modo che tutti i filtri vengano applicati alla versione di sviluppo della tabella. Se dev_filters e filters specificano filtri per la stessa colonna, dev_filters ha la precedenza per la versione di sviluppo della tabella.
Per ulteriori informazioni, consulta Lavorare più velocemente in modalità 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
}
In sostanza, equivale a utilizzare il 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 a partire dalla 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 riportato sopra, Looker applica il filtro applicato al campo filtered_lookml_dt.filter_date e lo applica al campo users.created_date.
Puoi anche utilizzare il sottoparametro bind_all_filters di explore_source per passare tutti i filtri di runtime da Esplora a una sottoquery di tabella nativa derivata. Per ulteriori informazioni, consulta la pagina della documentazione del 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
Tieni presente che un'esplorazione può visualizzare le righe in un ordine diverso da quello dell'ordinamento sottostante.
Conversione delle 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 della tabella derivata nativa verranno convertiti nel fuso orario specificato. Consulta la pagina della documentazione relativa ai valori timezone per un elenco dei fusi orari supportati.
Se non specifichi un fuso orario nella tua definizione di tabella derivata nativa, la tabella derivata nativa non eseguirà alcuna conversione di fuso orario sui dati basati sul tempo, ma i dati basati sul tempo verranno impostati sul fuso orario del database per impostazione predefinita.
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.