Ce document présente des exemples de scripts JavaScript et de noyau Dataform que vous pouvez utiliser pour créer un workflow SQL dans Dataform.
Créer des tables
Créer une vue avec le noyau Dataform
L'exemple de code suivant montre la définition d'une vue appelée new_view dans le fichier definitions/new_view.sqlx:
config { type: "view" }
SELECT * FROM source_data
Créer une vue matérialisée avec le noyau Dataform
L'exemple de code suivant montre la définition d'une vue matérialisée appelée new_materialized_view dans le fichier definitions/new_materialized_view.sqlx:
config {
type: "view",
materialized: true
}
SELECT * FROM source_data
Créer une table avec le noyau Dataform
L'exemple de code suivant montre la définition d'une table appelée new_table dans le fichier definitions/new_table.sqlx:
config { type: "table" }
SELECT * FROM source_data
Créer une table incrémentielle avec le noyau Dataform
L'exemple de code suivant montre une table incrémentielle qui traite de manière incrémentielle les lignes de la table productiondb.logs:
config { type: "incremental" }
SELECT timestamp, message FROM ${ref("productiondb", "logs")}
${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }
Utiliser la fonction ref pour référencer des tables avec le noyau Dataform
L'exemple de code suivant montre la fonction ref utilisée pour faire référence au tableau source_data dans le fichier de définition de tableau definitions/new_table_with_ref.sqlx:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Ajouter de la documentation à une table, une vue ou une déclaration avec le noyau Dataform
L'exemple de code suivant affiche les descriptions des tables et des colonnes dans le fichier de définition de table 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")}
Configurer des tables incrémentielles
Ajouter des lignes de tableau pour de nouvelles dates dans les données sources avec le noyau Dataform
L'exemple de code suivant montre une configuration d'une table incrémentielle dans le fichier definitions/incremental_table.sqlx. Dans cette configuration, Dataform ajoute une nouvelle ligne à incremental_table pour chaque nouvelle date:
config { type: "incremental" }
SELECT date(timestamp) AS date, action
FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)
Prendre un instantané d'une table régulièrement avec le noyau Dataform
L'exemple de code suivant montre une configuration d'une table incrémentielle dans le fichier definitions/snapshots_table.sqlx. Dans cette configuration, Dataform crée snapshots_table avec un instantané de productiondb.customers à la date spécifiée:
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()})`) }
Créer une table glissante sur 30 jours qui se met à jour de manière incrémentielle avec le noyau Dataform
L'exemple de code suivant montre une configuration d'une table incrémentielle dans le fichier definitions/incremental_example.sqlx. Dans cette configuration, Dataform crée une incremental_example temporaire qui se met à jour de manière incrémentielle et supprime la table 30 jours après sa création:
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()})`) }
Créer des opérations SQL personnalisées
Exécuter plusieurs opérations SQL dans un fichier SQLX avec le noyau Dataform
L'exemple de code suivant montre comment ; permet de séparer plusieurs opérations SQL définies dans definitions/operations.sqlx:
config { type: "operations" }
DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';
Exécuter du code SQL personnalisé avant de créer une table avec le noyau Dataform
L'exemple de code suivant montre une opération SQL personnalisée définie dans le bloc pre_operations du fichier de définition de table definitions/table_with_preops.sqlx:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Exécuter du code SQL personnalisé après avoir créé une table avec le noyau Dataform
L'exemple de code suivant montre une opération SQL personnalisée définie dans le bloc post_operations du fichier de définition de table 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"
}
Valider des tables
Ajouter des assertions à une table, une vue ou une déclaration avec le noyau Dataform
L'exemple de code suivant montre les assertions uniqueKey, nonNull et rowConditions ajoutées au fichier de définition de table 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 ...
Ajouter une assertion personnalisée avec le noyau Dataform
L'exemple de code suivant montre une assertion personnalisée dans un fichier de définition de table qui valide si les colonnes a, b ou c de source_data sont null:
config { type: "assertion" }
SELECT
*
FROM
${ref("source_data")}
WHERE
a is null
or b is null
or c is null
Développer avec JavaScript
Utiliser des variables et des fonctions intégrées avec JavaScript
L'exemple de code suivant montre la variable foo définie dans un bloc js, puis utilisée en ligne dans un fichier SQLX:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Générer une table par pays avec JavaScript
L'exemple de code suivant montre comment utiliser la fonction forEach pour générer un tableau par pays défini dans countries dans le fichier 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
`
);
});
Déclarer plusieurs sources dans un même fichier avec JavaScript
L'exemple de code suivant montre la déclaration de plusieurs sources de données dans le fichier definitions/external_dependencies.js:
declare({
schema: "stripe",
name: "charges"
});
declare({
schema: "shopify",
name: "orders"
});
declare({
schema: "salesforce",
name: "accounts"
});
Déclarer plusieurs sources dans un même fichier à l'aide de forEach
L'exemple de code suivant montre la déclaration de plusieurs sources de données avec la fonction forEach dans le fichier definitions/external_dependencies.js:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Supprimer les informations sensibles de toutes les tables contenant des informations personnelles avec JavaScript
L'exemple de code suivant montre une fonction dans le fichier definitions/delete_pii.js qui supprime les informations sélectionnées dans toutes les tables contenant des informations permettant d'identifier personnellement l'utilisateur :
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"]))
);
Ajouter preOps et postOps avec JavaScript
L'exemple de code suivant montre la fonction publish utilisée pour créer une requête avec preOps et postOps dans la table 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"`)
Créer des tables incrémentielles avec JavaScript
L'exemple de code suivant montre la fonction publish utilisée pour créer une table incrémentielle dans le fichier 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()}`)}
`)
Remplissage d'une table quotidienne avec JavaScript
L'exemple de code suivant montre le remplissage d'une table qui est mise à jour quotidiennement dans le fichier 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}'`)
);
Réutiliser du code avec des inclusions
Utiliser des variables globales avec JavaScript
L'exemple de code suivant montre la définition des constantes project_id et first_date dans includes/constants.js:
const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
project_id,
first_date
};
L'exemple de code suivant montre la constante first_date référencée dans le fichier definitions/new_table.sqlx:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.first_date}
Créer un mappage de pays avec JavaScript
L'exemple de code suivant montre la fonction personnalisée country_group définie dans le fichier 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
};
L'exemple de code suivant montre une définition de table qui utilise la fonction country_group dans le fichier de définition de table 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
L'exemple de code suivant montre la requête définie dans definitions/new_table.sqlx compilée 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
Générer un script SQL avec une fonction JavaScript personnalisée
L'exemple de code suivant montre la fonction personnalisée render_script définie dans 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 };
L'exemple de code suivant montre une définition de table qui utilise la fonction render_script dans le fichier de définition de table 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"]
)}
L'exemple de code suivant montre la requête définie dans definitions/new_table.sqlx compilée 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
Configurations des actions
Charger des fichiers SQL avec des configurations d'action
Les configurations d'action facilitent le chargement de fichiers SQL purs. Vous pouvez définir des configurations d'action dans des fichiers actions.yaml dans le dossier definitions.
Pour en savoir plus sur les types d'actions disponibles et les options de configuration d'action valides, consultez la documentation de référence sur les configurations Dataform.
L'exemple de code suivant montre la définition d'une vue appelée new_view dans le fichier definitions/actions.yaml:
actions:
- view:
filename: new_view.sql
Le fichier SQL definitions/new_view.sql, référencé par l'exemple de code précédent, contient du code SQL pur:
SELECT * FROM source_data