Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

timestamp not supported in HIVE

avatar
Expert Contributor

Hi,

Data is in files in the following format 2019-06-15T15:43:12 ( yyyy-MM-ddThh:mm:ss )

When I do a select * on the table , it is displayed as NULL

Hive version - 1.2.1

ALTER TABLE table SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss"); - did not help

Followed the links,

https://issues.apache.org/jira/browse/HIVE-9298

https://www.ericlin.me/2017/03/alternative-timestamp-support-in-hive-iso-8601/

Could you please help on this. Thanks

1 ACCEPTED SOLUTION

avatar
Master Guru

@Sampath Kumar

ALTER TABLE table SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss");

Works only in case of Textformat,CSV format tables.

If you are having other format table like orc..etc then set serde properties are not got to be working.

-

Tested by creating text format table:

Data:

1,2019-06-15T15:43:12
2,2019-06-15T15:43:19
create table i(id int,ts timestamp) row format delimited fields terminated by ',' stored as textfile;
ALTER TABLE i SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss");
 select * from i;
1       2019-06-15 15:43:12
2       2019-06-15 15:43:19

-

incase if we have orc file with 2019-06-15T15:43:12 format then altering the serde properties still results null format for timestamp field.

View solution in original post

3 REPLIES 3

avatar
Master Guru

@Sampath Kumar

ALTER TABLE table SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss");

Works only in case of Textformat,CSV format tables.

If you are having other format table like orc..etc then set serde properties are not got to be working.

-

Tested by creating text format table:

Data:

1,2019-06-15T15:43:12
2,2019-06-15T15:43:19
create table i(id int,ts timestamp) row format delimited fields terminated by ',' stored as textfile;
ALTER TABLE i SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss");
 select * from i;
1       2019-06-15 15:43:12
2       2019-06-15 15:43:19

-

incase if we have orc file with 2019-06-15T15:43:12 format then altering the serde properties still results null format for timestamp field.

avatar
Master Guru

@Sampath Kumar

Hive Timestamp type accepts format as yyyy-MM-dd HH:mm:ss[.SSS]

hive> select timestamp("2019-06-15 15:43:12");
2019-06-15 15:43:12
hive> select timestamp("2019-06-15 15:43:12.988");
2019-06-15 15:43:12.988
hive> select timestamp("2019-06-15T15:43:12")
NULL

If you are thinking to have timestamp type rather than text format tables then you use from_unixtime,unix_timestamp functions to remove "T" from the data and then you can have timestamp type in all formats.

-

If the answer is helpful to resolve the issue, Login and Click on Accept button below to close this thread.This will help other community users to find answers quickly 🙂


avatar
Expert Contributor

@Shu: Thank you. Could you please let us know, If we are using file format other than Textformat, CSV, how to handle null for timestamp filed?