Google 會運用 AI 技術將內容翻譯成你偏好的語言，但可能會出錯。

使用者定義函式

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

您可以定義永久或暫時的 UDF。您可以在多項查詢中重複使用 persistent UDF，而 temporary UDF 只能在單項查詢的範圍內使用。

如要建立 UDF，請使用 CREATE FUNCTION 陳述式。如要刪除 persistent user-defined function，請使用 DROP FUNCTION 陳述式。temporary UDF 會在查詢完成時立即失效。DROP FUNCTION 陳述式僅適用於多陳述式查詢和程序中的暫時性 UDF。

如要瞭解舊版 SQL 中的 UDF，請參閱「舊版 SQL 中的 user-defined function」。

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

下一個範例會建立與 persistent 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)
);

搭配使用系統變數與 SQL UDF

SQL UDF 支援 @@session_id 和 @@location 系統變數。您可以在函式建立陳述式的任何位置加入這些系統變數，傳回目前查詢的工作階段 ID 或位置。不支援其他系統變數。

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 值。只有 [-2⁵³, 2⁵³] 範圍內的 JSON 數字會完全轉換。否則，數值會四捨五入，可能導致精確度降低。

因為 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」。

如果沒有看到左側窗格，請按一下「Expand left pane」(展開左側窗格)，開啟窗格。
在「Explorer」窗格中展開專案，然後點選「資料集」。
按一下資料集。您也可以使用搜尋功能或篩選器尋找資料集。
按一下「常式」分頁標籤，然後選取函式。
在詳細資料窗格中，按一下「 Edit Routine Details」(編輯常式詳細資料)，即可編輯說明文字。
在對話方塊中輸入說明，或編輯現有的說明。按一下「儲存」，儲存新的說明文字。

SQL

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

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

前往「BigQuery」

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

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

按一下「執行」。

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

建立自訂遮蓋常式

您可以建立 UDF，與自訂遮蓋常式搭配使用。您應建立專屬資料集，並設定適當的 IAM 權限，以便管理遮蓋 UDF。自訂遮蓋常式必須符合下列規定：

自訂遮蓋常式必須是 SQL UDF。
在 OPTIONS 函式中，data_governance_type 選項必須設為 DATA_MASKING。
自訂遮蓋常式支援下列函式：
- AEAD.DECRYPT_BYTES AEAD 加密函式搭配 KEYS.KEYSET_CHAIN (不支援原始金鑰)
- AEAD.DECRYPT_STRING AEAD 加密函式搭配 KEYS.KEYSET_CHAIN (不支援使用原始金鑰)
- AEAD.ENCRYPT AEAD 加密函式，搭配 keyset_chain (不支援原始金鑰用量)
- CAST 轉換函式
- CONCAT 字串函式
- CURRENT_DATETIME 日期時間函式
- CURRENT_DATE 日期函式
- CURRENT_TIMESTAMP 時間戳記函式
- CURRENT_TIME 時間函式
- DETERMINISTIC_DECRYPT_BYTES AEAD 加密函式搭配 KEYS.KEYSET_CHAIN (不支援使用原始金鑰)
- DETERMINISTIC_DECRYPT_STRING AEAD 加密函式搭配 KEYS.KEYSET_CHAIN (不支援使用原始金鑰)
- DETERMINISTIC_ENCRYPT AEAD 加密函式搭配 KEYS.KEYSET_CHAIN (不支援使用原始金鑰)
- FARM_FINGERPRINT 雜湊函式
- FROM_BASE32 字串函式
- FROM_BASE64 字串函式
- FROM_HEX 字串函式
- GENERATE_UUID 公用函式
- KEYS.KEYSET_CHAIN AEAD 加密函式
- LENGTH 字串函式
- LOWER 字串函式
- LPAD 字串函式
- LTRIM 字串函式
- MD5 雜湊函式
- REGEXP_REPLACE 字串函式
- REGEXP_EXTRACT 字串函式
- REPLACE 字串函式
- RPAD 字串函式
- RTRIM 字串函式
- SAFE_CAST 轉換函式
- SHA1 雜湊函式
- SHA256 雜湊函式
- SHA512 雜湊函式
- STARTS_WITH 字串函式
- SUBSTRING 字串函式
- SUBSTR 字串函式
- TO_BASE32 字串函式
- TO_BASE64 字串函式
- TO_HEX 字串函式
- TRIM 字串函式
- UPPER 字串函式
自訂遮蓋常式可接受 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 前置字元，避免透過錯誤訊息公開原始資料。

建立自訂遮蓋常式後，即可在「Create data policies」(建立資料政策) 中做為遮蓋規則使用。

社群貢獻的函式

社群貢獻的 UDF 位於 bigquery-public-data.persistent_udfs 公開資料集和開放原始碼 bigquery-utils GitHub 存放區。如要在 Google Cloud 控制台查看所有社群 UDF，請在「Explorer」窗格中，為 bigquery-public-data 專案加上星號，然後展開該專案中的巢狀 persistent_udfs 資料集。

允許存取 VPC Service Controls 範圍內社群貢獻的函式

如果專案已啟用 VPC Service Controls，且 BigQuery 是受保護的服務，您必須為 bigquery-public-data 專案 (專案 ID：1057666841514) 定義輸出規則。

這項規則必須啟用下列作業：

bigquery.routines.get (用於日常安排)
bigquery.tables.getData (用於查詢 BigQuery 資料表)

以下程式碼為 YAML 設定範例：

  - egressFrom:
      identityType: ANY_IDENTITY
    egressTo:
      operations:
      - serviceName: 'bigquery.googleapis.com'
        methodSelectors:
        - permission: 'bigquery.routines.get'
        - permission: 'bigquery.tables.getData'
      resources:
      - projects/1057666841514 # bigquery-public-data

如要為這個存放區中的 UDF 貢獻內容，請參閱「貢獻 UDF」的操作說明。

統一存取多個區域的常式

如要在多個區域的查詢中使用 UDF，則必須在執行含有 UDF 的查詢的每個區域中提供 UDF。因此，您應在可能使用 UDF 執行查詢的任何區域建立及維護 UDF。即使資料表完全相同，您也必須維護兩個版本的函式。舉例來說，如果您將銷售資料儲存在 EU 和 US 多區域，則應在每個區域維護一個函式版本。例如：

在 EU 多區域中執行查詢：

  SELECT
    id,
    europe_dataset.my_function(value)
  FROM
    sales;

在 US 多區域中執行查詢：

  SELECT
    id,
    us_dataset.my_function(value)
  FROM
    sales;

此外，如果函式定義有所變更，您必須在所有區域中更新。

如要讓 UDF 不受區域限制，可以使用跨區域資料集複製功能：

建立新的專用資料集 (例如 my_utils)，儲存所有必要的 UDF。請注意，新增至這個資料集的任何資料表都會複製，因此費用會增加。不過，複製 UDF 和程序不會產生任何額外費用。
將所有 UDF 新增至新資料集。這也可能包含社群 UDF，例如從 GitHub 複製的 bqutil。
啟用資料集複製功能。請設定這個資料集，將其複製到您需要執行查詢來呼叫這些 UDF 的所有區域。這會將函式複製到這些區域，並保持同步。

執行查詢時，BigQuery 會自動使用本機資料集副本中的本機 UDF 版本，您不必指定定義函式的區域，因此查詢可在不同位置之間轉移。例如：

  SELECT
    id,
    my_utils.my_function(value)
  FROM
    sales;

限制

臨時和永久使用者定義函式有下列限制：

不支援 DOM 物件 Window、Document 和 Node，以及需要這些物件的函式。
JavaScript 函式會在沙箱環境中運作，而依賴基礎系統程式碼的函式可能會因系統呼叫受限而失敗。
JavaScript UDF 可能會逾時，導致查詢無法完成。逾時時間最短可設為 5 分鐘，但可能因多項因素而異，包括函式耗用的使用者 CPU 作業時間，以及 JavaScript 函式的輸入和輸出大小。
JavaScript 中的位元作業僅處理最重要的 32 位元。
UDF 設有特定頻率限制和配額限制。詳情請參閱「UDF 限制」。

persistent user-defined function 有下列限制：

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

temporary user-defined function 有下列限制。

建立暫時 UDF 時，function_name 不得包含半形句號。
檢視表和 persistent UDF 無法參照 temporary UDF。