ユーザー定義の関数

ユーザー定義の関数

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

UDF は一時的なものです。これは、UDF を使用できるのは、現在のクエリまたはコマンドラインのセッションだけであることを意味します。

UDF の例

CREATE TEMPORARY FUNCTION multiplyInputs(x INT64, y INT64)
RETURNS INT64
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           |
+-----+-----+--------------+

指定されたクエリは複数の CREATE TEMPORARY FUNCTION ステートメントから始まります。次に例を示します。

CREATE TEMPORARY FUNCTION multiplyInputs(x INT64, y INT64)
RETURNS INT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY FUNCTION divideByTwo(x INT64)
RETURNS INT64
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            | 1      | 3      |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 2      | 8      |
+-----+-----+--------------+--------+--------+

クエリ ステートメントは UDF の結果を別の UDF のパラメータとして使用できます。次に例を示します。

CREATE TEMPORARY FUNCTION multiplyInputs(x INT64, y INT64)
RETURNS INT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY FUNCTION divideByTwo(x INT64)
RETURNS INT64
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   | 3            |
| 2   | 10  | 5            |
| 3   | 15  | 16           |
+-----+-----+--------------+

次の例は、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    |
+-----+--------+

外部 UDF 構造

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

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

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

  • CREATE [TEMPORARY | TEMP ] FUNCTION。新しい一時関数を作成します。関数にはゼロ個以上の named_parameter が含まれます。それぞれはカンマで区切られた param_name param_type ペアで構成されます。
  • RETURNS [data_type]。一時関数が返すデータ型を指定します。詳細については、サポートされる UDF データ型をご覧ください。
  • Language [language]。一時関数の言語を指定します。詳細については、サポートされる UDF 言語をご覧ください。
  • AS [external_code]。一時関数が実行するコードを指定します。コードを UDF に追加する方法については、引用符のルールをご覧ください。

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

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

  • ARRAY
  • BOOL
  • BYTES
  • FLOAT64
  • INT64
  • STRING
  • STRUCT
  • TIMESTAMP

サポートされる UDF 言語

UDF は、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 型エンコーディングをご覧ください。

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

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

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

INT64 入力を整数として扱うには、PARSEINT() などの関数を使用します。これは大きな数字を最上位の 53 ビットに丸めます。

引用符のルール

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

CREATE TEMPORARY FUNCTION plusOne(x INT64)
RETURNS INT64
LANGUAGE js
AS "return parseInt(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。新しい一時関数を作成します。関数にはゼロ個以上の named_parameter が含まれます。それぞれはカンマで区切られた param_name param_type ペアで構成されます。
  • [RETURNS data_type]。(省略可)一時関数が返すデータ型を指定します。
  • AS [sql_expression]。一時関数が評価して返す SQL 式を指定します。

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

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

CREATE TEMP FUNCTION myFunc(a INT64, 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"]
)

上記の例では、example.jsexample-common.js のコードは UDF の [external_code] セクションのどのコードでも利用可能です。単一エレメントまたはアレイ構文を使用して、ライブラリ ファイルを指定できることに注意してください。

UDF とウェブ UI

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

事前準備

BigQuery ウェブ UI を使用するには、Google Cloud Platform Console で BigQuery が有効になっているプロジェクトへのアクセス権限をアカウントに設定する必要があります。

  1. Cloud Platform Console を初めて使用する場合は、コンソールにアクセスして利用規約に同意し、新しいプロジェクトを作成します。
  2. ウェブ UI に移動します

UDF によるクエリの実行

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

    CREATE TEMPORARY FUNCTION timesTwo(x INT64)
    RETURNS INT64
      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>

成功事例

入力を事前に絞り込む

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

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

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

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

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

制限事項

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

制限事項

  • DOM オブジェクトの WindowDocumentNode、およびこれらを必要とする関数はサポートされません。
  • ネイティブ コードに依存する JavaScript 関数はサポートされません。
  • JavaScript の Bitwise 演算は上位 32 ビットのみ処理します。

報告されている問題

パフォーマンス

標準 SQL UDF は、現時点でレガシー SQL バージョンよりも低速です。

クエリ キャッシュの無効化

BigQuery は、クエリ キャッシュから結果を返すかどうか決定するときに、CREATE TEMPORARY FUNCTION ステートメントの内容を考慮しません。したがって、UDF の外部コードを変更しても、キャッシュに保存された結果が引き続き返されます。

この問題に対処するには、UDF でクエリを実行する前に、クエリ オプションの [キャッシュに保存された結果の使用] を無効にします。

INT64 引数

データ型 INT64 と宣言されている引数は JavaScript UDF 環境では文字列として示されます。JavaScript は + 演算子を文字列の「結合」と解釈するため、難解なエラーが発生する可能性があります。

整数値をネイティブ JavaScript 数値として JavaScript 環境にインポートするには、CREATE TEMPORARY FUNCTION ステートメントで引数の型を FLOAT64 と宣言します。この結果、整数から JavaScript 数値へ自動的に変換されます。

SQL クエリの STRING に整数値を割り当て、引数をデータ型 STRING と宣言することもできます。これには、値を JavaScript 数値または他のデータ型 (JavaScript コードの goog.Math.Long など)に変換する必要があります。

エラー メッセージの行番号が不正確な場合がある

CREATE TEMP FUNCTION ステートメント内のコードに関係するメッセージの行番号は 1 つずつオフになります。

エラー メッセージにファイル / モジュール名が指定されていない

標準 SQL UDF のエラー メッセージは、エラーが発生したファイルまたはモジュールのコンテキストが現時点で指定されていません。

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

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