Secuencias de comandos de ejemplo principales de Dataform

En este documento se muestran ejemplos de secuencias de comandos principales de Dataform y JavaScript que puedes usar para crear un flujo de trabajo en Dataform.

Crear tablas

Crear una vista con Dataform Core

En el siguiente ejemplo 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

Crear una vista materializada con Dataform Core

En el siguiente ejemplo 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

Crear una tabla con el núcleo de Dataform

En el siguiente código de ejemplo 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

Crear una tabla incremental con Dataform Core

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

Usar la función ref para hacer referencia a tablas con Dataform Core

En el siguiente ejemplo 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 tabla definitions/new_table_with_ref.sqlx:

config { type: "table" }

SELECT * FROM ${ref("source_data")}

Añadir documentación a una tabla, una vista o una declaración con el núcleo de Dataform

En el siguiente código de ejemplo se muestran las descripciones de la tabla y las columnas en el archivo de definición de la 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")}

Configurar tablas incrementales

Añadir filas de tabla para nuevas fechas en los datos de origen con Dataform Core

En el siguiente código de muestra se muestra la configuración de una tabla incremental en el archivo definitions/incremental_table.sqlx. En esta configuración, Dataform añade una nueva fila a incremental_table por 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()})`)

Hacer una captura de una tabla periódicamente con Dataform Core

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

Crear una tabla de 30 días que se actualice de forma incremental con Dataform Core

En el siguiente código de ejemplo se muestra una configuración de una tabla incremental en el archivo definitions/incremental_example.sqlx. En esta configuración, Dataform crea una incremental_example temporal que se actualiza de forma incremental y elimina 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()})`) }

Crear operaciones SQL personalizadas

Ejecutar varias operaciones de SQL en un archivo SQLX con el núcleo de Dataform

En el siguiente código de ejemplo se muestra ; para separar varias operaciones SQL definidas en definitions/operations.sqlx:

config { type: "operations" }

DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';

Ejecutar SQL personalizado antes de crear una tabla con Dataform Core

En el siguiente fragmento de código se muestra una operación 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 ...
}

Ejecutar SQL personalizado después de crear una tabla con Dataform Core

En el siguiente fragmento de código se muestra una operación 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 `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

Validar tablas

Añadir aserciones a una tabla, una vista o una declaración con Dataform Core

En el siguiente ejemplo de código se muestran las aserciones uniqueKey, nonNull y rowConditions añadidas al archivo de definición de la 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 ...

Añadir una aserción personalizada con Dataform Core

En el siguiente ejemplo de código se muestra una aserción personalizada en un archivo de definición de tabla 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

Desarrollar con JavaScript

Usar variables y funciones insertadas con JavaScript

En el siguiente ejemplo de código se muestra la variable foo definida en un bloque js y, a continuación, se usa en línea en un archivo SQLX:

js {
 const foo = 1;
 function bar(number){
     return number+1;
 }
}

SELECT
 ${foo} AS one,
 ${bar(foo)} AS two

Generar una tabla por país con JavaScript

En el siguiente fragmento 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
      `
    );
});

Declarar varias fuentes en un archivo con JavaScript

En el siguiente ejemplo 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"
});

Declarar varias fuentes en un archivo con forEach

En el siguiente ejemplo 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
    })
  );

Eliminar información sensible en todas las tablas que contengan información personal identificable con JavaScript

El siguiente ejemplo de código muestra una función del archivo definitions/delete_pii.js que elimina la información seleccionada de todas las tablas que contienen información personal identificable (IPI):

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"]))
);

Añadir preOps y postOps con JavaScript

En el siguiente ejemplo 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")
  .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"`)

Crear tablas incrementales con JavaScript

En el siguiente ejemplo 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()}`)}
`)

Rellenar una tabla diaria con JavaScript

En el siguiente ejemplo de código se muestra cómo rellenar 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}'`)
);

Reutilizar código con includes

Usar variables globales con JavaScript

En el siguiente código de ejemplo 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 el siguiente código de ejemplo 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}

Crear una asignación de países con JavaScript

En el siguiente código de ejemplo 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`;
}

module.exports = {
   country_group
};

En el siguiente ejemplo de código se muestra una definición de tabla que usa la función country_group en el archivo de definición de tabla 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 el siguiente código de ejemplo se muestra 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

Generar una secuencia de comandos SQL con una función de JavaScript personalizada

En el siguiente código de ejemplo 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 el siguiente ejemplo de código se muestra una definición de tabla que usa la función render_script en el archivo de definición de tabla 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 el siguiente código de ejemplo se muestra 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

Configuraciones de acciones

Cargar archivos SQL con configuraciones de acciones

Las configuraciones de acciones facilitan la carga de archivos SQL puros. Puede definir configuraciones de acciones en archivos actions.yaml de la carpeta definitions.

Para obtener más información sobre los tipos de acciones disponibles y las opciones de configuración de acciones válidas, consulta la referencia de configuraciones de Dataform.

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

actions:
  - view:
    filename: new_view.sql

El archivo SQL definitions/new_view.sql, al que se hace referencia en el ejemplo de código anterior, contiene SQL puro:

SELECT * FROM source_data