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 ファイルの増分テーブルの構成を示しています。この構成では、新しい日付ごとに新しい行が 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
    })
  );

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

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

インクルードを使用してコードを再利用する

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

module.exports = {
   country_group
};

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

次のコードサンプルは、SQL にコンパイルされた definitions/new_table.sqlx で定義されたクエリを示しています。

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

次のコードサンプルは、SQL にコンパイルされた definitions/new_table.sqlx で定義されたクエリを示しています。

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

アクションの構成

アクション構成を使用して SQL ファイルを読み込む

アクション構成を使用すると、純粋な SQL ファイルを簡単に読み込むことができます。アクション構成は、definitions フォルダの actions.yaml ファイルで定義できます。

使用可能なアクション タイプと有効なアクション構成オプションの詳細については、Dataform 構成リファレンスをご覧ください。

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

actions:
  - view:
    filename: new_view.sql

上記のコードサンプルで参照されている definitions/new_view.sql SQL ファイルには、純粋な SQL が含まれています。

SELECT * FROM source_data