レガシー 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}, ... ]

出力スキーマ

UDF によって生成されるレコードのスキーマまたは構造を BigQuery に提供する必要があります(これは 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 とウェブ UI

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

要件

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

  1. これまでに GCP 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 デコード サンプルを試したい場合は、bigquery-sandbox-udf/url_decode.js を URI フィールドに貼り付けます。これらの手順が完了したら、[UDF Editor] の内容が削除されたことを確認します。

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

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

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

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

トップへ戻る

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

--udf_resource フラグを指定して 1 つ以上の UDF を含むクエリを実行するには、Cloud SDKbq コマンドライン ツールを使用できます。フラグの値には、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 ビットのみを処理します。
  • ユーザー定義関数を呼び出すクエリは非確定的な性質であるため、このようなクエリでキャッシュ内の結果を使用することはできません。

トップへ戻る

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

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

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