建立作業

本文說明如何在專屬 SQLX 檔案中定義自訂 SQL 作業。

Dataform 可以執行不符合發布資料表或寫入斷言的 Dataform 模型自訂 SQL 作業。您可以定義自訂 SQL 指令，供 Dataform 在 BigQuery 中執行。

事前準備

  1. 前往 Google Cloud 控制台的「Dataform」頁面。

    前往「Dataform」頁面

  2. 選取或建立存放區。

  3. 選取或建立開發工作區。

必要的角色

如要取得定義自訂 SQL 作業所需的權限，請要求管理員授予您工作區的 Dataform 編輯器 (roles/dataform.editor) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

您或許還可透過自訂角色或其他預先定義的角色取得必要權限。

建立自訂作業定義的檔案

將自訂作業定義 SQLX 檔案儲存在 definitions/ 目錄中。如要在 definitions/ 目錄中建立新的 SQLX 檔案，請按照下列步驟操作：

  1. 前往開發工作區。
  2. 在「Files」(檔案) 窗格中，點按 definitions/ 旁的 「更多」選單。
  3. 點選「建立檔案」。

  4. 在「Add a file path」(新增檔案路徑) 欄位中，輸入檔案名稱，然後在 definitions/ 後方加上 .sqlx。例如：definitions/sample-operation.sqlx。

    檔名只能包含數字、字母、連字號和底線。

  5. 點選「建立檔案」。

定義自訂 SQL 作業

您可以在 type: operations 的 SQLX 檔案中定義自訂 SQL 作業。您可以在 operations 檔案中編寫任何 BigQuery SQL 陳述式。Dataform 會在 BigQuery 中執行自訂 SQL 作業，且不會進行修改。

您可以在一個 SQLX 檔案中定義多個自訂 SQL 作業。 BigQuery 會在相同環境中執行檔案中的所有作業，並透過半形分號 (;) 連結所有作業，建立要執行的 SQL。

使用開放原始碼 Dataform 架構搭配 BigQuery 以外的資料倉儲時，如要定義多項自訂 SQL 作業，請以 --- 分隔作業。

如要在專屬 SQLX 檔案中定義自訂 SQL 作業，請按照下列步驟操作：

  1. 在開發工作區中，選取自訂作業定義的 SQLX 檔案。

  2. 在檔案中輸入下列程式碼片段：

    config { type: "operations" }

  3. 在 config 區塊外，編寫 SQL 作業。

  4. 選用：按一下「格式」。

下列程式碼範例顯示在 operations 檔案中定義的多個自訂 SQL 作業：

config { type: "operations" }

DELETE FROM dataset.table WHERE country = 'GB';

DELETE FROM dataset.table WHERE country = 'FR';

下列程式碼範例顯示手動建立檢視區塊的自訂 SQL 作業：

config { type: "operations" }
CREATE OR REPLACE VIEW dataset.table AS (SELECT 1 AS TEST)

建立可參照的輸出資料表

您可以在自訂 SQL 作業中手動建立資料表，供其他指令碼參照。如要建立可供其他指令碼使用的資料表，您必須宣告作業有輸出內容。

如要讓輸出資料表的名稱與 operations 檔案的名稱相符，可以在 CREATE 作業中使用 self 函式。

如要在自訂作業中建立資料表，並提供給其他指令碼使用，請按照下列步驟操作：

  1. 在開發工作區中，選取自訂作業定義的 SQLX 檔案。

  2. 在 SQLX 檔案中，輸入下列程式碼片段：

    config {
 type: "operations",
 hasOutput: true
}

  3. 如要讓輸出資料表的名稱與檔案名稱相符，請使用 self 函式，以以下格式撰寫 SQL CREATE 作業：

    CREATE OR REPLACE TABLE ${self()} AS (CUSTOM_SQL_QUERY)

    將 CUSTOM_SQL_QUERY 替換為資料表定義 SQL SELECT 陳述式。

  4. 選用：按一下「格式」。

參照自訂 SQL 作業輸出資料表

  • 如要在不同資料表的 SQLX 定義中參照自訂 SQL 作業輸出資料表，請在 ref 函式中輸入輸出資料表檔案名稱。

下列程式碼範例顯示 custom_SQL_operation_table.sqlx 檔案中的自訂 SQL 作業，該作業會建立名為 custom_SQL_operation_table 的可參照資料表：

// filename is custom_SQL_operation_table.sqlx
config {
type: "operations",
hasOutput: true
}
CREATE OR REPLACE VIEW ${self()} AS (SELECT 1 AS TEST)

以下程式碼範例說明如何在資料表定義 SQLX 檔案中參照 custom\_SQL\_operation\_table table：

config { type: "table" }
SELECT * FROM ${ref("custom_SQL_operation_table")}

建立空白表格

您可能想建立空白表格，讓其他服務填入資料。您可以使用 CREATE TABLE 函式，在自訂 SQL 作業中建立空白資料表。如要在其他工作流程動作定義 (例如資料表和檢視區塊) 中參照空白資料表，您可以將 hasOutput:true 屬性新增至空白資料表作業的 config 區塊。

  • 如要建立空白資料表，請在 type: "operations" 檔案中使用 CREATE TABLE 函式，格式如下：
config {
  type: "operations",
  hasOutput: true  // optional, lets you reference the empty table
}

CREATE TABLE ${self()} (

)

下列程式碼範例顯示自訂 SQL 作業，可建立含有整數和字串資料欄的空白資料表。建立的空白表格無法供其他工作流程動作參照：

config {
  type: "operations"
}

CREATE TABLE ${self()} (
  x INT64,
  y STRING
)

後續步驟