レガシー SQL のユーザー定義関数

このドキュメントでは、レガシー SQL クエリ構文で JavaScript のユーザー定義関数を使用する方法について詳しく説明します。BigQuery で推奨されるクエリ構文は GoogleSQL です。GoogleSQL のユーザー定義関数については、GoogleSQL ユーザー定義関数をご覧ください。

BigQuery のレガシー SQL は、JavaScript で記述されたユーザー定義関数（UDF）をサポートしています。UDF は MapReduce の「Map」関数に似ています。単一の行を入力として受け取り、出力として 0 個以上の行を生成します。出力のスキーマは入力のスキーマと異なる場合があります。

GoogleSQL のユーザー定義関数については、GoogleSQL のユーザー定義関数をご覧ください。

UDF の例

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

トップへ戻る

UDF の構造

function name(row, emit) {
  emit(<output data>);
}

BigQuery UDF の処理対象となるのは、テーブルの個々の行またはサブセレクトクエリ結果です。UDF には次の 2 つの公式パラメータがあります。

row: 入力行。
emit: 出力データを収集するために BigQuery によって使用されるフック。emit 関数は 1 つのパラメータを受け取ります。このパラメータは、出力データの単一行を表す JavaScript オブジェクトです。複数行のデータを出力するために emit 関数を（ループなどで）複数回呼び出すことができます。

次に示すのは、基本的な UDF を示したサンプルコードです。

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

UDF の登録

関数の名前を登録して、BigQuery SQL から呼び出すことができるようにする必要があります。登録名は、JavaScript 内で関数に使用した名前と一致しなくてもかまいません。

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

入力列

入力列名は、入力テーブル内またはサブクエリ内の列の名前（または、該当する場合はエイリアス）と一致する必要があります。

入力列がレコードである場合は、レコードからアクセスしたいリーフフィールドを（入力列リストで）指定する必要があります。

たとえば、個人の名前と年齢を格納するレコードがある場合は、次のように指定します。

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

名前と年齢の入力指定子は次のようになります。

['person.name', 'person.age']

名前または年齢を指定せずに ['person'] を使用すると、エラーが発生します。

結果の出力はスキーマに一致します。JavaScript オブジェクトの配列が返され、各オブジェクトには「name」プロパティと「age」プロパティがあります。例:

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

出力スキーマ

BigQuery には、UDF で生成される JSON 形式のレコードのスキーマまたは構造を指定する必要があります。スキーマには、ネストされたレコードを含め、サポートされている任意の BigQuery データ型を含めることができます。サポートされている型指定子は次のとおりです。

boolean
float
integer
record
string
timestamp

次のサンプルコードは、出力スキーマ内のレコードの構文を示したものです。各出力フィールドには、name と type 属性が必要です。ネストされたフィールドには、fields 属性も含める必要があります。

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

各フィールドには、オプションの mode 属性を含めることができます。この属性は次の値をサポートしています。

nullable : これはデフォルト値で、省略可能です。
required : これを指定した場合は、該当のフィールドを特定の値に設定する必要があり、未定義のままにはできません。
repeated : これを指定した場合、該当のフィールドは配列でなければなりません。

emit() 関数に渡される列は、出力スキーマのデータ型と一致する必要があります。出力スキーマ内で表されたフィールドのうち、emit 関数で省略されたものは、null として出力されます。

UDF の定義またはリファレンス

必要であれば、UDF を bigquery.defineFunction 内でインライン定義することもできます。例:

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

その他の場合は、UDF を個別に定義し、bigquery.defineFunction 内で関数へのリファレンスを渡すことができます。例:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

エラー処理

UDF の処理中に例外やエラーがスローされた場合は、クエリ全体が失敗します。エラーの処理には、try-catch ブロックを使用できます。例:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

UDF によるクエリの実行

レガシー SQL では、bq コマンドラインツールまたは BigQuery API で UDF を使用できます。Google Cloud コンソールでは、レガシー SQL の UDF はサポートされていません。

bq コマンドラインツールの使用

1 つ以上の UDF を含むクエリを実行するには、Google Cloud CLI の bq コマンドラインツールに --udf_resource フラグを指定します。フラグの値には、Cloud Storage（gs://...）の URI か、ローカルファイルへのパスを使用できます。複数の UDF リソースファイルを指定するには、このフラグを繰り返し指定します。

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

bq query --udf_resource=<file_path_or_URI> <sql_query>

次のサンプルでは、ローカルファイルに保存された UDF と、ローカルファイルに保存された SQL クエリを使用したクエリを実行します。

UDF の作成

UDF は、Cloud Storage に保存するか、ローカルテキストファイルとして保存できます。たとえば、次の urlDecode UDF を保存するには、urldecode.js という名前のファイルを作成し、以下の JavaScript コードをファイルに貼り付けて保存します。

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

クエリの作成

クエリをファイルに保存して、コマンドラインが冗長になりすぎないようにすることもできます。たとえば、query.sql というローカルファイルを作成し、そのファイル内に次の BigQuery ステートメントを貼り付けることができます。

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

ファイルを保存したら、コマンドラインでそのファイルを参照できます。

クエリの実行

UDF とクエリを個別のファイルで定義したら、コマンドラインでそれらを参照できます。たとえば、次のコマンドは query.sql というファイルとして保存したクエリを実行し、作成した UDF を参照します。

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

BigQuery API の使用

configuration.query

UDF を使用するクエリには、クエリ内で使用されるコード（またはコードリソースの場所）を指定する userDefinedFunctionResources 要素を含める必要があります。指定されるコードには、クエリで参照されるすべての UDF の登録関数呼び出しが含まれている必要があります。

コードリソース

クエリ構成には、JavaScript コード blob や、Cloud Storage に保存された JavaScript ソースファイルへの参照を含めることができます。

インラインの JavaScript コード blob は、userDefinedFunctionResource 要素の inlineCode セクションに入力されます。ただし、複数のクエリで再利用または参照されるコードを Cloud Storage に保存し、外部リソースとしてそれらを参照する必要があります。

Cloud Storage にある JavaScript ソースファイルを参照するには、userDefinedFunctionResource 要素の resourceURI セクションを、そのファイルの gs:// URI に設定します。

クエリ構成には複数の userDefinedFunctionResource 要素を含めることができます。各要素に inlineCode または resourceUri セクションを含めることができます。

例

次の JSON サンプルは、2 つの UDF リソースを参照するクエリリクエストを示したものです。1 つはインラインコードの blob で、もう 1 つは Cloud Storage から読み取られる lib.js ファイルです。この例では、myFunc と myFunc の登録呼び出しが lib.js によって提供されています。

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

トップへ戻る

ベストプラクティス

UDF の開発

Google の UDF テストツールを使用すれば、BigQuery の料金を増やすことなく、UDF のテストやデバッグを実行できます。

入力を事前に絞り込む

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

クエリの実行の例では、urlDecode への入力として、テーブル全体ではなくサブクエリが渡されます。[fh-bigquery:wikipedia.pagecounts_201504] テーブルの行数は約 56 億行であるため、テーブル全体に対して UDF を実行すると、JavaScript フレームワークは、絞り込んだサブクエリを使用した場合の 21 倍以上もの行を処理しなければならなくなります。

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

変更可能状態を複数の UDF 呼び出しで保存（またはアクセス）することは避けてください。次のサンプルコードは、このシナリオについて説明したものです。

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

上記のサンプルは意図したとおりに動作しません。これは、BigQuery が複数のノードにわたってクエリをシャードするためです。各ノードには、numRows の個別値を蓄積するスタンドアロンの JavaScript 処理環境があります。

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

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

SELECT クエリを展開する

UDF から選択する列を明示的にリストする必要があります。SELECT * FROM <UDF name>(...) はサポートされていません。

入力行データの構造を調べるには、次のように JSON.stringify() を使用して文字列出力列を出力できます。

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

トップへ戻る

制限

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

トップへ戻る

制限事項

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

トップへ戻る

レガシー SQL のユーザー定義関数

UDF の例

UDF の構造

UDF の登録

入力列

出力スキーマ

UDF の定義またはリファレンス

エラー処理

UDF によるクエリの実行

bq コマンドライン ツールの使用

UDF の作成

クエリの作成

クエリの実行

BigQuery API の使用

configuration.query

コードリソース

例

ベスト プラクティス

UDF の開発

入力を事前に絞り込む

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

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

SELECT クエリを展開する

制限

制限事項

bq コマンドラインツールの使用

ベストプラクティス