AWSが提供するDWHサービス、Amazon Redshift。
全世界での採用企業は数万社を超えており、弊社も国内において多くのお客様に導入のご支援をさせて頂きました。
RedshiftはAWSエコシステムとの親和性が高く、AWSを既にご利用のお客様は導入の敷居が低いDWHサービスとなっております。
しかし、適切なテーブル設計を行わなければパフォーマンスを全く発揮できません。
不適切なテーブル設計をしてしまったが故、「バッチ処理が当初想定していた時間で終わらない」等、弊社にご相談頂いたお客様も数多くいらっしゃいます。
では、Redshiftを扱うにあたってどのようなテーブル設計を行えば良いのか。
本記事では、パフォーマンスの向上に繋がるテーブル設計のポイントを3つ、ご紹介致します。
ソートキー（SortKey）は、テーブルのデータを特定の列(カラム)でソートする基準になるキーです。
ソートキーを適切に指定することによって、WHERE/GROUP BY/ORDER BYや結合を含むクエリの実行速度を大幅に短縮できます。
ソートキー指定のポイントは主に下記3点が挙げられます。
①最新のデータが最も頻繁にクエリ処理される場合は、TIMESTAMP型のカラムをソートキーの主要カラムとして指定。
②1 つのカラムに対して範囲フィルタリングまたは等価性フィルタリングを頻繁に実行する場合は、そのカラムをソートキーとして指定。
③テーブルを頻繁に結合する場合は、結合カラムをソートキーと分散キー（※後述）の両方を指定。
また、ソートキーは下記のように3種類存在します。※2020年8月現在
最もシンプルなソートキーです。
１つのカラムのみをソートキーとして指定します。
複合ソートキーは、複数のカラムを指定したソートキーです。
GROUP BYやORDER BYで使用される順番と同様に複合ソートキーを指定することで、パフォーマンス向上に役立ちます。
注意点としては、2番目以降に指定されたカラムは、1番目に指定したカラムと組み合わせたクエリでなければパフォーマンスを発揮できない点が挙げられます。
つまり、下記の記入例でテーブル定義した場合、GROUP BYやORDER BYの対象に[id]カラムを単独指定 or [id]カラムと[birthday]カラムを組み合わせて指定する場合はパフォーマンス向上に繋がるのですが、[birthday]カラムを単独指定した場合、殆どパフォーマンス向上には繋がりません。
インターリーブソートキーは、複合ソートキー同様、複数のカラムを指定したソートキーです。
複合ソートキーとの最大の相違点は、指定したカラムをそれぞれ単独で使用してもパフォーマンス向上に繋がる、といった点です。
つまり、下記記入例でテーブル定義した場合、GROUP BYやORDER BYの対象に[id]カラムを単独指定しても、[birthday]カラムを単独指定しても、組み合わせてもパフォーマンス向上に繋がります。
では複合ソートキーではなくインターリーブソートキーを常に選択したほうがいいのではないかと思われるかもしれませんが、そうとも限りません。
インターリーブソートキーの性能を維持するためには定期的にVACUUM REINDEXを実行する必要があるためです。
VACUUM REINDEXは実行コストが非常に高いため、スケジューリングを厳密に行う必要があります。
そのため、無闇矢鱈にインターリーブソートキーを指定すると全体の処理時間に影響を与えかねないため、基本的には複合ソートキーを選択する方が安全です。
データ量が少量で更新頻度が少ない（≒VACUUM REINDEXの実行コストが低め）かつ結合パターンが複数あるテーブル（≒ディメンションテーブル）等はインターリーブソートキーが選択肢に挙がるでしょう。
テーブルにデータをロードするとき、Redshiftはそのテーブルで定義されている分散スタイル(DISTSTYLE)に従って、テーブルの行を各ノードスライスに分散します。
スライスの数は、ノード上のプロセッサコアの数と同じになります。
例）Redshiftのスペック…ノードタイプ：dc2.large(2コア), ノード数：4 の場合
2コア × 4ノード = 8スライス
分散スタイルは下記のように4種類存在します。※2020年8月現在
デフォルトの分散スタイルです。分散スタイルを指定しないでDDLを発行した場合、AUTO分散が適用されます。
AUTO分散を指定すると、Redshiftはテーブルのデータ量に基づいて最適な分散スタイルを自動的に割り当てます。
例えば、データ量が少量のときにはALL分散が割り当てられ、その後データ量が増大したタイミングで、EVEN分散に変更されます。
EVEN分散は、特定のカラムの値に含まれている値にかかわらず、ラウンドロビン方式によって複数のスライス間で行を均等に分散させます。
テーブルが結合に関与していない場合や、KEY分散と ALL分散のどちらを選択すべきかが明確でない場合に適切な分散スタイルです。
ALL分散は、テーブル全体のコピーを全ノードに分散させます。
EVEN分散または KEY分散によってテーブルの一部の行のみが各ノードに配置されている場合、ALL分散を行うと、テーブルが関与しているあらゆる結合で全行が確実に併置(コロケーション)されるようになります。
注意点としては、クラスタ内のノードの数だけ必要なストレージが増えるため、データをロードまたは更新したり、複数のテーブルに挿入したりするのに時間が掛かる点が挙げられます。
よって、①更新頻度が低く、②更新範囲が狭く、③データ量が少量で、④別テーブルと頻繁に結合されるテーブル（≒ディメンションテーブル）に適切な分散スタイルです。
KEY分散は、分散キー(DISTKEY)に指定されたカラムに含まれている値に従って行の分散を行います。
複数の一致する値が同じノードスライスに配置されるため、結合キーに基づいてテーブルのペアを分散する場合、結合するカラムに含まれている値に従って行をスライスに併置(コロケーション)します。
これによって、共通のカラムで一致する値が物理的にまとめて格納されるようになります。
分散キーに指定できるのは1つのカラムのみです。
分散キーは、①値に偏りがなく、②値の種類が多い（=カーディナリティが高い）カラムが適しています。
Redshiftではカラム毎に圧縮エンコードを選択できます。
テーブル作成時に最適な圧縮エンコードを選択することよって、ストレージのスペースが節約され、ストレージからロードするデータ量が小さくなり、ディスクI/Oの量が減少するため、クエリパフォーマンスが向上します。
圧縮エンコードには下記のような種類が存在します。※2020年8月現在
| 圧縮エンコードタイプ | テーブル作成時のキーワード | 対応するデータ型 |
|---|---|---|
| raw (非圧縮) | RAW | 全て |
| AZ64 | AZ64 | SMALLINT、INTEGER、BIGINT、DECIMAL、DATE、TIMESTAMP、TIMESTAMPTZ |
| バイトディクショナリ | BYTEDICT | SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、CHAR、VARCHAR、DATE、TIMESTAMP、TIMESTAMPTZ |
| デルタ |
DELTA
|
SMALLINT、INT、BIGINT、DATE、TIMESTAMP、DECIMAL INT、BIGINT、DATE、TIMESTAMP、DECIMAL |
| LZO | LZO | SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、CHAR、VARCHAR、DATE、TIMESTAMP、TIMESTAMPTZ |
| Mostlyn |
MOSTLY8
MOSTLY32 |
SMALLINT、INT、BIGINT、DECIMAL INT、BIGINT、DECIMAL BIGINT、DECIMAL |
| ランレングス | RUNLENGTH | SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、CHAR、VARCHAR、DATE、TIMESTAMP、TIMESTAMPTZ |
| テキスト |
TEXT255 TEXT32K |
VARCHAR のみ VARCHAR のみ |
| Zstandard | ZSTD | SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、CHAR、VARCHAR、DATE、TIMESTAMP、TIMESTAMPTZ |
また、データを元に最適な圧縮エンコードをRedshiftに推定させることも出来ます。
圧縮エンコードを推定する方法は、テーブル作成＆データ挿入後に手動圧縮推定する方法と、COPYコマンド実行時に自動圧縮推定する方法の2つが存在します。
ちなみに、テーブル作成後にALTER TABLEで圧縮エンコードを変更することはできません。
変更したい場合はテーブルを再作成する必要があります。
手動圧縮する際の基本的な手順は下記となります。
①テーブルに一定量のデータを挿入する
テーブルに一定量のデータが入っていると、Redshiftは後述のANALYZE COMPRESSIONコマンドで正確に圧縮エンコードを推定することができます。
圧縮エンコード推定用の一時テーブルを作成してデータを挿入することを推奨します。（圧縮エンコードを指定してテーブルを再作成することになるため）
②ANALYZE COMPRESSIONコマンドで最適な圧縮エンコードを推定する
③推定結果を元に圧縮エンコードを指定してテーブルを作成する
自動圧縮推定はCOPYコマンド実行時、つまりAmazon S3やAmazon DynamoDBからロードする際に使用可能です。
詳細は公式ドキュメント 自動圧縮ありでテーブルをロードする を参考にして下さい。
以上のように、Redshiftのテーブル設計時には最適な ①ソートキー、②分散スタイル、③圧縮エンコード を選定することで、最大限のパフォーマンスを発揮させることができます。
さて、ここまでお読み頂いて、どのように感じられましたでしょうか。
「Redshiftのテーブル設計は難しい」「Redshiftは玄人向けではないか」と感じられた方も多くいらっしゃると思います。
弊社では、Redshiftほどテーブル設計を意識しなくとも、Redshift以上のパフォーマンスを発揮できるDWHサービス、Snowflakeを取り扱っております。
ご興味のある方はぜひお気軽にお問い合わせ下さい。
Amazon Redshiftよりコスパ良くパフォーマンスの高いDWH「Snowflake」の概要資料はこちら