En este documento, se muestran ejemplos de secuencias de comandos principales y de JavaScript de Dataform que puedes usar para crear un flujo de trabajo de SQL en Dataform.
Crea tablas
Cómo crear una vista con el núcleo de Dataform
En la siguiente muestra de código, se ve la definición de una vista llamada new_view
en el archivo definitions/new_view.sqlx
:
config { type: "view" }
SELECT * FROM source_data
Crea una vista materializada con el núcleo de Dataform
En la siguiente muestra de código, se ve la definición de una vista materializada llamada new_materialized_view
en el archivo definitions/new_materialized_view.sqlx
:
config {
type: "view",
materialized: true
}
SELECT * FROM source_data
Cómo crear una tabla con el núcleo de Dataform
En la siguiente muestra de código, verás la definición de una tabla llamada new_table
en el archivo definitions/new_table.sqlx
:
config { type: "table" }
SELECT * FROM source_data
Cómo crear una tabla incremental con el núcleo de Dataform
En la siguiente muestra de código, se indica una tabla incremental que procesa de forma incremental las filas de la tabla productiondb.logs
:
config { type: "incremental" }
SELECT timestamp, message FROM ${ref("productiondb", "logs")}
${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }
Cómo usar la función ref
para hacer referencia a tablas con el núcleo de Dataform
En la siguiente muestra de código, se indica la función ref
que se usa para hacer referencia a la tabla source_data
en el archivo de definición de tabla definitions/new_table_with_ref.sqlx
:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Cómo agregar documentación a una tabla, vista o declaración con el núcleo de Dataform
En la siguiente muestra de código, se incluyen descripciones de tablas y columnas en el archivo de definición de tabla 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")}
Configura tablas incrementales
Agrega filas de tabla nuevas para fechas nuevas en los datos de origen con el núcleo de Dataform
En la siguiente muestra de código, se presenta una configuración de una tabla incremental en el archivo definitions/incremental_table.sqlx
. En esta configuración, Dataform agrega una fila nueva a incremental_table
para cada fecha nueva:
config { type: "incremental" }
SELECT date(timestamp) AS date, action
FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)
Toma una instantánea de una tabla periódicamente con el núcleo de Dataform
En la siguiente muestra de código, se presenta una configuración de una tabla incremental en el archivo definitions/snapshots_table.sqlx
. En esta configuración,
Dataform crea snapshots_table
con una instantánea de
productiondb.customers
en la fecha 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()})`) }
Crea una tabla progresiva de 30 días que se actualice de forma incremental con el núcleo de Dataform
En la siguiente muestra de código, se presenta una configuración de una tabla incremental en el archivo definitions/incremental_example.sqlx
. En esta configuración,
Dataform crea un incremental_example
temporal que se actualiza
de forma incremental y borra la tabla 30 días después de su creación:
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()})`) }
Crea operaciones de SQL personalizadas
Ejecuta varias operaciones de SQL en un archivo SQLX con el núcleo de Dataform
En la siguiente muestra de código, se ve ;
que se usa para separar varias operaciones de SQL definidas en definitions/operations.sqlx
:
config { type: "operations" }
DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';
Ejecuta SQL personalizado antes de crear una tabla con el núcleo de Dataform
En la siguiente muestra de código, se presenta una operación de SQL personalizada definida en el bloque pre_operations
del archivo de definición de tabla definitions/table_with_preops.sqlx
:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Ejecuta SQL personalizado después de crear una tabla con el núcleo de Dataform
En la siguiente muestra de código, se presenta una operación de SQL personalizada definida en el bloque post_operations
del archivo de definición de tabla definitions/table_with_postops.sqlx
:
config {type: "table"}
SELECT * FROM ...
post_operations {
GRANT SELECT on ${self()} to group "allusers@examplecompany.com";
GRANT SELECT on ${self()} to group "allotherusers@examplecompany.com";
}
Valida tablas
Cómo agregar aserciones a una tabla, vista o declaración con el núcleo de Dataform
En la siguiente muestra de código, se muestran las aserciones uniqueKey
, nonNull
y rowConditions
que se agregaron al archivo de definición de tabla 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 ...
Agrega una aserción personalizada con el núcleo de Dataform
En la siguiente muestra de código, se muestra una aserción personalizada en un archivo de definición de tablas que valida si las columnas a
, b
o c
de source_data
son null
:
config { type: "assertion" }
SELECT
*
FROM
${ref("source_data")}
WHERE
a is null
or b is null
or c is null
Cómo desarrollar con JavaScript
Cómo usar variables y funciones intercaladas con JavaScript
En la siguiente muestra de código, se incluye la variable foo
definida en un bloque js
y, luego, que se usa intercalada en un archivo SQLX:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Genera una tabla por país con JavaScript
En la siguiente muestra de código, se indica el uso de la función forEach
para generar una tabla por cada país definido en countries
en el archivo 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
`
);
});
Cómo declarar varias fuentes en un archivo con JavaScript
En la siguiente muestra de código, se ve la declaración de varias fuentes de datos en el archivo definitions/external_dependencies.js
:
declare({
schema: "stripe",
name: "charges"
});
declare({
schema: "shopify",
name: "orders"
});
declare({
schema: "salesforce",
name: "accounts"
});
Declara varias fuentes dentro de un archivo mediante forEach
.
En la siguiente muestra de código, se ve la declaración de varias fuentes de datos con la función forEach
en el archivo definitions/external_dependencies.js
:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Borrar la información sensible de todas las tablas que contengan PII con JavaScript
En la siguiente muestra de código, se muestra una función en el archivo definitions/delete_pii.js
que borra la información seleccionada en todas las tablas que contienen información de identificación personal (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"]))
);
Cómo agregar preOps
y postOps
con JavaScript
En la siguiente muestra de código, se indica la función publish
que se usa para crear una consulta con preOps
y postOps
en la tabla definitions/pre_and_post_ops_example.js
:
publish("example")
.query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
.preOps(ctx => `DELETE ${ctx.self()}`)
.postOps(ctx => `GRANT SELECT on ${ctx.self()} to role`)
Cómo crear tablas incrementales con JavaScript
En la siguiente muestra de código, se indica la función publish
que se usa para crear una tabla incremental en el archivo 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()}`)}
`)
Cómo reabastecer una tabla diaria con JavaScript
En la siguiente muestra de código, se indica cómo reabastecer una tabla que se actualiza a diario en el archivo 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}'`)
);
Cómo reutilizar código con inclusiones
Cómo usar variables globales con JavaScript
En la siguiente muestra de código, se ve la definición de las constantes project_id
y first_date
en el includes/constants.js
:
const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
project_id,
first_date
};
En la siguiente muestra de código, se indica la constante first_date
a la que se hace referencia en el archivo definitions/new_table.sqlx
:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.first_date}
Cómo crear mapas de países con JavaScript
En la siguiente muestra de código, se ve la función personalizada country_group
definida en el archivo 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`;
En la siguiente muestra de código, se indica una definición de tabla que usa la función country_group
en el archivo de definición de tablas 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
En la siguiente muestra de código, se ve la consulta definida en definitions/new_table.sqlx
compilada en 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
Genera una secuencia de comandos de SQL con una función de JavaScript personalizada
En la siguiente muestra de código, se ve la función personalizada render_script
definida en 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 };
En la siguiente muestra de código, se indica una definición de tabla que usa la función render_script
en el archivo de definición de tablas 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"]
)}
En la siguiente muestra de código, se ve la consulta definida en definitions/new_table.sqlx
compilada en 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