question Re: hive date time problem in Archives of Support Questions (Read Only)

hive date time problem

Former Member — Mon, 19 Aug 2019 08:24:04 GMT

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.

Re: hive date time problem

pminovic — Wed, 07 Sep 2016 18:36:16 GMT

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

Re: hive date time problem

Former Member — Mon, 12 Sep 2016 12:22:25 GMT

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

Re: hive date time problem

atulwagh01 — Tue, 13 Sep 2016 20:34:23 GMT

Hi Swathi,

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