標準 SQL ユーザー定義関数

ユーザー定義関数(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 ステートメント内から呼び出します。

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_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

この例では、次の出力が生成されます。

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 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 から実行し、project1mydataset.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 としてエンコードされる値の範囲は [-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 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 呼び出しで変更可能な状態を保存したり、アクセスしたりしないでください。

メモリを効率的に使用する

JavaScript 処理環境では、クエリあたりの使用可能メモリが制限されています。蓄積されて過度のローカル状態になる JavaScript UDF クエリは、メモリ枯渇のために失敗する場合があります。

認可済みの UDF

認可済みの UDF は、特定のデータセットへのアクセスを認可された UDF です。UDF はデータセット内のテーブルに対して、UDF を呼び出しているユーザーにテーブルへのアクセス権がない場合でもクエリを実行できます。

認可済みの UDF を使用すると、特定のユーザーまたはグループに基になるテーブルへのアクセス権を付与せずに、そのユーザーまたはグループとクエリ結果を共有できます。たとえば、認可済みの UDF を使用すると、データの集計を行うことができます。また、テーブル値の検索を行い、その値を計算で使用することもできます。

UDF を認可するには、Google Cloud Console、REST API、または bq コマンドライン ツールを使用します。

Console

  1. Cloud Console の BigQuery ページに移動します。

    BigQuery に移動

  2. ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。

  3. 詳細パネルで、[ルーティンを承認] をクリックします。

  4. [承認済みルーティン] ページの [ルーティンを承認] セクションで、認可する UDF のプロジェクト ID、データセット ID、ルーティン ID を選択します。

  5. [承認を追加] をクリックします。

API

  1. datasets.get メソッドを呼び出して、UDF にアクセス可能なデータセットを取得します。レスポンスの本文には Dataset リソースの表現が含まれます。

  2. 次の JSON オブジェクトを Dataset リソースの access 配列に追加します。

    {
     "routine": {
       "datasetId": "DATASET_NAME",
       "projectId": "PROJECT_ID",
       "routineId": "ROUTINE_NAME"
     }
    }
    

    ここで

    • DATASET_NAME は、UDF を含むデータセットの名前です。
    • PROJECT_ID は、UDF を含むプロジェクトのプロジェクト ID です。
    • ROUTINE_NAME は UDF の名前です。
  3. 変更した Dataset 表現を使用して、dataset.update メソッドを呼び出します。

bq

  1. bq show コマンドを使用して、UDF にアクセス可能なデータセットの JSON 表現を取得します。コマンドからの出力は、Dataset リソースの JSON 表現です。結果をローカル ファイルに保存します。

    bq show --format=prettyjson TARGET_DATASET > dataset.json
    

    TARGET_DATASET は、UDF がアクセスするデータセットの名前に置き換えます。

  2. ファイルを編集して、Dataset リソースの access 配列に次の JSON オブジェクトを追加します。

    {
     "routine": {
       "datasetId": "DATASET_NAME",
       "projectId": "PROJECT_ID",
       "routineId": "ROUTINE_NAME"
     }
    }
    

    ここで

    • DATASET_NAME は、UDF を含むデータセットの名前です。
    • PROJECT_ID は、UDF を含むプロジェクトのプロジェクト ID です。
    • ROUTINE_NAME は UDF の名前です。
  3. bq update コマンドを使用してデータセットを更新します。

    bq update --source dataset.json TARGET_DATASET
    

認可済みの UDF の例

以下は、認可済みの UDF を作成して使用するエンドツーエンドの例です。

  1. private_datasetpublic_dataset という名前の 2 つのデータセットを作成します。データセットの作成の詳細については、データセットの作成をご覧ください。

  2. 次のステートメントを実行して、private_datasetprivate_table という名前のテーブルを作成します。

    CREATE OR REPLACE TABLE private_dataset.private_table
    AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;
    
  3. 次のステートメントを実行して、public_datasetcount_key という名前の UDF を作成します。UDF には private_tableSELECT ステートメントが含まれます。

    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));
    
  4. public_dataset データセットの bigquery.dataViewer ロールをユーザーに付与します。このロールには、ユーザーが関数を呼び出すための bigquery.routines.get 権限が含まれています。データセットにアクセス制御を割り当てる方法については、データセットへのアクセスの制御をご覧ください。

  5. この時点で、ユーザーは 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.
    
  6. bq コマンドライン ツールを使用して、次のように show コマンドを実行します。

    bq show --format=prettyjson private_dataset > dataset.json
    

    出力は dataset.json という名前のローカル ファイルに保存されます。

  7. dataset.json を編集して、次の JSON オブジェクトを access 配列に追加します。

    {
     "routine": {
       "datasetId": "public_dataset",
       "projectId": "PROJECT_ID",
       "routineId": "count_key"
     }
    }
    

    PROJECT_ID は、public_dataset のプロジェクト ID に置き換えます。

  8. bq コマンドライン ツールを使用して、次のように update コマンドを実行します。

    bq update --source dataset.json private_dataset
    
  9. UDF が private_dataset にアクセスできることを確認するには、次のクエリを実行します。

    SELECT public_dataset.count_key('key1');
    

UDF に説明を追加する

UDF に説明を追加する手順は次のとおりです。

Console

  1. Cloud Console の BigQuery ページに移動します。

    BigQuery に移動

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

  3. [詳細] ペインで、[説明] の横にある鉛筆アイコンをクリックして、説明テキストを編集します。

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

または、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 オブジェクトの WindowDocumentNode、およびこれらを必要とする関数はサポート対象外です。
  • ネイティブ コードに依存する 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 を参照することはできません。