PDT incrementali

In Looker, le tabelle derivate permanenti (PDT) vengono scritte nello schema temporaneo del database. Looker rende persistente 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 che Looker crea aggiungendo nuovi dati alla tabella anziché ricrearla interamente:

Una tabella di grandi dimensioni con le tre righe inferiori evidenziate per mostrare un numero ridotto di nuove righe aggiunte alla tabella.

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 ottenere i dati iniziali. Se la tabella è di grandi dimensioni, la creazione iniziale potrebbe richiedere molto tempo, come 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.

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 da utilizzare 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 di 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 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 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.
  • Le funzioni finestra SQL non sono supportate direttamente nelle PDT incrementali. Per utilizzare una funzione finestra con un PDT incrementale, crea il PDT incrementale come tabella di base o intermedia senza la funzione finestra, quindi fai riferimento al PDT incrementale in una tabella derivata o un PDT separato che applica la funzione finestra.

Definizione di una PDT incrementale

Puoi utilizzare i seguenti parametri per trasformare una PDT in una PDT incrementale:

  • increment_key (obbligatorio per rendere la PDT una PDT incrementale): definisce il periodo di tempo per cui eseguire 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 dell'ora del database su cui si basa la chiave di incremento. Per saperne di più, consulta la pagina della documentazione relativa a 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 ricostruiti per ogni build incrementale. Il parametro increment_offset è utile nel caso di dati in ritardo, in cui i periodi di tempo precedenti potrebbero contenere nuovi dati che non erano stati inclusi quando l'incremento corrispondente è stato originariamente 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 permanenti native, tabelle derivate permanenti basate su SQL e tabelle aggregate.

Ecco un esempio semplice di un file di visualizzazione 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. Dopodiché, la PDT verrà ricreata con incrementi di un giorno (increment_key: departure_date), tornando indietro di tre giorni (increment_offset: 3).

La chiave di incremento si basa sulla dimensione departure_date, che in realtà è il periodo 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 vista 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 Rebuild Derived Tables & Run (Ricostruisci tabelle derivate ed esegui) in un'esplorazione.
  • Quando la PDT viene incrementata, il generatore di PDT determina quando sono stati aggiunti gli ultimi dati alla tabella, in termini di incremento temporale più recente (il periodo di tempo definito dal parametro increment_key). In base a ciò, il generatore di PDT troncherà i dati all'inizio dell'incremento temporale più recente nella tabella, quindi creerà l'incremento più recente da lì.
  • Se la PDT ha un parametro increment_offset, il builder PDT ricostruirà anche il numero di periodi di tempo precedenti specificati nel parametro increment_offset. I periodi di tempo precedenti vengono calcolati a ritroso a partire dall'inizio dell'incremento di tempo più recente (il periodo di tempo definito dal parametro increment_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 una PDT con queste proprietà:

  • Chiave di incremento: 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, ad esempio, il 1° giugno l'ultima riga della tabella sarà stata aggiunta il 1° maggio.
  • Poiché questa PDT ha una chiave di incremento basata sulla data, il generatore di PDT troncherà il 1° maggio all'inizio della giornata e ricostruirà i dati per il 1° maggio e fino al giorno corrente, il 1° giugno.
  • Inoltre, questa PDT ha un offset di incremento pari a 3. Pertanto, il generatore di PDT rigenera anche i dati dei tre periodi di tempo (giorni) precedenti al 1° maggio. Di conseguenza, i dati vengono ricostruiti per il 28, 29 e 30 aprile e fino al giorno corrente, il 1° giugno.

In termini SQL, ecco il comando che il generatore 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)

Ecco il comando SQL che il generatore 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 queste proprietà:

  • Strategia di persistenza: attivata una volta al giorno
  • Incrementa chiave: mese
  • Increment offset (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 di PDT troncherà dal 31 maggio all'inizio del mese e ricostruirà 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 ricreati 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 PDT troncherà i dati fino all'inizio del mese di giugno, per poi ricostruirli 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 ricreati periodi di tempo precedenti.

Esempio 3

Questo esempio utilizza una PDT con queste proprietà:

  • Incrementa chiave: mese
  • Increment offset: 3
  • Strategia di persistenza: attivata una volta al giorno

Questo scenario illustra una configurazione non ottimale per una PDT incrementale, poiché si tratta di una PDT con attivazione giornaliera e un offset di tre mesi. Ciò significa che ogni giorno verranno ricostruiti almeno tre mesi di dati, il che sarebbe un utilizzo molto inefficiente di una PDT incrementale. Tuttavia, è uno scenario interessante da esaminare per capire come funzionano le 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. 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 di PDT troncherà dal 31 maggio all'inizio del mese e ricostruirà i dati per tutto il mese di 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 PDT 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, il 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 PDT troncherà il mese al 1° giugno e ricostruirà i dati per il mese di giugno, incluso il 2 giugno.
  • Inoltre, a causa dell'offset di incremento, il generatore di PDT ricreerà i dati dei tre mesi precedenti a giugno. Di conseguenza, i dati vengono ricostruiti 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 della vista 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 la PDT. Per farlo, seleziona il pulsante Visualizza azioni file, quindi seleziona un nome di esplorazione.

  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 esegui sulla PDT incrementale, il generatore di PDT creerà l'intera PDT per ottenere i dati iniziali. Se la tabella è di grandi dimensioni, la creazione iniziale potrebbe richiedere molto tempo, come 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 controllando il log eventi PDT. Se non vedi gli eventi di creazione di PDT nel log eventi PDT, controlla le informazioni sullo stato nella parte superiore della pagina Esplora del log eventi PDT. Se viene visualizzato il messaggio "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 di Esplora. La scheda SQL mostra la query e le azioni che verranno eseguite quando esegui la query in Esplora. Ad esempio, se i commenti nella scheda SQL dicono -- 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 Rebuild Derived Tables & Run (Ricostruisci tabelle derivate ed esegui) dall'esplorazione.

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

Dopo aver creato la PDT in modalità di sviluppo, la stessa tabella verrà utilizzata per la produzione una volta eseguito il deployment delle 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 Tabelle derivate in Looker.

Risoluzione dei problemi relativi alle PDT incrementali

Questa sezione descrive alcuni problemi comuni che potresti riscontrare durante l'utilizzo delle PDT incrementali, nonché i passaggi per risolvere i problemi e risolverli.

La creazione della PDT incrementale non va a buon fine dopo la modifica dello schema

Se la PDT incrementale è una tabella derivata basata su SQL e il parametro sql include un carattere jolly come SELECT *, le modifiche allo schema del database sottostante (come l'aggiunta di colonne, la rimozione di colonne o la modifica del tipo di dati delle colonne) potrebbero causare l'errore della PDT con il seguente messaggio:

SQL Error in incremental PDT: Query execution failed

Per risolvere il problema, modifica l'istruzione SELECT nel parametro sql in modo da selezionare singole colonne. Ad esempio, se la clausola select è SELECT *, modificala in SELECT column1, column2, ....

Se lo schema cambia e vuoi ricreare la PDT incrementale da zero, utilizza la chiamata API start_pdt_build e includi il parametro full_force_incremental.

Le funzioni finestra restituiscono risultati imprevisti in una PDT incrementale

L'utilizzo diretto delle funzioni finestra SQL in un PDT incrementale non è supportato. Poiché le build incrementali elaborano solo le righe per l'ultimo incremento di tempo e qualsiasi increment_offset configurato, la definizione di una funzione finestra direttamente in un PDT incrementale (ad esempio in un parametro derived_column o sql) può comportare valori mancanti o calcoli imprevisti tra i confini della partizione quando la tabella viene incrementata.

Per utilizzare una funzione finestra con una PDT incrementale, definisci la PDT incrementale come PDT di base o intermedia senza la funzione finestra, quindi fai riferimento alla PDT incrementale in una tabella derivata separata o in una PDT normale che applica la funzione finestra.

Dialetti di database supportati per le PDT incrementali

Affinché Looker supporti le PDT incrementali nel tuo progetto Looker, il dialetto del database deve supportare i comandi Data Definition Language (DDL) che consentono di eliminare e inserire righe.

La tabella seguente mostra i dialetti che supportano le PDT incrementali nell'ultima release di Looker (per Databricks, le PDT incrementali sono supportate solo su Databricks versione 12.1 e successive):

Dialetto Supportata?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica