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 si basa su una query definita utilizzando i termini LookML. È diversa da una tabella derivata basata su SQL, che si basa su una query 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 ulteriori informazioni, consulta la sezione Tabelle derivate native e tabelle derivate basate su SQL della pagina della documentazione dedicata alle tabelle derivate in Looker.
Le tabelle derivate native e quelle basate su SQL vengono definite in LookML utilizzando il derived_table parametro a livello di visualizzazione. Tuttavia, con le tabelle derivate native non è necessario creare una query SQL. Al contrario, utilizzi il parametro explore_source per specificare l'esplorazione su cui basare la tabella derivata, le colonne desiderate e altre caratteristiche desiderate.
Puoi anche chiedere a Looker di creare il codice LookML della tabella derivata da una query di SQL Runner, come descritto nella pagina della documentazione Utilizzo di SQL Runner per creare tabelle derivate.
Utilizzo di un'esplorazione per iniziare a definire le tabelle derivate native
A partire da un'esplorazione, Looker può generare codice LookML per tutte o la maggior parte delle tabelle derivate. Crea un'esplorazione e seleziona tutti i campi che vuoi includere nella tabella derivata. Poi, per generare il codice LookML della tabella derivata nativa:
Seleziona il menu a forma di ingranaggio Azioni di esplorazione e seleziona Ottieni LookML.
Fai clic sulla scheda Tabella derivata per visualizzare il codice LookML per la creazione di una tabella derivata nativa per l'esplorazione.
Copia il codice LookML.
Ora che hai copiato il codice LookML generato, incollalo in un file di vista:
In modalità di sviluppo, vai ai file del progetto.
Fai clic su + nella parte superiore dell'elenco dei file di 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.
Assegna alla visualizzazione un nome significativo.
(Facoltativo) Modifica i nomi delle colonne, specifica le colonne derivate e aggiungi filtri.
Quando utilizzi una misura di
type: countin un'esplorazione, le etichette di visualizzazione contrassegnano i valori risultanti con il nome della visualizzazione anziché con la parola Conteggio. Per evitare confusione, metti il nome della visualizzazione al plurale. Puoi modificare il nome della visualizzazione selezionando Mostra nome completo del campo in Serie nelle impostazioni di visualizzazione o utilizzando il parametroview_labelcon una versione al plurale del nome della visualizzazione.
Definizione di una tabella derivata nativa in LookML
Indipendentemente dal fatto che utilizzi tabelle derivate dichiarate in SQL o in LookML nativo, l'output della query di una derived_table's è una tabella con un insieme di colonne. Quando la tabella derivata è espressa in SQL, i nomi delle colonne di output sono impliciti nella 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 si basa 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 più i 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 crea la query per te utilizzando l'esplorazione specificata order_items e alcuni dei campi di questa 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 i riferimenti ai campi
Nel file di vista della tabella derivata nativa, utilizzi il parametro explore_source per puntare a un'esplorazione e definire le colonne e altre caratteristiche della tabella derivata nativa.
Nel file di vista della tabella derivata nativa, non è obbligatorio utilizzare il include parametro per puntare al file che contiene la definizione dell'esplorazione. Se non hai l'istruzione include, l'IDE di Looker non autosuggest i nomi dei campi né verificherà i riferimenti ai campi durante la creazione della tabella derivata nativa. In alternativa, puoi utilizzare lo strumento di convalida LookML per verificare i campi a cui fai riferimento nella tabella derivata nativa.
Tuttavia, se vuoi abilitare il suggerimento automatico e la verifica immediata dei campi nell'IDE di Looker o se hai un progetto LookML complesso con più esplorazioni con lo stesso nome o con la possibilità di riferimenti circolari, puoi utilizzare il parametro include per puntare alla posizione della definizione dell'esplorazione.
Le esplorazioni vengono spesso definite all'interno di un file di modello, ma, nel caso delle tabelle derivate native, è più pulito creare un file separato per l'esplorazione. I file di esplorazione LookML hanno l'estensione .explore.lkml, come descritto nella documentazione relativa a Creazione di file di esplorazione. In questo modo, nel file di visualizzazione della tabella derivata nativa puoi includere un singolo file Explore e non l'intero file del modello.
Se vuoi creare un file Explore separato e utilizzare il parametro include per puntare al file Explore nel file di vista della tabella derivata nativa, assicurati che i file LookML soddisfino i seguenti requisiti:
- Il file di vista 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 di visualizzazione di cui ha bisogno. 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"
Definizione delle colonne della tabella derivata nativa
Come mostrato nell'esempio precedente, 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'esplorazione originale.
Spesso, nella tabella di output vuoi un nome di colonna diverso dal nome dei campi nell'esplorazione originale. L'esempio precedente ha prodotto un calcolo del lifetime value per 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 seguente codice indica a Looker di "creare una colonna di output denominata lifetime_value dal campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nomi delle colonne impliciti
Se il parametro field viene omesso da una dichiarazione di colonna, si presume che sia <explore_name>.<field_name>. Ad esempio, se hai specificato explore_source: order_items, allora
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 le 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 tutte le colonne specificate 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, con la differenza che aggiunge 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 finestra, in particolare per creare numeri di sequenza, chiavi primarie, totali parziali e cumulativi e altri calcoli multi-riga utili. Dopo l'esecuzione della query principale, le 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 che contenga la funzione finestra desiderata. Quando fai riferimento ai valori, devi utilizzare il nome della colonna così come è definito nella tabella derivata nativa.
L'esempio seguente crea una tabella derivata nativa che include le colonne user_id, order_id e created_time. Poi, 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 derivata nativa
Supponiamo che tu voglia creare una tabella derivata del valore di un cliente negli ultimi 90 giorni. Vuoi gli stessi calcoli eseguiti nell'esempio precedente, ma vuoi includere solo gli acquisti degli ultimi 90 giorni.
Devi solo aggiungere un filtro alla derived_table che filtri le transazioni degli 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 il codice SQL per la tabella derivata.
Inoltre, puoi utilizzare il dev_filters sottoparametro 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 per eseguire iterazioni e test senza attendere la creazione della tabella completa dopo ogni modifica.
Il parametro dev_filters funziona in combinazione con il parametro filters, in modo che tutti i filtri vengano applicati alla versione di sviluppo della tabella. Se sia dev_filters sia filters specificano filtri per la stessa colonna, dev_filters ha la precedenza per la versione di sviluppo della tabella.
Per ulteriori informazioni, consulta la sezione Lavorare più velocemente in modalità di sviluppo.
Utilizzo dei filtri con modelli
Puoi utilizzare bind_filters per includere filtri con modelli:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
È essenzialmente la stessa cosa che 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 della explore_source sottostante.
from_field specifica il campo da cui ottenere il filtro, se è presente un filtro in fase di runtime.
Nell'esempio bind_filters precedente, Looker prenderà qualsiasi filtro applicato al campo filtered_lookml_dt.filter_date e lo applicherà al campo users.created_date.
Puoi anche utilizzare il bind_all_filters sottoparametro di explore_source per passare tutti i filtri di runtime da un'esplorazione a una sottoquery di tabella derivata nativa. Per ulteriori informazioni, 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
Tieni presente che un'esplorazione può visualizzare 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 vengono convertiti nel fuso orario specificato. Per un elenco dei fusi orari supportati, consulta la pagina della documentazione relativa ai valori timezone.
Se non specifichi un fuso orario nella definizione della tabella derivata nativa, la tabella derivata nativa non eseguirà alcuna conversione del fuso orario sui dati basati sul tempo e, al contrario, i dati basati sul tempo utilizzeranno 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" per utilizzare automaticamente il fuso orario della query in esecuzione.