STACK -
HDP 3.1.0.0 Hive 3.1.0
Trying to store timestamps like **2020-01-03T02:46:21.148+02:00** in an **ORC** hive table.
Storing the timestamps using the timestamp datatype gives NULL on querying which is expected as this is not the format hive expects it's timestamps to be.
But as per the documentation if we set the appropriate timestamp format serde property then hive should be able to read the timezones.
*"On the table level, alternative timestamp formats can be supported by providing the format to the SerDe property "timestamp.formats" (as of release 1.2.0 with HIVE-9298). For example, yyyy-MM-dd'T'HH:mm:ss.SSS,yyyy-MM-dd'T'HH:mm:ss."*
However despite **ALTER TABLE <tablename> SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss.SSSXXXXX");** the property seems to have no effect and hive is still not able to read it. I tried multiple variations of the format but none worked.
Creating the table as textfile/CSV doesn't help either
To replicate -
create table default.test_tz (tt timestamp) stored as orc; insert into default.test_tz values ("2020-01-03T02:46:21.148+02:00"), ("2017-02-16T11:24:29.000Z"), ("2017-02-16 11:24:29"), ("2019-06-15T15:43:19"); ALTER TABLE default.test_tz SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss.SSSXXXXX");
**Should I just store it as string? What is the best practice in such cases.**
... View more
