Athena: Cannot create view when struct contains two $prefix fields?

Athena, being based on Presto, generally supports querying complex nested data types, including arrays of structs. However, special characters in column names, especially $, can sometimes lead to unexpected behavior because these characters might be reserved for internal use or have specific syntactical functions in SQL and in the underlying Presto engine that Athena uses.

Here are a few suggestions to work around this issue:

• If possible, consider renaming the fields in your structs to avoid using $ at the beginning. This is the most straightforward solution to avoid syntax and parsing issues. Since you mentioned you created the table using Glue, you should be able to modify the schema in Glue to rename these fields and then recreate the table in Athena.
• When creating the view, try using aliases for the problematic columns that do not include the $ character. Although you're already doing this in your SELECT query, ensuring that none of the struct field names exposed in the view have $ might help. For example:

CREATE OR REPLACE VIEW view_name AS
SELECT pt.column, st.subcolumn1, st."$id" as id, st."$ref" as ref FROM table_name pt
CROSS JOIN UNNEST(pt.column3) AS t (st)

This approach renames $id and $ref in the output of the view to id and ref, respectively.

If this has answered your question or was helpful, accepting the answer would be greatly appreciated. Thank you!