engineers_blogこんにちは。インフラエンジニアの村上です。サービスのインフラの中でも物理的なものからOS・ミドルウェア・アプリケーションのメンテナンス・ビルド・リリース・運用まで幅広く関与しています。PlatformのBigQueryを活用してアクセスログの分析環境を構築した時の話を紹介します。この記事に書かれる事データ分析基盤としてBigQueryを使用した話とデータ量を例示しながら使用を開始した時のトラブルシュートとパフォーマンスについて紹介する。データ移行のコツもうまく含めながら書いていく。BigQueryを採用した訳マネーフォワードの家計簿は350万人以上のお客様に利用いただき、アクセスログは日々2.500万件程度増えております。(83.2億件)のデータをMySQL上に保管していました。数億レコードに対してデータ分析を行う上でMySQLだけだと辛い。事で、データ分析基盤を構築する事になりました。最近活用事例も多く、予算見積もりした時も低コストだったBigQueryを試してみる事になりました。予算を決める社内で新しい事を始める時には予算を決めて、その枠組でまず頑張ってみる事にしています。アクセスログに関しては以下の様に見積もりをしています。データ量を試算する見積もりの為、BigQueryに保存した時のデータ量を以下の流れで求めます。数カ月分BigQueryに入れてみてサイズ比較を行うMySQL上のデータサイズと比率を元に、BigQueryに全件入れた時のデータサイズを求める。MySQLで圧縮をかけている、いないで比率は変わると思われます。弊社環境では圧縮していました。コストを試算する保存コストBigQueryは長期保存の料金というのがあるので、インポート直後と、3ヶ月経過後で金額を計算しておきます。1320円TB単位でいれてこの値段なので保管に関してものすごく安いですね。までは無料を加味する。70950円ストリーミングコストバルクでいれるのでストリーミングコストはなし。トータルコストを算出する保存:1320円~2640円クエリ:6600円~70950円トータル:7920円~73590円となりました。この数値を元に社内では\30,000~\50,000目安で活用してみよう。/BigQuery/料金]を参照ください。Dataが提供する[embulk]を利用してデータをBigQueryに入れる事にしました。過去分・日次のデータ投入もEmbulkを使用しています。ストリーミングインサートはお金がかかるので避ける事で費用を節約しています。データ量に合わせて作成し、これらをScreenを貼った上でfor文でぐるぐる実行しています。EmbulkはGZIPオプションをつけるとCPUを結構使います。今回は1台で実行した結果、全データ移行に2~3週間かかっています。分けて実行する様にした所安定して移行が終わりました。記録を残してる範囲での処理時間と対象期間の分割は以下になります。ここまでは月単位で行けた。ここからは1カ月6分割して行けた。運用スクリプト日次ではJenkinsから朝6時にShellを呼び出して、Embulkを使ってBigQueryにデータを入れています。大体1日2000万件~2500万件のレコードを1時間強の時間で処理を終えています。処理フローは以下です。EXITでcatchする様にした。exit_statusはデフォルト9にして、正常終了時だけ0にUpdateしてからtrapに辿り着く様にした。最近の処理時間は以下の様な感じです。2時間57分サーバー側のリソースモニタCPU2コアのマシンで実行していますが、コアはそこそこ活用して動いています。Out句のGZIPオプションでGZIP化している処理がCPUを使う様です。GZIPを外すとCPUはかなり落ち着きますが、BigQueryに転送する時の転送スピードが遅くなります。MEM12GBのマシンの表示です。一時的にメモリは結構な量を使っている様です。100MのインターネットLAN、eth1は1GのインターナルなLANです。BigQueryに書き込みに行くところで、一時的に15M~20M程使っています。トラブルシュートさて、順調に行ったかの様に見えるデータ移行ですが、幾つかのエラーに遭遇したのでここに記録します。2013年分のログは一括でインポート出来て非常に順調だったのですが、2014年分を丸っと入れようとすると、以下のエラーが出ました。BigQueryにデータを入れる時に、1トランザクションで一定の処理時間を越えるとトランザクションがタイムアウトしてしまう様です。3000を足す事で対応しました。データ量が増えるとこれでも60分で落ちる様になるので対象期間を絞る事で発生しなくなりました。Committed.データ取得がタイムアウトする原因と対策データ量が増えるに従って処理が非常に遅くなってきました。MySQLのデータベースからデータを取る所で30分タイムアウトに引っかかった様子です。対象期間が大きすぎてデータ量が増えたかな?と思い、2016年5月30日の1日分を入れようとするだけでも、タイムアウトエラーが出てしまいました。これではDailyBatchが動かせないので困ります。つける事で解消しました。integerで落ちるケースがある。大量に転送していた中で2回程の以下のエラーに遭遇しました。Embulkの不具合なのかはわかりませんが、対応策として以下2点が効きました。・対象期間を短くする。integerまとめ本稿ではEmbulkを活用して83億レコードのデータをどうやって移行していくかについてご紹介しました。indexを有効に利用する事で、順調にデータが移行できました。Embulkを利用する事でBigQueryのコストも低コストに抑える事が出来ました。分析や調査にBigQueryの高速な検索を利用できるので是非みなさんも試してみてください。おわりにマネーフォワードでは、新しい取り組みにワクワクできるような仲間を募集しています。ご応募お待ちしています。