Athena version 3 - Timestamp column causes serialization error for parquet data format

Question

We ran into the serialization error of the timestamp field after switching to Athena version 3 for the parquet data files.
Using Athena version 2, the same parquet data is read without errors. Also, before opening this issue, we ensured that our data corresponded to all the described recommendations from the Athena 3 guide.

**Error:** `SERIALIZATION_ERROR: Could not serialize column 'timestamp' of type 'timestamp(3)' at position 1:1`

Below is the suggested solution to the problem from the documentation:

_**Precision mismatch in Timestamp columns causes serialization error**_
`Error message: SERIALIZATION_ERROR: Could not serialize column 'COLUMNZ' of type 'timestamp(3)' at position X:Y`

_**Cause**: Athena engine version 3 checks to make sure that the precision of timestamps in the data is the same as the precision specified for the column data type in the table specification. Currently, this precision is always 3. If the data has a precision greater than this, queries fail with the error noted._

_**Suggested solution**: Check your data to make sure that your timestamps have millisecond precision._

In our parquet data, we use a timestamp field with Unix time format, which has millisecond precision:
`"timestamp": 1688479202968`

**Example of our parquet data and DDL statement for creating Athena table:**
```json
{
  "id": "23020733",
  "timestamp": 1688479202968,
  "receiveTimestamp": 1688479203118
   ...
}
```
```sql
CREATE EXTERNAL TABLE `sample_table`(
  `id` string, 
  `timestamp` timestamp, 
  `receivetimestamp` timestamp 
   )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://...'
```

It is critical for us to ensure the normal functioning of the Athena query without changing existing requests or the parquet files.

At this stage, we have switched Athena to version 2 for all our services and are still working with it.

What is the correct description of this field in the Athena table? If it's a bug on WAS side, how could I report it to them?

Answer

This seems to be the expected behavior perhaps. Please refer [Athen Version History](https://docs.aws.amazon.com/athena/latest/ug/release-notes.html) and search for "Precision", you'll see all the changes related to timestamp precision and will give you an idea about the difference in behavior between version-2 and version-3.

However if you still think, this is a bug then first point of contact would be AWS Support and I'd suggest you to [log a case](https://docs.aws.amazon.com/awssupport/latest/user/case-management.html) under "Technical category" with AWS support describing the situation and they will certainly help you and provide additional context and guidance. Note that, you'll only be able to log a support case if you have a support plan which can let you create the case, basic support plan doesn't come with that ability.

Hope this helps.

Comment here if you have additional questions, happy to help.

Abhishek

Athena version 3 - Timestamp column causes serialization error for parquet data format

Relevant content