![【ウェブ セミナー】AI 時代のクラウド データ ウェアハウス Azure SQL Data Warehouse [実践編]](https://cdn.slidesharecdn.com/ss_thumbnails/webinarsqldw20170726-180220004900-thumbnail.jpg?width=560&fit=bounds)
More Related Content
What's hot (20)
Similar to Logをs3とredshiftに格納する仕組み (20)
More from Ken Morishita (9)
Logをs3とredshiftに格納する仕組み
- 1. LogをS3と Hive Redshi/ に 格納する仕組み 2013年5月22日 株式会社ゆめみ 森下 健 mokemokechicken@twi;er 1
- 2. 作るきっかけ アプリケーションログをMySQLに保存している （調査目的） MySQLだとスケールしない S3やHadoop(Hive)上に保存しよう （スケールしそう） 2 １００〜２００Write/sec くらいでキツイ
- 3. 作るきっかけ アプリケーションログをMySQLに保存している （調査目的） MySQLだとスケールしない S3やHadoop(Hive)上に保存しよう （スケールしそう） 3 １００〜２００Write/sec くらいでキツイ
- 4. 作るきっかけ S3やHadoop(Hive)上に保存しよう （スケールしそう） Hadoop(Hive)はQueryが遅くて 調査には不向きかも？ 代わりにRedshi/が使えそう 4
- 5. 目次 • 今回の目標 • Redshi/について • 設計上の工夫・注意点 5
- 6. 今回の目標 6
- 7. 前回はこんなシステムでした 7 ログ Fluentd tail Fluentd s3 S3 Master Core Core EMRクラスタ MySQL Hive 東京リージョン
- 8. 今回はこんなシステムにします 8 ログ Fluentd tail Fluentd s3 S3 Redshi/ MySQL バージニアリージョン 東京リージョン リージョンの境界
- 10. Redshi/とは • AWSのデータベースサービス – h;p://aws.amazon.com/jp/documentaPon/redshi// • 大規模データの蓄積、集計などに強い – 「Webから参照」ではなく「バッチ処理」向き – データウェアハウス(DWH)用途 • 「Hadoop+Hive」の10倍の「速度」＆「コスパ」 – h;p://www.slideshare.net/Hapyrus/amazon-‐redshi/-‐hadoop-‐hive10 10
- 11. Redshi/とは • 列指向データベース – → 集計処理やデータ圧縮にメリット – → 1行だけ取得する、などは得意ではない • 分散型・フルマネージドサービス – データ容量は 2TB 〜 1600TB まで対応可能 – 容量の変更もWebからぽちっと • 移行時はサービスがReadOnlyになる • Postgres8.0系のインターフェースでSQLが使える – psql や Postgresql Libraryが使える – ODBCなども使える 11
- 12. Redshi/とは • 対応リージョン(2013/05時点) – バージニア、オレゴン、アイルランド • DynamoDBやS3からデータのImportが可能 – 但し、S3は同一リージョンからのみ • DynamoDB にはリージョン制約はない？ • S3へのExportも可能 – 但し、同一リージョンへのみ 12
- 13. 料金 13
- 15. 1TBデータを保持した場合の料金比較 費目 前払い 単価 3年間費用 Redshi/ (XL 1ノード) ノード $3,000 $0.114/時間 $5,995 $5,995 RDS (Xlarge + MulP AZ) インスタンス $4,800 $0.326/時間 $13,367 $20,567 ストレージ $0.2/月・GB $7,200 Hadoop + Hive (Xlarge 1台) インスタンス $1,198 $0.053 $2,590 $6,010 S3ストレージ $0.095/月・GB $3,420 15 3年リザーブドインスタンス ※バージニアリージョン
- 16. 1TBデータを保持した場合の料金比較 費目 前払い 単価 3年間費用 Redshi/ (XL 1ノード) ノード $3,000 $0.114/時間 $5,995 $5,995 RDS (Xlarge + MulP AZ) インスタンス $4,800 $0.326/時間 $13,367 $20,567 ストレージ $0.2/月・GB $7,200 Hadoop + Hive (Xlarge 1台) インスタンス $1,198 $0.053 $2,590 $6,010 S3ストレージ $0.095/月・GB $3,420 16 Xlarge1台ではきっと遅すぎる MulP AZで なければ半額 上限3TB 2TBまで同じ S3上ではログはGZIPできるので10分の1くらいかも
- 17. テーブル設計 17
- 18. 列の型 • 整数： int2, int4, int8 • 実数： float4, float8, numeric • 論理値： BOOL • 文字列： CHAR, VARCHAR • 日付時刻: DATE, TIMESTAMP 18 下記の11個
- 19. 文字列型の制約 • CHAR – マルチバイト文字は扱えない • VARCHAR – UTF-‐8をサポート – 65535バイトまで – 4バイトUTF-‐8は扱えない – 次のCode Pointは扱えない • 0xD800 -‐ 0xDFFF • 0xFDD0 -‐ 0xFDEF, 0xFFFE, and 0xFFFF 19 結構ひっかかりそう
- 20. (物理)テーブル設計のポイント • Sort Key を選ぶ • DistribuPon Key を選ぶ • 圧縮方法を選ぶ • 制約(constraints)を定 義する 20
- 21. Sort Key • DISK上にデータを保存するときの順序 – データブロック（1MB）単位で最大値と最小値を管理して いる – 不要なブロックを読まなくて済む→高速化 • こういう列を指定しましょう – 直近のデータをよく使う → Timestamp – よく範囲検索に使う列 – JOINの条件によく使う列 • 複合列で指定することもできる 21
- 22. DistribuPon Key • データをどのように分散して保持するか • XLノードで2 Slice, 8XLノードで16 Sliceある • 各Sliceに分散する方法を決める • ２つの方法がある – EVEN： Round-‐Robin 的に分散 – KEY: KEYに指定された列の値で分散 22
- 23. DistribuPon Key • KEY列に指定した方が良い列 – JOINでよく使う列 • 気をつけること – KEY列の値に大きい偏りがあるとパフォーマンス に影響する – でも、なるべく指定したほうがいいみたい？ 23
- 25. Encoding： Raw • 無圧縮 • デフォルトではRawになる 25
- 26. Encoding： ByteDict • 1ブロック(1MB) 毎に「値の辞書」を作る • 辞書登録は256個まで • 辞書登録された値は1バイトで表される • 1ブロックで256個を超えればRawとして保存 26
- 27. Encoding： Delta • 前の値との差分(1,2Byte)で保存する • 時刻のような連続した値に効果が大きい • 差分が想定範囲より大きい場合、最悪Rawよ り効率が悪くなることがある 27
- 28. Encoding： Mostly • ほとんどの値の絶対値が1byte~4byte程度に 収まる場合に効果的 • 例えば、INT4の列でも通常値が1000以下で あれば、Mostly2(16bit) Encodingにすれば、 2byteで保存できる – 16bitを超える場合は,Rawで保存される 28
- 30. Encoding： Text • BYTEDICT EncodingのVARCHAR対応版 • Text255 – 1ブロック当たり245個の単語辞書を作る • Text32k – 1ブロック当たり辞書サイズが32Kになるまで作る 30
- 31. 圧縮選定の支援機能 • 自動圧縮機能 – CREATE TABLE直後の空のテーブルに対して – COPYでデータを投入すると適切に選んでくれる • デフォルトでは、1slice当たり10万件以上のデータが必要 • ANALYZE COMPRESSION – 現在のデータをみて、どの圧縮が良さそうか教えて くれる 31
- 32. 制約の定義 • PRIMARY KEY, INDEX, FOREIGN KEY は情報の み – 実際には何もチェックはされない – Query Planの参考に使われる – でも重要だから付けておいた方が良い • NOT NULL制約だけはちゃんと機能する 32
- 33. データのIMPORT 33
- 34. データのImport： COPY コマンド • DynamoDB や 同一Region S3から取り込み • CSVやTSVのような形式のみ(JSONは非対応) • GZIPファイルの読み込みも可能 • S3のprefixを指定して複数Importも可能 – 並列実行されるので高速になる 34
- 35. COPYのデータ形式 • ヘッダなしCSVやTSVだが Escapeのルールに 注意 • エスケープにはバックスラッシュを使う 35 <TAB> → <TAB> <改行> → <改行> → <TAB> → t <改行> → n 正 誤
- 37. 統計情報の更新： ANALYZE • 統計情報を更新して、Queryを最適化する • 列単位でANALYZEすることもできる – 不要な列はANALYZEする必要なし • 必要な列候補 – ソートやGroup Byで使う列 – JOINに使う列 – 検索条件に使う列 37
- 38. データ整理整頓： VACUUM • ２つの役割 • DELETEフラグの回収 – UPDATEやDELETEで削除フラグが付いた領域の回収 • UPDATEは内部的には「DELETE+INSERT」である • 未ソート領域のソート – INSERTやUPDATEされたデータは「未ソート領域」に保存される • 両方もしくはどちらかのみの実行が可能 – ちょくちょくやるほうが実行時間は短くなる 38
- 39. 速度的なメモ 39
- 40. 速度的なメモ • 最小構成(XLノード１つ)に対して • 1000万行(Gzip後79MB)のデータ • COPY に 約8分 • SELECT key, count(*) などに ２〜３秒 40 h;p://qiita.com/items/b09711c41bcfc8c2f931
- 41. 設計上の工夫・注意点 41
- 42. Hiveより簡単 • Psqlのインタフェースなので扱いは楽 – 同期的に結果が返ってくるし • テーブル設計のハマリポイントなどはまだ不明 • 前述のCOPYのエスケープにはやや注意 42
- 43. 冪等性について • S3→Redshi/への再処理の可能性がある • Importするデータに ”filename_”, “lineno_”という 2列を常に追加して、再度Importするときに「検 出＆削除」を事前にするようにした • 圧縮すればそれほど容量は増加しない（はず） – “filename_” は “TEXT255” – “lineno_” は “Delta” 43
- 44. Redshi/へImport済みかのチェック 最初は、対象テーブルの filename_ を検索して いたが、１億件くらいになると１分くらいかかるよ うになった（さすがに無茶だった） Import済みのファイル名をRedshi/内で管理す るテーブルを作成して、そこをチェックするよう にした 44
- 45. 処理の流れ 45
- 46. ログの取得 46 ログ Fluentd tail Fluentd s3 S3 MySQL Local DISK上のログファイルを Fluentd tail プラグインで読み取る バージニアリージョン 東京リージョン リージョンの境界 以前と同じ Redshi/
- 47. ログの転送 47 ログ Fluentd tail Fluentd s3 S3 MySQL Fluentd Forwardで中継用Fluentdに転送 バージニアリージョン 東京リージョン Redshi/ 以前と同じ
- 48. ログの集約とUpload 48 ログ Fluentd tail Fluentd s3 S3 MySQL 一定サイズか一定時間毎に ログファイルを圧縮してS3にUpload バージニアリージョン 東京リージョン Redshi/ 以前と同じ リージョン間の転送になるが、 GZIPファイルなので そこまで大きなファイルにならない
- 49. ログデータをTSVに変換 49 ログ Fluentd tail Fluentd s3 S3 MySQL JSON.gz形式からRedshi/にCOPYで読め るTSV.gz形式に変換 バージニアリージョン 東京リージョン Redshi/ 今回追加
- 50. TSV を COPYでImport 50 ログ Fluentd tail Fluentd s3 S3 MySQL 一定時間毎にS3上のTSVファイルを COPYコマンドでImport バージニアリージョン 東京リージョン Redshi/ 今回追加
- 51. チェック用に件数をカウント 51 ログ Fluentd tail Fluentd s3 S3 MySQL 件数を数えて MySQLに保存 東京リージョン Redshi/ 今回追加
- 52. この構成のメリット • 前回のHiveにImportする仕組みが使える – 共存も可能 • リージョンをまたぐが、実際の転送量はそれほど大きくない ので金額面の心配は要らない – バージニアリージョンの安さの方が勝る – ※Hiveの時もこうすべきだった • S3上にJSON.gzはあるので、いつでもHiveで処理が可能 52
- 53. 53 まとめ
- 54. まとめ • SQLでできる「調査」「集計」などはRedshi/が良さ そう – 特に「調査」では応答時間の短さは重要 • Hiveは「SQLではできない処理」専用にすると良 いだろう – 必要な時に起動するならコストは低いし • 「日時の集計」程度ならHiveでも良いかも – EMRの常時起動がコストがかかるので 54
- 55. （参考）以前の資料 55
- 57. 作るきっかけ アプリケーションログをMySQLに保存している （調査目的） MySQLだとスケールしない S3やHadoop(Hive)上に保存しよう （スケールしそう） 57 １００〜２００Write/sec くらいでキツイ
- 58. 本文書の目的 • 同じようなシステムを作る・使う人向け • 細かい話も多少出てきますが、作るときに読 み返してもらえれば 58
- 59. 目次 • 基本構成と簡単な説明 • EMRやHive周りの仕様の整理 • 設計上の工夫・注意点 • 参考性能 59
- 60. 基本構成と簡単な説明 60
- 61. 基本構成 61 ログ Fluentd tail Fluentd s3 S3 Master Core Core Task Task EMRクラスタ MySQL Hive
- 62. Fluentdについて • h;p://fluentd.org/ • ログの転送、ストリーム処理するのが得意 • Pluginを作ることで様々なカスタマイズ可能 – ファイルのTailやS3へのUpは既存のPluginで可能 • 信頼性高：ログの取りこぼしなどはほぼ無い 62
- 63. 何故Fluentdを使うのか？ • td-‐agentを使うならInstallは簡単 • 実績がある（Cookpadや大手SNS） • 転送や処理性能が高く、障害にも強い – Pluginも障害時には、再処理させてくれる • ログ処理のデファクトになりつつある – サーバエンジニアなら知っておいたほうが良いよ 63
- 64. Amazon S3 • 説明不要ですよね、いつもお世話になります • 永続的なログの保管場所として使う • Hadoopのデータ置き場としても使える 64
- 65. 何故S3を使うのか？ • ログの保存といえばS3でしょう • EC2やEMRからのアクセスが安価・容易 65
- 66. Amazon EMR • EMR(ElasPc Map Reduce)はHadoopサービス • 色々面倒なHadoopのセットアップを省略 • クラウドらしく、必要な時に起動して、終わったら 破棄することも可能 • 今回は、常時起動して利用します 66
- 67. 何故EMR(Hadoop)を使うのか？ • 今回は使ってみたかったというのが大きい • データ量的な問題は無いが、調査用にインタラク ティブに使うには少しレスポンスに問題があるか もしれない • バッチでログをMySQL等に入れる案もある 67
- 68. Apache Hive • Hadoop上のミドルウェア – 類似にPigというのがある • SQL Likeにデータを操作できるので、調査には向 いている – 新しい操作言語を覚えなくて良い – コマンド打って戻ってくるまで１分くらいはかかるけど • HadoopなのでHDFSやS3からデータを入出力 68
- 69. 何故Hiveを使うのか？ • Pigは独自言語なのでHiveの方が覚えやすい • こういう用途では比較的事例が多い • 本格的なデータ分析や処理でも使える 69
- 70. 処理の流れ 70
- 71. ログの取得 71 ログ Fluentd tail Fluentd s3 S3 Master Core Core Task Task EMRクラスタ MySQL Hive Local DISK上のログファイルを Fluentd tail プラグインで読み取る
- 72. ログの転送 72 ログ Fluentd tail Fluentd s3 S3 Master Core Core Task Task EMRクラスタ MySQL Hive Fluentd Forwardで中継用Fluentdに転送
- 73. ログの集約とUpload 73 ログ Fluentd tail Fluentd s3 S3 Master Core Core Task Task EMRクラスタ MySQL Hive 一定サイズか一定時間毎に ログファイルを圧縮してS3にUpload
- 74. ログデータのHive Import 74 ログ Fluentd tail Fluentd s3 S3 Master Core Core Task Task EMRクラスタ MySQL Hive 一定時間毎にS3上のファイルを HiveのテーブルにImport
- 75. チェック用に件数をカウント 75 ログ Fluentd tail Fluentd s3 S3 Master Core Core Task Task EMRクラスタ MySQL Hive 件数を数えて MySQLに保存
- 77. EMRのインスタンスグループ • EMRクラスタには３つのインスタンスグループ • Master、 Core、 Task の ３つ • １台のサーバに全てを やらせることも可能 77 Master Core Core Task Task EMRクラスタ Hive
- 78. Master インスタンスグループ • EMRクラスタに１つだけの存在 • HadoopのName Nodeに該当するようだ • あまり仕事をしないので、低スペックでOK • ファイル数が増えてくると、 メモリ不足になるので 高いスペックが必要になる 78 Master Core Core Task Task EMRクラスタ Hive
- 79. Coreインスタンスグループ • データも保存するし、Jobもこなす中核的存在 • 内蔵HDDがHDFS用のDISKとして使われる • S3を使わず、HDD上のHDFSにデータを保存 する場合は、台数が必要になる • 台数増は可能だが、 台数減はできない 79 Master Core Core Task Task EMRクラスタ Hive
- 80. Taskインスタンスグループ • Jobだけをこなす遊軍的存在 • 台数の増減が可能 80 Master Core Core Task Task EMRクラスタ Hive
- 81. HiveのParPPon • データの検索性を高めるために、ParPPon Keyを定め ることができる • ParPPon Key含みのQueryは、そのParPPonだけをScan するので高速になる – 含まなければ、全データScanになる • １Tableに複数Keyを付けることも可能 – 順序が重要になる – DBのIndexのようにParPPonを複数は作れない 81
- 82. Hiveの動的ParPPon • ParPPonは静的と動的がある • 静的は、更新Queryに直接値を記述 LOAD DATA INPATH ‘/path/to/20130101.tsv’ INTO TABLE sales parPPon (dt=’20130101ʹ′); • 動的は、データの値でParPPonできる INSERT TABLE sales PARTITION(date_id) SELECT * FROM hive_sales; 82
- 83. Hiveのデータ管理法 • BASE/<Table>/ の下にたくさんファイルがある • ParPPonを使っていると、 BASE/<Table>/<ParPPon Key>=<Value1>/files BASE/<Table>/<ParPPon Key>=<Value2>/files … • テーブル定義情報などは別のHDFS上にあるようだ 83
- 84. Hiveのデータ追加処理 Load命令 Insert命令 Overwrite指定なし ファイルがmoveされる (元ファイルは消える) moveなので、コピー先のHDFS上に 同一ファイル名があれば上書き データをコピーして追加 Overwrite指定あり 既存パーティションのデータは削除 ファイルがmoveされる (元ファイルは消える) 既存パーティションのデータは 削除 データをコピーして追加 動的ParPPon できない(値を読まないので) できる 84
- 85. 設計上の工夫・注意点 85
- 86. 今回悩ましかった点 • Hadoop＋Hiveの実行が完了したのかわかり にくい • 特に異常終了した場合、どこまで処理が進行 していたのか把握できない – 実際にDISKにデータを書き終わっても通信エラー で異常終了に見えることも考えられる 86
- 87. エラー発生時の復帰処理 • バッチ処理は確実に完了する必要がある – ↔ Web処理は、エラーならエラーと返せば良い • 何かエラーが起これば常に最初からやり直せ ば良いようにしておけば楽である – 複雑なシステムだとエラー要因も多く、それぞれ に応じた復帰処理は実装が大変 87
- 88. 冪等性 • 冪等性（べきとうせい） – 何回処理を実行しても同じ結果になること 88 UPDATE score SET point=20 WHERE user_id=10; UPDATE score SET point=point+5 WHERE user_id=10; 冪等である 冪等ではない
- 89. バッチ処理単位で冪等にする • バッチ処理が冪等なら、何か失敗すれば再度実 行すれば良いだけ • ファイルのUploadやDBの更新は、実装時に意識 すれば大抵大丈夫 – エラーが検知できるので • Hiveの部分だけ工夫が必要になった 89
- 90. HiveにImportする時の冪等性の確保 ■ Step 1 Importするデータ群をImportIDに紐付ける → その情報はMySQLなどに保存する 90 File A File B File C Import ID 1 MySQL
- 91. HiveにImportする時の冪等性の確保 ■ Step 2 Import IDをParPPon Keyとして Insert Overwrite → ParPPonを一旦削除してからコピーするので冪等 91 File A File B File C Import ID 1 EMRクラスタ ImportID=1/FileA /FileB /FileC このImportIDが既に存在すれば、削除してからImportされる MySQL 正常終了→完了フラグ
- 92. 実際には他のParPPon Keyも付ける ParPPon Keyは、 (日付、企業ID、Import ID） の３つになった 92
- 93. 処理性能の参考値 93
- 94. 処理性能の参考値 94 ログ Fluentd tail Fluentd s3 S3 Master Core Core EMRクラスタ MySQL Hive Small × 5台 Large × 2台 xlarge × 1台 xlarge × 2台
- 95. 処理性能の参考値 95 ログ Fluentd tail Fluentd s3 S3 Master Core Core EMRクラスタ MySQL Hive Small × 5台 Large × 2台 xlarge × 1台 xlarge × 2台 2000 Line/sec/台 × ２系統 10000 Line/sec × ２系統 ５分毎にUpload ５分毎にImport （重複起動なし）
- 96. 処理性能の参考値 96 ログ Fluentd tail Fluentd s3 S3 Master Core Core EMRクラスタ MySQL Hive Small × 5台 Large × 2台 xlarge × 1台 xlarge × 2台 2000 Line/sec/台 × ２系統 10000 Line/sec × ２系統 ５分毎にUpload ５分毎にImport （重複起動なし） （A）から２０〜４０分後にHiveに格納された ここまで ほぼ遅延なし 15～20分程度 （A） 72時間で51.84億件 全てHiveに保存された
- 97. まとめ 97
- 98. まとめ • ログをS3やHiveにImportする仕組み • 今回の案件独自の要求もあるので、他の案件で 使うには調整した方が良いだろう • とはいえ「S3にログを保存」「その後Hiveに Import」は問題なくできるので、要望があれば対 応できます 98
- 100. HiveのJSON処理 • JsonSerde(JSON Serializer Desirializer) と呼ばれ るライブラリを使う • ただ、実装が微妙であるものが多い – 型チェックが厳密すぎたり – Int と LongのCastをしてくれなかったり • とりあえず、列は全て文字列で扱うハメに… 100
- 101. Log Rotateの設定 Log Rotateで create オプションを付けると、Rotateした後に新 規に元ファイルを作成する しかし、O_CREAT で作成するので → ログをRotate →＜別プロセスがログを書く＞（＊A） → O_CREAT →（＊A）が消失する ので、少し注意が必要である。 ※ Kill –HUP するようなケースは気にしなくて良い 101
- 102. Hadoopのファイルサイズ • Map Reduceでは、並行してデータを読み取りる ことで、IO性能を上げる • １つのファイルサイズは、64MBくらいが良いらし い – もちろん、環境などによって変わるので目安程度 • 小さすぎたり、大きすぎると性能出ないことも 102
- 103. Hadoopのファイル数 • Hadoopの弱点として、ファイル数が多すぎる とMaster Nodeが異様にメモリを消費すること がある • １億ファイルで４０〜６０GBくらい消費する？ – h;ps://groups.google.com/forum/#!msg/hadoop-‐jp/qWAaVc-‐TGdY/viRYuh4rGqIJ 103
- 104. JobStep数の上限は255 EMRの１つのJobFlowに追加できるJobStep数の 上限は255個までです。 回避策などはこちら。 今回は、Masterノードにsshする方法をとってい ます。 104
- 105. Hiveの動的ParPPonの上限 ParPPon数の上限がデフォで1000なので、 SET hive.exec.dynamic.parPPon=true; SET hive.exec.dynamic.parPPon.mode=nonstrict; SET hive.exec.max.dynamic.parPPons=1000000; SET hive.exec.max.dynamic.parPPons.pernode=1000000; SET hive.exec.max.created.files=15000000; みたいにして増やしておく必要がある （数は適当。デメリット等は不明。単に増えると遅いのだ と思う） 105
- 106. Hive実行時のOpPonメモ 106 SET hive.exec.dynamic.parPPon=true; SET hive.exec.dynamic.parPPon.mode=nonstrict; SET hive.exec.max.dynamic.parPPons=1000000; SET hive.exec.max.dynamic.parPPons.pernode=1000000; SET hive.exec.max.created.files=15000000; SET hive.exec.compress.output=true; SET io.seqfile.compression.type=BLOCK; SET hive.exec.compress.intermediate=true; SET hive.intermediate.compression.type=BLOCK; SET mapred.output.compress=true; SET mapred.output.compression.type=BLOCK; SET mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec; SET hive.merge.mapfiles=true; SET hive.merge.mapredfiles=true; SET hive.merge.size.per.task=256000000; SET hive.merge.size.smallfiles.avgsize=16000000;