早速後編です。実は筆者もBigQueryをガチで使うのは初めてです。使ってみてわかったのは、結構普通のSQL(私の普通はOracle基準、つまりMySQLから見たらコテコテの機能モリモリのSQL)が通るということです。UDFもPreview？で通るらしいですが、今回はUDFまでは行きませんでした。

BQ予備知識

BQの予備知識としては
- SQLで問い合わせる
- Updateが出来ない
- Deleteが出来ない

まずは、これだけ知ってれば十分だと思います。BQの紹介で gcpja-night でも言われてたことですが。
SQLがエンジニアだけのものだと言うのは間違いで、非エンジニアでもSQLぐらい叩けるひとはザラに居るので、1から10までエンジニアが面倒見なくていい。
とのことです。エンジニアのくせにSQLが出来ないという人は、、、反省して勉強して下さい。
それてしまいましたが、SQLはやっぱり偉大だということです。英語が話せれば世界中のひととry 的な話です。
UpdateおよびDeleteが出来ない点ですが、前編でも出しましたが、列ベースのDBとしては仕方がないそうです。まあ、UpDateはインラインビューでどうとでもなるとして、Deleteについてはどうするかというと、テーブルの末尾に日付をつけるなどして、別テーブル扱いにします。古くて要らないデータはテーブルごと殺します。
じゃあ、テーブルが割れるじゃないか！ということですが、それも想定の範囲内。テーブルの末尾に日付がつくのですが、このテーブル名に対して Where句が掛けられます。さすがよく出来てる、歴史も長いしね。

Access Logの解析

さて、いよいよ解析するぞ-

BQに届く頻度(リアルタイム性)

テーブル名は日付で割れますが、1日1回というほどズボラではありません。Cloud Loggingをかましているので、どう考えても Fluentd BigQuery Pluginには負けると思いますが、だいたい5-10分位のインターバルかと。

BQに届いたデータ

前編の設定がちゃんと行き届いていれば、下記のようになっていると思います。
f:id:iga-ninja:20150429160733p:plain
Fluentのtag = Cloud Loggingのログ名 = BQのテーブルPrefix となります。日付が変わる毎に新しいテーブルが生成されます。

では、中身を確認します。
f:id:iga-ninja:20150429160808p:plain
f:id:iga-ninja:20150429160844p:plain
textPayloadに生ログが入っています。これをパースして使える状態にしなければいけません。

textPayloadの分解

今回の例は apache combimed形式です。パースのことを考えればaccess_logの時点でTSV(LTSVのLの部分はいらないかもしれない)が良さそうですが、みんなが欲しがるcombinedで。

やり方ですが、まあ、正規表現ですよね。BQの正規表現ってどうなってるのかな？と調べたら

https://cloud.google.com/bigquery/query-reference#regularexpressionfunctions

REGEXP_EXTRACTで後方参照ですね。楽勝楽勝！あとはcombinedの正規表現など、ググればすく出てくるのでそいつをアレンジすればOK。

SELECT 
REGEXP_EXTRACT(textPayload,r'^(\S*) \S* \S* \[[^]]*\] ".*?" \S* \S* ".*?" ".*?"') AS host,
REGEXP_EXTRACT(textPayload,r'^\S* (\S*) \S* \[[^]]*\] ".*?" \S* \S* ".*?" ".*?"') AS identify,
REGEXP_EXTRACT(textPayload,r'^\S* \S* (\S*) \[[^]]*\] ".*?" \S* \S* ".*?" ".*?"') AS user,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[([^]]*)\] ".*?" \S* \S* ".*?" ".*?"') AS time,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] "(.*?)" \S* \S* ".*?" ".*?"') AS request,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" (\S*) \S* ".*?" ".*?"') AS status,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" \S* (\S*) ".*?" ".*?"') AS bytes,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" \S* \S* "(.*?)" ".*?"') AS referer,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" \S* \S* ".*?" "(.*?)"') AS agent
FROM [all_logs.apache_access_20150428]

これで変換は完了。.* にこだわる理由は何？的な質問もあろうかと思いますが、"-" が "" となるケースもあるらしく、この道の著名人もcombinedの正規表現には結構しくじってます。これが正解かはわかりませんので、使うときは要確認で。解析の前に、とりあえずこいつをViewにしちゃいます。
f:id:iga-ninja:20150429161031p:plain
f:id:iga-ninja:20150429161232p:plain

集計する

たいして面白くない集計ですがとりあえずできた。

SELECT host, sum(integer(bytes))  FROM [all_logs.parced_view] group by host

結果はこんな感じ
f:id:iga-ninja:20150429161053p:plain

複数日付(テーブル)にまたがる場合

table wildcard functions とやらを使えば良さそうです。
https://cloud.google.com/bigquery/query-reference#tablewildcardfunctions

早速試してみましょう。1日たたないとこのテスト出来ないので、翌日にでもどうぞー

SELECT
REGEXP_EXTRACT(textPayload,r'^(\S*) \S* \S* \[[^]]*\] ".*?" \S* \S* ".*?" ".*?"') AS host,
REGEXP_EXTRACT(textPayload,r'^\S* (\S*) \S* \[[^]]*\] ".*?" \S* \S* ".*?" ".*?"') AS identify,
REGEXP_EXTRACT(textPayload,r'^\S* \S* (\S*) \[[^]]*\] ".*?" \S* \S* ".*?" ".*?"') AS user,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[([^]]*)\] ".*?" \S* \S* ".*?" ".*?"') AS time,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] "(.*?)" \S* \S* ".*?" ".*?"') AS request,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" (\S*) \S* ".*?" ".*?"') AS status,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" \S* (\S*) ".*?" ".*?"') AS bytes,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" \S* \S* "(.*?)" ".*?"') AS referer,
REGEXP_EXTRACT(textPayload,r'^\S* \S* \S* \[[^]]*\] ".*?" \S* \S* ".*?" "(.*?)"') AS agent
FROM
(TABLE_DATE_RANGE(
    all_logs.apache_access_,
    TIMESTAMP('2015-04-27'),
    TIMESTAMP('2015-04-29')))