PDT incrementali

In Looker, le tabelle derivate permanenti (PDT) vengono scritte nello schema temporaneo del database. Looker persiste e ricostruisce un PDT in base alla sua strategia di persistenza . Quando viene attivata la rigenerazione di una PDT, per impostazione predefinita Looker rigenera l'intera tabella.

Una PDT incrementale è una PDT che Looker crea aggiungendo nuovi dati alla tabella anziché ricostruirla interamente:

Una tabella di grandi dimensioni con le tre righe inferiori evidenziate per mostrare un piccolo numero 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 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 sarebbe in grado di determinare quali istruzioni DDL sarebbero necessarie per creare un incremento accurato.
  • La tabella di origine del 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, chiavi di ordinamento, indici o qualsiasi strategia di ottimizzazione supportata per il tuo dialetto. L'ottimizzazione della tabella di origine è fortemente raccomandata perché ogni volta che la tabella incrementale viene aggiornata, Looker interroga la 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 un PDT incrementale

È possibile utilizzare i seguenti parametri per trasformare un PDT in un PDT incrementale:

  • increment_key (obbligatorio per rendere la PDT incrementale): definisce il periodo di tempo per il quale devono essere eseguite query sui nuovi record.
  • {% incrementcondition %}Filtro liquido (necessario per effettuare unPDT basato su SQL un PDT incrementale; non applicabile aPDT basati su LookML ): Collega la chiave di incremento alla colonna temporale 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 (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, 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 al PDT.

Vedi ilincrement_key pagina di documentazione dei parametri per esempi che mostrano come creare PDT incrementali databelle derivate native persistenti, tabelle derivate persistenti basate su SQL, Etabelle 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 completamente la prima volta che viene eseguita una query su di essa. Dopodiché, il PDT verrà ricreato 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 date periodo di tempo 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 il periodo di tempo sono entrambi definiti nella visualizzazione flights, che è explore_source per questo PDT. Ecco come viene definito il gruppo di dimensioni departure nel file di vista flights:

...
  dimension_group: departure {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.dep_time ;;
  }
...

Interazione tra i parametri di incremento e la strategia di persistenza

Un PDTincrement_key Eincrement_offset Le impostazioni sono indipendenti dal PDTstrategia di persistenza:

  • La strategia di persistenza del PDT incrementale determina solo quando il PDT viene incrementato. Il generatore 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 Ricostruisci tabelle derivate ed esegui in un'esplorazione.
  • Quando il PDT viene incrementato, il generatore PDT determinerà quando i dati più recenti sono stati aggiunti alla tabella, in base all'incremento temporale più recente (il periodo di tempo definito dal parametro increment_key). In base a ciò, il generatore PDT troncherà i dati all'inizio dell'intervallo di tempo più recente nella tabella, quindi costruirà l'ultimo intervallo a partire da lì.
  • Se la PDT ha un parametro increment_offset, il generatore di PDT ricostruirà 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ù recente (il periodo di tempo definito dal parametro increment_key).

I seguenti scenari di esempio illustrano come vengono aggiornati i 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
  • Incrementa 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 PDT ha una chiave di incremento basata sulla data, il generatore di PDT troncherà il 1° maggio riportandolo all'inizio della giornata e ricostruirà i dati dal 1° maggio fino al giorno corrente, il 1° giugno.
  • Inoltre, questo PDT ha un offset incrementale di 3. Pertanto, lo strumento di creazione PDT ricostruisce anche i dati dei tre periodi di tempo (giorni) precedenti il 1° maggio. Il risultato è che i dati vengono ricostruiti per il 28, 29 e 30 aprile e fino al 1° giugno.

In termini SQL, ecco il comando che il generatore 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 generatore 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 un PDT con le seguenti proprietà:

  • Strategia di persistenza: attivata una volta al giorno
  • Incrementa chiave: mese
  • Incremento 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à i dati dal 31 maggio all'inizio del mese e li ricostruirà 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 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 errata per una PDT incrementale, in quanto si tratta di una PDT con attivazione giornaliera e un offset di tre mesi. Questo significa che ogni giorno verranno ricostruiti almeno tre mesi di dati, il che rappresenterebbe un utilizzo molto inefficiente di un 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 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 PDT troncherà i dati dal 31 maggio all'inizio del mese e ricostruirà i dati per tutto il mese di maggio fino al giorno corrente, incluso il 1° giugno.
  • Inoltre, questo PDT ha un offset incrementale di 3. Ciò significa che il generatore PDT ricostruisce anche i dati dei tre periodi (mesi) precedenti a maggio. Di conseguenza, i dati vengono ricostruiti da febbraio, marzo, aprile fino alla data odierna, 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 riportandolo 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 PDT ricostruirà i dati dei tre mesi precedenti a giugno. Il risultato è che i dati vengono ricostruiti a partire da marzo, aprile, maggio, fino alla data odierna, il 2 giugno.

Test di un 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 un PDT incrementale in modalità di sviluppo:

  1. Crea un'esplorazione per il PDT:

    • In un file modello associato, utilizza il parametro include per includere il file di visualizzazione del PDT nel file modello.
    • Nello stesso file del modello, utilizzare ilexplore parametro per creare un'esplorazione per la vista del PDT incrementale.
     include: "/views/e_faa_pdt.view"
 explore: e_faa_pdt {}

  2. Apri Esplora per PDT. Per fare ciò, seleziona il pulsante Vedi azioni file e poi seleziona un nome Esplora.

  1. Nella finestra Esplora, seleziona alcune dimensioni o misure e fai clic su Esegui. Looker creerà quindi l'intera PDT. Se questa è la prima query eseguita sulla PDT incrementale, il builder 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 il PDT iniziale sia stato creato nei seguenti modi:

    • Se disponi dell'autorizzazione see_logs, puoi verificare che la tabella sia stata creata esaminando il log eventi PDT. Se non vedi gli eventi di creazione PDT nel log eventi PDT, controlla le informazioni sullo stato nella parte superiore dell'esplorazione 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 consultare 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. Una volta creata la build iniziale del PDT, avvia una build incrementale del PDT utilizzando l'opzione Ricostruisci tabelle derivate ed 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 PDT per visualizzare gli eventi create increment complete per la PDT incrementale. Se non visualizzi questo evento nel registro eventi di PDT e lo stato della query indica "dalla cache", seleziona Cancella cache e aggiorna per ottenere informazioni più recenti.
    • Esamina 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. Una volta verificato che la tabella derivata è creata e incrementata correttamente, se non vuoi conservare l'esplorazione dedicata per la tabella derivata, puoi rimuovere o commentare i parametri explore e include della tabella derivata dal file del modello.

Dopo la creazione della PDT in modalità di sviluppo, la stessa tabella verrà utilizzata per la produzione una volta eseguito il deployment delle modifiche, a meno che tu non apporti ulteriori modifiche alla definizione della tabella. Per ulteriori informazioni, consultare la sezione Tabelle persistenti in modalità di sviluppo della pagina di documentazione Tabelle derivate in Looker.

Risoluzione dei problemi relativi alle PDT incrementali

Questa sezione descrive alcuni problemi comuni che potresti riscontrare durante l'utilizzo di PDT incrementali, nonché le procedure per la risoluzione di tali problemi.

La compilazione incrementale di PDT non riesce 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 (ad esempio l'aggiunta, la rimozione o la modifica del tipo di dati di una colonna) potrebbero causare l'errore della PDT:

SQL Error in incremental PDT: Query execution failed

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

Se il tuo schema cambia e vuoi ricostruire il tuo PDT incrementale da zero, usa la chiamata APIstart_pdt_build e includono ilfull_force_incremental parametro.

Dialetti del database supportati per le PDT incrementali

Affinché Looker supporti le PDT incrementali nel tuo progetto Looker, il dialetto del database deve supportare i comandi del linguaggio 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 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
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+
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