ユーザー定義の関数

ユーザー定義関数（UDF）を使用すると、SQL 式または JavaScript コードを使用して関数を作成できます。UDF は入力列を受け取って入力に対するアクションを実行し、それらのアクションの結果を値として返します。

UDF は永続的または一時的として定義できます。永続的な UDF は複数のクエリで再利用できるのに対し、一時的な UDF は 1 つのクエリのスコープ内にのみ存在します。

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

スカラーサブクエリ

SQL UDF は、スカラーサブクエリの値を返すことができます。スカラーサブクエリでは 1 つの列を選択する必要があります。

スカラーサブクエリを使用してユーザーテーブルで所定の年齢のユーザー数をカウントする 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 エンティティが UDF を含むプロジェクト内に存在している場合を除き、これらのエンティティの参照にはプロジェクト ID を含める必要があります。

たとえば、次のステートメントについて考えてみましょう。

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

このステートメントを project1 から実行し、project1 に mydataset.mytable が存在する場合、ステートメントは成功します。しかし、このステートメントを別のプロジェクトから実行すると、ステートメントは失敗します。このエラーを修正するには、テーブル参照にプロジェクト 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 で記述されたコードを呼び出すことができます。JavaScript UDF は通常、標準 SQL クエリよりも多くのスロットリソースを消費するため、ジョブのパフォーマンスが低下します。関数を 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	NUMBER
NUMERIC、BIGNUMERIC	IEEE 754 浮動小数点として正確に表現でき、小数部分が含まれていない NUMERIC または BIGNUMERIC の値は、NUMBER としてエンコードされます。NUMBER としてエンコードされる値の範囲は [-2⁵³, 2⁵³] です。それ以外の場合、この値は文字列としてエンコードされます。
STRING	STRING
STRUCT	各 STRUCT フィールドが名前付きフィールドである OBJECT
TIMESTAMP	タイムスタンプの `microsecond` 部が含まれるマイクロ秒フィールド付き DATE
DATE	DATE
JSON	JSON の OBJECTS、ARRAY、VALUES は同等の JavaScript OBJECTS、ARRAY、VALUES に変換されます。 JavaScript は INT64 値をサポートしていません。範囲 [-2⁵³, 2⁵³] の JSON 数値のみが正確に変換されます。それ以外の場合、数値は四捨五入されるため、精度が低下する可能性があります。

JavaScript で 64 ビット整数型はサポートされていないため、INT64 は入力データ型としてサポートされません。代わりに、FLOAT64 を使用して整数値を数値として表すか、STRING を使用して整数値を文字列として表します。

BigQuery では、JavaScript UDF の戻り値の型として INT64 がサポートされています。この場合、JavaScript 関数の本体で JavaScript の数値や文字列を返すことができます。この際、BigQuery によってこれらいずれかの型が INT64 に変換されます。

JavaScript UDF の戻り値が Promise の場合、BigQuery は Promise が解決するまで Promise を待機します。Promise がフルフィルメントの状態で解決した場合、BigQuery はその結果を返します。Promise が不承認の状態で解決した場合、BigQuery はエラーを返します。

引用符に関するルール

JavaScript コードを引用符で囲む必要があります。単純な 1 行のスニペットの場合、標準の引用符付き文字列を使用できます。

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 に渡す前に絞り込むことができれば、クエリをより高速かつ低コストで実行できます。

永続的な変更可能状態を回避する

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] に移動
[エクスプローラ] パネルで、プロジェクトとデータセットを開いて、関数を選択します。
[詳細] ペインで、[ ルーティンの詳細を編集] をクリックして説明テキストを編集します。
ダイアログで、説明をボックスに入力するか、既存の説明を編集します。[保存] をクリックして、新しい説明テキストを保存します。

SQL

関数の説明を更新するには、CREATE FUNCTION DDL ステートメントを使用して関数を再作成し、OPTIONS リストに description フィールドを設定します。

Google Cloud コンソールで [BigQuery Studio] ページに移動します。

[BigQuery Studio] に移動

クエリエディタで次のステートメントを入力します。

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

[実行] をクリックします。

クエリの実行方法については、インタラクティブクエリを実行するをご覧ください。

カスタムマスキングルーティンを作成する

カスタムマスキングルーティンで使用する UDF を作成できます。専用のデータセットを作成し、マスキング UDF を管理するための適切な IAM 権限を設定する必要があります。カスタムマスキングルーティンは、次の要件を満たす必要があります。

カスタムマスキングルーティンは SQL UDF である必要があります。
関数 OPTIONS では、data_governance_type オプションを DATA_MASKING に設定する必要があります。
カスタムマスキングルーティンは、次の関数をサポートしています。
- REGEXP_REPLACE 文字列関数
- FARM_FINGERPRINT ハッシュ関数
- MD5 ハッシュ関数
- SHA1 ハッシュ関数
- SHA256 ハッシュ関数
- SHA512 ハッシュ関数
- CAST 変換関数
- CONCAT 文字列関数
- REPLACE 文字列関数
- REGEX_EXTRACT 文字列関数
- SUBSTRING 文字列関数
- TO_BASE32 文字列関数
- TO_BASE64 文字列関数
- FROM_BASE32 文字列関数
- FROM_BASE64 文字列関数
- TO_HEX 文字列関数
- FROM_HEX 文字列関数
- GENERATE_UUID ユーティリティ関数
- CURRENT_DATE 日付関数
- CURRENT_DATETIME 日時関数
- CURRENT_TIME 時刻関数
- CURRENT_TIMESTAMP タイムスタンプ関数
- SAFE_CAST 変換関数
- LENGTH 文字列関数
- STARTS_WITH 文字列関数
- KEYS.KEYSET_CHAIN AEAD 暗号化関数
- AEAD.ENCRYPT keyset_chain を使用した AEAD 暗号化関数（未加工の鍵の使用は非対応）
- AEAD.DECRYPT_BYTES KEYS.KEYSET_CHAIN を使用した AEAD 暗号化関数（未加工の鍵の使用は非対応）
- AEAD.DECRYPT_STRING, KEYS.KEYSET_CHAIN を使用した AEAD 暗号化関数（未加工の鍵の使用は非対応）
- DETERMINISTIC_ENCRYPT KEYS.KEYSET_CHAIN を使用した AEAD 暗号化関数（未加工の鍵の使用は非対応）
- DETERMINISTIC_DECRYPT_BYTES KEYS.KEYSET_CHAIN を使用した AEAD 暗号化関数（未加工の鍵の使用は非対応）
- DETERMINISTIC_DECRYPT_STRING KEYS.KEYSET_CHAIN を使用した AEAD 暗号化関数（未加工の鍵の使用は非対応）
カスタムマスキングルーティンは、BigQuery データ型内で、入力なし、または 1 つの入力を受け入れることができます。ただし、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 リポジトリで提供されています。Google Cloud コンソールでは、[エクスプローラ] ペインで bigquery-public-data プロジェクトにスターをつけ、そのプロジェクト内のネストされた persistent_udfs データセットを開くと、すべてのトピックのコミュニティ UDF を表示できます。

このリポジトリに UDF を提供する場合は、UDF の投稿の手順をご覧ください。

制限事項

一時的および永続的なユーザー定義関数には、次の制限が適用されます。

DOM オブジェクトの Window、Document、Node、およびこれらを必要とする関数はサポート対象外です。
ネイティブコードに依存する JavaScript 関数は、制限されたシステムコールを行う場合などに失敗する可能性があります。
JavaScript UDF がタイムアウトして、クエリが完了しなくなる場合があります。タイムアウトは 5 分以内ですが、関数が消費する CPU 時間、JavaScript 関数の入出力の規模など、いくつかの要因によって変わる可能性があります。
JavaScript のビット演算は上位 32 ビットのみ処理します。
UDF には特定のレート制限と割り当て上限が適用されます。詳細については、UDF の制限をご覧ください。

永続的なユーザー定義関数には次の制限が適用されます。

1 つのデータセット内に、同じ名前を持つ永続的な UDF を複数作成することはできません。ただし、UDF の名前を、同じデータセットに含まれるテーブルの名前と同じにすることはできます。
ある永続 UDF を別の永続的な UDF や論理ビューから参照する場合は、データセット名の修飾子を付ける必要があります。例:
CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

一時的なユーザー定義関数には、次の制限が適用されます。

一時的な UDF を作成するとき、function_name にピリオドを含めることはできません。
ビューおよび永続的な UDF から一時的な UDF を参照することはできません。

ユーザー定義の関数

SQL UDF

SQL UDF テンプレート パラメータ

スカラー サブクエリ

SQL 式のデフォルト プロジェクト

JavaScript UDF

サポートされる JavaScript UDF データ型

引用符に関するルール

JavaScript ライブラリを組み込む

JavaScript UDF のベスト プラクティス

ルーティンを承認する

UDF に説明を追加する

コンソール

SQL

カスタム マスキング ルーティンを作成する

コミュニティ提供の関数

制限事項

SQL UDF テンプレートパラメータ

スカラーサブクエリ

SQL 式のデフォルトプロジェクト

JavaScript UDF のベストプラクティス

カスタムマスキングルーティンを作成する