問題設定

「蓄積してあるアクセスログをとりあえずBigQueryにアップする」使い方、よくあるパターンかと思います(ひとまず集計できるようにしたい、的な)。その際、扱いに困るのがユーザエージェントの処理です。この記事を書いているブラウザでは、以下のような文字列になります。

Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.73 Safari/537.36

アクセスしている状況は、

Windows 10
Google Chrome

です。例えば、OS別/ブラウザ別アクセス状況とかを集計したくなります。

SQLでとりあえず動くようにすると、例えば
http://www.slideshare.net/yukawabe18/kaizen-platform-bigquery-bqsushi-tokyo-2
の6,7ページあたりに、case文で対応している例があります。このcase文だと、

OSのバージョンは判定してない
ブラウザ判定の正規表現が長くて不安(正しいの？/メンテできるの？)

という問題がありそうに思います。

User Defined Function(UDF)の機能を使って、この問題を解決することを考えます。
以下のような条件を満たすものがあればよいわけです。

(UDFの制約として) JavaScriptで書かれている
(UDFの制約として) Window, Document, Node などのオブジェクトに依存していない
(実現したいこととして) OSのバージョンが判別できる
(実現したいこととして) ある程度のUser Agentを網羅していて、テストされている

「JavaScriptでユーザエージェント判別」という、よくありそうなタスクなので、OSSを探してみると候補がいろいろ出てきます。ただ、動かすのがブラウザ上でなくBigQuery上なので、2つめの制約があることに気づきます(公式ドキュメントにも書いてあります)。探してみた結果、woothee.jsがこの制約を満たしてくれそうだと判断して、これを使うことを考えます。

解決案

早速試してみましょう。

// https://github.com/woothee/woothee-js/blob/master/release/woothee.js
// をコピペした後、以下のようなJavaScriptでUDFを使えるようにします。

// UDF definition
function userAgent(row, emit) {
  emit(uaHelper(row.user_agent));
}

// Helper function with error handling
function uaHelper(s) {
  try {
    return woothee.parse(s);
  } catch (ex) {
    // エラーが起きたらエラーメッセージを返すようにしておきます
    return {message: ex.message}; 
  }
}

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

  ['user_agent'],  // Input column names

  // JSON representation of the output schema
  [{name: 'name', type: 'string'},
   {name: 'category', type: 'string'},
   {name: 'os', type: 'string'},
   {name: 'version', type: 'string'},
   {name: 'vendor', type: 'string'},
   {name: 'os_version', type: 'string'},
   {name: 'message', type: 'string'}],

  userAgent // The function reference
);

-- UDFの動作確認として、以下のようなクエリを実行してみることにします
select os, vendor, name from
userAgent(select 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.73 Safari/537.36' as user_agent) ua

JavaScriptが1349行というサイズで何か間違ったことをしている気がしてきますが、気にせずWeb UIからRUN QUERY ボタンを押してみましょう。

見慣れないエラーが出てきました。どうやら、UDFのソースコードサイズには32KBの制限があるようです。公式ドキュメントに載ってない気がします。
JavaScriptなので、minifyして再度トライしてみます。ここではググって最初に出てきたサイトで、woothee.jsを圧縮してみました。

無事、Windows10, Google Chrome を使用していると判別することができました。
ここでは出力をお見せしませんが、実際のアクセスログのデータセットにも適用できることを確認しています。

発展

今回の解決案のパターン(JavaScriptのOSSをコピペして動くようにする)はほかにもある気がしています。私が見つけたパターンだと、URLエンコードがSJISやEUCJPなどのUTF-8でないエンコーディングの文字列を元にしているケースで、ecl.jsを利用するパターンです。

ecl.js
http://www.junoe.jp/downloads/itoh/enc_js.shtml

実際のデータセットでdecodeURIComponentをかけてみてエラーになったので、このecl.jsに行き着きました。エラーメッセージを確認できるようにしておくのは重要です。

別の方法

この解決案はお手軽に見えるのですが、以下のようなデメリットもあります。

2016/1からの新料金プランではCPU使用量もコストに加算されるので、UDFをいつでも使うのはお値段かかりそう。
実際にどれくらいかかるかは何とも言えない。特に、UDFを使うと今時点(2015/12/08)ではtotalBytesProcessedがなぜか0になっている。
メンテンナンス対象がSQL以外に発生する。特に、UDFのカラムの増減が面倒。

UDFを使うことを考えず、アップロード前にアクセスログにユーザエージェント判別結果を追加しておくことも考慮にいれるのがよいと思われます。もちろん、事前に処理するコストが発生するので、一長一短あると思います。お手軽にアップしたデータをお手軽に集計する手段としては、今回の解決案はアリかなと思っています。

BigQuery User Defined Functionとwoothee.jsでユーザエージェント判別する

問題設定

解決案

発展

別の方法