Unable to query Redshift table in Aws Athena

The error you're encountering suggests that there might be a mismatch between the table schema in Redshift and how Athena is interpreting it. This could be due to a few reasons:

1. Schema synchronization: Ensure that the schema of the Redshift table is properly synchronized with Athena. Sometimes, changes made in Redshift may not be immediately reflected in Athena's metadata.

2. Column naming: Check if there are any column names in your Redshift table that might be reserved keywords in Athena. The error mentioning "column 'internal' does not exist" could indicate that Athena is misinterpreting a column name as a keyword.

3. Data catalog issues: Verify that the AWS Glue Data Catalog (which Athena uses to store metadata) is correctly set up and has the most up-to-date information about your Redshift table.

4. Query syntax: Double-check your query syntax to ensure it's compatible with Athena's SQL dialect, which might differ slightly from Redshift's.

5. Permissions: Confirm that your IAM role or user has the necessary permissions to access both the Redshift table and Athena.

To resolve this issue:

1. Refresh the table metadata in Athena or re-create the table definition.
2. Review your query and table schema for any potential conflicts with Athena's reserved words or syntax.
3. If the problem persists, you may need to contact AWS support for further assistance, as this could be related to an internal issue with Athena's query engine.

Remember, Athena and Redshift, while both SQL-based, have some differences in their implementations. Ensuring compatibility between the two services may require some adjustments in how you structure your queries or define your tables.
Sources
ErrorCode: INTERNAL_ERROR_QUERY_ENGINE | AWS re:Post
INTERNAL_ERROR_QUERY_ENGINE | AWS re:Post
ATHENA INTERNAL_ERROR_QUERY_ENGINE | AWS re:Post