标准 SQL 用户定义函数

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:
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

此语法由以下部分组成:

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }:创建或更新函数。如需替换名称相同的任何现有函数,请使用 OR REPLACE 关键字。如需在存在同名函数的情况下将查询视为成功,而不执行任何操作,请使用 IF NOT EXISTS 子句。
  • named_parameter:由英文逗号分隔的 param_nameparam_type 对组成。param_type 的值为 BigQuery 数据类型。对于 SQL UDF,param_type 的值也可以是 ANY TYPE
  • [RETURNS data_type]:指定函数返回的数据类型。
    • 如果函数是使用 SQL 定义的,则 RETURNS 为可选子句。如果省略 RETURNS 子句,则当查询调用函数时,BigQuery 会通过 SQL 函数体推断出函数的结果类型。
    • 如果函数是使用 JavaScript 定义的,则需要使用 RETURNS 子句。如需详细了解可使用的 data_type 值,请参阅支持的 JavaScript UDF 数据类型
  • 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

临时性的用户定义函数会在查询完成后立即失效,因此不支持 DROP FUNCTION 语句(除非是在脚本过程中)。

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_typeANY 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     |
+------------+-----------+

JavaScript UDF 结构

使用以下结构创建 JavaScript UDF。

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_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 数据类型

有些 SQL 类型可直接映射到 JavaScript 类型,但有些则不行。 BigQuery 采用下列方式表示类型:

BigQuery 数据类型 JavaScript 数据类型
ARRAY ARRAY
BOOL BOOLEAN
BYTES base64 编码的 STRING
FLOAT64 NUMBER
NUMERIC 如果 NUMERIC 值可精确表示为 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.jslib2.js 中的代码可用于 UDF 的 [external_code] 部分中的任何代码。

UDF 和网页界面

您可以通过 BigQuery 网页界面来使用一个或多个 UDF 运行查询。

运行具有 UDF 的查询

  1. 点击 COMPOSE QUERY 按钮。
  2. 点击 Query Editor 标签。
  3. 点击 Show Options 按钮。
  4. 取消选中 Use Legacy SQL 复选框。
  5. Query Editor 文本区域中输入 UDF 语句。例如:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. 在 UDF 语句下面输入您的查询。例如:

    SELECT timesTwo(numbers) AS doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. 点击 RUN QUERY 按钮。查询结果会显示在按钮下方。

UDF 和 bq 命令行工具

您可以使用 Google Cloud SDK 中的 bq 命令行工具运行包含一个或多个 UDF 的查询。

使用以下语法运行具有 UDF 的查询:

bq query <statement_with_udf_and_query>

JavaScript UDF 的最佳做法

对输入进行预先过滤

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

避免持久的可变状态

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

高效使用内存

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

限制

  • 处理单行数据时 JavaScript UDF 输出的数据量约为 5 MB 或更少
  • 包含用户定义函数 (UDF) 的旧版 SQL 查询的并发速率限制为 6 个并发查询
  • 包含 UDF 的旧版 SQL 查询的并发速率限制涵盖交互式查询和批量查询。包含 UDF 的交互式查询也会计入交互式查询的并发速率限制。此限制不适用于标准 SQL 查询。

  • 查询作业中的 JavaScript UDF 资源(例如内嵌代码 blob 或外部文件)的数量上限为 50
  • 每个内嵌代码 blob 的大小上限 - 32 KB。
  • 每个外部代码资源的大小上限为 1 MB。

以下限制适用于永久性用户定义函数
  • 函数名称的长度上限为 256 个字符
  • 参数数量上限为 256
  • 参数名称的长度上限为 128 个字符
  • 用户定义函数引用链的深度上限为 16
  • 类型为 STRUCT 的参数或输出的深度上限为 15
  • 对于每个 UDF,类型为 STRUCT 的参数或输出中的字段数上限为 1024
  • 每次查询的唯一 UDF 和表引用数上限 - 1000 充分扩展后,每个 UDF 可引用的唯一表和 UDF 总数最高为 1000 个。
  • CREATE FUNCTION 语句中的 JavaScript 库数量上限为 50
  • 包含的 JavaScript 库路径的长度上限为 5000 个字符
  • 每个 UDF 的更新速率上限 - 5 次/10 秒 创建函数后,每个函数每 10 秒最多可更新 5 次。
  • 每个内嵌代码 blob 的大小上限为 32 KB
  • 每项 JavaScript 代码资源的大小上限为 1 MB

限制

以下限制适用于临时性和永久性的用户定义函数:

  • DOM 对象(WindowDocumentNode)以及需要这些对象的函数不受支持。
  • 依赖原生代码的 JavaScript 函数不受支持。
  • JavaScript UDF 可能超时,导致查询无法完成。超时可能只有短短 5 分钟,但因多种因素而有所不同,包括函数占用的用户 CPU 时间以及 JS 函数的输入和输出大小。
  • 由于调用 JavaScript 用户定义函数的查询存在不确定性,因此此类查询无法使用缓存结果。
  • JavaScript 中的按位运算仅处理最高有效 32 位。
  • 您无法在 UDF 中引用表。

以下限制适用于永久性的用户定义函数:

  • 每个数据集只能包含一个具有相同名称的永久性 UDF。但是,您可以创建与同一数据集内的某个表同名的 UDF。
  • 从一个永久性 UDF 引用其他永久性 UDF 时,您必须使用数据集来限定名称。例如:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
  • 从逻辑视图引用永久性 UDF 时,您必须使用项目和数据集来完全限定名称。例如:
    CREATE VIEW mydataset.sample_view AS SELECT `my-project`.mydataset.referencedFunction();

以下限制适用于临时性的用户定义函数。

  • 创建临时性 UDF 时,function_name 不能包含英文句点。
  • 逻辑视图和永久性 UDF 不能引用临时性 UDF。