ユーザー定義の関数

標準 SQL のユーザー定義の関数

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

UDF は一時的なものです。これは、UDF を使用できるのは、現在のクエリまたはコマンドラインのセッションだけであることを意味します。標準 SQL クエリで使用するユーザー定義関数を作成する場合には、ウェブ UI の UDF Editor タブを使用しません。UDF Editor タブはレガシー SQL 用です。

外部 UDF 構造

次の構造を使用して外部 UDF を作成します。

CREATE [TEMPORARY | TEMP] FUNCTION function_name ([named_parameter[, ...]])
  RETURNS [data_type]
  LANGUAGE [language]
  AS [external_code]

個々の UDF は次のコンポーネントで構成されます。

  • CREATE [TEMPORARY | TEMP ] FUNCTION。新しい一時関数を作成します。関数には 0 個以上の named_parameter を使用できます。各パラメータは、カンマ区切りの param_nameparam_type のペアから構成されます。UDF を作成する場合には、TEMPORARY または TEMP のいずれかを使用する必要があります。
  • RETURNS [data_type]。関数が返すデータ型を指定します。詳細については、サポートされる UDF データ型をご覧ください。
  • Language [language]。関数の言語を指定します。詳細については、サポートされる外部 UDF 言語をご覧ください。
  • AS [external_code]。関数が実行するコードを指定します。コードを UDF に追加する方法については、引用符のルールをご覧ください。

外部 UDF の例

CREATE TEMPORARY 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 を作成できます。次に例を示します。

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY 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(x, y) as product,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

UDF の結果を別の UDF への入力として渡すことができます。次に例を示します。

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY 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        |
+-----+-----+--------------+

サポートされる UDF 言語

外部 UDF は LANGUAGE として js を使用して指定する JavaScript で記述されたコードをサポートします。次に例を示します。

CREATE TEMPORARY FUNCTION greeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  return "Hello, " + a + "!";
  """;
SELECT greeting(name) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS name;

+----------------+
| everyone       |
+----------------+
| Hello, Hannah! |
| Hello, Max!    |
| Hello, Jakob!  |
+----------------+

BigQuery データ型が JavaScript 型にマッピングする方法については、JavaScript での SQL 型エンコーディングをご覧ください。

サポートされる外部 UDF データ型

外部 UDF では、BigQuery は次のデータ型をサポートします。

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • 文字列
  • STRUCT
  • TIMESTAMP

JavaScript での SQL 型エンコーディング

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

JavaScript は 64 ビット整数型をサポートしていないため、INT64 は JavaScript UDF の入力データ型または出力データ型でサポートされません。代わりに、FLOAT64 を使用して整数値を数値として表すか、STRING を使用して整数値を文字列として表します。

BigQuery は次のように型を表します。

BigQuery のデータ型 JavaScript のデータ型
ARRAY ARRAY
BOOL BOOLEAN
バイト Base64 エンコードされた STRING
FLOAT64 NUMBER
STRING 文字列
STRUCT 各 STRUCT フィールドが名前付きフィールドである OBJECT
TIMESTAMP タイムスタンプの microsecond 部が含まれるマイクロ秒フィールド付き DATE
DATE 日付

引用符のルール

外部コードを引用符で囲む必要があります。単純な 1 行のスニペットの場合、標準の引用符付き文字列を使用できます。

CREATE TEMPORARY 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         |
+-----------+-----------+

スニペットに引用符が含まれている場合、または複数の行でスニペットが構成されている場合、3 重の引用符ブロックを使用します。

CREATE TEMPORARY 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!  |
+-----------------------+

SQL UDF の構造

次の構造を使用して SQL UDF を作成します。

CREATE [TEMPORARY | TEMP] FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

個々の UDF は次のコンポーネントで構成されます。

  • CREATE [TEMPORARY | TEMP ] FUNCTION。新しい一時関数を作成します。関数には 0 個以上の named_parameter を使用できます。各パラメータは、カンマ区切りの param_nameparam_type のペアから構成されます。UDF を作成する場合には、TEMPORARY または TEMP のいずれかを使用する必要があります。
  • [RETURNS data_type]。(省略可)関数が返すデータ型を指定します。
  • AS [sql_expression]。関数が評価して返す SQL 式を指定します。

SQL UDF の例

次の例は、SQL 関数を使用する UDF を示しています。

CREATE TEMPORARY 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    |
+-----+--------+

外部ライブラリのインクルード

OPTIONS セクションを使用して外部 UDF を拡張できます。このセクションによって UDF の外部コード ライブラリを指定することができます。

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
"""
OPTIONS (
  library="gs://my-bucket/path/to/lib1.js",
  library=["gs://my-bucket/path/to/lib2.js", "gs://my-bucket/path/to/lib3.js"]
);

SELECT myFunc(3.14, 'foo');

上記の例では、lib1.jslib2.jslib3.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 以下にする必要があります。
  • 各ユーザーが同時に特定のプロジェクトで実行できる JavaScript UDF クエリはおよそ 6 個に制限されます。同時クエリ制限を超過しているというエラーを受け取った場合は。数分待ってからもう一度実行してください。
  • JavaScript UDF がタイムアウトになるとクエリを実行できなくなります。タイムアウトは 5 分以内ですが、関数が消費する CPU 時間、JS 関数の入出力の規模など、いくつかの要因によって変わる可能性があります。
  • クエリジョブには最大 50 個の JavaScript UDF リソース(インライン コード blob または外部ファイル)を指定できます。
  • 各行コード blob のサイズは最大 32 KB に制限されます。
  • 各外部コードリソースのサイズは最大 1 MB に制限されます。

制限事項

  • DOM オブジェクトの WindowDocumentNode、およびこれらを必要とする関数はサポートされません。
  • ネイティブ コードに依存する JavaScript 関数はサポートされません。
  • JavaScript の Bitwise 演算は上位 32 ビットのみ処理します。
  • ユーザー定義の関数を呼び出すクエリは不明確な性質のため、このようなクエリでキャッシュ内の結果を使用することはできません。
  • UDF のテーブルは参照できません。

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

BigQuery のドキュメント