はじめに
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形式のファイルにはよくフィールドを説明する行がヘッダーとしてつけられています。例えば、東京気象庁から直接気象データをダウンロードすると、ヘッダー付きのデータが取得されます。テストのために、以下の簡易版の気象データ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
この気象データはテーブルの形式で見ると、図のようになります。
2.準備したデータをwatsonx.dataのストレージへアップロードする
今回はwatsonx.dataに標準で付属しているMinIO オブジェクト・ストレージを使います。Accessing the MinIO consoleを参考にしてS3 credentialsなどを設定し、MinIOのユーザーインターフェースを通じてストレージにアクセスします。
MinIOのhive-bucketの下にCreate new pathで新しいパスskip_headerを作ります。
Upload Fileをクリックして、ローカルPCから準備したdata_weather.csvをskip_headerの下にアップロードします。
data_weather.csvをアップロードした後、Create new pathボタンの隣のボタンをクリックしてファイルのパスをコピーしてください。これがファイルをHive外部テーブルとして使用するためのパスです。
３.CREATE TABLE statementのSQLを実行する
テーブル作成の構文に基づいてSQLを作成します。external_locationには先ほどコピーしたdata_weather.csvのパスを指定し、data_weather.csvの特性に基づいてcsv_separatorは,を指定し、formatはCSVと指定します。
また、今回準備したデータdata_weather.csvは3行ヘッダーをスキップする必要があります。
図のように、囲まれたヘッダーをスキップするために 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を実行しました。
結果を見ると、うまく三行をスキップできたことが確認できました。
失敗例
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。
2.テーブル作成のオプション
watsonx.data の照会マネージャー(Query Manager) でSELECT * FROM system.metadata.table_properties where catalog_name = 'hive_data';を実行すると、with句で使用可能なプロパティが表示されています。
今回検証したプロパティはskip_header_line_countです。上の図の情報によると、skip_header_line_countはスキップできるヘッダー行数を指定します。また、整数で指定する必要があります。