watsonx.dataで安価なオブジェクトストレージにテーブルを作成し、SQL文でアクセスしてみた

[watsonx.data](https://www.ibm.com/jp-ja/products/watsonx-data)で2種類の方法でオブジェクトストレージにテーブルを作成し、watsonx.dataに組み込まれているオープンソースの[Presto](https://prestodb.io/)エンジンを使ってSQL文でアクセスしてみた記事です。

watsonx.dataはオープンなデータレイクハウス・アーキテクチャに基づいて構築されており、大容量のデータを安価に保存できるオブジェクトストレージにテーブルを作成したり、他のデータベースを統合することができ、それらのデータベースのデータをwatsonx.dataに組み込まれているオープンソースのPrestoエンジンを使って横断的にSQL文でアクセスできます。Prestoエンジンは、Resource Manager, Coordinator, Workerで構成され、複数のWorkerでデータ処理することができます。

テーブル形式は[Iceberg](https://iceberg.apache.org/docs/latest/)を採用しており、DBMSのACID特性やタイムトラベル機能等をサポートしています。パーティションは、[Hidden partitioning](https://iceberg.apache.org/docs/latest/partitioning/#icebergs-hidden-partitioning)機能によりシステムが自動で管理するのでユーザーが明示的に指定する必要はなく、さらに、[Partition evolution](https://iceberg.apache.org/docs/latest/evolution/#partition-evolution)でデータ量に応じて、例えばdate型の列に対して日付でパーティションしていたものをある時点から月に変えるといった機能も提供されているようです。また、これらの処理はメタデータを使って行われるためテーブルの物理的なレイアウトには依存しないとのこと。

オブジェクトストレージに保存されるテーブルのデータ形式は[Parquet](https://parquet.apache.org/docs/overview/motivation/)という列指向の形式でデータ分析に向いたものとなっており、効率的なデータ圧縮機能等を提供しています。テーブル作成時の`with`オプションで`partitioning=array['列1', ...]`を指定することで、物理的なファイルを明示的に分割することも可能で、クエリ・パフォーマンスの向上、ストレージ・コストの低減等が期待できるとのことです。ただし、Icebergを使えば上記で述べた機能があるため、物理的なレイアウトにはあまりこだわらなくてもよさそうです。

## この記事の内容
- 以下の環境を作って試しました
  - [IBM Cloud Pak for Data](https://www.ibm.com/docs/en/cloud-paks/cp-data/4.7.x?topic=overview)(CP4D)上にwatsonx.dataを導入
    - CP4D バージョン: 4.7.3
    - watsonx.data (SW版) バージョン: 1.0.3
- テーブルを作成しデータを投入する方法として以下の2種類をやってみました
  1. csv形式のデータファイルを使って[IBM Cloud Object Storage](https://www.ibm.com/jp-ja/products/cloud-object-storage)(ICOS)にテーブルを作成し、データを投入する
  2. 他のデータソースからSQL文でIBM Cloud Object Storage(ICOS)にテーブルを作成して、データを投入する
- Db2に保存されているスノーフレーク・スキーマのファクト表の古いデータをICOS上に移行してみました。ファクト表を新しいデータ(Db2に保存)と古いデータ(オブジェクトストレージに保存)に分割したので、さらにVIEWで統合できると良いと思ったが、VIEWはサポートしていませんでした。分割したファクト表を結合して扱いたい場合は、代替案として、WITH句でファクト表を統合したサブクエリに名前をつけ、その名前を使ってSQL文を構成するやり方で対応するのがよさそうです。

以下、オブジェクトストレージ上にテーブルを作成した2種類の方法ついて記載しています。
1. csv形式のデータファイルを使ってIBM Cloud Object Storage(ICOS)にテーブルを作成し、データを投入する
1. 他のデータソースからSQL文でIBM Cloud Object Storage(ICOS)にテーブルを作成して、データを投入する

1と2共通の作業として、事前にオブジェクトストレージ(今回はICOSを使用)をwatsonx.dataに追加する必要があります。その方法について最初に記載しています。

## 0. IBM Cloud Object Storage(ICOS)をwatsonx.dataに追加する
この手順は以下のようになる。
0-1. IBM Cloud Object Storageののバケットとその資格情報を作成する
0-2. 作成したバケットをwatsonx.dataに追加する

### 0-1. IBM Cloud Object Storageのバケットとその資格情報を作成する

IBM CloudのカタログからObject Storageを選択し作成する。次に、IBM Cloudのリソースリストのストレージ配下に作成されたObject Storageを選択する。
以下のような画面が表示されるので、右側の`バケットの作成`ボタンをクリックする。

![ibm_cloud_object_storage.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/9c605ed5-9a8a-403d-45ab-18e7daa9c67a.png)

`バケットの作成`画面で、`Create a Custom Bucket`パネルの`作成`ボタンをクリックする。
以下の画面にて、`固有のバケット名`を入力し、`回復力`と`ロケーション`と`ストレージ・クラス`を選択して、`バケットの作成`ボタンをクリックする。
(ここでは、固有のバケット名を`watsonx-data-mo-icos-2023`、`回復力`を`Regional`、`ローケーション`を`jp-tok`、`ストレージ・クラス`を`Smart Tier`とした)

![icos_bucket_creation.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/e7688ddd-0450-16e3-e67b-3fb26d8eb582.png)

バケットが作成されると、オブジェクトのリスト画面が表示される。上記の`インスタンス / Cloud Object Storage-xxx /` の `Cloud Object Storage-xxx`を選択し画面を切り替え、さらに`サービス資格情報`タブを選択し、以下の画面を表示させる。

![icos_service_credential_list.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/dd29c20b-1e9c-4710-b666-95c207fd6a11.png)

右側の`新規資格情報`ボタンをクリックし、`資格情報の作成`パネルを表示させる。
`名前`を入力し、`ロール`を`ライター`、`HMAC資格情報を含める`を`オン`にセットし、`追加`ボタンをクリックする。

![icos_service_credentials_creation.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/1125d989-5c2b-1f55-106f-01c31bf2323d.png)

結果として以下のような資格情報が作成される。

![icos_service_credentials.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/cc9bc205-f76f-7679-e7ff-93717697797b.png)

上記の情報のうち、以下の2つの値を使用する。
```json
    "cos_hmac_keys": {
        "access_key_id": "c4b81082e47a426a96843c3ce30a7f39",
        "secret_access_key": "7d0087476f1aa6c8f07a2d35e7664e2ac4bb5648e914bf85"
    }
```

また、バケットのエンドポイントURLを得るために、画面左側メニューから`エンドポイント`を選択する。
今回のバケットは、`回復力`を`Regional`、`ロケーション`を`jp-tok`で作成したので、以下のようにその値をセットしてURLを確認する。

![icos_endpoint.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/28a81dc8-957f-bfac-af49-510d3b68944a.png)

上記の画面に記載されている`パブリック`のエンドポイントは以下となっている。
```
s3.jp-tok.cloud-object-storage.appdomain.cloud
```

### 0-2. 作成したバケットをwatsonx.dataに追加する

watsonx.dataの`インフラストラクチャー・マネージャー`の画面を表示させ、右側にある`コンポーネントの追加`から`バケットの追加`を選択する。

![watsonx_data_add_bucket.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/96165087-2b6d-033a-890b-90f04d6159c6.png)

今回のケースでは以下のように入力する。
| 名称   | 値    |
|:------|:------|
| バケット・タイプ | IBM Cloud Object Storage |
| Region  | Tokyo (jp-tok) |
| バケット名 | watsonx-data-mo-icos-2023 |
| エンドポイント | https\://s3.jp-tok.cloud-object-storage.appdomain.cloud |
| アクセス・キー | 資格情報のaccess_key_idの値 |
| 秘密鍵 | 資格情報のsecret_access_keyの値 |
| アクティブ化 | `今すぐアクティブ化`を選択 |
| カタログ・タイプ | Apache Iceberg |
| カタログ名 | icos1 |

追加する前に画面の中程にある`Test connection`で接続をテストしてみる。
テスト接続に成功すると下図のように`Successful`と表示されるので、それを確認した後、`今すぐ追加してアクティブ化`ボタンをクリックする。

![watsonx_data_add_bucket_panel.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/9fa2f6c7-8908-ac2e-74b0-2491fba2bcca.png)

`インフラストラクチャー・マネージャー`に以下のように追加したICOSのバケットとカタログが表示される。

![watsonx_data_infra_icos1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/46dc53fa-c964-1f00-c673-b64064f2ebb7.png)

Prestoエンジンに接続するために、`icos1`画面上にマウス・カーソルを移動し、その上部に表示された`関連付けの管理`アイコンをクリックする。

![watsonx_data_infra_icos1_connection.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/131b8419-8373-fbdd-5444-4da4a5cd7384.png)

`関連付けの管理`画面で`presto-01`エンジンにチェックし、`保存してエンジンを再始動する`ボタンをクリックする。
![watsonx_data_infra_icos1_connection2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/5855dcf3-f071-dc98-f557-3b0a56bf1a68.png)

しばらくすると、Prestoエンジンの再起動が終了し、以下のようになる。

![watsonx_data_infra_icos1_connected.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/ffecbb1f-aa6f-c455-5089-593bfbdf8ede.png)


## 1. csv形式のデータファイルを使ってIBM Cloud Object Storage(ICOS)にテーブルを作成し、データを投入する
この手順は以下のようになる。
1-1. テーブルの作成とデータを投入するためのcsv形式のファイルを用意する
1-2. 作成するテーブルが所属するスキーマを作成する
1-3. 用意したファイルをwatsonx.dataに投入し、テーブルの作成とデータの投入を行う

### 1-1. テーブルの作成とデータを投入するためのcsv形式のファイルを用意する

csv形式のデータファイルは以下のCognos Analyticsのチュートリアルに記載されている`IBM_HR_Training_2014-17.csv`を使用する。このファイルをローカルPCにダウンロードしておく。
- [Cognos Analytics - データのアップロード](https://www.ibm.com/docs/ja/cognos-analytics/12.0.0?topic=tutorial-uploading-data)

### 1-2. 作成するテーブルが所属するスキーマを作成する

watsonx.data画面左側のアイコン・メニューからデータ・マネージャーを選択する。
以下のような画面が表示されるので、`作成`を展開し、`図式の作成`(`スキーマの作成`)を選択する。

![watsonx_data_create_schema.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/ae614642-87d0-fc2b-1115-b06ab4ee1da8.png)

`catalog`で`icos1`を選択、スキーマの`名前`として`hr`を入力し、`作成`ボタンをクリックする。

![watsonx_data_schema_setting.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/84781e7a-e16b-1e6c-8085-a1ff09779388.png)

### 1-3. 用意したファイルをwatsonx.dataに投入し、テーブルの作成とデータの投入を行う

次に、再度`作成`を展開し、`ファイルから表を...`を選択する。
`ファイルから表を作成する`の`ソース`画面が表示されるので、ここにダウンロードした`IBM_HR_Training_2014-17.csv`ファイルをドラッグ&ドロップする。
以下のように列ごとにデータタイプがセットされるので、データとともに問題ないか確認する。ここでデータタイプを変更することもできる。問題なければ、`次へ`ボタンをクリックする。

![watsonx_data_create_table_from_file.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/7c956bd6-75c4-9c3d-f36c-486e81d603bd.png)

`ターゲット`画面で、`catalog`として`icos1`、`スキーマ`として`hr`を選択、`表名`として`hr_training`を入力する。`テーブルの形式`と`データ形式`は一択しかなく、`Apache Iceberg`と`Parquet`が選択されている。
`次へ`ボタンをクリックする。

![watsonx_data_create_table_from_file_target.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/049f1ebc-30f1-eb4e-8b7d-d301d0e9e225.png)

`サマリー`として設定内容と`CREATE TABLE`文が表示されるので確認して`作成`ボタンをクリックする。

![watsonx_data_create_table_from_file_summary.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/8093bac8-00a1-371b-ea0b-3f671b222569.png)

テーブルの作成&データの投入が成功すると以下のようなメッセージが表示される。
```
Your new table has been created.
icos1.hr.hr_training is now available in the data manager.
```

SQL文を発行して確認してみる。
watsonx.data画面左側のアイコン・メニューから`紹介ワークスペース`(SQL)を選択する。
`Untitled 1`タブの下のフィールドに以下のSQL文をセットする。
```sql
select * from icos1.hr.hr_training limit 3;
```

次に`presto-01で実行`ボタンをクリックしてSQL文を実行する。

![watsonx_data_sql_hr_training.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/75c4edb3-2c04-8c60-384b-69db7756c4ed.png)

SQLが正常に実行されると、下記のように実行結果が表示される。

![watsonx_data_sql_hr_training_results.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/5bfebd76-5749-8b12-8e08-ca6e377b4c02.png)

## 2. 他のデータソースからSQL文でIBM Cloud Object Storage(ICOS)にテーブルを作成して、データを投入する
この手順は以下のようになる。
2-1. 他のデータソースを確認してみる
2-2. 他のデータソースにあるテーブルをICOSにSQL文でコピーしてみる
2-3. 他のデータソースにあるファクト表の一部をICOSにSQL文でコピーしてみる

### 2-1. 他のデータソースを確認してみる

Db2に以下のスノーフレークスキーマが構成されている。(スキーマ名は`gdq11991`)
| テーブルのタイプ | テーブル名 | 行数 |
|:--|:--|--:|
| 次元表 | sls_product_dim | 274 |
| 次元表 | sls_product_line_lookup | 5 |
| 次元表 | sls_product_brand_lookup | 28 |
| 次元表 | sls_order_method_dim | 7 |
| ファクト表 | sls_sales_fact | 446023 |

このDb2をwatsonx.dataに組み込んでみた。以下の一番右にあるのがDb2で、カタログ名はdb2とした。

![watsonx_data_add_db2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/6f478bf8-f231-50cf-4be9-d7049ba792e7.png)

左側のアイコン・メニューから`照会ワークスペース`(SQL)を選択し、以下のSQL文を発行してみる。
```sql
select
  pll.product_line_en as product,
  pbl.product_brand_en as brand,
  md.order_method_en as order_method,
  sum(sf.quantity) as total
from
  db2.gdq11991.sls_order_method_dim as md,
  db2.gdq11991.sls_product_dim as pd,
  db2.gdq11991.sls_product_line_lookup as pll,
  db2.gdq11991.sls_product_brand_lookup as pbl,
  db2.gdq11991.sls_sales_fact as sf
where
  pd.product_key = sf.product_key
  and md.order_method_key = sf.order_method_key
  and pll.product_line_code = pd.product_line_code
  and pbl.product_brand_code = pd.product_brand_code
group by
  pll.product_line_en,
  pbl.product_brand_en,
  md.order_method_en
order by
  product,
  brand,
  order_method;
```

結果として正常に動作し、以下の結果が得られた。

![watsonx_data_olap_sql.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/7857ba7a-420f-e5c0-f8c2-4df414a7879b.png)

### 2-2. 他のデータソースにあるテーブルをICOSにSQL文でコピーしてみる

`sls_product_dim`テーブルをicos1カタログにコピーしてみる。そのためのスキーマ名を`sls`とする。
まずは、`hr`スキーマを作成したのと同じやり方で`sls`スキーマを作成する。
([1-2. 作成するテーブルが所属するスキーマを作成する](#1-2-作成するテーブルが所属するスキーマを作成する)を参照)

次に以下のSQL文を発行して`sls_product_dim`をコピーする。
```sql
create table icos1.sls.sls_product_dim
  with (
    format='PARQUET'
  )
  as select * from db2.gdq11991.sls_product_dim;
```
上記のSQL文を発行した後に、正しくデータがコピーされているか確認するために、そのテーブルに挿入されているレコードの数をカウントしてみた。274件で、コピー元のレコード数と一致する。

![watsonx_data_copy_table.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/7ca27914-7e1b-4395-1f3f-04e221fed3f1.png)

### 2-3. 他のデータソースにあるファクト表の一部をICOSにSQL文でコピーしてみる

ファクト表の`sls_sales_fact`テーブルの`order_day_key`にオーダーした年月日が保存されているので、この値の古いデータをICOSに移行してみる。

まずは、保存されているデータの年を確認してみる。`order_day_key`はinteger型でyyyymmdd形式で値が入っているので、以下のSQL文を発行してみる。

![watsonx_data_fact_years.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/46daed46-9fcc-4c7b-2785-501b668d0277.png)

2004年から2007年までのデータとなっており、全体的にちょっと古いが、今回は2007年が直近のデータとしてDb2に残し、それ以外のデータをICOSに移行してみる。

2007年より古いデータをコピーするために、以下のSQL文をwatsonx.dataに対して発行する。
```sql
create table icos1.sls.sls_sales_fact
  with (
    format='PARQUET'
  )
  as select * from db2.gdq11991.sls_sales_fact
  where order_day_key/10000 < 2007;
```

結果として、ICOSにテーブルが作成され、350551件のレコードが挿入されている。

![watsonx_data_copy_table2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/55b8744c-61f9-c56c-5f12-cd25c1c962b1.png)

次に、2007年より古いデータはICOSに移行したので、Db2側のファクトテーブルから移行したデータを削除する。(この処理は、watsonx.dataからDb2に対しての更新はサポートしていなかったので、直接Db2に接続して行った)
```sql
delete from gdq11991.sls_sales_fact where order_day_key/10000 < 2007;
```

結果として、ICOSのテーブルに挿入されたレコード数と同じ350551件のレコードが削除された。

ファクト表が分割されたのでVIEWを作って統合しようとしたがエラーとなった。少なくとも複数のデータソース(コネクター)間のテーブルを使ったVIEWはサポートしていないようだ。

代替案として、統合したファクト表を使いたい時は、VIEWを使う代わりに、WITH句でファクト表を結合するサブクエリに名前をつけて、それを使ってみた。

以下に、VIEWの代わりにWITH句を使ったSQL文の例を述べる。
(`sls_product_dim`テーブルもICOSにコピーしたので、そのテーブルも使ってみる)

```sql
with sales_fact as (
  select * from icos1.sls.sls_sales_fact union all
  select * from db2.gdq11991.sls_sales_fact
)
select
  pll.product_line_en as product,
  pbl.product_brand_en as brand,
  md.order_method_en as order_method,
  sum(sf.quantity) as total
from
  db2.gdq11991.sls_order_method_dim as md,
  icos1.sls.sls_product_dim as pd,
  db2.gdq11991.sls_product_line_lookup as pll,
  db2.gdq11991.sls_product_brand_lookup as pbl,
  sales_fact as sf
where
  pd.product_key = sf.product_key
  and md.order_method_key = sf.order_method_key
  and pll.product_line_code = pd.product_line_code
  and pbl.product_brand_code = pd.product_brand_code
group by
  pll.product_line_en,
  pbl.product_brand_en,
  md.order_method_en
order by
  product,
  brand,
  order_method;
```

上記をwatsonx.dataの`照会ワークスペース`(SQL)で実行してみると以下のように正常に処理された。

![watsonx_data_olap_sql_federation.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/507588/b2a4412f-00c1-cabd-5662-f07d2e3f7d33.png)

## 補足: PrestoエンジンのCoordinator/Worker数の変更方法

watsonx.data SW版をインストールすると、デフォルトでは、CoordinatorとWorkerが統合された1つのpodが起動する。以前は、これを変えるために`wxdengine/lakehouse-presto-01`の定義を以下のように変更していたのですが、少し仕様が変わったようです。
- lakehouseDeploymentTypeをsingleからmultinodeに変更
- 以下の「以前の変更例」で示した値を追加
  - presto_coordinator_replicasがcoordinator数
  - presto_worker_replicasがworker数

```:以前の変更例
# oc edit -n [namespace名] wxdengine/lakehouse-presto-01
...
  lakehouseDeploymentType: multinode
  presto_coordinator_replicas: 1
  presto_coordinator_resources_limits_cpu: 1
  presto_coordinator_resources_limits_memory: 48G
  presto_coordinator_resources_requests_cpu: 1
  presto_coordinator_resources_requests_memory: 48G
  presto_worker_replicas: 3
  presto_worker_resources_limits_cpu: 1
  presto_worker_resources_limits_memory: 48G
  presto_worker_resources_requests_cpu: 1
  presto_worker_resources_requests_memory: 48G
...
# oc get sts -n cpd-instance | grep ibm-lh-lakehouse
ibm-lh-lakehouse-presto-01-presto               0/0     17d
ibm-lh-lakehouse-presto-01-presto-coordinator   1/1     17d
ibm-lh-lakehouse-presto-01-presto-worker        3/3     17d
```

現時点のCP4D 4.7.x のマニュアルによると、自動スケーリングと手動スケーリングのやり方があり、手動スケーリングでは small/medium/large/xlarge が選択できるようです。
- [サービスのリソースの自動スケーリング](https://www.ibm.com/docs/ja/cloud-paks/cp-data/4.7.x?topic=services-automatically-scaling)
- [サービスのリソースの手動スケーリング](https://www.ibm.com/docs/ja/cloud-paks/cp-data/4.7.x?topic=services-manually-scaling)

```shell:Prestoエンジンを自動スケールするコマンド例
oc patch wxdengine/lakehouse-presto-01 \
  --type=merge \
   -n ${PROJECT_CPD_INST_OPERANDS} \
   -p '{ "spec": { "autoScaleConfig": "true", "lakehouseDeploymentType": "multinode" } }'
```

```shell:Prestoエンジンを手動スケールするコマンド例
oc patch wxdengine/lakehouse-presto-01 \
  --type=merge \
   -n ${PROJECT_CPD_INST_OPERANDS} \
   -p '{ "spec": { "scaleConfig": "small", "lakehouseDeploymentType": "multinode" } }'
```