Scripts de amostra do Dataform Core

Este documento mostra exemplos de scripts JavaScript e Dataform core que pode usar para criar um fluxo de trabalho no Dataform.

Criar tabelas

Criar uma vista com o Dataform core

O seguinte exemplo de código mostra a definição de uma vista denominada new_view no ficheiro definitions/new_view.sqlx:

config { type: "view" }

SELECT * FROM source_data

Criar uma vista materializada com o Dataform Core

O seguinte exemplo de código mostra a definição de uma vista materializada denominada new_materialized_view no ficheiro definitions/new_materialized_view.sqlx:

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

SELECT * FROM source_data

Criar uma tabela com o Dataform Core

O seguinte exemplo de código mostra a definição de uma tabela denominada new_table no ficheiro definitions/new_table.sqlx:

config { type: "table" }

SELECT * FROM source_data

Criar uma tabela incremental com o Dataform core

O seguinte exemplo de código mostra uma tabela incremental que processa incrementalmente 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()})`) }

Usar a função ref para fazer referência a tabelas com o Dataform Core

O exemplo de código seguinte mostra a função ref usada para fazer referência à tabela source_data no ficheiro de definição da tabela definitions/new_table_with_ref.sqlx:

config { type: "table" }

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

Adicionar documentação a uma tabela, uma vista ou uma declaração com o Dataform core

O seguinte exemplo de código mostra as descrições das tabelas e colunas no ficheiro de definição da 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")}

Configurar tabelas incrementais

Adicionar novas linhas de tabelas para novas datas nos dados de origem com o Dataform core

O seguinte exemplo de código mostra uma configuração de uma tabela incremental no ficheiro definitions/incremental_table.sqlx. Nesta configuração, a app Dataform anexa uma nova linha à tabela 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()})`)

Tirar uma captura de uma tabela periodicamente com o Dataform core

O seguinte exemplo de código mostra uma configuração de uma tabela incremental no ficheiro definitions/snapshots_table.sqlx. Nesta configuração, o Dataform cria snapshots_table com um instantâneo 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 móvel de 30 dias que é atualizada incrementalmente com o Dataform core

O seguinte exemplo de código mostra uma configuração de uma tabela incremental no ficheiro definitions/incremental_example.sqlx. Nesta configuração, o Dataform cria uma incremental_example temporária que é atualizada incrementalmente e elimina a tabela 30 dias após a sua 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()})`) }

Criar operações SQL personalizadas

Executar várias operações SQL num ficheiro SQLX com o Dataform Core

O exemplo de código seguinte 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';

Executar SQL personalizado antes de criar uma tabela com o Dataform core

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

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Executar SQL personalizado após a criação de uma tabela com o Dataform core

O exemplo de código seguinte mostra uma operação SQL personalizada definida no bloco post_operations do ficheiro de definição da 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"
}

Validar tabelas

Adicionar afirmações a uma tabela, uma vista ou uma declaração com o Dataform core

O seguinte exemplo de código mostra as asserções uniqueKey, nonNull e rowConditions adicionadas ao ficheiro de definição da 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 ...

Adicionar uma afirmação personalizada com o Dataform core

O exemplo de código seguinte mostra uma afirmação personalizada num ficheiro 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

Desenvolver com JavaScript

Usar variáveis e funções inline com JavaScript

O exemplo de código seguinte mostra a variável foo definida num bloco js e, em seguida, usada inline num ficheiro 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 seguinte mostra a utilização da função forEach para gerar uma tabela por cada país definido em countries no ficheiro 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
      `
    );
});

Declarar várias origens num ficheiro com JavaScript

O seguinte exemplo de código mostra a declaração de várias origens de dados no ficheiro definitions/external_dependencies.js:

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

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

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

Declarar várias origens num ficheiro através de forEach

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

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

Eliminar informações confidenciais em todas as tabelas que contenham PII com JavaScript

O exemplo de código seguinte mostra uma função no ficheiro definitions/delete_pii.js que elimina 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"]))
);

Adicionar preOps e postOps com JavaScript

O exemplo de código seguinte 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"`)

Criar tabelas incrementais com JavaScript

O exemplo de código seguinte mostra a função publish usada para criar uma tabela incremental no ficheiro 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 seguinte exemplo de código mostra o preenchimento alternativo de uma tabela que é atualizada diariamente no ficheiro 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}'`)
);

Reutilizar código com includes

Usar variáveis globais com JavaScript

O seguinte exemplo de código 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 seguinte exemplo de código mostra a constante first_date referenciada no ficheiro 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 seguinte exemplo de código mostra a função personalizada country_group definida no ficheiro 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 seguinte mostra uma definição de tabela que usa a função country_group no ficheiro 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 seguinte exemplo de código 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

Gerar um script SQL com uma função JavaScript personalizada

O seguinte exemplo de código 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 seguinte mostra uma definição de tabela que usa a função render_script no ficheiro 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 seguinte exemplo de código 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

Carregar ficheiros SQL com configurações de ações

As configurações de ações facilitam o carregamento de ficheiros SQL puros. Pode definir configurações de ações em ficheiros actions.yaml na pasta definitions.

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

O seguinte exemplo de código mostra a definição de uma vista denominada new_view no ficheiro definitions/actions.yaml:

actions:
  - view:
    filename: new_view.sql

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

SELECT * FROM source_data