Redshift query with recursive CTE

Question & Answer
, ,
1

I’m trying to create a dataset with a SQL Query to a Redshift database. The query contains a recursive CTE and works well on Redshift but Quicksight gives me a “[Amazon] (500310) Invalid operation: Recursive CTE in subquery are not supported.;”

Is it not possible to use recursive queries from Quicksight?

Thanks!

2

Temp table is not supported yet in QuickSight. But WITH clause can be used in SQL query to a limit that RedShift data source support. For eg: sample query

with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;

according to the redshift AWS doc WITH clause - Amazon Redshift, will work just fine. Do you have any complications in your query? can you find out in redshift query history about the result of the query execution that is from QuickSight? All QuickSight queries will be logged in redshift query history which might give some insights.

3

Thanks ninja-dp

The solution I implemented in the end was to create a view in Redshift with WITH clauses, and the configure a SELECT * of the view. Might not be the best solution, but gets the job done!

3 Likes
4

Thank you for sharing your solution! :slightly_smiling_face: