トレタにおけるBigQueryの活用法について

サーバーサイドエンジニアの芹沢です。

トレタは検索用のデータストアとしてBigQueryを使用しています。 奇抜な使い方はしていませんが、トレタにおけるBigQuery活用法を紹介します。

システム構成

BigQuery周りのシステム構成を１枚の図にまとめるとこんな感じです。珍しいものは使っていませんがその分安定した構成かと思います。

BigQueryにimportしているデータ

大きく分けて以下2種類のデータをBigQueryにimportしています。

1.APIが参照しているRDBのデータ

APIが参照しているRDB(Amazon Aurora)のslaveからデータをimportしてデータ分析や調査用のデータ検索業務に使っています。

2.各種ログ

以下のログをfluentdでBigQueryに保存しています。

• nginxのaccessログ
• railsで1リクエスト単位で出力しているカスタムログ
• sidekiqのログ(Sidekiq.loggerが出力するログ)
• shoryukenのログ(Shoryuken.loggerが出力するログ)
• sendgridのメール送信ログ

sendglidのログについては過去に佐野おにいさんが解説記事を書いていますのでこちらの記事を参照ください。

tech.toreta.in

データ可視化ツールとしての活用

社内向けのデータの可視化にはRedashを使い、用途別のDashboardを作成してセールス、カスタマーサポート、マーケティングチームが普段から追っている数値をエンジニアに頼らず検索できるようになっています。

また、BigQueryに入れたログをグラフで描画するなどのエンジニアリング方面でも使用しています。

Redashの導入についてはaccountの権限管理など色々とハマりどころがあったようなのですが、その辺の話はRedash導入をリードしたサーバーサイドエンジニアの中村君がこのブログに書いてくれると思います。

データ集計基盤としての活用

最近トレタのRDBにたまったデータを活用する機運が高まり、データの集計や分析を行うようになりました。（今はどちらかと言えば集計と可視化がメインですが）

何かデータを使ってやりたいことがある時、試行錯誤段階ではRやPython + Jupyter Notebookで少量のデータを対象に前処理や集計をするのですが、それをproductionの全データに適用すると、処理時間がかかりすぎて丸１日かかってしまうといったケースが増えてきました。

そこで、複雑な集計処理や重たい処理はなるべくSQLに落とし込んでBigQueryで処理するようにすることで解決を図っています。

例えば、普通にコードで書くと全件ループせざるを得ず数十秒かかるような集計処理でも、WINDOW関数などを駆使してSQLに落としてこんでクエリを投げると、何事もなかったように数秒で結果が返ってきます。1回のクエリで処理できるデータのサイズに上限があるため、あらゆることが1回のクエリで解決する訳ではありませんが大抵は問題なく実行できています。
余談ですが、pythonのデータ分析ライブラリのpandasには、BigQueryにクエリを投げて結果をdataframeに変換して返す処理をread_gbqという関数一つで実現できるので大変便利です。

SQLに寄せる最適化を進めた結果、bqコマンドラインツールでクエリを投げて結果を別のテーブルにimportするだけで完了する処理も出てきました。BigQueryのUDFはまだ使っていないのですが、UDFも活用すればさらにscript less化が加速するのではないでしょうか。

なお、トレタではこういったデータエンジニアリングや、ビジネス方面のアナリティクスが得意なデータサイエンティストを募集しています。こちらもよろしくお願いします。

www.wantedly.com

ログ検索基盤としての活用

sidekiqとshoryukenのログ本文には、Workerの名前、ジョブのIDやステータス、実行したスレッドのIDなどが含まれています。これらの情報をキーとして利用しやすいにように、parseして構造化する必要があります。また、個人情報を出力することもあるため、マスキングも必須です。

これらの課題を解決するため、LogのFormatterを独自のものに置き換えて運用しています。現在は以下のようなjsonのformatでログを出力してBigQueryにimportしています。

{
   "datetime":1472553986,
   "hostname":"sidekiq-server",
   "pid":"17863",
   "tid":"owww1vp08",
   "severity":"INFO",
   "worker":"TestWorker",
   "jid":"JID-08102742edd82acc5b698f7e",
   "job_action":"done",
   "processing_time":10.000,
   "formatter_err":null,
   "message":"{\"class_name\":\"String\",\"body\":\"done\"}",
   "log_version":1
}

Sidekiq.logger のinfo , error 等のメソッドの引数に渡されたデータは messageというキーにjsonとして格納されるので、BigQueryのJSON関数を使ってカラムに展開した一時VIEWを作成し、そのVIEWに対してクエリを実行することでメッセージの本文で絞り込むことが可能です。

例えば、過去10日間のログの中からerrorという文字列がmessageに含まれるものを検索したい時は、こんな感じのSQLで取得できます。

#standardSQL
WITH
  MESSAGE_EXTRACTED AS(
  SELECT
    TIMESTAMP_SECONDS(datetime) AS UTC_DATETIME,
    worker,
    JSON_EXTRACT_SCALAR(message, '$.class_name') AS mesasge_class_name,
    JSON_EXTRACT_SCALAR(message, '$.body') AS message_body
  FROM
    `sidekiq_logs*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(DATE(CURRENT_TIMESTAMP(), 'Asia/Tokyo'), INTERVAL 10 day))
    AND FORMAT_DATE("%Y%m%d", DATE(CURRENT_TIMESTAMP(), 'Asia/Tokyo')) )
SELECT
  *
FROM
  MESSAGE_EXTRACTED
WHERE
  message_body LIKE '%error%'
ORDER BY
  UTC_DATETIME DESC
LIMIT
  1000

まとめ

簡単ではありますがトレタにおけるBigQueryの活用方法について紹介させていただきました。
ビジネスにおけるデータ活用の重要度が日々高まる昨今、トレタもデータから得た知識を活用してビジネスを加速させていきたいと思います。