添加自定义 SQL 操作

本文档介绍如何在专用 SQLX 文件中定义自定义 SQL 操作。

Dataform 可以执行不适合发布表或编写断言的 Dataform 模型的自定义 SQL 操作。您可以为 Dataform 定义要在 BigQuery 中执行的自定义 SQL 命令。

准备工作

  1. 在 Google Cloud 控制台中，转到 Dataform 页面。

    转到 Dataform 页面

  2. 选择或创建代码库。

  3. 选择或创建开发工作区。

所需的角色

如需获取定义自定义 SQL 操作所需的权限，请让管理员向您授予工作区的 Dataform Editor (roles/dataform.editor) IAM 角色。如需详细了解如何授予角色，请参阅管理访问权限。

您也可以通过自定义角色或其他预定义角色来获取所需的权限。

为自定义操作定义创建文件

将自定义操作定义 SQLX 文件存储在 definitions/ 目录中。如需在 definitions/ 目录中创建新的 SQLX 文件，请按以下步骤操作：

  1. 转到开发工作区。
  2. 在 Files 窗格的 definitions/ 旁边，点击 More 菜单。
  3. 点击创建文件。

  4. 在添加文件路径字段中，输入文件的名称，在 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 操作中创建空表。为了能够在其他 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
)

后续步骤