Dataform 可以执行不适合发布表或编写断言的 Dataform 模型的自定义 SQL 操作。您可以为 Dataform 定义要在 BigQuery 中执行的自定义 SQL 命令。
准备工作
所需的角色
如需获取定义自定义 SQL 操作所需的权限,请让管理员向您授予工作区的 Dataform Editor (roles/dataform.editor
) IAM 角色。如需详细了解如何授予角色,请参阅管理访问权限。
为自定义操作定义创建文件
将自定义操作定义 SQLX 文件存储在 definitions/
目录中。如需在 definitions/
目录中创建新的 SQLX 文件,请按以下步骤操作:
- 转到开发工作区。
- 在 Files 窗格的
definitions/
旁边,点击 More 菜单。 - 点击创建文件。
在添加文件路径字段中,输入文件的名称,在
definitions/
后面加上.sqlx
。例如definitions/sample-operation.sqlx
。文件名只能包含数字、字母、连字符和下划线。
点击创建文件。
定义自定义 SQL 操作
您可以在 type: operations
的 SQLX 文件中定义自定义 SQL 操作。您可以在 operations
文件中编写任何 BigQuery SQL 语句。Dataform 无需修改即可在 BigQuery 中运行自定义 SQL 操作。
您可以在一个 SQLX 文件中定义多个自定义 SQL 操作。BigQuery 会在同一上下文中运行文件中的所有操作,并通过用英文分号 ;
联接所有操作来创建已执行的 SQL。
如需在将开源 Dataform 框架与 BigQuery 以外的数据仓库一起使用时,定义多个自定义 SQL 操作,请使用 ---
分隔操作。
如需在专用 SQLX 文件中定义自定义 SQL 操作,请按以下步骤操作:
- 在开发工作区中,选择用于自定义操作定义的 SQLX 文件。
在文件中输入以下代码段:
config { type: "operations" }
在
config
代码块之外,编写您的 SQL 操作。可选:点击格式。
以下代码示例展示了在 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
函数。
如需在自定义操作中创建表并使其可用于其他脚本,请按以下步骤操作:
- 在开发工作区中,选择用于自定义操作的 SQLX 文件。
在 SQLX 文件中,输入以下代码段:
config { type: "operations", hasOutput: true }
可选:如需将输出表的名称与文件名匹配,请使用以下格式使用
self
函数编写 SQLCREATE
操作:CREATE OR REPLACE TABLE ${self()} AS (CUSTOM_SQL_QUERY)
将 CUSTOM_SQL_QUERY 替换为您的表定义 SQL
SELECT
语句。可选:点击格式。
引用自定义 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
)
后续步骤
- 如需了解如何使用 JavaScript 创建自定义 SQL 操作,请参阅使用 JavaScript 创建 SQL 工作流。
- 如需了解如何手动触发执行,请参阅触发器执行。
- 如需了解如何使用 BigQuery DML,请参阅使用数据操纵语言 (DML)。
- 如需详细了解
self
函数和其他 Dataform 内置函数,请参阅 Dataform API。