旧版 SQL 中的用户定义函数

本文档详细介绍了如何使用旧版 SQL 查询语法中的 JavaScript 用户定义函数。 BigQuery 的首选查询语法是 GoogleSQL。如需了解 GoogleSQL 中的用户定义函数，请参阅 GoogleSQL 用户定义的函数。

BigQuery 旧版 SQL 支持以 JavaScript 编写的用户定义函数 (UDF)。 UDF 类似于 MapReduce 中的“Map”函数：它使用单个行作为输入并输出零个或多个行。输出架构可能与输入架构有所不同。

如需了解 GoogleSQL 中的用户定义函数，请参阅 GoogleSQL 中的用户定义函数。

UDF 示例

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

返回页首

UDF 结构

function name(row, emit) {
  emit(<output data>);
}

BigQuery UDF 可作用于表的各行或子选择查询结果。UDF 具有两个正式参数：

row：输入行。
emit：BigQuery 用于收集输出数据的钩子。emit 函数采用一个参数：即表示单行输出数据的 JavaScript 对象。可以在诸如循环之类的部分中多次调用 emit 函数来输出多行数据。

以下代码示例展示了一个基本 UDF。

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

注册 UDF

您必须为函数注册一个名称，这样才能从 BigQuery SQL 调用此函数。注册的名称不必与您在 JavaScript 中使用的函数名称相匹配。

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

输入列

输入列名称必须与输入表或子查询中的列名称（或别名，如果适用）相匹配。

对于记录形式的输入列，您必须在输入列列表中指定要从记录中访问的叶级字段。

例如，如果您的记录存储的是某人的姓名和年龄：

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

姓名和年龄的输入说明符如下：

['person.name', 'person.age']

如果只使用了 ['person'] 而未指定 name 或 age，将会产生错误。

生成的输出将与此架构匹配；您将获得一个由 JavaScript 对象组成的数组，其中的每个对象都具有“name”和“age”属性 (Property)。例如：

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

输出架构

您必须向 BigQuery 提供 UDF 所生成记录的架构或结构（以 JSON 表示）。此架构可以包含任何受支持的 BigQuery 数据类型（包括嵌套记录）。支持的类型说明符如下：

布尔型
浮点型
整型
记录
字符串
时间戳

以下代码示例展示了输出架构中记录的语法。每个输出字段都需要 name 和 type 特性 (Attribute)。嵌套字段还必须包含一个 fields 特性。

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

每个字段都包含一个可选的 mode 特性，该特性支持以下值：

nullable：这是默认值，可以省略。
required：如果指定此值，则给定字段必须设为一个值，而不能是未定义的。
repeated：如果指定此值，则给定字段必须为数组。

传递给 emit() 函数的行必须与输出架构的数据类型相匹配。显示在输出架构中但在 emit 函数中被省略的字段将输出为 Null。

UDF 定义或引用

如果您愿意，可以在 bigquery.defineFunction 中以内嵌方式定义 UDF。例如：

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

也可以单独定义 UDF 并在 bigquery.defineFunction 中传递对该函数的引用。例如：

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

错误处理

如果在处理 UDF 期间引发异常或错误，则整个查询将会失败。您可以使用 try-catch 块来处理错误。例如：

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

运行具有 UDF 的查询

在旧版 SQL 中，您可以通过 bq 命令行工具或 BigQuery API 使用 UDF。Google Cloud 控制台不支持旧版 SQL 中的 UDF。

使用 bq 命令行工具

如需运行包含一个或多个 UDF 的查询，请在 Google Cloud CLI 中通过 bq 命令行工具指定 --udf_resource 标志。此标志的值可以是 Cloud Storage (gs://...) URI 或本地文件路径。如需指定多个 UDF 资源文件，请重复使用此标志。

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

bq query --udf_resource=<file_path_or_URI> <sql_query>

以下示例运行一个使用本地文件中所存储 UDF 的查询和一个同样存储在本地文件中的 SQL 查询。

创建 UDF

UDF 可以存储在 Cloud Storage 中，也可以作为本地文本文件来存储。例如，如需存储以下 urlDecode UDF，请先创建一个名为 urldecode.js 的文件，并将以下 JavaScript 代码粘贴到其中，然后保存此文件。

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

创建查询

也可以将查询存储在一个文件中，以防止命令行变得过于冗长。例如，您可以创建一个名为 query.sql 的本地文件并将以下 BigQuery 语句粘贴到此文件中。

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

保存文件后，您可以使用命令行引用该文件。

运行查询

在单独的文件中定义 UDF 和查询后，您可以在命令行中引用它们。例如，以下命令会运行保存为 query.sql 文件的查询并引用创建的 UDF。

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

使用 BigQuery API

configuration.query

使用 UDF 的查询必须包含 userDefinedFunctionResources 元素，以便提供要在查询中使用的代码（或代码资源位置）。提供的代码必须包含查询引用的任何 UDF 的注册函数调用。

代码资源

您的查询配置可以包含 JavaScript 代码 blob，以及对 Cloud Storage 中所存储 JavaScript 源文件的引用。

内嵌 JavaScript 代码 blob 将填充到 userDefinedFunctionResource 元素的 inlineCode 部分中。但是，将在多个查询中重复使用或引用的代码应保留在 Cloud Storage 中，并作为外部资源引用。

要从 Cloud Storage 引用 JavaScript 源文件，请将 userDefinedFunctionResource 元素的 resourceURI 部分设置为该文件的 gs:// URI。

查询配置可以包含多个 userDefinedFunctionResource 元素。每个元素可以包含 inlineCode 或 resourceUri 部分。

示例

以下 JSON 示例演示了一个引用两个 UDF 资源的查询请求：一个内嵌代码 blob 和一个要从 Cloud Storage 中读取的 lib.js 文件。在此示例中，myFunc 和 myFunc 注册调用由 lib.js 提供。

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

返回页首

最佳做法

开发 UDF

您可以使用我们的 UDF 测试工具测试和调试 UDF，而不会产生 BigQuery 费用。

预过滤输入

如果您的输入可以在传递到 UDF 之前轻松进行过滤，可能会加快查询速度并减少费用。

在运行查询这个示例中，我们以输入形式将子查询（而不是整个表）传递给 urlDecode。[fh-bigquery:wikipedia.pagecounts_201504] 表包含大约 56 亿行，如果我们对整个表运行 UDF，JavaScript 框架需要处理的行数将超过使用已过滤子查询时的 21 倍。

避免永久性的可变状态

不要跨 UDF 调用来存储或访问可变状态。以下代码示例描述了这种情形：

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

上面的示例将不会按预期运行，因为 BigQuery 会将您的查询分布到多个节点上。每个节点都有一个独立的 JavaScript 处理环境，可以将各个单独的 numRows 值累积起来。

高效使用内存

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

扩展 select 查询

您必须明确地列出从 UDF 中选择的列。不支持 SELECT * FROM <UDF name>(...)。

要检查输入行数据的结构，您可以使用 JSON.stringify() 发出字符串输出列：

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

返回页首

限制

处理单个行时，UDF 输出的数据量大约应为 5 MB 或更小值。
在特定项目中，每个用户只能同时运行大约 6 个 UDF 查询。如果收到的错误指示您超出了并发查询限制，请等待几分钟后重试。
UDF 可能发生超时，使查询无法完成。超时可短至只有 5 分钟，但具体取决于多种因素，包括函数占用的用户 CPU 时间量以及 JS 函数的输入和输出大小。
查询作业最多可包含 50 个 UDF 资源（内嵌代码 blob 或外部文件）。
每个内嵌代码 blob 的大小上限为 32 KB。要使用较大的代码资源，请将您的代码存储在 Cloud Storage 中并作为外部资源进行引用。
每项外部代码资源的大小上限为 1 MB。
所有外部代码资源的累计大小上限为 5 MB。

返回页首

限制

不支持 DOM 对象 Window、Document、Node 以及需要这些对象的函数。
不支持依赖原生代码的 JavaScript 函数。
JavaScript 中的按位运算仅处理最高有效 32 位。
由于调用用户定义函数的查询存在不确定性，因此此类查询无法使用缓存结果。

返回页首