Dataform 核心範例指令碼

本文件會列出 Dataform 核心和 JavaScript 指令碼範例，您可以使用這些範例在 Dataform 中建立 SQL 工作流程。

建立資料表

使用 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 core 的資料表

以下程式碼範例顯示 ref 函式，用於在 definitions/new_table_with_ref.sqlx 資料表定義檔案中參照 source_data 資料表：

config { type: "table" }

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

在使用 Dataform core 的資料表、檢視畫面或宣告中加入文件

下列程式碼範例顯示 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 core 在來源資料中新增資料列，以便新增日期

以下程式碼範例顯示 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 會在指定日期建立 snapshots_table，並使用 productiondb.customers 的快照：

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

建立滾動式 30 天資料表，以 Dataform 核心逐漸更新

以下程式碼範例顯示 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 作業

在 SQLX 檔案中使用 Dataform 核心執行多個 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 core 在資料表、檢視畫面或宣告中加入斷言

下列程式碼範例顯示已新增至 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 區塊中定義的 foo 變數，然後在 SQLX 檔案中內嵌使用：

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

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

使用 JavaScript 為每個國家/地區產生一個表格

下列程式碼範例說明如何使用 forEach 函式，針對 definitions/one_table_per_country.js 檔案中 countries 中定義的每個國家/地區產生一張資料表：

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

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

使用 JavaScript 在單一檔案中宣告多個來源

以下程式碼範例顯示如何在 definitions/external_dependencies.js 檔案中宣告多個資料來源：

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

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

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

使用 forEach 在單一檔案中宣告多個來源

以下程式碼範例說明如何在 definitions/external_dependencies.js 檔案中使用 forEach 函式宣告多個資料來源：

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

使用 JavaScript 刪除所有含有 PII 的資料表中的機密資訊

以下程式碼範例顯示 definitions/delete_pii.js 檔案中的函式，該函式會刪除所有含有個人識別資訊 (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"]))
);

使用 JavaScript 新增 preOps 和 postOps

下列程式碼範例顯示 publish 函式，可用於在 definitions/pre_and_post_ops_example.js 資料表中使用 preOps 和 postOps 建立查詢：

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 建立增量資料表

以下程式碼範例顯示 publish 函式，可用於在 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()}`)}
`)

使用 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

以下程式碼範例顯示在 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"]
                               )}

以下程式碼範例顯示在 definitions/new_table.sqlx 中定義的查詢，並已編譯為 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

動作設定

使用動作設定載入 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