watsonx.data:PrestoでCSVをデータとしたHive外部テーブル作成してみた

## はじめに
watsonx.dataはPrestoエンジンを使用してHive Connectorでテーブルを作成することができます。Hive Connectorではテーブル作成時、オプションのWITH句を使用して、テーブルに対するプロパティを設定できます。今回はCSV形式のデータを使って表を作成します。

CSV形式のファイルにはよくフィールドを説明する行がヘッダーとしてつけられています。しかし、Hive表ではすべての行をデータとして扱うため それらの行をスキップする(無視する)必要があります。この場合、skip_header_line_countというプロパティをオプションが使えます。このプロパティを使用すると、不要なヘッダーをスキップでき、不規則なデータの処理に役立ちます。

## 環境
使用した環境は以下になります。
・Red Hat OpenShift Container Platform 4.14
・IBM Cloud Pak for Data (CP4D) 5.0.2
・IBM watsonx.data 2.0.2
・Presto (Java) v0.286

## skip_header_line_countを利用してCSVファイルのヘッダーをスキップしたテーブル作成
### 1.データの準備
CSV形式のファイルにはよくフィールドを説明する行がヘッダーとしてつけられています。例えば、[東京気象庁](https://www.data.jma.go.jp/obd/stats/etrn/select/prefecture.php?prec_no=44)から直接気象データをダウンロードすると、ヘッダー付きのデータが取得されます。テストのために、以下の簡易版の気象データ`data_weather.csv`を使用しました。
```
ダウンロードした時刻：2024/05/10 03:33:11,,,
,,,
年月日,平均気温(℃),平均気温,気温
2024/4/9,17.9,8,1
2024/4/10,14.5,8,1
2024/4/11,15.0,8,1
2024/4/12,16.3,8,1
2024/4/13,17.0,8,1
2024/4/14,17.5,8,1
2024/4/15,16.3,8,1
2024/4/16,17.0,8,1
2024/4/17,18.2,8,1
2024/4/18,16.1,8,1
2024/4/19,16.7,8,1
```
この気象データはテーブルの形式で見ると、図のようになります。
![Screenshot 2024-09-26 at 2.53.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/bb0c525f-020a-504a-aee3-7870fd774eba.png)

### 2.準備したデータをwatsonx.dataのストレージへアップロードする
今回はwatsonx.dataに標準で付属しているMinIO オブジェクト・ストレージを使います。[Accessing the MinIO console](https://www.ibm.com/docs/en/watsonx/watsonxdata/2.0.x?topic=administering-accessing-minio-console)を参考にしてS3 credentialsなどを設定し、MinIOのユーザーインターフェースを通じてストレージにアクセスします。

MinIOのhive-bucketの下にCreate new pathで新しいパス`skip_header`を作ります。

![Screenshot 2024-10-01 at 13.14.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/baf68374-8b05-5064-a9bb-db6bda6bfe9d.png)

Upload Fileをクリックして、ローカルPCから準備した`data_weather.csv`を`skip_header`の下にアップロードします。

![Screenshot 2024-09-26 at 6.09.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/f69ce784-7a83-5b50-1c6a-315646e2415d.png)
`data_weather.csv`をアップロードした後、Create new pathボタンの隣のボタンをクリックしてファイルのパスをコピーしてください。これがファイルをHive外部テーブルとして使用するためのパスです。

![Screenshot 2024-09-26 at 6.17.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/549b7082-ecc2-f7b1-1c88-76b96c5e07a5.png)

### ３.CREATE TABLE statementのSQLを実行する
[テーブル作成の構文](https://qiita.com/Xu_Mingqin/items/8236aeb6756f66055715#1watsonxdataprestoでテーブル作成の構文)に基づいてSQLを作成します。external_locationには先ほどコピーした`data_weather.csv`のパスを指定し、`data_weather.csv`の特性に基づいてcsv_separatorは`,`を指定し、formatは`CSV`と指定します。

また、今回準備したデータ`data_weather.csv`は3行ヘッダーをスキップする必要があります。
![68747470733a2f2f71696974612d696d6167652d73746f72652e73332e61702d6e6f727468656173742d312e616d617a6f6e6177732e636f6d2f302f333839343238342f62623063353235662d303230612d353034612d616565332d3738373066643737346562612e706e67.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/1e0539a8-57c0-c31d-8836-f65cb108f3e1.png)
図のように、囲まれたヘッダーをスキップするために　`skip_header_line_count = 3`を指定して、以下のSQLを作成しました。
```
USE "hive_data"."test";
CREATE TABLE data_skip_header (																   "年月日_new_header" varchar,																						
	   "平均気温(℃)_new_header" varchar,																						
	   "平均気温_new_header" varchar,																						
	   "気温_new_header" varchar
	   ) WITH (
	    external_location = 's3a://hive-bucket/skip_header',
	    csv_separator=',',
	    format = 'CSV',
	    skip_header_line_count = 3
	    );
```
watsonx.dataの照会ワークスペース（Query workspace）で上のSQLを実行しました。

![Screenshot 2024-09-30 at 15.55.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/4652fb98-52b5-e02a-c2bd-be6b6043532d.png)



結果を見ると、うまく三行をスキップできたことが確認できました。
![Screenshot 2024-09-26 at 8.54.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/1df54f1f-4456-624d-6b75-3e34f891761c.png)


## 失敗例
### skip_header_line_countの値にはシングルクォートを使用できない件
以下のSQLを実行したら、`Invalid value for table property 'skip_header_line_count': Cannot convert ''3'' to integer`というエラーが出ました。シングルクォートで囲む必要はありません。
```
USE "hive_data"."test";
CREATE TABLE data_skip_header (																   "年月日_new_header" varchar,																						
	   "平均気温(℃)_new_header" varchar,																						
	   "平均気温_new_header" varchar,																						
	   "気温_new_header" varchar
	   ) WITH (
	    external_location = 's3a://hive-bucket/skip_header',
	    csv_separator=',',
	    format = 'CSV',
	    skip_header_line_count = '3'
	    );
```
## 付録　

### 1.watsonx.data:Prestoでテーブル作成の構文
watsonx.dataで、照会マネージャー(Query Manager) に「Create table」のSQL文を入力し、Prestoエンジンを指定すると、テーブルを作成できます。テーブル作成の構文は以下の通りです。
```
CREATE TABLE [ IF NOT EXISTS ]
table_name (
  { column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
  | LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ] }
  [, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
```
構文の詳細については、次をご参照ください：[CREATE TABLE statement](https://www.ibm.com/docs/en/watsonx/watsonxdata/2.0.x?topic=statements-create-table)。

### 2.テーブル作成のオプション
watsonx.data の照会マネージャー(Query Manager) で`SELECT * FROM system.metadata.table_properties where catalog_name = 'hive_data';`を実行すると、with句で使用可能なプロパティが表示されています。
![Screenshot 2024-10-01 at 13.58.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/3b50c2b8-e996-598e-c8f9-30aa852f2acb.png)
![Screenshot 2024-10-01 at 13.58.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3894284/9657ebea-8c4e-7fa6-29b3-2fa67260d2b2.png)
今回検証したプロパティは`skip_header_line_count`です。上の図の情報によると、`skip_header_line_count`はスキップできるヘッダー行数を指定します。また、整数で指定する必要があります。