hive create table having timestamp with timezone data type in Athena

[#1]

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/'

[#2 · Accepted solution]

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/'