Dealing With Graph Data

[stephenstubbs]

Feature Request

Is your feature request related to a problem? Please describe:
I'm currently moving a posgres database to tidb. I have some directed acyclic graph data. One table with nodes and another with edges. In terms of querying it I need to find all parents and all children of a node. In postgres I use a recursive CTE. I see there is no recursive CTE or graph support like mariadb has https://mariadb.com/kb/en/oqgraph-storage-engine/ and would like to know if there are any plans to add either or both of these.

Describe the feature you'd like:
Recursive CTE or ideally graph data types support.

Describe alternatives you've considered:
I have considered using a separate database like dgraph or neo4j for the data but then this will not be ACID compliant.

[ghost]

Support for CTEs is planned. You can track it in the following issues:

• support common table expression (CTE) #17472 (Non-recursive)
• support recursive common table expression (CTE) #6824 (Recursive)

There are no current plans for a separate engine, such as OQGraph. Thank you for using TiDB, please let us know if you have other migration questions :-)

[stephenstubbs]

Is there any other solution to this problem other than using either of these methods? I've looked at spark but I don't see a solution. I would rather not revert to using mysql or mariadb just for this feature.

[stephenstubbs]

Perhaps running something like dgraph for the graph data along side tidb is the best solution. Otherwise running janusgraph or cayley on top of tidb.

[stephenstubbs]

Seeing as my graph requirement is fairly simple I'm going to have a go at creating a project with the petgraph crate and tikv client and see how that performs.

[ghost]

Is there any other solution to this problem other than using either of these methods? I've looked at spark but I don't see a solution. I would rather not revert to using mysql or mariadb just for this feature.

Non-recursive CTEs can be emulated with temporary tables, but recursive is much harder - the queries tend to use self-joins and look much more complicated. I understand your desire to have this feature, and like you I am hoping that it is added soon :-)

[tisonkun]

@wjhuang2016 I think we can close this issue prefer #17472.

@sstubbs @nullnotnil there is an ongoing develop effort on both recursive and non-recursive CTE to TiDB. Feel free to track it from #17472 and https://github.com/orgs/pingcap/projects/37 , or throw any ideas about it.

[wjhuang2016]

Yes, I think recursive CTE can solve this problem. Please refer to #17472. I am going to close this issue now.