SlideShare a Scribd company logo

AWS Black Belt Tech シリーズ 2015 - Amazon Redshift

•
•
Amazon Web Services Japan
Amazon Web Services Japan

AWS Blackbelt 2015年8月26日 Amazon Redshiftの資料です。今後の予定は以下をご覧ください。 http://aws.amazon.com/jp/about-aws/events/#webinar

1 of 80
Downloaded 189 times
1 @awscloud_jp #awsblackbelt Amazon Redshift AWS Black Belt Tech Webinar 2015 アマゾン ウェブ サービス ジャパン株式会社 下佐粉 昭 2015/08/26 （2015/11/25更新）
2 @awscloud_jp #awsblackbelt 自己紹介 名前：下佐粉 昭（しもさこ あきら） Twitter - @simosako 所属： – アマゾンデータサービスジャパン株式会社 – 技術本部 ソリューションアーキテクト 好きなAWSサービス:Redshift, RDS, S3 • 人間が運用等から開放されて楽になる系の サービスが好きです
3 @awscloud_jp #awsblackbelt アジェンダ • Amazon Redshiftとは？ • パフォーマンスを意識した表設計 • Amazon Redshiftの運用 • Workload Management (WLM) • ユーザ定義関数（UDF） • まとめ • 補足資料：COPYコマンド
4 @awscloud_jp #awsblackbelt Amazon Redshiftとは？
5 @awscloud_jp #awsblackbelt Amazon Redshiftの概要 • クラウド上のDWH – 数クリックで起動 – 使った分だけの支払い • 高いパフォーマンス – ハイ・スケーラビリティ • 高い汎用性 – PostgreSQL互換のSQL – 多くのBIツールがサポート
6 @awscloud_jp #awsblackbelt MPPとシェアードナッシングがスケールアウトの鍵 • MPP : Massive Parallel Processing – １つのタスクを複数のノードで分散して実行する仕組み – Redshiftではリーダーノードがタスクをコンピュートノードに分 散して実行する – ノードを追加する（スケールアウト）でパフォーマンス向上可能 • シェアードナッシング – ディスクをノードで共有しない構成 – ディスクを共有するとノード数が増えた時にボトルネックになる ため、それを回避 – ノードとディスクがセットで増えていく
7 @awscloud_jp #awsblackbelt Redshiftの構成① SELECT * FROM lineitem; リーダーノードがクライア ントからSQLを受け取る CPU CPU CPU CPU CPU CPU Leaderノード Computeノード １つの表を各ノード のストレージに分散 して保存（シェアー ドナッシング）
8 @awscloud_jp #awsblackbelt Redshiftの構成② SELECT * FROM lineitem; SQLをコンパイル、 コードを生成し、コン ピュートノードへ配信 CPU CPU CPU CPU CPU CPU Leaderノード Computeノード スライス＝ メモリとディスクを ノード内で分割した論 理的な処理単位 コンピュートノードの追 加でパフォーマンス向上 （スケールアウト）
9 @awscloud_jp #awsblackbelt ノードタイプ • SSDベースのDCとHDDベースのDSから選択 – データは圧縮されて格納されるため、ストレージ総量より多くのデータが格納可能 • 最大100ノード：1.6PByteまで拡張可能 – ノードタイプと数は後から変更可能 DC1 - Dense Compute vCPU メモリ(GB) ストレージ ノード数 価格(※) dc1.large 2 15 0.16TB SSD 1~32 $0.314 /1時間 dc1.8xlarge 32 244 2.56TB SSD 2~100 $6.095 /1時間 DS2 – Dense Storage ds2.xlarge 4 31 2TB HDD 1~32 $1.190 /1時間 ds2.8xlarge 36 244 16TB HDD 2~100 $9.520 /1時間 ※価格は東京リージョンにおいて2015年11月25日時点のものです New!!
10 @awscloud_jp #awsblackbelt 【補足】リーダーノードと利用費用 • リーダーノードもコンピュートノードも同じ ノードタイプで構成される • リーダーノード分は利用費用が不要 • １ノード構成にした場合、リーダーノードとコ ンピュートノードが１ノードに同居する
11 @awscloud_jp #awsblackbelt IOを削減する① - 列指向型（カラムナ） ・行指向型（他RDBMS） ・列指向型（Redshift） orderid name price 1 Book 100 2 Pen 50 … n Eraser 70 orderid name price 1 Book 100 2 Pen 50 … n Eraser 70 DWH用途に適した格納方法
12 @awscloud_jp #awsblackbelt analyze compression listing; Table | Column | Encoding ---------+----------------+---------- listing | listid | delta listing | sellerid | delta32k listing | eventid | delta32k listing | dateid | bytedict listing | numtickets | bytedict listing | priceperticket | delta32k listing | totalprice | mostly32 listing | listtime | raw IOを削減する② - 圧縮 • データは圧縮してストレージに格納 される • カラムナのため類似したデータが集 まり、高い圧縮率 • エンコード（圧縮アルゴリズム）は 列ごとに選択可能 • COPYコマンドやANALYZEコマンド で圧縮アルゴリズムの推奨を得るこ とが可能
13 @awscloud_jp #awsblackbelt IOを削減する③ - ゾーンマップ Redshiftは「ブロック」単位で ディスクにデータを格納 １ブロック＝１MB ブロック内の最小値と最大値をメ モリに保存 不要なブロックを読み飛ばすこと が可能 10 | 13 | 14 | 26 |… … | 100 | 245 | 324 375 | 393 | 417… … 512 | 549 | 623 637 | 712 | 809 … … | 834 | 921 | 959 10 324 375 623 637 959
14 @awscloud_jp #awsblackbelt フルマネージドサービス 設計・構築・運用の手間を削減 • 数クリックで起動 • １時間単位の費用 • ノード数やタイプは後から変更可能 • バックアップ(Snapshot)やモニタリング機能を内蔵 – GUI（マネジメントコンソール） – API経由で操作も可能 • パッチ適用も自動的 – メンテナンスウィンドウでパッチの時間帯を指定可能
15 @awscloud_jp #awsblackbelt Redshiftが向く用途 • 特化型のデータベースのため、適した用途に使うことで パフォーマンスを発揮します • Redshiftに向くワークロード – 巨大なデータ・セット（数百GB～ペタバイト） – １つ１つのSQLが複雑だが、同時実行SQLは少ない – データの更新は一括導入 • ユースケース – データウェアハウス（DWH） – ユーザがクエリーを作成する（自由クエリー）（BI等）
16 @awscloud_jp #awsblackbelt Redshiftの特徴を生かせないユースケース • SQLの並列実行数が多い（※同時接続数ではなく同時実行数） – RDS（MySQL ,PostgreSQL, Oracle, SQL Server)を検討 • 極めて短いレーテンシが必要なケース – ElastiCache (インメモリDB)やRDSを検討 • ランダム、かつパラレルな更新アクセス – RDSもしくはDynamoDB (NoSQL)を検討 • 巨大なデータを格納するが集計等はしない – DynamoDBや大きいインスタンスのRDSを検討
17 @awscloud_jp #awsblackbelt Amazon Redshiftの位置づけ データ・ストアの特性に応じた使い分け Amazon DynamoDB Amazon RDS Amazon ElastiCache Amazon Redshift SQL NoSQL• 低レンテンシ • インメモリ • 3拠点間での レプリケーション • SSDに永続化 • トランザク ション処理 • 汎用用途 • 集計・分析処理 • 大容量データ • DWH
18 @awscloud_jp #awsblackbelt パフォーマンスを意識した表設計
19 @awscloud_jp #awsblackbelt DDLによるパフォーマンスの最適化 • ディスクIOを削減する – サイズを減らす – 読む範囲を減らす • ノード間通信を削減する – 通信しないようなデータ配置
20 @awscloud_jp #awsblackbelt ディスクIOを削減する：型を適切に選択する • 型を適切に選択してサイズを節約する – 不必要に大きい型を選択しない – BIGINT(8バイト)よりも、INT(4バイト)やSMALLINT(2バイト) – FLOAT(8バイト)よりも、REAL(4バイト） – 日付は文字列(CHAR)で格納せずTIME型を使用
21 @awscloud_jp #awsblackbelt Redshiftで利用可能な型 • 下表の型をサポート • charはシングルバイトのみサポート • varcharはUTF-8形式でのマルチバイトをサポート 参照） http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-datatypes.html
22 @awscloud_jp #awsblackbelt ディスクIOを削減する：適切な圧縮方法の選択 • 圧縮を行うことで、一度のディスクアクセスで読み込める データ量が多くなり、速度の向上が見込める • 圧縮のエンコード（アルゴリズム）が複数用意されており、 CREATE TABLEで各列に選択することが可能 • 動的には変更できない (作りなおして INSERT … SELECT） CREATE TABLE table_name ( 列名 型 ENCODE エンコード, )
23 @awscloud_jp #awsblackbelt 圧縮エンコーディングの種類 • データの特性に応じたエンコーディングを選択するのが理想 • ANALYZE COMPRESSIONコマンドで推奨を確認可能 – 先にデータの投入が必要 • LZOは比較的多くのケースで有効
24 @awscloud_jp #awsblackbelt 圧縮エンコーディングの確認 • pg_table_def のencoding列で確認可能 24 mydb=# select "column",type,encoding from pg_table_def where tablename='customer_enc'; column | type | encoding --------------+-----------------------+---------- c_custkey | integer | delta c_name | character varying(25) | lzo c_address | character varying(25) | lzo c_city | character varying(10) | bytedict c_nation | character varying(15) | bytedict c_region | character varying(12) | bytedict c_phone | character varying(15) | lzo c_mktsegment | character varying(10) | bytedict
25 @awscloud_jp #awsblackbelt ディスクアクセスの範囲を最小にする • SORTKEY – SORTKEYに応じて、ディスク上にデータが順序を守って格納 – クエリー・オプティマイザはソート順序を考慮し、最適なプランを構築 – CREATE TABLE時に指定。複数列が指定可能 • CREATE TABLE t1(…) SORTKEY (c1,c2 …) • SORTKEYの使いどころ – 頻繁に特定のカラムに対して、範囲または等式検索を行う場合 • 例）時刻列 – 頻繁にジョインを行う場合、該当カラムをSORTKEYおよびDISTKEYとし て指定→ ハッシュ・ジョインの代わりにソート・マージ・ジョインが選択 される 25
26 @awscloud_jp #awsblackbelt SORTKEY の例 • orderdate 列をSORTKEY に指定した場合： 2013/07/17 2013/07/18 2013/07/18 2003/07/19 … I0001 I0002 I0003 I0004 ・・・ 2013/08/20 2013/08/21 2013/08/22 2013/08/22 … I0020 I0021 I0022 I0023 orderdate…orderid SELECT * FROM orders WHERE orderdate BETWEEN ‘2013-08-01’ AND ‘2013-08-31’; クエリで必要なデータが固まっているた めディスクアクセス回数が減少
27 @awscloud_jp #awsblackbelt Interleaved Sort Key • 新しいSort keyのメカニズム • 最大８つまでのSort Key列を指定でき、それぞれ同等に扱わ れる CREATE TABLE ~ … INTERLEAVED SORTKEY (deptid, locid); • 旧来のSortで複数のキーを指定する場合（Compound Sort Key)とは特性が異なり、各列を同等に扱う • Interleaved Sort Keyが有効なケース – どのキーがWHERE句で指定されるか絞り切れないケース – 複数キーのAND条件で検索されるケース New!!
28 @awscloud_jp #awsblackbelt Interleaved Sort Keyのデータ配置イメージ DeptId LocId 1 A 1 B 1 C 1 D 2 A 2 B 2 C 2 D DeptId LocId 3 A 3 B 3 C 3 D 4 A 4 B 4 C 4 D Compound Sort Key Interleaved Sort Key DeptId LocId 1 A 1 B 2 A 2 B 1 C 1 C 2 D 2 D DeptId LocId 3 A 3 B 4 A 4 B 3 C 3 D 4 C 4 D DeptId = 1 -> 1 block LocId = C -> 4 block DeptId = 1 -> 2 block LocId = C -> 2 block DeptId=1 and LocId=C-> 1 block
29 @awscloud_jp #awsblackbelt データの平準化:各ノードのデータサイズが著しく異なると パフォーマンスに影響が出る CPU CPU CPU CPU CPU CPU ノード間のデータ容量 の偏りはクエリー実行 時間に影響を与える
30 @awscloud_jp #awsblackbelt データの転送を最小限にする 自ノードに必要なデータ がない場合、データ転送 が発生 - 単一ノード - ブロードキャスト リーダー・ノードに 各ノードの結果を集約
31 @awscloud_jp #awsblackbelt ディストリビューションの選択 ALL Node 1 Slice 1 Slice 2 Node 2 Slice 3 Slice 4 全ノードにデータをコ ピー KEY(DISTKEY) Node 1 Slice 1 Slice 2 Node 2 Slice 3 Slice 4 同じキーを同じ場所に Node 1 Slice 1 Slice 2 Node 2 Slice 3 Slice 4 EVEN ラウンドロビンで均一分散 （※デフォルト） CREATE TABLE t(…) DISTSTYLE { EVEN | KEY | ALL }
32 @awscloud_jp #awsblackbelt EVEN vs. DISTKEY（１） • EVEN • DISTKEY=p_partkey select trim(name) tablename, slice, sum(rows) from stv_tbl_perm where name='part' group by name, slice order by slice; tablename | slice | sum -----------+-------+--------- part | 0 | 1600000 part | 1 | 1600000 … part | 126 | 1600000 part | 127 | 1600000 tablename | slice | sum -----------+-------+--------- part | 0 | 1596925 part | 1 | 1597634 … part | 126 | 1610452 part | 127 | 1596154 各スライスに均等に分散 キーのカーディナリティに依存
33 @awscloud_jp #awsblackbelt EVEN vs. DISTKEY（２） • DISTKEY = p_brand tablename | slice | sum -----------+-------+--------- part | 0 | 0 part | 1 | 0 part | 2 | 0 part | 3 | 0 part | 4 | 8193350 … part | 118 | 8193342 part | 119 | 0 part | 120 | 16384823 part | 121 | 8191943 カーディナリティの低い カラムでは、データの極端な 偏りが生じる場合がある = クエリー処理効率の低下
34 @awscloud_jp #awsblackbelt ALL • 全レコードが各ノードの特定スライスに集約 tablename | slice | sum -----------+-------+--------- part | 0 |204800000 part | 1 | 0 part | 2 | 0 part | 3 | 0 part | 4 | 0 … part | 96 |204800000 part | 97 | 0 part | 98 | 0 … … 各ノードの先頭スライスに 全レコードが格納される。
35 @awscloud_jp #awsblackbelt コロケーション（１） • 関連するレコードのコロケーション – ジョイン対象となるレコードを同一ノードに集める • コロケーションの方法 1. ジョインに使用するカラムをDISTKEYとして作成 または 2. 分散方式 ALLでテーブルを作成（マスター・テーブルなど） select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part Where (p_partkey = l_partkey … 1. それぞれをDISTKEYとして作成 または 2. テーブルをALLで作成
36 @awscloud_jp #awsblackbelt コロケーション（２）：DISTKEY 6200995 | almond pale linen | Manufacturer#3| Brand#32 part lineitem 5024338535 | 6200995 | 0.01 |0.08 | A | F |1992-01-02 | 1992-02-14 2201039 | almond pale linen | Manufacturer#1| Brand#11 part lineitem 121932093 | 2201039 | 0.05 |0.43 | D | E |1994-07-11 | 1994-08-23
37 @awscloud_jp #awsblackbelt コロケーション（３）：ALL part lineitem part lineitem l_partkey l_partkey p_partkey p_partkey 更新：全ノードにレプリケーション クエリー：ジョインはローカルで完結
38 @awscloud_jp #awsblackbelt テーブル設計のポイント • ディスクIOを最小にする – 適切な型の選択 – 適切な圧縮アルゴリズム – ソートキーの設定 • ネットワーク転送を最小にする – 小規模なテーブル（マスター・テーブル）はALLで作成する – 多くのテーブルはEVENで作成するだけで十分なパフォーマンスが 出ることが多い – ジョインのパフォーマンスを最適化するにはジョイン対象のキーを DISTKEYで作成（コロケーション） – 大福帳のようなジョイン済（非正規化）表はEVENで分散
39 @awscloud_jp #awsblackbelt Amazon Redshiftの運用
40 @awscloud_jp #awsblackbelt Amazon Redshiftへのデータ投入：オーバービュー AWS Cloud オンプレミス・ データセンター Amazon DynamoDB Amazon S3 Data Volume Amazon Elastic MapReduce Amazon RDS Amazon Redshift Amazon Glacier logs / files ソースDB VPN Connection AWS Direct Connect S3 Multipart Upload EC2/オンプレ (SSH経由)
41 @awscloud_jp #awsblackbelt S3を起点としたRedshift運用の基本的な流れ 1. ロードするデータ（ファイル）をS3に 置く 2. COPYコマンドでデータを高速ロード 3. Analyze＆Vacuumを実行 4. バックアップ（SNAPSHOT）を実行 5. SQLを投入して利用開始（1.へ戻る） S3 Redshift COPY SQL 一般ユーザ 管理者 運用コマンド
42 @awscloud_jp #awsblackbelt S3からデータをCOPYする • ファイルをS3のバケットに置く – カンマや｜等で区切られたテキストファイル形式(delimiterオプションで指定） – 文字コードはUTF-8(デフォルト）とUTF-16をサポート – ファイルサイズが大きい場合は圧縮し（後述）、マルチパートアップロードする • Redshiftに接続してcopyコマンドを実行 – S3にアクセスするためのアクセスキーが必要 – 別リージョン内のS3バケットからのCOPYも可能 (REGIONオプションを指定） • 自動圧縮される – 列にエンコーディング定義がなく、かつ1行も導入されていない場合に実施される – COMPUPDATE OFFオプションを指定すると自動圧縮無しでCOPY copy customer from 's3://mybucket/customer/customer.tbl’ credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>’ delimiter '|'
43 @awscloud_jp #awsblackbelt COPYの速度を上げるには？ • 元ファイルを圧縮する(gzipもしくはlzo) – COPYでgzip もしくはlzoオプションを指定 • ファイルを分割する（スライス数の倍数が最適） – 並列にロードされるため高速にロード可能 • ファイル名は”customer.tbl.1.gz”,”customer.tbl.2.gz”の ように、指定した名前で前方一致出来るように作成 copy customer from 's3://mybucket/customer/customer.tbl’ credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>’ gzip delimiter '|'
44 @awscloud_jp #awsblackbelt 制約について • Redshiftには制約が存在しない – ユニーク制約、プライマリーキー、外部キー、検査制約が無い – ユーザ側の工夫でユニーク性を担保する • 例）一旦データをテンポラリ表にインサートもしくはCOPYし、 SELECT DISTINCTしたデータをインサートする – 制約やプライマリーキーの作成は可能。作成する事でオプティ マイザーにデータの特性情報を伝えることが可能
45 @awscloud_jp #awsblackbelt テーブルのANALYZE • 統計情報はクエリプラン決定の元データとして利用される • ANALYZEコマンドで統計情報を最新に保つことで最適なパフォー マンスを維持 ANALYZEコマンド データベース 全体 単一のテーブ ル テーブルの特 定の列 ANALYZEコマ ンドは行のサン プルを取得し、 計算を行った後 に統計情報を保 存 よく使われる列はANALYZEを行う • ソートやグループ化 • 結合の対象 • WHERE句の条件 • データ投入や更新の後、 定期的にデータベース全 体にANALYZEを実行 • 新しいテーブルを作った らANALYZEを実行 統計情報
46 @awscloud_jp #awsblackbelt テーブルのVACUUM • Redshiftのデータ更新は“追記型” • 削除しても削除がマークされるだけでディスク上にはデータが残っている • VACUUMコマンドで不要領域を削除（コンパクション）し、同時にソート 順にデータを並べ替える 1,2,4 RFK,JFK,GWB 900 Columbus,800 Washington,600 Kansas VACUUM Customer; 1,2,3,4 RFK,JFK,LBJ,GWB 900 Columbus,800 Washington, 700 Foxborough,600 Kansasx xxx xxxxxxxxxxxxxxx DELETE/UPDATEによって空い た未使用領域はVACUUMコマ ンドを実行することでコンパク ションされる
47 @awscloud_jp #awsblackbelt VACUUMコマンド • 通常はFULLを実行（コンパクション＆ソート） – コンパクションだけ実行するにはDELETE ONLY – ソートだけ実行するにはSORT ONLY • Interleaved Sortした表にはREINDEXを指定 – コンパクション＆Interleaved Sort順に並べ替えを実行 VACUUM [ FULL | SORT ONLY | DELETE ONLY | REINDEX ] [ table_name ]
48 @awscloud_jp #awsblackbelt バックアップ機能 – スナップショット • ディスクイメージをS3へバックアップ – 自動スナップショット – 手動スナップショット：ユーザが任意のタイミングで実行 マネジメントコンソールから“Take Snapshot”を選択し、 任意のIDを付けるだけでバックアップ開始
49 @awscloud_jp #awsblackbelt 他リージョンへのスナップショット • 既存クラスタのスナップショットを 別リージョンに作成可能 • リテンション・ピリオド（保存期 間）の指定も可能（最大35日） • KMS暗号化済のスナップショット 転送にも対応 • リージョン間のデータ転送費用が発 生 New!!
50 @awscloud_jp #awsblackbelt Redshiftのモニタリング • コンソールビルトインのGUI – リソース使用率、EXPLAIN、実行クエリー履歴 等 • API経由でデータ取得可能（CloudWatch）
51 @awscloud_jp #awsblackbelt Redshiftへの接続(1) • JDBCとODBCの専用ドライバーが用意さ れている – 管理コンソールからラウンロード可能 • PostgreSQLとプロトコル互換性が有り、 PostgreSQLドライバーでも接続可能 – psqlコマンドでも接続可能 • 極力Redshift専用ドライバーの利用を 推奨
52 @awscloud_jp #awsblackbelt Redshiftへの接続(2) RedshiftにパブリックIPを付与して、AWS外から直接接続が可能 パブリックIPの付与・取り外しは動的に変更可能です • 考慮点１）暗号化 – ODBCもJDBCもSSLで暗号化可能 – ただしJDBCの場合KeytoolでSSL Certificateの導入が必要 • https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/connecting-ssl- support.html#connecting-ssl-support-java • 考慮点２）TCP/IP通信の切断防止 – 長い時間が掛かるSQLやCOPYを実行すると、応答を待ちきれずにTCP/IP接続が切断され るケースがありえます – JDBCやODBCの接続パラメータ、もしくはアプリからドライバに対して設定 – Redshift専用のJDBC/ODBCドライバではKeep AliveがデフォルトでON – もしくはクライアント側のTCP/IP設定を変える（OS内の全アプリが影響を受けます） • http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html New!! : 2015/11/20
53 @awscloud_jp #awsblackbelt Workload Management (WLM)
54 @awscloud_jp #awsblackbelt Workload Management (WLM) • 実行に長い時間を用するクエリー（ロングクエ リー）は、クラスタ全体のボトルネックとなり、 ショートクエリーを待たせる可能性がある • WLMで用途ごとに、クエリー並列度の上限を設 けた複数のキューを定義することでクエリー処 理の制御が可能
55 @awscloud_jp #awsblackbelt Workload Management • 実行に長い時間を用するクエリー（ロングクエリー）は、クラスタ全体の ボトルネックとなり、ショートクエリーを待たせる可能性がある • WLMで用途ごとに、クエリー並列度の上限を設けた複数のキューを定義す ることでクエリー処理の制御が可能 • デフォルトでは、Redshiftクラスタは単一のキューで構成されている。 RunningDefault queue
56 @awscloud_jp #awsblackbelt WLMの実装（１） User Group A Short-running queueLong-running queue Long Query Group
57 @awscloud_jp #awsblackbelt WLMの実装（２） １ ５
58 @awscloud_jp #awsblackbelt WLMの効果 • キュー単位でクエリー並列度を保障 – メモリのアロケーションも指定可能 • 特定ユーザ（群）によるクラスタ占有を回避 – 最大クエリー実行時間による制御も可能 • 並列度の増加は、必ずしも性能の向上には つながらない -> リソース競合の可能性
59 @awscloud_jp #awsblackbelt WLMパラメータとパラメータの動的変更 • 新しくWLMのパラメータにdynamicとstaticの区別が用意され、 dynamicはRedshiftを再起動せずにパラメータ変更が可能に • dynamic parameter – Concurrency(並列実行数), – Percent of memory to use (メモリ使用量) • static parameter – User groups – User group wildcard – Query groups – Query group wildcard – Timeout New!!
60 @awscloud_jp #awsblackbelt UDF (ユーザ定義関数) ※2015/09/13：資料追加
61 @awscloud_jp #awsblackbelt RedshiftのUDFサポート • クラスターバージョン v.1.0.991以降で利用可能 • UDF＝ユーザ定義関数 – ユーザが独自の関数を定義できる機能 • Python言語で記述 • スカラー関数の作成をサポート – スカラー：１つの入力値ごとに、１つの値を返す関数 NEW!
62 @awscloud_jp #awsblackbelt UDF定義の例 • 例）引数aとbを比較して大きい方を返すUDF CREATE FUNCTION f_greater (a float, b float) RETURNS float STABLE AS $$ if a > b: return a return b $$ LANGUAGE plpythonu; SELECT f_greater (c1, c2) FROM t1
63 @awscloud_jp #awsblackbelt UDFの登録~実行 1. UDFを登録 2. リーダーノードでバイトコードにコンパイルし、各コンピュートノードに転送 3. SQLからUDFが呼び出されると、各コンピュートノード内でPythonインタプリタが起 動して実行 UDFを実行するPythonインタープリタはサンドボックス環境に置かれ、リソースが制限されている リーダーノード コンピュートノード Python インタープリタ Python インタープリタ Python インタープリタ バイト コード バイト コード バイト コード CREATE FUNCTION
64 @awscloud_jp #awsblackbelt UDFの定義 • 名前は既存関数と被らないように注意 – 引数が異なる関数は別のものとして定義可能（オーバーロード可能） – 例）f_を先頭に付けるといったルールでRedshift既存関数との名前被りを避け る(f_はRedshiftビルトイン関数では使わない事が保証されている） – もしくはスキーマで分ける。ただしデフォルトではビルトイン関数が優先され るので注意 → SET search_pathで調整は可能 • Redshiftの型はPythonの型に変換されるため、その型と、 Pythonプログラム側の型が合っている必要がある CREATE [OR REPLACE] FUNCTION f_function_name ( [引数の名前 引数の型, ... ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ (ここにコード） $$ LANGUAGE plpythonu;
65 @awscloud_jp #awsblackbelt UDFの定義② - 型変換 • Redshiftのdecimal がPythonのfloatに マッピングされる 点に注意 Redshiftの型 Pythonの型 smallint integer bigint long long short long decimal double real float boolean bool char varchar string timestamp datetime
66 @awscloud_jp #awsblackbelt UDFの定義③ - 最適化オプション • VOLATILE （デフォルト） – 同じ引数であっても異なる値が変える可能性を指定 – 実行するたびに毎回計算しなおします • STABLE – １クエリー内での処理中で、かつ同一の引数の場合に同じ結果が返る関数に指定 – オプティマイザーは一度実行した結果をクエリー内で再利用します • IMMUTABLE – 同一の引数であれば常に同じ結果が返る関数に指定 – オプティマイザーは関数を即値に置き換えます CREATE [OR REPLACE] FUNCTION f_function_name ( [引数の名前 引数の型, ... ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ (ここにコード） $$ LANGUAGE plpythonu;
67 @awscloud_jp #awsblackbelt UDFで利用可能なPythonライブラリ • import文でライブラリを読み込み可能 • Python 2.7.8標準ライブラリが導入済 – https://docs.python.org/2/library/index.html – ただし以下は、含まない。 • ScrolledText、Tix、Tkinter、tk、 turtle、smtpd • 加えて以下のライブラリを含んでいる – numpy 1.8.2 – pandas 0.14.1 – python-dateutil 2.2 – pytz 2014.7 – scipy 0.12.1 – six 1.3.0 – wsgiref 0.1.2
68 @awscloud_jp #awsblackbelt カスタムライブラリ機能 • 独自のPythonのライブラリを登録しておいて、UDFで使う事が 可能 CREATE LIBRARY library_name LANGUAGE plpythonu FROM { 'https://file_url' | 's3://bucketname/file_name' [ WITH ] CREDENTIALS [AS] 'aws_access_credentials' [ REGION [AS] 'aws_region' ] [ ENCRYPTED ] } • 登録できるのはSuper Userのみ • S3もしくはHTTPSアクセス可能なところにライブラリファイルを置き、 名前を付けて登録 – *.tar.gzか*.zip形式で保存 – Python 2.7.6以降で動くもの – PG_LIBRARY表に登録ライブラリ一覧が記録されます • CREDENTIALはS3から読み取る場合に必要（AWSのアクセスキー）
69 @awscloud_jp #awsblackbelt 権限 • UDFの作成 – Admin以外がUDFを作成にするは、権限付与が必要 – 例）GRANT USAGE ON LANGUAGE plpythonu TO ユーザ名; • UDFの削除・リプレース – Admin、もしくはUDFの作者のみ可能 • UDFの実行 – UDFへの実行（EXECUTE)パーミッションが必要 – ただし新規に作成されるUDFはPUBLICユーザグループのEXECUTE権限で実行が可能 – 上記をやめるには、PUBLICからEXECUTE権限をREVOKEする （参照）http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/udf-security-and- privileges.html
70 @awscloud_jp #awsblackbelt UDF：注意点 １）UDFからはネットワークアクセスやファイルIOは 出来ないように制限されている ２）ユーザライブラリの登録は合計100MBまで ３）UDFの並列実行はWLM設定の1/4に制限される – 例）WLMの並列度＝１５のキューでは、UDFの並列度は3
71 @awscloud_jp #awsblackbelt まとめ • DWH的用途に特化したRDB – ペタバイト級まで拡張可能 • クラウドの良さを活かせるDWH • マネージド・サービス – 機器セットアップやインストールの手間なし – バックアップ（スナップショット）が自動 – その他運用に必要な各種機能（モニタリング、EXPLAIN等）をビルトインで提供 • チューニングポイント – ディスクIOの削減（圧縮、ソートキー） – ネットワーク通信の削減（分散の調整） – Workload Management
72 @awscloud_jp #awsblackbelt Redshift 参考資料 • ドキュメント – https://aws.amazon.com/jp/documentation/redshift/ • フォーラム – https://forums.aws.amazon.com/forum.jspa?forumID=155& start=0 • 新機能アナウンスメント – https://forums.aws.amazon.com/thread.jspa?threadID=132 076&tstart=25 • Amazon Redshift Utils on github – https://github.com/awslabs/amazon-redshift-utils
73 @awscloud_jp #awsblackbelt Q&A 次回Webinarのお申し込み http://aws.amazon.com/jp/event_schedule/
74 @awscloud_jp #awsblackbelt Webinar資料の配置場所 • AWS クラウドサービス活用資料集 – http://aws.amazon.com/jp/aws-jp-introduction/
75 @awscloud_jp #awsblackbelt 公式Twitter/Facebook AWSの最新情報をお届けします @awscloud_jp 検索 最新技術情報、イベント情報、お役立ち情報、お得なキャンペーン情報などを 日々更新しています！ もしくは http://on.fb.me/1vR8yWm
76 @awscloud_jp #awsblackbelt AWS初心者向けWebinar • AWSをこれからご使用になる向けのソリュー ションカットのオンラインセミナー – http://aws.amazon.com/jp/about-aws/events/
77 @awscloud_jp #awsblackbelt ご参加ありがとうございました。
78 @awscloud_jp #awsblackbelt 補足資料：COPYコマンド
79 @awscloud_jp #awsblackbelt MANIFESTファイルによるファイル指定 • MANIFESTファイルにより、特定のファイル群 をS3バケットからCOPYできる { "entries": [ {"url":"s3://mybucket-alpha/2013-10-04-custdata", "mandatory":true}, {"url":"s3://mybucket-alpha/2013-10-05-custdata", "mandatory":true}, {"url":"s3://mybucket-beta/2013-10-04-custdata", "mandatory":true}, {"url":"s3://mybucket-beta/2013-10-05-custdata", "mandatory":true} ] }
80 @awscloud_jp #awsblackbelt COPYコマンド：その他の機能 • JSONファイルのCOPY – データ構造の自動認識あるいはJSONPathによる定義 • Avroフォーマットへの対応 • Amazon EMRからの読み込み copy sales from 'emr:// j-1H7OUO3B52HI5/myoutput/part*' credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access- key>'; クラスタID HDFSのパス New!!

More Related Content

AWS Black Belt Tech シリーズ 2015 - Amazon Redshift

  • 1. 1 @awscloud_jp #awsblackbelt Amazon Redshift AWS Black Belt Tech Webinar 2015 アマゾン ウェブ サービス ジャパン株式会社 下佐粉 昭 2015/08/26 （2015/11/25更新）
  • 2. 2 @awscloud_jp #awsblackbelt 自己紹介 名前：下佐粉 昭（しもさこ あきら） Twitter - @simosako 所属： – アマゾンデータサービスジャパン株式会社 – 技術本部 ソリューションアーキテクト 好きなAWSサービス:Redshift, RDS, S3 • 人間が運用等から開放されて楽になる系の サービスが好きです
  • 3. 3 @awscloud_jp #awsblackbelt アジェンダ • Amazon Redshiftとは？ • パフォーマンスを意識した表設計 • Amazon Redshiftの運用 • Workload Management (WLM) • ユーザ定義関数（UDF） • まとめ • 補足資料：COPYコマンド
  • 5. 5 @awscloud_jp #awsblackbelt Amazon Redshiftの概要 • クラウド上のDWH – 数クリックで起動 – 使った分だけの支払い • 高いパフォーマンス – ハイ・スケーラビリティ • 高い汎用性 – PostgreSQL互換のSQL – 多くのBIツールがサポート
  • 6. 6 @awscloud_jp #awsblackbelt MPPとシェアードナッシングがスケールアウトの鍵 • MPP : Massive Parallel Processing – １つのタスクを複数のノードで分散して実行する仕組み – Redshiftではリーダーノードがタスクをコンピュートノードに分 散して実行する – ノードを追加する（スケールアウト）でパフォーマンス向上可能 • シェアードナッシング – ディスクをノードで共有しない構成 – ディスクを共有するとノード数が増えた時にボトルネックになる ため、それを回避 – ノードとディスクがセットで増えていく
  • 7. 7 @awscloud_jp #awsblackbelt Redshiftの構成① SELECT * FROM lineitem; リーダーノードがクライア ントからSQLを受け取る CPU CPU CPU CPU CPU CPU Leaderノード Computeノード １つの表を各ノード のストレージに分散 して保存（シェアー ドナッシング）
  • 8. 8 @awscloud_jp #awsblackbelt Redshiftの構成② SELECT * FROM lineitem; SQLをコンパイル、 コードを生成し、コン ピュートノードへ配信 CPU CPU CPU CPU CPU CPU Leaderノード Computeノード スライス＝ メモリとディスクを ノード内で分割した論 理的な処理単位 コンピュートノードの追 加でパフォーマンス向上 （スケールアウト）
  • 9. 9 @awscloud_jp #awsblackbelt ノードタイプ • SSDベースのDCとHDDベースのDSから選択 – データは圧縮されて格納されるため、ストレージ総量より多くのデータが格納可能 • 最大100ノード：1.6PByteまで拡張可能 – ノードタイプと数は後から変更可能 DC1 - Dense Compute vCPU メモリ(GB) ストレージ ノード数 価格(※) dc1.large 2 15 0.16TB SSD 1~32 $0.314 /1時間 dc1.8xlarge 32 244 2.56TB SSD 2~100 $6.095 /1時間 DS2 – Dense Storage ds2.xlarge 4 31 2TB HDD 1~32 $1.190 /1時間 ds2.8xlarge 36 244 16TB HDD 2~100 $9.520 /1時間 ※価格は東京リージョンにおいて2015年11月25日時点のものです New!!
  • 10. 10 @awscloud_jp #awsblackbelt 【補足】リーダーノードと利用費用 • リーダーノードもコンピュートノードも同じ ノードタイプで構成される • リーダーノード分は利用費用が不要 • １ノード構成にした場合、リーダーノードとコ ンピュートノードが１ノードに同居する
  • 11. 11 @awscloud_jp #awsblackbelt IOを削減する① - 列指向型（カラムナ） ・行指向型（他RDBMS） ・列指向型（Redshift） orderid name price 1 Book 100 2 Pen 50 … n Eraser 70 orderid name price 1 Book 100 2 Pen 50 … n Eraser 70 DWH用途に適した格納方法
  • 12. 12 @awscloud_jp #awsblackbelt analyze compression listing; Table | Column | Encoding ---------+----------------+---------- listing | listid | delta listing | sellerid | delta32k listing | eventid | delta32k listing | dateid | bytedict listing | numtickets | bytedict listing | priceperticket | delta32k listing | totalprice | mostly32 listing | listtime | raw IOを削減する② - 圧縮 • データは圧縮してストレージに格納 される • カラムナのため類似したデータが集 まり、高い圧縮率 • エンコード（圧縮アルゴリズム）は 列ごとに選択可能 • COPYコマンドやANALYZEコマンド で圧縮アルゴリズムの推奨を得るこ とが可能
  • 13. 13 @awscloud_jp #awsblackbelt IOを削減する③ - ゾーンマップ Redshiftは「ブロック」単位で ディスクにデータを格納 １ブロック＝１MB ブロック内の最小値と最大値をメ モリに保存 不要なブロックを読み飛ばすこと が可能 10 | 13 | 14 | 26 |… … | 100 | 245 | 324 375 | 393 | 417… … 512 | 549 | 623 637 | 712 | 809 … … | 834 | 921 | 959 10 324 375 623 637 959
  • 14. 14 @awscloud_jp #awsblackbelt フルマネージドサービス 設計・構築・運用の手間を削減 • 数クリックで起動 • １時間単位の費用 • ノード数やタイプは後から変更可能 • バックアップ(Snapshot)やモニタリング機能を内蔵 – GUI（マネジメントコンソール） – API経由で操作も可能 • パッチ適用も自動的 – メンテナンスウィンドウでパッチの時間帯を指定可能
  • 15. 15 @awscloud_jp #awsblackbelt Redshiftが向く用途 • 特化型のデータベースのため、適した用途に使うことで パフォーマンスを発揮します • Redshiftに向くワークロード – 巨大なデータ・セット（数百GB～ペタバイト） – １つ１つのSQLが複雑だが、同時実行SQLは少ない – データの更新は一括導入 • ユースケース – データウェアハウス（DWH） – ユーザがクエリーを作成する（自由クエリー）（BI等）
  • 16. 16 @awscloud_jp #awsblackbelt Redshiftの特徴を生かせないユースケース • SQLの並列実行数が多い（※同時接続数ではなく同時実行数） – RDS（MySQL ,PostgreSQL, Oracle, SQL Server)を検討 • 極めて短いレーテンシが必要なケース – ElastiCache (インメモリDB)やRDSを検討 • ランダム、かつパラレルな更新アクセス – RDSもしくはDynamoDB (NoSQL)を検討 • 巨大なデータを格納するが集計等はしない – DynamoDBや大きいインスタンスのRDSを検討
  • 17. 17 @awscloud_jp #awsblackbelt Amazon Redshiftの位置づけ データ・ストアの特性に応じた使い分け Amazon DynamoDB Amazon RDS Amazon ElastiCache Amazon Redshift SQL NoSQL• 低レンテンシ • インメモリ • 3拠点間での レプリケーション • SSDに永続化 • トランザク ション処理 • 汎用用途 • 集計・分析処理 • 大容量データ • DWH
  • 19. 19 @awscloud_jp #awsblackbelt DDLによるパフォーマンスの最適化 • ディスクIOを削減する – サイズを減らす – 読む範囲を減らす • ノード間通信を削減する – 通信しないようなデータ配置
  • 20. 20 @awscloud_jp #awsblackbelt ディスクIOを削減する：型を適切に選択する • 型を適切に選択してサイズを節約する – 不必要に大きい型を選択しない – BIGINT(8バイト)よりも、INT(4バイト)やSMALLINT(2バイト) – FLOAT(8バイト)よりも、REAL(4バイト） – 日付は文字列(CHAR)で格納せずTIME型を使用
  • 21. 21 @awscloud_jp #awsblackbelt Redshiftで利用可能な型 • 下表の型をサポート • charはシングルバイトのみサポート • varcharはUTF-8形式でのマルチバイトをサポート 参照） http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-datatypes.html
  • 22. 22 @awscloud_jp #awsblackbelt ディスクIOを削減する：適切な圧縮方法の選択 • 圧縮を行うことで、一度のディスクアクセスで読み込める データ量が多くなり、速度の向上が見込める • 圧縮のエンコード（アルゴリズム）が複数用意されており、 CREATE TABLEで各列に選択することが可能 • 動的には変更できない (作りなおして INSERT … SELECT） CREATE TABLE table_name ( 列名 型 ENCODE エンコード, )
  • 23. 23 @awscloud_jp #awsblackbelt 圧縮エンコーディングの種類 • データの特性に応じたエンコーディングを選択するのが理想 • ANALYZE COMPRESSIONコマンドで推奨を確認可能 – 先にデータの投入が必要 • LZOは比較的多くのケースで有効
  • 24. 24 @awscloud_jp #awsblackbelt 圧縮エンコーディングの確認 • pg_table_def のencoding列で確認可能 24 mydb=# select "column",type,encoding from pg_table_def where tablename='customer_enc'; column | type | encoding --------------+-----------------------+---------- c_custkey | integer | delta c_name | character varying(25) | lzo c_address | character varying(25) | lzo c_city | character varying(10) | bytedict c_nation | character varying(15) | bytedict c_region | character varying(12) | bytedict c_phone | character varying(15) | lzo c_mktsegment | character varying(10) | bytedict
  • 25. 25 @awscloud_jp #awsblackbelt ディスクアクセスの範囲を最小にする • SORTKEY – SORTKEYに応じて、ディスク上にデータが順序を守って格納 – クエリー・オプティマイザはソート順序を考慮し、最適なプランを構築 – CREATE TABLE時に指定。複数列が指定可能 • CREATE TABLE t1(…) SORTKEY (c1,c2 …) • SORTKEYの使いどころ – 頻繁に特定のカラムに対して、範囲または等式検索を行う場合 • 例）時刻列 – 頻繁にジョインを行う場合、該当カラムをSORTKEYおよびDISTKEYとし て指定→ ハッシュ・ジョインの代わりにソート・マージ・ジョインが選択 される 25
  • 26. 26 @awscloud_jp #awsblackbelt SORTKEY の例 • orderdate 列をSORTKEY に指定した場合： 2013/07/17 2013/07/18 2013/07/18 2003/07/19 … I0001 I0002 I0003 I0004 ・・・ 2013/08/20 2013/08/21 2013/08/22 2013/08/22 … I0020 I0021 I0022 I0023 orderdate…orderid SELECT * FROM orders WHERE orderdate BETWEEN ‘2013-08-01’ AND ‘2013-08-31’; クエリで必要なデータが固まっているた めディスクアクセス回数が減少
  • 27. 27 @awscloud_jp #awsblackbelt Interleaved Sort Key • 新しいSort keyのメカニズム • 最大８つまでのSort Key列を指定でき、それぞれ同等に扱わ れる CREATE TABLE ~ … INTERLEAVED SORTKEY (deptid, locid); • 旧来のSortで複数のキーを指定する場合（Compound Sort Key)とは特性が異なり、各列を同等に扱う • Interleaved Sort Keyが有効なケース – どのキーがWHERE句で指定されるか絞り切れないケース – 複数キーのAND条件で検索されるケース New!!
  • 28. 28 @awscloud_jp #awsblackbelt Interleaved Sort Keyのデータ配置イメージ DeptId LocId 1 A 1 B 1 C 1 D 2 A 2 B 2 C 2 D DeptId LocId 3 A 3 B 3 C 3 D 4 A 4 B 4 C 4 D Compound Sort Key Interleaved Sort Key DeptId LocId 1 A 1 B 2 A 2 B 1 C 1 C 2 D 2 D DeptId LocId 3 A 3 B 4 A 4 B 3 C 3 D 4 C 4 D DeptId = 1 -> 1 block LocId = C -> 4 block DeptId = 1 -> 2 block LocId = C -> 2 block DeptId=1 and LocId=C-> 1 block
  • 29. 29 @awscloud_jp #awsblackbelt データの平準化:各ノードのデータサイズが著しく異なると パフォーマンスに影響が出る CPU CPU CPU CPU CPU CPU ノード間のデータ容量 の偏りはクエリー実行 時間に影響を与える
  • 30. 30 @awscloud_jp #awsblackbelt データの転送を最小限にする 自ノードに必要なデータ がない場合、データ転送 が発生 - 単一ノード - ブロードキャスト リーダー・ノードに 各ノードの結果を集約
  • 31. 31 @awscloud_jp #awsblackbelt ディストリビューションの選択 ALL Node 1 Slice 1 Slice 2 Node 2 Slice 3 Slice 4 全ノードにデータをコ ピー KEY(DISTKEY) Node 1 Slice 1 Slice 2 Node 2 Slice 3 Slice 4 同じキーを同じ場所に Node 1 Slice 1 Slice 2 Node 2 Slice 3 Slice 4 EVEN ラウンドロビンで均一分散 （※デフォルト） CREATE TABLE t(…) DISTSTYLE { EVEN | KEY | ALL }
  • 32. 32 @awscloud_jp #awsblackbelt EVEN vs. DISTKEY（１） • EVEN • DISTKEY=p_partkey select trim(name) tablename, slice, sum(rows) from stv_tbl_perm where name='part' group by name, slice order by slice; tablename | slice | sum -----------+-------+--------- part | 0 | 1600000 part | 1 | 1600000 … part | 126 | 1600000 part | 127 | 1600000 tablename | slice | sum -----------+-------+--------- part | 0 | 1596925 part | 1 | 1597634 … part | 126 | 1610452 part | 127 | 1596154 各スライスに均等に分散 キーのカーディナリティに依存
  • 33. 33 @awscloud_jp #awsblackbelt EVEN vs. DISTKEY（２） • DISTKEY = p_brand tablename | slice | sum -----------+-------+--------- part | 0 | 0 part | 1 | 0 part | 2 | 0 part | 3 | 0 part | 4 | 8193350 … part | 118 | 8193342 part | 119 | 0 part | 120 | 16384823 part | 121 | 8191943 カーディナリティの低い カラムでは、データの極端な 偏りが生じる場合がある = クエリー処理効率の低下
  • 34. 34 @awscloud_jp #awsblackbelt ALL • 全レコードが各ノードの特定スライスに集約 tablename | slice | sum -----------+-------+--------- part | 0 |204800000 part | 1 | 0 part | 2 | 0 part | 3 | 0 part | 4 | 0 … part | 96 |204800000 part | 97 | 0 part | 98 | 0 … … 各ノードの先頭スライスに 全レコードが格納される。
  • 35. 35 @awscloud_jp #awsblackbelt コロケーション（１） • 関連するレコードのコロケーション – ジョイン対象となるレコードを同一ノードに集める • コロケーションの方法 1. ジョインに使用するカラムをDISTKEYとして作成 または 2. 分散方式 ALLでテーブルを作成（マスター・テーブルなど） select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part Where (p_partkey = l_partkey … 1. それぞれをDISTKEYとして作成 または 2. テーブルをALLで作成
  • 36. 36 @awscloud_jp #awsblackbelt コロケーション（２）：DISTKEY 6200995 | almond pale linen | Manufacturer#3| Brand#32 part lineitem 5024338535 | 6200995 | 0.01 |0.08 | A | F |1992-01-02 | 1992-02-14 2201039 | almond pale linen | Manufacturer#1| Brand#11 part lineitem 121932093 | 2201039 | 0.05 |0.43 | D | E |1994-07-11 | 1994-08-23
  • 37. 37 @awscloud_jp #awsblackbelt コロケーション（３）：ALL part lineitem part lineitem l_partkey l_partkey p_partkey p_partkey 更新：全ノードにレプリケーション クエリー：ジョインはローカルで完結
  • 38. 38 @awscloud_jp #awsblackbelt テーブル設計のポイント • ディスクIOを最小にする – 適切な型の選択 – 適切な圧縮アルゴリズム – ソートキーの設定 • ネットワーク転送を最小にする – 小規模なテーブル（マスター・テーブル）はALLで作成する – 多くのテーブルはEVENで作成するだけで十分なパフォーマンスが 出ることが多い – ジョインのパフォーマンスを最適化するにはジョイン対象のキーを DISTKEYで作成（コロケーション） – 大福帳のようなジョイン済（非正規化）表はEVENで分散
  • 40. 40 @awscloud_jp #awsblackbelt Amazon Redshiftへのデータ投入：オーバービュー AWS Cloud オンプレミス・ データセンター Amazon DynamoDB Amazon S3 Data Volume Amazon Elastic MapReduce Amazon RDS Amazon Redshift Amazon Glacier logs / files ソースDB VPN Connection AWS Direct Connect S3 Multipart Upload EC2/オンプレ (SSH経由)
  • 41. 41 @awscloud_jp #awsblackbelt S3を起点としたRedshift運用の基本的な流れ 1. ロードするデータ（ファイル）をS3に 置く 2. COPYコマンドでデータを高速ロード 3. Analyze＆Vacuumを実行 4. バックアップ（SNAPSHOT）を実行 5. SQLを投入して利用開始（1.へ戻る） S3 Redshift COPY SQL 一般ユーザ 管理者 運用コマンド
  • 42. 42 @awscloud_jp #awsblackbelt S3からデータをCOPYする • ファイルをS3のバケットに置く – カンマや｜等で区切られたテキストファイル形式(delimiterオプションで指定） – 文字コードはUTF-8(デフォルト）とUTF-16をサポート – ファイルサイズが大きい場合は圧縮し（後述）、マルチパートアップロードする • Redshiftに接続してcopyコマンドを実行 – S3にアクセスするためのアクセスキーが必要 – 別リージョン内のS3バケットからのCOPYも可能 (REGIONオプションを指定） • 自動圧縮される – 列にエンコーディング定義がなく、かつ1行も導入されていない場合に実施される – COMPUPDATE OFFオプションを指定すると自動圧縮無しでCOPY copy customer from 's3://mybucket/customer/customer.tbl’ credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>’ delimiter '|'
  • 43. 43 @awscloud_jp #awsblackbelt COPYの速度を上げるには？ • 元ファイルを圧縮する(gzipもしくはlzo) – COPYでgzip もしくはlzoオプションを指定 • ファイルを分割する（スライス数の倍数が最適） – 並列にロードされるため高速にロード可能 • ファイル名は”customer.tbl.1.gz”,”customer.tbl.2.gz”の ように、指定した名前で前方一致出来るように作成 copy customer from 's3://mybucket/customer/customer.tbl’ credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>’ gzip delimiter '|'
  • 44. 44 @awscloud_jp #awsblackbelt 制約について • Redshiftには制約が存在しない – ユニーク制約、プライマリーキー、外部キー、検査制約が無い – ユーザ側の工夫でユニーク性を担保する • 例）一旦データをテンポラリ表にインサートもしくはCOPYし、 SELECT DISTINCTしたデータをインサートする – 制約やプライマリーキーの作成は可能。作成する事でオプティ マイザーにデータの特性情報を伝えることが可能
  • 45. 45 @awscloud_jp #awsblackbelt テーブルのANALYZE • 統計情報はクエリプラン決定の元データとして利用される • ANALYZEコマンドで統計情報を最新に保つことで最適なパフォー マンスを維持 ANALYZEコマンド データベース 全体 単一のテーブ ル テーブルの特 定の列 ANALYZEコマ ンドは行のサン プルを取得し、 計算を行った後 に統計情報を保 存 よく使われる列はANALYZEを行う • ソートやグループ化 • 結合の対象 • WHERE句の条件 • データ投入や更新の後、 定期的にデータベース全 体にANALYZEを実行 • 新しいテーブルを作った らANALYZEを実行 統計情報
  • 46. 46 @awscloud_jp #awsblackbelt テーブルのVACUUM • Redshiftのデータ更新は“追記型” • 削除しても削除がマークされるだけでディスク上にはデータが残っている • VACUUMコマンドで不要領域を削除（コンパクション）し、同時にソート 順にデータを並べ替える 1,2,4 RFK,JFK,GWB 900 Columbus,800 Washington,600 Kansas VACUUM Customer; 1,2,3,4 RFK,JFK,LBJ,GWB 900 Columbus,800 Washington, 700 Foxborough,600 Kansasx xxx xxxxxxxxxxxxxxx DELETE/UPDATEによって空い た未使用領域はVACUUMコマ ンドを実行することでコンパク ションされる
  • 47. 47 @awscloud_jp #awsblackbelt VACUUMコマンド • 通常はFULLを実行（コンパクション＆ソート） – コンパクションだけ実行するにはDELETE ONLY – ソートだけ実行するにはSORT ONLY • Interleaved Sortした表にはREINDEXを指定 – コンパクション＆Interleaved Sort順に並べ替えを実行 VACUUM [ FULL | SORT ONLY | DELETE ONLY | REINDEX ] [ table_name ]
  • 48. 48 @awscloud_jp #awsblackbelt バックアップ機能 – スナップショット • ディスクイメージをS3へバックアップ – 自動スナップショット – 手動スナップショット：ユーザが任意のタイミングで実行 マネジメントコンソールから“Take Snapshot”を選択し、 任意のIDを付けるだけでバックアップ開始
  • 49. 49 @awscloud_jp #awsblackbelt 他リージョンへのスナップショット • 既存クラスタのスナップショットを 別リージョンに作成可能 • リテンション・ピリオド（保存期 間）の指定も可能（最大35日） • KMS暗号化済のスナップショット 転送にも対応 • リージョン間のデータ転送費用が発 生 New!!
  • 50. 50 @awscloud_jp #awsblackbelt Redshiftのモニタリング • コンソールビルトインのGUI – リソース使用率、EXPLAIN、実行クエリー履歴 等 • API経由でデータ取得可能（CloudWatch）
  • 51. 51 @awscloud_jp #awsblackbelt Redshiftへの接続(1) • JDBCとODBCの専用ドライバーが用意さ れている – 管理コンソールからラウンロード可能 • PostgreSQLとプロトコル互換性が有り、 PostgreSQLドライバーでも接続可能 – psqlコマンドでも接続可能 • 極力Redshift専用ドライバーの利用を 推奨
  • 52. 52 @awscloud_jp #awsblackbelt Redshiftへの接続(2) RedshiftにパブリックIPを付与して、AWS外から直接接続が可能 パブリックIPの付与・取り外しは動的に変更可能です • 考慮点１）暗号化 – ODBCもJDBCもSSLで暗号化可能 – ただしJDBCの場合KeytoolでSSL Certificateの導入が必要 • https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/connecting-ssl- support.html#connecting-ssl-support-java • 考慮点２）TCP/IP通信の切断防止 – 長い時間が掛かるSQLやCOPYを実行すると、応答を待ちきれずにTCP/IP接続が切断され るケースがありえます – JDBCやODBCの接続パラメータ、もしくはアプリからドライバに対して設定 – Redshift専用のJDBC/ODBCドライバではKeep AliveがデフォルトでON – もしくはクライアント側のTCP/IP設定を変える（OS内の全アプリが影響を受けます） • http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html New!! : 2015/11/20
  • 54. 54 @awscloud_jp #awsblackbelt Workload Management (WLM) • 実行に長い時間を用するクエリー（ロングクエ リー）は、クラスタ全体のボトルネックとなり、 ショートクエリーを待たせる可能性がある • WLMで用途ごとに、クエリー並列度の上限を設 けた複数のキューを定義することでクエリー処 理の制御が可能
  • 55. 55 @awscloud_jp #awsblackbelt Workload Management • 実行に長い時間を用するクエリー（ロングクエリー）は、クラスタ全体の ボトルネックとなり、ショートクエリーを待たせる可能性がある • WLMで用途ごとに、クエリー並列度の上限を設けた複数のキューを定義す ることでクエリー処理の制御が可能 • デフォルトでは、Redshiftクラスタは単一のキューで構成されている。 RunningDefault queue
  • 56. 56 @awscloud_jp #awsblackbelt WLMの実装（１） User Group A Short-running queueLong-running queue Long Query Group
  • 58. 58 @awscloud_jp #awsblackbelt WLMの効果 • キュー単位でクエリー並列度を保障 – メモリのアロケーションも指定可能 • 特定ユーザ（群）によるクラスタ占有を回避 – 最大クエリー実行時間による制御も可能 • 並列度の増加は、必ずしも性能の向上には つながらない -> リソース競合の可能性
  • 59. 59 @awscloud_jp #awsblackbelt WLMパラメータとパラメータの動的変更 • 新しくWLMのパラメータにdynamicとstaticの区別が用意され、 dynamicはRedshiftを再起動せずにパラメータ変更が可能に • dynamic parameter – Concurrency(並列実行数), – Percent of memory to use (メモリ使用量) • static parameter – User groups – User group wildcard – Query groups – Query group wildcard – Timeout New!!
  • 60. 60 @awscloud_jp #awsblackbelt UDF (ユーザ定義関数) ※2015/09/13：資料追加
  • 61. 61 @awscloud_jp #awsblackbelt RedshiftのUDFサポート • クラスターバージョン v.1.0.991以降で利用可能 • UDF＝ユーザ定義関数 – ユーザが独自の関数を定義できる機能 • Python言語で記述 • スカラー関数の作成をサポート – スカラー：１つの入力値ごとに、１つの値を返す関数 NEW!
  • 62. 62 @awscloud_jp #awsblackbelt UDF定義の例 • 例）引数aとbを比較して大きい方を返すUDF CREATE FUNCTION f_greater (a float, b float) RETURNS float STABLE AS $$ if a > b: return a return b $$ LANGUAGE plpythonu; SELECT f_greater (c1, c2) FROM t1
  • 63. 63 @awscloud_jp #awsblackbelt UDFの登録~実行 1. UDFを登録 2. リーダーノードでバイトコードにコンパイルし、各コンピュートノードに転送 3. SQLからUDFが呼び出されると、各コンピュートノード内でPythonインタプリタが起 動して実行 UDFを実行するPythonインタープリタはサンドボックス環境に置かれ、リソースが制限されている リーダーノード コンピュートノード Python インタープリタ Python インタープリタ Python インタープリタ バイト コード バイト コード バイト コード CREATE FUNCTION
  • 64. 64 @awscloud_jp #awsblackbelt UDFの定義 • 名前は既存関数と被らないように注意 – 引数が異なる関数は別のものとして定義可能（オーバーロード可能） – 例）f_を先頭に付けるといったルールでRedshift既存関数との名前被りを避け る(f_はRedshiftビルトイン関数では使わない事が保証されている） – もしくはスキーマで分ける。ただしデフォルトではビルトイン関数が優先され るので注意 → SET search_pathで調整は可能 • Redshiftの型はPythonの型に変換されるため、その型と、 Pythonプログラム側の型が合っている必要がある CREATE [OR REPLACE] FUNCTION f_function_name ( [引数の名前 引数の型, ... ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ (ここにコード） $$ LANGUAGE plpythonu;
  • 65. 65 @awscloud_jp #awsblackbelt UDFの定義② - 型変換 • Redshiftのdecimal がPythonのfloatに マッピングされる 点に注意 Redshiftの型 Pythonの型 smallint integer bigint long long short long decimal double real float boolean bool char varchar string timestamp datetime
  • 66. 66 @awscloud_jp #awsblackbelt UDFの定義③ - 最適化オプション • VOLATILE （デフォルト） – 同じ引数であっても異なる値が変える可能性を指定 – 実行するたびに毎回計算しなおします • STABLE – １クエリー内での処理中で、かつ同一の引数の場合に同じ結果が返る関数に指定 – オプティマイザーは一度実行した結果をクエリー内で再利用します • IMMUTABLE – 同一の引数であれば常に同じ結果が返る関数に指定 – オプティマイザーは関数を即値に置き換えます CREATE [OR REPLACE] FUNCTION f_function_name ( [引数の名前 引数の型, ... ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ (ここにコード） $$ LANGUAGE plpythonu;
  • 67. 67 @awscloud_jp #awsblackbelt UDFで利用可能なPythonライブラリ • import文でライブラリを読み込み可能 • Python 2.7.8標準ライブラリが導入済 – https://docs.python.org/2/library/index.html – ただし以下は、含まない。 • ScrolledText、Tix、Tkinter、tk、 turtle、smtpd • 加えて以下のライブラリを含んでいる – numpy 1.8.2 – pandas 0.14.1 – python-dateutil 2.2 – pytz 2014.7 – scipy 0.12.1 – six 1.3.0 – wsgiref 0.1.2
  • 68. 68 @awscloud_jp #awsblackbelt カスタムライブラリ機能 • 独自のPythonのライブラリを登録しておいて、UDFで使う事が 可能 CREATE LIBRARY library_name LANGUAGE plpythonu FROM { 'https://file_url' | 's3://bucketname/file_name' [ WITH ] CREDENTIALS [AS] 'aws_access_credentials' [ REGION [AS] 'aws_region' ] [ ENCRYPTED ] } • 登録できるのはSuper Userのみ • S3もしくはHTTPSアクセス可能なところにライブラリファイルを置き、 名前を付けて登録 – *.tar.gzか*.zip形式で保存 – Python 2.7.6以降で動くもの – PG_LIBRARY表に登録ライブラリ一覧が記録されます • CREDENTIALはS3から読み取る場合に必要（AWSのアクセスキー）
  • 69. 69 @awscloud_jp #awsblackbelt 権限 • UDFの作成 – Admin以外がUDFを作成にするは、権限付与が必要 – 例）GRANT USAGE ON LANGUAGE plpythonu TO ユーザ名; • UDFの削除・リプレース – Admin、もしくはUDFの作者のみ可能 • UDFの実行 – UDFへの実行（EXECUTE)パーミッションが必要 – ただし新規に作成されるUDFはPUBLICユーザグループのEXECUTE権限で実行が可能 – 上記をやめるには、PUBLICからEXECUTE権限をREVOKEする （参照）http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/udf-security-and- privileges.html
  • 71. 71 @awscloud_jp #awsblackbelt まとめ • DWH的用途に特化したRDB – ペタバイト級まで拡張可能 • クラウドの良さを活かせるDWH • マネージド・サービス – 機器セットアップやインストールの手間なし – バックアップ（スナップショット）が自動 – その他運用に必要な各種機能（モニタリング、EXPLAIN等）をビルトインで提供 • チューニングポイント – ディスクIOの削減（圧縮、ソートキー） – ネットワーク通信の削減（分散の調整） – Workload Management
  • 72. 72 @awscloud_jp #awsblackbelt Redshift 参考資料 • ドキュメント – https://aws.amazon.com/jp/documentation/redshift/ • フォーラム – https://forums.aws.amazon.com/forum.jspa?forumID=155& start=0 • 新機能アナウンスメント – https://forums.aws.amazon.com/thread.jspa?threadID=132 076&tstart=25 • Amazon Redshift Utils on github – https://github.com/awslabs/amazon-redshift-utils
  • 74. 74 @awscloud_jp #awsblackbelt Webinar資料の配置場所 • AWS クラウドサービス活用資料集 – http://aws.amazon.com/jp/aws-jp-introduction/
  • 76. 76 @awscloud_jp #awsblackbelt AWS初心者向けWebinar • AWSをこれからご使用になる向けのソリュー ションカットのオンラインセミナー – http://aws.amazon.com/jp/about-aws/events/
  • 79. 79 @awscloud_jp #awsblackbelt MANIFESTファイルによるファイル指定 • MANIFESTファイルにより、特定のファイル群 をS3バケットからCOPYできる { "entries": [ {"url":"s3://mybucket-alpha/2013-10-04-custdata", "mandatory":true}, {"url":"s3://mybucket-alpha/2013-10-05-custdata", "mandatory":true}, {"url":"s3://mybucket-beta/2013-10-04-custdata", "mandatory":true}, {"url":"s3://mybucket-beta/2013-10-05-custdata", "mandatory":true} ] }
  • 80. 80 @awscloud_jp #awsblackbelt COPYコマンド：その他の機能 • JSONファイルのCOPY – データ構造の自動認識あるいはJSONPathによる定義 • Avroフォーマットへの対応 • Amazon EMRからの読み込み copy sales from 'emr:// j-1H7OUO3B52HI5/myoutput/part*' credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access- key>'; クラスタID HDFSのパス New!!