hive date time issue
Category : Hive
Many time when we load data into hive tables and if we have a date & time field in our data then we may have seen an issue with getting data field. So to solve this issue I have created this article and explained steps in details.
I have the following sample input file(a.txt)
a,20-11-2015 22:07
b,17-08-2015 09:45
I created the table in hive
hive> create table mytime(a string, b timestamp) row format delimited fields terminated by ‘,’;
hive> load data local inpath ‘/root/a.txt’ overwrite into table mytime;
hive> select* from mytime;
So now you saw we will get null value only. To overcome this situation we need an additional, temporary table to read your input file, and then some date conversion:
hive> create table tmp(a string, b string) row format delimited fields terminated by ‘,’;
hive> load data local inpath ‘a.txt’ overwrite into table tmp;
hive> create table mytime(a string, b timestamp);
hive> insert into table mytime select a, from_unixtime(unix_timestamp(b, ‘dd-MM-yyyy HH:mm’)) from tmp;
hive> select * from mytime;
a 2015-11-20 22:07:00
b 2015-08-17 09:45:00
I hope this article will help you to get your work done in effective manner.