PDT incrementali

In Looker, le tabelle derivate permanenti (PDT) vengono scritte nello schema temporaneo del tuo database. Looker persiste e ricrea una PDT in base alla sua strategia di persistenza. Per impostazione predefinita, Looker ricrea l'intera tabella quando viene attivata la creazione di una PDT.

Una PDT incrementale è una PDT che Looker crea aggiungendo dati aggiornati alla tabella invece di ricreare la tabella nella sua interezza:

Se il tuo dialetto supporta PDT incrementali, puoi trasformare i seguenti tipi di PDT in 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 molto tempo, proprio come la creazione 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_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 della tabella deve essere definita utilizzando il parametro sql per essere utilizzata come PDT incrementale. Le PDT basate su SQL definite con il parametro sql_create o con il parametro create_process non possono essere create in modo incrementale. Come puoi vedere nell'Esempio 1 di 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 sono 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, ad esempio partizionamento, chiavi di ordinamento, indici 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 una query nella 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

Per trasformare una PDT in una PDT incrementale, puoi utilizzare i seguenti parametri:

• increment_key (obbligatorio per rendere la PDT incrementale una PDT incrementale): definisce il periodo di tempo per il quale eseguire query sui nuovi record.
• {% incrementcondition %} Filtro liquido (necessario per rendere una PDT basata su SQL una PDT incrementale; non applicabile alle PDT basate su LookML): connette la chiave di incremento alla colonna della durata del database su cui si basa la chiave di incremento. Per ulteriori informazioni, consulta la pagina della documentazione di increment_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 creati di nuovo per ogni build incrementale. Il parametro increment_offset è utile in caso di dati in arrivo 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 trovare 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 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 alla prima esecuzione di una query. Dopodiché, la PDT verrà ricreata in incrementi di un giorno (increment_key: departure_date), per poi tornare indietro di tre giorni (increment_offset: 3).

La chiave di incremento si basa sulla dimensione departure_date, che in realtà corrisponde al periodo 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 corrisponde al valore 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 incrementale della PDT 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 l'opzione Ricrea le tabelle derivate ed esegui in un'esplorazione.
• Quando il numero di PDT aumenta, il builder di PDT determina quando i dati più recenti sono stati aggiunti in precedenza alla tabella, 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 e poi genererà l'ultimo incremento da lì.
• Se la PDT ha un parametro increment_offset, lo strumento per la creazione delle PDT ricrea anche il numero di periodi di tempo precedenti specificati nel parametro increment_offset. I periodi di tempo precedenti risalgono all'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

Questo esempio utilizza una PDT con le seguenti proprietà:

• Aumenta chiave: data
• Offset incremento: 3
• Strategia di persistenza: attivata una volta al mese, il primo giorno del mese.

Ecco come verrà aggiornata questa tabella:

• Con una strategia di persistenza mensile, la tabella viene creata automaticamente una volta al mese. Questo significa che il 1° giugno, ad esempio, l'ultima riga della tabella è stata aggiunta il 1° maggio.
• Poiché questa PDT ha una chiave di incremento basata sulla data, il generatore di PDT troncherà il 1° maggio per tornare all'inizio della giornata e creerà di nuovo i dati relativi al 1° maggio e al giorno corrente, ovvero il 1° giugno.
• Inoltre, questa PDT ha un offset di incremento pari a 3. Di conseguenza, il builder di PDT ricostruisce anche i dati dei tre periodi di tempo precedenti (giorni) precedenti al 1° maggio. Di conseguenza, i dati vengono ricreati per il 28, il 29 e il 30 aprile e fino al giorno attuale, il 1° giugno.

In termini di SQL, ecco il comando che il builder di PDT eseguirà 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)

Ed 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

Questo esempio utilizza una PDT con le seguenti proprietà:

• Strategia di persistenza: attivata una volta al giorno
• Incrementa chiave: mese
• Offset incremento: 0

Ecco come verrà aggiornata questa tabella il 1° giugno:

• Con la strategia di persistenza giornaliera, la tabella viene creata automaticamente una volta al giorno. Il 1° giugno, l'ultima riga della tabella è stata aggiunta il 31 maggio.
• Poiché la chiave di incremento si basa sul mese, il generatore di PDT verrà troncato dal 31 maggio all'inizio del mese e creerà di nuovo i dati relativi a tutto maggio e fino al giorno corrente, incluso il 1° giugno.
• Poiché questa PDT non ha un offset di incrementi, non vengono creati di nuovo periodi di tempo precedenti.

Ecco come verrà aggiornata questa tabella il 2 giugno:

• Il 2 giugno, l'ultima riga nella tabella è stata aggiunta il 1° giugno.
• Poiché il builder di PDT verrà troncato all'inizio del mese di giugno, quindi i dati verranno ricreati a partire dal 1° giugno e fino al giorno corrente, i dati vengono ricreati solo per il 1° e il 2 giugno.
• Poiché questa PDT non ha un offset di incrementi, non vengono creati di nuovo periodi di tempo precedenti.

Esempio 3

Questo esempio utilizza una PDT con le seguenti proprietà:

• Incrementa chiave: mese
• Offset incremento: 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 di attivazione giornaliera con un offset di tre mesi. Ciò significa che ogni giorno verranno ricostruiti almeno tre mesi di dati, il che comporterebbe un uso molto inefficiente di una PDT incrementale. Tuttavia, è uno scenario interessante da esaminare per comprendere come funzionano le PDT incrementali.

Ecco come verrà aggiornata questa tabella il 1° giugno:

• Con la strategia di persistenza giornaliera, la tabella viene creata automaticamente una volta al giorno. Il 1° giugno, ad esempio, l'ultima riga della tabella è stata aggiunta il 31 maggio.
• Poiché la chiave di incremento si basa sul mese, il generatore di PDT verrà troncato dal 31 maggio all'inizio del mese e creerà di nuovo i dati relativi a tutto maggio e fino al giorno corrente, incluso il 1° giugno.
• Inoltre, questa PDT ha un offset di incremento pari a 3. Ciò significa che il builder di PDT ricostruisce anche i dati dei tre periodi di tempo precedenti (mesi) precedenti a maggio. In questo modo, i dati vengono ricreati da febbraio, marzo, aprile e fino al giorno corrente, il 1° giugno.

Ecco come verrà aggiornata questa tabella il 2 giugno:

• Il 2 giugno, l'ultima riga della tabella è stata aggiunta il 1° giugno.
• Lo strumento per la creazione di PDT troncherà il mese al 1° giugno e creerà di nuovo i dati del mese di giugno, incluso il 2 giugno.
• Inoltre, a causa della compensazione dell'incremento, il builder di PDT ricostruisce i dati dei tre mesi precedenti prima di giugno. Di conseguenza, i dati vengono ricreati da marzo, aprile, maggio e fino al giorno corrente, 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 vista 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 l'esplorazione per la PDT. Per farlo, seleziona il pulsante Visualizza azioni file e poi il nome di un'esplorazione.

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

1. In Esplora, seleziona alcune dimensioni o misure e fai clic su Esegui. Looker creerà l'intera PDT. Se questa è la prima query che esegui 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 molto tempo, proprio come la creazione di 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 esaminando il log eventi PDT. Se non visualizzi 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 ricevere informazioni più recenti.
   • In alternativa, puoi guardare 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, 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 l'opzione Ricrea le tabelle derivate ed esegui dall'esplorazione.

4. Puoi utilizzare gli stessi metodi di prima per verificare che la PDT crei in modo incrementale:

   • Se hai l'autorizzazione see_logs, puoi utilizzare il log eventi PDT 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 ricevere informazioni più recenti.
   • Esamina i commenti nella scheda SQL della barra Dati dell'esplorazione. 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 aumenterà correttamente, se non vuoi conservare l'esplorazione dedicata per la PDT, puoi rimuovere o commentare i parametri explore e include delle 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 persistenti in modalità di sviluppo della pagina della documentazione Tabelle derivate in Looker.

Dialetti di database supportati per PDT incrementali

Affinché Looker supporti PDT incrementali nel 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 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 atiana	No
Amazon Athena	No
Amazon Aurora MySQL	No
Amazon Redshift	Sì
Apache drud	No
Apache Druid 0.13 e versioni successive	No
Apache Druid 0.18 e 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+ 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
Verde prugna	Sì
HyperSQL	No
IBM Netezza	No
MariaDB	No
Microsoft Azure PostgreSQL	Sì
Database SQL di Microsoft Azure	No
Analisi di Microsoft Azure Synapse	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 o versioni successive	Sì
Oracle	No
ADWC Oracle	No
PostgreSQL 9.5 e versioni successive	Sì
PostgreSQL pre-9.5	Sì
PrestoDB	No
PrestoSQL	No
SAP HANA	No
SAP HANA 2 o versioni successive	No
SingleStore	No
SingleStore 7 o versioni successive	No
Snowflake	Sì
Teradata	No
Trino	No
Vettoriale	No
Vertica	Sì