Neste documento, mostramos exemplos de scripts principais e JavaScript do Dataform que que podem ser usadas para criar um fluxo de trabalho SQL no Dataform.
Como criar tabelas
Como criar uma visualização com o Dataform Core
O exemplo de código a seguir 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 Dataform Core
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 Dataform Core
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 Dataform Core
O exemplo de código a seguir mostra uma tabela incremental que
processa de maneira 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 referenciar tabelas com o Dataform Core
O exemplo de código a seguir mostra a função ref
usada para referenciar os
Tabela source_data
em definitions/new_table_with_ref.sqlx
do arquivo de definição da tabela:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Adicionar documentação a uma tabela, visualização ou declaração com o Dataform Core
O exemplo de código a seguir 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
Adição de novas linhas da tabela para novas datas nos dados de origem com o Dataform Core
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 a 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 capturar um snapshot de uma tabela periodicamente com o núcleo do Dataform
O exemplo de código a seguir mostra a configuração de uma tabela incremental na
definitions/snapshots_table.sqlx
. Nessa configuração,
O Dataform cria snapshots_table
com um snapshot dos
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()})`) }
Como criar uma tabela contínua de 30 dias atualizada de forma incremental com o Dataform Core
O exemplo de código a seguir mostra a configuração de uma tabela incremental na
definitions/incremental_example.sqlx
. Nessa configuração,
O Dataform cria um incremental_example
temporário que é atualizado
de forma incremental e exclui a tabela 30 dias após 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()})`) }
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 a seguir mostra ;
usado para separar várias operações SQL
definido 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 definitions/table_with_preops.sqlx
do arquivo de definição da tabela:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Como executar o SQL personalizado depois 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 post_operations
do definitions/table_with_postops.sqlx
do arquivo de definição da tabela:
config {type: "table"}
SELECT * FROM ...
post_operations {
GRANT `roles/bigquery.dataViewer`
ON
TABLE ${self()}
TO "group:allusers@example.com", "user:otheruser@example.com"
}
Como validar tabelas
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 uniqueKey
, nonNull
,
e declarações rowConditions
adicionadas ao definitions/tested_table.sqlx
do arquivo de definição da tabela:
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 Dataform Core
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 in-line com JavaScript
O exemplo de código abaixo mostra a variável foo
definida em um bloco js
.
e, em seguida, usada inline em um arquivo SQLX:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Como gerar uma tabela por país com JavaScript
O exemplo de código a seguir mostra o uso da função forEach
para gerar
uma tabela para 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 fontes em um arquivo com JavaScript
O exemplo de código a seguir 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 fontes em um arquivo usando forEach
O exemplo de código a seguir mostra a declaração de várias fontes de dados com o
Função forEach
no arquivo definitions/external_dependencies.js
:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Excluir informações sensíveis de todas as tabelas que contêm PII com JavaScript.
O exemplo de código a seguir mostra uma função no definitions/delete_pii.js
.
arquivo 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"]))
);
Adicionar preOps
e postOps
com JavaScript
O exemplo de código a seguir 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 a seguir mostra a função publish
usada para criar um
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()}`)}
`)
Como 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 inclusões
Usar variáveis globais com JavaScript
O exemplo de código a seguir mostra a definição de project_id
e first_date
.
constantes 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 a seguir 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ís com JavaScript
O exemplo de código a seguir mostra a função personalizada country_group
definida em
o 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 a seguir mostra uma definição de tabela que usa a
Função country_group
no definitions/new_table.sqlx
do arquivo de definição da tabela:
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
compilado 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
.
definido 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 definitions/new_table.sqlx
do arquivo de definição da tabela:
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
compilado 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 configurações de ação válidas consulte a página de configurações do Dataform Referência.
O exemplo de código a seguir 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 código anterior
, contém SQL puro:
SELECT * FROM source_data