watsonx.data: 〜ファイル置くだけ簡単取りこみ! 〜 オブジェクトストレージ上のParquetファイルからHiveテーブルにデータ取り込み、IcebergテーブルにINSERT　

watsonx.dataのHiveテーブルはオブジェクトストレージ上のcsvやParquetファイルなどを使用してテーブル作成が可能で、明示的なLOADなしでデータをテーブルに取り込むことができます。またwatsonx.dataではHiveテーブルからIcebergテーブルを作成することも可能です。

当記事ではwatsonx.data（SaaS版）で、Prestoエンジンと関連付けされたIBM Object Storage(ICOS)上にある**Parquetファイル**を使用してテーブル作成し、　その後HiveテーブルからIcebergテーブルを作成してみます。

> 2024/09/06: ファイルを置くオブジェクトストレージはHiveカタログと関連付けされたICOS上に限らず、Prestoエンジンと関連付けがされているICOSであればどこでもよいことがわかったので、修正しました。


# 前提
**1. Hiveカタログ作成済み**
ない場合は「インフラストラクチャー・マネージャー」から「コンポーネントの追加」で使用するStorageを選択後、一般情報、ストレージ構成を入力し、「カタログの関連付け」「今すぐアクティブ化」をチェックし、カタログタイプに「Apache Hive」を設定して作成してください。
作成後、関連付けの管理で、prestoエンジンと関連づけてください。

ここでは`hive_data`という名前で Hiveカタログ　を作成したとして進めます。

参考：[ストレージとカタログのペアの追加](https://cloud.ibm.com/docs/watsonxdata?topic=watsonxdata-reg_bucket)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/6c9b66ea-be3f-a37d-aab9-faa4e3e2ab1c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/3b16f2b1-11a1-6c8f-21ef-42e435cf4bb6.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/70604a26-771f-5f70-a4ed-b379a8946bf5.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/f7929022-d4ad-e316-d781-940f4da92b4f.png)


**2.　Icebergカタログ作成済み**
(オプション: HiveテーブルからIcebergテーブルを作成する場合)

ない場合は上記Hiveカタログと同様に「インフラストラクチャー・マネージャー」から「コンポーネントの追加」で使用するStorageを選択後、一般情報、ストレージ構成を入力し、「カタログの関連付け」「今すぐアクティブ化」をチェックし、カタログタイプに「Apache Iceberg」を設定して作成してください。
作成後、関連付けの管理で、prestoエンジンと関連づけてください。

ここでは`iceberg_data`という名前で Icebergカタログ　を作成したとして進めます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/df17fe15-28a0-3747-2fde-26f9bdab4407.png)

**3.　ファイルを置くICOSがprestoエンジンと関連づけ済み**
ファイルを置くICOSはすでにprestoエンジンと関連づけ済みである必要があります。
上記1、2でカタログを作成したICOSで自分でアクセス可能なものであればOKです。

あるいは新規でまったく別のICOSを関連づけても構いません。ただprestoエンジンと関連づけるためにはカタログ作成が必須なので、なんでもいいのでICOSとカタログのペアで登録してください、


# 1. 前準備： 使用するParquetファイルの準備
使用するParquetファイルの準備します。


## Parquetファイル
Parquetファイルには項目名および通常属性のメタデータが入っています。作成予定テーブルの項目名および属性を使用したでParquetファイル　を準備してください（あるいはテーブルをParquetファイルの項目名および属性で作成します）。

## ここで使用するParquetファイル
TLC Trip Record Data(NYのタクシーのオープンデータ)を2ヶ月分を取り込んでみます。
[TLC Trip Record Data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page)より、2024年のJanuaryとFebruaryのYellow Taxi Trip Recordsをダウンロードします。
`yellow_tripdata_2024-01.parquet`, `yellow_tripdata_2024-02.parquet`というファイルがダウンロードされます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/cc54b6f1-3582-0e3a-4d0a-94966eea2ce1.png)


# 2. ParquetファイルをPrestoエンジンと関連づけられたICOSのバケットにアップロード

ここでは`スキーマ名/テーブル名/`のフォルダー(=ファイルの接頭部)に取り込みたいParquetファイルを置きます。
尚、フォルダーに入ったファイルであれば場所(path)はどこでもOKです。取り込み時にはファイル名ではなくフォルダーまでのPATHを指定します。

ここではIBM Cloud Object Storage（ICOS）のコンソールを使ってアップロードします。

ここでは、Parquetファイルを取り込むHiveテーブルは
- スキーマ名：`hive_taxi`
- テーブル名: `ny_taxi_trip`

と決めておきます。

#### 2-1. IBM Cloud Object Storage のコンソールで左側のメニューにある「インスタンス」ドロップダウンメニューをクリックし、Prestoエンジンと関連づけられたクラウド・オブジェクト・ストレージ・インスタンスを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/5a28eb02-80a0-f5d3-e6f5-19ae27832351.png)

#### 2-2. Prestoエンジンと関連づけられたバケットをクリックし、右上の「アップロード」ボタンをクリックします
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/2c80c778-b747-59b2-078b-4bbcfd0be776.png)

#### 2-3.　右側に表示される「アップロード」画面で、アップロードするParquetファイルをドラッグアンドドロップします。
ここでは`yellow_tripdata_2024-01.parquet`をドラッグアンドドロップします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/4d19111e-2cbe-73e3-510d-084cc6e9389a.png)

#### 2-4. 「オブジェクトの編成(拡張)」をクリックします
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/92c93cc9-1ca4-684e-f918-001cce14f697.png)



#### 2-5. スクロールダウンして「接頭部」にファイルを入れるフォルダーのPATHを入力します。
ここでは`hive_taxi/ny_taxi_trip/`と入れます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/d042c406-7c72-03c3-c5a6-7236c8f5c0fd.png)



#### 2-6.「アップロード」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/05fd199e-83a8-18a4-4fd7-fe29cd010296.png)

転送状況が表示されます。完了まで待ちます
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/3f827b86-38a3-df42-7807-29ff1171ea37.png)

完了したら「X」をクリックして右画面を消します
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/e1e0a2d5-de4f-d1c6-cbcf-caa03615cf6e.png)

#### 2-7. `入力したフィルダー名`/取り込みたいparquetファイル　のオブジェクト名になっていることを確認します。
ここでは`hive_taxi/ny_taxi_trip/yellow_tripdata_2024-01.parquet`となっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/4ffa741d-c0b0-1a09-e881-4d21d8332ebc.png)

# 3. watsonx.data上でHiveテーブルの作成
取り込み先のHiveテーブルを作成します。

#### 3-1. watsonx.dataコンソールに戻り、左側のメニューから「データマネージャー」アイコンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/71d26222-30d9-4ca6-3f73-addafe75b0b6.png)

#### 3-1. 「作成」ドロップダウンメニューをクリックし、「図式の作成」を選択します。
\* 「図式の作成」は「スキーマの作成」の意味です。おいおい修正されると思います(!)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/4bf87456-5ee7-a04e-0ee1-c0c161ed9f36.png)

#### 3-2: 、「図式の作成」ウィンドウで、Catalog に 使用するHiveカタログ、ここでは`hive_data` を選択し、Name に スキーマ名、ここでは`hive_taxi` と入力します(これは、先ほどアップロードした上位レベルフォルダの名前と一致します)。そして「作成」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/0f4c8462-9ab4-649b-e212-559a633b849c.png)

#### 3-4. 、左側のメニューから「照会ワークスペース」をクリックします
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/547956ef-b506-a14f-99b8-cc169a3f490a.png)

#### 3-5. 取り込みたいparquetファイルと同じフォーマットでテーブル作成します。

以下のように`external_location`を指定して作成します。
```sql
CREATE TABLE <Hiveカタログ名>.<スキーマ名>.<テーブル名>（
　　　　　・・・・　テーブル定義を記入
)
WITH (
   format = 'PARQUET',
   external_location='s3a://<バケット名>/<ファイルを置いたフォルダー名>'
)

```

ここでは
- Hiveカタログ名: `hive_data`
- スキーマ名：`hive_taxi`
- テーブル名: `ny_taxi_trip`
- ファイルを置いたフォルダー名: `hive_taxi/ny_taxi_trip/`
を使います。

以下は`yellow_tripdata_2024-01.parquet`のフォーマットのテーブル作成ステートメントです。
一番下の<バケット名>は自分のHiveカタログのバケット名で置き換えてください。
```sql
CREATE TABLE hive_data.hive_taxi.ny_taxi_trip (
   "VendorID" bigint,
   "tpep_pickup_datetime" timestamp,
   "tpep_dropoff_datetime" timestamp,
   "passenger_count" double,
   "trip_distance" double,
   "RatecodeID" double,
   "store_and_fwd_flag" varchar,
   "PULocationID" bigint,
   "DOLocationID" bigint,
   "payment_type" bigint,
   "fare_amount" double,
   "extra" double,
   "mta_tax" double,
   "tip_amount" double,
   "tolls_amount" double,
   "improvement_surcharge" double,
   "total_amount" double,
   "congestion_surcharge" double,
   "airport_fee" double
)
WITH (
   format = 'PARQUET',
    external_location='s3a://<バケット名>/hive_taxi/ny_taxi_trip'
)
```

テーブル作成ステートメントを入力したら、「XXXで実行」（XXXはエンジン名)をクリックして実行してください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/9e18f196-6b4e-1336-7d03-79b13e013306.png)

結果セットのresultがtrueになっていれば、作成成功です。アップロードしたparquetファイルのデータもテーブルに入っているはずです:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/e9d83911-7fb1-8710-12cd-01c3d7c9b96c.png)


# 4. Hiveテーブルのデータの確認
以下はhive_data.hive_taxi.ny_taxi_tripの確認例です。
SQLでデータが入ったか確認してみましょう！
SQL:
```sql
SELECT
  *
FROM
  "hive_data"."hive_taxi"."ny_taxi_trip"
LIMIT
  10;
```
実行例： ばっちり入ってますね！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/34b82022-449d-056a-7756-9f5502c6336e.png)


以下からの検証はhive_data.hive_taxi.ny_taxi_tripで説明します。
# 5. ファイル追加してみます
 `yellow_tripdata_2024-02.parquet`という2024年2月分のファイルをICOSに「アップロードし、データが追加されたか確認します。

 確認用に現在のレコード数と、tpep_pickup_datetimeのMAX値を調べておきます。
 ```sql
 -- 件数カウント
 select count(*) from "hive_data"."hive_taxi"."ny_taxi_trip";
 -- tpep_pickup_datetimeのMIN, MAX値
select min(tpep_pickup_datetime) MIN_DATETIME ,max(tpep_pickup_datetime) MAX_DATETIME from "hive_data"."hive_taxi"."ny_taxi_trip";
```
件数は　2964624件
tpep_pickup_datetimeのMIN, MAX値 はそれぞれ、`2002-12-31 22:59:39.000`, `2024-02-01 00:01:15.000` となります。

 `yellow_tripdata_2024-02.parquet`をICOSのファイルを置いたパケットの`/hive_taxi/ny_taxi_trip`にアップロードします。

 
ここではIBM Cloud Object Storage（ICOS）のコンソールを使ってアップロードします。

#### 5-1. IBM Cloud Object Storage のコンソールで左側のメニューにある「インスタンス」ドロップダウンメニューをクリックし、Hiveカタログと関連づけられたクラウド・オブジェクト・ストレージ・インスタンスを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/5a28eb02-80a0-f5d3-e6f5-19ae27832351.png)

#### 5-2. hive_taxiフォルダー、その中のny_taxi_tripフォルダーをクリックして開きます。
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/ae5bd4c0-bada-c040-b76d-ae505a3c7a0b.png)

#### 5-3.  `yellow_tripdata_2024-02.parquet`をドラッグしてアップロードします

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/5ef1ac0b-12e5-802d-8a7a-c02faf9e8334.png)

#### 5-4.  watsonx.dataコンソールに戻り、「照会ワークスペース」から再度以下のSQLを実行します。
```sql
 -- 件数カウント
 select count(*) from "hive_data"."hive_taxi"."ny_taxi_trip";
 -- tpep_pickup_datetimeのMIN, MAX値
select min(tpep_pickup_datetime) MIN_DATETIME ,max(tpep_pickup_datetime) MAX_DATETIME from "hive_data"."hive_taxi"."ny_taxi_trip";
```
件数は　5972150件となり、2月分が増えています
tpep_pickup_datetimeのMIN, MAX値 はそれぞれ、`2002-12-31 22:59:39.000`, `2024-03-01 00:01:37.000` となり、2月分が追加されたことがわかります。

このようにファイルを追加すればレコードが追加できます。

# 6. ファイル削除してみます
追加した `yellow_tripdata_2024-02.parquet`をICOS上から削除してみます。

#### 6-1. IBM Cloud Object Storage のコンソールで左側のメニューにある「インスタンス」ドロップダウンメニューをクリックし、Hiveカタログと関連づけられたクラウド・オブジェクト・ストレージ・インスタンスを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/5a28eb02-80a0-f5d3-e6f5-19ae27832351.png)

#### 6-2. ファイルを置いたバケット → hive_taxiフォルダー → ny_taxi_tripフォルダーをクリックして開きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/b97744c6-41fb-b9d2-7e28-f297ed5f7a80.png)

#### 6-3. `yellow_tripdata_2024-02.parquet`の右側の3点メニューをクリックし「削除」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/6a583e09-1966-ca87-5839-6da10592474f.png)

#### 6-4. ファイル名を確認し、「　完全に削除」と入力後、「削除」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/e6fbea13-bb8f-cc34-434b-1bfa4bd106d2.png)

削除されました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/8d31ea5d-d28c-e6ca-16bd-241366442a88.png)

#### 6-5.  watsonx.dataコンソールに戻り、「照会ワークスペース」から再度以下のSQLを実行します。
```sql
 -- 件数カウント
 select count(*) from "hive_data"."hive_taxi"."ny_taxi_trip";
 -- tpep_pickup_datetimeのMIN, MAX値
select min(tpep_pickup_datetime) MIN_DATETIME ,max(tpep_pickup_datetime) MAX_DATETIME from "hive_data"."hive_taxi"."ny_taxi_trip";
```

件数は　2964624件となり、2月分が削除され、1月分のみになりました。
tpep_pickup_datetimeのMIN, MAX値 はそれぞれ、`2002-12-31 22:59:39.000`, `2024-02-01 00:01:15.000` となり、最初の状態と同じにもどりました。

このようにファイル削除をすれば、その分のレコードも削除されます。

# 7. Hiveテーブルから、Icebergテーブルを作成

`hive_data.hive_taxi.ny_taxi_trip`テーブルから同じ内容でIcebergテーブルを作成します。

ここでは以下のIcebergテーブルを作成します。
- Icebergカタログ名: `iceberg_data`
- スキーマ名：`ic_taxi`
- テーブル名: `ny_taxi_trip`
を使います。

#### 7-1. スキーマを作成します
今回はコマンドで作成してみます。
watsonx.dataの「照会ワークスペース」から以下のSQLを実行します。
<バケット名>は自分のIcebergカタログのバケット名で置き換えてください。
```sql
create schema iceberg_data.ic_taxi with (location = 's3a://<バケット名>/ic_taxi/');
 
```

#### 7-2. `hive_data.hive_taxi.ny_taxi_trip`テーブルから同じ内容でIcebergテーブルを作成します
watsonx.dataの「照会ワークスペース」から以下のSQLを実行します。
```sql
create table iceberg_data.ic_taxi.ny_taxi_trip 
 as select * from hive_data.hive_taxi.ny_taxi_trip;
```
無事作成できました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/ae87b4cb-215d-c37a-d4e4-109a5cac6287.png)

# 8. Hiveテーブルから、IcebergテーブルにINSERT
Hiveテーブルから、既に作成済みのIcebergテーブルにINSERTしたい場合は、watsonx.dataの「照会ワークスペース」から以下のSQLを実行します。
```sql
insert into iceberg_data.ic_taxi.ny_taxi_trip 
 select * from hive_data.hive_taxi.ny_taxi_trip;
```
無事INSERTできました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/29f239bf-9c5b-7980-2278-566531744f9b.png)


# 9. まとめ
このようにHiveテーブルは設定すればフォルダーの中にファイルを置くだけで、テーブルとして使用できます。
さらにファイルを追加すればそのレコードが追加されますし、削除すればそのレコードが削除されます。
またそのHiveテーブルからIcebergテーブルも簡単に作成、INSERTできますので、データ取り込み方法の1つとして使用可能です。ぜひ試してみてください。

以上です。