In Looker, le tabelle derivate permanenti (PDT) vengono scritte nello schema temporaneo del tuo database. Looker persiste e ricostruisce una PDT in base alla sua strategia di persistenza. Quando viene attivata la ricompilazione di una PDT, per impostazione predefinita Looker ricostruisce l'intera tabella.
Una PDT incrementale è una PDT creata da Looker aggiungendo nuovi dati alla tabella anziché ricrearla nella sua interezza:
Se il tuo dialetto supporta le PDT incrementali, puoi trasformarle nei seguenti tipi di PDT incrementali:
La prima volta che esegui una query su una PDT incrementale, Looker crea l'intera PDT per recuperare i dati iniziali. Se la tabella è di grandi dimensioni, la build iniziale potrebbe richiedere una notevole quantità di tempo, così come accade per qualsiasi tabella di grandi dimensioni. Una volta creata la tabella iniziale, le build successive saranno incrementali e richiederanno meno tempo se la PDT incrementale è configurata in modo strategico.
Per le PDT incrementali, tieni presente quanto segue:
- Le PDT incrementali sono supportate solo per le PDT che utilizzano una strategia di persistenza basata su trigger (
datagroup_trigger
,sql_trigger_value
ointerval_trigger
). Le PDT incrementali non sono supportate per le PDT che utilizzano la strategia di persistenzapersist_for
. - Per le PDT basate su SQL, la query della tabella deve essere definita utilizzando il parametro
sql
per essere utilizzata come PDT incrementale. Le PDT basate su SQL definite con il parametrosql_create
o il parametrocreate_process
non possono essere create in modo incrementale. Come puoi vedere nell'Esempio 1 in questa pagina, Looker utilizza un comando INSERT o MERGE per creare gli incrementi per una PDT incrementale. La tabella derivata non può essere definita utilizzando istruzioni DDL (Data Definition Language) personalizzate, poiché Looker non è in grado di determinare quali istruzioni DDL sarebbero necessarie per creare un incremento accurato. - La tabella di origine della PDT incrementale deve essere ottimizzata per le query basate sul tempo. Nello specifico, la colonna basata sul tempo utilizzata per la chiave di incremento deve avere una strategia di ottimizzazione, ad esempio partizionamento, sortkeys, index o qualsiasi strategia di ottimizzazione supportata per il tuo dialetto. L'ottimizzazione della tabella di origine è vivamente consigliata perché ogni volta che la tabella incrementale viene aggiornata, Looker esegue query sulla tabella di origine per determinare i valori più recenti della colonna basata sul tempo utilizzata per la chiave di incremento. Se la tabella di origine non è ottimizzata per queste query, la query di Looker per i valori più recenti potrebbe essere lenta e costosa.
Definizione di una PDT incrementale
Puoi utilizzare i seguenti parametri per trasformare una PDT in una PDT incrementale:
increment_key
(necessario per rendere la PDT una PDT incrementale): definisce il periodo di tempo per il quale devono essere eseguite query sui nuovi record.{% incrementcondition %}
Filtro liquido (necessario per creare una PDB basata su SQL; non applicabile alle PDL basate su LookML): connette la chiave di incremento alla colonna data/ora del database su cui si basa la chiave di incremento. Per ulteriori informazioni, consulta la pagina della documentazione relativa aincrement_key
.- (Facoltativo)
increment_offset
: un numero intero che definisce il numero di periodi di tempo precedenti (in base alla granularità della chiave di incremento) che vengono ricreati per ogni build incrementale. Il parametroincrement_offset
è utile nel caso di dati arrivati in ritardo, dove i periodi di tempo precedenti potrebbero avere nuovi dati che non erano inclusi quando l'incremento corrispondente è stato originariamente creato e aggiunto alla PDT.
Consulta la pagina della documentazione relativa al parametro increment_key
per esempi che mostrano come creare PDT incrementali da tabelle derivate native permanenti, tabelle derivate basate su SQL permanenti e tabelle aggregate.
Ecco un semplice esempio di un file di vista che definisce una PDT incrementale basata su LookML:
view: flights_lookml_incremental_pdt {
derived_table: {
indexes: ["id"]
increment_key: "departure_date"
increment_offset: 3
datagroup_trigger: flights_default_datagroup
distribution_style: all
explore_source: flights {
column: id {}
column: carrier {}
column: departure_date {}
}
}
dimension: id {
type: number
}
dimension: carrier {
type: string
}
dimension: departure_date {
type: date
}
}
Questa tabella verrà creata interamente la prima volta che viene eseguita una query. Dopodiché, la PDT verrà ricreata in incrementi di un giorno (increment_key: departure_date
), fino a tre giorni prima (increment_offset: 3
).
La chiave di incremento si basa sulla dimensione departure_date
, che in realtà è l'intervallo di tempo date
del gruppo di dimensioni departure
. Per una panoramica del funzionamento dei gruppi di dimensioni, consulta la pagina della documentazione relativa al parametro dimension_group
. Il gruppo di dimensioni e l'intervallo di tempo sono entrambi definiti nella visualizzazione flights
, che è la explore_source
per questa PDT. Ecco come viene definito il gruppo di dimensioni departure
nel file di visualizzazione flights
:
...
dimension_group: departure {
type: time
timeframes: [
raw,
date,
week,
month,
year
]
sql: ${TABLE}.dep_time ;;
}
...
Interazione dei parametri di incremento e della strategia di persistenza
Le impostazioni increment_key
e increment_offset
di una PDT sono indipendenti dalla strategia di persistenza della PDT:
- La strategia di persistenza della PDT incrementale determina solo quando la PDT aumenta. Il builder di PDT non modifica la PDT incrementale a meno che non venga attivata la strategia di persistenza della tabella o a meno che la PDT non venga attivata manualmente con Ricrea le tabelle derivate e Esegui in un'esplorazione.
- Quando la PDT aumenta, il generatore di PDT stabilisce quando sono stati precedentemente aggiunti alla tabella i dati più recenti, in termini di incremento di tempo più attuale (il periodo di tempo definito dal parametro
increment_key
). In base a ciò, il generatore di PDT troncherà i dati all'inizio dell'incremento di tempo più recente nella tabella, quindi creerà l'ultimo incremento da lì. - Se la PDT ha un parametro
increment_offset
, il builder di PDT ricrea anche il numero di periodi di tempo precedenti specificato nel parametroincrement_offset
. I periodi di tempo precedenti vanno a ritroso a partire dall'inizio dell'incremento di tempo più recente (il periodo di tempo definito dal parametroincrement_key
).
Gli scenari di esempio riportati di seguito illustrano come vengono aggiornate le PDT incrementali mostrando l'interazione di increment_key
, increment_offset
e della strategia di persistenza.
Esempio 1
In questo esempio viene utilizzata una PDT con le seguenti proprietà:
- Chiave di aumento: data
- Incrementa offset: 3
- Strategia di persistenza: attivata una volta al mese, il primo giorno del mese
Ecco come verrà aggiornata questa tabella:
- Una strategia di persistenza mensile significa che la tabella viene creata automaticamente una volta al mese. Ciò significa che il 1° giugno, ad esempio, l'ultima riga della tabella sarà stata aggiunta il 1° maggio.
- Poiché questo file PDT ha una chiave di incremento basata sulla data, lo strumento di creazione del file PDT troncherà il 1° maggio all'inizio della giornata e ricostruirà i dati per il 1° maggio e fino al giorno corrente, 1° giugno.
- Inoltre, questa PDT ha un offset incrementale di
3
. Di conseguenza, lo strumento per la creazione del report PDT ricostruisce anche i dati dei tre periodi di tempo (giorni) precedenti al 1° maggio. Il risultato è che i dati vengono ricreati per il 28, 29 e 30 aprile e fino al giorno corrente del 1° giugno.
In termini SQL, di seguito è riportato il comando che il generatore di PDT verrà eseguito il 1° giugno per determinare le righe della PDT esistente che devono essere ricreate:
## Example SQL for BigQuery:
SELECT FORMAT_TIMESTAMP('%F %T',TIMESTAMP_ADD(MAX(pdt_name),INTERVAL -3 DAY))
## Example SQL for other dialects:
SELECT CAST(DATE_ADD(MAX(pdt_name),INTERVAL -3 DAY) AS CHAR)
Ecco il comando SQL che il builder di PDT eseguirà il 1° giugno per creare l'ultimo incremento:
## Example SQL for BigQuery:
MERGE INTO [pdt_name] USING (SELECT [columns]
WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM'))
AS tmp_name ON FALSE
WHEN NOT MATCHED BY SOURCE AND created_date >= TIMESTAMP('4/28/21 12:00:00 AM')
THEN DELETE
WHEN NOT MATCHED THEN INSERT [columns]
## Example SQL for other dialects:
START TRANSACTION;
DELETE FROM [pdt_name]
WHERE created_date >= TIMESTAMP('4/28/21 12:00:00 AM');
INSERT INTO [pdt_name]
SELECT [columns]
FROM [source_table]
WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM');
COMMIT;
Esempio 2
In questo esempio viene utilizzata una PDT con le seguenti proprietà:
- Strategia di persistenza: attivata una volta al giorno
- Chiave di incremento: mese
- Incrementa offset: 0
Ecco come verrà aggiornata questa tabella il 1° giugno:
- La strategia di persistenza giornaliera significa che la tabella viene creata automaticamente una volta al giorno. Il 1° giugno, l'ultima riga della tabella verrà aggiunta il 31 maggio.
- Poiché la chiave di incremento si basa sul mese, il generatore PDT tronca dal 31 maggio all'inizio del mese e ricostruisce i dati per tutto il mese di maggio e fino al giorno corrente, incluso il 1° giugno.
- Poiché questa PDT non ha un offset di incremento, non viene ricreato alcun periodo di tempo precedente.
Ecco come verrà aggiornata questa tabella il 2 giugno:
- Il 2 giugno, l'ultima riga della tabella verrà aggiunta il 1° giugno.
- Poiché il generatore di file PDT tronca i dati fino all'inizio del mese di giugno, poi ricostruisce i dati a partire dal 1° giugno fino al giorno corrente, i dati vengono ricostruiti solo per il 1° e il 2 giugno.
- Poiché questa PDT non ha un offset di incremento, non viene ricreato alcun periodo di tempo precedente.
Esempio 3
Questo esempio utilizza un PDT con le seguenti proprietà:
- Chiave di incremento: mese
- Increment offset: 3
- Strategia di persistenza: attivata una volta al giorno
Questo scenario illustra una configurazione scadente per una PDT incrementale, poiché si tratta di una PDT che attiva ogni giorno un offset di tre mesi. Ciò significa che ogni giorno verranno ricreati almeno tre mesi di dati, il che comporterebbe un uso molto inefficiente di una PDT incrementale. Tuttavia, è uno scenario interessante da esaminare come modo di comprendere come funzionano le PDT incrementali.
Ecco come verrà aggiornata questa tabella il 1° giugno:
- La strategia di persistenza giornaliera significa che la tabella viene creata automaticamente una volta al giorno. Ad esempio, il 1° giugno, l'ultima riga della tabella sarà stata aggiunta il 31 maggio.
- Poiché la chiave di incremento si basa sul mese, il builder di PDT verrà troncato dal 31 maggio all'inizio del mese e ricreando i dati per tutto il mese di maggio e fino al giorno corrente, incluso il 1° giugno.
- Inoltre, questa PDT ha un offset incrementale di
3
. Ciò significa che lo strumento di creazione di PDT ricrea anche i dati dei tre periodi di tempo (mesi) precedenti prima di maggio. Il risultato è che i dati vengono ricreati da febbraio, marzo e aprile fino al giorno corrente, il 1° giugno.
Ecco come verrà aggiornata questa tabella il 2 giugno:
- Il 2 giugno, l'ultima riga della tabella verrà aggiunta il 1° giugno.
- Lo strumento di creazione di PDT troncherà il mese dal 1° giugno e ricrei i dati per il mese di giugno, incluso il 2 giugno.
- Inoltre, a causa dell'offset dell'incremento, il builder di PDT ricrea i dati dei tre mesi precedenti prima di giugno. Il risultato è che i dati vengono ricreati da marzo, aprile e maggio fino al giorno corrente, ovvero il 2 giugno.
Testare un PDT incrementale in modalità di sviluppo
Prima di eseguire il deployment di una nuova PDT incrementale nell'ambiente di produzione, puoi testarla per assicurarti che venga creata e incrementata. Per testare una PDT incrementale in modalità di sviluppo:
Crea un'esplorazione per la PDT:
- In un file del modello associato, utilizza il parametro
include
per includere il file di visualizzazione della PDT nel file del modello. - Nello stesso file del modello, utilizza il parametro
explore
per creare un'esplorazione per la vista della PDT incrementale.
include: "/views/e_faa_pdt.view" explore: e_faa_pdt {}
- In un file del modello associato, utilizza il parametro
Apri Esplora per la PDT. Per farlo, seleziona il pulsante Visualizza azioni file e poi un nome di esplorazione.
Nell'esplorazione, seleziona alcune dimensioni o misure e fai clic su Esegui. Looker creerà quindi l'intera PDT. Se questa è la prima query che hai eseguito sulla PDT incrementale, il generatore di PDT creerà l'intera PDT per ottenere i dati iniziali. Se la tabella è di grandi dimensioni, la build iniziale potrebbe richiedere una notevole quantità di tempo, così come accade per qualsiasi tabella di grandi dimensioni.
Puoi verificare che la PDT iniziale sia stata creata nei seguenti modi:
- Se disponi dell'autorizzazione
see_logs
, puoi verificare che la tabella sia stata creata cercando nel log eventi PDC. Se non vedi gli eventi di creazione PDT nel log eventi PDT, controlla le informazioni sullo stato nella parte superiore dell'esplorazione del log eventi PDT. Se è indicato "Dalla cache", puoi selezionare Svuota cache e Aggiorna per ottenere informazioni più recenti. - Altrimenti, puoi leggere i commenti nella scheda SQL della barra Dati di Esplora. La scheda SQL mostra la query e le azioni che verranno intraprese quando esegui la query in Esplora. Ad esempio, se i commenti nella scheda SQL indicano
,questa è l'azione che verrà eseguita quando fai clic su Esegui.-- generate derived table e_incremental_pdt
- Se disponi dell'autorizzazione
Dopo aver creato la build iniziale della PDT, richiedi una build incrementale della PDT utilizzando Ricrea le tabelle derivate e Esegui da Esplora.
Puoi utilizzare gli stessi metodi di prima per verificare che la PDT venga creata in modo incrementale:
- Se disponi dell'autorizzazione
see_logs
, puoi utilizzare il log eventi PDT per visualizzare gli eventicreate increment complete
per la PDT incrementale. Se non vedi questo evento nel log eventi PDT e lo stato della query è "dalla cache", seleziona Svuota cache e Aggiorna per ottenere informazioni più recenti. - Guarda i commenti nella scheda SQL della barra Dati di Esplora. In questo caso, i commenti indicheranno che il PDT è stato incrementato. Ad esempio:
-- increment persistent derived table e_incremental_pdt to generation 2
- Se disponi dell'autorizzazione
Dopo aver verificato che la PDT è stata creata e incrementata correttamente, se non vuoi mantenere l'esplorazione dedicata per la PDT, puoi rimuovere o commentare i parametri
explore
einclude
della PDT dal file del modello.
Una volta creata la PDT in modalità di sviluppo, la stessa tabella verrà utilizzata per la produzione dopo il deployment delle modifiche, a meno che non apporti ulteriori modifiche alla definizione della tabella. Per saperne di più, consulta la sezione Tabelle permanenti in modalità di sviluppo nella pagina della documentazione Tabelle derivate in Looker.
Dialetti di database supportati per le PDT incrementali
Affinché Looker supporti PDT incrementali nel tuo progetto Looker, il dialetto del database deve supportare i comandi DDL (Data Definition Language) che consentono l'eliminazione e l'inserimento di righe.
La tabella seguente mostra quali dialetti supportano PDT incrementali nell'ultima release di Looker (per Databricks, le PDT incrementali sono supportate solo su Databricks 12.1 e versioni successive:
Dialetto | Supportato? |
---|---|
Valanga Actia | No |
Amazon Athena | No |
Amazon Aurora MySQL | No |
Amazon Redshift | Sì |
Druid Apache | No |
Apache Druid 0.13 o versioni successive | No |
Apache Druid 0.18 o versioni successive | No |
Apache Hive 2.3 o versioni successive | No |
Apache Hive 3.1.2 e versioni successive | No |
Apache Spark 3 e versioni successive | No |
ClickHouse | No |
Cloudera Impala 3.1 e versioni successive | No |
Cloudera Impala 3.1 o versioni successive con driver nativo | No |
Cloudera Impala con driver nativo | No |
DataVirtuality | No |
Databricks | Sì |
Denodo 7 | No |
Denodo 8 | No |
Dremio | No |
Dremio 11+ | No |
Exasol | No |
Firebolt | No |
SQL precedente di Google BigQuery | No |
SQL standard di Google BigQuery | Sì |
PostgreSQL di Google Cloud | Sì |
Google Cloud SQL | No |
Google Spanner | No |
Greenplum | Sì |
HyperSQL | No |
Netezza di IBM | No |
MariaDB | No |
PostgreSQL Microsoft Azure | Sì |
Database SQL di Microsoft Azure | No |
Microsoft Azure Synapse Analytics | Sì |
Microsoft SQL Server 2008 e versioni successive | No |
Microsoft SQL Server 2012 o versioni successive | No |
Microsoft SQL Server 2016 | No |
Microsoft SQL Server 2017 o versioni successive | No |
MongoBI | No |
MySQL | Sì |
MySQL 8.0.12 e versioni successive | Sì |
Oracle | No |
Oracle ADWC | No |
PostgreSQL 9.5 e versioni successive | Sì |
PostgreSQL pre-9.5 | Sì |
PrestoDB | No |
PrestoSQL | No |
SAP HANA 2 o versioni successive | No |
SingleStore | No |
SingleStore 7 e versioni successive | No |
Snowflake | Sì |
Teradata | No |
Trino | No |
Vettoriale | No |
Vertica | Sì |