前提
クエリエンジンとしてHiveを使用しています。
Timestamp型文字列のカラムを含んだcsv/tsvファイルをHDFS上に配置しテーブルに読み込んだのち、そのカラムを別テーブルに挿入する処理が必要になった時の話です。
csv/tsvファイルのTimestamp型文字列をそのままHiveのTimestamp型として読み込めるかと思ったのですが、少し工夫が必要でした。その具体的な工夫について記載します。
結論
- Hive 1.2.0 以降を使用しているなら、https://issues.apache.org/jira/browse/HIVE-9298 に記載がある通りTimestampのフォーマットをテーブルごとに設定します。
- それ以前の場合、Timestamp型文字列のカラムを含んだcsv/tsvファイルを外部テーブルとして読み込むにはunixtimeに変換して出力すると良いです。そして別テーブルに挿入する際にHiveのクエリでTimestamp型に変換してやります。
方法
1. Hive 1.2.0 以降を使用している場合
テーブルが作成されている場合、以下のようにcsv/tsvファイルを読み込むテーブルのserdepropertyとしてTimestampの形式を設定してやります。
ALTER TABLE hogehoge_table SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd HH:mm:ss");
※ただし "yyyy-MM-dd HH:mm:ss" の部分はcsv/tsvファイルに出力したTimestampの形式に応じて設定する
これからテーブルを作成する場合、DDLで設定するのが良さそうです。
CREATE EXTERNAL TABLE hogehoge_table (
column_1 STRING,
column_2 INT,
target_column TIMESTAMP
)
PARTITIONED BY (
partition_column STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
WITH SERDEPROPERTIES (
"timestamp.formats"="yyyy-MM-dd HH:mm:ss"
)
LOCATION '<your hdfs file location>'
;
いずれにしてもtarget_columnはHiveのTimestamp形式としてSELECTできます。
2. それ以前の場合
csv/tsvファイルを読み込むテーブルの作成時、Timestamp型文字列ではなくunixtime形式で設定しておきます。
CREATE EXTERNAL TABLE hogehoge_table (
column_1 STRING,
column_2 INT,
target_column BIGINT
)
PARTITIONED BY (
partition_column STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
LOCATION '<your hdfs file location>'
;
別テーブルへの挿入時のSELECT文で変換してやります。
INSERT OVERWRITE TABLE fugafuga_table
PARTITION (partition_column = 'aaa')
SELECT
column_1
,column_2
,CAST(FROM_UNIXTIME(target_column, 'yyyy-MM-dd HH:mm:ss') AS TIMESTAMP) AS target_column
FROM hogehoge_table
WHERE partition_column = 'aaa'
最後に
HiveのTips記事でした。
Timestamp型と上手く付き合っていきたいですね。
参考
https://issues.apache.org/jira/browse/HIVE-9298
https://stackoverflow.com/questions/62607279/how-to-load-a-csv-file-containing-time-string-value-to-timestamp-in-hive
https://community.cloudera.com/t5/Support-Questions/timestamp-not-supported-in-HIVE/td-p/242638