More than 1 year has passed since last update.

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

Last updated at 2022-09-30Posted at 2022-09-30

はじめに

AWS では標準 SQL を使用して Amazon S3 のデータを簡単に分析できるクエリサービスとして Amazon Athena がありますが、IBM Cloud でも類似サービスとして、Data Engine (previously SQL Query) があります。
本記事では、IBM Blog | SQL Query Releases Serverless Transformation and Partitioning of Data in Open Formats を参考に様々なデータ形式で Data Engine を動作させてみます。
また、CP4DaaS で、Data Engine のカタログ管理機能を使用して作成したテーブルをプロジェクトの資産として管理できるところを動作させてみます。

前提

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

Data Engine 動作内容

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

これに関して、Data Engine の UI を立ち上げて以下の3つのケースで動作させ、Run time (実行時間) と Data scanned (スキャンしたデータ) の実行結果を比較します。

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

ケース1: CSV のまま

CSV のままで変換は不要です。
データを取得します。

SELECT * FROM cos://us-east/my-data-engine-bkt-tt/BenefitsCostSharing.csv STORED AS CSV WHERE BusinessYear = 2016

ケース2: CSV を Parquet に変換

CSV を Parquet に変換します。
```
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 といった形に置き換えて、ユースケースに応じて様々なデータ形式に変換することができます。

データを取得します。

SELECT * FROM cos://us-east/my-data-engine-bkt-tt/parquet/ STORED AS PARQUET WHERE BusinessYear = 2016

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

CSV を Parquet に変換 + Hive スタイルのパーティショニングします。
```
 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行)を作成することができます。

データを取得します。

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 のカタログ管理の機能を使ってテーブル作成

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

データを取得します。(正しくテーブルが作成できていることを確認)
```
SELECT * FROM Network_1 LIMIT 5
```

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

CP4DaaS のナビゲーション・メニューにて プロジェクト > すべてのプロジェクト を選択してプロジェクト一覧を表示し、新しいプロジェクト ボタンを押下します。
プロジェクトの作成画面にて 空のプロジェクトを作成 を選択します。
新規プロジェクトで必要項目を入力して 作成 ボタンを押下します。

名前: プロジェクト名(ここでは、DataEngine Test と入力)

作成したプロジェクト画面にて、資産 タブを押下します。
新規資産 ボタンを押下します。
新規資産登録画面にて すべてのタイプ を選択し、その後、データアクセスツールにて 接続 を選択します。
検索窓に data engine と入力し、すべての接続タイプにて IBM Cloud Data Engine を選択します。
選択済みの接続タイプが IBM Cloud Data Engine であることを確認し、選択 ボタンを押下します。
接続の概要で必要項目を入力し、作成 ボタンを押下します。

名前: 任意の文字列 (ここでは、Data Engine Connection)
CRN: DataEngine のCRN
- Data Engine の UI にて、Connect タブを選択し、Use the following instance CRN to connect: の文字列をコピーにより取得できます。
ターゲット Cloud Storage: ICOS のURL
パスワード: apikey
- 手順は IBM Cloud の API キーを取得を確認してください。

ポップアップが表示されますが、そのまま 作成 ボタンを押下します。
Import assets を押下します。
アセットの追加画面にて、Connected data → 作成した接続(ここでは、Data Engine Connection) → Data Engine のカタログ管理の機能を使ってテーブル作成 で作成したテーブルを選択(ここでは network_1)して、選択 ボタンを押下します。
アセットが追加されていることを確認し、追加した network_1 を押下します。
プレビュー タブを選択し、データが表示されることを確認します。

終わりに

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

参考

お断り

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up