Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

hive date time problem

Solved Go to solution
Highlighted

hive date time problem

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

Accepted Solutions
Highlighted

Re: hive date time problem

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
Highlighted

Re: hive date time problem

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

Highlighted

Re: hive date time problem

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

Highlighted

Re: hive date time problem

New Contributor

Hi Swathi,

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

Don't have an account?
Coming from Hortonworks? Activate your account here