Recursion using WITH

Dremio University
1

I am trying to write a query where
with tmp_tree(
child,
parent,
level
)
as
(
select
child,
parent,
level
from tree A
where parent= ‘A’ and
A.child !=A.parent
union all
select
child,
parent,
level
from tree A ,tmp_tree tmp
tmp.NodeId=A.ParentNodeId
and A.ParentNodeId=‘A’
and tmp.child = A.child
and A.child !=A.parent
and A.level > tmp.level
)
select *
from tmp_tree

table tmp_tree not found

Is it that - Dremio does not support recursion - is there some other way to get this dataset ?

2

Do you have tmp_tree already in Dremio as a PDS or VDS? Sometimes if not you might need the full path to it Like Source.folder.file

3

No i dont have this as PDS or VDS … i wrote this as a like to like migration of SQL to Dremio
Do i have to create a PDS\VDS for this to work im Dremio ?

4

Well normally you add a source first. i.e. like Mongo, then you’ll see the collections within that source and can promote it to a physical dataset (PDS) run queries there and save as a VDS from there.

https://docs.dremio.com/data-sources/

Can you provide a screenshot of how your source is setup?

5

I think the current conversation here is a red herring. The quick answer is that Dremio does not currently support recursive CTE.

6

i see so - whatever however i try i cannot achieve it ?
If i create a tmp_tree vds with the above query and then use it ?

7

What you are trying to do will not work because it is not supported.

8

Still learning here as well. Can this be accomplished via CREATE TABLE AS $scratch.tmp_tree …, and then querying the $scratch table?

9

@Jbrezovan What is the SQL you are using behind the CTAS?

10

I started here: Dremio

Make sure you enable exports for the sources (not tables, but sources)–I missed this detail, and kept getting errors like “[x source] is immutable for user” until I read the documentation more closely and figured it out.