Datastageで、Snowflakeの巨大データを処理してみた #2

# この記事について
- この記事は[IBMが提供する製品に関する情報やナレッジを共有する Advent Calendar 2024](https://qiita.com/advent-calendar/2024/ibm_data_ai_automation)の12月20日分として記載しています

# 背景
- [こちらの記事](https://qiita.com/Shumpei_Kubo/items/53a2adbed0716e5c529a)で、「SF10」「SF100」スキーマのデータは処理できましたが、「SF1000」については途中でエラーとなってしまいました
- やはり超巨大データを扱う場合に、データをひっぱってきてJOINして、最後にフィルターをかけるという処理自体、メモリ上に超巨大データを保持しながら、というところが無理あるかと思い、各データから引っ張ってきた直後に列の削除やフィルタリングを掛けて、その後でJOINするという処理に変えました
- 各ステージで、後続に渡すデータ量を必要最小限にするようにしていく感じに変えました
- 今回は、一番大きな「SF1000」を処理した後、そのフローを使ってデータソースをそれぞれ「SF100」「SF10」「SF1」と入れ替えて実施して処理時間を比べてみます
- また、データソース側で処理（Transform）してからExtractするというTETL方式でも試してみました。


# 実施概要

- 対象データベースはSnowflake（Azure/東京）の「SNOWFLAKE_SAMPLE_DATA」を使用します
- 「SF1000」「SF100」「SF10」「SF1」各スキーマから、「REGION」「NATION」「CUSTOMER」「ORDERS」テーブルを引っこ抜いて余計な列を削除してフィルタリングしてからJOINするフローをDatastageに実行させます
- どの程度のデータ量で、どの程度の処理時間がかかるのかを測ってみます
- Datastage は、IBMCloudの東京リージョン上のOpenshiftに建てたCloud Pak for Data v5.0.3 上のコンテナソフトウェアを使用しています



# スキーマ「SF1000」の場合

## 処理対象

- スキーマ「SF1000」
    - 「REGION」:5行、4.0KB
    - 「NATION」:25行、4.0KB
    - 「CUSTOMER」:150M（1億5千万）行、10.1GB
    - 「ORDERS」:1.5B（15億）行、48.6GB



- 最もレコード件数が多いテーブルが「ORDERS」
    - 件数：1.5B（15億）行
    - データサイズ：48.6GB
![スクリーンショット 2024-12-12 15.45.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/831e355d-c6e4-6acc-486a-2460f1d35381.png)


## Snowflake側の設定

- XL サイズを使用しました
![スクリーンショット 2024-12-20 9.53.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/4d6bd65c-44c0-d812-00bd-f99ea75de0f1.png)


## Datastage側の設定
- インスタンスとしてLサイズを使用し、パーティションを３に設定しました
- インスタンス設定
![スクリーンショット 2024-12-20 10.10.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/8f11ddcf-28c7-b53c-cf8e-99abdff78d84.png)
![スクリーンショット 2024-12-20 10.10.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/463f0a4d-0dd9-bf33-f150-774764a90721.png)

- プロジェクトにおける実行環境設定
![スクリーンショット 2024-12-20 10.12.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/cf616377-0214-4b15-bef9-3f9bca0c6bf9.png)


- エフェメラルストレージを1000GB程度まで拡張
- （社内の有識者からもらったアドバイス。ただしあくまでテスト用の一時的な処理であり、本番ではストレージを別途準備するように、とのこと）
```
[root@localhost ~]# oc -n ${PROJECT_CPD_INST_OPERANDS} patch pxruntime ds-px-xlarge --patch '{"spec":{"ephemeralStorageLimit": "1000Gi"}}' --type=merge
pxruntime.ds.cpd.ibm.com/ds-px-xlarge patched
[root@localhost ~]# 
```


## Snowflake の結果
- 何回か実施していて、ひょっとしてキャッシュに入ったのか、どのサイズのテーブルも1秒かかっていませんでした
![スクリーンショット 2024-12-20 10.04.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/e23045ec-1e10-c027-9d97-4caf3f79b4ab.png)


## Datastage の結果

### フロー
- 正常終了しました

![スクリーンショット 2024-12-20 7.06.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/f8696222-18ad-96d0-26b8-1db8cd543f21.png)


### ジョブ（時間）
- 約６時間かかりました
- やはり最もサイズが大きい（48GB/15億行）のORDERSテーブルの処理に一番時間がかかっています

![スクリーンショット 2024-12-20 7.07.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/2ef24e02-a64d-99d7-2a16-a61af198f2ec.png)


## TETL 設定の場合
- 上記の設定のまま、データをデータソース側で処理（Transform）してExtract、一部をTransformした上でターゲットに対してLoadするTETLという方法でも実施してみました。（画面左上に「TETL」の表記有り）
- なお、ELT、またはTETLで処理する場合、Datastageはその実行エンジンとして、[内部的にdbtを使っています。](https://dataplatform.cloud.ibm.com/docs/content/dstage/dsnav/topics/materialization_policies.html?pos=2&locale=ja&context=cpdaas)

![スクリーンショット 2024-12-25 16.17.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/b671cdce-1ae8-dd7f-7da9-d903fc0c3f1e.png)


- こちらも問題なく実行完了しました。

![スクリーンショット 2024-12-25 16.14.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/73df3a7e-63a6-faea-a676-a294a850929b.png)

- 処理時間は5時間40分程度で、通常のETL処理と比較して20分程度短縮されています。
![スクリーンショット 2024-12-25 16.15.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/a4719145-9200-9195-df38-45a2d3ba6cdb.png)



# スキーマ「SF100」の場合

- 上記SF1000 のフローをコピーして、データソースだけスキーマSF100 のものに変えて実行してみました
![スクリーンショット 2024-12-20 10.54.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/a5a3c977-f288-ebe5-0d59-30743cd27890.png)


## 処理対象

- スキーマ「SF100」
    - 「REGION」:5行、4.0KB
    - 「NATION」:25行、4.0KB
    - 「CUSTOMER」:15M（1500万）行、1.0GB
    - 「ORDERS」:150M（1億5千万）行、4.3GB


- 最もレコード件数が多いテーブルが「ORDERS」
    - 件数：1億5千万行
    - データサイズ：4.3GB
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/acaa524c-83b3-ae58-dd8d-919b3087aa43.png)
---


## フロー
- 正常終了しました
![スクリーンショット 2024-12-20 12.05.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/80675f6e-a657-d804-c26c-0a5de62958b0.png)

## ジョブ（時間）

- 30分程度で終了しました
![スクリーンショット 2024-12-20 12.06.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/d8e3a0ce-174f-172b-a0e9-b04626a5430b.png)




## TETL 設定の場合
- 上記の設定のまま、データをデータソース側で処理（Transform）してExtract、一部をTransformした上でターゲットに対してLoadするTETLという方法でも実施してみました。（画面左上に「TETL」の表記有り）
- なお、ELT、またはTETLで処理する場合、Datastageはその実行エンジンとして、[内部的にdbtを使っています。](https://dataplatform.cloud.ibm.com/docs/content/dstage/dsnav/topics/materialization_policies.html?pos=2&locale=ja&context=cpdaas)


![スクリーンショット 2024-12-25 18.23.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/dbb147f5-4341-1323-8ec3-e82d6bd56bf0.png)


- こちらも問題なく実行完了しました。


![スクリーンショット 2024-12-25 18.24.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/21249656-0c41-d207-fafb-eceb130d428c.png)


- 処理時間は32分で、通常のETL処理と比較してあまり差はみられませんでした。
![スクリーンショット 2024-12-25 18.24.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/87951057-d338-8c67-04a9-1c5251c8e02c.png)



# スキーマ「SF10」の場合

- 上記SF1000 のフローをコピーして、データソースだけスキーマSF10 のものに変えて実行してみました

![スクリーンショット 2024-12-20 17.04.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/05e49f46-8238-b5a7-3c48-3458a30c7393.png)

## 処理対象


- スキーマ「SF10」
    - 「REGION」:5行、4.0KB
    - 「NATION」:25行、4.0KB
    - 「CUSTOMER」:1.5M（150万）行、103.2MB
    - 「ORDERS」:15M（1500万）行、425.8MB


- 最もレコード件数が多いテーブルが「ORDERS」
    - 件数：1千500万行
    - データサイズ：425.8MB
![スクリーンショット 2024-12-12 15.36.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/86462384-1888-7959-99ce-55354b9d235b.png)




## フロー
- 正常終了しました

![スクリーンショット 2024-12-20 17.16.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/3506af01-f2cc-9c71-894f-fb4bad948fd2.png)



## ジョブ（時間）
- 4分ちょっとで終了しました
![スクリーンショット 2024-12-20 17.17.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/f6fe9671-12c0-20d3-4916-89b3370ad11e.png)






## TETL 設定の場合
- 上記の設定のまま、データをデータソース側で処理（Transform）してExtract、一部をTransformした上でターゲットに対してLoadするTETLという方法でも実施してみました。

- なお、ELT、またはTETLで処理する場合、Datastageはその実行エンジンとして、[内部的にdbtを使っています。](https://dataplatform.cloud.ibm.com/docs/content/dstage/dsnav/topics/materialization_policies.html?pos=2&locale=ja&context=cpdaas)


![スクリーンショット 2024-12-25 18.27.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/384c43db-f184-3ab6-d291-92fc1e88b391.png)


- こちらも問題なく実行完了しました。（画面左上に「TETL」の表記有り）
![スクリーンショット 2024-12-25 18.27.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/04ff4f10-fa65-8851-218a-6e99f79f77e1.png)


- 処理時間は4分ちょっとで、通常のETL処理と比較してあまり差はみられませんでした。

![スクリーンショット 2024-12-25 18.28.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/053365d9-9ee5-9ff7-2f18-948289fe5db5.png)





# スキーマ「SF1」の場合

- 上記SF1000 のフローをコピーして、データソースだけスキーマSF1 のものに変えて実行してみました

![スクリーンショット 2024-12-20 17.09.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/66152a1a-cd31-8601-854b-6b2a8ca8f3bd.png)

## 処理対象

- スキーマ「SF1」
    - 「REGION」:5行、4.0KB
    - 「NATION」:25行、4.0KB
    - 「CUSTOMER」:150K（15万）行、10.3MB
    - 「ORDERS」:1.5M（150万）行、40.3MB
![スクリーンショット 2024-12-20 17.19.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/56f190b8-140d-53e8-c058-3206e4465a53.png)


## フロー
- 正常終了しました

![スクリーンショット 2024-12-20 17.25.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/eb0127a7-a00e-2045-add2-1734978ff54d.png)


## ジョブ（時間）
- 1分足らずで終了しました

![スクリーンショット 2024-12-20 17.26.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/ee550205-24cf-76fc-122b-862746f1ec0e.png)



## TETL 設定の場合
- 上記の設定のまま、データをデータソース側で処理（Transform）してExtract、一部をTransformした上でターゲットに対してLoadするTETLという方法でも実施してみました。

- なお、ELT、またはTETLで処理する場合、Datastageはその実行エンジンとして、[内部的にdbtを使っています。](https://dataplatform.cloud.ibm.com/docs/content/dstage/dsnav/topics/materialization_policies.html?pos=2&locale=ja&context=cpdaas)

![スクリーンショット 2024-12-25 18.30.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/e9044a07-10b3-3821-d844-84646fd8084e.png)



- こちらも問題なく実行完了しました。（画面左上に「TETL」の表記有り）
![スクリーンショット 2024-12-25 18.30.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/6873769e-dc73-b6b1-438f-129a9e597baf.png)



- 処理時間は1分足らずで、通常のETL処理と比較してあまり差はみられませんでした。

![スクリーンショット 2024-12-25 18.31.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/295123/924043d0-a1f5-64ed-90ae-c2f445392b7c.png)