1 Answer
- Newest
- Most votes
- Most comments
0
Hello, This is known issue in the Athena Engine version 3 with the creation of the views only, as explained in your example. For now the workaround is to Use Engine version 2.
"Multiple column syntaxes" with unnest() do work with Athena also but in your example you are only using unnest() on a single column "users". See below example which shows correct way of opening multiple columns with unnest():
WITH dataset AS (
SELECT ARRAY[
CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
] AS users ,
ARRAY[
CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
] AS users1
)
SELECT t.* FROM dataset CROSS JOIN UNNEST(users,users1) t(s,t)
Relevant content
- AWS OFFICIALUpdated 4 months ago
- AWS OFFICIALUpdated 2 months ago
- AWS OFFICIALUpdated 2 months ago
- AWS OFFICIALUpdated a year ago
Hello Do you have any plans on improving this behavior? Cause my team have the very same situation - we can't upgrade until there is a way to use unnest over an array of structs
Thanks!