データ分析用に BigQuery を導入してみた

f:id:hacomono-tech:20210303223036j:plain

はじめましてこんにちは。hacomono の開発をしています、松尾です。

弊社では、リアル店舗のCRM・予約・キャッシュレス決済サービス『hacomono』を開発・運用しております。

www.hacomono.jp

note 初利用です。記事の原稿を markdown で書いていて、出来上がったから後は貼り付けるだけだわ〜と思ってましたが、 note は markdown 形式で書けないんですね。焦りました。

今回は、各テナントのデータ (MySQL) を Google BigQuery に同期する基盤を作ったので、そちらについての記事になります。

背景・課題

hacomono は信頼性が高いシングルテナントとういう構成上、テナントを横断したような総合的な数字が出しにくいという課題がありました。例えば、マルチテナントの場合だと、 SELECT COUNT(*) FROM users WHERE deleted_at IS NULL のような SQL を叩けば会員ユーザー数がすぐに分かりますが、 hacomono はシングルテナントなので、この SQL を全テナントで実行しないと全体のユーザー数がわかりません。

また、 CS で追うヘルススコアを算出し外部サービスに同期する要件も出てきそうだったため、データを 1 箇所にまとめて分析しやすくする必要がありました。

BigQuery を選んだ理由

この課題を解決するための選択肢は 2 つありました。

選択肢1: どこのテナントにも属さない RDS を立てて、そこにデータを投入する
選択肢2: クラウドの DWH を利用する

選択肢 1 は、費用面や各テナントとのつなぎ込みが大変そうだったのでやめました。選択肢 2 で進めていくことになるのですが、どの DWH を利用するかでも選択肢がありました。ざっと調べた感じだと、 AWS の Redshift と Google の BigQuery (以降、 BQ と呼ぶ) がメジャーなのでその比較をしました。

Redshift は稼働しているだけで料金がかかるサービスのようです。対して BQ はデータを保持しておくのは無料で、データを操作するときに料金がかかるタイプのサービスであることがわかりました。そんなに毎日ゴリゴリ分析する必要は今の所はなく、料金的にもスモールスタートしやすそうという理由で BQ を選定しました。

アカウントを作成する

BQ を使うにあたってサービスアカウントを発行する必要があります。一般的なアカウントとサービスアカウントの違いは「サービスアカウントとユーザーアカウントの違い」に詳しく書いてあります。

今回は Ruby を使って同期するので、クライアントライブラリを利用した場合のサンプルを参考にアカウントの発行をして実装していきます。

ロールについてですが、色々試しながら必要最小限にしました。

BQ に投入する MySQL 側のテーブル・カラムの選定

テーブルの選定

BQ を利用することが決まったので、各テナントのテーブルを BQ に Import するコードを Ruby でごりっと書きましたが、ここで問題が発生... サンプルデータを投入するだけで 10 分ほど同期に時間がかかることがわかりました。サンプルデータで 10 分くらいならまぁ良いんじゃね？と思うかも知れませんが、これが 100 テナントある場合を考えてみると、 10 分 * 100 テナントで 1,000 分 = 約 16 時間かかります。これはなかなか現実的ではないなと思い、当初は全テーブルを投入する予定だったのですが、分析に必要そうなテーブルのみを同期対象とする仕組みを入れました。

カラムについて

個人情報に該当するデータは BQ には投入しない、という要件があったのでそれを満たす実装をしました。また、テナント名のカラムを各テーブルに新たに追加しました。このカラムによって、必要なテーブルを UNION ALL したときに GROUP BY で集計することができるようになります。

SELECT
 service_name,
 count(*),
 max(created_at)
FROM
 `hoge_prod.users__*`
GROUP BY
 service_name

BQ 側のデータセット・テーブル名

データセット

hoge_test, hoge_staging, hoge_prod のように環境毎に作成しました。

hacomono は本番環境の他に検証環境というものもお客様に提供しています。各環境毎の数字も取りたいという理由から環境毎にデータセットを分けるようにしました。

また、開発中のミスを防ぐという理由もあります。「よーし、新しく追加したいテーブルが出てきたからコードを追記して開発環境でバッチを動かしてみよう！」となった場合、このようにデータセットを分けておかないと、「bundle exec rake import_big_query」のようなコマンドを打とうものならテナントのテーブルが上書きされてしまいます。 DryRun 機能も実装しましたが、実際に投入して中身を見たいというのは開発時に良くあることなのでデータセットを分けました。ちなみに分けてないと、テストでモックを書き忘れてしまった場合などにもこのようなことが起こりえます。 (テストではモックを使うよう強制するコードを書きましたが念の為ということで...)

テーブル

BQ はカラムベースの DB なので、テーブルが大きくなればなるほど検索にお金がかかります。WHERE や LIMIT で絞っても効果なしです。そのため、各テナント毎にテーブルを作ってなるべくお金がかからないようにしたい思いがありました。「userstenant1」 , 「userstenant2」のようなイメージです。

この設計は時系列のログデータを BQ で管理する際に一般的に使われる方法で、今回のようにテナント毎にテーブルを分けるのがベストな設計なのかは正直わかりません。ネットで調べてもシングルテナントで BQ 使ってみた、のような記事が見当たりませんでした。みんなどうやってるんだろう。