標準 SQL 使用者定義的函式

BigQuery 支援使用者定義的函式 (UDF)。UDF 可讓您使用其他 SQL 運算式或 JavaScript 建立函式,這些函式會接受輸入資料欄並執行各項動作,再以資料值的形式傳回這些動作的結果。如需舊版 SQL 使用者定義函式的相關資訊,請參閱舊版 SQL 中的使用者定義函式

UDF 可為永久性或暫時性。您可以在多個查詢中重複使用永久性 UDF,但只能將暫時性 UDF 用於單一查詢。

UDF 語法

如要建立永久性 UDF,請使用下列語法:

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [`project_name`.]dataset_name.function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

如要建立暫時性 UDF,請使用下列語法:

CREATE [OR REPLACE] {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 { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }:建立或更新函式。如要取代任何名稱相同的現有函式,請使用 OR REPLACE 關鍵字。如果已有名稱相同的函式,而您希望將查詢視為成功執行且不採取任何動作,請使用 IF NOT EXISTS 子句。
  • 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 運算式。
  • [OPTIONS (library = library_array)]:如果是 JavaScript UDF,請指定要加入函式定義的 JavaScript 程式庫陣列。
  • AS javascript_code:指定 JavaScript 函式的定義。javascript_code 為字串常值。

如要刪除永久性使用者定義函式,請使用下列語法:

DROP FUNCTION [IF EXISTS] [`project_name`.]dataset_name.function_name

暫時性使用者定義函式會在查詢結束後立即失效,因此除非是用於指令碼程序中,這類函式不支援 DROP FUNCTION 陳述式。

SQL UDF 結構

使用下列語法建立 SQL UDF:

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

範本 SQL UDF 參數

param_type = ANY TYPE 的範本參數可以在呼叫函式時比對多個引數類型。

  • 如有多項參數的類型均為 ANY TYPE,則 BigQuery 不會強制在這些引數之間建立任何類型的關係。
  • 函式傳回類型不得為 ANY TYPE。您必須省略傳回類型,也就是由系統依據 sql_expression 自動決定類型,或指定明確的類型。
  • 如果您傳送的函式引數類型與函式定義不相容,系統會在函式呼叫時產生錯誤。

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

JavaScript UDF 結構

您可以使用以下結構建立 JavaScript UDF。

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]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 的結果當做輸入值傳送至其他 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 UDF 資料類型一節。

支援的 JavaScript UDF 資料類型

有些 SQL 類型可以直接對應至 JavaScript 類型,其他類型則不能。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 不支援 64 位元整數類型,因此 JavaScript UDF 也不支援使用 INT64 的輸入類型。請改用 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 """
  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
"""
    // 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 與網頁版 UI

您可以透過 BigQuery 網頁版 UI,使用一或多個 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 內。
  • 舊版 SQL 查詢包含使用者定義函式 (UDF) 時的並行頻率限制 - 6 個並行查詢。
  • 就包含 UDF 的舊版 SQL 查詢而言,並行頻率限制同時適用於互動式查詢及批次查詢,包含 UDF 的互動式查詢也會計入互動式查詢的並行頻率限制。不過,這項限制不適用於標準 SQL 查詢。

  • 查詢工作中內嵌程式碼 blob 或外部檔案等 JavaScript UDF 資源的數量上限 - 50 項。
  • 每個內嵌程式碼 blob 的大小上限 - 32 KB。
  • 每項外部程式碼資源的大小上限 - 1 MB。

以下限制適用於永久性使用者定義函式
  • 函式名稱的長度上限 - 256 個字元
  • 引數的數量上限 - 256 個
  • 引數名稱的長度上限 - 128 個字元
  • 使用者定義函式參考鏈深度上限 - 16 層
  • 引數或 STRUCT 類型輸出內容的深度上限 - 15 層
  • 引數或各個 UDF STRUCT 類型輸出內容中的欄位數量上限 - 1,024 個
  • 每項查詢的不重複 UDF 加資料表參照數量上限 - 1,000 項 完全展開之後,每個 UDF 最多可以參照 1,000 個經過合併的不重複資料表和 UDF。
  • CREATE FUNCTION 陳述式中的 JavaScript 資料庫數量上限 - 50 個
  • 加入的 JavaScript 資料庫路徑長度上限 - 5,000 個字元
  • 每個 UDF 的更新頻率上限 - 每 10 秒 5 次 函式建立完成之後,您可以更新每個函式,頻率上限為每 10 秒 5 次。
  • 每個內嵌程式碼 blob 的大小上限為 32 KB。
  • 每項 JavaScript 程式碼資源的大小上限為 1 MB。

限制

以下限制適用於暫時性和永久性的使用者定義函式:

  • 系統不支援 WindowDocumentNode 等 DOM 物件,以及需要使用這些物件的函式。
  • 系統不支援需要使用原生程式碼的 JavaScript 函式。
  • JavaScript UDF 可能會逾時,而造成您的查詢無法完成。逾時可能只有短短 5 分鐘,但可能因多種因素而異,包括您的函式占用多少使用者 CPU 作業時間,以及對於 JS 函式的輸入和輸出有多大。
  • 叫用 JavaScript 使用者定義函式的查詢具有非確定性本質,因此無法使用快取結果。
  • JavaScript 中的位元作業僅處理最重要的 32 位元。
  • 您無法在 UDF 中參照資料表。

以下限制適用於永久性使用者定義函式:

  • 每個資料集只能含有一個使用相同名稱的永久性 UDF。不過,您可以在同一資料集中建立與資料表同名的 UDF。
  • 從其他永久性 UDF 參照某個永久性 UDF 時,您必須使用資料集來限定名稱。例如:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
  • 從邏輯檢視表參照永久性 UDF 時,您必須使用專案和資料集來完全限定名稱。例如:
    CREATE VIEW mydataset.sample_view AS SELECT `my-project`.mydataset.referencedFunction();

以下限制適用於暫時性使用者定義函式。

  • 當您建立暫時性 UDF 時,function_name 不得包含句號。
  • 邏輯檢視表和永久性 UDF 不得參照暫時性 UDF。