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


I have a table in Athena which has multiple arrays of structs within it, i.e. column:bigint column2:double column3:array:struct< $id:bigint subcolumn1:string>> column4:array:struct< $id:bigint $ref:string subcolumn2:string>>

I am successfully able to run a query that unnests one of those arrays of structs SELECT pt.column, st.subcolumn1 FROM table_name pt CROSS JOIN UNNEST(pt.column3) AS t (st)

However, when I attempt to turn that same query as a view by prepended the CREATE OR REPLACE VIEW view_name AS... statement, I get the following error:

Error: name expected at the position 7 of 'struct<$id:string,$ref:string,subcolumns2:string>' but '$' is found. (Service: null; Status Code: 0; Error Code: null; Request ID: null; Proxy: null) This query ran against the "YOUR" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: fc0405b3-a75e-4737-9e31-ac7392fd7be2

Is there a limitation on the creation of views with an additional field starting with $? Was able to create the table using Glue, then select the data... Now just cannot make that select reusable with a view.

preguntada hace 2 meses148 visualizaciones
1 Respuesta

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:
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!

profile picture
respondido hace 2 meses

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas