hive date time issue

hive date time issue

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.