Ce document présente des exemples de scripts Dataform et JavaScript que vous pouvez utiliser pour créer un workflow SQL dans Dataform.
Créer des tables
Créer une vue avec Dataform Core
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 Dataform Core
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 Dataform Core
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 Dataform Core
L'exemple de code suivant montre une table incrémentielle qui traite les lignes de la table productiondb.logs de manière incrémentielle:
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 Dataform Core
L'exemple de code suivant montre la fonction ref utilisée pour référencer la table source_data dans le fichier de définition de table 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 Dataform Core
L'exemple de code suivant montre les descriptions de tables et de 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 les nouvelles dates dans les données sources avec Dataform Core
L'exemple de code suivant montre la configuration d'une table incrémentielle dans le fichier definitions/incremental_table.sqlx. Dans cette configuration, Dataform ajoute une ligne au 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 régulièrement un instantané d'une table avec Dataform Core
L'exemple de code suivant montre la 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 Dataform Core
L'exemple de code suivant montre la configuration d'une table incrémentielle dans le fichier definitions/incremental_example.sqlx. Dans cette configuration, Dataform crée un 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 Dataform Core
L'exemple de code suivant montre ; utilisé pour 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 un code SQL personnalisé avant de créer une table avec Dataform Core
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 un code SQL personnalisé après avoir créé une table avec Dataform Core
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 Dataform Core
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 Dataform Core
L'exemple de code suivant montre une assertion personnalisée dans un fichier de définition de table qui vérifie 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 de façon intégrée 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 une table pour chaque pays défini dans le fichier countries du 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 comment déclarer 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 avec forEach
L'exemple de code suivant montre comment déclarer 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 permettant d'identifier personnellement l'utilisateur à l'aide de 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()}`)}
`)
Remplir une table quotidienne avec JavaScript
L'exemple de code suivant montre comment remplir une table 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 le 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`;
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 d'actions
Charger des fichiers SQL avec des configurations d'action
Les configurations d'action facilitent le chargement de fichiers SQL pur. Vous pouvez définir des configurations d'action dans les fichiers actions.yaml du 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