BigQuery の UDF を使ってアクセスログからブラウザシェア集計

BigQuery で UDF が使えるようになったので、UDF を使ってアクセスログのデータから簡単にブラウザのシェアを集計してみました。

BigQuery の UDF では、入力として一行のレコードを受け取り、それを変換して新しいレコード（複数行でもよいし0行でもよい）を出力する関数を定義します。今回は単純にユーザエージェントからブラウザ種別を取得したいだけなので、ユーザエージェントのみをカラムとして持つレコードを受け取って、ブラウザ種別のみをカラムとしてもつレコードを出力するようにします。

（前提として、アクセスログのテーブルの ua というカラムにユーザエージェント文字列が記録されているとします。）

UDF

// UDF 本体
function browser(row, emit) {
  emit({browser: ua2browser(row.ua)});
}

// ユーザエージェンと文字列をブラウザ種別に変換するヘルパ関数
function ua2browser(ua) {
  var patterns = [
    [/Chrome/, "Chrome"],
    [/Firefox/, "Firefox"],
    [/Safari/, "Safari"],
    [/Trident.*rv:11\.0/, "IE11"],
    [/MSIE 10\.0/, "IE10"],
    [/MSIE 9\.0/, "IE9"],
    [/MSIE 8\.0/, "IE8"],
    [/MSIE 7\.0/, "IE7"],
    [/MSIE 6\.0/, "IE6"],
    [/MSIE/, "IE"]
  ];
  var n = patterns.length;
  if(ua) {
    for(var i=0; i<n; ++i) {
      if(ua.match(patterns[i][0])) return patterns[i][1];
    }
  }
  return "unknown";
}

// クエリから利用できるようにするために UDF を登録する
bigquery.defineFunction(
  'browser', // 関数名
  ['ua'], // 入力レコードのスキーマ（名前のみ）
  [{'name': 'browser', 'type': 'string'}], //出力レコードのスキーマ（名前と型）
  browser // UDF の function の参照
);

browser() が UDF の本体です。入力レコードを row として受け取り、出力レコードをオブジェクトとして emit の引数に渡します。

あと、ユーザエージェント文字列をブラウザ種別に変換するために ua2browser() というヘルパ関数を定義しています。ユーザエージェント文字列に対してこの正規表現にマッチしたらこのブラウザ種別を返すというようなマッピングを定義しています（ここではあくまで例示のために雑に定義していますので、あしからず）。

最後に、定義した UDF をクエリから使えるように bigquery.defineFunction() で登録します。引数として、以下のものを渡します。

• UDF の名前
• 入力レコードのスキーマ
• 出力レコードのスキーマ
• レコードを変換する function の参照

これでクエリからこの UDF が使えるようになります。

Query

SELECT
  browser, count(browser) count
FROM
  browser(SELECT ua FROM [apache.apache201508])
GROUP BY browser
ORDER BY count desc;

できました！

参考

• BigQuery User-Defined Functions