ユーザー定義の関数

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

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

注: 永続的な UDF は、オーナー間で共有する場合に安全に呼び出すことができます。UDF は、データの変換、外部システムとの通信、Google Cloud のオペレーション スイートなどのアプリケーションへのログの送信を行うことはできません。

UDF を作成するには、CREATE FUNCTION ステートメントを使用します。永続的なユーザー定義関数を削除するには、DROP FUNCTION ステートメントを使用します。一時的な UDF は、クエリが終了するとすぐに期限切れになります。DROP FUNCTION ステートメントは、複数ステートメント クエリとプロシージャ内の一時的な UDF によってのみサポートされます。

レガシー SQL の UDF については、レガシー SQL のユーザー定義関数をご覧ください。

SQL UDF

次の例では、AddFourAndDivide という名前の一時的な SQL UDF を作成し、SELECT ステートメント内から呼び出します。

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 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;

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 エンティティへの参照には、エンティティが CREATE FUNCTION ステートメントである場合を除き、プロジェクト 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 の例を次に示します。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^53, 2^53] です。上記の条件に当てはまらない値は 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 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 に説明を追加する手順は次のとおりです。

コンソール

1. コンソールの [BigQuery] ページに移動します。

   BigQuery に移動

2. [エクスプローラ] パネルで、プロジェクトとデータセットを開いて、関数を選択します。

3. [詳細] ペインで、 [ルーティンの詳細を編集] をクリックして、説明テキストを編集します。

4. ダイアログで、説明をボックスに入力するか、既存の説明を編集します。[保存] をクリックして、新しい説明テキストを保存します。

または、OPTIONS フィールドの description パラメータを使用して説明を設定するために、標準 SQL クエリを使用できます。[クエリエディタ] ボックスに関数定義を入力し、次の行を追加します。

OPTIONS (description="DESCRIPTION");

DESCRIPTION は、追加する説明に置き換えます。

bq

UDF と bq コマンドライン ツールの bq query 構文を使用すると、コマンドラインから関数の説明を設定できます。--nouse_legacy_sql または -- use_legacy_sql=false フラグを使用して標準 SQL を指定し、関数定義を入力します。次の行を定義に追加して、OPTIONS フィールドに description パラメータを設定します。

OPTIONS (description="DESCRIPTION")

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 を参照することはできません。