Dataform コアサンプルスクリプト

{

このドキュメントでは、Dataform で SQL ワークフローを作成する際に使用できる Dataform のコアスクリプトと JavaScript スクリプトの例を示します。

テーブルの作成

Dataform コアを使用してビューを作成する

次のコードサンプルは、definitions/new_view.sqlx ファイル内の new_view というビューの定義を示しています。

config { type: "view" }

SELECT * FROM source_data

Dataform コアを使用して実体化されたビューを作成する

次のコードサンプルは、definitions/new_materialized_view.sqlx ファイル内の new_materialized_view というマテリアライズドビューの定義を示しています。

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

SELECT * FROM source_data

Dataform コアを使用してテーブルを作成する

次のコードサンプルは、definitions/new_table.sqlx ファイル内の new_table というテーブルの定義を示しています。

config { type: "table" }

SELECT * FROM source_data

Dataform コアを使用して増分テーブルを作成する

次のコードサンプルは、productiondb.logs テーブルの行をインクリメントに処理する増分テーブルを示しています。

config { type: "incremental" }

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

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

`ref` 関数を使用して Dataform コアでテーブルを参照する

次のコードサンプルは、definitions/new_table_with_ref.sqlx テーブル定義ファイル内の source_data テーブルを参照するために使用される ref 関数を示しています。

config { type: "table" }

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

Dataform コアを使用してテーブル、ビュー、宣言にドキュメントを追加する

次のコードサンプルは、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")}

増分テーブルの構成

Dataform コアを使用してソースデータの新しい日付に新しいテーブル行を追加する

次のコードサンプルは、definitions/incremental_table.sqlx ファイルの増分テーブルの構成を示しています。この構成では、Dataform は新しい日付ごとに新しい行を incremental_table に追加します。

config { type: "incremental" }

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

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

Dataform コアを使用してテーブルのスナップショットを定期的に取得する

次のコードサンプルは、definitions/snapshots_table.sqlx ファイル内の増分テーブルの構成を示しています。この構成では、Dataform は、指定された日付に productiondb.customers のスナップショットで snapshots_table を作成します。

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

Dataform コアを使用して 30 日間のローリングテーブルを構築し、段階的に更新する

次のコードサンプルは、definitions/incremental_example.sqlx ファイル内の増分テーブルの構成を示しています。この構成では、Dataform が一時的な incremental_example を作成して増分更新し、作成から 30 日後にテーブルを削除します。

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

カスタム SQL オペレーションの作成

Dataform コアを使用して SQLX ファイルで複数の SQL オペレーションを実行する

次のコードサンプルは、definitions/operations.sqlx で定義された複数の SQL オペレーションの分離に使用される ; を示しています。

config { type: "operations" }

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

Dataform コアでテーブルを作成する前にカスタム SQL を実行する

次のコードサンプルは、definitions/table_with_preops.sqlx テーブル定義ファイルの pre_operations ブロックで定義されているカスタム SQL オペレーションを示しています。

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Dataform コアでテーブルを作成した後にカスタム SQL を実行する

次のコードサンプルは、definitions/table_with_postops.sqlx テーブル定義ファイルの post_operations ブロックで定義されたカスタム SQL オペレーションを示しています。

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

テーブルの検証

Dataform コアを使用してテーブル、ビュー、宣言にアサーションを追加する

次のコードサンプルは、definitions/tested_table.sqlx テーブル定義ファイルに追加された uniqueKey、nonNull、rowConditions アサーションを示しています。

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

Dataform コアでカスタムアサーションを追加する

次のコードサンプルは、source_data の列 a、b、c が null かどうかを検証するテーブル定義ファイルのカスタムアサーションを示しています。

config { type: "assertion" }

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

JavaScript での開発

JavaScript でインライン変数と関数を使用する

次のコードサンプルは、js ブロックで定義され、SQLX ファイルでインラインで使用される foo 変数を示しています。

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

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

JavaScript を使用して国ごとに 1 つのテーブルを生成する

次のコードサンプルは、forEach 関数を使用して、definitions/one_table_per_country.js ファイル内の countries で定義されている国ごとに 1 つのテーブルを生成する方法を示しています。

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

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

JavaScript を使用して 1 つのファイル内で複数のソースを宣言する

次のコードサンプルは、definitions/external_dependencies.js ファイル内での複数のデータソースの宣言を示しています。

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

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

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

`forEach` を使用して 1 つのファイル内で複数のソースを宣言する

次のコードサンプルは、definitions/external_dependencies.js ファイルの forEach 関数を使用した複数のデータソースの宣言を示しています。

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

PII を含むすべてのテーブルの機密情報を JavaScript で削除する

次のコードサンプルは、個人情報（PII）を含むすべてのテーブルから選択された情報を削除する definitions/delete_pii.js ファイルの関数を示しています。

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

JavaScript を使用して `preOps` と `postOps` を追加する

次のコードサンプルは、definitions/pre_and_post_ops_example.js テーブルで preOps と postOps を使用してクエリを作成するために使用される publish 関数を示しています。

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

JavaScript を使用して増分テーブルを作成する

次のコードサンプルは、definitions/incremental_example.js ファイルに増分テーブルを作成するために使用される publish 関数を示しています。

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

JavaScript を使用した日次テーブルのバックフィル

次のコードサンプルは、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}'`)
);

includes を使用したコードの再利用

JavaScript でのグローバル変数の使用

次のコードサンプルは、includes/constants.js の project_id 定数と first_date 定数の定義を示しています。

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

次のコードサンプルは、definitions/new_table.sqlx ファイルで参照される first_date 定数を示しています。

config {type: "table"}

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

JavaScript を使用して国のマッピングを作成する

次のコードサンプルは、includes/mapping.js ファイルで定義された 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`;

次のコードサンプルは、definitions/new_table.sqlx テーブル定義ファイルで country_group 関数を使用するテーブル定義を示しています。

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

次のコードサンプルは、definitions/new_table.sqlx で定義されたクエリを 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

カスタム JavaScript 関数を使用して SQL スクリプトを生成する

次のコードサンプルは、includes/script_builder.js で定義された 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 };

次のコードサンプルは、definitions/new_table.sqlx テーブル定義ファイルで render_script 関数を使用するテーブル定義を示しています。

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

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