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。