PDT incrementali

In Looker, le tabelle derivate persistenti (PDT) vengono scritte nello schema temporaneo del database. Looker persiste e ricostruisce una PDT basata sulla sua strategia di persistenza. Quando si attiva la ricostruzione di una PDT, per impostazione predefinita Looker ricostruisce l'intera tabella.

Se il tuo dialetto supporta la PDT incrementale, puoi scegliere di aggiungere dati aggiornati alla PDT anziché ricrearla interamente:

La prima volta che esegui una query su una PDT incrementale, Looker crea l'intera PDT per ottenere i dati iniziali. Se la tabella è grande, la build iniziale potrebbe richiedere molto tempo, così 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 è stata impostata in modo strategico.

Inoltre, assicurati che la tabella delle origini PDT incrementale sia ottimizzata per le query basate sul tempo. In particolare, la colonna basata sul tempo utilizzata per la chiave incrementale deve avere una strategia di ottimizzazione, ad esempio partizionamento, ordinati, 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 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.

Se il tuo dialetto supporta le PDT incrementali, puoi creare i seguenti tipi di PDT incrementali:

  • Tabelle aggregate
  • PDT basate su LookML (native)

  • PDT basate su SQL

    Per le PDT basate su SQL, la query nella 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 vedi in Esempio 1 in questa pagina, Looker utilizza un comando INSERT o un comando DNSSEC per creare gli incrementi per una PDT incrementale. La tabella derivata non può essere definita utilizzando le istruzioni DDL (Data Definition Language) personalizzate, poiché Looker non sarebbe in grado di determinare quali istruzioni DDL sarebbero necessarie per creare un incremento accurato.

Definizione di una PDT incrementale

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

  • increment_key (obbligatorio per rendere PDT incrementale): definisce il periodo di tempo in cui eseguire query sui nuovi record.
  • {% incrementcondition %} Filtro liquido (obbligatorio per applicare una PDT basata su SQL come PDT incrementale; non applicabile alle PDT basate su Look): connette la chiave di incremento alla colonna del tempo del database su cui si basa la chiave di incremento. 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 (alla granularità della chiave di incremento) ricreati per ogni build incrementale. Il parametro increment_offset è utile nel caso di dati in arrivo in ritardo, in cui i periodi di tempo precedenti potrebbero avere nuovi dati che non sono stati inclusi quando l'incremento corrispondente è stato originariamente creato e aggiunto al PDT.

Consulta la pagina della documentazione relativa al parametro increment_key per consultare esempi che mostrano come creare PDT incrementali da tabelle native persistenti, tabelle derivate basate su SQL 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
  }
}

La tabella verrà creata interamente alla prima esecuzione di una query. In seguito, la PDT verrà ricreata in incrementi di un giorno (increment_key: departure_date), 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 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 della vista 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 PDT incrementale determina solo quando gli incrementi della PDT. Il generatore 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 Rebuild Derived Tables & Run in un'esplorazione.
  • Quando aumenta le PDT, il generatore di PDT determina il momento in cui i dati più recenti sono stati precedentemente aggiunti alla tabella, in termini di incremento del tempo più recente (il periodo di tempo definito dal parametro increment_key). Sulla base di questi dati, il generatore PDT tronca i dati all'inizio dell'incremento di tempo più recente nella tabella, quindi sviluppa l'incremento più recente da lì.
  • Se la PDT ha un parametro increment_offset, lo strumento per la creazione di PDT ricostruisce anche il numero di periodi di tempo precedenti specificati nel parametro increment_offset. I periodi di tempo precedenti tornano a partire dall'inizio dell'incremento di tempo più recente (il periodo di tempo definito dal parametro increment_key).

Gli scenari di esempio seguenti 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 queste proprietà:

  • Chiave di incremento: data
  • Offset dell'incremento: 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 tronca il 1° maggio all'inizio della giornata e ricostruisce i dati dal 1° maggio al giorno corrente fino al 1° giugno.
  • Inoltre, questa PDT ha un offset incrementale di 3. Pertanto, il generatore PDT ricostruisce anche i dati dei tre periodi di tempo precedenti (giorni) prima del 1° maggio. Il risultato è che vengono ricostruiti i dati per il 28, 29, 30 aprile e fino al giorno corrente del 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 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)

Ed 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

In questo esempio viene utilizzata una PDT con queste proprietà:

  • Strategia di persistenza: attivata una volta al giorno
  • Chiave di incremento: mese
  • Offset dell'incremento: 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 sarà aggiunta il 31 maggio.
  • Poiché la chiave increment si basa sul mese, il generatore PDT tronca il 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 offset dell'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 sarà stata aggiunta il 1° giugno.
  • Poiché il generatore PDT tronca il flusso all'inizio del mese di giugno per poi ricostruire i dati a partire dal 1° e fino al giorno corrente, i dati vengono ricostruiti solo per il 1° e il 2 giugno.
  • Poiché questa PDT non ha offset dell'incremento, non vengono ricostruiti periodi di tempo precedenti.

Esempio 3

In questo esempio viene utilizzata una PDT con queste proprietà:

  • Chiave di incremento: mese
  • Offset dell'incremento: 3
  • Strategia di persistenza: attivata una volta al giorno

Questo scenario presenta una configurazione errata per una PDT incrementale, poiché si tratta di una PDT attiva giornaliera con offset di tre mesi. Ciò significa che vengono ricreati almeno tre mesi di dati ogni giorno, il che significherebbe un uso 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 significa che la tabella viene creata automaticamente una volta al giorno. Il 1° giugno, ad esempio, l'ultima riga della tabella verrà aggiunta il 31 maggio.
  • Poiché la chiave increment si basa sul mese, il generatore PDT tronca il 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, questa PDT ha un offset incrementale di 3. Ciò significa che il generatore PDT ricostruisce anche i dati dei tre periodi di tempo precedenti (mesi) prima di maggio. Il risultato è che 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 PDT tronca il mese al 1° giugno e ricostruisce i dati del mese di giugno, incluso il 2 giugno.
  • Inoltre, a causa dell'offset di incremento, il generatore PDT ricostruisce i dati a partire dai tre mesi precedenti a giugno. Il risultato è che i dati vengono ricostruiti da marzo, aprile, maggio e fino al giorno corrente, 2 giugno.

Test di una PDT incrementale in modalità sviluppo

Prima di eseguire il deployment di una nuova PDT incrementale nel tuo ambiente di produzione, puoi testare la PDT per assicurarti che venga creata e incrementata. Per testare una PDT incrementale in modalità sviluppo:

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

  1. Apri la pagina Esplora per le PDT.

    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 utilizzando il menu delle azioni sul file nella parte superiore del file LookML:

  2. 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 PDT creerà l'intera PDT per ottenere i dati iniziali. Se la tabella è grande, la build iniziale potrebbe richiedere molto tempo, così come la creazione di qualsiasi tabella di grandi dimensioni.

  3. Puoi verificare che la PDT iniziale sia stata creata nei seguenti modi:

    • Se hai l'autorizzazione see_logs, puoi verificare che la tabella sia stata creata esaminando il log eventi PDT. Se non vedi l'evento di creazione di PDT nel log eventi PDT, controlla le informazioni sullo stato nella parte superiore di Esplora log di PDT. Se viene visualizzato il messaggio "da cache", puoi selezionare Svuota cache e aggiorna per visualizzare informazioni più recenti.
    • In caso contrario, puoi esaminare i commenti nella scheda SQL della barra Dati di Explore. 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 indicano -- generate derived table e_incremental_pdt, questa è l'azione che verrà intrapresa quando fai clic su Esegui.

  4. Una volta creata la build iniziale della PDT, richiedi una build incrementale della PDT utilizzando l'opzione Rebuild Derived Tables & Run (Esplora risorse derivate e ricostruisci le tabelle derivate).

  5. 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 relativi alla PDT incrementale. Se non vedi questo evento nel log eventi PDT e lo stato della query è "da cache", seleziona Svuota cache e aggiorna per visualizzare informazioni più recenti.
    • Osserva i commenti nella scheda SQL della barra Dati di Explore. In questo caso, i commenti indicheranno che la PDT è stata incrementata. Ad esempio: -- increment persistent derived table e_incremental_pdt to generation 2

  6. Dopo aver verificato che la PDT è stata creata e incrementata correttamente, se non vuoi conservare l'esplorazione dedicata per la PDT, puoi rimuovere o aggiungere un commento ai parametri explore e include di PDT dal file del modello.

Una volta creata la PDT in modalità Sviluppo, la stessa tabella verrà utilizzata per la produzione dopo aver 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à sviluppo nella pagina della documentazione sulle tabelle derivate in Looker.

Dialetti di database supportati per le PDT incrementali

Affinché Looker supporti le PDT incrementali nel tuo progetto Looker, il tuo 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: