Criar tabelas

No Dataform, uma tabela é um dos tipos de objetos que compõem um fluxo de trabalho. É possível criar tabelas que referenciam dados das fontes de dados declaradas para o fluxo de trabalho ou de outras tabelas no fluxo de trabalho. O Dataform compila as definições de tabela em SQL em tempo real. Quando você aciona a execução, o Dataform executa o código SQL e cria as tabelas definidas no BigQuery.

É possível criar os seguintes tipos de tabela em um arquivo SQLX do type: "table":

Também é possível definir partições e clusters de tabela.

Para manter um registro da finalidade de uma tabela ou da relação dela com outras tabelas no seu fluxo de trabalho, adicione documentação à tabela ou às colunas selecionadas.

Para testar os dados em uma tabela em relação a condições específicas, crie consultas de teste de qualidade de dados chamadas declarações. O Dataform executa declarações sempre que atualiza seu fluxo de trabalho e alerta você se alguma declaração falhar.

Para modificar o esquema, o banco de dados e o nome padrão de uma tabela selecionada, é possível modificar as configurações da tabela.

Para desativar a criação de tabelas ou executar uma instrução SQL antes ou depois da criação de tabelas, configure outras ações.

Para organizar as tabelas no BigQuery depois de executá-las, adicione rótulos do BigQuery. Para saber mais, consulte Introdução aos rótulos.

Para restringir o acesso aos dados no nível da coluna da tabela, adicione tags de política do BigQuery. Para saber mais, consulte Introdução ao controle de acesso no nível da coluna.

Para preservar as políticas de dados e as tags de governança de dados (pré-lançamento) do BigQuery aplicadas por sistemas externos em execuções de tabela, é possível preservar os metadados de controles de acesso.

Além de definir tabelas em um arquivo SQLX type: "table", é possível criar tabelas vazias definindo uma consulta SQL personalizada em um arquivo SQLX type: "operations". Talvez você queira criar uma tabela vazia para que um serviço diferente possa preenchê-la com dados.

Antes de começar

  1. No Google Cloud console, acesse a página Dataform.

    Acesse o Dataform

  2. Crie e inicialize um espaço de trabalho de desenvolvimento no repositório.

  3. Opcional: declare uma fonte de dados.

Funções exigidas

Para receber as permissões necessárias para realizar as tarefas neste documento, peça ao administrador para conceder a você os seguintes papéis do IAM:

Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Também é possível conseguir as permissões necessárias usando papéis personalizados ou outros papéis predefinidos.

Para preservar os metadados de controles de acesso, a conta de serviço que executa os fluxos de trabalho do Dataform precisa ter permissões específicas do IAM, dependendo da tarefa:

  • Extrair metadados: para ler a visualização INFORMATION_SCHEMA.COLUMNS, a conta de serviço precisa ter permissões de leitura no esquema do conjunto de dados ou da tabela. Essas permissões estão incluídas nos seguintes papéis do IAM:

  • Aplicar tags de governança de dados: para anexar e gerenciar tags de governança de dados em tabelas e visualizações, a conta de serviço precisa das permissões bigquery.tables.createTagBinding e bigquery.tables.deleteTagBinding. Essas permissões estão incluídas nos seguintes papéis predefinidos do IAM:

  • Aplicar políticas de dados: para criar e atualizar políticas de dados (incluindo políticas sem tags), a conta de serviço precisa das permissões bigquery.dataPolicies.create e bigquery.dataPolicies.update. Essas permissões estão incluídas nos seguintes papéis predefinidos do IAM:

Criar uma tabela

Nesta seção, mostramos como criar tabelas com o Dataform Core no Dataform.

Sobre as definições de tabela

Para definir uma tabela, defina o tipo de tabela e escreva uma instrução SELECT em um arquivo SQLX type: "table". Em seguida, o Dataform compila o código do Dataform Core em SQL, executa o código SQL e cria as tabelas definidas no BigQuery.

Em uma instrução SELECT do Dataform Core, você define a estrutura da tabela e faz referência a outros objetos do fluxo de trabalho.

Além de definir tabelas em um arquivo SQLX type: "table", é possível criar tabelas vazias definindo uma consulta SQL personalizada em um arquivo SQLX type: "operations". Para mais informações, consulte Criar uma tabela vazia.

Dependências de referência com ref

Para referenciar uma ação de fluxo de trabalho em uma instrução SELECT e adicioná-la automaticamente como uma dependência, use a função ref. O Dataform executa as dependências antes das tabelas que dependem delas para verificar a ordem correta do pipeline.

A função ref é uma função principal integrada do Dataform que é fundamental para o gerenciamento de dependências no Dataform. A função ref permite referenciar e depender automaticamente dos seguintes objetos definidos no fluxo de trabalho do Dataform, em vez de codificar o esquema e os nomes das tabelas:

O Dataform usa a função ref para criar uma árvore de dependência de todas as tabelas que serão criadas ou atualizadas.

Após a compilação, o Dataform adiciona instruções boilerplate à instrução SQL, como CREATE, REPLACE, INSERT ou MERGE.

O exemplo de código a seguir mostra uma definição de tabela com o uso da função 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

Na função ref, você fornece o nome da declaração de tabela ou fonte de dados de que quer depender. Normalmente, esse é o nome do arquivo SQLX em que a declaração da tabela ou da fonte de dados é definida.

Se o nome de uma tabela for substituído, use o nome substituído na função ref. Por exemplo, faça referência a uma tabela com config { name: "overridden_name" } como ref("overridden_name"). Para mais informações, consulte Substituir configurações de tabela e Referenciar uma tabela com um nome de tabela substituído.

Quando você tem várias tabelas com o mesmo nome em esquemas diferentes, é possível fazer referência a uma tabela específica fornecendo dois argumentos para a função ref: nome do esquema e nome da tabela.

O exemplo de código a seguir mostra a função ref com dois argumentos para especificar uma tabela em um esquema específico:

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

Também é possível adicionar dependências de tabela manualmente ao bloco config para tabelas, asserções, declarações de fonte de dados ou operações SQL personalizadas que não são referenciadas em uma função ref na instrução SELECT. O Dataform executa essas dependências antes das tabelas dependentes.

O exemplo de código a seguir mostra uma dependência de tabela no bloco config:

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

Para mais informações sobre o gerenciamento de dependências no fluxo de trabalho, consulte Definir dependências.

Fazer referência a outras tabelas com resolve

A função resolve permite referenciar uma declaração de tabela ou fonte de dados em uma instrução SELECT, como a função ref, mas não adiciona a referência como uma dependência. Isso significa que o objeto referenciado usando a função resolve não afeta a execução da tabela que usa a função resolve.

Para mais informações sobre as funções integradas do Dataform Core, consulte a referência do Dataform Core.

Criar um arquivo SQLX para uma definição de tabela

Armazene os arquivos SQLX de definição de tabela no diretório definitions/. Para criar um novo arquivo SQLX no diretório definitions/, siga estas etapas:

  1. No console do Google Cloud , acesse a página Dataform.

    Acesse o Dataform

  2. Para abrir um repositório, clique no nome dele.

  3. Para abrir um espaço de trabalho de desenvolvimento, clique no nome dele.

  4. No painel Arquivos, ao lado de definitions/, clique em Mais.

  5. Selecione Criar arquivo.

  6. No campo Adicionar um caminho de arquivo, insira o nome do arquivo seguido por .sqlx depois de definitions/. Por exemplo, definitions/my-table.sqlx.

    Os nomes de arquivos só podem incluir números, letras, hifens e sublinhados.

  7. Selecione Criar arquivo.

Definir o tipo de tabela

Para criar uma nova definição de tipo de tabela, siga estas etapas:

  1. No espaço de trabalho de desenvolvimento, no painel Arquivos, abra o diretório definitions/.
  2. Selecione o arquivo SQLX de definição de tabela que você quer editar.

  3. No arquivo, digite este snippet de código:

    config { type: "TABLE_TYPE" }

    Substitua TABLE_TYPE por um dos seguintes tipos de tabela:

    • table
    • incremental
    • view

  4. Opcional: para definir uma visualização materializada, insira a propriedade materialized em type: "view" no seguinte formato:

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

    Para mais informações, consulte ITableConfig.

  5. Opcional: clique em Formatar.

Definir a estrutura e as dependências da tabela

Para criar uma instrução SELECT de definição de tabela e definir a estrutura e as dependências da tabela, siga estas etapas:

  1. No espaço de trabalho de desenvolvimento, no painel Arquivos, abra o diretório definitions/.
  2. Selecione o arquivo SQLX de definição de tabela que você quer editar.
  3. Abaixo do bloco config, escreva uma instrução SELECT.
  4. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma definição de tabela com uma instrução SELECT e a função 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

Mitigar limitações de esquema de coluna

Quando você define uma tabela em um arquivo SQLX type: "table" ou type: "incremental", o Dataform compila a consulta SELECT em uma instrução CREATE OR REPLACE TABLE ... AS SELECT no BigQuery sem uma lista de definição de esquema de coluna explícita. Como resultado, o BigQuery cria colunas no modo NULLABLE por padrão.

As definições de tabela do Dataform não permitem definir o modo de uma coluna como REQUIRED (NOT NULL) no bloco config ou na propriedade columns. No BigQuery, também não é possível mudar o modo de uma coluna de NULLABLE para REQUIRED sem recriar a tabela com uma definição de esquema explícita. Para mais informações sobre como modificar modos de coluna no BigQuery, consulte Alterar o modo de uma coluna.

Para validar valores não nulos ou criar tabelas com colunas REQUIRED, use uma das seguintes abordagens:

Validar valores não nulos com declarações
Adicione uma declaração integrada nonNull ou rowConditions no bloco config da tabela, como assertions: { nonNull: ["column_name"] } ou assertions: { rowConditions: ["column_name IS NOT NULL"] }. Também é possível definir uma declaração manual em um arquivo SQLX type: "assertion" separado. As declarações verificam se as linhas não contêm valores NULL durante a execução do fluxo de trabalho, sem alterar o modo de coluna do BigQuery de NULLABLE para REQUIRED. Para mais informações sobre declarações, consulte Testar dados com declarações.
Defina colunas NOT NULL com operações SQL personalizadas ou no BigQuery
Para criar uma tabela em que o esquema do BigQuery define colunas para o modo REQUIRED, escreva uma instrução DDL CREATE OR REPLACE TABLE com restrições de coluna NOT NULL explícitas em um arquivo de operação SQL personalizada com config { type: "operations", hasOutput: true } ou crie e gerencie o esquema da tabela diretamente no BigQuery. Para mais informações sobre como definir colunas REQUIRED em instruções DDL, consulte Como criar uma tabela com colunas REQUIRED.

Adicionar dependências de tabela manuais

Para adicionar dependências de tabela que não são referenciadas na instrução SELECT, mas que precisam ser executadas antes da tabela atual, siga estas etapas:

  1. No espaço de trabalho de desenvolvimento, no painel Arquivos, abra o diretório definitions/.
  2. Selecione o arquivo SQLX de definição de tabela que você quer editar.

  3. No bloco config da tabela, insira o seguinte snippet de código:

    dependencies: [ "DEPENDENCY_TABLE", ]

    Substitua DEPENDENCY_TABLE pelo nome de arquivo da tabela que você quer adicionar como dependência. É possível inserir vários nomes de arquivo.

  4. Opcional: clique em Formatar.

O exemplo de código a seguir mostra duas tabelas adicionadas como dependências manuais ao bloco config de um arquivo de definição de tabela:

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

Substituir configurações da tabela

É possível modificar o esquema, o banco de dados e o nome padrão de uma tabela selecionada.

Por padrão, uma tabela segue o esquema e a configuração do banco de dados que você definiu em workflow_settings.yaml. O nome de uma tabela é o mesmo que o nome do arquivo SQLX de definição de tabela.

Para substituir o esquema e o nome de uma tabela selecionada, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.

  2. No painel Arquivos, abra definitions/.

  3. Abra um arquivo de definição de tabela SQLX.

  4. No bloco config, insira o seguinte snippet de código:

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

    Substitua:

    • OVERRIDDEN_SCHEMA: o conjunto de dados do BigQuery em que você quer criar a tabela.

    • OVERRIDDEN_DATABASE: o ID do projeto do BigQuery em que você quer criar a tabela.

    • OVERRIDDEN_NAME: o nome da tabela, que é diferente do nome do arquivo de definição de tabela SQLX.

  5. Opcional: clique em Formatar.

Para mais informações, consulte Referenciar uma tabela com um nome de tabela substituído.

Criar tabelas gerenciadas do Apache Iceberg

Use o Dataform para criar tabelas do BigQuery no formato de tabela do Iceberg. Essas tabelas são conhecidas como tabelas gerenciadas do Iceberg. Para mais informações sobre os papéis necessários e outras tarefas de configuração, consulte as etapas Antes de começar para fluxos de trabalho de tabelas gerenciadas do Iceberg.

Criar a definição da tabela do Iceberg

Para criar as tabelas, defina a configuração do Iceberg no bloco bigquery de um arquivo de definição de tabela.

Para definir as tabelas, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Abra um arquivo SQLX de definição de tabela.

  4. No bloco config, adicione um bloco bigquery que contenha um bloco iceberg no seguinte formato:

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

    Substitua BUCKET_NAME pelo nome do bucket do Cloud Storage em que os dados da tabela estão armazenados.

    Outras propriedades são opcionais e têm valores padrão se não forem especificadas. Para mais informações, consulte as propriedades de iceberg.

    É possível fornecer os valores para cada tabela ou definir padrões no nível do fluxo de trabalho em workflow_settings.yaml. É possível modificar os padrões no nível do fluxo de trabalho definindo explicitamente as propriedades no bloco iceberg.

Criar partições e clusters de tabela

Nesta seção, mostramos como usar o Dataform Core para criar partições e clusters de tabela. O BigQuery é compatível com tabelas particionadas e clustering de tabelas. Para mais informações, consulte Introdução às tabelas particionadas e Como criar e usar tabelas em cluster.

Criar uma partição de tabela

Para criar uma partição de tabela, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Abra um arquivo SQLX de definição de tabela.

  4. No bloco config, adicione o bloco bigquery abaixo da declaração do tipo de tabela no seguinte formato:

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

  5. No bloco bigquery, insira o seguinte snippet de código:

        partitionBy: "PARTITION_EXPRESSION"

    Substitua PARTITION_EXPRESSION por uma expressão para particionar a tabela.

  6. Opcional: clique em Formatar.

O exemplo de código a seguir mostra o particionamento de uma tabela por hora em um arquivo SQLX de definição de tabela:

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

O exemplo de código a seguir mostra o particionamento de uma tabela por um valor inteiro em um arquivo SQLX de definição de tabela:

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

Definir um filtro de partição

Para definir um filtro de partição, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Abra um arquivo SQLX de definição de tabela particionada.

  4. No bloco bigquery, insira o seguinte snippet de código:

    requirePartitionFilter : true

  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra um conjunto de filtros de partição no bloco bigquery de um arquivo SQLX de tabela particionada:

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

Para mais informações sobre o filtro de partição no BigQuery, consulte Como definir o atributo de filtro de partição obrigatório em uma tabela particionada.

Definir um período de armazenamento para partições

Para controlar a retenção de todas as partições em uma tabela particionada, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Abra um arquivo SQLX de definição de tabela particionada.

  4. No bloco bigquery, insira o seguinte snippet de código:

    partitionExpirationDays: NUMBER_OF_DAYS

    Substitua NUMBER_OF_DAYS pelo número de dias que você quer reter as partições.

  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra um período de armazenamento para partições definido como 14 dias no bloco bigquery de um arquivo SQLX de tabela particionada:

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

Criar um cluster de tabelas

Para criar um cluster de tabela, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Abra um arquivo SQLX de definição de tabela.

  4. No bloco bigquery, insira o seguinte snippet de código:

        clusterBy: ["CLUSTER_COLUMN"]

    Substitua CLUSTER_COLUMN pelo nome da coluna que você quer usar para agrupar a tabela. Para mais informações, consulte clustering_column_list.

  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma tabela particionada agrupada em cluster pelas colunas name e revenue:

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

Configurar uma tabela incremental

Nesta seção, mostramos como usar o Dataform Core para configurar uma tabela incremental.

Sobre tabelas incrementais

O Dataform atualiza as tabelas de maneira diferente com base no tipo de tabela. Durante cada execução de uma tabela ou visualização, o Dataform recria toda a tabela ou visualização do zero.

Quando você define uma tabela incremental, o Dataform a cria do zero apenas na primeira vez. Durante as execuções subsequentes, o Dataform só insere ou mescla novas linhas na tabela incremental de acordo com as condições configuradas.

O Dataform insere novas linhas apenas em colunas que já existem na tabela incremental. Se você fizer mudanças na consulta de definição da tabela incremental, por exemplo, adicionando uma nova coluna, será necessário decidir se vai recriar a tabela do zero. Para recriar a tabela, na próxima vez que você acionar uma execução de tabela, selecione a opção Executar com atualização completa. Para outras opções, consulte Mudar um esquema de tabela incremental sem uma atualização completa.

Veja alguns casos de uso comuns para tabelas incrementais:

Otimização de desempenho
Para alguns tipos de dados, como registros da Web ou dados de análise, talvez você queira processar apenas novos registros em vez de reprocessar toda a tabela.
Redução de latência
É possível usar tabelas incrementais para executar fluxos de trabalho de maneira rápida, mas frequente, reduzindo a latência downstream das tabelas de saída.
Snapshots diários
É possível configurar uma tabela incremental para criar snapshots diários dos dados da tabela, por exemplo, para análise longitudinal das configurações do usuário armazenadas em um banco de dados de produção.

Processar um subconjunto de linhas em uma tabela incremental

Para determinar um subconjunto de linhas para o Dataform processar durante cada execução, adicione uma cláusula WHERE condicional ao arquivo de definição SQLX da tabela incremental. Na cláusula WHERE, é possível especificar uma condição incremental e uma não incremental. O Dataform aplica a condição incremental durante a execução da tabela sem uma atualização completa e a condição não incremental durante a execução com uma atualização completa.

Para configurar uma tabela incremental, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Abra um arquivo SQLX de definição de tabela incremental.

  4. Insira uma cláusula WHERE no seguinte formato:

    config { type: "incremental" }

SELECT_STATEMENT

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

    Substitua:

    • SELECT_STATEMENT: a instrução SELECT que define a tabela.
    • INCREMENTAL_CONDITION: a condição que você especifica na cláusula WHERE para selecionar linhas para o Dataform processar durante a execução da tabela sem uma atualização completa.
    • NON_INCREMENTAL_CONDITION: a condição que você especifica na cláusula WHERE para selecionar linhas que o Dataform vai processar durante a execução da tabela com uma atualização completa.

  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma tabela incremental que processa de forma incremental as linhas da tabela 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"`)}

O exemplo de código a seguir mostra uma tabela incremental que cria um snapshot da tabela 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()})`) }

Mesclar linhas em uma tabela incremental

Para verificar se uma tabela incremental contém apenas uma linha correspondente a uma combinação selecionada de colunas, defina as colunas selecionadas como uniqueKey para mesclar linhas que tenham o mesmo valor de uniqueKey. Ao atualizar a tabela, o Dataform mescla as linhas com o mesmo valor uniqueKey em vez de anexá-las.

Para configurar a mesclagem em uma tabela incremental, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Selecione um arquivo SQLX de definição de tabela incremental

  4. No bloco config, defina as colunas selecionadas como uniqueKey no seguinte formato:

    uniqueKey: ["COLUMN_NAME"]

    Substitua COLUMN_NAME pelo nome de uma coluna selecionada.

  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma tabela incremental com a coluna transaction_id definida como uniqueKey para verificar se ela sempre contém uma linha:

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

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

Filtrar linhas em uma tabela incremental

Em uma tabela particionada incremental, para evitar que o Dataform verifique toda a tabela para encontrar linhas correspondentes, defina updatePartitionFilter para considerar apenas um subconjunto de registros.

O exemplo de código a seguir mostra uma tabela particionada incremental com mesclagem configurada pelas propriedades updatePartitionFilter e uniqueKey, usando dois tipos distintos de filtragem:

  • Remoção de destino usando a propriedade updatePartitionFilter: limita as partições verificadas na tabela de destino durante uma operação MERGE para encontrar linhas que correspondam a uniqueKey.
  • Remoção de origem usando uma cláusula WHERE: limita quais linhas são buscadas na tabela de origem durante uma execução incremental. Para isso, use a função ${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()})`) }

Evitar verificações completas de tabelas ao fazer a ingestão de uma tabela particionada

Ao criar uma tabela incremental que faz referência a uma tabela particionada, recomendamos que você crie a consulta de tabela para evitar verificações completas da tabela particionada durante cada atualização incremental.

É possível limitar o número de partições que o BigQuery verifica para atualizar a tabela incremental usando uma expressão constante na consulta da tabela. Para transformar um valor da tabela particionada em uma expressão constante, use o script do BigQuery para declarar o valor como uma variável no bloco pre_operations. Em seguida, use a variável como uma expressão constante em uma cláusula WHERE na consulta SELECT.

Com essa configuração, o Dataform atualiza a tabela incremental com base nas partições mais recentes da tabela particionada referenciada, sem verificar a tabela inteira.

Para configurar uma tabela incremental que faça referência a uma tabela particionada e evite verificações completas de tabela, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Selecione um arquivo SQLX de definição de tabela incremental
  4. No bloco pre_operations, declare uma variável com scripts do BigQuery.
  5. Filtre a instrução SELECT que define a tabela com uma cláusula WHERE que faz referência à variável declarada.
  6. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma tabela incremental em que a tabela raw_events referenciada é particionada por 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

No exemplo de código anterior, a variável event_timestamp_checkpoint é definida no bloco pre_operations. A variável event_timestamp_checkpoint é usada como uma expressão constante na cláusula WHERE.

Recriar uma tabela incremental do zero com atualização completa

É possível forçar a recriação de uma tabela incremental do zero usando a interface de linha de comando com a opção --full-refresh ou a opção Executar com atualização completa ao acionar uma execução de fluxo de trabalho.

Quando você seleciona a opção de atualização completa, no espaço de trabalho de desenvolvimento ou usando a CLI do Dataform, o Dataform ignora o parâmetro ${when(incremental(), ... } durante a execução e recria a tabela com uma instrução CREATE OR REPLACE.

Proteger uma tabela incremental contra atualização completa

Para proteger uma tabela incremental contra recriação do zero e possível perda de dados, defina a tabela incremental como protected. Talvez você queira evitar que uma tabela incremental seja recriada se a fonte de dados for temporária.

Para marcar uma tabela incremental como protected, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Selecione um arquivo SQLX de definição de tabela incremental.
  4. No bloco config, digite protected: true.
  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma tabela incremental marcada como protected:

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

Mudar um esquema de tabela incremental sem uma atualização completa

É possível modificar um esquema de tabela incremental na consulta SELECT sem uma atualização completa da tabela usando a propriedade de configuração onSchemaChange. Essa propriedade permite adicionar novas colunas ou remover colunas de uma tabela, preservando os dados históricos. Essa abordagem ajuda a evitar a perda de dados e as atualizações manuais no BigQuery.

Para usar esse recurso, instale o Dataform Core 3.0.11 ou mais recente.

Depois de definir a propriedade onSchemaChange, o Dataform verifica a consulta SELECT para determinar se as colunas foram adicionadas ou removidas. Em seguida, o Dataform executa a ação especificada pelo valor da propriedade onSchemaChange.

Para definir a propriedade onSchemaChange, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Selecione um arquivo SQLX de definição de tabela incremental.

  4. No bloco config, insira o seguinte snippet de código:

    onSchemaChange: "ON_CHANGE_ACTION"

    Substitua ON_CHANGE_ACTION por uma das seguintes ações:

    • IGNORE (padrão): ignora as colunas adicionadas e mostra um erro para as colunas ausentes. Se onSchemaChange não estiver definido, esse será o comportamento padrão quando o esquema mudar.
    • FAIL: interrompe a ação se o Dataform detectar qualquer alteração de esquema, o que ajuda a manter o esquema consistente.
    • EXTEND: adiciona novas colunas da consulta à tabela incremental e valores NULL para registros anteriores. Mostra um erro se uma coluna for removida ou estiver ausente do esquema original na consulta. Use essa configuração para adicionar novas colunas às tabelas incrementais durante o ambiente de execução.

    • SYNCHRONIZE: adiciona novas colunas da consulta à tabela incremental e adiciona valores NULL para registros anteriores. Remove as colunas que estavam no esquema original, mas que agora estão ausentes da consulta atual.

  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma tabela incremental com a ação EXTEND definida para a propriedade onSchemaChange:

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

Adicionar documentação da tabela

Nesta seção, mostramos como adicionar descrições de uma tabela e das colunas e registros dela a um arquivo SQLX do Dataform Core.

É possível adicionar descrições de tabelas, colunas e registros a todos os tipos de tabela no Dataform: tabelas, tabelas incrementais e visualizações.

É recomendável documentar o seguinte:

  • A finalidade da tabela.
  • O conteúdo ou a função das colunas ou dos registros na tabela.
  • A relação da tabela e outras ações do fluxo de trabalho, por exemplo, as tabelas ou visualizações que dependem da tabela atual.
  • As declarações aplicadas à tabela.
  • As pré-operações ou pós-operações aplicadas à tabela.
  • O proprietário da tabela, ou seja, o usuário que a criou. Essas informações podem ser úteis se vários membros da equipe trabalharem em um fluxo de trabalho.

Adicionar metadados para o Knowledge Catalog

Os metadados padrão do BigQuery, como conjuntos de dados, tabelas e visualizações, ficam disponíveis automaticamente no Knowledge Catalog.

Também é possível definir metadados personalizados para as tabelas e visualizações diretamente no bloco de configuração dos arquivos .sqlx. Após a conclusão de uma ação, o Dataform inicia automaticamente uma sincronização de metadados com o Knowledge Catalog. Esse processo de enriquecimento atualiza o Knowledge Catalog com os metadados semânticos definidos na configuração do SQLX.

Use a chave de metadados para especificar informações para o Knowledge Catalog. O processo de enriquecimento é compatível com as seguintes construções de metadados:

  • Visão geral: documentação e texto de resumo da entrada. Requer a versão 3.0.37 ou posterior do Dataform Core.
  • Aspectos genéricos: detalhes semânticos, como informações de tipo e sistema de tabelas. Requer o Dataform Core versão 3.0.52 ou mais recente.

O exemplo de configuração a seguir mostra como adicionar uma visão geral e aspectos de metadados genéricos a uma configuração de tabela para o Knowledge Catalog:

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

Para verificar o status de uma atualização de metadados, consulte Inspecionar registros de execução do espaço de trabalho para fluxos de trabalho do Dataform ou Ver execuções manuais anteriores para pipelines do BigQuery.

Para verificar os metadados sincronizados, pesquise o recurso no Knowledge Catalog. Para mais informações, consulte Pesquisar recursos.

Adicionar uma descrição de tabela

Para adicionar uma descrição a uma tabela em um arquivo SQLX, siga estas etapas:

  1. No console do Google Cloud , acesse a página Dataform.

    Acesse o Dataform

  2. Selecione um repositório.

  3. Selecione um espaço de trabalho de desenvolvimento.

  4. No painel Arquivos, clique no arquivo SQLX de definição de tabela que você quer editar.

  5. No bloco config do arquivo, insira a descrição da tabela no seguinte formato:

    description: "Description of the table",

  6. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma descrição de tabela adicionada ao bloco config de um arquivo de definição de tabela SQLX:

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

Adicionar descrições de colunas e registros

Para adicionar descrições de colunas e registros individuais a um arquivo SQLX, siga estas etapas:

  1. No bloco config do arquivo de definição da tabela, insira columns: {}.

  2. Em columns: {}, insira as descrições das colunas no seguinte formato:

    column_name: "Description of the column",

  3. Em columns: {}, insira as descrições de registro no seguinte formato:

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

  4. Opcional: clique em Formatar.

O exemplo de código a seguir mostra descrições de tabelas, colunas e registros no bloco config de um arquivo de definição de tabela 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

Reutilizar a documentação da coluna com inclusões

É possível reutilizar descrições de colunas no Dataform em todo o fluxo de trabalho SQL com inclusões de JavaScript. Recomendamos reutilizar a documentação da coluna se você tiver várias colunas com o mesmo nome e descrição no fluxo de trabalho do SQL.

É possível definir uma constante com uma descrição de uma única coluna ou uma constante com um conjunto de descrições de coluna para reutilizar as descrições de todas as colunas em uma tabela. Para mais informações sobre como criar e usar inclusões no Dataform, consulte Reutilizar código em um único repositório com inclusões.

O exemplo de código a seguir mostra várias constantes com descrições de colunas individuais definidas no arquivo 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,
};

O exemplo de código a seguir mostra as constantes user_id e age, definidas em includes/docs.js, usadas no arquivo de definição de tabela SQLX definitions/my_table.sqlx para gerar documentação para colunas selecionadas na tabela:

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

O exemplo de código a seguir mostra uma constante com um conjunto de descrições de coluna definidas no arquivo 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
};

O exemplo de código a seguir mostra a constante columns, que é definida em includes/table_docs.js e usada no arquivo de definição de tabela SQLX definitions/my_table.sqlx para gerar documentação para todas as colunas na tabela:

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

SELECT 1 AS one

Adicionar rótulos do BigQuery

Nesta seção, mostramos como adicionar rótulos a tabelas no Dataform.

O BigQuery permite adicionar rótulos aos recursos. Para mais informações sobre rótulos no BigQuery, consulte Introdução aos rótulos.

Para adicionar um rótulo do BigQuery a uma tabela no Dataform, adicione o rótulo ao bloco bigquery no bloco config do arquivo SQLX de definição da tabela.

Para adicionar um rótulo do BigQuery a um arquivo de definição de tabela, siga estas etapas:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Selecione um arquivo de definição de tabela SQLX.

  4. No bloco config, adicione um rótulo no seguinte formato:

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

    Substitua:

    • LABEL1: o nome do rótulo.
    • VALUE_OF_LABEL1: o valor do rótulo.

  5. Opcional: para adicionar um rótulo com um nome que contenha caracteres especiais, insira o nome do rótulo entre aspas ("").

  6. Opcional: clique em Formatar.

O exemplo de código a seguir mostra os rótulos department:shipping e cost-center:logistics adicionados ao bloco bigquery em um arquivo SQLX de definição de tabela particionada:

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

SELECT CURRENT_TIMESTAMP() AS ts

Preservar metadados de controles de acesso

Quando o Dataform executa ações de fluxo de trabalho que recriam objetos, como tabelas padrão, visualizações ou tabelas incrementais durante uma atualização completa, ele normalmente gera instruções CREATE OR REPLACE no BigQuery para atualizar o esquema e os dados do objeto. Por padrão, no BigQuery, as operações de substituição de tabela descartam automaticamente os metadados de controle de acesso no nível da coluna, como políticas de dados e tags de governança de dados (pré-lançamento) aplicadas por sistemas externos de governança de dados.

É possível configurar o Dataform para preservar automaticamente as políticas de dados do BigQuery, incluindo as políticas de dados sem tags, e as tags de governança de dados em todas as execuções de fluxo de trabalho. Para isso, ative a opção preserveGovernanceControls. Quando essa opção está ativada, o Dataform executa a seguinte sequência durante a execução:

  1. Faz backup dos metadados de coluna atuais da visualização INFORMATION_SCHEMA.COLUMNS.
  2. Cria uma tabela de preparo vazia intermediária.
  3. Reaplica as políticas de segurança à tabela intermediária.
  4. Preenche a tabela com dados.
  5. Realiza uma troca de tabela atômica para substituir a tabela original.

Ativar a preservação de governança automatizada para uma tabela

Para ativar a preservação automatizada de políticas de dados e tags de governança de dados para uma tabela individual, incremental ou visualização, defina o parâmetro preserveGovernanceControls como true no bloco config do arquivo de definição SQLX do objeto.

Para configurar a preservação de governança para uma tabela individual, faça o seguinte:

  1. Acesse o espaço de trabalho de desenvolvimento.
  2. No painel Arquivos, abra definitions/.
  3. Selecione um arquivo de definição de tabela SQLX.
  4. No bloco config, digite preserveGovernanceControls: true.
  5. Opcional: clique em Formatar.

O exemplo de código a seguir mostra uma definição de tabela com a preservação de governança ativada:

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

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

Ativar a preservação automatizada da governança para todas as tabelas

É possível ativar a preservação de governança automatizada como comportamento padrão em todo o projeto para todas as tabelas, tabelas incrementais e visualizações no repositório. Para isso, defina o parâmetro preserveGovernanceControls como true no arquivo workflow_settings.yaml do repositório.

Para definir o padrão para todo o projeto, adicione o parâmetro preserveGovernanceControls ao arquivo workflow_settings.yaml, conforme mostrado no exemplo a seguir:

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

Se você ativar a preservação de governança globalmente no arquivo workflow_settings.yaml, poderá modificar esse comportamento e desativar a preservação para uma tabela específica definindo o parâmetro preserveGovernanceControls como false no bloco config dessa tabela individual.

Reaplicar políticas de acesso de linha usando pós-operações

Embora a ativação da preservação de governança proteja automaticamente os metadados no nível da coluna, como políticas de dados do BigQuery e tags de governança de dados, ela não restaura automaticamente as políticas de acesso de linha. Como a substituição de objetos descarta as regras de segurança no nível da linha, é possível reaplicar as políticas de acesso à linha após a criação da tabela definindo instruções SQL DDL no bloco post_operations do arquivo de definição de tabela SQLX.

O exemplo de código a seguir mostra como reaplicar uma política de acesso por linha no bloco post_operations de uma tabela:

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-%');
}

Limitações

Ao usar o recurso de preservação de governança, considere as seguintes limitações:

  • Remoções de colunas de esquema. Se uma coluna for removida da definição da tabela SQLX, o Dataform vai ignorar as políticas de dados e as tags de backup associadas à coluna excluída durante a fase de restauração.
  • Renomeações de colunas de esquema. Se você renomear uma coluna na definição da tabela SQLX, o Dataform não poderá mapear as associações de tag ou política antigas para o novo nome da coluna. Como resultado, as tags e políticas atuais nessas colunas são perdidas ou trocadas, e você precisa reaplicar manualmente as tags e políticas às colunas renomeadas.
  • Mudanças de particionamento e clustering. Não é possível alterar o particionamento ou o clustering de uma tabela que usa preserveGovernanceControls. O BigQuery rejeita a substituição. Para resolver isso, remova a tabela e execute o fluxo de trabalho novamente. É preciso reaplicar manualmente as políticas de dados no nível da coluna e as tags de governança de dados à tabela recriada.
  • Remoções de expiração de partição. Remover a propriedade partitionExpirationDays da configuração não remove a validade da partição de uma tabela atual. Para remover a validade da partição de uma tabela, defina a opção explicitamente como o valor selecionado ou altere a tabela manualmente.
  • Tipos de tabela. Após uma execução bem-sucedida, a tabela é informada como um clone de tabela (table_type = 'CLONE' em INFORMATION_SCHEMA.TABLES). Ela se comporta como uma tabela padrão, e a tabela base mostrada nos metadados do clone não existe mais.
  • Metadados no nível da tabela e políticas de acesso de linha. As tags no nível da tabela e as políticas de acesso no nível da linha não são preservadas entre as execuções. É possível reaplicar políticas de acesso a linhas após a criação da tabela definindo instruções SQL DDL dentro do bloco post_operations do arquivo de definição de tabela SQLX.
  • Trocas de tabelas não transacionais. As operações de DDL do BigQuery não são transacionais. Se uma execução de fluxo de trabalho falhar no meio da sequência de renomeação atômica, talvez seja necessária uma intervenção manual se a tabela de destino for deixada em um estado intermediário. Por exemplo, se a tabela for renomeada permanentemente como TABLE_NAME_old ou deixada como df_tmp_intermediate.
  • Tags de política. A preservação de governança automatizada não é compatível com tags de política do BigQuery. Em vez disso, adicione tags de governança de dados do BigQuery (pré-lançamento) e políticas de dados às suas colunas para usar a integração SQL integrada.

Considerações sobre custos de armazenamento

Quando preserveGovernanceControls está ativado, o Dataform recria a tabela usando uma tabela de preparo temporária em cada execução (ou em cada atualização completa para tabelas incrementais). Como em qualquer recriação de tabela no BigQuery, a versão anterior dos dados da tabela é mantida na viagem no tempo para a janela configurada do conjunto de dados, que é de sete dias por padrão, seguida por um período de segurança contra falhas de sete dias.

O faturamento de armazenamento lógico é o modelo de faturamento padrão no BigQuery, enquanto o faturamento de armazenamento físico é uma configuração opcional definida por conjunto de dados. Se o conjunto de dados usar o faturamento de armazenamento físico, esses dados retidos de viagem no tempo e à prova de falhas serão faturados além da tabela ativa. Como resultado, tabelas grandes que são recriadas com frequência podem aumentar significativamente os custos de armazenamento.

Para reduzir esse custo, faça o seguinte:

A seguir