用户定义的函数

借助用户定义的函数 (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 语句中调用该 UDF：

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_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

此示例生成以下输出：

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 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 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

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，除非该实体位于包含 UDF 的项目中。

例如，请参考以下语句：

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 编写的代码。与标准 SQL 查询相比，JavaScript UDF 通常会消耗更多的槽资源，从而降低作业性能。如果函数可以使用 SQL 表示，则通常最好将代码作为标准 SQL 查询作业运行。

以下示例显示了一个 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 浮点值，且没有小数部分，则系统会将此值编码为数字。这些值应在 [-2⁵³, 2⁵³] 范围内，否则，此值会被编码为字符串。
STRING	STRING
STRUCT	OBJECT，其中每个 STRUCT 字段都是命名字段
TIMESTAMP	DATE，其微秒字段包含时间戳的 `microsecond` 部分
DATE	DATE
JSON	JSON OBJECTS、ARRAYS 和 VALUES 会转换为等效的 JavaScript OBJECTS、ARRAYS 和 VALUES。 JavaScript 不支持 INT64 值。只有 [-2⁵³, 2⁵³] 范围内的 JSON 数字会进行精确转换。否则，数值会四舍五入，这可能会导致精度损失。

由于 JavaScript 不支持 64 位整数类型，因此系统不支持将 INT64 用作 JavaScript UDF 的输入类型。请改为使用 FLOAT64 将整数值表示为数字，或使用 STRING 将整数值表示为字符串。

BigQuery 支持将 INT64 用作 JavaScript UDF 中的返回类型。在这种情况下，JavaScript 函数体可以返回 JavaScript 数字或字符串。然后，BigQuery 会将其中任一种类型转换为 INT64。

如果 JavaScript UDF 的返回值为 Promise，则 BigQuery 会等待 Promise，直至 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.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

如果代码段包含引号或由多行组成，请使用三引号块：

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.js 和 lib2.js 中的代码可用于 UDF 的 [external_code] 部分中的任何代码。

JavaScript UDF 的最佳做法

对输入进行预先过滤

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

避免持久的可变状态

不要在 JavaScript UDF 调用之间存储或访问可变状态。例如，请避免以下格式：

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

高效使用内存

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

向例程授权

您可以将 UDF 授权为例程。借助授权例程，您可以与特定用户或群组共享查询结果，而无需为其授予生成结果的底层表的访问权限。例如，已获授权的例程可以计算对数据的聚合，也可以查找表值并在计算中使用该值。如需了解详情，请参阅已获授权的例程。

向 UDF 添加说明

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

控制台

在 Google Cloud 控制台中转到 BigQuery 页面。

转到 BigQuery
在探索器面板中，展开您的项目和数据集，然后选择函数。
在详细信息窗格中，点击 修改例程详情以修改说明文本。
在对话框中输入说明或修改现有说明。点击保存以保存新的说明文本。

SQL

如需更新函数的说明，请使用 CREATE FUNCTION DDL 语句重新创建函数，并在 OPTIONS 列表中设置 description 字段：

在 Google Cloud 控制台中，转到 BigQuery 页面。

转到 BigQuery

在查询编辑器中，输入以下语句：

CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

点击运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

创建自定义遮盖例程

您可以创建与自定义遮盖例程搭配使用的 UDF。自定义遮盖例程必须满足以下要求：

自定义遮盖例程必须是 SQL UDF。
在函数 OPTIONS 中，data_governance_type 选项必须设置为 DATA_MASKING。
自定义遮盖例程支持以下功能：
- REGEXP_REPLACE 字符串函数
- FARM_FINGERPINT 哈希函数
- MD5 哈希函数
- SHA1 哈希函数
- SHA256 哈希函数
- SHA512 哈希函数
- CAST 转换函数
- CONCAT 字符串函数
自定义遮盖例程可以接受无输入，也可以接受 BigQuery 数据类型中的一个输入，但 GEOGRAPHY 和 STRUCT 除外。自定义遮盖例程不支持 GEOGRAPHY 和 STRUCT。
不支持模板化 SQL UDF 参数。
提供输入时，输入和输出数据类型必须相同。
必须提供输出类型。
定义正文中不能引用其他 UDF、子查询、表或视图。
创建遮盖例程后，例程无法更改为标准函数。这意味着，如果 data_governance_type 选项设置为 DATA_MASKING，则您无法使用 DDL 语句或 API 调用更改 data_governance_type。

例如，将用户的社会保障号替换为 XXX-XX-XXXX 的遮盖例程可能如下所示：

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

以下示例使用 SHA256 函数对用户提供的 salt 进行哈希处理：

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

以下示例使用常量值遮盖 DATETIME 列：

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

创建自定义遮盖例程后，可将其作为遮盖规则提供，详见创建数据政策。

社区提供的函数

社区提供的 UDF 位于 bigquery-public-data.persistent_udfs 公共数据集和开源 bigquery-utils GitHub 代码库中。您可以在 Google Cloud 控制台中查看所有社区 UDF，方法是在探索器窗格中为 bigquery-public-data 项目添加星标，然后在该项目中展开嵌套的 persistent_udfs 数据集。

如果您想为此代码库中的 UDF 做一点贡献，请参阅为 UDF 做贡献以了解相关说明。

限制

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

不支持 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。