クラウド環境におけるDWHの選択肢として、Redshiftはもはや珍しいものではなくなりましたが、弊社内の採用実績はそれほど多くはありませんでした。
本記事は元々そのような社内向けに、Redshiftの基本的な仕様をなるべく網羅的に理解できるようまとめたものです。

筆者について

新卒でフューチャーに入社し、今年で8年目になります。
入社後は一貫して技術畑、オンプレミスのインフラに始まり、直近ではアプリケーションまで含めたプロジェクトの技術統括を担当しています。

私もかつては社内有数のAWSエンジニアを自負していましたが、最近は別の仕事のため少し遠ざかっており、クラウドの世界は日進月歩なこともあり知識をアップデートする必要を感じています。
※この記事の準備中にもElastic Resizeがリリースされました。

本記事は私のリハビリも兼ね、ドキュメントのまとめだけでなく、実際に手を動かして振る舞いを確認した箇所も含んでいます。

Amazon Redshift

RedshiftはカラムナーストレージとMPP(Massively Parallel Processing)に基づくDWH向けデータベースサービスです。
次の順番でそれぞれまとめていきます。

ノード構成
ネットワーク
メンテナンス
パラメータグループ
ワークロード管理
ユーザーとグループ
監査ログ
テーブル設計
データのロードとアンロード・バキューム
ラッシュパフォーマンス
Redshift Spectrum
参考資料

1. ノード構成

1-1. 概要

Amazon Redshiftクラスターは1つのリーダーノードと、複数のコンピューティングノードから構成されます。
それぞれの役割の概要を次にまとめます。

リーダーノード
- クライアントアプリケーションからクエリを受け取ってクエリを解析し、クエリ実行プランを作成する
- コンピューティングノードに対するこれらのプランの並列実行を調整し、コンピューティングノードから得た中間結果を集計してから、最終的にクライアントアプリケーションに結果を返す
コンピューティングノード
- クエリ実行プランを実行し、これらのクエリを処理するためにデータをコンピューティングノード間で伝送する(再分散)
- 集計の中間結果は、クライアントアプリケーションに送り返される前にリーダーノードに送り返される

1-2. 選べるノードタイプ

ノードタイプと料金はこちら。

リーダーノードは無料
- コンピューティングノードを一つにした場合、リーダーノード=コンピューティングノードのシングルノード構成となる
- 通常運用する場合は、コンピューティングノードを複数にするが、この場合リーダーノードが別に1ノード作成される。課金対象となるのはコンピューティングノードのみである
リザーブドインスタンスが購入できる

1-3. リージョンとアベイラビリティ

すべてのクラスターノードが同じAZにプロビジョニングされ、Multi-AZ構成は選択不可能 です。

Amazon Redshift ではデータウェアハウスクラスター内の障害ノードが自動的に検知され、障害ノードの交換が行われます。データウェアハウスクラスターは代替ノードがプロビジョニングされてデータベースに追加されるまで、クエリと更新を行うことはできません。Amazon Redshift では代替ノードが即座に利用可能となり、まず最も高い頻度でアクセスされるデータが S3 からロードされます。こうすることで、可能な限り速やかにクエリの実行が再開できるようになります。単一ノードのクラスターは、データのレプリケーションをサポートしません。ドライブに障害が発生した場合、S3 のスナップショットからクラスターを復元する必要があります。実稼働には少なくとも 2 つのノードを使用することをお勧めします。
https://aws.amazon.com/jp/redshift/faqs/

実際にどれくらいの時間で復旧するのかは未調査です。

2. ネットワーク

2-1. クラスターサブネットグループ

RDSのデータベースサブネットグループと同じようなもので、別途作成する必要があります。

2-2. 拡張されたVPCのルーティング(Enhanced VPC Routing)

拡張されたVPCのルーティングを有効にすると、Amazon Redshiftはクラスターとデータリポジトリ間のすべてのCOPYとUNLOADトラフィックがAmazon VPCを通るよう強制します。

S3との通信はVPC Endpointを作ればOK。ただしRedshift SpectrumはGlueカタログを利用するため、インターネット経由でGlueにアクセスできる必要があり、NATゲートウェイ等の利用が必要となる。※PoCでは確認できていない
DynamoDBは未検証だが、おそらくVPC Endpoint経由であれば問題ないはずである
EMRクラスターは未検証だが、VPC内で通信できれば支障はないはずである

拡張されたVPCルーティングが有効でない場合、Amazon RedshiftはAWSネットワークにおけるその他のサービスへのトラフィックを含むトラフィックをインターネット経由でルーティングします。

3. メンテナンス

3-1. リサイズ

リサイズは以下1~4のプロセスで実行され、気軽に実行できるものではないです。

新しいクラスターを作成する
ソースクラスターを読み取り専用モードで再起動する。既存のコネクションは全て切断され、実行中のトランザクションはロールバックされる
ソースクラスターから新しいクラスターへデータをコピーする
エンドポイントを新しいクラスターへ変更する

クラスターのサイズ変更にかかる時間は、各ノードのデータ量に依存します。

通常、サイズ変更処理には数時間から1日かかる
データ量が多いクラスターではさらに時間がかかることもある

Amazon Redshiftはサイズ変更の操作中、テーブルをソートしません。

クラスターのサイズを変更すると、Amazon Redshiftは分散方式に基づいてデータベースのテーブルを新しいコンピューティングノードに分散し、ANALYZEを実行して統計を更新する
削除のマークが付いた行は転送されないため、テーブルを再ソートする必要がある場合のみVACUUMを実行する必要がある

リサイズ後のIPアドレスについて以下の特徴があります。

クラスターがパブリックであり、VPC 内に存在する場合、サイズ変更後もリーダーノードの elastic IP アドレス（EIP）は変更されない
クラスターがプライベートであり、VPC 内に存在する場合、サイズ変更後もリーダーノードのプライベート IP アドレスは変更されない
クラスターがVPC内に存在しない場合、サイズ変更オペレーションの一部として、新しいパブリック IP アドレスがリーダーノードに割り当てられる

3-2. Elastic Resize

伸縮自在なサイズ変更がサポートされました。

既存のクラスターにあるノードを追加または削除し、自動的にデータを新しいノードに再分散する。以下1~4のイベントが記録されます。

リサイズリクエスト受付
リサイズ開始
リサイズ完了
再分散完了

Elastic Resizeは新しいクラスターを作成しないため、伸縮自在なサイズ変更オペレーションは通常のリサイズに比べて素早く完了します。実測したところ、確かに速かった(～5分以内)です。

ちなみに、「バックグラウンドでデータが再分配されているとき、一部のクエリの実行時間がわずかに増加するのに気付くかも知れません。」とありますが、差を確認することはできなかったです。

その他、留意したほうが良さそうな点をまとめました。

一時的に使用不可な時間が発生するが、セッションは切断されず、クエリはキューに溜まる
- 2ノードから4ノードへの変更時に40秒程度クエリが発行できない時間が発生した
- その後、クエリの実行時間が大きく増大する時間が60秒程度発生した
  - 2ノードで600msec程度のクエリに4sec～13sec
ノード数を2倍にするか、半分にするかのどちらかしか選べない。また、一度ノード数を増やしてしまうとスライス数が増えるため、2倍にしてから半分にする(元に戻す)とスライス数が過剰になってしまう

3-3. スナップショット

スナップショットは自動または手動を選択できます。

自動スナップショット
- 自動スナップショットは、クラスターを作成するときデフォルトで有効になる
- 通常は、8時間ごとまたはノードあたり5GBのデータ変更ごと (あるいはそのいずれか早い方) に作成される
手動スナップショット
- 手動スナップショットはいつでも取得できる
- デフォルトでは、手動スナップショットは、クラスターを削除した後も、無限に保持される。手動スナップショットを作成するときに保持期間を指定できる

また、次のような特徴があります。

テーブル定義にBACKUP NOを加えると、バックアップ対象外になる
クラスター全体を復元する代わりに、スナップショットから単一のテーブルを復元できる
別アカウントへ共有可能。※RDSと同様
復元する際に(コンソールから実行する限りは)監査設定とIAMロールが外れるので注意が必要。ユーザー側で再設定する必要がある

3-4. メンテナンスウィンドウ

Amazon Redshiftは定期的にメンテナンスを実行して、クラスターにアップグレードを適用します。更新中は Amazon Redshiftクラスターで通常の操作を実行することはできません。

また、特に抑えておきたい内容に以下のような項目があります。

メンテナンスウィンドウは30分以上、24時間以内で指定可能
- クラスター作成時は指定できず、自動で設定される。※コンソール使用時
メンテナンスがスケジュールされた場合、最長45日までは延期可能。

4. パラメータグループ

使用できるパラメータのリストは、パラメータグループが属しているパラメータグループファミリーによって異なります。

これはRedshiftのエンジンバージョン毎に異なるパラメータのリストが使われる(新しいバージョンでないと設定できないパラメータやサポートされないパラメータがある)ということですが、2019年5月時点ではエンジンバージョンは一つ(redshift-1.0)しかないので現時点では気にしなくても良さそうです。

反映する場合は 再起動が必要です。ただしWLM(後述)のうち一部のパラメータは動的変更可能です。

パラメータ一覧

Item	Note
auto_analyze	自動ANALYZEを有効にするか。データ更新がバッチのみなら手動ANALYZEのみにした方が制御しやすい
datestyle	‘ISO, YMD’が無難
enable_user_activity_logging	データベースで実行される前に各クエリを記録する
extra_float_digits	浮動小数点値 (float4 と float8 を含みます) の表示桁数を設定する
max_concurrency_scaling_clusters	同時実行スケーリングで起動されるクラスター数上限を設定する
max_cursor_result_set_size	設定は可能だが、廃止されている
query_group	ワークロード管理で利用するが、パラメータグループで設定することはまずないと思われる
require_ssl	tls接続を使用する
use_fips_ssl	FIPS 準拠の SSL モードを有効にする
search_path	検索パス(PostgreSQLと同じもの)
statement_timeout	指定されたミリ秒数以上かかっているステートメントを中止する。0に設定すれば制限なし

セッション内でのみ変更可能なパラメータもあります。

Item	Note
`analyze_threshold_percent`	`ANALYZE`を実行する際のしきい値を設定する。`analyze_threshold_percent`の指定よりも変更された行の割合が低いテーブルの分析は省略される。デフォルトは10(パーセント)
`describe_field_name_in_uppercase`	`SELECT`で返却される列名を大文字にする。デフォルトはオフ
`timezone`	セッションのタイムゾーンを設定する。デフォルトはUTC。パラメータグループでは設定できない。デフォルト設定した場合はユーザーのデフォルトセッションパラメータに設定する
`wlm_query_slot_count`	WLM設定参照

5. ワークロード管理

Amazon Redshift では、**ワークロード管理(WLM)**を使用して、使用可能なクエリキューの数と、処理するためにクエリをそれらのキューにルーティングする方法を定義します。

デフォルトのWLM設定には、最大5つのクエリを同時に実行できる1つのキューが含まれ、最大で8個のキューを定義できます。

5.1 スーパーユーザーキュー

スーパーユーザーキューは、スーパーユーザー専用に予約されており、設定することはできません。

このキューは、システムに影響を与えるクエリを実行する必要があるときや、トラブルシューティング目的でのみ使用することが想定されています。

スーパーユーザーキューでクエリを実行するには、ユーザーはスーパーユーザーとしてログインし、事前定義された superuserクエリグループを使用してクエリを実行する必要があります。

5.2 WLM設定

WLMはwlm_json_configurationパラメータで制御すします。wlm_json_configurationはJSON(キューに対応するオブジェクトの配列)として定義します。キューに名前を付けられないのでわかりにくいですね。

プロパティは動的/静的があります。動的はクラスターを再起動することなく適用でき、静的プロパティはクラスターの再起動が必要です。

名称	種別	説明	備考
`query_concurrency`	動的	キューの同時実行クエリ数。キューが同時実行レベルに達すると、後続のクエリを処理するリソースが利用可能になるまでそれらのクエリはキューで待機する。	定義可能な範囲は1～50だが、全てのキューを合計して15までが推奨値
`max_execution_time`	動的	クエリが実行されて始めてからキャンセルされるまでの最大時間（ミリ秒単位）。COPY ステートメントと、ANALYZE や VACUUM などのメンテナンスオペレーションは、WLM タイムアウトの対象にはならない
`memory_percent_to_use`	動的	キューに割り当てるメモリの割合。すべてのキューに割り当てられたメモリの合計が100パーセントを下回る場合、未割り当てのメモリはサービスによって管理され、処理用に追加メモリをリクエストするキューに一時的に付与できる。クエリスロットに割り当てられるメモリ量は、キューに割り当てられたメモリをスロットカウントで割った割合と同じ。	デフォルトは各ユーザー定義キューへ均等に割り当て。100%になるような割り当てが無難。`Superuser`キューは独自に割り当てられているメモリがあるため変更負荷
`user_group`	静的	ユーザーグループ名のカンマ区切りリスト。ユーザーグループのメンバーがデータベースでクエリを実行すると、そのメンバーのクエリはユーザーグループに関連付けられたキューにルーティングされる
`user_group_wild_card`	静的	ユーザーグループでワイルドカードを有効にするかどうかを示すブール値。ワイルドカードが有効な場合、「+」または「?」を使用し、クエリを実行するときに複数のユーザーグループを指定できる	0:無効 1:有効
`query_group`	静的	クエリグループのカンマ区切りリスト。クエリグループのメンバーがデータベースでクエリを実行すると、そのメンバーのクエリはクエリグループに関連付けられたキューにルーティングされる
`query_group_wild_card`	静的	クエリグループでワイルドカードを有効にするかどうかを示すブール値。ワイルドカードが有効な場合、「+」または「?」を使用し、クエリを実行するときに複数のクエリグループを指定できる	0:無効 1:有効

以下、キューとルーティングについて追記します。

ルーティングについて
- query groupを使ってルーティングする場合
  - ユーザーのデフォルトセッションパラメータでquery_groupを定義することも出来るので、変えたい時だけSETする運用が可能である
  - ex. set query_group to 'priority-high'; xxx; reset query_group;
- user_groupをつかってルーティングする場合
  - アプリケーションによってワークロードが変わる場合は、アプリケーションごとにユーザーを作成することで、異なるキューを割り当てることができる
キュー割り当てルール
1. user = Superuser and Query Group = superuser, then Superuser queue
2. Matching user group, then user group
3. Matching query group, then query group
4. default queue
VACUUMのルーティング
- VACUUMのルーティングはテーブルの所有者権限(またはスーパーユーザー権限)を持っていないと実行できないため、所有者が含まれるuser_groupでキュー定義しているとVACUUMがそのキューに行ってしまう。user_groupではなくquery_groupでルーティングするようにした方が良い
- 例えばテーブルAの所有者ユーザーAがグループAに所属していた場合、グループAに対して定義されたキューがあると、ユーザーAが発行するSELECTとVACUUMがどちらの同じキューへルーティングされる
キューの分割パターン
- スループット重視で並列度を管理可能な夜間バッチ
  - 実行されるクエリが予測できるので、タイムアウトはさせない
  - 夜間バッチ前後にWLM設定を変更し、日中クエリにメモリを明け渡すことも検討する
- リクエスト数が多いが、定型的でキャッシュヒットしやすいクエリ(ダッシュボード等)
  - 実行されるクエリが予測できるので、タイムアウトはさせない
  - 同時実行スケーリングを有効にする
- 実行されるクエリが予め予測できないアドホッククエリ
  - メモリを使いすぎないように、メモリ上限を少なめにしておく
  - タイムアウトを設定する
  - クエリモニタリングを設定する

キューの確認方法

service_classがわかりにくいですが…

1, 2, 3, 4はシステム用
5がスーパーユーザーキュー
6～13までユーザー定義キュー
14が SQA 用のキュー

キューの確認方法

select service_class, num_query_tasks, query_working_mem, name from stv_wlm_service_class_config;

キューとクエリのマッピング方法

キュートクエリのマッピング

select
    wq.queue_start_time
,   wq.service_class
,   wq.total_exec_time
,   q.label
,   q.concurrency_scaling_status
,   trim(q.querytxt) as query_text
from
    stl_wlm_query wq
inner join
    stl_query q
on  wq.query = q.query
where 1=1
and wq.service_class > 5
and wq.final_state = 'Completed'
order by
    q.query desc
limit 20
;

5.3 クエリモニタリング

リソース消費が激しいクエリや、検索件数が多すぎるクエリなど、滅茶苦茶なクエリを監視することができます。

また、基準に抵触した場合に、ログ・ホップ・キャンセルを選ぶことができます。

ログ: ログ出力するのみ
ホップ: 実行状態を保ったままキューを移動させる
キャンセル: クエリをキャンセルする

5.4 クエリホッピング

WLMタイムアウトした場合・クエリモニタリングでホップした場合、次の有効なキューに移されます。この場合は一度キャンセルされるわけではなく、実行状態を保ったままキューを移動します。

これにより低レイテンシ用のキューで予期せず長時間かかるクエリが実行された場合に、長時間クエリ用のキューへ移動することが可能になります。

6. ユーザー・グループ

現在のPostgreSQLではRoleに統合されているが、Redshiftでは分かれています。
わかりにくいのでユーザーとグループで同じ名前を使わないようにすべきでしょう。

グループの使い道
- 権限設定
- WLMキューの振り分け
全てのユーザーが暗黙的にPUBLICグループに所属しています。
- PUBLICグループに対してpublicスキーマのCREATEとUSAGEが付与されているので、publicスキーマを使用しない場合はREVOKEしておくか、search_pathを設定しておくと良いでしょう。
通常のデータベースユーザーもIAM認証も可能です。
ユーザーのパスワードを無効にするにはDISABLEを指定します。パスワードを無効にしてもIAM認証は可能です。
CREATEUSERオプションを使用すると、CREATE USERを含め、データベースに関するすべての権限を持つスーパーユーザーが作成されます。
SYSLOG ACCESS UNRESTRICTEDを指定すると、別のユーザーによって生成された行を含む、ユーザーが表示可能なシステムテーブルとビューのすべての行を表示できます。ただし、スーパーユーザーのみが表示可能なテーブルへのアクセス権は与えられません。デフォルトはRESTRICTEDです。なお、STV_RECENTS および SVV_TRANSACTIONS のすべての行は、すべてのユーザーに表示されます。
CONNECTION LIMITを指定すると、ユーザーが同時に開けるデータベース接続の最大数を指定できます。デフォルトはUNLIMITEDであり、クラスターの同時接続制限数は500です。
ALTER USER username SET parameter TO valueでセッションパラメータのデフォルト値を設定できます。
- ユーザーレベルで設定するのはsearch_pathとstatement_timeoutくらいでしょうか。

ユーザーとグループのマッピング確認は以下の通りです。

ユーザとグループのマッピング確認

select
    grp.groname
,   usr.usename
from
    pg_group grp
inner join
    pg_user usr
on usr.usesysid = ANY(grp.grolist)
where 1=1
--    pg_group.groname='<YOUR_GROUP_NAME>'
;

7. 監査ログ

接続ログ・ユーザーログ・ユーザーアクティビティログが収集可能です。

接続ログ
- 認証の試みと、接続および切断を記録する
- 自動でシステムテーブル(STL_CONNECTION_LOG）に出力されるが、S3に出力することも可能
ユーザーログ
- データベースのユーザー定義への変更に関する情報を記録する
- 自動でシステムテーブル(STL_USERLOG）に出力されるが、S3に出力することも可能
ユーザーアクティビティログ
- データベースで実行される前に各クエリを記録する
- S3へのログ出力を有効化したうえでパラメータ enable_user_activity_logging の設定が必要

ただし、S3出力時には権限設定が必要で、ELBと同様にRedshiftがS3に書き込めるよう特定のアカウントIDからのアクセス許可（s3:GetBucketAcl, s3:PutObject）を付与します。
検証したところ、有効にしてからログが配信されるまで1時間くらいかかりました。

8. テーブル設計

8-1. 実行計画

テーブル結合

性能観点では、Merge Joinが最適で、次点がHash JoinでNested Loopの順。

しかし、Merge Joinが選択されるためには、分散キー/ソートキーで結合される必要があります。分散キーとソートキーは同じカラムでないといけないので、ファクトとディメンジョンの結合ではまずならないです。また。ソートキーはcompound sort keyである必要があります。[分散スタイル](#8-2. 分散スタイル)は両テーブルともKEYである必要があります。ALLでは(必要なデータはローカルにあるはずだが)Merge Joinが選択されません。

上記の制約もありMerge Joinが使えるケースは希少だと思うので、基本的にHash Joinを使うように考えた方が良さそうです。高速化する場合は分割並列化し、分割キーで(可能なら)ソートキーを作る、あるいは複合キーに加えます。

また、Nested Loopを選ばないためには、結合は等価結合でないとダメです。

【参考】ステップ概要・クエリプランの評価

クエリの実行

開発者ガイドのクエリプランと実行ワークフローによると、実際にはSQLがそのまま実行されるのではなく、C++コードに変換された後コンパイルされて実行されます。

コンパイルに秒単位の時間がかかるため、初回の検索に時間がかかりますが、実行計画が同じクエリであればコンパイル結果が再利用されます。

設計方針

最終的にはERではなくクエリパタンに基づき実行計画を設計して、分散スタイルとソートキーを設定する
とはいえ、ERがないとクエリが設計できないので、下記のガイドに従って仮決めする。
大雑把に言えばレイテンシーに影響があるのはソートキーと圧縮で、スループットに影響があるのは圧縮キー
- それでもレイテンシーが足りなければデータマートやサマリテーブルを検討する

8-2. 分散スタイル

テーブルを作成する場合はEVEN/ALL/KEY/AUTO(デフォルト)のいずれかの分散スタイルを指定します。

EVEN: ラウンドロビン方式で各スライスへ行を分散する
ALL: テーブル全体のコピーが全てのノードに分散される
- サイズが小さいテーブルに適しているようにも思えるが、更新コストが増える割に再分散コストが低いため、大きなメリットは得られない。また、結合しない場合は各スライスの検索結果をUNIONすることになるため、むしろ遅くなる。
- 更新頻度が低く、更新範囲が広くないテーブルに適している
- 結合する際に内部表となる場合に有効。実行計画がDS_DIST_ALL_NONEとなる
KEY: 特定の列に含まれている値に従って、複数の一致する値を同じノードスライスに配置する
- 指定できる分散キーは一つだけ。複合キーにはできない
- where句で指定されるカラムは不向き。クエリが分散しなくなってしまうため。ただし、分散キーをソートに指定することも出来る
AUTO: テーブルサイズに応じてEVENかALLか自動的に判定する

Amazon Redshift テーブル設計詳細ガイド:Part 2 分散スタイルと分散キーに最適な分散スタイルと分散キーを選ぶ方法論が紹介されています。以下に簡単にまとめます。

適切なDistKeyの特定
- 列のデータが均一に分散しているか？
  - DistKeyの値を同じくするレコード数がどれも同じくらいか？
- 列のカーディナリティが高いか？
  - スライス数を大きく上回る(10倍以上)のユニーク値を持つか？
  - 少ないとスライスごとにデータサイズがばらつく
- クエリは選択的フィルターを実行するか？
  - 実行しないのであれば、分散キーの有力候補
  - 実行するのであれば、まず第一ソートキーになり得るか確認する
    - 加えて同じキーで分散・ソートが設定してあるテーブルと結合するならMerge Joinになるので、分散キーの有力候補
分散スタイルの選択
- 結合しないテーブルの場合
  - 適切なDistKeyがあればKey分散を選択する
  - なければEVEN分散になる。ALL分散は採用しない
- 結合するテーブルの場合、まずALL分散を検討する
  - 以下の条件を全て満たせばALL分散を採用する
    - 小さいテーブルである。全ノードへコピーするためストレージ使用量が増えてしまう
    - 更新頻度の低いテーブルである。全ノードで重複データを持つため、全ノードにレプリケートされるまで書き込みが終わらなくなる
    - 駆動表にならない。駆動表となった場合に、全ノードで同一データをスキャンするため非効率
    - 適切な分散キーが見つからない
  - 採用できない場合、結合しないテーブルにおける検討に従ってKey分散ないしEVEN分散とする

8-3. ソートキー

ソートキーを指定することでスキャン範囲を制限することができる。
- index scanというよりも、パーティションプルーニングに近い。
なるべくprefixのついていない文字列を選ぶ(先頭8バイトしかソートに使われない)
where句で指定されるカラム(indexと同じ使い方)か、結合キーになるカラムを指定する
- 分散キー・ソートキーで結合する場合にMerge Joinが選択され、Redshiftでは最適な結合プランになる。結合キーだからソートキーにするのではなく、Merge Joinを狙う場合に指定する
ソートキーの種類(Compound and Interleaved Sort Keys)
- compoundは複数のソートキーを指定した場合に、指定順にソートされる。interleavedは多次元のゾーンマップが構成されるので指定順に関係なし
- Merge Joinを狙うか？YESならcompound
  - Merge Joinとなる条件は結合条件に分散キーとソートキーが含まれること
    - Key分散しているテーブル同士でないとMege Joinにはならない。ソートキーが一致していてもKey分散とALL分散ではMerge Joinが選択されない
  - Hash Joinの方が速いケースもある。結合よりも抽出を高速にした方が良い場合は抽出条件をソートキーにした方が良い
- ソートはゾーンマップを改善するか？クエリはゾーンマップを利用するか？
  - ゾーンマップは、1MBブロック毎に、ブロック内の最小値と最大値をメモリー内にメタデータとして保存します
    - 各スライス事にゾーンマップを管理する。最低限、カラムデータサイズがスライス数x1MBを超えないと意味がない
  - FunctionやCAST(暗黙CASTも含むので注意)では利用されない。PostgreSQLのパーティションキーと同じ
  - 抽出条件指定でcompoundかinterleavedか選択する
    - compoundの場合、第一ソートキーが指定されないと無意味
    - とはいえ、vacuum reindexのコストが高いため、interleavedは選びにくい。マート専用か
- ソートは実行時のソート処理を削減するか？YESならcompound
  - ORDER BY、GROUP BY,　Window関数内のPARTITION BY, ORDER BYなど
  - ソートを高速化したいケースでは大概ゾーンマップを利用したくなるはずなので、これだけを理由とするケースはあまり想像できない
  - where col_a = 'a' group by col_bのようなケースでソートキーcol_a, col_bが有効かどうか
- どれもNOならソートキーにしない
メンテナンス
- compound
  - vacuum delete onlyはバックグラウンドで実行されるため、バッチの中でvacuum sort onlyを実行するのは有用かも
  - 未ソート領域が大きい場合はVACUUMよりもディープコピーを行う
- interleaved
  - COPYまたはCTASを使用して空テーブルにロードすると、自動的にインデックスが作成される。INSERTを使用してロードした場合はVACUUM REINDEXの実行が必要
  - VACUUM REINDEXのコストが高い。まるごとCTASで作り直す設計にし、VACUUM REINDEXを実行しないことを第一に考えるべき
    - ただしCTASだと圧縮エンコーディングが指定できず、自動設定される
compound sortkeyの状態確認

compound sortkeyの状態確認

select
    sti.schema
,   stp.name
,   stp.sorted_rows
,   stp.rows
,   round(100.0 * stp.sorted_rows / (stp.rows + 0.0000001), 2) as sort_percentage
from (
    select
        id
    ,   name
    ,   sum(sorted_rows) as sorted_rows
    ,   sum(rows) as rows
    from
        stv_tbl_perm
    group by
        id, name
    ) stp
inner join svv_table_info sti
    on stp.id = sti.table_id
where
    sti.sortkey_num > 0
and stp.rows > 0
and stp.sorted_rows <> stp.rows
order by
    sort_percentage
;

interleaved sortkeyの状態確認

interleaved sortkeyの状態確認

select
    tbl as tbl_id
,   stv_tbl_perm.name as table_name
,   col
,   interleaved_skew
,   last_reindex
from
    svv_interleaved_columns
inner join stv_tbl_perm
    on  svv_interleaved_columns.tbl = stv_tbl_perm.id
order by 1

8-4. キー

UNIQUE・PRIMARY KEY・References・FOREIGN KEYはプランナが利用するものの、システムに強制されない(制約にならない)です。

8-5. データ型

数値
- 数値データ型には、整数型、10 進数型、および浮動小数点数型などがあります。

型	備考
`SMALLINT`/`INT2`	符号付き2バイト整数
`INTEGER`/`INT`/`INT4`	符号付き4バイト整数
`BIGINT`/`INT8`	符号付き8バイト整数
`DECIMAL`/`NUMERIC`	任意精度
`REAL`/`FLOAT4`	単精度浮動小数点数
`DOUBLE PRECISION`/`FLOAT`/`FLOAT8`	倍精度浮動小数点数

文字列
- CHAR/VARCHARは最大長を宣言する代わりにMAXキーワードが使用可能です。

型	備考
`CHAR`	バイトセマンティクスで4096バイト。シングルバイト文字のみ
`VARCHAR`	バイトセマンティクスで65535バイト。マルチバイト文字がサポートされており、対応している文字コードはUTF-8、1～4バイト文字まで利用可
`TEXT`	`VARCHAR(256)`の別名

日付と時刻
- 日付はDATE、日付時刻にはTIMESTAMPとTIMESTAMPTZがある
- クラスタのタイムゾーンはUTC固定だが、セッションパラメータを変更することは可能
真偽値
- 論理ブール演算型 (true/false)としてBOOLEAN/BOOLがある
その他
- 配列・JSONはサポートされていない

8-6. 圧縮エンコード

ANALYZE COMPRESSIONを実行して圧縮率を評価できる
- ANALYZE COMPRESSIONは排他的テーブルロックを取得し、テーブルに対する同時読み取り書き込みがブロックされる。ANALYZE COMPRESSIONコマンドは、テーブルがアイドル状態になっている場合にのみ実行する。
圧縮エンコードの選択。基本的にはデータドメインで定まる。以下に記載のないエンコーディングは有効なユースケースが不明なので、ANALYZE COMPRESSIONで提示されたら検討すると良いと思います。
- ソートキーカラムは非圧縮(RAW)にする
- 区分値はバイトディクショナリエンコード(BYTEDICT)
  - ディクショナリサイズ: 1MB
  - キーサイズ: 1バイト. 最大256個
  - 列のデータドメインが一意の値 256 個未満である場合に最適
- 汎用で使われるエンコードはLZOとZSTD。ZSTDの方が優れているが、後発であるためか圧縮エンコードを指定しない場合のデフォルトはLZOになる
  - LZO(LZO)
    - LZOエンコードは、非常に長い文字列を格納するCHARおよびVARCHAR列、特に製品説明、ユーザーコメント、JSON文字列などの自由形式テキストに適している
    - LZOは、ソートキー、およびBOOLEAN、REAL、またはDOUBLE PRECISIONデータ型として定義された列として指定された列以外のエンコードのデフォルトとされている
  - Zstandard(ZSTD)
    - Zstandard (ZSTD) エンコーディングは、多様なデータセット間で非常にパフォーマンスのいい高圧縮比率を提供します。ZSTD は、製品説明、ユーザーのコメント、ログ、JSON 文字列など、長さがさまざまな文字列を保存するCHARおよびVARCHAR列に対して、特に効果を発揮します
    - ZSTD では、Amazon Redshift のすべてのデータ型がサポートされています
- ランレングスエンコード(RUNLENGTH)
  - 連続して繰り返される値を、値と連続発生数 (実行の長さ) から成るトークンに置き換えます
  - ソートキーに関係従属するカラムに使えるかと思いましたが、実測したところZSTDの方が高圧縮でした。

カラム毎に使用しているブロック数は以下のSQLで確認できます。圧縮の効果が確認できます。

利用ブロック数の確認

select
    t.schema
,   t.table
,   c.column_name
,   max(b.blocknum)
from
    stv_blocklist b
inner join
    svv_table_info t
        on b.tbl = t.table_id
inner join
    svv_columns c
        on t.schema = c.table_schema
        and t.table = c.table_name
        and b.col = c.ordinal_position -1
where
    t.table = 'xxx'
and c.column_name = 'yyy'
group by 1,2,3
order by 1,2,3

9. データのロードとアンロード・バキューム

9-1. データロード

COPYコマンドを使用して、Amazon S3 バケット、Amazon EMR クラスター、リモートホスト (SSH 接続を使用)、または Amazon DynamoDB テーブルからデータをロードできる
COPY、UNLOAD、CREATE LIBRARY のための IAM のアクセス許可
データソース
- Amazon S3 からの COPY
  - オブジェクトプリフィックス(/終わりでなくても良い)
  - マニフェストファイルパス
- Amazon EMR からの COPY
- リモートホスト (SSH) からの COPY
- Amazon DynamoDB からの COPY
データ形式
- CSV [ QUOTE [AS] 'quote_character' ] [DELIMITER [AS] 'delimiter_char']
  - デフォルトの引用文字は二重引用符 ( “ ) だが、QUOTEオプションを使用して別の引用文字を指定できる
  - デフォルトの区切り記号はカンマ (,) だが、DELIMITERパラメータを使用して別の区切り記号を指定できる
- DATE列とTIMESTAMP列をロードする場合、日付の場合は YYYY-MM-DDで、タイムスタンプの場合はYYYY-MM-DD HH:MI:SSがデフォルトの形式となる。デードデータでデフォルトの形式が使用されていない場合、DATEFORMATとTIMEFORMATを使用して形式を指定できる
ファイル圧縮
- BZIP2/GZIP/LZOP/ZSTD
- LZOP形式ではUNLOADできない。BZIP2は時間がかかるので、GZIPが無難
- ZSTDはAmazon S3からCOPYを使用する場合のみサポートされる
その他パラメータ(通常はSTATUPDATE ONだけ指定する運用で良いと思われる)
- COMPROWS numrows: 圧縮分析のサンプルサイズ(スライス合計)として使用される行数を指定する。COMPROWSを指定しない場合、サンプルサイズはデフォルトでスライスごとに100,000になる。COMPROWSの値がスライスごとに100,000行のデフォルト値より小さい場合、自動的にデフォルト値にアップグレードされる。ただし、ロードされるデータの量が有意のサンプルとしては不十分な場合、自動圧縮は実行されない
- COMPUPDATE [ { ON | TRUE } | { OFF | FALSE } ]: COMPUPDATEを省略した場合、ターゲットテーブルが空であり、テーブルのすべての列にRAWエンコードがあるかまったくエンコードがないときにのみ、COPYは自動圧縮を適用する。COMPUPDATE ON(またはTRUE)の場合、テーブル列にRAW以外のエンコードがある場合も、テーブルが空であればCOPYは自動圧縮を適用します。COMPUPDATE OFF(またはFALSE)の場合、自動圧縮は無効になる。
- MAXERROR AS error_count: ロードのエラー数がerror_count以上である場合、ロードは失敗する。ロードのエラーがそれより少ない場合、処理は続行される(正常データのみロードされる)
- NOLOAD: データを実際にロードせずにデータファイルの有効性をチェックする
- STATUPDATE [ { ON | TRUE } | { OFF | FALSE } ]: ON(またはTRUE)の場合、テーブルが最初に空であるかどうかに関係なく、統計は自動的に更新される。STATUPDATEパラメータを使用しない場合、テーブルが最初は空ならば、統計は自動的に更新される
ALTER TABLE RENAMEはトランザクション内で実行できるので、TMPにロード-> TAB RENAME TO BK->TMP RENAME TABのような入れかえができる

9-2. データアンロード

参考: UNLOAD

出力可能な形式
- 固定長(FIXWIDTH)
- Character Separated Values(DELIMITER)
  - 基本的にESCAPEオプションを付けておいた方が無難
    - エスケープ文字は\
    - ラインフィード・キャリッジリターン・区切り文字・エスケープ文字・引用符(ADDQUOTESを指定した場合)がエスケープされる
クエリでORDER BY句を指定してソート順にデータをアンロードしておくと、データの再ロード時にデータをソートするために必要な時間を節約できる
- TOPは SELECT句ではサポートされていない。代わりにLIMITを使用する
- SELECTクエリは、外部のSELECTでLIMIT句を使用することはできない
  - ネストするか、別テーブルにデータを移してからUNLOADする
  - select ... limit 10はダメで、select ... from (select ... limit 10)はOKということ
クエリの中に引用符 (たとえば、リテラル値を囲むため) またはバックスラッシュ (\) がある場合は、クエリテキスト内でバックスラッシュでエスケープする必要がある
書き込み先にマニフェストファイルを指定する場合(MANIFESTオプションを指定する場合)、「manifest」サフィックスが自動的に付与されるため、name_prefixには含めない
マニフェストファイルを指定しない場合の出力フォーマットは<object-path>/<name-prefix><slice-number>_part_<part-number>
ヘッダー出力する場合はHEADERオプション。固定長では出力できない
圧縮する場合はBZIP2オプションかGZIPオプションを付与する
デフォルトでは既存ファイルの上書きは行わない。上書きする場合はALLOWOVERWRITEオプションを付与する
デフォルトではスライス数に応じて複数ファイルに並列書き込みを行う。オフにする場合はPARALLEL OFF
ファイルサイズ上限を指定すると1ファイルあたりのサイズ上限が指定できる。デフォルトでは6.2GB.

9-3. VACUUM

参考: VACUUM

テーブルの所有者またはスーパーユーザーのみがテーブルにバキューム処理を実行できる
VACUUMにはサブコマンドで指定される以下のモードがある
- SORT ONLY: ソートのみ。Compound Sort Keyのメンテナンス
  - デフォルトではテーブルの行の95パーセント以上がすでにソートされているテーブルのソートフェーズをスキップする。実行時にTO threshold PERCENTオプションを指定することでしきい値を変更できる
- DELETE ONLY: 削除のみ。バックグラウンドで自動的にDELETE ONLY Vacuumを実行するため、手動実行する必要は通常ない
  - デフォルトでは残りの行の少なくとも95パーセントが削除対象としてマークされていない領域を再利用する。実行時にTO threshold PERCENTオプションを指定することでしきい値を変更できる
  - ユーザーがALTER TABLEなどのデータ定義言語 (DDL) 操作を実行すると、自動バキューム操作は一時停止する
- FULL(デフォルト): ソート＋削除。TO threshold PERCENTオプションを指定すると、ソートと削除の両方にしきい値が適用される
- REINDEX: Interleaved Sort Keyのメンテナンス。VACUUM FULLよりも大幅に実行時間が長くなる
排他制御
- トランザクションブロック内でVACUUMは実行できない
- VACUUM開始時にテーブルへの一時的な排他アクセスが必要になる
- ユーザーは、バキューム処理中のテーブルにアクセスできる。バキューム処理中のテーブルにクエリおよび書き込み操作を実行できるが、データ操作言語 (DML) コマンドおよびバキュームを同時に実行すると両方の処理時間が長くなる可能性がある。バキューム処理中にUPDATEおよびDELETEステートメントを実行する場合は、システムのパフォーマンスが低減する場合がある。VACUUM DELETEは、更新操作と削除操作を一時的にブロックする
一度にクラスターで実行できるVACUUMコマンドは1つだけ
- vacuum専用のキューを作るのが簡単か
VACUUMではANALYZEはされない

10. ラッシュパフォーマンス

RedshiftのWLMにおける推奨同時クエリ実行数は15です。
これまでこの点を根拠として大量の参照クエリを受け付けるワークロードには不向きと考えられてきました。
しかしながら最近の機能アップデートにより、大量の参照クエリを処理する仕組みがRedshiftにも備わっています。

10-1. Result Caching

リーダーノード内のメモリにクエリ結果をキャッシュする。受け付けたクエリに対応する結果がキャッシュに含まれている場合、コンピュートノード上での処理を伴わずに結果が返却される
select limit 1000でキャッシュされた結果はlimit 100でも有効
コメントが違うだけのSQLであればキャッシュが使われる
JDBCでPreparedStatementを使用した場合、ログ上は同じSQLのように見えるが、バインド値が同じでないとキャッシュヒットしないし、同じあればヒットする
セッションレベルではenable_result_cache_for_session = offで無効化できる

キャッシュヒットしたクエリの確認

キャッシュヒットしたクエリ

select
    userid
,   query
,   starttime
,   endtime
,   elapsed
,   substring
,   source_query
from
    svl_qlog
where
    source_query is not null
;

キャッシュヒット率の確認

キャッシュヒット率確認

select
    count(1)
,   count(source_query)
from
    svl_qlog q
where
    q.userid = 103
and q.starttime > '2019-04-12 9:07:00'
and q.endtime < '2019-04-12 9:08:00'

10-2. Short Query Acceleration

実行時間が短いと判定された一部のクエリを、実行時間が長いクエリよりも優先する
SQAでは実行時間が短いクエリを専用領域で実行する。SQA用のWLMキューを事前定義する必要はない
SQAは実行時間が短く、ユーザー定義のキュー内にあるクエリのみを優先する。デフォルトキューでは無効
クエリのパターンをSQAが学習するため、時間が経つほど予測精度は向上する
対象クエリはCTASとSELECT(正確には読み取り専用クエリ)

サービスクエリ別クエリ統計。SQAクエリはサービスクラス14を使用する。

サービスクエリ別クエリ統計

select final_state, service_class, count(*), avg(total_exec_time),
percentile_cont(0.9) within group (order by total_queue_time), avg(total_queue_time)
from stl_wlm_query where userid >= 100 group by 1,2 order by 2,1;

SQA によって選択され正常に完了したクエリの特定

SQAによって選択され正常に完了したクエリの特定

select a.queue_start_time, a.total_exec_time, label, trim(querytxt)
from stl_wlm_query a, stl_query b
where a.query = b.query and a.service_class = 14 and a.final_state = 'Completed'
order by b.query desc limit 5;

SQA で選択されたがタイムアウトしたクエリの特定

SQAで選択されたがタイムアウトしたクエリの特定

select a.queue_start_time, a.total_exec_time, label, trim(querytxt)
from stl_wlm_query a, stl_query b
where a.query = b.query and a.service_class = 14 and a.final_state = 'Evicted'
order by b.query desc limit 5;

10-3. 同時実行スケーリング

詳しくはModern Cloud Data Warehousing ft. Intuit: Optimize Analytics Practices (ANT202-R1) - AWS re:Invent 2018
参照系クエリ専用のノードが自動的に起動される
- Concurrency Scaling Modeをautoに設定したキューで滞留が発生すると起動される
  - ただし、短時間(3秒未満くらい)のクエリが滞留しても起動されなかった。仕様なのか不明
  - WLMキューを増やしても起動されなくなった。ある程度さばけていると判定されると起動しないのか
  - 滞留だけでなくスループット等もトリガーにしていると思われるが、不明
- 実測してみると負荷投入後、速ければ数秒遅くとも数十秒程度のラグで起動した
- 滞留しないように必要なだけのクラスタがmax_concurrency_scaling_clustersの範囲内で起動される。クラスタ一つあたりの並列度を指定することはできなかった
24時間毎に1時間分のクレジットが最大30時間まで与えられる
利用できるクエリは以下の制約を満たす必要がある
- Read Only
- interleaved sort keyを設定したテーブルを参照しない
- Redshift Spectrumを参照しない
- テンポラリテーブルを参照しない

メインクラスターと同時実行クラスターとで実行されたクエリの統計

SELECT w.service_class AS queue
     , q.concurrency_scaling_status
     , COUNT( * ) AS queries
     , SUM( q.aborted )  AS aborted
     , SUM( ROUND( total_queue_time::NUMERIC / 1000000,2 ) ) AS queue_secs
     , SUM( ROUND( total_exec_time::NUMERIC / 1000000,2 ) )  AS exec_secs
FROM stl_query q
     JOIN stl_wlm_query w
          USING (userid,query)
WHERE q.userid = 103
AND q.starttime > '2019-04-12 7:20:00'
AND q.endtime < '2019-04-12 7:45:00'
GROUP BY 1,2
ORDER BY 1,2;

11. Redshift Spectrum

11-1. 料金

Redshiftそれ自体と異なり、スキャンされたデータ1TBにつき5USDの課金となります。
クエリ単位で10MB以下のスキャンは切り上げられます。

11-2. 準備

Amazon Redshift Spectrum 用の IAM ポリシーが必要です。

11-3. ファイルフォーマット

多くの形式(PARQUESTかORCが無難.AWSのドキュメントではORCよりもParquestを推奨しているように見受けられます)に対応しています。

AVRO
PARQUET
TEXTFILE
SEQUENCEFILE
RCFILE
RegexSerDe
ORC
Grok
OpenCSV
Ion
JSON

圧縮(PARQUEST/ORCは圧縮込みのフォーマット)は以下に対応しています。

gzip: 普通
Snappy: 高速
bzip2: 高圧縮

ファイルは64MB以上で均等に分割されます。

11-4. パーティション

パーティション表に追加できる数(Amazon Redshift における制限)があります。

テーブルあたりのパーティション数の上限: 1,000,000
アカウントあたりのパーティション数の上限: 10,000,000

参考: Redshift Spectrum 外部テーブルのパーティション化

11-5. 注意

統計情報の設定(TABLE PROPERTIES (numRows'='row_count'))は必須です。設定されていないとプランが崩れます。

11-6. 使いどころ

スキャンや集約インテンシブなワークロードを並行で実行するようなユースケース
- S3に対する検索はユーザーが予約しているリーダーノード・コンピュートノードとは別のリソースが使われるため、Redshift Spectrumだけを利用するのであれば、コンピュートノードには負荷がかかりません
- フィルタ(where)や集約(group by)はRedshift Spectrum層で処理されます
- 結果的に同時実行性能が大きく向上します
結合する際はRedshiftにデータをもった方が有利です。結合する場合はRedshift Spectrumの検索結果がコンピュートノードへ分散される。例えばRedshift Spectrumのファクトを抽出・集約してデータ量を減らしてから、Redshiftのディメンジョンと結合するようなクエリは有効に作用すると期待されます
古いデータはS3に追い出して、ビュー(create view with no schema binding)でRedshiftのテーブルと連結(union all)することで、ビューに対して検索すると、RedshiftのテーブルとRedshift Spectrumの両方を透過的に検索できます
- 上記のような使い方を考えたくなるのですが、別テーブルと結合した際の実行計画がまったく最適化されないので、基本的に結合するような使い方はダメなようなです
  - (A UNION ALL B) JOIN CのようなケースでA JOIN C とB JOIN Cに分解されないため、分散スタイルが有効に使えません。
- フィルタや集約はRedshift Spectrum層で処理されるため、有効です。

Amazon Redshiftの仕様を調べてみた

筆者について

Amazon Redshift

1. ノード構成

1-1. 概要

1-2. 選べるノードタイプ

1-3. リージョンとアベイラビリティ

2. ネットワーク

2-1. クラスターサブネットグループ

2-2. 拡張されたVPCのルーティング(Enhanced VPC Routing)

3. メンテナンス

3-1. リサイズ

3-2. Elastic Resize

3-3. スナップショット

3-4. メンテナンスウィンドウ

4. パラメータグループ

5. ワークロード管理

5.1 スーパーユーザーキュー

5.2 WLM設定

キューの確認方法

5.3 クエリモニタリング

5.4 クエリホッピング

6. ユーザー・グループ

7. 監査ログ

8. テーブル設計

8-1. 実行計画

テーブル結合

クエリの実行

設計方針

8-2. 分散スタイル

8-3. ソートキー

8-4. キー

8-5. データ型

8-6. 圧縮エンコード

9. データのロードとアンロード・バキューム

9-1. データロード

9-2. データアンロード

9-3. VACUUM

10. ラッシュパフォーマンス

10-1. Result Caching

10-2. Short Query Acceleration

10-3. 同時実行スケーリング

11. Redshift Spectrum

11-1. 料金

11-2. 準備

11-3. ファイルフォーマット

11-4. パーティション

11-5. 注意

11-6. 使いどころ

12. 参考資料

12-1. AWS公式

12-2. 他社事例

12-3. 要素技術