PDT incrementali

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:

• Tabelle aggregate
• PDT basate su LookML (native)
• PDT basate su SQL

La prima volta che esegui una query su una PDT incrementale, Looker crea 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. 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 o interval_trigger). Le PDT incrementali non sono supportate per le PDT che utilizzano la strategia di persistenza persist_for.
• Per le PDT basate su SQL, la query tabella deve essere definita utilizzando il parametro sql affinché venga utilizzata come PDT incrementale. Le PDT basate su SQL definite con il parametro sql_create o il parametro create_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 di 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. In particolare, la colonna basata sul tempo utilizzata per la chiave di incremento deve avere una strategia di ottimizzazione, come partizionamento, sortkey, indici o qualsiasi altra 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 gli ultimi valori 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 saperne di più, consulta la pagina della documentazione increment_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 parametro increment_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. In seguito, la PDT verrà ricreata per 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 il periodo di tempo sono entrambi definiti nella vista 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 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 parametro increment_offset. I periodi di tempo precedenti tornano indietro a partire dall'inizio dell'incremento di tempo più attuale (il periodo di tempo definito dal parametro increment_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

In questo esempio viene utilizzata una PDT con le seguenti proprietà:

• Chiave di aumento: 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é questa PDT ha una chiave di incremento in base alla data, il generatore di PDT troncherà il 1° maggio all'inizio della giornata e ricalcolerà i dati relativi al 1° maggio e fino al giorno corrente, ovvero il 1° giugno.
• Inoltre, questa PDT ha un offset incrementale di 3. Pertanto, il builder di PDT ricostruisce anche i dati dei tre periodi di tempo precedenti (giorni) prima del 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
• Increment 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 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.
• 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 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 viene ricreato alcun periodo di tempo precedente.

Esempio 3

In questo esempio viene utilizzata una 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.

Test di una 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:

1. 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 {}
   

2. Apri Esplora per la PDT. Per farlo, seleziona il pulsante Visualizza le azioni sul file, quindi seleziona il nome di un'esplorazione.

Suggerimento: dopo aver incluso la vista nel file del modello e aver creato un'esplorazione, puoi passare direttamente all'esplorazione dal file della vista o dal file del modello nel progetto LookML. Per farlo, seleziona il menu Visualizza le azioni sul file ⏷ nella parte superiore del file LookML.

1. In Esplora, 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.

2. 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 dei 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 -- generate derived table e_incremental_pdt,questa è l'azione che verrà eseguita quando fai clic su Esegui.

3. Dopo aver creato la build iniziale della PDT, richiedi una build incrementale della PDT utilizzando Ricrea le tabelle derivate e Esegui da Esplora.

4. 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 PDC per visualizzare gli eventi create 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 la PDT è stata incrementata. Ad esempio: -- increment persistent derived table e_incremental_pdt to generation 2

5. 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 e include 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	Supportata?
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 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 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ì
Google Cloud PostgreSQL	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 o 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+	No
Snowflake	Sì
Teradata	No
Trino	No
Vettoriale	No
Vertica	Sì