Dataform-Beispielskripts

{

In diesem Dokument werden beispielhafte Dataform-Core- und JavaScript-Skripts beschrieben, mit denen Sie einen SQL-Workflow in Dataform erstellen können.

Tabellen erstellen

Ansicht mit Dataform Core erstellen

Das folgende Codebeispiel zeigt die Definition einer Ansicht namens new_view in der Datei definitions/new_view.sqlx:

config { type: "view" }

SELECT * FROM source_data

Materialisierte Ansicht mit Dataform-Kern erstellen

Das folgende Codebeispiel zeigt die Definition einer materialisierten Ansicht mit dem Namen new_materialized_view in der Datei definitions/new_materialized_view.sqlx:

config {
  type: "view",
  materialized: true
}

SELECT * FROM source_data

Tabelle mit Dataform Core erstellen

Das folgende Codebeispiel zeigt die Definition einer Tabelle namens new_table in der Datei definitions/new_table.sqlx:

config { type: "table" }

SELECT * FROM source_data

Inkrementelle Tabelle mit Dataform-Kern erstellen

Das folgende Codebeispiel zeigt eine inkrementelle Tabelle, die Zeilen der Tabelle productiondb.logs inkrementell verarbeitet:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

`ref`-Funktion zum Referenzieren von Tabellen mit Dataform-Kern verwenden

Im folgenden Codebeispiel wird die Funktion ref gezeigt, mit der auf die Tabelle source_data in der Tabellendefinitionsdatei definitions/new_table_with_ref.sqlx verwiesen wird:

config { type: "table" }

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

Dokumentation zu einer Tabelle, Ansicht oder Deklaration mit Dataform Core hinzufügen

Das folgende Codebeispiel zeigt Tabellen- und Spaltenbeschreibungen in der definitions/documented_table.sqlx-Tabellendefinitionsdatei:

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")}

Inkrementelle Tabellen konfigurieren

Neue Tabellenzeilen für neue Datumsangaben in Quelldaten mit Dataform-Kern hinzufügen

Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der Datei definitions/incremental_table.sqlx. In dieser Konfiguration hängt Dataform eine neue Zeile an das incremental_table für jedes neue Datum an:

config { type: "incremental" }

SELECT date(timestamp) AS date, action
FROM weblogs.user_actions

${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)

Snapshot einer Tabelle regelmäßig mit Dataform-Kern erstellen

Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der Datei definitions/snapshots_table.sqlx. In dieser Konfiguration erstellt Dataform snapshots_table mit einem Snapshot von productiondb.customers zum angegebenen Datum:

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()})`) }

Eine rollierende 30-Tage-Tabelle erstellen, die schrittweise mit dem Dataform-Kern aktualisiert wird

Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der Datei definitions/incremental_example.sqlx. In dieser Konfiguration wird in Dataform ein temporäres incremental_example erstellt, das schrittweise aktualisiert und die Tabelle 30 Tage nach dem Erstellen gelöscht wird:

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()})`) }

Benutzerdefinierte SQL-Vorgänge erstellen

Mehrere SQL-Vorgänge in einer SQLX-Datei mit Dataform-Kern ausführen

Das folgende Codebeispiel zeigt ;, mit dem mehrere in definitions/operations.sqlx definierte SQL-Vorgänge getrennt werden:

config { type: "operations" }

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

Benutzerdefiniertes SQL ausführen, bevor eine Tabelle mit Dataform-Kern erstellt wird

Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der im pre_operations-Block der definitions/table_with_preops.sqlx-Tabellendefinitionsdatei definiert ist:

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Benutzerdefinierte SQL-Abfrage nach dem Erstellen einer Tabelle mit Dataform Core ausführen

Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der im post_operations-Block der definitions/table_with_postops.sqlx-Tabellendefinitionsdatei definiert ist:

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";
}

Tabellen validieren

Assertions zu einer Tabelle, Ansicht oder Deklaration mit Dataform Core hinzufügen

Das folgende Codebeispiel zeigt die Assertions uniqueKey, nonNull und rowConditions, die der Tabellendefinitionsdatei definitions/tested_table.sqlx hinzugefügt wurden:

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

Benutzerdefinierte Assertion mit dem Dataform-Kern hinzufügen

Das folgende Codebeispiel zeigt eine benutzerdefinierte Assertion, die der Tabellendefinitionsdatei definitions/custom_assertion.sqlx hinzugefügt wird:

config { type: "assertion" }

SELECT
  *
FROM
  ${ref("source_data")}
WHERE
  a is null
  or b is null
  or c is null

Mit JavaScript entwickeln

Inline-Variablen und -Funktionen mit JavaScript verwenden

Im folgenden Codebeispiel sehen Sie die Variable foo, die in einem js-Block definiert und dann inline in einer SQLX-Datei verwendet wird:

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

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

Eine Tabelle pro Land mit JavaScript generieren

Im folgenden Codebeispiel sehen Sie, wie mit der Funktion forEach für jedes in countries in der Datei definitions/one_table_per_country.js definierte Land eine Tabelle generiert wird:

const countries = ["GB", "US", "FR", "TH", "NG"];

countries.forEach(country => {
  publish("reporting_" + country)
    .dependencies(["source_table"])
    .query(
      ctx => `
      SELECT '${country}' AS country
      `
    );
});

Mehrere Quellen in einer Datei mit JavaScript deklarieren

Das folgende Codebeispiel zeigt die Deklaration mehrerer Datenquellen in der Datei definitions/external_dependencies.js:

declare({
  schema: "stripe",
  name: "charges"
});

declare({
  schema: "shopify",
  name: "orders"
});

declare({
  schema: "salesforce",
  name: "accounts"
});

Mehrere Quellen in einer Datei mit `forEach` deklarieren

Das folgende Codebeispiel zeigt die Deklaration mehrerer Datenquellen mit der Funktion forEach in der Datei definitions/external_dependencies.js:

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

Vertrauliche Informationen in allen Tabellen löschen, die personenidentifizierbare Informationen mit JavaScript enthalten

Das folgende Codebeispiel zeigt eine Funktion in der Datei definitions/delete_pii.js, mit der ausgewählte Informationen in allen Tabellen gelöscht werden, die personenidentifizierbare Informationen enthalten:

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

`preOps` und `postOps` mit JavaScript hinzufügen

Das folgende Codebeispiel zeigt die Funktion publish, die zum Erstellen einer Abfrage mit preOps und postOps in der Tabelle definitions/pre_and_post_ops_example.js verwendet wird:

publish("example")
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .preOps(ctx => `DELETE ${ctx.self()}`)
  .postOps(ctx => `GRANT SELECT on ${ctx.self()} to role`)

Inkrementelle Tabellen mit JavaScript erstellen

Das folgende Codebeispiel zeigt die Funktion publish, mit der eine inkrementelle Tabelle in der Datei definitions/incremental_example.js erstellt wird:

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()}`)}
`)

Backfill von Tabellen pro Tag mit JavaScript

Das folgende Codebeispiel zeigt einen Backfill einer Tabelle, die täglich in der Datei definitions/backfill_daily_data.js aktualisiert wird:

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}'`)
);

Code wiederverwenden

Globale Variablen mit JavaScript verwenden

Im folgenden Codebeispiel sehen Sie die Definition der Konstanten project_id und first_date in includes/constants.js:

const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
  project_id,
  first_date
};

Im folgenden Codebeispiel sehen Sie die Konstante first_date, auf die in der Datei definitions/new_table.sqlx verwiesen wird:

config {type: "table"}

SELECT * FROM source_table WHERE date > ${constants.first_date}

Länderzuordnung mit JavaScript erstellen

Das folgende Codebeispiel zeigt die in der Datei includes/mapping.js definierte benutzerdefinierte Funktion country_group:

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`;

Das folgende Codebeispiel zeigt eine Tabellendefinition, bei der die Funktion country_group in der Tabellendefinitionsdatei definitions/new_table.sqlx verwendet wird:

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

Das folgende Codebeispiel zeigt die in definitions/new_table.sqlx definierte Abfrage, die in SQL kompiliert wird:

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

SQL-Skript mit einer benutzerdefinierten JavaScript-Funktion generieren

Das folgende Codebeispiel zeigt die in includes/script_builder.js definierte benutzerdefinierte Funktion render_script:

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 };

Das folgende Codebeispiel zeigt eine Tabellendefinition, bei der die Funktion render_script in der Tabellendefinitionsdatei definitions/new_table.sqlx verwendet wird:

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

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