AWS Summit Tokyo 2014: Amazon Redshiftによるリアルタイム分析サービスの構築

Cookpadのエンジニアの青木さんによる、Redshiftの活用方法です。　

さきほどのEMRのセッションも聞きつつも、SQLの世界は深いので、まだまだSQLで頑張れる部分はたくさんあるのではないかと思っています。 Redshiftで可能なことのごく一部しか使っておらず、「Redshiftじゃきつい」というレベルにもまだないです。

ただ、いうほど”リアルタイム”ではなかったです。ユーザーからの検索はリアルタイムで直接SELECTしているみたいですが、データ更新はバッチ処理でした。

User – EC2 – Redshiftという構成になっているのが一番驚きました。「たべみる」がB2Bサービスなのでそれほど同時に使う人数が多くないのでしょうが、こういう構成は無理かなと思い込んでいたのでとても参考になりました。

たべみる: 検索システム

「バレンタイン」のキーワード分析　単語の組み合わせ　マッチ度　バレンタイン + 本命, 簡単, 大量

User – ELB – EC2 – Redshift

Rails4 / Ruby2.1 dw2.large x 12

目標応答時間 500msec

応答速度向上の施策圧縮タイプの選択 sort key: where句で入れているものは全てSort Keyに入れる大きく削るならDist Key, 細かいチューニングはSort key サマリーテーブル: 大きいデータをクエリすると絶対に時間かかるので、サマっておく

サマリーテーブルいつ作る？安心と伝統の夜間バッチ 37minで実行できているのでHourlyの更新も可能だが、あえてやっていない。不要なので。 B2Bサービスなので、1日に少しずつ追加されてもあまり意味が無いため。

ログまたはMySQL –> file –> Redhisft(raw –> 中間データ –> summaryの三段階)

信頼と実績のTSV渡し

他の用途ではfluentdによる継続ロードも

ELTのお約束
- エクスポート禁止:　データ移動は遅い　 DB外処理は非並列
- UPDATE禁止: 複数回実行しにくい(冪等にしておきたい) VACUUMが厄介
- 1行INSERT禁止: COPYかINSERT SELECT
INSERT SELECTだけ使え！！
SQLでは書けなかった処理
- 日本語処理　半角→全角変換
- 可変数カラム　タブ区切り文字列の分割
- 要するに文字列処理がきつい
SQLで書ける！こんな処理
- 単語の組み合わせ生成ジョイン
- 未来の月曜日N週ぶんの生成ジョイン
- テーブルの縦横変換ジョイン
- 移動平均、移動累積和ウィンドウ関数
- グループごとのランキングTOPN ウィンドウ関数
テーブルの縦横変換の例
グループごとのランキングの例ウィンドウ関数を使うと、分割するけど、これを集約せずに結果だけ取ることができる
- rank() over ( partition by ~ order by ~)

差分更新
- 追加されたデータだけ更新する
- デメリットは、冪等にしくくいこと
- そのため、あらかじめ生成されるレンジのデータを消してからデータを挿入する処理にする
洗い替え
- dropまたはtruncateして作りなおす
- 差分を考える必要がない
- 遅い
- 新しいテーブルを作ると権限(GRANT)を1個1個つけて回る必要があり、面倒
- VACUUM問題
アトミック洗い替え
- 新しいテーブルを作ってる途中にテーブルが無くなってしまうので、テーブルが作り終わってからTRANSACTION内でALTER TABLE RENAMEする
- 24時間参照するテーブルではこれを使う
- デメリットとしては、データが2倍になってしまう
応用：アトミックな本番切り替え
- 複数のテーブルをいっぺんに切り替える
- 複数のテーブルがお互いにデータが関連している場合
更新パターンの使い分け
- 生データは差分更新
- 中間データは洗い替え
- サマリーデータはアトミック洗い替え