旧版 SQL 中的用户定义函数

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

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

如需了解标准 SQL 中的用户定义函数,请参阅标准 SQL 中的用户定义函数

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”属性。例如:

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

输出架构

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

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

以下代码示例展示了输出架构中记录的语法。每个输出字段都需要 nametype 属性。嵌套字段必须还包含一个 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 和网页界面

您可以使用 BigQuery 网页界面来添加 UDF,然后使用这些函数来运行查询。

前提条件

要使用 BigQuery 网页界面,您的帐号必须有权访问 Google Cloud Platform Console 中已启用 BigQuery 的项目。

  1. 如果您之前未使用过 GCP Console,请转至此控制台,接受服务条款,并新建一个项目。

  2. 导航到 BigQuery 网页界面

添加 UDF

  1. 点击编写查询 (COMPOSE QUERY) 按钮。

  2. 点击 UDF 编辑器 (UDF Editor) 标签以添加 UDF。

  3. 复制以下代码并粘贴到 UDF 编辑器 (UDF Editor) 文本区域中:

    // The UDF
    function urlDecode(row, emit) {
      emit({title: decodeHelper(row.title),
            requests: row.num_requests});
    }
    
    // Helper function for 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
    );
    

在网页界面中运行查询

  • 点击查询编辑器标签。

  • 复制以下查询并粘贴到查询编辑器文本区域中。

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

    以上查询查找的是从 2015 年 4 月起访问量最大且标题中包含 cédille 字符 (ç) 的法语维基百科文章。

  • 点击运行查询 (RUN QUERY) 按钮。查询结果会显示在按钮下方。

引用 Cloud Storage 中的代码

在上面的示例中,您将 UDF 直接添加到 BigQuery 网页界面中。此外,您也可以将全部或部分 JavaScript 代码存储在 Cloud Storage 中。无论 UDF 代码位于何处,您都必须使用代码中的 bigquery.defineFunction 调用来注册每个 UDF。bigquery.definefunction 调用可以在 BigQuery 网页界面或远程代码资源中提供。远程源文件必须以“.js”为扩展名。

例如,您可以将第三方库、您自己的 UDF 代码和 UDF 注册函数调用保留在单独的文件中。这些文件将作为单独的外部资源加载到您的查询中。

在 BigQuery 网页界面中引用外部 UDF

  1. 点击文本区域下方的显示选项按钮。

  2. 点击 UDF 源 URI (UDF Source URIs) 标题旁边的编辑按钮。

  3. 对于每个远程源文件,点击添加 UDF 源 URI (Add UDF Source URI) 按钮并输入 Google Cloud Storage URI。

    如果您想使用外部 UDF 来尝试前一网址解码示例,请将 bigquery-sandbox-udf/url_decode.js 粘贴到 URI 字段中。完成这些步骤后,请务必移除 UDF 编辑器 (UDF Editor) 的内容。

  4. 点击确定按钮。

随后,您可以切换到查询编辑器标签,按照与上例相同的步骤来在查询中使用此 UDF。

通常情况下,使用外部 UDF 时,您还可以在 UDF 编辑器 (UDF Editor) 文本区域中添加其他 JavaScript 代码,只要在网页界面或外部文件内的 defineFunction 块中注册了其他 UDF 即可。

您还可以使用 bq 命令行工具引用 Cloud Storage 中存储的 UDF。如需了解详情,请参阅 UDF 和 bq 命令行工具

返回页首

UDF 和 bq 命令行工具

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

要运行包含 UDF 的查询,请使用以下语法:

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

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

创建 UDF

UDF 可以存储在 Cloud Storage 中,也可以作为本地文本文件来存储。例如,要存储 urlDecode UDF(请参阅 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)"

UDF 和 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 元素。每个元素可以包含 inlineCoderesourceUri 部分。

示例

以下 JSON 示例演示了一个引用两个 UDF 资源的查询请求:一个内嵌代码 blob 和一个要从 Cloud Storage 中读取的 lib.js 文件。在此示例中,myFuncmyFunc 注册调用由 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 对象 WindowDocumentNode 以及需要这些对象的函数。
  • 不支持依赖原生代码的 JavaScript 函数。
  • JavaScript 中的按位运算仅处理最高有效 32 位。
  • 由于调用用户定义函数的查询存在不确定性,因此此类查询无法使用缓存结果。

返回页首

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

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