新增自訂 SQL 作業

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

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

事前準備

  1. 在 Google Cloud 控制台中，前往「Dataform」頁面。

    前往「Dataform」頁面

  2. 選取或建立存放區。

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

必要的角色

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

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

建立自訂作業定義檔案

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

  1. 前往開發工作區。
  2. 在「檔案」窗格中，點選 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 以外的資料倉儲，請使用 --- 分隔作業。

如要在專用 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 作業中建立空白資料表。如要在其他 SQL 工作流程物件定義 (例如資料表和檢視畫面) 中參照空資料表，您可以將 hasOutput:true 屬性新增至空資料表作業的 config 區塊。

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

CREATE TABLE ${self()} (

)

以下程式碼範例顯示自訂 SQL 作業，可建立包含整數和字串資料欄的空資料表。建立的空資料表無法由其他 SQL 工作流程物件參照：

config {
  type: "operations"
}

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

後續步驟