Skip to main content
sampathkumar_ma
sampathkumar_ma
Gold ⭐️
June 16, 2019
Solved

timestamp not supported in HIVE

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

Shu_ashu
Shu_ashuAnswer
Gold ⭐️⭐️⭐️
June 17, 2019

@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 in thread

3 replies

Shu_ashu
Shu_ashuAnswer
Gold ⭐️⭐️⭐️
June 17, 2019

@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.

Translate
    Shu_ashu
    Shu_ashu
    Gold ⭐️⭐️⭐️
    June 19, 2019

    @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 🙂


    Translate
    sampathkumar_ma
    sampathkumar_maAuthor
    Gold ⭐️
    June 19, 2019

    @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?

    Translate
    Terms & ConditionsAccessibility statement