Dataform 核心範例指令碼

本文提供 Dataform 核心和 JavaScript 指令碼範例，可用於在 Dataform 中建立工作流程。

建立表格

使用 Dataform Core 建立檢視區塊

以下程式碼範例顯示 definitions/new_view.sqlx 檔案中名為 new_view 的檢視區塊定義：

config { type: "view" }

SELECT * FROM source_data

使用 Dataform Core 建立具體化檢視表

以下程式碼範例顯示 definitions/new_materialized_view.sqlx 檔案中名為 new_materialized_view 的具體化檢視區塊定義：

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

SELECT * FROM source_data

使用 Dataform Core 建立資料表

以下程式碼範例顯示 definitions/new_table.sqlx 檔案中名為 new_table 的資料表定義：

config { type: "table" }

SELECT * FROM source_data

使用 Dataform Core 建立增量資料表

下列程式碼範例顯示遞增資料表，該資料表會遞增處理 productiondb.logs 資料表的資料列：

config { type: "incremental" }

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

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

使用 ref 函式參照 Dataform Core 中的資料表

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

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 Core 定期建立資料表快照

以下程式碼範例顯示 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()})`) }

使用 Dataform Core 建立滾動式 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 Core 在 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 Core 建立資料表後執行自訂 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 Core 新增自訂斷言

下列程式碼範例顯示資料表定義檔案中的自訂斷言，可驗證 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

下列程式碼範例顯示用於在 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}'`)
);

重複使用含有 include 的程式碼

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