早速後編です。実は筆者も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句が掛けられます。さすがよく出来てる、歴史も長いしね。
さて、いよいよ解析するぞ-
BQに届く頻度(リアルタイム性)
テーブル名は日付で割れますが、1日1回というほどズボラではありません。Cloud Loggingをかましているので、どう考えても Fluentd BigQuery Pluginには負けると思いますが、だいたい5-10分位のインターバルかと。
BQに届いたデータ
前編の設定がちゃんと行き届いていれば、下記のようになっていると思います。
Fluentのtag = Cloud Loggingのログ名 = BQのテーブルPrefix となります。日付が変わる毎に新しいテーブルが生成されます。
では、中身を確認します。
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にしちゃいます。
集計する
たいして面白くない集計ですがとりあえずできた。
SELECT host, sum(integer(bytes)) FROM [all_logs.parced_view] group by host
結果はこんな感じ
複数日付(テーブル)にまたがる場合
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')))
ちゃんと3日分のデータが引けるはずです。いちいち日付を指定するのが面倒であれば、Now()とか使えばOKだし、逆に存在するテーブル全部とかも日付の幅を広げれば可能。
まとめ
- Cloud Loggingから出てくるデータは textPayloadに生のまま入っている
- textPayloadを分割するView(インラインビュー)を作成すると色々楽
- 日付で割れたテーブルも table wildcard functions で楽勝！
リファレンス眺めてるだけでも楽しい！JDBCもいけるし、GASでGoogleスプレッドシートとかとも連携できる。手軽にこんなシステムが使えるなんていい時代。
