読者です 読者をやめる 読者になる 読者になる

About connecting the dots.

statistics/machine learning adversaria.

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;

そんだけです.

*1:target_dateはISO8601形式のstring型カラムになります

*2:でも計算コストはソコソコ違ってくると思うんですけどね... 正規表現処理って結構重たいし.