Exemplos de scripts principais do Dataform

Este documento mostra exemplos de scripts do núcleo do Dataform e JavaScript que podem ser usados para criar um fluxo de trabalho SQL no Dataform.

Como criar tabelas

Criar uma visualização com o núcleo do Dataform

O exemplo de código abaixo mostra a definição de uma visualização chamada new_view no arquivo definitions/new_view.sqlx:

config { type: "view" }

SELECT * FROM source_data

Como criar uma visualização materializada com o núcleo do Dataform

O exemplo de código a seguir mostra a definição de uma visualização materializada chamada new_materialized_view no arquivo definitions/new_materialized_view.sqlx:

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

SELECT * FROM source_data

Como criar uma tabela com o núcleo do Dataform

O exemplo de código a seguir mostra a definição de uma tabela chamada new_table no arquivo definitions/new_table.sqlx:

config { type: "table" }

SELECT * FROM source_data

Como criar uma tabela incremental com o núcleo do Dataform

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 timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Como usar a função ref para fazer referência a tabelas com o núcleo do Dataform

O exemplo de código abaixo mostra a função ref usada para referenciar a tabela source_data no arquivo de definição de tabela definitions/new_table_with_ref.sqlx:

config { type: "table" }

SELECT * FROM ${ref("source_data")}

Como adicionar documentação a uma tabela, visualização ou declaração com o núcleo do Dataform

O exemplo de código abaixo mostra descrições de tabelas e colunas no arquivo de definição de tabela definitions/documented_table.sqlx:

config { type: "table",
         description: "This table is an example",
         columns:{
             user_name: "Name of the user",
             user_id: "ID of the user"
      }
  }

SELECT user_name, user_id FROM ${ref("source_data")}

Como configurar tabelas incrementais

Como adicionar novas linhas de tabela para novas datas nos dados de origem com o núcleo do Dataform

O exemplo de código a seguir mostra uma configuração de uma tabela incremental no arquivo definitions/incremental_table.sqlx. Nessa configuração, o Dataform anexa uma nova linha ao incremental_table para cada nova data:

config { type: "incremental" }

SELECT date(timestamp) AS date, action
FROM weblogs.user_actions

${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)

Como fazer um snapshot de uma tabela periodicamente com o núcleo do Dataform

O exemplo de código a seguir mostra uma configuração de uma tabela incremental no arquivo definitions/snapshots_table.sqlx. Nessa configuração, o Dataform cria snapshots_table com um snapshot de productiondb.customers na data especificada:

config { type: "incremental" }

SELECT current_date() AS snapshot_date, customer_id, name, account_settings FROM productiondb.customers

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

Criar uma tabela de 30 dias que é atualizada de forma incremental com o núcleo do Dataform

O exemplo de código abaixo mostra uma configuração de uma tabela incremental no arquivo definitions/incremental_example.sqlx. Nessa configuração, o Dataform cria uma incremental_example temporária que é atualizada de forma incremental e exclui a tabela após 30 dias da criação:

config {type: "incremental"}

post_operations {
  delete FROM ${self()} WHERE date < (date_add(Day, -30, CURRENT_DATE))
}

SELECT
 date(timestamp) AS date,
 order_id,
FROM source_table
  ${ when(incremental(), `WHERE timestamp > (SELECT max(date) FROM ${self()})`) }

Como criar operações SQL personalizadas

Como executar várias operações SQL em um arquivo SQLX com o núcleo do Dataform

O exemplo de código abaixo mostra ; usado para separar várias operações SQL definidas em definitions/operations.sqlx:

config { type: "operations" }

DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';

Como executar SQL personalizado antes de criar uma tabela com o núcleo do Dataform

O exemplo de código a seguir mostra uma operação SQL personalizada definida no bloco pre_operations do arquivo de definição de tabela definitions/table_with_preops.sqlx:

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Como executar SQL personalizado após a criação de uma tabela com o núcleo do Dataform

O exemplo de código a seguir mostra uma operação SQL personalizada definida no bloco post_operations do arquivo de definição de tabela definitions/table_with_postops.sqlx:

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

Validação de tabelas

Como adicionar declarações a uma tabela, visualização ou declaração com o núcleo do Dataform

O exemplo de código a seguir mostra as declarações uniqueKey, nonNull e rowConditions adicionadas ao arquivo de definição de tabela definitions/tested_table.sqlx:

config {
  type: "table",
  assertions: {
    uniqueKey: ["user_id"],
    nonNull: ["user_id", "customer_id"],
    rowConditions: [
      'signup_date is null or signup_date > "2022-01-01"',
      'email like "%@%.%"'
    ]
  }
}
SELECT ...

Como adicionar uma declaração personalizada com o núcleo do Dataform

O exemplo de código a seguir mostra uma declaração personalizada em um arquivo de definição de tabela que valida se as colunas a, b ou c de source_data são null:

config { type: "assertion" }

SELECT
  *
FROM
  ${ref("source_data")}
WHERE
  a is null
  or b is null
  or c is null

Como desenvolver com JavaScript

Como usar variáveis e funções inline com JavaScript

O exemplo de código abaixo mostra a variável foo definida em um bloco js e usada inline em um arquivo SQLX:

js {
 const foo = 1;
 function bar(number){
     return number+1;
 }
}

SELECT
 ${foo} AS one,
 ${bar(foo)} AS two

Gerar uma tabela por país com JavaScript

O exemplo de código abaixo mostra o uso da função forEach para gerar uma tabela por cada país definido em countries no arquivo definitions/one_table_per_country.js:

const countries = ["GB", "US", "FR", "TH", "NG"];

countries.forEach(country => {
  publish("reporting_" + country)
    .dependencies(["source_table"])
    .query(
      ctx => `
      SELECT '${country}' AS country
      `
    );
});

Como declarar várias origens em um arquivo com JavaScript

O exemplo de código abaixo mostra a declaração de várias fontes de dados no arquivo definitions/external_dependencies.js:

declare({
  schema: "stripe",
  name: "charges"
});

declare({
  schema: "shopify",
  name: "orders"
});

declare({
  schema: "salesforce",
  name: "accounts"
});

Como declarar várias origens em um arquivo usando forEach

O exemplo de código abaixo mostra a declaração de várias fontes de dados com a função forEach no arquivo definitions/external_dependencies.js:

["charges", "subscriptions", "line_items", "invoices"]
  .forEach(source => declare({
      schema: "stripe",
      name: source
    })
  );

Como excluir informações sensíveis em todas as tabelas com PII usando JavaScript

O exemplo de código a seguir mostra uma função no arquivo definitions/delete_pii.js que exclui as informações selecionadas em todas as tabelas que contêm informações de identificação pessoal (PII):

const pii_tables = ["users", "customers", "leads"];
pii_tables.forEach(table =>
  operate(`gdpr_cleanup: ${table}`,
    ctx => `
      DELETE FROM raw_data.${table}
      WHERE user_id in (SELECT * FROM users_who_requested_deletion)`)
      .tags(["gdpr_deletion"]))
);

Como adicionar preOps e postOps com JavaScript

O exemplo de código abaixo mostra a função publish usada para criar uma consulta com preOps e postOps na tabela definitions/pre_and_post_ops_example.js:

publish("example")
  .preOps(ctx => `GRANT \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .postOps(ctx => `REVOKE \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)

Como criar tabelas incrementais com JavaScript

O exemplo de código abaixo mostra a função publish usada para criar uma tabela incremental no arquivo definitions/incremental_example.js:

publish("incremental_example", {
  type: "incremental"
}).query(ctx => `
  SELECT * FROM ${ctx.ref("other_table")}
  ${ctx.when(ctx.incremental(),`WHERE timestamp > (SELECT MAX(date) FROM ${ctx.self()}`)}
`)

Preencher uma tabela diária com JavaScript

O exemplo de código a seguir mostra o preenchimento de uma tabela que é atualizada diariamente no arquivo definitions/backfill_daily_data.js:

var getDateArray = function(start, end) {
  var startDate = new Date(start); //YYYY-MM-DD
  var endDate = new Date(end); //YYYY-MM-DD

  var arr = new Array();
  var dt = new Date(startDate);
  while (dt <= endDate) {
    arr.push(new Date(dt).toISOString().split("T")[0]);
    dt.setDate(dt.getDate() + 1);
  }
  return arr;
};

var dateArr = getDateArray("2020-03-01", "2020-04-01");

// step 1: create table
operate(`create table`, 'create table if not exists backfill_table (`fields`) `);
// step 2: insert into the table

dateArr.forEach((day, i) =>
  operate(`backfill ${day}`
   `insert into backfill_table select fields where day = '${day}'`)
);

Como reutilizar código com includes

Como usar variáveis globais com JavaScript

O exemplo de código a seguir mostra a definição das constantes project_id e first_date no includes/constants.js:

const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
  project_id,
  first_date
};

O exemplo de código abaixo mostra a constante first_date referenciada no arquivo definitions/new_table.sqlx:

config {type: "table"}

SELECT * FROM source_table WHERE date > ${constants.first_date}

Criar um mapeamento de países com JavaScript

O exemplo de código a seguir mostra a função personalizada country_group definida no arquivo includes/mapping.js:

function country_group(country){
  return `
  case
    when ${country} in ('US', 'CA') then 'NA'
    when ${country} in ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') then 'EU'
    when ${country} in ('AU') then ${country}
    else 'Other'
  end`;
}

module.exports = {
   country_group
};

O exemplo de código abaixo mostra uma definição de tabela que usa a função country_group no arquivo de definição de tabela definitions/new_table.sqlx:

config { type: "table"}

SELECT
  country AS country,
  ${mapping.country_group("country")} AS country_group,
  device_type AS device_type,
  sum(revenue) AS revenue,
  sum(pageviews) AS pageviews,
  sum(sessions) AS sessions

FROM ${ref("source_table")}

GROUP BY 1, 2, 3

O exemplo de código a seguir mostra a consulta definida em definitions/new_table.sqlx compilada para SQL:

SELECT
  country AS country,
  case
    when country in ('US', 'CA') then 'NA'
    when country in ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') then 'EU'
    when country in ('AU') then country
    else 'Other'
  end AS country_group,
  device_type AS device_type,
  sum(revenue) AS revenue,
  sum(pageviews) AS pageviews,
  sum(sessions) AS sessions

FROM "dataform"."source_table"

GROUP BY 1, 2, 3

Como gerar um script SQL com uma função JavaScript personalizada

O exemplo de código a seguir mostra a função personalizada render_script definida em includes/script_builder.js:

function render_script(table, dimensions, metrics) {
  return `
      SELECT
      ${dimensions.map(field => `${field} AS ${field}`).join(",")},
      ${metrics.map(field => `sum(${field}) AS ${field}`).join(",\n")}
      FROM ${table}
      GROUP BY ${dimensions.map((field, i) => `${i + 1}`).join(", ")}
    `;
}

module.exports = { render_script };

O exemplo de código a seguir mostra uma definição de tabela que usa a função render_script no arquivo de definição de tabela definitions/new_table.sqlx:

config {
    type: "table",
    tags: ["advanced", "hourly"],
    disabled: true
}

${script_builder.render_script(ref("source_table"),
                               ["country", "device_type"],
                               ["revenue", "pageviews", "sessions"]
                               )}

O exemplo de código a seguir mostra a consulta definida em definitions/new_table.sqlx compilada para SQL:

SELECT
  country AS country,
  device_type AS device_type,
  sum(revenue) AS revenue,
  sum(pageviews) AS pageviews,
  sum(sessions) AS sessions

FROM "dataform"."source_table"

GROUP BY 1, 2

Configurações de ações

Como carregar arquivos SQL com configurações de ação

As configurações de ação facilitam o carregamento de arquivos SQL puros. É possível definir configurações de ação em arquivos actions.yaml na pasta definitions.

Para mais informações sobre os tipos de ação disponíveis e as opções de configuração de ação válidas, consulte a Referência de configurações do Dataform.

O exemplo de código abaixo mostra a definição de uma visualização chamada new_view no arquivo definitions/actions.yaml:

actions:
  - view:
    filename: new_view.sql

O arquivo SQL definitions/new_view.sql, referenciado pelo exemplo de código anterior, contém SQL puro:

SELECT * FROM source_data