BigQuery 支持用户定义函数 (UDF)。借助 UDF,您可以使用其他 SQL 表达式或 JavaScript 创建函数。这些函数接受输入列并执行操作,然后以值的形式返回这些操作的结果。如需了解旧版 SQL 中的用户定义函数,请参阅旧版 SQL 中的用户定义函数。
UDF 可以是永久性的也可以是临时性的。您可以在多个查询间重复使用永久性 UDF,而在单个查询中使用临时性 UDF。
UDF 语法
如需创建永久性 UDF,请使用以下语法:
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] [[project_name.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
如需创建临时性 UDF,请使用以下语法:
CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS] function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
named_parameter: param_name param_type sql_function_definition: AS (sql_expression) javascript_function_definition: [determinism_specifier] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code determinism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }
此语法由以下部分组成:
CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }:创建或更新函数。如需替换名称相同的任何现有函数,请使用
OR REPLACE
关键字。如需在存在同名函数的情况下将查询视为成功,而不执行任何操作,请使用IF NOT EXISTS
子句。project_name 是您要在其中创建函数的项目的名称。默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号
`
将项目名称引起来(示例:`google.com:my_project`
)。dataset_name 是您要在其中创建函数的数据集的名称。默认为请求中的
defaultDataset
。named_parameter。由英文逗号分隔的
param_name
和param_type
对组成。param_type
的值为 BigQuery 数据类型。对于 SQL UDF,param_type
的值也可以是ANY TYPE
。determinism_specifier。仅适用于 JavaScript 的用户定义的函数。向 BigQuery 提供关于是否可缓存查询结果的提示。可以是下列值之一:
DETERMINISTIC
:传递相同的参数时,函数始终返回相同的结果。查询结果可能可缓存。例如,如果函数add_one(i)
始终返回i + 1
,则该函数是确定性函数。NOT DETERMINISTIC
:在传递相同的参数时,函数并不总是返回相同的结果,因此无法缓存。例如,如果add_random(i)
返回i + rand()
,则该函数不是确定性的,并且 BigQuery 不会使用缓存的结果。如果调用的所有函数均为 DETERMINISTIC,则 BigQuery 将尝试缓存结果,除非由于其他原因无法缓存结果。如需了解详情,请参阅使用缓存的查询结果。
[RETURNS data_type]。它指定函数返回的数据类型。
- 如果函数是使用 SQL 定义的,则
RETURNS
为可选子句。如果省略RETURNS
子句,则当查询调用函数时,BigQuery 会通过 SQL 函数体推断出函数的结果类型。 - 如果函数是使用 JavaScript 定义的,则需要
RETURNS
子句。如需详细了解可使用的data_type
值,请参阅支持的 JavaScript UDF 数据类型。
- 如果函数是使用 SQL 定义的,则
AS (sql_expression):它指定用于定义函数的 SQL 表达式。
[OPTIONS (library = library_array)]。对于 JavaScript UDF,请指定一个包含要加入函数定义的 JavaScript 库的数组。
AS javascript_code:指定 JavaScript 函数的定义。
javascript_code
为字符串字面量。
如需删除永久性的用户定义函数,请使用以下语法:
DROP FUNCTION [IF EXISTS] [[project_name.]dataset_name.]function_name
临时性 UDF 会在查询完成后立即失效,因此 DROP FUNCTION
语句仅支持脚本和
过程中的临时性 UDF。
SQL UDF
使用以下语法创建 SQL UDF:
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (sql_expression) named_parameter: param_name param_type
模板化 SQL UDF 参数
在调用函数时,param_type
为 ANY TYPE
的模板化参数可以匹配多个参数类型。
- 如果多个参数的类型为
ANY TYPE
,则 BigQuery 不会在这些参数之间强制执行任何类型关系。 - 函数返回类型不能是
ANY TYPE
。它要么省略(即系统根据sql_expression
自动确定),要么是某个明确的类型。 - 向函数传递与函数定义不兼容类型的参数将导致调用时错误。
SQL UDF 示例
以下示例显示了使用 SQL 函数的 UDF。
CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);
WITH numbers AS
(SELECT 1 as val
UNION ALL
SELECT 3 as val
UNION ALL
SELECT 4 as val
UNION ALL
SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;
+-----+--------+
| val | result |
+-----+--------+
| 1 | 2.5 |
| 3 | 3.5 |
| 4 | 4 |
| 5 | 4.5 |
+-----+--------+
以下示例展示了使用模板化参数的 SQL UDF。生成的函数接受各种类型的参数。
CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
(x + 4) / y
);
SELECT addFourAndDivideAny(3, 4) AS integer_output,
addFourAndDivideAny(1.59, 3.14) AS floating_point_output;
+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75 | 1.7802547770700636 |
+----------------+-----------------------+
以下示例所示的 SQL UDF 使用模板化参数返回任何类型的数组的最后一个元素。
CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
arr[ORDINAL(ARRAY_LENGTH(arr))]
);
SELECT
names[OFFSET(0)] AS first_name,
lastArrayElement(names) AS last_name
FROM (
SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
SELECT ['Marie', 'Skłodowska', 'Curie']
);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred | Rogers |
| Marie | Curie |
+------------+-----------+
以下示例所示的 SQL UDF 使用标量子查询统计用户表中具有给定年龄的用户数。
CREATE TEMP TABLE users
AS SELECT 1 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;
CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS ((SELECT COUNT(1) FROM users WHERE age = userAge));
SELECT countUserByAge(10) AS count_user_age_10,
countUserByAge(20) AS count_user_age_20,
countUserByAge(30) AS count_user_age_30;
+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
| 2 | 0 | 1 |
+-------------------+-------------------+-------------------+
JavaScript UDF
使用以下结构创建 JavaScript UDF。
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) RETURNS data_type [DETERMINISTIC | NOT DETERMINISTIC] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code
支持的 JavaScript UDF 数据类型
有些 SQL 类型可直接映射到 JavaScript 类型,但有些则不行。 BigQuery 采用下列方式表示类型:
BigQuery 数据类型 | JavaScript 数据类型 |
---|---|
ARRAY | ARRAY |
BOOL | BOOLEAN |
BYTES | base64 编码的 STRING |
FLOAT64 | NUMBER |
NUMERIC、BIGNUMERIC(预览版) | 如果 NUMERIC 或 BIGNUMERIC 值可精确表示为 IEEE 754 浮点值,且没有小数部分,则系统会将其编码为数字。这些值应在 [-253, 253] 范围内,否则,将其编码为字符串。 |
STRING | STRING |
STRUCT | OBJECT,其中每个 STRUCT 字段都是命名字段 |
TIMESTAMP | DATE,其微秒字段包含时间戳的 microsecond 部分 |
DATE | DATE |
由于 JavaScript 不支持 64 位整数类型,因此系统不支持将 INT64
用作 JavaScript UDF 的输入类型。请改为使用 FLOAT64
将整数值表示为数字,或使用 STRING
将整数值表示为字符串。
BigQuery 支持将 INT64
用作 JavaScript UDF 中的返回类型。在这种情况下,JavaScript 函数体可以返回 JavaScript 数字或字符串。然后,BigQuery 会将其中任一种类型转换为 INT64
。
如果 JavaScript UDF 的返回值为 Promise
,则 BigQuery 会等待 Promise
得到解决。如果 Promise
处于已履行状态,则 BigQuery 会返回其结果。如果 Promise
处于遭拒状态,则 BigQuery 会返回错误。
引用规则
必须将 JavaScript 代码用引号括起来。对于一行简单的代码段,可使用带标准英文引号的字符串:
CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;
+-----------+-----------+
| val | result |
+-----------+-----------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
+-----------+-----------+
如果代码段包含英文引号或由多行组成,请使用三引号块:
CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
var d = new Date();
if (d.getHours() < 12) {
return 'Good Morning, ' + a + '!';
} else {
return 'Good Evening, ' + a + '!';
}
""";
SELECT customGreeting(names) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;
+-----------------------+
| everyone |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max! |
| Good Morning, Jakob! |
+-----------------------+
包括 JavaScript 库
您可以使用 OPTIONS
部分扩展 JavaScript UDF。
此部分可让您指定 UDF 的外部代码库。
CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (
library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
)
AS
"""
// Assumes 'doInterestingStuff' is defined in one of the library files.
return doInterestingStuff(a, b);
""";
SELECT myFunc(3.14, 'foo');
在上述示例中,lib1.js
和 lib2.js
中的代码可用于 UDF 的 [external_code]
部分中的任何代码。
JavaScript UDF 示例
CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
return x*y;
""";
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;
+-----+-----+--------------+
| x | y | product |
+-----+-----+--------------+
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
+-----+-----+--------------+
您可以将 UDF 的结果作为输入传递给另一个 UDF。例如:
CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
return x*y;
""";
CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
return x/2;
""";
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x,
y,
multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;
+-----+-----+--------------+
| x | y | half_product |
+-----+-----+--------------+
| 1 | 5 | 1.25 |
| 2 | 10 | 5 |
| 3 | 15 | 11.25 |
+-----+-----+--------------+
以下示例对给定 JSON 字符串中名为 foo
的所有字段的值进行求和。
CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js AS """
function SumFoo(obj) {
var sum = 0;
for (var field in obj) {
if (obj.hasOwnProperty(field) && obj[field] != null) {
if (typeof obj[field] == "object") {
sum += SumFoo(obj[field]);
} else if (field == "foo") {
sum += obj[field];
}
}
}
return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";
WITH Input AS (
SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
SELECT NULL, 4 AS foo UNION ALL
SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
TO_JSON_STRING(t) AS json_row,
SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
+---------------------------------------------------------------------+---------+
| json_row | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10} | 14.14 |
| {"s":null,"foo":4} | 4 |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59 |
+---------------------------------------------------------------------+---------+
如需详细了解 BigQuery 数据类型如何映射到 JavaScript 类型,请参阅支持的 JavaScript UDF 数据类型
JavaScript UDF 的最佳做法
对输入进行预先过滤
如果在将输入传递到 JavaScript UDF 之前可以方便地进行过滤以减少其数量,您的查询速度将更快,费用会更低。
避免持久的可变状态
不要在 JavaScript UDF 调用之间存储或访问可变状态。
高效使用内存
在 JavaScript 处理环境中,每个查询可使用的内存有限。 累积太多本地状态的 JavaScript UDF 查询可能会因内存耗尽而失败。
运行具有 UDF 的查询
使用 Cloud Console
您可以通过 Cloud Console 来运行使用一个或多个 UDF 的查询。
- 点击编写新查询。
-
在查询编辑器窗格中,输入 UDF 语句。例如:
CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64) RETURNS FLOAT64 LANGUAGE js AS """ return x*2; """;
-
在 UDF 语句下,输入 SQL 查询。例如:
SELECT timesTwo(numbers) AS doubles FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
-
点击运行查询。查询结果显示在按钮下方。
使用 bq
命令行工具
您可以通过 Cloud SDK 使用 bq
命令行工具运行包含一个或多个 UDF 的查询。
使用以下语法运行具有 UDF 的查询:
bq query <statement_with_udf_and_query>
已获授权的 UDF
已获授权的 UDF 是有权访问特定数据集的 UDF。UDF 可以查询数据集中的表,即使调用 UDF 的用户无权访问这些表也是如此。
借助已获授权的 UDF,您可以与特定用户或群组共享查询结果,而无需为这些用户或群组授予底层表访问权限。例如,已获授权的 UDF 可以计算对数据的聚合,也可以查找表值并在计算中使用该值。
您可以使用 Google Cloud Console、REST API 或 bq
命令行工具为某个 UDF 授权:
控制台
转到 Cloud Console 中的 BigQuery 页面。
在导航面板的资源部分中,展开您的项目并选择数据集。
在详细信息面板中,点击为例程授权。
在为例程授权页面的为例程授权部分中,选择要为其授权的 UDF 的项目 ID、数据集 ID 和例程 ID。
点击添加授权。
API
调用
datasets.get
方法,提取您希望 UDF 访问的数据集。响应正文包含Dataset
资源的表示法。将以下 JSON 对象添加到
Dataset
资源中的access
数组:{ "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" } }
其中:
- DATASET_NAME 是包含 UDF 的数据集的名称。
- PROJECT_ID 是包含 UDF 的项目的 ID。
- ROUTINE_NAME 是 UDF 的名称。
使用修改后的
Dataset
表示法调用dataset.update
方法。
bq
使用
bq show
命令获取您希望 UDF 访问的数据集的 JSON 表示法。该命令的输出是Dataset
资源的 JSON 表示法。将结果保存到本地文件。bq show --format=prettyjson TARGET_DATASET > dataset.json
将 TARGET_DATASET 替换为 UDF 有权访问的数据集的名称。
修改该文件,将以下 JSON 对象添加到
Dataset
资源中的access
数组:{ "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" } }
其中:
- DATASET_NAME 是包含 UDF 的数据集的名称。
- PROJECT_ID 是包含 UDF 的项目的 ID。
- ROUTINE_NAME 是 UDF 的名称。
使用
bq update
命令更新数据集。bq update --source dataset.json TARGET_DATASET
已获授权的 UDF 示例
以下是创建和使用已获授权的 UDF 的端到端示例。
创建名为
private_dataset
和public_dataset
的两个数据集。如需详细了解如何创建数据集,请参阅创建数据集。运行以下语句以在
private_dataset
中创建名为private_table
的表:CREATE OR REPLACE TABLE private_dataset.private_table AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;
运行以下语句以在
public_dataset
中创建名为count_key
的表:UDF 包含一个针对private_table
的SELECT
语句。CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING) RETURNS INT64 AS ((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));
向用户授予针对
public_dataset
数据集的bigquery.dataViewer
角色。此角色包含bigquery.routines.get
权限,此权限可让用户调用函数。如需了解如何分配对数据集的访问权限控制,请参阅控制对数据集的访问权限。此时,用户有权调用
count_key
函数,但无法访问private_dataset
中的表。如果用户尝试调用该函数,则将收到类似于以下内容的错误消息:Access Denied: Table myproject:private_dataset.private_table: User does not have permission to query table myproject:private_dataset.private_table.
使用
bq
命令行工具运行show
命令,如下所示:bq show --format=prettyjson private_dataset > dataset.json
输出将保存到名为
dataset.json
的本地文件中。修改
dataset.json
以将下列 JSON 对象添加到access
数组中:{ "routine": { "datasetId": "public_dataset", "projectId": "PROJECT_ID", "routineId": "count_key" } }
将 PROJECT_ID 替换为
public_dataset
的项目 ID。使用
bq
命令行工具运行update
命令,如下所示:bq update --source dataset.json private_dataset
如需验证 UDF 是否有权访问
private_dataset
,用户可以运行以下查询:SELECT public_dataset.count_key('key1');
向 UDF 添加说明
如需向 UDF 添加说明,请按照以下步骤操作:
控制台
转到 Cloud Console 中的 BigQuery 页面。
在探索器面板中,展开您的项目和数据集,然后选择函数。
在详细信息窗格中,点击说明旁边的铅笔图标以修改说明文本。
在对话框中输入说明或修改现有说明。点击更新以保存新的说明文本。
或者,您也可以使用标准 SQL 查询和 OPTIONS
字段的 description
参数来更新说明。在查询编辑器框中,输入您的函数定义,然后添加下面一行:
OPTIONS (description="DESCRIPTION") AS """
将 DESCRIPTION 替换为您要添加的说明。
bq
您可以通过命令行使用 UDF 和 bq 命令行工具中的 bq query
语法来修改函数的说明。使用 --nouse_legacy_sql
或 --
use_legacy_sql=false
标志指定标准 SQL,然后输入您的函数定义。将下面一行添加到您的定义中,以在 OPTIONS
字段中设置 description
参数:
OPTIONS (description="DESCRIPTION") AS """
将 DESCRIPTION 替换为您要添加的说明。
限制
以下限制适用于用户定义的临时性和永久性函数:
- 不支持 DOM 对象
Window
、Document
、Node
以及需要这些对象的函数。 - 不支持依赖原生代码的 JavaScript 函数。
- JavaScript UDF 可能超时,导致查询无法完成。超时可能只有短短 5 分钟,但因多种因素而有所不同,包括函数占用的用户 CPU 时间以及 JavaScript 函数的输入和输出大小。
- JavaScript 中的按位运算仅处理最高有效 32 位。
- UDF 存在特定速率限制和配额限制。如需了解详情,请参阅 UDF 限制。
以下限制适用于用户定义的永久性函数:
- 每个数据集只能包含一个具有相同名称的永久性 UDF。但是,您可以创建与同一数据集内的某个表同名的 UDF。
- 从一个永久性 UDF 或逻辑视图引用其他永久性 UDF 时,您必须使用数据集来限定名称。例如:
CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
以下限制适用于临时性的用户定义函数。
- 创建临时性 UDF 时,
function_name
不能包含英文句点。 - 视图和永久性 UDF 不能引用临时性 UDF。