Getting "[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE]" When Using Common Query for CloudFront Logs - Unexpected Behavior of Common Query

The error message you're receiving, "[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE]", indicates that Athena experienced an internal error while executing your query. This is an unexpected behavior, and it's good that you're reaching out for assistance.

Here are a few things you can try to troubleshoot the issue:

1. Check the query syntax: Ensure that your query syntax is correct. Double-check the table name, column names, and date range. You can try running a simpler query to see if it's a syntax issue.

2. Partition the data: CloudFront logs are typically partitioned by date, so you may want to try partitioning the data in your query. This can help Athena process the data more efficiently. For example:

SELECT *
FROM default.cloudfront_logs
WHERE "date" BETWEEN DATE '2024-05-01' AND DATE '2024-05-05'
  AND (cast(status as varchar(20)) LIKE '5%')
  AND "date" IN (
    SELECT DISTINCT "date"
    FROM default.cloudfront_logs
    WHERE "date" BETWEEN DATE '2024-05-01' AND DATE '2024-05-05'
  );

3. Check the table schema: Ensure that the table schema is correct and that the data types match what you're using in your query. You can use the DESCRIBE default.cloudfront_logs; command to view the table schema.

4. Increase the Athena engine version: You mentioned that AWS upgraded you to Athena engine version 3, which is a newer version. However, it's possible that the issue is related to the engine version. You can try running the query with the previous engine version to see if that resolves the problem.

5. Contact AWS Support: If the above steps don't resolve the issue, you can contact AWS Support for further assistance. They should be able to investigate the problem and provide more guidance.

It's important to note that you shouldn't be charged for the failed query, as mentioned in the error message. However, it's still a good idea to understand the root cause of the issue to ensure that your future queries run smoothly.

Thank you for your fast response, JonQ.

1. Even a simplified query [1] have me the same "internal error while executing query" message that I stated before.

2. When I run your suggestion for Partitioning the data I get the same error.

3. Using the command:

DESCRIBE default.cloudfront_logs;

shows the expected table schema with 26 items showing

4. Using "Amazon Q" I was able to see how to change the version of Athena, however only "3" is showing up from the list.

5. I do not have enough money alotment to be able to sign-up for AWS Support, although I was had this enabled for years before.

Any other advice you can give me would be very helpful. I appreciate your time & would love to have Athena normalized to be able to troubleshoot this strange CloudFront happening.

[1] Simplified query:

SELECT * FROM default.cloudfront_logs WHERE status = 500 ORDER BY "date" DESC LIMIT 50 ;

It's been a couple weeks and I haven't heard an update. This is continuing to occur with this simple query. Can I please have an update or a suggestion on how I can view run this query? I need to troubleshoot 500 errors coming from CloudFront for given dates.

Thanks for your help!