athena.read_sql_query fails with timestamp precision error against iceberg tables on athena engine 3

[mikemac-n0]

Describe the bug

I'm encountering errors like:

QueryFailed: NOT_SUPPORTED: Incorrect timestamp precision for timestamp(6); the configured precision is MILLISECONDS. You may need to manually clean the data at location ...

when running the wr.athena.read_sql_query method with the default settings against an Iceberg Athena table.

The error disappears if I switch to ctas_approach=False against Iceberg tables, i.e. the query succeeds as expected. The error does continue to occur with unload_approach=True and ctas_approach=False set.

My queries against the same Iceberg tables succeed from the Athena web ui, and from the aws cli.

I'm not seeing this error against regular non-Iceberg Athena tables.

Of note: I switched to Athena engine 3 yesterday. These errors were not observed prior to this.

The package version was initially 2.* when the errors were first encountered. I upgraded to aws-sdk-pandas 3.0.0 and the error behavior continues.

How to Reproduce

Run a query against an Iceberg table with wr.athena.read_sql_query's default settings, on AWS Athena engine 3.

Expected behavior

A pandas dataframe would be returned, instead of a traceback.

Your project

No response

Screenshots

No response

OS

mac

Python version

3.10.9

AWS SDK for pandas version

2.*/3.0.0

Additional context

No response

[jaidisido]

Thanks for the detailed report @mikemac-n0. I did some research and it seems that the underlying issue is with Trino (the successor to Presto used in Athena engine v3). There is an issue raised in their repo: trinodb/trino#6658 which seems specific to Iceberg tables.

When using wr.athena.read_sql, CTAS approach is used by default (for performance reasons) and your query is wrapped in a CTAS statement. For example:

CREATE TABLE "default"."temp_table_5873d373f8f54f5492304414ca54b20e"
WITH(
    external_location = 's3://bucket/temp_table_5873d373f8f54f5492304414ca54b20e',
    format = 'PARQUET')
AS SELECT timestamp FROM "test_2252"

which raises the error you mentioned (in Athena v3 only).

By switching to ctas_approach=False, the API reverts to a simple select statement:

SELECT timestamp FROM "test_2252"

and it succeeds.

The workaround suggested in their issue is to change the timestamp precision but I am not aware of a way to do this with Glue/Athena. So the only options I see are to 1) stick with engine v2 2) not use ctas approach or 3) cast the timestamp in your SELECT query statement

[mikemac-n0]

Thanks very much @jaidisido , that clarifies things. I appreciate the practical suggestions for working around the issue.

This doesn't seem like an issue with aws-sdk-pandas then. Per the "I am not aware of a way to do this with Glue/Athena" would you think it reasonable (for me) to file a ticket with the Athena team, or does this mainly seem to be at the trino level?

Thanks again!

[jaidisido]

My hunch is that it's more of a Trino level issue but there might be a workaround in Glue/Athena that I am not aware perhaps

[jaidisido]

Closing this for now, but feel free to reopen if you hear of a workaround that we can implement, thanks

[sfgarcia]

Hi @jaidisido. The issue you mentioned trinodb/trino#6658 was fixed with this PR trinodb/trino#13981

Do you know if the issue with aws-sdk-pandas should also be solved? I'm not sure what is the Trino version AWS Athena uses. The documentation mentions: "For engine version 3, Athena has introduced a continuous integration approach to open source software management that improves concurrency with the Trino" (https://docs.aws.amazon.com/athena/latest/ug/engine-versions-reference-0003.html). I'm not sure if that implies that Athena engine version 3 always uses the last Trino version? Because the current Trino version is 451 and the issue was solved in version 429 (trinodb/trino#19357)

I ask this question because at our team we opted for set ctas_approach = False to avoid this bug, but I wanted to know if we could switch back to ctas_approach = True and improve the performance of the queries we are running

[andreasbolstad]

This is still an issue. My team is using the non ctas approach as a workaround.

Should this issue be reopened? The Trino fix is almost a year old by now, and still not synced with Athena, apparently.

[giacomocolonettizup]

Stills an issue as of today...

[sutsr]

My team has also hit this issue and spent a significant amount of time exploring workarounds. After arriving at a solution we're happy with and not seeing it mentioned, I figured I'd share it.

CTAS = False did not work for us, due to limitations of the CSV format used as an intermediate representation:

• Complex columns, e.g. arrays of structs, are cast to strings of format "[{key1=value1, key2=value2}]" which makes them difficult to rectify, and especially intractable when any value is a string containing apostrophes
  • This can be worked around by casting the complex columns to JSON objects in the query, but then you must fully specify the selected columns, which was an issue for us due to the number of columns being retrieved
• Loss of ability to distinguish between nulls and empty strings, as they have the same representation in CSV

The approach we chose was to use CTAS = True, and cast the timestamps to varchar in the query, which conveniently yields ISO8601 formatted strings.

This approach offers the distinct advantage of preserving all column data type information apart from timestamp types, because the intermediate representation is Parquet, not CSV. Re-parsing the ISO8601 strings back to timestamps is easily done as a subsequent step.