hive create table having timestamp with timezone data type in Athena

prakharjain — Fri, 18 Oct 2019 16:32:39 GMT

I tried to find solution that fits my use case and tried many things, but failed.

Please help me. Thank you in advance.

I have input in following format:

12, 2019-07-08 00:02:54.436062+00

23, 2019-07-08 00:48:41.23138+00

how do i create table with timestamp datatype for 2nd column. Please don't suggest regexSerDe. (because this is dummy example, my test string is very complex)

In all my input data, all values in 2nd col have '+00' in the end. So, I don't even have to convert to any timezone, i can just skip last 3 characters when parsing. Is there any way to do that?

What I have tried

CREATE EXTERNAL TABLE test (

id int,

ts timestamp)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ','
)

LOCATION 's3://user/'

Re: hive create table having timestamp with timezone data type in Athena

prakharjain — Fri, 18 Oct 2019 16:44:48 GMT

Solved it using,

CREATE EXTERNAL TABLE test (

id int,

ts timestamp)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ',',

"timestamp.formats"="yyyy-MM-dd HH:mm:ss.SSSSSS+00"
)

LOCATION 's3://user/'

question hive create table having timestamp with timezone data type in Athena in Support Questions

hive create table having timestamp with timezone data type in Athena

Re: hive create table having timestamp with timezone data type in Athena