ユーザー定義の関数
ユーザー定義関数(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 エンティティが UDAF を含むプロジェクト内に存在している場合を除き、これらのエンティティの参照にはプロジェクト 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 としてエンコードされる値の範囲は [-253, 253] です。それ以外の場合、この値は文字列としてエンコードされます。 |
STRING | STRING |
STRUCT | 各 STRUCT フィールドが名前付きフィールドである OBJECT |
TIMESTAMP | タイムスタンプの microsecond 部が含まれるマイクロ秒フィールド付き DATE |
DATE | DATE |
JSON |
JSON の OBJECTS、ARRAY、VALUES は同等の JavaScript OBJECTS、ARRAY、VALUES に変換されます。 JavaScript は INT64 値をサポートしていません。範囲 [-253, 253] の 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] ページに移動します。
[エクスプローラ] パネルで、プロジェクトとデータセットを開いて、関数を選択します。
[詳細] ペインで、[
ルーティンの詳細を編集] をクリックして説明テキストを編集します。ダイアログで、説明をボックスに入力するか、既存の説明を編集します。[保存] をクリックして、新しい説明テキストを保存します。
SQL
関数の説明を更新するには、CREATE FUNCTION
DDL ステートメントを使用して関数を再作成し、OPTIONS
リストに description
フィールドを設定します。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
CREATE OR REPLACE FUNCTION mydataset.my_function(...) AS ( ... ) OPTIONS ( description = 'DESCRIPTION' );
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。
カスタム マスキング ルーティンを作成する
カスタム マスキング ルーティンで使用する UDF を作成できます。カスタム マスキング ルーティンは、次の要件を満たす必要があります。
- カスタム マスキング ルーティンは SQL UDF である必要があります。
- 関数
OPTIONS
では、data_governance_type
オプションをDATA_MASKING
に設定する必要があります。 - カスタム マスキング ルーティンは、次の関数をサポートしています。
REGEXP_REPLACE
文字列関数FARM_FINGERPINT
ハッシュ関数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
を変更することはできません。
たとえば、ユーザーの社会保障番号を 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 を参照することはできません。