本頁面由 Cloud Translation API 翻譯而成。

使用者定義函式

使用者定義函式 (UDF) 可讓您使用 SQL 運算式或 JavaScript 程式碼建立函式。UDF 可接受輸入資料欄、對輸入內容執行動作，並將執行結果以值的形式傳回。

您可以將 UDF 定義為永久或暫時性質，您可以在多個查詢中重複使用永久性 UDF，而單項查詢的範圍內只能有一個暫時性 UDF。

如要建立 UDF，請使用 CREATE FUNCTION 陳述式。如要刪除永久使用者定義函式，請使用 DROP FUNCTION 陳述式。暫時性 UDF 會在查詢完成時立即失效。DROP FUNCTION 陳述式僅適用於多陳述式查詢和程序中的暫時性 UDF。

如要瞭解舊版 SQL 中的 UDF，請參閱「舊版 SQL 中的使用者定義函式」。

SQL UDF

下列範例會建立名為 AddFourAndDivide 的暫時性 SQL UDF，並在 SELECT 陳述式中呼叫 UDF：

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

此範例會產生以下輸出內容：

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

下列範例會建立與永久 UDF 相同的函式：

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

由於這個 UDF 是永久性，您必須為函式指定資料集 (本例中的 mydataset)。執行 CREATE FUNCTION 陳述式後，您就可以從查詢中呼叫函式：

SELECT
  val, mydataset.AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8,12]) AS val;

範本 SQL UDF 參數

類型等於 ANY TYPE 的參數可以在呼叫函式時比對多個引數類型。

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

下方範例顯示使用範本參數的 SQL UDF。

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  addFourAndDivideAny(3, 4) AS integer_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

此範例會產生以下輸出內容：

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

下例會使用範本參數傳回任意類型陣列中的最後一項元素：

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE)
AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

SELECT
  lastArrayElement(x) AS last_element
FROM (
  SELECT [2,3,5,8,13] AS x
);

此範例會產生以下輸出內容：

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Scalar subquery

SQL UDF 可傳回純量子查詢的值。純量子查詢必須選取單一資料欄。

以下範例顯示使用向量子查詢的 SQL UDF，用於計算使用者表中特定年齡使用者的數量：

CREATE TEMP TABLE users
AS (
  SELECT
    1 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS (
  (SELECT COUNT(1) FROM users WHERE age = userAge)
);

SELECT
  countUserByAge(10) AS count_user_age_10,
  countUserByAge(20) AS count_user_age_20,
  countUserByAge(30) AS count_user_age_30;

此範例會產生以下輸出內容：

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

SQL 運算式中的預設專案

在 SQL UDF 的內容中，任何 BigQuery 實體的參照 (例如資料表或檢視畫面) 都必須包含專案 ID，除非實體位於包含 UDF 的相同專案中。

例如，請看以下敘述：

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM mydataset.mytable)
);

如果您從 project1 執行這項陳述式，且 mydataset.mytable 存在於 project1 中，則陳述式會成功。不過，如果您從其他專案執行這項陳述式，陳述式就會失敗。如要修正這項錯誤，請在資料表參照中加入專案 ID：

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project1.mydataset.mytable)
);

您也可以從建立函式的專案或資料集中，參照其他專案或資料集中的實體：

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project2.another_dataset.another_table)
);

JavaScript UDF

JavaScript UDF 可讓您從 SQL 查詢呼叫以 JavaScript 編寫的程式碼。與標準 SQL 查詢相比，JavaScript UDF 通常會耗用更多分頁資源，導致工作效能降低。如果函式可用 SQL 表示，通常建議以標準 SQL 查詢工作執行程式碼。

以下範例顯示 JavaScript UDF。JavaScript 程式碼會在原始字串中加上引號。

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  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           |
+-----+-----+--------------+

下方範例會將指定 JSON 字串中名為 foo 的所有欄位值加總。

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  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    |
+---------------------------------------------------------------------+---------+

支援的 JavaScript UDF 資料類型

有些 SQL 類型可以直接對應至 JavaScript 類型，其他類型則不能。BigQuery 會以下列方式表示類型：

BigQuery 資料類型	JavaScript 資料類型
ARRAY	ARRAY
BOOL	BOOLEAN
BYTES	base64 編碼的 STRING
FLOAT64	數字
NUMERIC、BIGNUMERIC	如果 NUMERIC 或 BIGNUMERIC 值能夠以 IEEE 754 浮點值準確表示，且沒有任何小數部分，則可以當成數字加以編碼。這些值的範圍必須是 [-2⁵³, 2⁵³]。否則，系統會將值當成字串加以編碼。
STRING	STRING
STRUCT	OBJECT，其中每個 STRUCT 欄位都是已命名欄位
TIMESTAMP	DATE，其微秒欄位包含時間戳記的 `microsecond` 部分
DATE	DATE
JSON	JSON 物件、陣列和值會轉換為對等的 JavaScript 物件、陣列和值。 JavaScript 不支援 INT64 值。只有 JSON 數字在 [-2⁵³, 2⁵³] 範圍內才會精確轉換。否則系統會將數值四捨五入，這可能會導致精確度降低。

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

BigQuery 不支援 INT64 做為 JavaScript UDF 中的傳回類型。在這種情況下，JavaScript 函式主體可以傳回 JavaScript 數字或字串。然後，BigQuery 會將這兩種類型轉換成 INT64。

如果 JavaScript UDF 的傳回值為 Promise，BigQuery 會等到 Promise 安定下來為止。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.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

如果程式碼片段包含引號，或者由多行組成，請使用加三引號的區塊：

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js
AS r"""
  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 r"""
  // Assumes 'doInterestingStuff' is defined in one of the library files.
  return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

在前例中，lib1.js 和 lib2.js 中的程式碼均可供 UDF [external_code] 區段的任何程式碼使用。

JavaScript UDF 的最佳做法

預先篩選輸入內容

如果輸入內容在傳遞到 JavaScript UDF 之前，能夠輕易地進行篩選，您查詢的執行速度可能會更快，費用也可能會更便宜。

避免永久的可變動狀態

請勿在不同的 JavaScript UDF 呼叫之間儲存或存取可變動的狀態。舉例來說，請避免使用下列模式：

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

有效率地使用記憶體

JavaScript 處理環境限制了每個查詢可用的記憶體。如果 JavaScript UDF 查詢累積過多本機狀態，可能會因記憶體耗盡而失敗。

授權處理常式

您可以將 UDF 授權為常式。授權例行程序可讓您與特定使用者或群組分享查詢結果，而不用為他們提供產生結果的基礎資料表存取權。舉例來說，授權例行程序可以計算資料的匯總值，或查詢資料表值，並在計算中使用該值。詳情請參閱「已授權的日常生活動作」。

為 UDF 新增說明

如要為 UDF 新增說明，請按照下列步驟操作：

主控台

前往 Google Cloud 控制台的「BigQuery」頁面。

前往 BigQuery
在「Explorer」面板中展開專案和資料集，然後選取函式。
在「Details」窗格中，按一下「Edit Routine Details」來編輯說明文字。
在對話方塊中輸入說明，或編輯現有的說明。按一下「儲存」，即可儲存新的說明文字。

SQL

如要更新函式的說明，請使用 CREATE FUNCTION DDL 陳述式重新建立函式，並在 OPTIONS 清單中設定 description 欄位：

前往 Google Cloud 控制台的「BigQuery」頁面。

前往 BigQuery

在查詢編輯器中輸入以下陳述式：

CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

按一下「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

建立自訂遮蔽例程

您可以建立自訂函式，用於自訂遮罩例行程序。您應建立專屬資料集，並設定適當的 IAM 權限，以便管理遮罩 UDF。自訂遮罩例行程序必須符合下列規定：

自訂遮罩處理常式必須是 SQL UDF。
在 OPTIONS 函式中，data_governance_type 選項必須設為 DATA_MASKING。
自訂遮罩例行程序支援下列函式：
- AEAD.DECRYPT_BYTES 搭配 KEYS.KEYSET_CHAIN 的 AEAD 加密函式 (不支援原始金鑰用途)
- AEAD.DECRYPT_STRING, 搭配 KEYS.KEYSET_CHAIN 的 AEAD 加密函式 (不支援原始金鑰用途)
- AEAD.ENCRYPT 含有 keyset_chain 的 AEAD 加密函式 (不支援原始金鑰用途)
- CAST 轉換函式
- CONCAT 字串函式
- CURRENT_DATETIME 日期時間函式
- CURRENT_DATE 日期函式
- CURRENT_TIMESTAMP 時間戳記函式
- CURRENT_TIME 時間函式
- DETERMINISTIC_DECRYPT_BYTES 搭配 KEYS.KEYSET_CHAIN 的 AEAD 加密函式 (不支援原始金鑰用途)
- DETERMINISTIC_DECRYPT_STRING 搭配 KEYS.KEYSET_CHAIN 的 AEAD 加密函式 (不支援原始金鑰用途)
- DETERMINISTIC_ENCRYPT 搭配 KEYS.KEYSET_CHAIN 的 AEAD 加密函式 (不支援原始金鑰用途)
- FARM_FINGERPRINT 雜湊函式
- FROM_BASE32 字串函式
- FROM_BASE64 字串函式
- FROM_HEX 字串函式
- GENERATE_UUID 公用函式
- KEYS.KEYSET_CHAIN AEAD 加密函式
- LENGTH 字串函式
- MD5 雜湊函式
- REGEXP_REPLACE 字串函式
- REGEX_EXTRACT 字串函式
- REPLACE 字串函式
- SAFE_CAST 轉換函式
- SHA1 雜湊函式
- SHA256 雜湊函式
- SHA512 雜湊函式
- STARTS_WITH 字串函式
- SUBSTRING 字串函式
- SUBSTR 字串函式
- TO_BASE32 字串函式
- TO_BASE64 字串函式
- TO_HEX 字串函式
自訂遮罩例行程序可以接受 BigQuery 資料類型中的零個或一個輸入值，但 GEOGRAPHY 和 STRUCT 除外。GEOGRAPHY 和 STRUCT 不支援自訂遮罩例程式。
不支援範本 SQL UDF 參數。
提供輸入內容時，輸入和輸出資料類型必須相同。
必須提供輸出類型。
定義主體中不得參照其他 UDF、子查詢、資料表或檢視畫面。
建立遮罩例行程序後，就無法將該例行程序變更為標準函式。也就是說，如果 data_governance_type 選項設為 DATA_MASKING，您就無法使用 DDL 陳述式或 API 呼叫來變更 data_governance_type。
自訂遮罩例程式支援 CASE 和 CASE expr 陳述式。下列運算子可與 CASE 和 CASE expr 陳述式搭配使用：
- Comparison Operators - <、<=、>、>=、=、!=、IN
- Logical Operators - AND、OR、NOT
- IS Operator

舉例來說，以下是將使用者的身分證字號替換成 XXX-XX-XXXX 的遮罩例程：

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

以下範例使用 SHA256 函式，使用使用者提供的鹽值產生雜湊：

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

以下範例會使用常數值遮蓋 DATETIME 資料欄：

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

最佳做法是盡可能使用 SAFE 前置字串，避免透過錯誤訊息公開原始資料。

建立自訂遮蓋例行程序後，即可在「建立資料政策」中使用該遮蓋規則。

社群提供的函式

社群提供的 UDF 可在 bigquery-public-data.persistent_udfs 公開資料集和開放原始碼 bigquery-utils GitHub 存放區中使用。您可以在「Explorer」窗格中為 bigquery-public-data 專案加上星號，然後展開該專案中的巢狀 persistent_udfs 資料集，即可在 Google Cloud 控制台中查看所有社群 UDF。

如要為這個存放區中的 UDF 做出貢獻，請參閱「提供 UDF」一文中的操作說明。

限制

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

系統不支援 Window、Document 和 Node 等 DOM 物件，以及需要使用這些物件的函式。
依賴原生程式碼的 JavaScript 函式可能會失敗，例如在進行受限制的系統呼叫時。
JavaScript UDF 可能會逾時，而造成您的查詢無法完成。逾時可能只有短短 5 分鐘，但可能因多種因素而異，包括您的函式占用多少使用者 CPU 作業時間，以及對於 JS 函式的輸入和輸出有多大。
JavaScript 中的位元作業僅處理最重要的 32 位元。
UDF 會受到特定的頻率限制和配額限制。詳情請參閱「UDF 限制」。

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

每個資料集只能含有一個使用相同名稱的永久性 UDF。不過，您可以在同一資料集中建立與資料表同名的 UDF。
從其他永久性 UDF 或邏輯檢視表參照某個永久性 UDF 時，您必須使用資料集來限定名稱。例如：
CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

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

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