Redshiftクラスタに10TBのデータを入れてみる

# はじめに

- Redshiftのra3系ノードに10TBのテストデータを入れた結果をまとめます。
    - あまりこういう検証データが世の中になかったので。
        - かつ、検証で数百ドルを消費したことの所属会社への贖罪も兼ねて。

# 環境情報

VPC内に立てたクラスタ系Redshift（サーバレスではないほう）で以下の2パターンを検証しました。

- トライ1
    - **ra3.xlplusの1台**構成
        - 1台構成の場合、最大で**4TB**までストレージを利用可能。
        - 1台でノードを作成すると、リーダーノードとコンピュートノードが1台に同居する形で、合計1台になります。
- トライ2
    - **ra3.xlplusの2台**構成
        - 2台構成の場合、最大で**64TB**までストレージを利用可能。(詳しくは[こちら](https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html#rs-ra3-node-types)
        - 2台でノードを作成すると、リーダーノード1台(無料) + コンピュートノード2台で、合計3台になります。

- 当然、ra3.xlplusの1台構成(4TB上限)で10TBのデータを入れようとしてもストレージに入り切りません。そのあたりもどのような挙動になるか検証してみました。
 - また、ra3系クラスタは従来のdc2系クラスタとはストレージ周りのアーキテクチャが大きく異なります。dc2系だと大きく異なる結果が出るかもしれませんのでご注意ください。

- その他環境情報
    - リージョン：東京リージョン
    - クラスタのパラメータ
        - デフォルト(自動 WLM)

## 

## テストデータ概要

- 今回利用するテストデータはTPC-DSをベースにクラウドデータウェアハウス用にカスタマイズしたAWS公式データです。バージョンは 2.13 です。
    - https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCDS



- TCP-DSとは
    - データ分析基盤向けのスノーフレークスキーマで構成されたベンチマーク用データセットです。
    - TCP-DSについては以下がわかりやすいかと思います。
        - https://qiita.com/manabian/items/93d9330cdb607383c2b9
        - https://www.slideshare.net/kaigai/tpcdspostgresql/5
    - 公式情報は以下です。ER図も載っています。
        - https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.1.0.pdf
    - BigQueryでTCP-DSのベンチマークを行った結果なども[ここ](https://notebook.community/GoogleCloudPlatform/training-data-analyst/quests/bq-optimize/01_baseline_performance/solution/explore_and_benchmark
)にありました。
        - データサイズは今回の私の検証より小さい2TBになっています。
        - BigQueryで利用しているデータもBigQuery用にカスタマイズされたTPC-DSであるため、Redshift用の検証と似てはいるものの比較はできません。
        - ただcastや結合、一部クエリの差はあれど、AWSのものと大きな差はないように見えました。(sqlは[こちら](https://github.com/GoogleCloudPlatform/training-data-analyst/tree/master/quests/bq-optimize/01_baseline_performance/solution/sql))

- 今回使用したデータ
    - 今回はAWSが提供するデータセットのうち、10TBのものを選び、以下のDDL文を実行しテーブルを作成しました。
        - https://github.com/awslabs/amazon-redshift-utils/blob/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCDS/10TB/ddl.sql
    - テストデータのソースはUS-EAST-1のS3にあります。
        - 今回のように東京リージョンのクラスタで試す場合は、リージョン間通信の時間的オーバーヘッドと、転送コストがかかります。
        - 直接データを確認する場合は、AWSマネジメントコンソールログイン後、[こちら](https://s3.console.aws.amazon.com/s3/buckets/redshift-downloads?prefix=TPC-DS/2.13/10TB/&region=us-east-1)のページを確認ください。

## テストデータ詳細

### テーブルの行数、特徴

- 今回のデータセット(10TB)のテーブル名、行数、Redshiftのテーブルパラメータ(key設定)、スキーマ分類を下表にしました。
    - ※スキーマ分類は、TCP-DS公式のPDFに基づいて記載しています。

| テーブル名 | 行数 |key設定|スキーマ分類※|
|:-----------|-----------------:|:------------:|:------------:|
|call_center|54|diststyle all|Dimension|
|catalog_page|40,000|diststyle all|Dimension|
|catalog_returns|1,440,033,112|distkey(cr_item_sk) sortkey(cr_returned_date_sk)|Fact|
|catalog_sales|14,399,964,710|distkey(cs_item_sk) sortkey(cs_sold_date_sk)|Fact|
|customer|65,000,000|distkey(c_customer_sk)|Dimension|
|customer_address|32,500,000|distkey(ca_address_sk)|Dimension|
|customer_demographics|1,920,800|distkey (cd_demo_sk)|Dimension|
|date_dim|73,049|diststyle all|Dimension|
|household_demographics|7,200|diststyle all|Dimension|
|income_band|20|diststyle all|Dimension|
|inventory|1,311,525,000|distkey(inv_item_sk) sortkey(inv_date_sk)|Fact|
|item|402,000|distkey(i_item_sk) sortkey(i_category)|Dimension|
|promotion|2,000|diststyle all|Dimension|
|reason|70|diststyle all|Dimension|
|ship_mode|20|diststyle all|Dimension|
|store|1,500|diststyle all|Dimension|
|store_returns|2,879,356,097|distkey(sr_item_sk) sortkey(sr_returned_date_sk)|Fact|
|store_sales|28,801,286,459|distkey(ss_item_sk) sortkey(ss_sold_date_sk)|Fact|
|time_dim|86,400|diststyle all|Dimension|
|warehouse|25|diststyle all|Dimension|
|web_page|4,002|diststyle all|Dimension|
|web_returns|720,020,485|distkey(wr_order_number) sortkey(wr_returned_date_sk)|Fact|
|web_sales|7,199,963,324|distkey(ws_order_number) sortkey(ws_sold_date_sk)|Fact|
|web_site|78|diststyle all|Dimension|


- テーブルの特徴
    - 最大で288億レコードのテーブル(store_sales)があります。
    - すべてのテーブルにRedshiftの明示的な[分散スタイル](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_choosing_dist_sort.html)が設定されています。（つまりデフォルトのAUTO設定ではない）
        - 行数が比較的少なめなディメンションテーブルはRedshiftの分散スタイルが「ALL分散(diststyle all)」となっており、全てのRedshiftノードに同じデータが配置される設計になっています。
        - 一方、行数が多めなファクトテーブルは分散スタイルが「キー分散(distkey)」となっており、JOINで使われるサロゲートキーなどがキーとして指定されています。
    - また、ソートキーもwhere句で使われがちなdate系のサロゲートキーなどに設定されています。
    - 上記のとおり、Redshiftのベストプラクティスに沿った設計になっています。


### データの概要

- 小売販売のデータです。
    - 3つの販売チャネルを持っています。
        - 店舗(store)
        - カタログ(catalog)
        - Web(web)
    - それぞれの販売チャネルで販売(sales)、返品(returns)のデータを持っています。

### スキーマ構造

- 販売履歴や、返品履歴を中心とした[スノーフレークスキーマ](https://www.databricks.com/jp/glossary/snowflake-schema)構造となっています。
-  スノーフレークスキーマなので[正規化](https://it-biz.online/it-skills/normalization/)がかなり行われています。
    -  例えばDateだけを持つディメンジョンテーブル(Date_Dim)、Timeだけを持つディメンジョンテーブル(Time_Dim)などが独立しています。それぞれDate_Dimの場合1行が1日、Time_Dimの場合1行が1秒で構成されています。
        -  つまり分析用のETL処理が行われた後のスキーマ構造となっています。ここまで正規化されたテーブルは現実には分析用のデータマートとしても見かけることは少ないのではないかと思います。
    -  それぞれのテーブルは[サロゲートキー](https://amg-solution.jp/blog/8980)で結合されています。特にファクトテーブルの多くは半分程度のカラムがサロゲートキーで構成されています。データモデリングとしてサロゲートキーを徹底すべきなのかという観点については[ここ](https://blog.msyk.net/?p=1514)とか[ここ](https://www.praha-inc.com/lab/posts/database-normalization)が個人的に参考になりました。
- ER図は本記事末尾のAppendixに転記してあります。


# 結果

## COPY完了時間

- 留意事項
    - 元データはUS-EAST-1のS3上にあります。東京リージョンへの転送オーバーヘッドがわずかに発生します。
    - 元データはgzファイル形式で細分化されています。大きいテーブルだと約100MBのgzファイルが複数存在します。効率よく読み込めるサイズになっていると思われます。そのため、1ファイルがもっと巨大なケースとCOPY速度を比較する際は注意してください。
        - [公式ブログ](https://aws.amazon.com/jp/blogs/news/top-8-best-practices-for-high-performance-etl-processing-using-amazon-redshift/)に高速化のコツが記載されています。
            - > 1つのテーブルに複数のファイルをロードする場合は、複数のCOPYコマンドではなく、テーブルに対して1つのCOPYコマンドを使用します。 Amazon Redshiftはデータの取り込みを自動的に並列化します。 1つのCOPYコマンドを使用してデータをテーブルにバルクロードすると、クラスタリソースの最適な使用と可能な限り高いスループットが可能となります。

    
### 結果

- ra3.xlplusの 1台構成と2台構成でそれぞれCOPY時間を計測しました。
- 所要時間が1時間以上かかったものを太字にしています。
- 記載の時間は目安であり、特に長時間のものはわかりやすいように丸めています。
- テーブルサイズはデータ投入後、システムテーブルの「svv_diskusage」から取得したものです。
  - おそらく列圧縮がかかった状態のサイズと思われます。
- テーブル毎に上からCOPY文を順次実行しています。「N/A」は、ストレージが逼迫したためCOPYを実施していないことを示しています。

| テーブル名 | 行数 |テーブルサイズ(MB)| ra3.xlplus x 1 | ra3.xlplus x 2|
|:-----------|------------:|------------:|------------:|------------:|
|call_center|54|68|3 seconds|3 seconds|
|catalog_page|40,000|26|4 seconds|3 seconds|
|catalog_returns|1,440,033,112|101,290|**4 hours**|**2 hours**|
|catalog_sales|14,399,964,710|1,231,329|**Error (29 hours)**|**13 hours**|
|customer|65,000,000|4,280|4 minutes|2 minutes|
|customer_address|32,500,000|1,508|1 minutes|41 seconds|
|customer_demographics|1,920,800|60|8 seconds|8 seconds|
|date_dim|73,049|62|6 seconds|4 seconds|
|household_demographics|7,200|16|3 seconds|2 seconds|
|income_band|20|12|3 seconds|2 seconds|
|inventory|1,311,525,000|18,828|23 minutes|8 minutes|
|item|402,000|244|12 seconds|7 seconds|
|promotion|2,000|44|5 seconds|3 seconds|
|reason|70|12|4 seconds|2 seconds|
|ship_mode|20|18|3 seconds|2 seconds|
|store|1,500|64|5 seconds|3 seconds|
|store_returns|2,879,356,097|160,714|N/A|**3 hours**|
|store_sales|28,801,286,459|1,829,366|N/A|**22 hours**|
|time_dim|86,400|26|N/A|3 seconds|
|warehouse|25|34|N/A|2 seconds|
|web_page|4,002|34|N/A|2 seconds|
|web_returns|720,020,485|48,137|N/A|**1 hours**|
|web_sales|7,199,963,324|619,858|N/A|**6 hours**|
|web_site|78|58|N/A|2 seconds|

- 所感
    - 今回の結果をみる限り、2台構成の場合はCOPY文も早くなることがわかります。
        - 特に大きいテーブルでは分散の効果が顕著であり、完了時間が2倍以上早くなっています。
            - catalog_returnsテーブル、inventoryテーブル等
    - COPY完了時間は行数よりも、データサイズのほうが相関が強いようです。
        - 行数が近しい catalog_returns テーブルと、inventory テーブルを比較。
        - ここでは投入後のテーブルサイズで比較しているため、あくまで参考値となります。元データの合計サイズを測れば、もう少し正確な相関度がわかるかと思います。

- データ投入後のRedshift使用ストレージ
  - 2台構成で、10TB全てのデータのCOPYが正常に完了したのちマネジメントコンソールを確認すると、以下のように約 4TBと表記されています。これは上記表に記載した「テーブルサイズ」の合計値とおおよそ合致します。
      - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/86f833b5-a03a-2ada-b527-b3f04c6c2d9a.png)


## ディスクフルとなるまでの過程

### 結果と推移

- ra3.xlplus x 1の構成では、catalog_returnsテーブル(約144億レコード)のCOPY実行後、約29時間後にSQL文が失敗しています。
    - catalog_returnsテーブルは、正常にRedshiftに投入できた場合、Redshift上で約1.2TB(圧縮後)のデータ量を持つテーブルです。それまでCOPYした分を合計しても1.3TB程度ですので、今回の構成におけるRedshiftのストレージ上限の4TBより下回っています。
    - つまりデータ圧縮はCOPYを実施しながら行われているのではなく、データがRedshiftに入った後に行われるので、元データの合計が4TBを越えているものをCOPYするとエラーになると推測されます。
        - このあたりは裏取りしたわけではありません。特にこういったCOPY処理時に、以下の点をもう少し調べたいと思っています。
            - RMSだけでなく、コンピュートノードのローカルSSDキャッシュが使われているのかどうか
            - 分散スタイルによるRMS/ローカルSSDキャッシュ利用への影響

- COPYクエリ単位での負荷とストレージ容量グラフは以下の通りです。
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/0e7e850b-45bd-ddf1-32ab-d095bd65316e.png)
    - [使用されたストレージ容量]
        - 右端がディスクフルとなった時間です。上端の100%は、ra3の1台構成のため4TBを示しています。
        - グラフの真ん中あたりで、メトリクスが乱高下している箇所があります。推測ですが内部で自動的に[圧縮エンコード](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_Compression_encodings.html)が発動したのではないかと思われます。
            - 圧縮エンコードはcreate tables時に[各カラムにたいして指定](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/Examples__compression_encodings_in_CREATE_TABLE_statements.html)することができますが、今回のテーブルは圧縮エンコードを指定していないため、デフォルトの"ENCODE AUTO"に設定されています。
            - 圧縮エンコード後のサイズについては[こちら](https://dev.classmethod.jp/articles/redshift-compression-optimize-try/)の記事も参考になります。
    - [CPU使用率]
        - 圧縮エンコードが発動したと思われるタイミングでCPU使用率のスパイクが発生しています。

- COPY文でディスクフルが発生すると、COPY文がロールバックされます。つまりそのCOPY文で投入しようとしていたデータはすべてRedshiftから削除されます。
    - ストレージ使用率のグラフもロールバックによりメトリクスが急激に下がりますので、過去の時間帯に発生したディスクフルを見逃してしまうことも考えられます。長期間のスケールでストレージ容量のグラフを確認する癖をつけておいたほうがよいと思います。

### ディスクフルの判断方法
- ディスクフルの判断方法について
    - SQLクライアントでエラー発生時までセッションが残っている場合は、SQLクエリの標準エラーとして「ERROR: Disk Full」という文字列が表示されるようです。
        - 私の場合はAWSマネジメントコンソールのRedshift クエリエディタ v2で実行していたため、SQL実行中にマネジメントコンソール自体のセッションが切れてしまい、標準エラー文字列を確認できませんでした。
    - 後から判断する場合は、Redshiftのシステムテーブル[STL_DISK_FULL_DIAG]を見ることで、ディスクフルを引き起こしたクエリのIDを確認することができます。
```sql
select '2000-01-01'::timestamp + (currenttime/1000000.0)* interval '1 second' as currenttime,node_num,query_id,temp_blocks from pg_catalog.stl_disk_full_diag;
```
上記SQLの出力例は以下の通り。
```
| currenttime                | node_num | query_id | temp_blocks |
|----------------------------|----------|----------|-------------|
| 2023-07-14 11:31:14.603769 | 0        | 6803581  | 2857137     |
```

- 上記のSQL文や、ディスクフルとなった場合の対策などは公式の以下ページに詳しく記載されています。
    - https://repost.aws/ja/knowledge-center/redshift-high-disk-usage

# まとめ

 - COPYはノード数を増やすと早くなる。
 - COPYによるディスクフルは気づきにくいため、こまめなグラフ確認やSQL文での裏取りを心がける

# Appendix. 

## TCP-DSのER図(公式PDFより)
https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.1.0.pdf

- 店舗販売のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/6342da89-2a17-6bba-4400-d98987846890.png)
        - [店舗販売]は[Promotion(販促)]テーブルと連結されているのが特徴かと思います。

- 店舗返品のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/b864456d-7b4e-7101-6372-bf11c94913df.png)
        - 店舗販売と異なり店舗返品は、[Reason(返却理由)]テーブルと連結されているのが特徴かと思います。

- カタログ販売のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/d6ddfe79-4a3d-6f77-8f13-b2b527425ca5.png)
        - 店舗と異なりカタログは、[Ship_Mode][Call_Center][Warehouse]テーブルと連結されているのが特徴かと思います。

- カタログ返品のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/18cf0658-2645-e0d5-2232-1c63d1d9ff36.png)

- Web販売のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/28c05391-306a-5722-07b1-16d719e7762c.png)
        - 店舗やカタログと異なりWebは、[Web_Page]テーブルと連結されているのが特徴かと思います。

- Web返品のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/40d5df64-fc65-a824-85d9-7c1d5bec1f46.png)

- 在庫のER図
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/e99cb4fe-7554-02d2-23e4-efd356a94689.png)

## 2台構成におけるCOPY文のメトリクス推移

参考までに、載せておきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/8d8078f7-9670-18a7-0fcf-a5a40c8da11a.png)


以上です。