watsonx.dataのPrestoエンジンでHive/Icebergのパーティション表を作成する

## 0. 前提
各手順の確認には、SW版のwatsonx.data および Prestoエンジンを利用しています。
また、カタログとバケットのペアおよびスキーマは、事前に追加済みのものを利用しています。

| カタログ名 | カタログ用バケット| 検証用スキーマ|
| --- | --- | --- | 
| hive_data | hive-bucket | hive_test_01 |  
| iceberg_data | iceberg-bucket | iceberg_test_01 |  

## 1. テーブル作成オプションの確認方法

watsonx.data/Prestoではテーブル作成時、オプションのWITH句を使用して、テーブルに対するプロパティを設定できます。

```text: CREATE TABLE のシンタックス
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 [, ...] ) ]
```

オプションのWITH句で指定可能なプロパティは、システム・テーブル `system.metadata.table_properties` から確認します。

``` sql: テーブルで指定可能なプロパティ一覧
SELECT * FROM system.metadata.table_properties;
```
出力結果へ下記項目が含まれます。
- catalog_name:  カタログタイプが含まれます。`hive_data` または `iceberg_data` です。
- property_name: CREATE TABLEステートメントのWITH句で指定可能なプロパティ名です。
- default_value: デフォルトのプロパティ値です。　
- type:          プロパティ値の指定方法です。
- description:   プロパティ名の説明です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/a26aa5a6-028e-5be0-1d95-de9f33ae5e58.png)

watsonx.data製品資料: CREATE TABLE statement
https://www.ibm.com/docs/en/watsonxdata/1.1.x?topic=statements-create-table

## 2. パーティション表の作成

### 1. Hiveテーブル

#### ① テーブルのプロパティ確認

まず、CREATE TABLE のWITH句で指定するため、パーティション表のプロパティを確認します。
システム・テーブル `system.metadata.table_properties`を照会する際、WHERE条件で `catalog_name='hive_data' AND property_name LIKE '%partition%'` とすることで、Hiveテーブル向けかつパーティション関連のプロパティに絞り込みます。

``` sql: テーブルのプロパティ一覧からHiveテーブルのパーティション表オプションを探す
SELECT * 
FROM system.metadata.table_properties 
WHERE 
    catalog_name='hive_data' AND
    property_name LIKE '%partition%'
;
```

プロパティ名 `partitioned_by` がヒットしました。
CREATE TABLE のWITH句で、このプロパティを指定すれば良さそうです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/cb127d7c-6c68-ab66-4bc9-39e4e1a58b37.png)

#### ② テーブル作成

パーティション表を作成します。
Hiveテーブルにけるパーティションキーは、テーブルの最後の列位置（右端）で、テーブルのプロパティと同じ順序であることが必要です。
例では、year/month/day の3列をパーティションキーとして指定します。

``` sql:Hiveテーブル：WITH句でpartitioned_byを指定
/*Hiveテーブルのパーティション表作成*/
CREATE TABLE hive_data.hive_test_01.hive_sample_partition_table(
  id INT,
  val_1 INT,
  val_2 DECIMAL,
  val_3 DECIMAL,
  measure_timestamp TIMESTAMP,
  year INT,
  month INT,
  day INT
) WITH (
  partitioned_by = ARRAY ['year', 'month', 'day']
);
```

パーティション表が作成されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/3264c489-67ee-b91f-7029-e47affaf28cf.png)

##### パーティション表作成の失敗例

パーティションキーはテーブルの最後の列位置（右端）で、テーブルのプロパティと同じ順序であることが必要ですが、試しにパーティションキーの列位置を中間に配置したCREATE TABLE文を実行してみます。

``` sql: （失敗例）Hiveテーブル：WITH句でpartitioned_byを指定
/*Hiveテーブルのパーティション表作成*/ -- エラー
CREATE TABLE hive_data.hive_test_01.hive_sample_partition_table2(
  id INT,
  val_1 INT,
  val_2 DECIMAL,
  val_3 DECIMAL,
  year INT,
  month INT,
  day INT,
  measure_timestamp TIMESTAMP
) WITH (
  partitioned_by = ARRAY ['year', 'month', 'day']
);
```
やはりテーブル作成は失敗し、下記のエラーメッセージが返されました。

`Partition keys must be the last columns in the table and in the same order as the table properties: [year, month, day] 
`
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/07c78063-45fb-47c8-b336-ab38106db517.png)

#### ③ テーブルへデータを挿入

パーティションキー day に、それぞれ異なる値を指定した行をINSERTします。

```sql:HiveテーブルへのINSERT
-- パーティションキーdayに1を指定
INSERT INTO hive_data.hive_test_01.hive_sample_partition_table VALUES
(1, 10, 0, 100, timestamp '2023-11-1 1:00:00.000', 2023, 11, 1),
(2, 20, 0, 200, timestamp '2023-11-1 2:00:00.000', 2023, 11, 1);

-- パーティションキーdayに3を指定
INSERT INTO hive_data.hive_test_01.hive_sample_partition_table VALUES
(3, 30, 0, 300, timestamp '2023-11-3 3:00:00.000', 2023, 11, 3);
```

#### ④ パーティションの確認

Hiveテーブルのデータファイルは、バケット上に格納されています。
テーブル用のパス配下を確認すると、パーティションキーの値に応じてさらにパスが分割されています。

パーティションキー1階層目 year
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/beb4bcf3-fc7f-9218-5322-ed0f42f798ed.png)

パーティションキー2階層目 month
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/d4aa1ff4-9841-1e4c-c24e-fcfe8b8a9d9c.png)

パーティションキー3階層目 day
day=1とday=3に分かれていることが確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/212b507b-6a94-f4ae-8022-729dc60e377f.png)

パーティションキーで分割されたパスの最下層へ、データファイルの実体が配置されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/d3ce9c19-0569-620e-f267-b57aa2a6394f.png)


### 2. Icebergテーブル

#### ① テーブルのプロパティ確認

まず、CREATE TABLE のWITH句で指定するため、パーティション表のプロパティを確認します。
システム・テーブル `system.metadata.table_properties`を照会する際、WHERE条件で `catalog_name='iceberg_data' AND property_name LIKE '%partition%'` とすることで、Icebergテーブル向けかつパーティション関連のプロパティに絞り込みます。

``` sql: テーブルのプロパティ一覧からicebergテーブルのパーティション表オプションを探す
SELECT * 
FROM system.metadata.table_properties 
WHERE 
    catalog_name='iceberg_data' AND
    property_name LIKE '%partition%'
;
```

プロパティ名 `partitioning` がヒットしました。
CREATE TABLE のWITH句で、このプロパティを指定すれば良さそうです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/d0f7191f-6e0c-a471-779b-c2ebbb0bcf72.png)

#### ② テーブル作成

パーティション表を作成します。
先述のHiveテーブルでは、パーティションキーがテーブルの最後の列位置（右端）で、テーブルのプロパティと同じ順序であることが必要でした。
一方、今回作成するIcebergテーブルではこの制約はありません。パーティションキーの列位置を中間に配置することが可能です。
例では、year/month/day の3列をパーティションキーとして指定します。

``` sql: Icebergテーブル（パーティション表）：WITH句でpartitioningを指定
/*Icebergテーブルのパーティション表作成*/
CREATE TABLE iceberg_data.iceberg_test_01.iceberg_sample_partition_table(
  id INT,
  val_1 INT,
  val_2 DECIMAL,
  val_3 DECIMAL,
  year INT,
  month INT,
  day INT,
  measure_timestamp TIMESTAMP
) WITH (
  partitioning = ARRAY ['year', 'month', 'day']
);
```
パーティション表が作成されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/69acaf47-1ad6-b8ca-3a50-f761055ed805.png)


#### ③ テーブルへデータを挿入

パーティションキー day に、それぞれ異なる値を指定した行をINSERTします。

```sql:Iceberg通常表
-- パーティションキーdayに1を指定
INSERT INTO iceberg_data.iceberg_test_01.iceberg_sample_partition_table VALUES
(1, 10, 0.1, 100, 2023, 11, 1, timestamp '2023-11-1 1:00:00.000'),
(2, 20, 0.2, 200, 2023, 11, 1, timestamp '2023-11-1 2:00:00.000');

-- パーティションキーdayに3を指定
INSERT INTO iceberg_data.iceberg_test_01.iceberg_sample_partition_table VALUES
(3, 30, 0.3, 300, 2023, 11, 3, timestamp '2023-11-3 3:00:00.000');
```

#### ④ パーティションの確認

Icebergテーブルのデータファイルは、バケット上に格納されています。
まず、テーブル用のパス配下にmetadataとdataフォルダが存在します。このうちdataフォルダを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/fa49f482-93c7-2d86-8a46-a0377f015d16.png)

dataフォルダを確認すると、パーティションキーの値に応じてさらにパスが分割されています。

パーティションキー1階層目 year
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/9eca39a6-7762-f2cf-9087-79e9a80aa9d1.png)

パーティションキー2階層目 month
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/9f979405-c9f3-48df-cf6c-22b4d3301769.png)

パーティションキー3階層目 day
day=1とday=3に分かれていることが確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/ec74ee6e-e945-6998-b085-3336f3dd3476.png)

パーティションキーで分割されたパスの最下層へ、データファイルの実体が配置されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/237849/f1f11e62-d24a-65c6-c84f-f5c7b701910b.png)

## 3. 関連資料

watsonx.data製品資料: CREATE TABLE statement
https://www.ibm.com/docs/en/watsonxdata/1.1.x?topic=statements-create-table

Presto製品資料：CREATE TABLE
https://prestodb.io/docs/current/sql/create-table.html

Apache Iceberg製品資料：Partitioning
https://iceberg.apache.org/docs/latest/partitioning/