添加自定义 SQL 操作

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

Dataform 可以执行不符合 Dataform 模型（发布表格或写入断言）的自定义 SQL 操作。您可以定义自定义 SQL 命令，以便 Dataform 在 BigQuery 中执行。

准备工作

  1. 在 Google Cloud 控制台中，进入 Dataform 页面。

    前往 Dataform 页面

  2. 选择或创建代码库。

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

所需的角色

如需获得定义自定义 SQL 操作所需的权限，请让您的管理员为您授予工作区的 Dataform Editor (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 以外的数据仓库搭配使用时定义多个自定义 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 文件中用于创建名为 custom_SQL_operation_table 的可引用表的自定义 SQL 操作：

// 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
)

后续步骤