In Looker, le tabelle derivate permanenti (PDT) vengono scritte nello schema temporaneo del 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é ricostruirla 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_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. Le PDT basate su SQL definite con il parametrosql_createo il parametrocreate_processnon 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 un 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 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. Ti consigliamo vivamente di ottimizzare la tabella di origine perché ogni volta che la tabella incrementale viene aggiornata, Looker esegue una 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.
Definire un 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 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.- (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 nella sua interezza la prima volta che viene eseguita una query. In seguito, 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 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 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 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 builder di PDT ricrea anche il numero di periodi di tempo precedenti specificato nel parametroincrement_offset. I periodi di tempo precedenti tornano indietro a partire dall'inizio dell'incremento di tempo più attuale (il periodo di tempo definito dal parametroincrement_key).
I seguenti scenari di esempio illustrano come vengono aggiornate le PDT incrementali, mostrando l'interazione di increment_key, increment_offset e la strategia di persistenza.
Esempio 1
Questo esempio utilizza un PDT con le seguenti proprietà:
- Incrementa chiave: data
- Increment 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. 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)
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
- Incrementa chiave: mese
- Incrementa offset: 0
Ecco come verrà aggiornata questa tabella il 1° giugno:
- La strategia di persistenza giornaliera indica che la tabella viene 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 verrà aggiunta il 1° giugno.
- Poiché il builder di PDT verrà troncato all'inizio del mese di giugno e poi ricreato i dati a partire dal 1° giugno e fino al giorno corrente, i dati vengono ricreati solo per il 1° giugno 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 ricreati almeno tre mesi di dati, il che comporterebbe un uso molto inefficiente di una 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 indica 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 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 di Play Console 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.
Test di una 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 del modello associato, utilizza il parametro
includeper includere il file di visualizzazione della PDT nel file del 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 del modello associato, utilizza il parametro
Apri l'esplorazione per il PDT. Per farlo, seleziona il pulsante Visualizza le azioni sul file, quindi seleziona il nome di un'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 build iniziale potrebbe richiedere una notevole quantità di tempo, così come accade per 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 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 dei log eventi PDT. Se è indicato "Dalla cache", puoi selezionare Svuota cache e Aggiorna per ottenere 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 PDC 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. - 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 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.
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 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? |
|---|---|
| Valanga Actia | No |
| Amazon Athena | No |
| Amazon Aurora MySQL | No |
| Amazon Redshift | Sì |
| Apache Druid | No |
| Apache Druid 0.13 o versioni successive | No |
| Apache Druid 0.18 o versioni successive | No |
| Apache Hive 2.3 e versioni successive | No |
| Apache Hive 3.1.2 o versioni successive | No |
| Apache Spark 3 e versioni successive | No |
| ClickHouse | No |
| Cloudera Impala 3.1 o 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 |
| Firebolt | No |
| SQL precedente di Google BigQuery | No |
| SQL standard di Google BigQuery | Sì |
| Google Cloud PostgreSQL | 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 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ì |