Exemples de scripts Dataform principaux

Ce document présente des exemples de scripts Dataform Core et JavaScript qui 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 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 Dataform Core

L'exemple de code suivant montre la fonction ref utilisée pour référencer le Table source_data dans le definitions/new_table_with_ref.sqlx fichier de définition de table:

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 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 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éfini 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 definitions/table_with_preops.sqlx fichier de définition de table:

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 definitions/table_with_postops.sqlx fichier de définition de table:

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:[e-mail]", "user:[e-mail]"
}

Valider des tables

Ajouter des assertions à une table, une vue ou une déclaration avec Dataform Core

L'exemple de code suivant montre uniqueKey, nonNull, et rowConditions ont été ajoutées à definitions/tested_table.sqlx fichier de définition de table:

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é de manière 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 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 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 le 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 definitions/delete_pii.js fichier qui supprime les informations sélectionnées dans toutes les tables contenant 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 le definitions/pre_and_post_ops_example.js:

publish("example")
  .preOps(ctx => `GRANT \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:[e-mail]"`)
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .postOps(ctx => `REVOKE \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:[e-mail]"`)

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 de project_id et first_date constantes 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 le Fonction country_group dans definitions/new_table.sqlx fichier de définition de table:

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é 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éfini 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 le Fonction render_script dans definitions/new_table.sqlx fichier de définition de table:

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é 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 actions.yaml fichiers du dossier definitions.

Pour en savoir plus sur les types d'actions disponibles et les configurations d'action valides consultez la documentation Configurations Dataform Documentation de référence.

L'exemple de code suivant montre la définition d'une vue appelée new_view dans Fichier definitions/actions.yaml:

actions:
  - view:
    filename: new_view.sql

Le fichier SQL definitions/new_view.sql, référencé par le code précédent contient du SQL pur:

SELECT * FROM source_data