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

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

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

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

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

次のサンプルコードは、出力スキーマ内のレコードの構文を示したものです。各出力フィールドには、nametype 属性が必要です。ネストされたフィールドには、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 とウェブ UI

BigQuery ウェブ UI を使用して UDF を追加し、クエリの実行時にそれらの UDF を使用できます。

前提条件

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

  1. 以前に Cloud Console を使用していない場合は、コンソールに移動し、利用規約に同意して、新しいプロジェクトを作成します。

  2. BigQuery ウェブ UI に移動する

UDF の追加

  1. [COMPOSE QUERY] ボタンをクリックします。

  2. [UDF Editor] タブをクリックして UDF を追加します。

  3. 次のコードをコピーして、[UDF Editor] テキスト ボックスに貼り付けます。

    // The UDF
    function urlDecode(row, emit) {
      emit({title: decodeHelper(row.title),
            requests: row.num_requests});
    }
    
    // Helper function for 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
    );
    

ウェブ UI でのクエリの実行

  • [Query Editor] タブをクリックします。

  • 次のクエリをコピーして、[Query Editor] テキスト ボックスに貼り付けます。

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

    上記のクエリは、タイトルにセディーユ文字(ç)が含まれるフランス語ウィキペディア記事のうち、2015 年 4 月に特に多くアクセスされた記事を検索します。

  • [RUN QUERY] ボタンをクリックします。クエリの結果がボタンの下に表示されます。

Cloud Storage からコードを参照する

上記の例では、UDF を BigQuery ウェブ UI に直接追加しました。また、JavaScript コードの一部または全部を Cloud Storage に保存することもできます。UDF コードの場所にかかわらず、コード内で bigquery.defineFunction 呼び出しを使って各 UDF を登録する必要があります。bigquery.definefunction の呼び出しは、BigQuery ウェブ UI 内またはリモートコード リソース内で行うことができます。リモート ソースファイルには「.js」拡張子を使用する必要があります。

たとえば、サードパーティのライブラリ、独自の UDF コード、UDF 登録関数の呼び出しを別々のファイルで管理することもできます。これらのファイルは、クエリ内で別個の外部リソースとして読み込まれます。

BigQuery ウェブ UI で外部 UDF を参照する

  1. テキスト ボックスの下にある [Show Options] ボタンをクリックします。

  2. [UDF Source URIs] 見出しの横にある [Edit] ボタンをクリックします。

  3. 各リモート ソースファイルについて、[Add UDF Source URI] ボタンをクリックし、Cloud Storage URI を入力します。

    外部 UDF を使用して上記の URL デコード サンプルを試したい場合は、URI フィールドに bigquery-sandbox-udf/url_decode.js を貼り付けます。これらの手順が完了したら、[UDF Editor] の内容が削除されたことを確認します。

  4. [OK] ボタンをクリックします。

その後、[Query Editor] タブに切り替え、上記のサンプルと同じ手順に従って、クエリ内で UDF を使用できます。

一般に、外部 UDF を使用しているとき、[UDF Editor] テキスト ボックスに追加の JavaScript コードを入力することもできます(ただし、追加の UDF がウェブ UI または外部ファイル内の defineFunction ブロックで登録されている必要があります)。

また、bq コマンドライン ツールを使用して、Cloud Storage に保存されている UDF を参照することもできます。詳しくは、UDF と bq コマンドライン ツールをご覧ください。

トップへ戻る

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

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

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

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

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

UDF の作成

UDF は、Cloud Storage に保存するか、ローカル テキスト ファイルとして保存できます。たとえば、UDF およびウェブ UI で説明されている 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)"

UDF と 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 ファイルです。この例では、myFuncmyFunc の登録呼び出しが 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 オブジェクトの WindowDocumentNode、およびこれらを必要とする関数はサポート対象外です。
  • ネイティブ コードに依存する JavaScript 関数はサポートされません。
  • JavaScript のビット演算は上位 32 ビットのみを処理します。
  • ユーザー定義関数を呼び出すクエリは非確定的な性質であるため、このようなクエリでキャッシュ内の結果を使用することはできません。

トップへ戻る