In Looker, le tabelle derivate permanenti (PDT) vengono scritte nello schema temporaneo del database. Looker rende permanente e ricostruisce una PDT in base alla sua strategia di persistenza. Quando viene attivata la ricostruzione 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é ricostruirla nella sua interezza:
Se il tuo dialetto supporta le PDT incrementali, puoi trasformare i seguenti tipi di PDT in 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 compilazione iniziale potrebbe richiedere molto tempo, così come la compilazione di 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.
Tieni presente quanto segue per le PDT incrementali:
- Le PDT incrementali sono supportate solo per le PDT che utilizzano una strategia di persistenza basata su trigger (
datagroup_trigger,sql_trigger_valueointerval_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
sqlper essere utilizzata come PDT incrementale. I PDT basati su SQL definiti con il parametrosql_createo con il parametrocreate_processnon possono essere creati in modo incrementale. Come puoi vedere nell'esempio 1 in questa pagina, Looker utilizza un comando INSERT o MERGE per creare gli incrementi per un PDT incrementale. La tabella derivata non può essere definita utilizzando istruzioni DDL (Data Definition Language) personalizzate, poiché Looker non sarebbe in grado di determinare quali istruzioni DDL sarebbero necessarie per creare un incremento accurato. - La tabella di origine del file 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 dispendiosa.
Definire un PDT incrementale
Puoi utilizzare i seguenti parametri per trasformare una PDT in una PDT incrementale:
increment_key(obbligatorio per rendere la PDT incrementale): definisce il periodo di tempo per cui devono essere eseguite query sui nuovi record.{% incrementcondition %}Filtro Liquid (obbligatorio per rendere una PDT basata su SQL una PDT incrementale; non applicabile alle PDT basate su LookML): collega la chiave di incremento alla colonna di tempo del database su cui si basa. Per ulteriori informazioni, consulta la pagina della documentazione relativa aincrement_key.increment_offset(facoltativo): un numero intero che definisce il numero di periodi di tempo precedenti (con la granularità della chiave di incremento) che vengono ricostruiti per ogni compilazione incrementale. Il parametroincrement_offsetè utile nel caso di dati in ritardo, in cui i periodi di tempo precedenti potrebbero contenere nuovi dati che non sono stati inclusi quando l'incremento corrispondente è stato creato e aggiunto alla PDT.
Consulta la pagina della documentazione del parametro increment_key per esempi che mostrano come creare PDT incrementali da tabelle derivate native permanenti, tabelle derivate permanenti basate su SQL e tabelle aggregate.
Ecco un semplice esempio di file di visualizzazione che definisce un PDT incrementale basato 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 nella sua interezza la prima volta che viene eseguita una query. Dopodiché, il PDT verrà ricostruito in incrementi di un giorno (increment_key: departure_date) arretrando di tre giorni (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 del parametro dimension_group). Il gruppo di dimensioni e l'intervallo di tempo sono entrambi definiti nella visualizzazione flights, che è il 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 viene incrementata. Il generatore di PDT non modifica la PDT incrementale, a meno che non venga attivata la strategia di persistenza della tabella o che la PDT non venga attivata manualmente con l'opzione Ricostruisci tabelle derivate ed esegui in un'esplorazione.
- Quando la PDT viene incrementata, il relativo generatore determina quando i dati più recenti sono stati aggiunti alla tabella in termini di incremento temporale più recente (il periodo di tempo definito dal parametro
increment_key). In base a questo, lo strumento di creazione della tabella PDT tronca i dati all'inizio dell'incremento di tempo più recente nella tabella, quindi crea l'incremento più recente da lì. - Se la PDT ha un parametro
increment_offset, il generatore di PDT ricostruirà anche il numero di periodi di tempo precedenti specificati 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
Questo esempio utilizza un PDT con le seguenti proprietà:
- Incrementa chiave: 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 indica che la tabella viene creata automaticamente una volta al mese. Ciò significa che, ad esempio, il 1° giugno 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, questo PDT ha un offset di incremento pari a
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. Di conseguenza, i dati vengono ricostruiti per i giorni 28, 29 e 30 aprile e fino al giorno 1° giugno.
In termini SQL, ecco il comando che il generatore di PDT eseguirà il 1° giugno per determinare le righe del PDT esistente che devono essere ricostruite:
## 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)
Di seguito è riportato il comando SQL che il generatore PDT eseguirà il 1° giugno per creare l'incremento più recente:
## 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
Questo esempio utilizza un PDT con le seguenti proprietà:
- Strategia di persistenza: attivata una volta al giorno
- Incrementa chiave: mese
- Incrementa offset: 0
Ecco come verrà aggiornata questa tabella il 1° giugno:
- La strategia di persistenza giornaliera prevede che la tabella venga creata automaticamente una volta al giorno. Il 1° giugno, l'ultima riga della tabella sarà stata 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 vengono ricostruiti periodi di tempo precedenti.
Ecco come verrà aggiornata questa tabella il 2 giugno:
- Il 2 giugno, l'ultima riga della tabella sarà stata 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 vengono ricostruiti periodi di tempo precedenti.
Esempio 3
Questo esempio utilizza un PDT con le seguenti proprietà:
- Incrementa chiave: mese
- Incrementa offset: 3
- Strategia di persistenza: attivata una volta al giorno
Questo scenario illustra una configurazione non ottimale per un'attività di pianificazione delle pubblicazioni incrementali, poiché si tratta di un'attività di pianificazione delle pubblicazioni con attivazione giornaliera e un offset di tre mesi. Ciò significa che ogni giorno verranno ricostruiti almeno tre mesi di dati, il che rappresenta un utilizzo molto inefficiente di un PDT incrementale. Tuttavia, è uno scenario interessante da esaminare per capire come funzionano i PDT incrementali.
Ecco come verrà aggiornata questa tabella il 1° giugno:
- La strategia di persistenza giornaliera prevede che la tabella venga creata automaticamente una volta al giorno. Il 1° giugno, ad esempio, l'ultima riga della tabella sarà stata 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.
- Inoltre, questo PDT ha un offset di incremento pari a
3. Ciò significa che lo Strumento per la creazione di pacchetti di dati ricostruisce anche i dati dei tre periodi di tempo (mesi) precedenti a maggio. Di conseguenza, i dati vengono ricostruiti da febbraio, marzo, aprile e fino al giorno corrente, 1° giugno.
Ecco come verrà aggiornata questa tabella il 2 giugno:
- Il 2 giugno, l'ultima riga della tabella sarà stata aggiunta il 1° giugno.
- Il generatore di file PDT troncherà il mese fino al 1° giugno e ricostruirà i dati per il mese di giugno, incluso il 2 giugno.
- Inoltre, a causa dell'offset dell'incremento, lo strumento per la creazione del report PDT ricostruirà i dati dei tre mesi precedenti a giugno. Di conseguenza, i dati vengono ricostruiti da marzo, aprile, maggio e fino al giorno corrente, 2 giugno.
Testare un PDT incrementale in modalità di sviluppo
Prima di eseguire il deployment di un nuovo PDT incrementale nell'ambiente di produzione, puoi testarlo per assicurarti che venga compilato e incrementato. Per testare una PDT incrementale in modalità di sviluppo:
Crea un'esplorazione per il PDT:
- In un file modello associato, utilizza il parametro
includeper includere il file di visualizzazione del PDT nel file modello. - Nello stesso file del modello, utilizza il parametro
exploreper creare un'esplorazione per la visualizzazione della PDT incrementale.
include: "/views/e_faa_pdt.view" explore: e_faa_pdt {}- In un file modello associato, utilizza il parametro
Apri l'esplorazione per il 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, lo strumento di creazione della PDT creerà l'intera PDT per recuperare i dati iniziali. Se la tabella è di grandi dimensioni, la compilazione iniziale potrebbe richiedere molto tempo, così come la compilazione di qualsiasi tabella di grandi dimensioni.
Puoi verificare che il PDT iniziale sia stato creato nei seguenti modi:
- Se disponi dell'autorizzazione
see_logs, puoi verificare che la tabella sia stata creata controllando il log eventi PDT. 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 è presente la dicitura "dalla cache", puoi selezionare Svuota cache e aggiorna per visualizzare informazioni più recenti. - In alternativa, puoi esaminare i commenti nella scheda SQL della barra Dati dell'esplorazione. La scheda SQL mostra la query e le azioni che verranno intraprese quando la esegui nell'esplorazione. Ad esempio, se i commenti nella scheda SQL indicano
-- generate derived table e_incremental_pdt
- Se disponi dell'autorizzazione
Dopo aver creato la compilazione iniziale della PDT, richiedi una compilazione incrementale della PDT utilizzando l'opzione Ricostruisci tabelle derivate ed esegui dall'esplorazione.
Puoi utilizzare gli stessi metodi di prima per verificare che il pacchetto di dati di previsione venga creato in modo incrementale:
- Se disponi dell'autorizzazione
see_logs, puoi utilizzare il log eventi PDT per visualizzare gli eventicreate increment completeper la PDT incrementale. Se non visualizzi questo evento nel log eventi PDT e lo stato della query è "da cache", seleziona Svuota cache e aggiorna per ottenere informazioni più recenti. - Esamina i commenti nella scheda SQL della barra Dati dell'esplorazione. 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 il PDT sia stato creato e sia in aumento correttamente, se non vuoi mantenere l'esplorazione dedicata per il PDT, puoi rimuovere o commentare i parametri
exploreeincludedel PDT dal file del modello.
Dopo aver creato il PDT in modalità di sviluppo, la stessa tabella verrà utilizzata per la produzione una volta implementate le modifiche, a meno che non apporti ulteriori modifiche alla definizione della tabella. Per ulteriori informazioni, consulta la sezione Tabelle persistenti in modalità di sviluppo della pagina della documentazione relativa alle tabelle derivate in Looker.
Dialetti del database supportati per le tabelle PDT incrementali
Affinché Looker supporti i PDT incrementali nel progetto, il dialetto del database deve supportare i comandi Data Definition Language (DDL) che consentono di eliminare e inserire righe.
La seguente tabella mostra quali dialetti supportano le PDT incrementali nell'ultima release di Looker (per Databricks, le PDT incrementali sono supportate solo su Databricks 12.1 e versioni successive):
| Dialetto | Supportato? |
|---|---|
| Actian Avalanche | No |
| Amazon Athena | No |
| Amazon Aurora MySQL | No |
| Amazon Redshift | Sì |
| Apache Druid | No |
| Apache Druid 0.13 e versioni successive | No |
| Apache Druid 0.18 o versioni successive | No |
| Apache Hive 2.3 e 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 e 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 |
| Fulmine | 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 |
| IBM Netezza | No |
| MariaDB | No |
| Microsoft Azure PostgreSQL | Sì |
| Database SQL di Microsoft Azure | No |
| Microsoft Azure Synapse Analytics | Sì |
| Microsoft SQL Server 2008 e versioni successive | No |
| Microsoft SQL Server 2012 e versioni successive | No |
| Microsoft SQL Server 2016 | No |
| Microsoft SQL Server 2017 e 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 precedente alla versione 9.5 | Sì |
| PrestoDB | No |
| PrestoSQL | No |
| SAP HANA 2 e versioni successive | No |
| SingleStore | No |
| SingleStore 7 e versioni successive | No |
| Snowflake | Sì |
| Teradata | No |
| Trino | No |
| Vettoriale | No |
| Vertica | Sì |