标准 SQL 用户定义函数

借助用户定义的函数 (UDF),您可以使用 SQL 表达式或 JavaScript 代码创建函数。UDF 接受输入列、对输入执行操作并以值的形式返回这些操作的结果。

UDF 可以定义为永久性或临时性。永久性 UDF 可以重复用于多个查询,而临时性 UDF 仅存在于单个查询范围内。

如需创建 UDF,请使用 CREATE FUNCTION 语句。如需删除永久性的用户定义函数,请使用 DROP FUNCTION 语句。临时性 UDF 会在查询完成后立即失效。只有在脚本过程中,临时性 UDF 才支持 DROP FUNCTION 语句。

如需了解旧版 SQL 中的 UDF,请参阅旧版 SQL 中的用户定义函数

SQL UDF

以下示例创建一个名为 AddFourAndDivide 的临时 SQL UDF,并从 SELECT 语句中调用它:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
  RETURNS FLOAT64
  AS ((x + 4) / y);

SELECT val, AddFourAndDivide(val, 2)
  FROM UNNEST([2,3,5,8]) AS val;

此示例生成以下输出:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

下一个示例创建与永久性 UDF 相同的函数:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
  RETURNS FLOAT64
  AS ((x + 4) / y);

由于此 UDF 是永久性的,因此您必须为该函数(在此示例中为 mydataset)指定数据集。运行 CREATE FUNCTION 语句后,您可以从查询中调用该函数:

SELECT val, mydataset.AddFourAndDivide(val, 2)
  FROM UNNEST([2,3,5,8,12]) AS val;

模板化 SQL UDF 参数

调用该函数时,类型为 ANY TYPE 的参数可以匹配多个参数类型。

  • 如果多个参数的类型为 ANY TYPE,则 BigQuery 不会在这些参数之间强制执行任何类型关系。
  • 函数返回类型不能是 ANY TYPE。它要么省略(即系统根据 sql_expression 自动确定),要么是某个明确的类型。
  • 向函数传递与函数定义不兼容类型的参数将导致调用时出错。

以下示例展示了使用模板化参数的 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    |
+----------------+-----------------------+

下一个示例使用模板化参数返回任何类型的数组的最后一个元素:

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

SELECT lastArrayElement(x) as last_element
  FROM (SELECT [2,3,5,8,13] as x)

此示例生成以下输出:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

标量子查询

SQL UDF 可以返回标量子查询的值。标量子查询必须选择单个列。

以下示例所示的 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 |
+-------------------+-------------------+-------------------+

SQL 表达式中的默认项目

在 SQL UDF 的主体中,对 BigQuery 实体(例如表或视图)的任何引用都必须包含项目 ID,除非该实体位于运行 CREATE FUNCTION 语句的项目中。

例如,请参考以下语句:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM mydataset.mytable))

如果从 project1 运行此语句且 project1 中存在 mydataset.mytable,则此语句会成功。但是,如果从其他项目运行此语句,则此语句会失败。如需更正此错误,请在表引用中添加项目 ID:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM project1.mydataset.mytable))

您还可以引用位于不同于在其中创建函数的项目或数据集的其他项目或数据集中的实体:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM project2.another_dataset.another_table))

JavaScript UDF

JavaScript UDF 允许您从 SQL 查询调用以 JavaScript 编写的代码。

以下示例显示了一个 JavaScript UDF。JavaScript 代码使用原始字符串括住。

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
  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           |
+-----+-----+--------------+

以下示例对给定 JSON 字符串中名为 foo 的所有字段的值进行求和。

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS r"""
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    |
+---------------------------------------------------------------------+---------+

支持的 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 r"""
  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
r"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

在上述示例中,lib1.jslib2.js 中的代码可用于 UDF 的 [external_code] 部分中的任何代码。

JavaScript UDF 的最佳做法

对输入进行预先过滤

如果在将输入传递到 JavaScript UDF 之前可以方便地进行过滤以减少其数量,您的查询速度将更快,费用会更低。

避免持久的可变状态

不要在 JavaScript UDF 调用之间存储或访问可变状态。

高效使用内存

在 JavaScript 处理环境中,每个查询可使用的内存有限。 累积太多本地状态的 JavaScript UDF 查询可能会因内存耗尽而失败。

已获授权的 UDF

已获授权的 UDF 是有权访问特定数据集的 UDF。UDF 可以查询数据集中的表,即使调用 UDF 的用户无权访问这些表也是如此。

如需了解详情,请参阅创建授权函数

向 UDF 添加说明

如需向 UDF 添加说明,请按照以下步骤操作:

控制台

  1. 转到 Cloud Console 中的 BigQuery 页面。

    转到 BigQuery

  2. 探索器面板中,展开您的项目和数据集,然后选择函数。

  3. 详细信息窗格中,点击说明旁边的铅笔图标以修改说明文本。

  4. 在对话框中输入说明或修改现有说明。点击更新以保存新的说明文本。

或者,您也可以使用标准 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 对象 WindowDocumentNode 以及需要这些对象的函数。
  • 依赖原生代码的 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。