PDT incrementali

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:

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

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 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 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 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 a 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 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 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

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:

1. Crea un'esplorazione per il 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 visualizzazione della PDT incrementale.

    include: "/views/e_faa_pdt.view"
    explore: e_faa_pdt {}
   

2. Apri l'esplorazione per il 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. 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.

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

3. Dopo aver creato la compilazione iniziale della PDT, richiedi una compilazione incrementale della PDT utilizzando l'opzione Ricostruisci tabelle derivate ed esegui dall'esplorazione.

4. 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 eventi create increment complete per 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

5. 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 explore e include del 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ì