Data Engine を様々なデータ形式で動作確認 & そのデータを CP4DaaS で管理

# はじめに

- AWS では標準 SQL を使用して Amazon S3 のデータを簡単に分析できるクエリサービスとして [Amazon Athena](https://docs.aws.amazon.com/athena/index.html) がありますが、IBM Cloud でも類似サービスとして、**[Data Engine (previously SQL Query)](https://cloud.ibm.com/docs/sql-query)** があります。
- 本記事では、[IBM Blog | SQL Query Releases Serverless Transformation and Partitioning of Data in Open Formats](https://www.ibm.com/cloud/blog/announcements/sql-query-releases-serverless-transformation-and-partitioning-of-data-in-open-formats) を参考に様々なデータ形式で Data Engine を動作させてみます。
- また、CP4DaaS で、Data Engine のカタログ管理機能を使用して作成したテーブルをプロジェクトの資産として管理できるところを動作させてみます。

# 前提

- [ICOS上のファイルを簡単にSQL検索できる「SQL Query」を使ってみた](https://qiita.com/ishida330/items/45513e1644d058db04a4) の **1.サービス・インスタンス作成** 〜 **5. SELECTする** などを参考にして Data Engine の環境準備が整っていること。
- サンプルデータを ICOS にアップロード済みであること。
  - 今回は、Kaggle  で公開している (※ ユーザー登録が必要) [Health Insurance Marketplace](https://www.kaggle.com/datasets/hhs/health-insurance-marketplace) をダウンローして使用します。
    - Health Insurance Marketplace は個人および中小企業に提供される健康および歯科プランに関するデータになります。
    - Data Engine の動作確認では、`BenefitsCostSharing.csv` を使用します。1.33 GB あるので、[Aspera](https://www.ibm.com/jp-ja/products/aspera) 転送すると爆速で ICOS にアップロードすることができます。
    - また、CP4DaaS の動作確認では、`Network.csv` を使用します。

# Data Engine 動作内容

JSON や CSV などのソースデータを [Parquet](https://parquet.apache.org/) または [ORC](https://orc.apache.org/) などの列指向形式に変換することでクエリパフォーマンスを向上させたり、データをパーティション分割することで各クエリによってスキャンされるデータ量を制限させ、パフォーマンスが向上し、コストを削減することができます。

これに関して、Data Engine の UI を立ち上げて以下の3つのケースで動作させ、Run time (実行時間) と Data scanned (スキャンしたデータ) の実行結果を比較します。
- ケース1: CSV のまま
- ケース2: CSV を Parquet に変換
- ケース3: Hive スタイルのパーティショニングを使用して、CSV を Parquet に変換

## ケース1: CSV のまま

1. CSV のままで変換は不要です。
2. データを取得します。
  ```sql
  SELECT * FROM cos://us-east/my-data-engine-bkt-tt/BenefitsCostSharing.csv STORED AS CSV WHERE BusinessYear = 2016
  ```

## ケース2: CSV を Parquet に変換

- CSV を Parquet に変換します。
  ```sql
  SELECT * FROM cos://us-east/my-data-engine-bkt-tt/BenefitsCostSharing.csv STORED AS CSV INTO cos://us-east/my-data-engine-bkt-tt/parquet/ STORED AS PARQUET
  ```
  - 参考として、`STORED AS PARQUET` の部分を `STORED AS ORC` , `STORED AS JSON` , `STORED AS CSV` , `STORED AS AVRO` といった形に置き換えて、ユースケースに応じて様々なデータ形式に変換することができます。
- データを取得します。
  ```sql
  SELECT * FROM cos://us-east/my-data-engine-bkt-tt/parquet/ STORED AS PARQUET WHERE BusinessYear = 2016
  ```

## ケース3: Hive スタイルのパーティショニングを使用して、CSV を Parquet に変換

- CSV を Parquet に変換 + Hive スタイルのパーティショニングします。
  ```sql
   SELECT * FROM cos://us-east/my-data-engine-bkt-tt/BenefitsCostSharing.csv STORED AS CSV INTO cos://us-east/my-data-engine-bkt-tt/parquetByBusinessYear/ STORED AS PARQUET PARTITIONED BY (BusinessYear)
  ```
  - 参考として、`PARQUET PARTITONED BY (BusinessYear)` の部分を `PARTITIONED INTO 3 BUCKETS` に置き換えて結果データを固定数(ここでは3つ)のオブジェクトに分割したり、`PARTITIONED EVERY 30 ROWS` に置き換えて結果データをページ分割されたオブジェクト(ここでは30行)を作成することができます。
- データを取得します。
  ```sql
  SELECT * FROM cos://us-east/my-data-engine-bkt-tt/parquetByBusinessYear/ STORED AS PARQUET WHERE BusinessYear = 2016
  ```

## 検証結果

検証結果は、以下の通りになりました。

|ケース| Run time | Data scanned |
|:-|:-|:-|
|ケース1: CSV のまま|130.70秒|1.33 GB|
|ケース2: CSV を Parquet に変換|61.36 秒|20.6 MB|
|ケース3: Hive スタイルのパーティショニングを使用して、CSV を Parquet に変換|43.25 秒|3.76 MB|

- ケース1 と ケース3 を比較すると、パフォーマンスが3倍ほど向上し、スキャンしたデータも 約 1/354 に削減され、非常に効果があることが分かりました。

# CP4DaaS の動作内容

次に、CP4DaaS で、Data Engine のカタログ管理機能を使用して作成したテーブルをプロジェクトの資産として管理できるところを動作させてみます。

作業手順は以下の通りになります。注意点として、Data Engine のカタログ管理機能を使用するには **スタンダードプラン** である必要があります。
- 作業手順1. Data Engine のカタログ管理の機能を使ってテーブル作成
- 作業手順2. CP4DaaS にてプロジェクトを作成し Data Engine のテーブルを資産登録

## 作業手順1. Data Engine のカタログ管理の機能を使ってテーブル作成

1. Data Engine の UI を立ち上げます。
2. テーブルを作成します。
    ```sql
    CREATE TABLE Network_1 USING CSV LOCATION cos://us-east/my-data-engine-bkt-tt2/Network.csv
    ```
    参考までに Parquet の場合、`USING CSV` の部分を `USING　PARQUET` といった形に置き換えます。
3. テーブルスキーマを確認します。(正しくテーブルが作成できていることを確認)
    ```sql
    DESCRIBE TABLE Network_1
    ```
- データを取得します。(正しくテーブルが作成できていることを確認)
    ```sql
    SELECT * FROM Network_1 LIMIT 5
    ```

## 作業手順2. CP4DaaS にてプロジェクトを作成し Data Engine のテーブルを資産登録

1. CP4DaaS のナビゲーション・メニューにて `プロジェクト > すべてのプロジェクト` を選択してプロジェクト一覧を表示し、`新しいプロジェクト` ボタンを押下します。
  ![新規プロジェクト作成](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/4ccd2b2d-db51-a08b-5774-96aea68bda8b.png)
2. プロジェクトの作成画面にて `空のプロジェクトを作成` を選択します。
  ![空プロジェクト作成](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/b5fe24d3-36da-d7d6-75e6-977fcd9e804a.png)
3. 新規プロジェクトで必要項目を入力して `作成` ボタンを押下します。
  - 名前: プロジェクト名(ここでは、`DataEngine Test` と入力)
  ![新規プロジェクト入力](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/9b69ccbf-a238-3933-f2c3-2a2512151c8e.png)
4. 作成したプロジェクト画面にて、`資産` タブを押下します。
  ![新規資産画面表示](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/40eabc05-86da-f42b-0f43-e6b4f75095c3.png)
5. `新規資産` ボタンを押下します。
  ![新規資産ボタン押下](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/3f62b2a3-013e-186c-3243-eceea8e90784.png)
6. 新規資産登録画面にて `すべてのタイプ` を選択し、その後、データアクセスツールにて `接続` を選択します。
  ![データアクセスツール選択](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/b16f89b6-33c0-1e9a-63cd-079a5936135f.png)
7. 検索窓に `data engine` と入力し、すべての接続タイプにて `IBM Cloud Data Engine` を選択します。
  ![接続タイプ選択](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/4edb0d2e-96df-4423-ea99-2d02eaad1c06.png)
8. 選択済みの接続タイプが `IBM Cloud Data Engine` であることを確認し、`選択` ボタンを押下します。
  ![選択済み接続タイプ選択](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/faace50b-5b1b-a2f0-0cf1-bfb7151e8d12.png)
9. 接続の概要で必要項目を入力し、`作成` ボタンを押下します。
  ![接続概要入力1](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/735c16cd-20e8-dc47-1d88-bd79ec907a11.png)
  ![接続概要入力1](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/32de49e2-6682-2638-faa1-308db8a5d467.png)
  - 名前: 任意の文字列 (ここでは、`Data Engine Connection`)
  - CRN: DataEngine のCRN
    - Data Engine の UI にて、`Connect` タブを選択し、`Use the following instance CRN to connect:` の文字列をコピーにより取得できます。
      ![DataEngine CRN](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/d80e040b-9f99-08fb-6fe0-aecf321da472.png)

  - ターゲット Cloud Storage: ICOS のURL
  - パスワード: apikey
    - 手順は [IBM Cloud の API キーを取得](https://qiita.com/to_tomo/items/63f98ea8640c6f568849#%E4%BD%9C%E6%A5%AD%E6%89%8B%E9%A0%861-ibm-cloud-%E3%81%AE-api-%E3%82%AD%E3%83%BC%E3%82%92%E5%8F%96%E5%BE%97) を確認してください。 
10. ポップアップが表示されますが、そのまま `作成` ボタンを押下します。
  ![接続の作成](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/10133a29-7725-628a-db36-9478ed7a8196.png)

11. `Import assets` を押下します。
  ![Import assets](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/ec61d131-1fa5-7633-b8c7-7175af3f1fde.png)
12. アセットの追加画面にて、`Connected data` → 作成した接続(ここでは、`Data Engine Connection`) → `Data Engine のカタログ管理の機能を使ってテーブル作成` で作成したテーブルを選択(ここでは `network_1`)して、`選択` ボタンを押下します。
  ![アセットの追加](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/fd147a8d-3ecd-01b9-c5ad-68191ed2950f.png)
13. アセットが追加されていることを確認し、追加した `network_1` を押下します。
  ![アセットの追加](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/1f0ed5d3-f426-feba-fd9d-e844bb8ed320.png)
14. `プレビュー` タブを選択し、データが表示されることを確認します。
  ![プレビュー](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2753136/8769def9-019c-d6a8-a447-e41426aea7ba.png)

# 終わりに

- Data Engine 動作の関連事項として、[IBM Blog | IBM Cloud Object Storage for Spark SQL](https://www.ibm.com/cloud/blog/big-data-layout) でも、性能面での考慮点が纏められているので参考になるかと思います。
  - いくつか抜粋すると、以下のような内容が掲載されています。
    - 128 MB のオブジェクトサイズを目指す。
      - `maxRecordsPerFile` を使用して大きなオブジェクトを避ける。
      - `coalesce` や `repartition` コマンドを使用して、パーティション数を減らして、REST 要求の数を抑える。
    - `Parquet` を使用する。
      - 列単位の圧縮によって、ストレージコストを削減する。
    - 大きな CSV または JSON オブジェクトを Gzip で圧縮しない。
      - Ggip は分割可能な圧縮アルゴリズムではないため、読み取るためにオブジェクトを全体を解凍する必要がある。
      - メモリ不足になる可能性がある。
      - オブジェクト解凍後、ワーカーに再配分するためにシャッフルが必要になる。
    - Hive スタイルのパーティショニングを使用する。
      - 月、日、時は最上位のパーティション列に適している。
      - 頻繁にクエリが実行される場合、パーティション列を追加する。
      - カーディナリティ(カラムに格納されているデータ種類)が 1000 以上のパーティション列の使用を避ける。
- CP4DaaS の動作で示したように、Data Engine のカタログ管理機能を使用して作成したテーブルをプロジェクトの資産として管理することができます。

# 参考

- [IBM Cloud Docs | IBM Cloud® Data Engine > Overview](https://cloud.ibm.com/docs/sql-query?topic=sql-query-overview)
- [IBM Cloud Docs | IBM Cloud® Data Engine > カタログ管理](https://cloud.ibm.com/docs/sql-query?topic=sql-query-hivemetastore)
- [IBM Cloud docs | IBM Cloud Data Engine 接続](https://dataplatform.cloud.ibm.com/docs/content/wsj/manage-data/conn-sqlquery.html?locale=en&audience=wdp)
- [Amazon Athena ユーザーガイド | 列指向形式への変換](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/convert-to-columnar.html)
- [Amazon Athena ユーザーガイド | Athena でのデータのパーティション化](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html)

# お断り

このサイトの掲載内容は私自身の見解であり、必ずしも所属会社の立場、戦略、意見を代表するものではありません。 記事は執筆時点の情報を元に書いているため、必ずしも最新情報であるとはかぎりません。 記事の内容の正確性には責任を負いません。自己責任で実行してください。