标准 SQL 用户定义函数

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

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

注意：在所有者之间共享时，可以安全调用永久性 UDF。UDF 不能变更数据、与外部系统通信，也无法将日志发送到 Google Cloud 的运维套件或类似应用。

如需创建 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 浮点值，且没有小数部分，则系统会将其编码为数字。这些值应在 [-2^53, 2^53] 范围内，否则，将其编码为字符串。
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.js 和 lib2.js 中的代码可用于 UDF 的 [external_code] 部分中的任何代码。

JavaScript UDF 的最佳做法

对输入进行预先过滤

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

避免持久的可变状态

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

高效使用内存

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

已获授权的 UDF

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

借助已获授权的 UDF，您可以与特定用户或群组共享查询结果，而无需为这些用户或群组授予底层表访问权限。例如，已获授权的 UDF 可以计算对数据的聚合，也可以查找表值并在计算中使用该值。

您可以使用 Google Cloud Console、REST API 或 bq 命令行工具为某个 UDF 授权：

控制台

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

   转到 BigQuery

2. 在导航面板的资源部分中，展开您的项目并选择数据集。

3. 在详细信息面板中，点击为例程授权。

4. 在为例程授权页面的为例程授权部分中，选择要为其授权的 UDF 的项目 ID、数据集 ID 和例程 ID。

5. 点击添加授权。

API

1. 调用 datasets.get 方法，提取您希望 UDF 访问的数据集。响应正文包含 Dataset 资源的表示法。

2. 将以下 JSON 对象添加到 Dataset 资源中的 access 数组：

   {
    "routine": {
      "datasetId": "DATASET_NAME",
      "projectId": "PROJECT_ID",
      "routineId": "ROUTINE_NAME"
    }
   }
   

   其中：

   • DATASET_NAME 是包含 UDF 的数据集的名称。
   • PROJECT_ID 是包含 UDF 的项目的 ID。
   • ROUTINE_NAME 是 UDF 的名称。

3. 使用修改后的 Dataset 表示法调用 dataset.update 方法。

bq

1. 使用 bq show 命令获取您希望 UDF 访问的数据集的 JSON 表示法。该命令的输出是 Dataset 资源的 JSON 表示法。将结果保存到本地文件。

   bq show --format=prettyjson TARGET_DATASET > dataset.json
   

   将 TARGET_DATASET 替换为 UDF 有权访问的数据集的名称。

2. 修改该文件，将以下 JSON 对象添加到 Dataset 资源中的 access 数组：

   {
    "routine": {
      "datasetId": "DATASET_NAME",
      "projectId": "PROJECT_ID",
      "routineId": "ROUTINE_NAME"
    }
   }
   

   其中：

   • DATASET_NAME 是包含 UDF 的数据集的名称。
   • PROJECT_ID 是包含 UDF 的项目的 ID。
   • ROUTINE_NAME 是 UDF 的名称。

3. 使用 bq update 命令更新数据集。

   bq update --source dataset.json TARGET_DATASET
   

注意：如果您通过调用 CREATE OR REPLACE FUNCTION 来修改 UDF，则必须重新为 UDF 授权。

已获授权的 UDF 示例

以下是创建和使用已获授权的 UDF 的端到端示例。

1. 创建名为 private_dataset 和 public_dataset 的两个数据集。如需详细了解如何创建数据集，请参阅创建数据集。

2. 运行以下语句以在 private_dataset 中创建名为 private_table 的表：

   CREATE OR REPLACE TABLE private_dataset.private_table
   AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;
   

3. 运行以下语句以在 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));
   

4. 向用户授予针对 public_dataset 数据集的 bigquery.dataViewer 角色。此角色包含 bigquery.routines.get 权限，此权限可让用户调用函数。如需了解如何分配对数据集的访问权限控制，请参阅控制对数据集的访问权限。

   注意：请考虑创建一个具有最小权限的自定义角色，而不是使用内置角色。如需了解详情，请参阅创建和管理自定义角色。

5. 此时，用户有权调用 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.
   

6. 使用 bq 命令行工具运行 show 命令，如下所示：

   bq show --format=prettyjson private_dataset > dataset.json
   

   输出将保存到名为 dataset.json 的本地文件中。

7. 修改 dataset.json 以将下列 JSON 对象添加到 access 数组中：

   {
    "routine": {
      "datasetId": "public_dataset",
      "projectId": "PROJECT_ID",
      "routineId": "count_key"
    }
   }
   

   将 PROJECT_ID 替换为 public_dataset 的项目 ID。

8. 使用 bq 命令行工具运行 update 命令，如下所示：

   bq update --source dataset.json private_dataset
   

9. 如需验证 UDF 是否有权访问 private_dataset，用户可以运行以下查询：

   SELECT public_dataset.count_key('key1');
   

向 UDF 添加说明

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

控制台

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

   转到 BigQuery

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

   注意：默认体验是预览版 Cloud Console。如果您点击隐藏预览版功能 (Hide preview features) 以转到正式版 Cloud Console，则改为执行以下步骤：在资源窗格中，选择您的函数。

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 对象 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。