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.

질문됨 2달 전162회 조회
1개 답변

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
답변함 2달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠