Error: mismatched input WITH - CTE's in views about dbt-athena HOT 7 OPEN

As I struggled a bit to understand the way with statements work, here is a full example

SELECT * FROM (
    with t1 as (select * FROM {{ref('other_dbt_view_or_table')}})
    SELECT * FROM t1
)

from dbt-athena.

Thanks, interesting find!

It would be good to find out in what cases Athena does support CTEs. Maybe we need to wrap the inner query in a select * from(with...? I am currently not on a computer, so I can not check.

from dbt-athena.

Ah, interesting, yes wrapping in the SELECT statement gets around the problem.
I'm generally using CTE's in (materialized) views in Athena with all CTE's preceding the final SELECT statement.

from dbt-athena.

Yeah, that makes sense, that's also how I use dbt generally.

We could likely change the adapter to wrap all queries in a select to workaround this issue.

from dbt-athena.

Thanks @AndresNamm !! That worked great!

from dbt-athena.

Maybe something that should be added to the docs while we don't workaround that Athena doesn't support it in a create table/view as statement?

from dbt-athena.

I believe we should just change the create_view_as and create_table_as macro's to generate a view starting with SELECT *.
Original macro's are here:

https://github.com/fishtown-analytics/dbt/blob/dev/marian-anderson/core/dbt/include/global_project/macros/adapters/common.sql

Help welcome!

from dbt-athena.