HiveでISO8601形式の時刻データを扱う
連日イカに潜っているため,すっかりご無沙汰になっている当ブログです.今回は小ネタ.
HDFS上に保存しているデータの日付カラムがISO8601形式だったりすることがよくあるんですけど,これってHiveのtimestamp型で読み込めないんですね.蜂初心者なので全く知りませんでした.なのでテーブルスキーマだとstringで扱うしかないのが辛いところ.とはいえクエリの段ではtimestampに変換したいですね,というのが今回のお話,
ちなみにISO8601ってこういうやつですね.Tとタイムゾーンが入ってるのが特徴.
2015-01-02T12:34:56+09:00
どうやら組み込み関数ではISO8601は取り扱いできないので,UDF使えばいけそうであると.このサイトにあるように,以下のような形でさくっと変換できます*1.
ADD JAR hdfs:///external-jars/commons-codec-1.9.jar; ADD JAR hdfs:///external-jars/joda-time-2.2.jar; ADD JAR hdfs:///external-jars/sm-hive-udf-1.0-SNAPSHOT.jar; SELECT from_unixtime(iso8601_to_unix_timestamp(target_date), 'yyyy-MM-dd-HH-mm-ss') FROM test_table;
でもUDF読むのも面倒だったので,組み込み関数組み合わせてこんな感じで凌ぐと.Hiveには正規表現使わないreplace関数はないんですね.まぁMapReduce噛ませるんだから,普通のreplaceでも正規表現使ったreplaceでも大差ないじゃんってことなのかなぁと想像*2.
SELECT CAST(regexp_replace(substr(target_date, 0, 19), 'T', ' ') AS timestamp) AS target_date FROM test_table;
そんだけです.