Recrusive cte in databrick sql

[Databricks143]

Hi Team,

How to write recrusive cte in databricks SQL.

Please let me know any one have solution for this 

[Databricks143]

need to run below query in databricks ql but it showing error as UserCTE table not found.

WITH UserCTE AS (
  SELECT userId, userName, managerId, 0 AS EmpLevel
  FROM Users where managerId IS NULL

  UNION ALL

  SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
  FROM Users AS usr
    INNER JOIN UserCTE AS mgr
      ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT * 
  FROM UserCTE AS u where u.ManagerId=3
  ORDER BY EmpLevel

Please help me on this

[-werners-]

recursive CTE is not supported in spark. but there is a workaround using python:

https://medium.com/@24chynoweth/recursive-cte-on-databricks-2ac0dff8ca06

[Databricks143]

Thanks for update .

We have frame work  restrictions in databricks  in my organisation and  we need to use only scala code

[-werners-]

the code is easily translated to scala.
There is nothing python specific about it.

[A_Kennedy]

Did you find a solution? I have an almost identical CTE query to pull employee hierarchy info and I am receiving the same error

[-werners-]

It is still not supported.  Not sure when it will be (if ever).

[EWhitley]

Weirdly, if you ask the Databricks assistant, it tells you it does support recursive CTE and will give you sample code for it. If you follow up and press it on details it doubles down and says it supports it, but tell you the runtime version information.

I'm wondering if support is in development.