標準 SQL ユーザー定義関数

BigQuery はユーザー定義関数(UDF)をサポートしています。UDF を利用すると、別の SQL 式、または JavaScript を使用して関数を作成できます。これらの関数は入力列を受け入れ、アクションを実行し、このアクションの結果を値として返します。レガシー SQL のユーザー定義関数については、レガシー SQL のユーザー定義関数をご覧ください。

UDF には永続的なものと一時的なものがあります。永続的な UDF は複数のクエリで再利用できるのに対し、一時的な UDF は 1 つのクエリ内でのみ使用できます。

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:
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

この構文は、次のコンポーネントで構成されています。

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }。関数を作成または更新します。同じ名前を持つ既存の関数を置き換えるには、OR REPLACE キーワードを使用します。同じ名前の関数がすでに存在する場合に実際には何もせずにクエリを成功したものとして扱うには、IF NOT EXISTS 句を使用します。
  • named_parameter。カンマで区切られた param_nameparam_type のペアで構成されます。param_type の値は BigQuery のデータ型です。SQL UDF の場合は、param_type の値を ANY TYPE にすることもできます。
  • [RETURNS data_type]。関数が返すデータ型を指定します。
    • 関数が SQL で定義されている場合、RETURNS 句はオプションです。RETURNS 句を省略した場合、BigQuery は、クエリが関数を呼び出すときに SQL 関数本文から関数の結果の型を推測します。
    • 関数が JavaScript で定義されている場合、RETURNS 句は必須です。data_type に指定できる値の詳細については、サポートされている JavaScript UDF データ型をご覧ください。
  • 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

一時的なユーザー定義関数は、クエリが終了するとすぐに期限切れとなるため、スクリプトプロシージャを除く DROP FUNCTION 文には対応しません。

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
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_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 データ型

SQL 型には JavaScript 型への直接マッピングが用意されているものと、用意されていないものがあります。BigQuery では次のように型が示されます。

BigQuery のデータ型 JavaScript のデータ型
ARRAY ARRAY
BOOL BOOLEAN
BYTES Base64 エンコードされた STRING
FLOAT64 NUMBER
NUMERIC NUMERIC の値を IEEE 754 浮動小数点として正確に表現でき、小数部分が含まれていない場合、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] セクションのすべてのコードで使用できます。

UDF とウェブ UI

1 つ以上の UDF を使用してクエリを実行するには BigQuery ウェブ UI を使用できます。

UDF によるクエリの実行

  1. [クエリを作成] ボタンをクリックします。
  2. [クエリエディタ] タブをクリックします。
  3. [オプションを表示] ボタンをクリックします。
  4. [レガシー SQL を使用する] チェックボックスをオフにします。
  5. UDF ステートメントを [クエリエディタ] テキスト ボックスに入力します。例:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. UDF ステートメントの下にクエリを入力します。例:

    SELECT timesTwo(numbers) AS doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. [RUN QUERY] ボタンをクリックします。クエリの結果がボタンの下に表示されます。

UDF と bq コマンドライン ツール

Google Cloud SDKbq コマンドライン ツールを使用して、1 つ以上の UDF を含むクエリを実行できます。

UDF でクエリを実行するには次の構文を使用します。

bq query <statement_with_udf_and_query>

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

入力を事前に絞り込む

入力を UDF に渡す前に、簡単に絞り込むことができれば、クエリをより高速かつ低コストで実行できます。

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

UDF 呼び出しで変更可能な状態を保存したり、アクセスしたりしないでください。

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

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

上限

  • 単一行の処理時に JavaScript UDF が出力するデータの量 - およそ 5 MB 以下。
  • ユーザー定義関数(UDF)を含むレガシー SQL クエリに対する同時実行レート上限 - 6 件の同時クエリ。
  • UDF を含むレガシー SQL クエリに対する同時実行レート上限は、インタラクティブ クエリとバッチクエリの両方が対象になります。UDF を使用したインタラクティブ クエリは、インタラクティブ クエリの同時実行レート上限に対してもカウントされます。この上限は標準 SQL クエリには適用されません。

  • クエリジョブで指定できる JavaScript UDF リソース(インライン コード blob または外部ファイルなど)の上限 - 50 個
  • 各インライン コード blob のサイズの上限 - 32 KB
  • 各外部コードリソースのサイズの上限 - 1 MB

永続的なユーザー定義関数には、次の制限が適用されます。
  • 関数名の最大文字数 - 256 文字
  • 引数の最大数 - 256 個
  • 引数名の最大文字数 - 128 文字
  • ユーザー定義関数参照チェーンの最大深度 - 16
  • STRUCT 型の引数または出力の最大深度 - 15
  • STRUCT 型の引数または出力に含まれるフィールドの UDF あたりの最大数 - 1,024 個
  • 一意の UDF とテーブル参照を合わせたクエリあたりの最大数 - 1,000。完全な展開後に、UDF ごとに一意のテーブルと UDF を合わせて 1,000 個まで参照できます。
  • CREATE FUNCTION ステートメントに含まれる JavaScript ライブラリの最大数 - 50 個
  • 含まれる JavaScript ライブラリパスの最大文字数 - 5,000 文字
  • UDF あたりの最大更新レート - 10 秒あたり 5 回。関数の作成後に、各関数を 10 秒あたり 5 回まで更新できます。
  • 各行コード blob のサイズ上限は 32 KB です。
  • 各 JavaScript コードリソースのサイズ上限は 1 MB です。

制限事項

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

  • DOM オブジェクトの WindowDocumentNode、およびこれらを必要とする関数はサポート対象外です。
  • ネイティブ コードに依存する JavaScript 関数はサポートされません。
  • JavaScript UDF がタイムアウトして、クエリが完了しなくなる場合があります。タイムアウトは 5 分以内ですが、関数が消費する CPU 時間、JS 関数の入出力の規模など、いくつかの要因によって変わる可能性があります。
  • JavaScript ユーザー定義関数を呼び出すクエリは非決定的であるため、このようなクエリでキャッシュ内の結果を使用することはできません。
  • JavaScript のビット演算は上位 32 ビットに対してのみ行われます。
  • UDF のテーブルは参照できません。

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

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

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

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

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。