0

As far as I know Clickhouse doesn't support recursive CTEs, so I'd like to rewrite a query that I'm using on SQL Server. Below there's the snippet :)

WITH cte
(ID, ID_PARENT, OBJ_DESC, CHILD, PARENT, CLASS_NAME, CATEGORY_NAME, LEVEL) 
AS ( 
    select a1.STC_SQLIDENTITY, a2.ID, a1.OBJ_DESC, a1.CHILD, a1.PARENT, a1.CLASS_NAME, a1.CATEGORY_NAME, 4 as LEVEL from treeOne as a1
    join treeTwo as a2
    on a2.CHILD = a1.PARENT 
    where PARENT in 
    (select distinct PARENT from alberatura where PARENT not in (select CHILD from treeOne))
    union all
    select a2.SQLIDENTITY,  itms.ID, a2.OBJ_DESC, a2.CHILD, a2.PARENT, a2.CLASS_NAME, a2.CATEGORY_NAME, itms.LEVEL+1 as LEVEL from treeOne a2
    INNER JOIN cte itms ON itms.CHILD = a2.PARENT
)
SELECT * FROM cte;

Do you think that Clickhouse is efficient to do such operations?

Thanks a lot!

pihash
  • 1
  • 1
  • 2
  • I can't say that I know all that much about ClickHouse, but a quick peruse of their docs seems to indicate that CTEs are supported https://clickhouse.tech/docs/en/sql-reference/statements/select/with/ Of course, given that it's a column-oriented DBMS, you will likely find that your existing queries probably won't be a simple copy/paste and away you go - but they would be a starting point. As to your question about the efficiency (or otherwise), I don't have an answer for that, sorry. – Craig Mar 31 '21 at 22:28
  • 1
    Thanks @Craig for your answer! I read the documentation but looking at this [github issue](https://github.com/ClickHouse/ClickHouse/issues/16907) it seems that the recursive version of CTE is not supported yet. For this reason I would like to rewrite it in another way... – pihash Apr 01 '21 at 08:57
  • i just tested factorial from https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL and it's not working – kirill_igum Jun 21 '21 at 00:08

0 Answers0