Skip to content

ATHENA INTERNAL_ERROR_QUERY_ENGINE

0

Hi,

we have 2 tables, both in s3, both iceberg format, same structure, same partitioning, the only possible difference we can think of is that one of them has the metadata more compacted (that in theory this should improve the performance). but if we use an specific query in both tables, one spends 6 secs to run and the other (the new optimized one) throws the error:

Amazon Athena experienced an internal error while executing this query. Please try submitting the query again and if the issue reoccurs, contact AWS support for further assistance. We apologize for the inconvenience.

QUERY USED:

`WITH days AS ( SELECT DISTINCT game_title, dt FROM telemetry_data_prod.fct_dau_report

), first_pass AS ( SELECT game_title, dt, LAG(dt) OVER (PARTITION BY game_title ORDER BY dt) AS prev_dt FROM days ), first_flags AS ( SELECT game_title, dt, CASE WHEN prev_dt IS NULL THEN 0 WHEN dt = date_add('day', 1, prev_dt) THEN 0 ELSE 1 END AS break_flag_from_first FROM first_pass ), first_groups AS ( SELECT game_title, dt, SUM(break_flag_from_first) OVER ( PARTITION BY game_title ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS grp_from_first FROM first_flags ), last_pass AS ( SELECT game_title, dt, LAG(dt) OVER (PARTITION BY game_title ORDER BY dt DESC) AS next_dt FROM days ), last_flags AS ( SELECT game_title, dt, CASE WHEN next_dt IS NULL THEN 0 WHEN dt = date_add('day', -1, next_dt) THEN 0 ELSE 1 END AS break_flag_from_last FROM last_pass ), last_groups AS ( SELECT game_title, dt, SUM(break_flag_from_last) OVER ( PARTITION BY game_title ORDER BY dt DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS grp_from_last FROM last_flags ) SELECT d.game_title, MIN(d.dt) AS first_day, MAX(d.dt) AS last_day, COUNT_IF(f.grp_from_first = 0) AS consecutive_days_from_first_day, COUNT_IF(l.grp_from_last = 0) AS consecutive_days_before_last_day FROM days d JOIN first_groups f ON d.game_title = f.game_title AND d.dt = f.dt JOIN last_groups l ON d.game_title = l.game_title AND d.dt = l.dt GROUP BY d.game_title ORDER BY d.game_title;`

asked 3 days ago43 views

2 Answers
0

The INTERNAL_ERROR_QUERY_ENGINE you're experiencing with your optimized Iceberg table could be related to metadata compaction causing compatibility issues with Athena's query engine. When Iceberg tables undergo optimization operations like compaction and snapshot management, the metadata structure can change in ways that may trigger internal errors in certain query patterns.

Your query uses complex window functions with LAG, multiple CTEs, and aggregations over partitioned data. The combination of these operations with compacted metadata might be exposing an edge case in Athena's query processing.

Here are some troubleshooting steps:

  1. Try breaking down the query into smaller parts to isolate which CTE or operation triggers the error when run against the optimized table.

  2. Check if there are differences in the Iceberg metadata files between the two tables, particularly around snapshot history and manifest files after compaction.

  3. Verify that both tables are using compatible Iceberg format versions and that the optimization process didn't introduce any schema evolution changes.

  4. Consider temporarily disabling certain Athena engine optimizations by adjusting query hints or session properties if available.

  5. If the issue persists, you can add correspondence to your existing case through the AWS Support Center at https://console.aws.amazon.com/support/ with details about the specific error, query pattern, and table configurations. Include information about the optimization operations performed on the table that's failing.

The fact that the identical query works on the non-optimized table suggests the issue is specifically related to how the compacted metadata interacts with Athena's query execution engine rather than a problem with your query syntax.
Sources
Working with Amazon S3 Tables and table buckets - Amazon Simple Storage Service

answered 3 days ago

EXPERT

reviewed 3 days ago

0

Hi, If the same query works on one table and fails on the other, I don’t think the SQL is the problem. I’d look at the table you compacted. First I’d check whether it fails even with something basic, like SELECT count(*) or SELECT DISTINCT game_title, dt on that table. If those already fail, the query has nothing to do with it.

My main suspect is the Iceberg format version. Which engine did you use for the compaction? Newer Spark/Glue/EMR versions can upgrade the table to format v3, and Athena may not be able to read it. You can check with SHOW TBLPROPERTIES and compare format-version between the two tables.

It’s also worth looking at $files and $snapshots on both tables to see whether the compaction left delete files or changed the partitioning. You can also try the query with FOR VERSION AS OF on a snapshot from before the compaction: if it works there, the compaction is the cause.

If the table is still v2, rerunning the compaction from Athena with OPTIMIZE ... REWRITE DATA USING BIN_PACK so the files are rewritten by Athena’s own writer. If it went to v3, you’ll need to rebuild it as v2 with the same engine you used for the compaction, because Athena can’t do that part itself.

If even a simple SELECT keeps failing, open a case with AWS Support and include the query ID. They’re the only ones who can see what actually broke on their side.

answered a day ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.