Support Questions

Find answers, ask questions, and share your expertise

hive date time problem

avatar

i have problem, when i am loading data in hive table

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

create table mytime(a string, b timestamp) row format delimited fields terminated by ',';

load data local inpath 'a.txt' overwrite into table mytime;

select* from mytime;

7404-untitled.png

it showing null values.

please give me suggestion to solve this problem.

thanks in advance.

1 ACCEPTED SOLUTION

avatar
Master Guru

You 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

View solution in original post

3 REPLIES 3

avatar
Master Guru

You 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

avatar

hi all

i have problem, when i am loading the multiple date format data in single file to hive table

i have the following sample input file(a.txt)

a|20-11-2015 22:07

b|wed, jul 23, '04

c|2016-05-11T05:04:25+0000

d|wed 2016-05-11T05:04:25+0000

i created the table in hive

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;

by using above query only one format date is loaded remaining format date shows null.

how to load if single file having different date formats as shown in above a.txt to hive tables

please try to resolve this problem

thanks in advance

with regards,

swathi

avatar
New Contributor

Hi Swathi,

As per my understanding, source date should be in any one format. Then only date conversion will work properly.