Secuencias de comandos de muestra principales de Dataform

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

Crea tablas

Crea una vista con Dataform Core

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

config { type: "view" }

SELECT * FROM source_data

Crea una vista materializada con el núcleo de Dataform

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

Crea una tabla con Dataform Core

En la siguiente muestra de código, se indica la definición de una tabla llamada new_table. En el archivo definitions/new_table.sqlx:

config { type: "table" }

SELECT * FROM source_data

Crea una tabla incremental con Dataform Core

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

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

En la siguiente muestra de código, se observa la función ref que se usa para hacer referencia al Tabla source_data en definitions/new_table_with_ref.sqlx archivo de definición de tablas:

config { type: "table" }

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

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

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

Agregar nuevas filas de la tabla para fechas nuevas en los datos de origen con Dataform Core

En la siguiente muestra de código, se indica 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 el núcleo de Dataform

El siguiente muestra de código muestra una configuración de una tabla incremental en el 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 móvil de 30 días que se actualice de forma incremental con Dataform Core

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

Crea operaciones personalizadas de SQL

Ejecuta varias operaciones de SQL en un archivo SQLX con Dataform Core

En la siguiente muestra de código, se puede ver cómo ; se usa para separar varias operaciones de SQL definido en definitions/operations.sqlx:

config { type: "operations" }

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

Ejecuta SQL personalizado antes de crear una tabla con Dataform Core

En la siguiente muestra de código, aparece una operación SQL personalizada que se define en el Bloque pre_operations del definitions/table_with_preops.sqlx archivo de definición de tablas:

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, aparece una operación SQL personalizada que se define en el Bloque post_operations del definitions/table_with_postops.sqlx archivo de definición de tablas:

config {type: "table"}

SELECT * FROM ...

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

Valida tablas

Cómo agregar aserciones a una tabla, vista o declaración con el núcleo de Dataform

En la siguiente muestra de código, se muestran uniqueKey, nonNull, y rowConditions agregadas a definitions/tested_table.sqlx archivo de definición de tablas:

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 ...

Agrega una aserción personalizada con el núcleo de Dataform

En la siguiente muestra de código, se observa una aserción personalizada en un archivo de definición de tablas. 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

Cómo desarrollar con JavaScript

Cómo usar variables y funciones intercaladas con JavaScript

En la siguiente muestra de código, se observa la variable foo definida en un bloque js. y, luego, lo usamos intercalado 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 la 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 presenta la declaración de múltiples 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"
});

Declara varias fuentes en un archivo con forEach

En la siguiente muestra de código, se presenta la declaración de múltiples fuentes de datos con el Función forEach en el archivo definitions/external_dependencies.js:

["charges", "subscriptions", "line_items", "invoices"]
  .forEach(source => declare({
      schema: "stripe",
      name: source
    })
  );

Cómo borrar información sensible en todas las tablas que contengan PII con JavaScript

En la siguiente muestra de código, se observa una función en 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"]))
);

Agrega preOps y postOps con JavaScript

En la siguiente muestra de código, se observa 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:[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]"`)

Crea tablas incrementales con JavaScript

En la siguiente muestra de código, se observa la función publish que se usa para crear un 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()}`)}
`)

Reabastece una tabla diaria con JavaScript

En la siguiente muestra de código, se indica cómo reabastecer una tabla que se actualiza diariamente 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 volver a usar código con inclusiones

Usa variables globales con JavaScript

En la siguiente muestra de código, se indica la definición de project_id y first_date constantes 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 observa 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ís con JavaScript

En la siguiente muestra de código, se observa la función personalizada country_group que se define 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 la siguiente muestra de código, aparece una definición de tabla que utiliza el Función country_group en definitions/new_table.sqlx archivo de definición de tablas:

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, aparece la consulta definida en definitions/new_table.sqlx compilado 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

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

En la siguiente muestra de código, se observa la función personalizada render_script. definido 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, aparece una definición de tabla que utiliza el Función render_script en definitions/new_table.sqlx archivo de definición de tablas:

config {
    type: "table",
    tags: ["advanced", "hourly"],
    disabled: true
}

${script_builder.render_script(ref("source_table"),
                               ["country", "device_type"],
                               ["revenue", "pageviews", "sessions"]
                               )}

En la siguiente muestra de código, aparece la consulta definida en definitions/new_table.sqlx compilado 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

Configuración de acciones

Carga archivos SQL con configuraciones de acción

Los archivos de configuración de acción facilitan la carga de archivos SQL puros. Puedes definir parámetros de configuración de acciones en archivos actions.yaml de la carpeta definitions.

Para obtener más información sobre los tipos de acciones disponibles y los parámetros de configuración de acciones válidos consulta Configuraciones de Dataform Referencia.

En la siguiente muestra de código, se indica 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 hace referencia el código anterior muestra, contiene SQL puro:

SELECT * FROM source_data