Search code examples
sqlamazon-athenaprestotrino

How do I get rid of this Error:Column alias list has 1 entries but 't' has 4 columns available while creating a view on top of a view in AWS athena?

I have created a "dummy" table to access records stored in parquet in S3 & I have created a "dummy_flattened" view on top of "dummy" table using this query

CREATE OR REPLACE VIEW dummy_flattened AS 
select
  deviceid
, a.postid
, a.craterid
, a.posttype
, a.category
FROM dummy cross join UNNEST(postlist) as t(a)

example of a postlist row :

[{category=[{id=1, name=Dance}], craterid=1, postid=1, posttype=a}, {category=[{2, name=Dance}], craterid=2, postid=2, posttype=b}]

Now when I try to create a view on top of dummy_flattened view using the query

create or replace view test as select distinct deviceid from dummy_flattened limit 100

Athena is giving me the error :

Failed analyzing stored view 'logs.dummy_flattened': line
19:12: Column alias list has 1 entries but 't' has 4 columns available

but i can view the results with the same query mentioned above

select distinct deviceid from dummy_flattened limit 100

However when i don't include a.postid,a.craterid...while creating the dummy_flattened i can successfully create test view. However I couldn't figure out how to create a view on top of flattened view that includes postid,craterid,postype..without running in to a error.

I have been trying multiple combinations for the past few hours with no luck.

A few of the combinations i tried while creating flattened view

1. select deviceid,t.* as (a) FROM dummy cross join UNNEST(postlist) as t
2. select deviceid,(t.a.*) as (c,d,e,f) FROM dummy cross join UNNEST(postlist) as t(a)
3. And the one i mentioned above at the beginning

I would like to know if i'm missing something or can we not create views on top of unnested records. I have been refferring to multiple documents and other similar questions in stack overflow but couldn't find anything. As a last resort i'm posting this

Solution

  • UNNEST looks broken in Athena engine v3. Athena v3 is based on Trino which unpacks arrays of rows into individual fields (see @Guru Stron's answer below), but it looks not fully supported.

    As a workaround, one may switch to Athena engine v2 to run a CREATE VIEW query with UNNEST. Once created, you may switch back to Athena version v3: SELECT queries on the view, created in v2, work fine.

    For convenience, you may introduce a new Athena workgroup with engine v2 for views creation but use Athena v3 workgroup for querying since v3 has optimization advantages.