標準 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 資料類型

BigQuery 支援下列 JavaScript UDF 資料類型:

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

JavaScript 中的 SQL 類型編碼

有些 SQL 類型可以直接對應至 JavaScript 類型,其他則不能。

因為 JavaScript 不支援 64 位元整數類型,因此 JavaScript UDF 也不支援使用 INT64 的輸入類型。請改用 FLOAT64 將整數值以數字表示,或用 STRING 將整數值以字串表示。

BigQuery 不支援 INT64 做為 JavaScript UDF 中的傳回類型。在這種情況下,JavaScript 函式主體可以傳回 JavaScript 數字或字串。然後,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 與網頁版 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 內。
  • JavaScript UDF 可能會逾時,而造成您的查詢無法完成。逾時可能只有短短 5 分鐘,但可能因多種因素而異,包括您的函式佔用多少使用者 CPU 作業時間,以及對於 JS 函式的輸入和輸出有多大。
  • 查詢工作最多可以有 50 個 JavaScript UDF 資源 (內嵌程式碼 blob 或外部檔案)。
  • 每個內嵌程式碼 blob 的大小上限為 32 KB。
  • 每個外部程式碼資源的大小上限為 1 MB。

限制

  • 如果是臨時的 UDF,function_name 不可包含句號。
  • DOM 物件 (WindowDocumentNode) 以及需要這些物件的函式均不受支援。
  • 依賴原生程式碼的 JavaScript 函式不受支援。
  • JavaScript 中的位元作業僅處理最重要的 32 位元。
  • 由於它們的非絕對本質,叫用使用者定義的函式的查詢無法使用快取的結果。
  • 您無法在 UDF 中參照資料表。
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁