Redshift Spectrum query fails with a syntax error while the same query succeeds in Athena - reserved word differences between engines (nested struct)

Symptom

The following query runs successfully in Athena.

SELECT body.personId,
       body.identityId,
       body.credentials.oktaId,
       body.credentials."group" AS credential_group,
       body.credentials.created
FROM   "<glue_database>".identitycredentials_created_1_0_0
WHERE  date = '<yyyy-mm-dd>';

Querying the same Glue catalog table through a Redshift Spectrum external schema fails as follows.

SQL Error [42601]: ERROR: syntax error at or near "credentials"
in context ",body.credentials", at line 4, column 27

This symptom has two clues.

• The SQLSTATE is 42601 (syntax error). A problem with external schema permissions, the IAM role, or a Parquet type mismatch would return a permission error or a data-incompatibility error instead. A syntax error means the query already failed at the parser stage, so you do not need to check your S3, Glue, or IAM configuration.
• In the nested path, "group" is already wrapped in double quotes, but credentials is not. In other words, names that are reserved in both engines have already been handled, and a name that is reserved in only one engine remains.

Diagnosis

1. The token after at or near is the problematic identifier.
2. Check that token against the Redshift reserved word list and the Athena reserved word lists (which are split into DDL and SELECT). In the example, CREDENTIALS is on the Redshift list but not on either Athena list, so it passes only in Athena.
3. Check each segment of the nested path the same way. In the example, only credentials was left unhandled.
4. The following names are common in event and log data and are reserved only in Redshift, which makes them likely to break in Spectrum.

Root cause

Redshift Spectrum uses the Redshift SQL parser as-is. Therefore, even when you query an external table, Redshift's reserved word rules apply, and the result diverges from Athena, which uses a separate parser. Regardless of how the table is defined in the Glue catalog, as long as the query engine is Redshift, Redshift reserved words are what matter.

In Redshift, a standard identifier cannot be a reserved word. To use a reserved word as an identifier, you must write it as a delimited identifier enclosed in double quotes.

A nested struct is accessed through a path that joins field names with dot notation, and each segment of the path is parsed as a separate identifier. So if any single segment's name is a reserved word, a syntax error occurs at that point. This applies to a struct's inner field names, not just top-level column names.

The mismatch is bidirectional. credentials is reserved only in Redshift, so the SELECT fails only in Redshift; date is reserved only in Athena DDL, so backticks are needed only when you write CREATE EXTERNAL TABLE in Athena (the SELECT works unquoted in both engines). The escape characters also differ (Athena DDL uses backticks; SELECT and Redshift use double quotes), so running Athena's backtick DDL directly in Redshift fails.

Resolution

1. Wrap only the offending segment in double quotes.

body."credentials".oktaId,
body."credentials"."group" AS credential_group,
body."credentials".created

Do not wrap the entire path. "body.credentials.oktaId" looks for a top-level column whose name is that literal string, so it will not find the column.

2. Assign a table alias and start the path from the alias.

SELECT ic.body.personId,
       ic.body.identityId,
       ic.body."credentials".oktaId,
       ic.body."credentials"."group" AS credential_group,
       ic.body."credentials".created
FROM   <external_schema>.identitycredentials_created_1_0_0 ic
WHERE  ic.date = '<yyyy-mm-dd>';

However, if the alias collides with an existing schema name, ic.body is interpreted as a table in that schema rather than an array column (a note in the official tutorial), which leads to a lookup failure rather than a syntax error. Choose an alias that does not collide with a schema name.

Note: Redshift folds delimited identifiers to lowercase. If a Glue struct field name is mixed case, double quotes alone are not enough and you also need enable_case_sensitive_identifier. Athena SELECT preserves case, so the same query can produce different results across the two engines.

Related information

• Reserved words
• Names and identifiers
• Escape reserved keywords in queries
• Tutorial: Querying nested data with Amazon Redshift Spectrum