Amazon Redshiftによるリアルタイム分析サービスの構築
•
83 likes•33,819 views
AWS Summit Tokyo 2014「Amazon Redshiftによるリアルタイム分析サービスの構築」 COOKPADが提供している分析サービス「たべみる」のアーキテクチャ
Amazon Redshiftによるリアルタイム分析サービスの構築
- 3. ユニークブラウザ数 0万 1,000万 2,000万 3,000万 4,000万 5,000万 2013/4 Q1 Q2 Q3 Q4 2014/4 Q1 Q2 Q3 Q4 PC スマホ（ブラウザ） スマホ（アプリ） フィーチャーフォン その他 4,404万UB （2014年4月期 決算説明会資料より）
- 4. ‣ 単語の検索頻度 ‣ 単語の組み合わせ頻度 ‣ 現在盛り上がっている 単語 ‣ ユーザーの年代や居住 地域を軸とした分析 たべみる
- 7. Agenda 1. たべみるのシステム構成 2. データ連携の詳細 3. SQLによるデータ処理 4. データ更新のパターン
- 9. システム構成詳細 ‣ app: Rails 4 / Ruby 2.1 ‣ db: Redshift (dw2.large 12)
- 10. 目標応答時間
- 11. 500ms
- 12. 応答速度向上の施策 1 圧縮 2 sortkey 3 サマリーテーブル ※10ミリ秒単位でチューニングする場合、分散キー（distkey）はほぼ無関係
- 13. サマリーテーブルいつ作る？
- 14. 安心と伝統の 夜間バッチ
- 15. Redshift MySQL Treasure Data (Hadoop) 夜間バッチ構成 検索ログ ユーザーマスター バッチ サーバー 単語マスター file file file 生データ 中間データ サマリー table table table table table table table table table table
- 17. データ連携
- 18. 信頼と実績の TSV渡し
- 19. バッチ サーバー TSV ソースDB テーブル Redshift テーブル データ連携方式 S3 TSV PUT COPYexport ‣ フォーマット変換 ‣ クレンジング ‣ SQLでできない処理
- 22. サマリーテーブル作成
- 23. SQLで加工 （ELT）
- 24. ELTのお約束 1 エクスポート禁止 データ移動は遅い DB外処理は非並列 2 UPDATE禁止 複数回実行しにくい VACUUMが厄介 3 1行INSERT禁止 COPYかINSERT SELECT
- 26. SQLでは書けなかった処理 1 日本語処理 半角→全角変換 2 可変数カラム タブ区切り文字列の分割
- 27. ようするに 文字列処理
- 28. SQLでも書ける！こんな処理 ‣ 単語の組み合わせ生成 ‣ 未来の月曜日N週分の生成 ‣ テーブルの縦横変換 ‣ 移動平均、移動累積和 ‣ グループごとのランキングトップN ジョイン ジョイン ジョイン ウィンドウ関数 ウィンドウ関数
- 29. テーブルの縦横変換 log_time num word1 word2 word3 18:00:00 1 おじや 18:03:42 2 大根 煮物 18:19:01 1 ケーキ 18:32:56 3 えび グラタン ▲マカロニ 18:49:23 2 おじや たまご log_time seq word 18:00:00 1 おじや 18:03:42 1 大根 （同上） 2 煮物 18:19:01 1 ケーキ 18:32:56 1 えび （同上） 2 グラタン （同上） 3 ▲マカロニ 18:49:23 1 おじや （同上） 2 たまご 「横持ち」テーブル 「縦持ち」テーブル
- 30. テーブルの縦横変換 seq 1 2 3 4 5 6 transpose_matrix select log.log_time , m.seq , case m.seq when 1 then log.word1 when 2 then log.word2 when 3 then log.word3 when 4 then log.word4 when 5 then log.word5 when 6 then log.word6 end as word from search_log log inner join transpose_matrix m on log.num >= m.seq;
- 31. グループごとのランキング user_category_id keyword search_count rank 20 サラダ 235,097 1 20 パスタ 190,413 2 20 もやし 187,999 3 30 なす 321,977 1 30 きゅうり 260,460 2 30 じゃがいも 217,201 3 40 大根 229,324 1 40 ささみ 201,876 2 40 チーズ 179,024 3 50 大根 139,141 1 50 いんげん 110,471 2
- 32. グループごとのランキング user_category_id keyword search_count rank 20 サラダ 235,097 1 20 パスタ 190,413 2 20 もやし 187,999 3 30 なす 321,977 1 30 きゅうり 260,460 2 30 じゃがいも 217,201 3 40 大根 229,324 1 40 ささみ 201,876 2 40 チーズ 179,024 3 50 大根 139,141 1 50 いんげん 110,471 2
- 33. グループごとのランキング user_category_id keyword search_count rank 20 サラダ 235,097 1 20 パスタ 190,413 2 20 もやし 187,999 3 30 なす 321,977 1 30 きゅうり 260,460 2 30 じゃがいも 217,201 3 40 大根 229,324 1 40 ささみ 201,876 2 40 チーズ 179,024 3 50 大根 139,141 1 50 いんげん 110,471 2
- 34. グループごとのランキング select user_category_id, keyword, search_count, rank() over ( partition by user_category_id order by search_count desc ) from user_category_search_count;
- 36. データ更新パターン
- 37. テーブル更新パターン 1 差分更新 更新データのみ追加 2 洗い替え 全行作り直し 3 アトミック洗い替え 洗い替えの変種
- 38. パターン１：差分更新 ターゲットテーブルソーステーブル 追加されたデータ 差分 insert into TARGET_TABLE select … from SOURCE_TABLE where data_date >= TODAY ;
- 39. パターン２：洗い替え 作成 drop table TARGET_TABLE; create table TARGET_TABLE (…); insert intoTARGET_TABLE select … from SOURCE_TABLE; ターゲットテーブルソーステーブル
- 40. ターゲット テーブルソーステーブル 新規テーブル 作成 すり替え create table t_NEW (…); insert into t_NEW select … from SOURCE_TABLE; begin transaction; alter table TARGET_TABLE rename to t_OLD; alter table t_NEW rename to TARGET_TABLE; commit; パターン３：アトミック洗い替え
- 41. 応用：アトミックな本番切り替え targetsource new 作成 すり替え targetsource new targetsource new
- 43. 更新パターンの使い分け 生データ 中間データ サマリー table table table table table table table 差分更新 洗い替え アトミック 洗い替え
- 45. まとめ
- 46. なぜRedshiftだったのか 1. COOKPADがAWSにあるから 2. マネージドサービスだから 3. 並列処理性能が高いから 4. ウィンドウ関数など高度な機能が実装されているから 5. あるていどオンライン処理にも耐えられるから