Error: mismatched input WITH - CTE's in views

[lukealexmiller]

I tried setting up a project in dbt using the adapter today and ran into a problem using WITH statement in a view.

I get the error: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 2:5: mismatched input 'WITH' expecting {'(', 'SELECT', 'VALUES', 'TABLE'}

Looking at the logs, this appears to be because dbt is wrapping the entire SELECT statement, including the CTE's generated by WITH statement, in parentheses, as:

create view my_table as ( WITH my_other_table AS ( ... ) SELECT .... )

On closer inspection there actually only appears to be an opening parenthesis and not a closing one in the logs. However, running the statement with one or both parentheses in Athena yields the same error.

Are WITH statements currently supported in views using the adapter?

Thanks!

[Dandandan]

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.

[lukealexmiller]

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.

[Dandandan]

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.

[AndresNamm]

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
)

[BeantownData]

Thanks @AndresNamm !! That worked great!

[Dandandan]

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?

[Dandandan]

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!