标准 SQL 用户定义函数

BigQuery 支持用户定义函数 (UDF)。借助 UDF,您可以使用其他 SQL 表达式或 JavaScript 创建函数。这些函数接受输入列并执行操作,然后以值的形式返回这些操作的结果。如需了解旧版 SQL 中的用户定义函数,请参阅旧版 SQL 中的用户定义函数

UDF 可以是永久性的也可以是临时性的。您可以跨多个查询重复使用永久性 UDF,但您只能在单个查询中使用临时性 UDF。如需了解永久性 UDF,请参阅 CREATE FUNCTION 文档

UDF 语法

BigQuery 中的临时性用户定义函数使用下述语法:

CREATE { [TEMPORARY | TEMP] FUNCTION | OR REPLACE [TEMPORARY | TEMP] FUNCTION |
    [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 { TEMPORARY | TEMP } FUNCTION。 它创建一个新的临时函数。函数可包含零个或多个 named_parameter。创建临时性 UDF 时必须包含 TEMPORARYTEMP
  • 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 表达式。
  • AS javascript_code。它指定 JavaScript 函数的定义。javascript_code 是一个字符串字面量。

JavaScript UDF 结构

使用以下结构创建 JavaScript UDF。

CREATE  { TEMPORARY | TEMP }  FUNCTION 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。例如:

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(x, y) as product,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

您可以将一个 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 中的 SQL 类型编码

支持的 JavaScript UDF 数据类型

对于 JavaScript UDF,BigQuery 支持以下数据类型:

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • NUMERIC
  • STRING
  • STRUCT
  • TIMESTAMP

JavaScript 中的 SQL 类型编码

有些 SQL 类型可直接映射到 JavaScript 类型,但有些则不行。

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

BigQuery 不支持将 INT64 用作 JavaScript UDF 中的返回类型。在这种情况下,JavaScript 函数体可以返回 JavaScript Number 或 String。随后,BigQuery 将其中任一类型转换为 INT64

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 代码用引号引起来。对于一行简单的代码段,可使用带标准引号的字符串:

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

SQL UDF 结构

使用以下语法创建 SQL UDF:

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

模板化 SQL UDF 参数

在调用函数时,模板化参数可以匹配多个参数类型。如果函数签名包含模板化参数,则 BigQuery 允许函数调用将多个参数类型之一传递给函数。

SQL 用户定义的函数签名可以包含以下模板化 param_type 值:

  • ANY TYPE。该函数将接受此参数的任何类型的输入。如果多个参数具有 ANY TYPE 类型,则 BigQuery 不会在创建函数时强制执行这些参数之间的任何关系。但是,向函数传递与函数定义不兼容类型的参数将导致调用时出错。

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

包括外部库

可使用 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 或更小。
  • JavaScript UDF 可能超时,导致查询无法完成。超时可短至 5 分钟,但根据多种因素而有所不同,包括函数占用的用户 CPU 时间以及 JS 函数的输入和输出大小。
  • 查询作业最多可包含 50 个 JavaScript UDF 资源(内嵌代码 blob 或外部文件)。
  • 每个内嵌代码 blob 的最大大小限额为 32 KB。
  • 每个外部代码资源的最大大小限额为 1 MB。

限制

  • 对于临时 UDF,function_name 不能包含英文句点。
  • 不支持 DOM 对象 WindowDocumentNode 以及需要这些对象的函数。
  • 不支持依赖原生代码的 JavaScript 函数。
  • JavaScript 中的按位运算仅处理最高有效 32 位。
  • 由于其不确定性,调用用户定义函数的查询无法使用缓存结果。
  • 无法在 UDF 中引用表。
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面