Ejemplo de secuencias de comandos de Dataform

{

En este documento, se muestran ejemplos de secuencias de comandos principales y de JavaScript que pueden usarse para crear un flujo de trabajo de SQL en Dataform.

Crea tablas

Cómo crear una vista con Dataform core

En la siguiente muestra de código, se muestra la definición de una vista llamada new_view en el archivo definitions/new_view.sqlx:

config { type: "view" }

SELECT * FROM source_data

Cómo crear una vista materializada con Dataform core

En la siguiente muestra de código, se muestra 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

Cree una tabla con Dataform core

En la siguiente muestra de código, se muestra 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 muestra una tabla incremental que procesa las filas de la tabla productiondb.logs de forma incremental:

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 núcleo de Dataform

En la siguiente muestra de código, se muestra la función ref que se usa para hacer referencia a la tabla source_data en el archivo de definición de tablas 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 Dataform core

En la siguiente muestra de código, se muestran descripciones de tablas y columnas en el archivo de definición de tablas 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

Cómo agregar nuevas filas de tablas para fechas nuevas en los datos de origen con Dataform core

En la siguiente muestra de código, se muestra la 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()})`)

Cómo tomar una instantánea de una tabla de forma periódica con Dataform core

En la siguiente muestra de código, se muestra la 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()})`) }

Cómo crear una tabla progresiva de 30 días que se actualice de forma incremental con Dataform Core

En la siguiente muestra de código, se muestra la 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 después de 30 días desde 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 Dataform core

En la siguiente muestra de código, se muestra ; 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';

Ejecute SQL personalizado antes de crear una tabla con Dataform core

En la siguiente muestra de código, se muestra una operación de SQL personalizada definida en el bloque pre_operations del archivo de definición de tablas 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 Dataform core

En la siguiente muestra de código, se muestra una operación de SQL personalizada definida en el bloque post_operations del archivo de definición de tablas 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, una vista o una declaración con Dataform core

En la siguiente muestra de código, se muestran las aserciones uniqueKey, nonNull y rowConditions que se agregan al archivo de definición de tablas 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 ...

Cómo agregar una aserción personalizada con Dataform core

En la siguiente muestra de código, se muestra una aserción personalizada que se agrega al archivo de definición de tablas definitions/custom_assertion.sqlx:

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 muestra la variable foo definida en un bloque js y, luego, 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 muestra 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 muestra 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"
});

Cómo declarar varias fuentes en un archivo con `forEach`

En la siguiente muestra de código, se muestra 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 información sensible en todas las tablas que contienen 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"]))
);

Agregando `preOps` y `postOps` con JavaScript

En la siguiente muestra de código, se muestra 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 muestra 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()}`)}
`)

Reabastecimiento de una tabla diaria con JavaScript

En la siguiente muestra de código, se muestra el reabastecimiento de 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 muestra la definición de las constantes project_id y first_date en 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 muestra 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 una asignación de país con JavaScript

En la siguiente muestra de código, se muestra 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 muestra una definición de tablas 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 muestra la consulta definida en definitions/new_table.sqlx compilada 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

Cómo generar una secuencia de comandos de SQL con una función de JavaScript personalizada

En la siguiente muestra de código, se muestra 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 muestra una definición de tablas 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"]
                               )}