Creare tabelle

In Dataform, una tabella è uno dei tipi di oggetti che compongono un workflow. Puoi creare tabelle che fanno riferimento ai dati delle origini dati dichiarate per il tuo workflow o di altre tabelle nel workflow. Dataform compila le definizioni delle tabelle in SQL in tempo reale. Quando attivi l'esecuzione, Dataform esegue il codice SQL e crea le tabelle definite in BigQuery.

Puoi creare i seguenti tipi di tabelle in un file SQLX type: "table":

Puoi anche definire partizioni e cluster della tabella.

Per tenere traccia dello scopo di una tabella o della sua relazione con altre tabelle nel tuo flusso di lavoro, puoi aggiungere la documentazione alla tabella o alle colonne selezionate.

Per testare i dati di una tabella in base a condizioni specifiche, puoi creare query di test della qualità dei dati chiamate asserzioni. Dataform esegue le asserzioni ogni volta che aggiorna il workflow e ti avvisa se un'asserzione non va a buon fine.

Per eseguire l'override dello schema, del database e del nome predefiniti di una tabella selezionata, puoi eseguire l'override delle impostazioni della tabella.

Per disabilitare la creazione di tabelle o eseguire un'istruzione SQL prima o dopo la creazione di tabelle, puoi configurare azioni aggiuntive.

Per organizzare le tabelle in BigQuery dopo averle eseguite, puoi aggiungere etichette BigQuery. Per saperne di più, vedi Introduzione alle etichette.

Per limitare l'accesso ai dati a livello di colonna della tabella, puoi aggiungere tag di policy BigQuery. Per saperne di più, consulta Introduzione al controllo dell'accesso a livello di colonna.

Per conservare le policy dei dati e i tag di governance dei dati (anteprima) di BigQuery applicati da sistemi esterni alle esecuzioni delle tabelle, puoi conservare i metadati dei controlli di accesso.

Oltre a definire le tabelle in un file SQLX type: "table", puoi creare tabelle vuote definendo una query SQL personalizzata in un file SQLX type: "operations". Potresti voler creare una tabella vuota in modo che un altro servizio possa compilarla con i dati.

Prima di iniziare

  1. Nella console Google Cloud , vai alla pagina Dataform.

    Vai a Dataform

  2. Crea e inizializza un'area di lavoro di sviluppo nel tuo repository.

  3. (Facoltativo) Dichiara un'origine dati.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per completare le attività descritte in questo documento, chiedi all'amministratore di concederti i seguenti ruoli IAM:

  • Editor Dataform (roles/dataform.editor) sul workspace
  • Per sincronizzare i metadati con Knowledge Catalog: Dataplex Catalog Editor (roles/dataplex.catalogEditor) sul progetto o sul gruppo di voci @bigquery

Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Potresti anche riuscire a ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.

Per conservare i metadati dei controlli di accesso, l'account di servizio che esegue i workflow Dataform deve disporre di autorizzazioni IAM specifiche a seconda dell'attività:

  • Estrai metadati: per leggere la visualizzazione INFORMATION_SCHEMA.COLUMNS, il account di servizio deve disporre delle autorizzazioni in lettura per lo schema del set di dati o della tabella. Queste autorizzazioni sono incluse nei seguenti ruoli IAM:

  • Applica i tag di governance dei dati: per collegare e gestire i tag di governance dei dati su tabelle e viste, l'account di servizio deve disporre delle autorizzazioni bigquery.tables.createTagBinding e bigquery.tables.deleteTagBinding. Queste autorizzazioni sono incluse nei seguenti ruoli IAM predefiniti:

  • Applica policy dei dati: per creare e aggiornare le policy dei dati (incluse le policy senza tag), il account di servizio deve disporre delle autorizzazioni bigquery.dataPolicies.create e bigquery.dataPolicies.update. Queste autorizzazioni sono incluse nei seguenti ruoli IAM predefiniti:

Creare una tabella

Questa sezione mostra come creare tabelle con Dataform Core in Dataform.

Informazioni sulle definizioni delle tabelle

Per definire una tabella, devi definire il tipo di tabella e scrivere un'istruzione SELECT in un file SQLX type: "table". Dataform compila il codice Dataform Core in SQL, esegue il codice SQL e crea le tabelle definite in BigQuery.

In un'istruzione SELECT di Dataform Core, definisci la struttura della tabella e fai riferimento ad altri oggetti del flusso di lavoro.

Oltre a definire le tabelle in un file SQLX type: "table", puoi creare tabelle vuote definendo una query SQL personalizzata in un file SQLX type: "operations". Per saperne di più, vedi Creare una tabella vuota.

Dipendenze di riferimento con ref

Per fare riferimento a un'azione del workflow in un'istruzione SELECT e aggiungerla automaticamente come dipendenza, utilizza la funzione ref. Dataform esegue le dipendenze prima delle tabelle che dipendono da loro per verificare l'ordine corretto della pipeline.

La funzione ref è una funzione integrata di Dataform Core fondamentale per la gestione delle dipendenze in Dataform. La funzione ref consente di fare riferimento e dipendere automaticamente dai seguenti oggetti definiti nel workflow di Dataform, anziché impostare come hardcoded i nomi di schemi e tabelle:

Dataform utilizza la funzione ref per creare un albero delle dipendenze di tutte le tabelle da creare o aggiornare.

Dopo la compilazione, Dataform aggiunge istruzioni boilerplate all'istruzione SQL, ad esempio CREATE, REPLACE, INSERT o MERGE.

Il seguente esempio di codice mostra una definizione di tabella con l'utilizzo della funzione ref:

config { type: "table" }

SELECT
  order_date AS date,
  order_id AS order_id,
  order_status AS order_status,
  SUM(item_count) AS item_count,
  SUM(amount) AS revenue

FROM ${ref("store_clean")}

GROUP BY 1, 2

Nella funzione ref, fornisci il nome della tabella o della dichiarazione dell'origine dati da cui vuoi dipendere. In genere, si tratta del nome file del file SQLX in cui è definita la dichiarazione della tabella o dell'origine dati.

Se il nome di una tabella viene sostituito, utilizza il nome sostituito nella funzione ref. Ad esempio, fai riferimento a una tabella con config { name: "overridden_name" } come ref("overridden_name"). Per ulteriori informazioni, vedi Ignorare le impostazioni della tabella e Fare riferimento a una tabella con un nome ignorato.

Se hai più tabelle con lo stesso nome in schemi diversi, puoi fare riferimento a una tabella specifica fornendo due argomenti alla funzione ref: nome dello schema e nome della tabella.

Il seguente esempio di codice mostra la funzione ref con due argomenti per specificare una tabella all'interno di uno schema specifico:

config { type: "table" }
SELECT * FROM ${ref("schema", "store_clean")}

Puoi anche aggiungere manualmente le dipendenze della tabella al blocco config per tabelle, asserzioni, dichiarazioni di origini dati o operazioni SQL personalizzate a cui non viene fatto riferimento in una funzione ref nell'istruzione SELECT. Dataform esegue queste dipendenze prima delle tabelle dipendenti.

Il seguente esempio di codice mostra una dipendenza della tabella nel blocco config:

config { dependencies: [ "unreferenced_table" ] }
SELECT * FROM ...

Per saperne di più sulla gestione delle dipendenze nel tuo workflow, consulta Imposta le dipendenze.

Fai riferimento ad altre tabelle con resolve

La funzione resolve consente di fare riferimento a una dichiarazione di tabella o origine dati in un'istruzione SELECT come la funzione ref, ma non aggiunge il riferimento come dipendenza. Ciò significa che l'oggetto a cui viene fatto riferimento utilizzando la funzione resolve non influisce sull'esecuzione della tabella che utilizza la funzione resolve.

Per saperne di più sulle funzioni Dataform core integrate, consulta la documentazione di riferimento di Dataform core.

Crea un file SQLX per definire una tabella

Archivia i file SQLX di definizione della tabella nella directory definitions/. Per creare un nuovo file SQLX nella directory definitions/, segui questi passaggi:

  1. Nella console Google Cloud , vai alla pagina Dataform.

    Vai a Dataform

  2. Per aprire un repository, fai clic sul relativo nome.

  3. Per aprire un'area di lavoro di sviluppo, fai clic sul nome dell'area di lavoro.

  4. Nel riquadro File, accanto a definitions/, fai clic su Altro.

  5. Fai clic su Crea file.

  6. Nel campo Aggiungi un percorso del file, inserisci il nome del file seguito da .sqlx dopo definitions/. Ad esempio: definitions/my-table.sqlx.

    I nomi file possono includere solo numeri, lettere, trattini e trattini bassi.

  7. Fai clic su Crea file.

Definisci il tipo di tabella

Per creare una nuova definizione del tipo di tabella:

  1. Nell'area di lavoro di sviluppo, nel riquadro File, espandi la directory definitions/.
  2. Seleziona il file SQLX di definizione della tabella che vuoi modificare.

  3. Nel file, inserisci il seguente snippet di codice:

    config { type: "TABLE_TYPE" }

    Sostituisci TABLE_TYPE con uno dei seguenti tipi di tabella:

    • table
    • incremental
    • view

  4. (Facoltativo) Per definire una vista materializzata, inserisci la proprietà materialized in type: "view" nel seguente formato:

    config {
  type: "view",
  materialized: true
}

    Per saperne di più, consulta ITableConfig.

  5. (Facoltativo) Fai clic su Formato.

Definisci la struttura e le dipendenze della tabella

Per scrivere un'istruzione SELECT di definizione della tabella e definire la struttura e le dipendenze della tabella, segui questi passaggi:

  1. Nell'area di lavoro di sviluppo, nel riquadro File, espandi la directory definitions/.
  2. Seleziona il file SQLX di definizione della tabella che vuoi modificare.
  3. Sotto il blocco config, scrivi un'affermazione SELECT.
  4. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una definizione di tabella con un'istruzione SELECT e la funzione ref:

config { type: "table" }
SELECT
  customers.id AS id,
  customers.first_name AS first_name,
  customers.last_name AS last_name,
  customers.email AS email,
  customers.country AS country,
  COUNT(orders.id) AS order_count,
  SUM(orders.amount) AS total_spent
FROM
  dataform-samples.dataform_sample.crm_customers AS customers
  LEFT JOIN ${ref('order_stats')} orders
    ON customers.id = orders.customer_id

WHERE
  customers.id IS NOT NULL
  AND customers.first_name <> 'Internal account'
  AND country IN ('UK', 'US', 'FR', 'ES', 'NG', 'JP')

GROUP BY 1, 2, 3, 4, 5

Aggiungere dipendenze della tabella manuali

Per aggiungere dipendenze della tabella che non sono referenziate nell'istruzione SELECT, ma che devono essere eseguite prima della tabella corrente, segui questi passaggi:

  1. Nell'area di lavoro di sviluppo, nel riquadro File, espandi la directory definitions/.
  2. Seleziona il file SQLX di definizione della tabella che vuoi modificare.

  3. Nel blocco config della tabella, inserisci il seguente snippet di codice:

    dependencies: [ "DEPENDENCY_TABLE", ]

    Sostituisci DEPENDENCY_TABLE con il nome file della tabella che vuoi aggiungere come dipendenza. Puoi inserire più nomi di file.

  4. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra due tabelle aggiunte come dipendenze manuali delle tabelle al blocco config di un file di definizione della tabella:

config { dependencies: [ "some_table", "some_other_table" ] }

Esegui l'override delle impostazioni della tabella

Puoi eseguire l'override dello schema, del database e del nome predefiniti di una tabella selezionata.

Per impostazione predefinita, una tabella segue la configurazione dello schema e del database impostata in workflow_settings.yaml. Il nome di una tabella corrisponde al nome del file SQLX di definizione della tabella.

Per eseguire l'override dello schema e del nome di una tabella selezionata:

  1. Vai al tuo workspace di sviluppo.

  2. Nel riquadro File, espandi definitions/.

  3. Apri un file di definizione della tabella SQLX.

  4. Nel blocco config, inserisci il seguente snippet di codice:

     {
   schema: "OVERRIDDEN_SCHEMA",
   database: "OVERRIDDEN_DATABASE",
   name: "OVERRIDDEN_NAME"
 }

    Sostituisci quanto segue:

    • OVERRIDDEN_SCHEMA: il set di dati BigQuery in cui vuoi creare la tabella.

    • OVERRIDDEN_DATABASE: l'ID del progetto BigQuery in cui vuoi creare la tabella.

    • OVERRIDDEN_NAME: il nome della tabella, che è diverso dal nome file della definizione della tabella SQLX.

  5. (Facoltativo) Fai clic su Formato.

Per ulteriori informazioni, vedi Fare riferimento a una tabella con un nome tabella sottoposto a override.

Crea tabelle gestite Apache Iceberg

Utilizza Dataform per creare tabelle BigQuery nel formato di tabella Iceberg. Queste tabelle sono note come tabelle gestite da Iceberg. Per saperne di più sui ruoli richiesti e su altre attività di configurazione, consulta i passaggi descritti nella sezione Prima di iniziare per i flussi di lavoro delle tabelle gestite di Iceberg.

Crea la definizione della tabella Iceberg

Per creare le tabelle, definisci la configurazione Iceberg nel blocco bigquery di un file di definizione della tabella.

Per definire le tabelle, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Apri un file SQLX di definizione della tabella.

  4. Nel blocco config, aggiungi un blocco bigquery che contiene un blocco iceberg nel seguente formato:

    config {
  type: "table",
  name: "my_table",
  uniqueKey: ["uniquekey"],
  bigquery: {
    iceberg: {
      bucket_name: "BUCKET_NAME"
    }
  }
}

    Sostituisci BUCKET_NAME con il nome del bucket Cloud Storage in cui sono archiviati i dati della tabella.

    Altre proprietà sono facoltative e hanno valori predefiniti se non specificati. Per maggiori informazioni, consulta le proprietà iceberg.

    Puoi fornire i valori per ogni tabella o impostare i valori predefiniti a livello di workflow in workflow_settings.yaml. Puoi eseguire l'override dei valori predefiniti a livello di workflow impostando esplicitamente le proprietà nel blocco iceberg.

Crea partizioni e cluster di tabelle

Questa sezione mostra come utilizzare Dataform core per creare partizioni e cluster di tabelle. BigQuery supporta le tabelle partizionate e il clustering delle tabelle. Per saperne di più, consulta Introduzione alle tabelle partizionate e Creazione e utilizzo di tabelle in cluster.

Crea una partizione di tabella

Per creare una partizione di tabella:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Apri un file SQLX di definizione della tabella.

  4. Nel blocco config, aggiungi il blocco bigquery sotto la dichiarazione del tipo di tabella nel seguente formato:

    config {
  type: "table",
  bigquery: {
  }
}

  5. Nel blocco bigquery, inserisci il seguente snippet di codice:

        partitionBy: "PARTITION_EXPRESSION"

    Sostituisci PARTITION_EXPRESSION con un'espressione per il partizionamento della tabella.

  6. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra il partizionamento di una tabella per ora in un file SQLX di definizione della tabella:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATETIME_TRUNC(<timestamp_column>, HOUR)"
  }
}

Il seguente esempio di codice mostra il partizionamento di una tabella in base a un valore intero in un file SQLX di definizione della tabella:

config {
  type: "table",
  bigquery: {
    partitionBy: "RANGE_BUCKET(<integer_column>, GENERATE_ARRAY(0, 1000000, 1000))"
  }
}

Impostare un filtro di partizionamento

Per impostare un filtro di partizione, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Apri un file SQLX di definizione di una tabella partizionata.

  4. Nel blocco bigquery, inserisci il seguente snippet di codice:

    requirePartitionFilter : true

  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra un filtro di partizione impostato nel blocco bigquery di un file SQLX di una tabella partizionata:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    requirePartitionFilter : true
  }
}
SELECT CURRENT_TIMESTAMP() AS ts

Per saperne di più sul filtro di partizione in BigQuery, consulta Impostazione dell'attributo del filtro di partizione obbligatorio su una tabella partizionata.

Impostare un periodo di conservazione per le partizioni

Per controllare la conservazione di tutte le partizioni in una tabella partizionata, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Apri un file SQLX di definizione di una tabella partizionata.

  4. Nel blocco bigquery, inserisci il seguente snippet di codice:

    partitionExpirationDays: NUMBER_OF_DAYS

    Sostituisci NUMBER_OF_DAYS con il numero di giorni per cui vuoi conservare le partizioni.

  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra un periodo di conservazione per le partizioni impostato su 14 giorni nel blocco bigquery di un file SQLX di una tabella partizionata:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    partitionExpirationDays: 14,
  }
}
SELECT CURRENT_TIMESTAMP() AS ts

Crea un cluster di tabelle

Per creare un cluster di tabelle:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Apri un file SQLX di definizione della tabella.

  4. Nel blocco bigquery, inserisci il seguente snippet di codice:

        clusterBy: ["CLUSTER_COLUMN"]

    Sostituisci CLUSTER_COLUMN con il nome della colonna in base alla quale vuoi raggruppare la tabella. Per saperne di più, consulta clustering_column_list.

  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una tabella partizionata raggruppata in base alle colonne name e revenue:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    clusterBy: ["name", "revenue"]
  }
}
SELECT CURRENT_TIMESTAMP() as ts, name, revenue

Configurare una tabella incrementale

Questa sezione mostra come utilizzare Dataform Core per configurare una tabella incrementale.

Informazioni sulle tabelle incrementali

Dataform aggiorna le tabelle in modo diverso in base al tipo di tabella. Durante ogni esecuzione di una tabella o di una vista, Dataform ricrea l'intera tabella o vista da zero.

Quando definisci una tabella incrementale, Dataform la crea da zero solo la prima volta. Durante le esecuzioni successive, Dataform inserisce o unisce solo le nuove righe nella tabella incrementale in base alle condizioni configurate.

Dataform inserisce nuove righe solo nelle colonne già esistenti nella tabella incrementale. Se apporti modifiche alla query di definizione della tabella incrementale, ad esempio aggiungendo una nuova colonna, devi decidere se ricreare la tabella da zero. Per ricostruire la tabella, la prossima volta che attivi un'esecuzione della tabella, seleziona l'opzione Esegui con aggiornamento completo. Per altre opzioni, consulta Modificare lo schema di una tabella incrementale senza un aggiornamento completo.

Ecco alcuni casi d'uso comuni per le tabelle incrementali:

Ottimizzazione delle prestazioni
Per alcuni tipi di dati, come i log web o i dati di analisi, potresti voler elaborare solo i nuovi record anziché rielaborare l'intera tabella.
Riduzione della latenza
Puoi utilizzare le tabelle incrementali per eseguire i workflow in modo rapido ma frequente, riducendo la latenza downstream delle tabelle di output.
Snapshot giornalieri
Puoi configurare una tabella incrementale per creare snapshot giornalieri dei dati della tabella, ad esempio per l'analisi longitudinale delle impostazioni utente archiviate in un database di produzione.

Elabora un sottoinsieme di righe in una tabella incrementale

Per determinare un sottoinsieme di righe da elaborare da Dataform durante ogni esecuzione, aggiungi una clausola WHERE condizionale al file di definizione SQLX della tabella incrementale. Nella clausola WHERE, puoi specificare una condizione incrementale e una condizione non incrementale. Dataform applica la condizione incrementale durante l'esecuzione della tabella senza un aggiornamento completo e la condizione non incrementale durante l'esecuzione con un aggiornamento completo.

Per configurare una tabella incrementale:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Apri un file SQLX di definizione della tabella incrementale.

  4. Inserisci una clausola WHERE nel seguente formato:

    config { type: "incremental" }

SELECT_STATEMENT

${when(incremental(), `WHERE INCREMENTAL_CONDITION`, `WHERE NON_INCREMENTAL_CONDITION`) }

    Sostituisci quanto segue:

    • SELECT_STATEMENT: l'istruzione SELECT che definisce la tabella.
    • INCREMENTAL_CONDITION: la condizione specificata nella clausola WHERE per selezionare le righe che Dataform deve elaborare durante l'esecuzione della tabella senza un aggiornamento completo.
    • NON_INCREMENTAL_CONDITION: la condizione specificata nella clausola WHERE per selezionare le righe che Dataform deve elaborare durante l'esecuzione della tabella con un aggiornamento completo.

  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una tabella incrementale che elabora in modo incrementale le righe della tabella productiondb.logs:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(),
   `WHERE date > (SELECT MAX(date) FROM ${self()}) AND country = "UK"`,
   `WHERE country = "UK"`)}

Il seguente esempio di codice mostra una tabella incrementale che crea uno snapshot della tabella productiondb.customers:

config { type: "incremental" }

SELECT CURRENT_DATE() AS snapshot_date, customer_id, name, account_settings FROM ${ref("productiondb", "customers")}

${when(incremental(), `WHERE snapshot_date > (SELECT MAX(snapshot_date) FROM ${self()})`) }

Unisci le righe in una tabella incrementale

Per verificare che una tabella incrementale contenga una sola riga corrispondente a una combinazione di colonne selezionata, imposta le colonne selezionate come uniqueKey per unire le righe che hanno lo stesso valore uniqueKey. Durante l'aggiornamento della tabella, Dataform unisce le righe con lo stesso valore uniqueKey anziché aggiungerle.

Per configurare l'unione in una tabella incrementale, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Seleziona un file SQLX di definizione della tabella incrementale

  4. Nel blocco config, imposta le colonne selezionate come uniqueKey nel seguente formato:

    uniqueKey: ["COLUMN_NAME"]

    Sostituisci COLUMN_NAME con il nome di una colonna selezionata.

  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una tabella incrementale con la colonna transaction_id impostata come uniqueKey per verificare che contenga sempre una riga:

config {
  type: "incremental",
  uniqueKey: ["transaction_id"]
}

SELECT timestamp, action FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Filtra le righe in una tabella incrementale

In una tabella partizionata incrementale, per evitare che Dataform esegua la scansione dell'intera tabella per trovare le righe corrispondenti, imposta updatePartitionFilter in modo che prenda in considerazione solo un sottoinsieme di record.

Il seguente esempio di codice mostra una tabella partizionata incrementale con unione configurata dalle proprietà updatePartitionFilter e uniqueKey, utilizzando due tipi distinti di filtro:

  • Eliminazione mirata utilizzando la proprietà updatePartitionFilter: limita le partizioni analizzate nella tabella di destinazione durante un'operazione MERGE per trovare le righe che corrispondono a uniqueKey.
  • Pruning dell'origine utilizzando una clausola WHERE: limita le righe recuperate dalla tabella di origine durante un'esecuzione incrementale. Questo risultato si ottiene utilizzando la funzione ${when(incremental(), ...)}.
config {
  type: "incremental",
  uniqueKey: ["transaction_id"],
  bigquery: {
    partitionBy: "DATE(timestamp)",
    updatePartitionFilter:
        "timestamp >= timestamp_sub(current_timestamp(), interval 24 hour)"
  }
}

SELECT timestamp, action FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Evita le scansioni di intere tabelle durante l'importazione da una tabella partizionata

Quando crei una tabella incrementale che fa riferimento a una tabella partizionata, ti consigliamo di creare la query della tabella in modo da evitare scansioni complete della tabella partizionata durante ogni aggiornamento incrementale.

Puoi limitare il numero di partizioni che BigQuery analizza per aggiornare la tabella incrementale utilizzando un'espressione costante nella query della tabella. Per trasformare un valore della tabella partizionata in un'espressione costante, utilizza lo scripting BigQuery per dichiarare il valore come variabile nel blocco pre_operations. Quindi, utilizza la variabile come espressione costante in una clausola WHERE nella query SELECT.

Con questa configurazione, Dataform aggiorna la tabella incrementale in base alle partizioni più recenti della tabella partizionata a cui viene fatto riferimento, senza scansionare l'intera tabella.

Per configurare una tabella incrementale che fa riferimento a una tabella partizionata ed evita scansioni complete della tabella:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Seleziona un file SQLX di definizione della tabella incrementale
  4. Nel blocco pre_operations, dichiara una variabile con lo scripting BigQuery.
  5. Filtra l'istruzione SELECT che definisce la tabella con una clausola WHERE che fa riferimento alla variabile dichiarata.
  6. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una tabella incrementale in cui la tabella raw_events di riferimento è partizionata per event_timestamp:

config {
  type: "incremental",
}

pre_operations {
  DECLARE event_timestamp_checkpoint DEFAULT (
    ${when(incremental(),
    `SELECT max(event_timestamp) FROM ${self()}`,
    `SELECT timestamp("2000-01-01")`)}
  )
}

SELECT
  *
FROM
  ${ref("raw_events")}
WHERE event_timestamp > event_timestamp_checkpoint

Nell'esempio di codice precedente, la variabile event_timestamp_checkpoint è definita nel blocco pre_operations. La variabile event_timestamp_checkpoint viene quindi utilizzata come espressione costante nella clausola WHERE.

Ricostruisci una tabella incrementale da zero con l'aggiornamento completo

Puoi forzare la ricreazione di una tabella incrementale da zero utilizzando l'interfaccia a riga di comando con l'opzione --full-refresh o l'opzione Esegui con aggiornamento completo quando attivi l'esecuzione di un flusso di lavoro.

Quando selezioni l'opzione di aggiornamento completo, nello spazio di lavoro di sviluppo o utilizzando la CLI Dataform, Dataform ignora il parametro ${when(incremental(), ... } durante l'esecuzione e ricrea la tabella con un'istruzione CREATE OR REPLACE.

Proteggere una tabella incrementale dall'aggiornamento completo

Per proteggere una tabella incrementale dalla ricostruzione da zero e dalla potenziale perdita di dati, puoi impostarla come protected. Potresti voler impedire la ricreazione di una tabella incrementale se l'origine dati è temporanea.

Per contrassegnare una tabella incrementale come protected, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Seleziona un file SQLX di definizione della tabella incrementale.
  4. Nel blocco config, inserisci protected: true.
  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una tabella incrementale contrassegnata come protected:

config {
  type: "incremental",
  protected: true
}
SELECT ...

Modificare lo schema di una tabella incrementale senza un aggiornamento completo

Puoi modificare lo schema di una tabella incrementale nella query SELECT senza un aggiornamento completo della tabella utilizzando la proprietà di configurazione onSchemaChange. Questa proprietà ti consente di aggiungere nuove colonne o rimuovere colonne esistenti da una tabella, conservando i dati storici. Questo approccio aiuta a prevenire la perdita di dati ed evita gli aggiornamenti manuali in BigQuery.

Per utilizzare questa funzionalità, devi installare Dataform Core 3.0.11 o versioni successive.

Dopo aver impostato la proprietà onSchemaChange, Dataform controlla la query SELECT per determinare se sono state aggiunte o rimosse colonne. Dataform esegue quindi l'azione specificata dal valore della proprietà onSchemaChange.

Per impostare la proprietà onSchemaChange, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Seleziona un file SQLX di definizione della tabella incrementale.

  4. Nel blocco config, inserisci il seguente snippet di codice:

    onSchemaChange: "ON_CHANGE_ACTION"

    Sostituisci ON_CHANGE_ACTION con una delle seguenti azioni:

    • IGNORE (impostazione predefinita): ignora le colonne aggiunte e mostra un errore per le colonne mancanti. Se onSchemaChange non è impostato, questo è il comportamento predefinito quando lo schema cambia.
    • FAIL: interrompe l'azione se Dataform rileva modifiche allo schema, il che contribuisce a mantenere lo schema coerente.
    • EXTEND: aggiunge nuove colonne dalla query alla tabella incrementale e aggiunge i valori NULL per i record precedenti. Mostra un errore se una colonna viene rimossa o non è presente nello schema originale della query. Puoi utilizzare questa impostazione per aggiungere nuove colonne alle tabelle incrementali durante il runtime.

    • SYNCHRONIZE: aggiunge nuove colonne dalla query alla tabella incrementale e aggiunge i valori NULL per i record precedenti. Rimuove le colonne presenti nello schema originale, ma che ora non sono presenti nella query corrente.

  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una tabella incrementale con l'azione EXTEND impostata per la proprietà onSchemaChange:

config {
    type: "incremental",
    onSchemaChange: "EXTEND",
}
SELECT ...

Aggiungi documentazione della tabella

Questa sezione mostra come aggiungere descrizioni di una tabella e delle relative colonne e record a un file SQLX Dataform Core.

Puoi aggiungere descrizioni di tabelle, colonne e record a tutti i tipi di tabelle in Dataform: tabelle, tabelle incrementali e viste.

Ti consigliamo di documentare quanto segue:

  • Lo scopo della tabella.
  • Il contenuto o il ruolo di colonne o record nella tabella.
  • La relazione della tabella e di altre azioni del workflow, ad esempio le tabelle o le viste che dipendono dalla tabella corrente.
  • Le asserzioni applicate alla tabella.
  • Le operazioni pre o post applicate alla tabella.
  • Il proprietario della tabella, ovvero l'utente che l'ha creata. Queste informazioni potrebbero essere utili se più membri del team lavorano a un workflow.

Aggiungi metadati per Knowledge Catalog

I metadati standard di BigQuery, ad esempio set di dati, tabelle e viste, sono automaticamente disponibili in Knowledge Catalog.

Puoi anche definire metadati personalizzati per le tabelle e le viste direttamente nel blocco di configurazione dei file .sqlx. Al completamento di un'azione, Dataform avvia automaticamente una sincronizzazione dei metadati con Knowledge Catalog. Questo processo di arricchimento aggiorna Knowledge Catalog con i metadati semantici definiti nella configurazione SQLX.

Utilizza la chiave dei metadati per specificare le informazioni per Knowledge Catalog. Il processo di arricchimento supporta i seguenti costrutti di metadati:

  • Panoramica: documentazione e testo riepilogativo per la voce. Richiede Dataform Core versione 3.0.37 o successive.
  • Aspetti generici: dettagli semantici come informazioni sul tipo e sul sistema della tabella. Richiede Dataform Core versione 3.0.52 o successive.

La seguente configurazione di esempio mostra come aggiungere una panoramica e aspetti dei metadati generici a una configurazione della tabella per Knowledge Catalog:

config {
  type: "table",
  metadata: {
    overview: "This table provides standardized trip data.",
    extraProperties: {
        generic: {
              system: "BigQuery",
              type: "table"
        }
      }
  }
}

Per controllare lo stato di un aggiornamento dei metadati, consulta Ispeziona i log di esecuzione del workspace per i workflow di Dataform o Visualizza le esecuzioni manuali precedenti per le pipeline di BigQuery.

Per verificare i metadati sincronizzati, puoi cercare l'asset in Knowledge Catalog. Per saperne di più, consulta Cerca risorse.

Aggiungere una descrizione della tabella

Per aggiungere una descrizione a una tabella in un file SQLX:

  1. Nella console Google Cloud , vai alla pagina Dataform.

    Vai a Dataform

  2. Seleziona un repository.

  3. Seleziona un workspace di sviluppo.

  4. Nel riquadro File, fai clic sul file SQLX di definizione della tabella che vuoi modificare.

  5. Nel blocco config del file, inserisci la descrizione della tabella nel seguente formato:

    description: "Description of the table",

  6. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una descrizione della tabella aggiunta al blocco config di un file di definizione della tabella SQLX:

config {
  type: "table",
  description: "Description of the table",
 }

Aggiungi descrizioni di colonne e record

Per aggiungere descrizioni di singole colonne e singoli record a un file SQLX:

  1. Nel blocco config del file di definizione della tabella, inserisci columns: {}.

  2. All'interno di columns: {}, inserisci le descrizioni delle colonne nel seguente formato:

    column_name: "Description of the column",

  3. All'interno di columns: {}, inserisci le descrizioni dei record nel seguente formato:

      record_name: {
      description: "Description of the record",
      columns: {
        record_column_name: "Description of the record column"
      }
}

  4. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra le descrizioni di tabelle, colonne e record nel blocco config di un file di definizione della tabella SQLX:

config {
  type: "table",
  description: "Description of the table.",
  columns: {
    column1_name: "Description of the first column",
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    record_name: {
      description: "Description of the record.",
      columns: {
       record_column1_name: "Description of the first record column",
       record_column2_name: "Description of the second record column",
      }
    }
  }
}
SELECT
  "first_column_value" AS column_1_name,
  "second_column_value" AS column_2_name,
  "third_column_value" AS column_3_name,
  STRUCT("first" AS record_column1_name,
    "second" AS record_column2_name) AS record_name

Riutilizza la documentazione delle colonne con gli include

Puoi riutilizzare le descrizioni delle colonne in Dataform nel tuo flusso di lavoro SQL con le inclusioni JavaScript. Potresti voler riutilizzare la documentazione delle colonne se hai più colonne con lo stesso nome e la stessa descrizione nel tuo flusso di lavoro SQL.

Puoi definire una costante con una descrizione di una singola colonna o una costante con un insieme di descrizioni di colonne per riutilizzare le descrizioni di tutte le colonne di una tabella. Per saperne di più sulla creazione e sull'utilizzo degli elementi include in Dataform, consulta Riutilizza il codice in un singolo repository con gli elementi include.

Il seguente esempio di codice mostra più costanti con descrizioni di singole colonne definite nel file JavaScript includes/docs.js:


// filename is includes/docs.js

const user_id = `A unique identifier for a user`;
const age = `The age of a user`;
const creation_date = `The date this user signed up`;
const user_tenure = `The number of years since the user's creation date`;
const badge_count = `The all-time number of badges the user has received`;
const questions_and_answer_count = `The all-time number of questions and answers the user has created`;
const question_count = `The all-time number of questions the user has created`;
const answer_count = `The all-time number of answers the user has created`;
const last_badge_received_at = `The time the user received their most recent badge`;
const last_posted_at = `The time the user last posted a question or answer`;
const last_question_posted_at = `The time the user last posted an answer`;
const last_answer_posted_at = `The time the user last posted a question`;

module.exports = {
   user_id,
   age,
   creation_date,
   user_tenure,
   badge_count,
   questions_and_answer_count,
   question_count,
   answer_count,
   last_badge_received_at,
   last_posted_at,
   last_question_posted_at,
   last_answer_posted_at,
};

Il seguente esempio di codice mostra le costanti user_id e age, definite in includes/docs.js, utilizzate nel file di definizione della tabella SQLX definitions/my_table.sqlx per generare la documentazione per le colonne selezionate nella tabella:

config {
  type: "table",
  description: "Table description.",
  columns: {
    user_id: docs.user_id,
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    age: docs.age,
  }
}

SELECT ...

Il seguente esempio di codice mostra una costante con un insieme di descrizioni delle colonne definite nel file JavaScript includes/docs.js:


// filename is includes/docs.js

const columns = {
    user_id = `A unique identifier for a user`,
    age = `The age of a user`,
    creation_date = `The date this user signed up`,
    user_tenure = `The number of years since the user's creation date`,
    badge_count = `The all-time number of badges the user has received`,
    questions_and_answer_count = `The all-time number of questions and answers the user has created`,
    question_count = `The all-time number of questions the user has created`,
    answer_count = `The all-time number of answers the user has created`,
    last_badge_received_at = `The time the user received their most recent badge`,
    last_posted_at = `The time the user last posted a question or answer`,
    last_question_posted_at = `The time the user last posted an answer`,
    last_answer_posted_at = `The time the user last posted a question`,
}


module.exports = {
  columns
};

Il seguente esempio di codice mostra la costante columns, definita in includes/table_docs.js e utilizzata nel file di definizione della tabella SQLX definitions/my_table.sqlx per generare la documentazione per tutte le colonne della tabella:

config { type: "table",
description: "My table description",
columns: docs.columns
}

SELECT 1 AS one

Aggiungi etichette BigQuery

Questa sezione mostra come aggiungere etichette alle tabelle in Dataform.

BigQuery supporta l'aggiunta di etichette alle risorse. Per ulteriori informazioni sulle etichette in BigQuery, consulta Introduzione alle etichette.

Per aggiungere un'etichetta BigQuery a una tabella in Dataform, aggiungi l'etichetta al blocco bigquery nel blocco config del file SQLX di definizione della tabella.

Per aggiungere un'etichetta BigQuery a un file di definizione della tabella, segui questi passaggi:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Seleziona un file di definizione della tabella SQLX.

  4. Nel blocco config, aggiungi un'etichetta nel seguente formato:

    bigquery: {
    labels: {
      LABEL1: "VALUE_OF_LABEL1"
    }
  }

    Sostituisci quanto segue:

    • LABEL1: il nome dell'etichetta
    • VALUE_OF_LABEL1: il valore dell'etichetta

  5. (Facoltativo) Per aggiungere un'etichetta con un nome che contiene caratteri speciali, inserisci il nome dell'etichetta tra virgolette ("").

  6. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra le etichette department:shipping e cost-center:logistics aggiunte al blocco bigquery in un file SQLX di definizione di una tabella partizionata:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    labels: {
      department: "shipping",
      "cost-center": "logistics"
    }
  }
}

SELECT CURRENT_TIMESTAMP() AS ts

Conserva i metadati dei controlli di accesso

Quando Dataform esegue azioni del flusso di lavoro che ricreano oggetti, ad esempio tabelle standard, viste o tabelle incrementali durante un aggiornamento completo, in genere genera istruzioni CREATE OR REPLACE in BigQuery per aggiornare lo schema e i dati dell'oggetto. Per impostazione predefinita in BigQuery, le operazioni di sostituzione delle tabelle eliminano automaticamente i metadati dei controlli dell'accesso a livello di colonna esistenti, ad esempio norme sui dati e tag di governance dei dati (anteprima) applicati da sistemi di governance dei dati esterni.

Puoi configurare Dataform in modo che conservi automaticamente le policy dei dati BigQuery esistenti, incluse quelle senza tag, e i tag di governance dei dati tra le esecuzioni del workflow attivando l'opzione preserveGovernanceControls. Quando questa opzione è abilitata, Dataform esegue la seguente sequenza durante l'esecuzione:

  1. Esegue il backup dei metadati delle colonne esistenti dalla vista INFORMATION_SCHEMA.COLUMNS.
  2. Crea una tabella di staging vuota intermedia.
  3. Applica nuovamente le policy di sicurezza alla tabella intermedia.
  4. Compila la tabella con i dati.
  5. Esegue uno scambio di tabelle atomico per sostituire la tabella originale.

Abilitare la conservazione della governance automatizzata per una tabella

Per attivare la conservazione automatica delle norme sui dati e dei tag di governance dei dati per una singola tabella, una tabella incrementale o una vista, imposta il parametro preserveGovernanceControls su true nel blocco config del file di definizione SQLX dell'oggetto.

Per configurare la conservazione della governance per una singola tabella:

  1. Vai al tuo workspace di sviluppo.
  2. Nel riquadro File, espandi definitions/.
  3. Seleziona un file di definizione della tabella SQLX.
  4. Nel blocco config, inserisci preserveGovernanceControls: true.
  5. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra una definizione di tabella con la conservazione della governance abilitata:

config {
  type: "table",
  preserveGovernanceControls: true
}

SELECT
  customer_id,
  email,
  phone_number
FROM
  ${ref("raw_customers")}

Abilita la conservazione della governance automatica per tutte le tabelle

Puoi abilitare la conservazione automatica della governance come comportamento predefinito a livello di progetto per tutte le tabelle, le tabelle incrementali e le viste nel repository impostando il parametro preserveGovernanceControls su true nel file workflow_settings.yaml del repository.

Per impostare il valore predefinito a livello di progetto, aggiungi il parametro preserveGovernanceControls al file workflow_settings.yaml, come mostrato nell'esempio seguente:

defaultProject: my-gcp-project-id
defaultDataset: my_dataform_dataset
defaultLocation: US
defaultAssertionDataset: my_dataform_assertions
dataformCoreVersion: 3.0.62
preserveGovernanceControls: true

Se attivi la conservazione della governance a livello globale nel file workflow_settings.yaml, puoi eseguire l'override di questo comportamento e disattivare la conservazione per una tabella specifica impostando il parametro preserveGovernanceControls su false nel blocco config della singola tabella.

Riapplica le policy di accesso alle righe utilizzando le operazioni successive

Sebbene l'attivazione della conservazione della governance protegga automaticamente i metadati a livello di colonna, come le policy dei dati BigQuery e i tag di governance dei dati, non ripristina automaticamente le policy di accesso alle righe. Poiché la sostituzione degli oggetti elimina le regole di sicurezza a livello di riga esistenti, puoi riapplicare le policy di accesso alle righe dopo la creazione della tabella definendo istruzioni DDL SQL all'interno del blocco post_operations del file di definizione della tabella SQLX.

Il seguente esempio di codice mostra come applicare nuovamente una policy di accesso alle righe nel blocco post_operations di una tabella:

config {
  type: "table",
}

SELECT
  bike_id,
  start_time,
  duration
FROM
  ${ref("raw_trips")}

post_operations {
  CREATE ROW ACCESS POLICY my_policy
  ON ${self()}
  GRANT TO ('serviceAccount:sa-name@example-gcp-project.')
  FILTER USING (bike_id LIKE 'US-%');
}

Limitazioni

Quando utilizzi la funzionalità di conservazione della governance, tieni presente le seguenti limitazioni:

  • Rimozioni di colonne dello schema. Se una colonna viene rimossa dalla definizione della tabella SQLX, Dataform ignora eventuali tag e criteri di backup dei dati associati alla colonna eliminata durante la fase di ripristino.
  • Ridenominazione delle colonne dello schema. Se rinomini una colonna esistente nella definizione della tabella SQLX, Dataform non può mappare le associazioni di tag o policy precedenti al nuovo nome della colonna. Di conseguenza, i tag e le policy esistenti in queste colonne vengono persi o scambiati e devi riapplicare manualmente i tag e le policy alle colonne rinominate.
  • Modifiche al partizionamento e al clustering. La modifica del partizionamento o del clustering di una tabella esistente che utilizza preserveGovernanceControls non è supportata in loco. BigQuery rifiuta la sostituzione. Per risolvere il problema, elimina la tabella ed esegui nuovamente il workflow. Devi applicare manualmente le policy dei dati a livello di colonna e i tag di governance dei dati alla tabella ricreata.
  • Rimozioni per scadenza della partizione. La rimozione della proprietà partitionExpirationDays dalla configurazione non rimuove la scadenza della partizione da una tabella esistente. Per rimuovere la scadenza della partizione da una tabella esistente, devi impostare l'opzione in modo esplicito sul valore selezionato o modificare la tabella manualmente.
  • Tipi di tabelle. Dopo l'esecuzione riuscita, la tabella viene segnalata come clone di una tabella (table_type = 'CLONE' in INFORMATION_SCHEMA.TABLES). Si comporta come una tabella standard e la tabella di base mostrata nei metadati del clone non esiste più.
  • Metadati a livello di tabella e policy di accesso alle righe. I tag a livello di tabella e le policy di accesso a livello di riga non vengono conservati tra le esecuzioni. Puoi applicare nuovamente le policy di accesso alle righe dopo la creazione della tabella definendo le istruzioni DDL SQL all'interno del blocco post_operations del file di definizione della tabella SQLX.
  • Scambi di tabelle non transazionali. Le operazioni DDL di BigQuery non sono transazionali. Se l'esecuzione di un workflow non va a buon fine a metà della sequenza di ridenominazione atomica, potrebbe essere necessario un intervento manuale se la tabella di destinazione viene lasciata in uno stato intermedio, ad esempio se la tabella viene rinominata in modo permanente in TABLE_NAME_old o lasciata come df_tmp_intermediate.
  • Tag di policy. La conservazione della governance automatizzata non supporta i tag di policy di BigQuery. Aggiungi invece tag di governance dei dati (anteprima) e criteri dei dati BigQuery alle colonne per utilizzare l'integrazione SQL integrata.

Considerazioni sui costi di archiviazione

Quando preserveGovernanceControls è abilitato, Dataform ricrea la tabella tramite una tabella temporanea di gestione temporanea a ogni esecuzione (o a ogni aggiornamento completo per le tabelle incrementali). Come per qualsiasi ricreazione di tabelle in BigQuery, la versione precedente dei dati della tabella viene conservata in time travel per la finestra configurata del set di dati, ovvero sette giorni per impostazione predefinita, seguiti da un periodo di sicurezza di sette giorni.

La fatturazione dell'archiviazione logica è il modello di fatturazione predefinito in BigQuery, mentre la fatturazione dell'archiviazione fisica è un'impostazione di attivazione configurata per set di dati. Se il set di dati utilizza la fatturazione per lo spazio di archiviazione fisico, i dati di viaggio nel tempo e di sicurezza conservati vengono fatturati in aggiunta alla tabella attiva. Di conseguenza, le tabelle di grandi dimensioni che vengono ricreate di frequente possono aumentare notevolmente i costi di archiviazione.

Per ridurre questo costo:

Passaggi successivi