爽快！分析基盤の紹介 #3 : RDB snapshot と集計用テーブル作成編

おはようございます。こんにちは。こんばんわ。

急に寒くなって鍋とか食べたくなりましたね。モツ鍋大好きです。佐々木です。

今日はセカイエアドベントカレンダー6日目です。

qiita.com

今日は RDB の snapshot とそれを使った集計用テーブルについて書きたいと思います。

分析・集計における snapshot の役割

サービスを運用にするにあたって RDB の使用はほぼ不可欠です。

RDBの snapshot の取るポリシーは様々だと思いますが、大事なデータのバックアップ等に使用することが多いでしょう。

AWS の RDS にも DB の snapshot を取る仕組みはありますね。

docs.aws.amazon.com

ただ分析における snapshot を取る意味はちょっと変わってきます。

セカイエの分析基盤ではアクセスログだけでなく各サービスのテーブルのデータを利用した集計が存在しています（本当はログレベルでその辺のデータがあったほうが、分析部分を切り離せるので理想ですが、今回はここの分については触れないでおきます）

話は戻りますが、分析においてサービスのテーブルを利用する場合（例えば ユーザテーブル）

ユーザの年齢やメールアドレスなど可変なデータが存在しています。

たとえばユーザテーブルを売上のデータなどに join して利用する場合、集計バッチを動かした日によって出来上がるデータが可変になってしまいます。

これは持論なのですが、集計で出来上がるデータはどのタイミングで集計しても、常に同じデータが出来上がらないといけないと思っています。

ですので、ユーザテーブルなどはその日が終わったタイミングで snapshot をとって、その snapshot のデータと各ログデータを組み合わせて集計しています（本当はログレベルでその辺のデータが以下略

RDB 上のデータと BigQuery 上のログをマージさせて分析用データを作成するために

今回はシミュレーションのログとユーザテーブルをマージして集計用テーブルを作成する流れについて説明します。

まずイメージしやすいように全体の流れを貼っておきます。

シミュレーションログ

シミュレーションの使用ログは fluentd を使って BigQuery に投げています

大体こんな感じ

[
  {
    "time": "2015-12-06 07:11:09 UTC",
    "user_id": "xxxxxx",
    "items": [
      {
        "id": "xxx",
        "category": "xxx",
        "series": "xxx"
        "maker": "xxx",
        "price": "xxxxx"
      }
    ]
  }
]

ユーザテーブル

ユーザテーブルは AWS の RDS 上にあります。

日付が変わったタイミングで snapshot 用のスクリプトが RDS の ユーザテーブルを dump して BigQuery 上に転送しています。

大体こんな感じ

[
  {
    "id": "xxx",
    "mail_address": "xxx@xxx.xx",
    "name": "XXX",
    "pref": "xxx",
    "city": "xxx"
    "sex": "xxx",
    "age": "xx",
    "created": "2014-10-09 15:19:56 UTC"
  }
]

集計用テーブル

RDS のテーブルを BigQuery 上にもってきさえすればあとはこっちのものです

2つのテーブルをJOINして集計用のテーブルを作成します。

view を使うというのも考えたのですが過去分を Google Cloud Storage にアーカイブすることを考えたらテーブルにしたほうが楽かなと思ってテーブルを作成しています

[
  {
    "item_category": "XXX",
    "item_maker": "XXX",
    "item_series": "XXX",
    "item_price": "xxx",
    "user_id": "xxx",
    "user_name": "XXX",
    "user_pref": "XXX",
    "user_city": "XXX",
    "user_sex": "XXX",
    "user_age": "XXX"
  }
]

出来上がった画面

分析用シミュレーションテーブルはこんな感じで閲覧できます

終わりに

今日はセカイエアドベントカレンダー6日目で RDS のデータとログデータを組み合わせた集計用テーブルを作成する仕組みについて紹介しました。

明日は松橋さんです。よろしくお願いします

ほな！