hive date time issue

  • 0

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;

7404-untitled

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.


Leave a Reply