hive date time problem

[#1]

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;

it showing null values.

please give me suggestion to solve this problem.

thanks in advance.

[#2 · Accepted solution]

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

[#3]

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

[#4]

Hi Swathi,

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