BigQuery はユーザー定義関数（UDF）をサポートしています。UDF を利用すると、別の SQL 式、または JavaScript を使用して関数を作成できます。これらの関数は入力列を受け入れ、アクションを実行し、このアクションの結果を値として返します。レガシー SQL のユーザー定義関数については、レガシー SQL のユーザー定義関数をご覧ください。
UDF には永続的なものと一時的なものがあります。複数のクエリでの永続的な UDF と 1 つのクエリ内での一時的な UDF は再利用できます。
UDF の構文
永続的な UDF を作成するには、次の構文を使用します。
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] [[project_name.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
一時的な UDF を作成するには、次の構文を使用します。
CREATE [OR REPLACE] {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: [determinism_specifier] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code determinism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }
この構文は、次のコンポーネントで構成されています。
CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }。関数を作成または更新します。同じ名前を持つ既存の関数を置き換えるには、
OR REPLACEキーワードを使用します。同じ名前の関数がすでに存在する場合に、実際には何もせずにクエリを成功したものとして扱うには、IF NOT EXISTS句を使用します。project_name は、関数を作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート
`で囲む必要があります（例:`google.com:my_project`）。dataset_name は、関数を作成するデータセットの名前です。デフォルトでは、リクエスト内の
defaultDatasetの名前が設定されます。named_parameter。カンマで区切られた
param_nameとparam_typeのペアで構成されます。param_typeの値は BigQuery のデータ型です。SQL UDF の場合は、param_typeの値をANY TYPEにすることもできます。determinism_specifierJavaScript のユーザー定義関数にのみ適用されます。クエリ結果をキャッシュに保存できるかどうかについてのヒントを BigQuery に提供します。次のいずれかの値です。
DETERMINISTIC: 同じ引数を渡すと、常に同じ結果が返されます。クエリ結果はキャッシュに保存できる可能性があります。たとえば、関数add_one(i)が常にi + 1を返す場合、この関数は確定的です。NOT DETERMINISTIC: 同じ引数を渡しても、同じ結果が返されるとは限りません。このため、キャッシュに保存できません。たとえば、add_random(i)がi + rand()を返す場合、関数は確定的ではないため、BigQuery はキャッシュに保存された結果を使用しません。呼び出された関数がすべて DETERMINISTIC の場合、他の理由で結果をキャッシュに保存できない場合を除き、BigQuery は結果をキャッシュに保存しようとします。詳細については、キャッシュに保存されているクエリ結果を使用するをご覧ください。
[RETURNS data_type]。関数が返すデータ型を指定します。
- 関数が SQL で定義されている場合、
RETURNS句はオプションです。RETURNS句を省略した場合、BigQuery は、クエリが関数を呼び出すときに SQL 関数本文から関数の結果の型を推測します。 - 関数が JavaScript で定義されている場合、
RETURNS句は必須です。data_typeで使用できる値の詳細については、サポートされた JavaScript UDF データ型をご覧ください。
- 関数が SQL で定義されている場合、
AS (sql_expression)。関数を定義する SQL 式を指定します。
[OPTIONS (library = library_array)]。JavaScript UDF の関数定義に含める JavaScript ライブラリの配列を指定します。
AS javascript_code。JavaScript 関数の定義を指定します。
javascript_codeは、文字列リテラルです。
永続的なユーザー定義関数を削除するには、次の構文を使用します。
DROP FUNCTION [IF EXISTS] [[project_name.]dataset_name.]function_name
クエリが終了するとすぐに一時的な UDF が期限切れになるため、DROP FUNCTION ステートメントはスクリプトとプロシージャ内の一時的な UDF によってのみサポートされます。
SQL UDF
次の構文を使用して、SQL UDF を作成します。
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (sql_expression) named_parameter: param_name param_type
SQL UDF テンプレート パラメータ
関数が呼び出されるとき、param_type = ANY TYPE のテンプレート パラメータは複数の引数型と一致する場合があります。
ANY TYPE型のパラメータが複数ある場合、BigQuery では、これらの引数間に型の関係が適用されることはありません。- 関数の戻り値の型を
ANY TYPEに指定することはできません。省略することでsql_expressionを基に自動的に決定するようにするか、明示的な型を指定する必要があります。 - 関数定義に適合しない型の関数引数を渡すと、呼び出し時にエラーになります。
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 |
+------------+-----------+JavaScript UDF
次の構造を使用して、JavaScript UDF を作成します。
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) RETURNS data_type [DETERMINISTIC | NOT DETERMINISTIC] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code
サポートされる JavaScript UDF データ型
SQL 型には JavaScript 型への直接マッピングが用意されているものと、用意されていないものがあります。BigQuery では次のように型が示されます。
| BigQuery のデータ型 | JavaScript のデータ型 |
|---|---|
| ARRAY | ARRAY |
| BOOL | BOOLEAN |
| BYTES | Base64 エンコードされた STRING |
| FLOAT64 | NUMBER |
| NUMERIC | NUMERIC の値を IEEE 754 浮動小数点として正確に表現でき、小数部分が含まれていない場合、NUMBER としてエンコードされます。NUMBER としてエンコードされる値の範囲は [-253, 253] です。上記の条件に当てはまらない値は STRING としてエンコードされます。 |
| STRING | STRING |
| STRUCT | 各 STRUCT フィールドが名前付きフィールドである OBJECT |
| TIMESTAMP | タイムスタンプの microsecond 部が含まれるマイクロ秒フィールド付き DATE |
| DATE | DATE |
JavaScript で 64 ビット整数型はサポートされていないため、INT64 は入力データ型としてサポートされません。代わりに、FLOAT64 を使用して整数値を数値として表すか、STRING を使用して整数値を文字列として表します。
BigQuery では、JavaScript UDF の戻り型として INT64 がサポートされています。この場合、JavaScript 関数の本体で JavaScript の数値や文字列を返すことができます。この際、BigQuery によってこれらいずれかの型が INT64 に変換されます。
JavaScript UDF の戻り値が Promise である場合、BigQuery は 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 |
| 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! |
+-----------------------+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
"""
// 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 の例
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 の結果を別の 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 UDF のデータ型をご覧ください。
JavaScript UDF のベスト プラクティス
入力を事前に絞り込む
入力を JavaScript UDF に渡す前に、簡単に絞り込むことができれば、クエリをより高速かつ低コストで実行できます。
永続的な変更可能状態を回避する
UDF 呼び出しで変更可能な状態を保存したり、アクセスしたりしないでください。
メモリを効率的に使用する
JavaScript 処理環境では、クエリあたりの使用可能メモリが制限されています。蓄積されて過度のローカル状態になる JavaScript UDF クエリは、メモリ枯渇のために失敗する場合があります。
UDF によるクエリの実行
Cloud Console の使用
1 つ以上の UDF を使用してクエリを実行するには Cloud Console を使用します。
- [クエリを新規作成] をクリックします。
-
[クエリエディタ] ペインで UDF ステートメントを入力します。例:
CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64) RETURNS FLOAT64 LANGUAGE js AS """ return x*2; """; -
UDF ステートメントの下に SQL クエリを入力します。例:
SELECT timesTwo(numbers) AS doubles FROM UNNEST([1, 2, 3, 4, 5]) AS numbers; -
[クエリを実行] をクリックします。クエリの結果がボタンの下に表示されます。
bq コマンドライン ツールを使用する
Cloud SDK の bq コマンドライン ツールを使用して、1 つ以上の UDF を含むクエリを実行できます。
UDF でクエリを実行するには次の構文を使用します。
bq query <statement_with_udf_and_query>認可済みの UDF
認可済みの UDF は、特定のデータセットへのアクセスを認可された UDF です。UDF はデータセット内のテーブルに対して、UDF を呼び出しているユーザーにテーブルへのアクセス権がない場合でもクエリを実行できます。
認可済みの UDF を使用すると、特定のユーザーまたはグループに基になるテーブルへのアクセス権を付与せずに、そのユーザーまたはグループとクエリ結果を共有できます。たとえば、認可済みの UDF を使用すると、データの集計を行うことができます。また、テーブル値の検索を行い、その値を計算で使用することもできます。
UDF を認可するには、Google Cloud Console、REST API、または bq コマンドライン ツールを使用します。
Console
Cloud Console の BigQuery ページに移動します。
ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。
詳細パネルで、[ルーティンを承認] をクリックします。
[承認済みルーティン] ページの [ルーティンを承認] セクションで、認可する UDF のプロジェクト ID、データセット ID、ルーティン ID を選択します。
[承認を追加] をクリックします。
API
datasets.getメソッドを呼び出して、UDF にアクセス可能なデータセットを取得します。レスポンスの本文にはDatasetリソースの表現が含まれます。次の JSON オブジェクトを
Datasetリソースのaccess配列に追加します。{ "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" } }ここで
- DATASET_NAME は、UDF を含むデータセットの名前です。
- PROJECT_ID は、UDF を含むプロジェクトのプロジェクト ID です。
- ROUTINE_NAME は UDF の名前です。
変更した
Dataset表現を使用して、dataset.updateメソッドを呼び出します。
bq
bq showコマンドを使用して、UDF にアクセス可能なデータセットの JSON 表現を取得します。コマンドからの出力は、Datasetリソースの JSON 表現です。結果をローカル ファイルに保存します。bq show --format=prettyjson TARGET_DATASET > dataset.json
TARGET_DATASET は、UDF がアクセスするデータセットの名前に置き換えます。
ファイルを編集して、
Datasetリソースのaccess配列に次の JSON オブジェクトを追加します。{ "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" } }ここで
- DATASET_NAME は、UDF を含むデータセットの名前です。
- PROJECT_ID は、UDF を含むプロジェクトのプロジェクト ID です。
- ROUTINE_NAME は UDF の名前です。
bq updateコマンドを使用してデータセットを更新します。bq update --source dataset.json TARGET_DATASET
認可済みの UDF の例
以下は、認可済みの UDF を作成して使用するエンドツーエンドの例です。
private_datasetとpublic_datasetという名前の 2 つのデータセットを作成します。データセットの作成の詳細については、データセットの作成をご覧ください。次のステートメントを実行して、
private_datasetにprivate_tableという名前のテーブルを作成します。CREATE OR REPLACE TABLE private_dataset.private_table AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;次のステートメントを実行して、
public_datasetにcount_keyという名前の UDF を作成します。UDF にはprivate_tableのSELECTステートメントが含まれます。CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING) RETURNS INT64 AS ((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));public_datasetデータセットのbigquery.dataViewerロールをユーザーに付与します。このロールには、ユーザーが関数を呼び出すためのbigquery.routines.get権限が含まれています。データセットにアクセス制御を割り当てる方法については、データセットへのアクセスの制御をご覧ください。この時点で、ユーザーは
count_key関数を呼び出す権限を持っていますが、private_datasetのテーブルにアクセスできません。ユーザーが関数を呼び出そうとすると、次のようなエラー メッセージが表示されます。Access Denied: Table myproject:private_dataset.private_table: User does not have permission to query table myproject:private_dataset.private_table.
bqコマンドライン ツールを使用して、次のようにshowコマンドを実行します。bq show --format=prettyjson private_dataset > dataset.json
出力は
dataset.jsonという名前のローカル ファイルに保存されます。dataset.jsonを編集して、次の JSON オブジェクトをaccess配列に追加します。{ "routine": { "datasetId": "public_dataset", "projectId": "PROJECT_ID", "routineId": "count_key" } }PROJECT_ID は、
public_datasetのプロジェクト ID に置き換えます。bqコマンドライン ツールを使用して、次のようにupdateコマンドを実行します。bq update --source dataset.json private_dataset
UDF が
private_datasetにアクセスできることを確認するには、次のクエリを実行します。SELECT public_dataset.count_key('key1');
UDF に説明を追加する
UDF に説明を追加する手順は次のとおりです。
Console
Cloud Console の BigQuery ページに移動します。
[リソース] ペインで、関数を選択します。
[詳細] ペインで、[説明] の横にある鉛筆アイコンをクリックして、説明テキストを編集します。
ダイアログで、説明をボックスに入力するか、既存の説明を編集します。[更新] をクリックして、新しい説明テキストを保存します。
または、OPTIONS フィールドの description パラメータを使用して説明を更新するために、標準 SQL クエリを使用できます。[クエリエディタ] ボックスに関数定義を入力し、次の行を追加します。
OPTIONS (description="DESCRIPTION") AS """
DESCRIPTION は、追加する説明に置き換えます。
bq
UDF と bq コマンドライン ツールの bq query 構文を使用すると、コマンドラインから関数の説明を編集できます。--nouse_legacy_sql または --
use_legacy_sql=false フラグを使用して標準 SQL を指定し、関数定義を入力します。次の行を定義に追加して、OPTIONS フィールドに description パラメータを設定します。
OPTIONS (description="DESCRIPTION") AS """
DESCRIPTION は、追加する説明に置き換えます。
制限事項
一時的および永続的なユーザー定義関数には、次の制限が適用されます。
- 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 を参照することはできません。