Recommended way to create a graph from a table or SQL query

[selendym]

Hello.

What is the recommended way to create a graph from a table (or from an SQL query, in general)?

Say, we have a table A with fields id and parent_id, where the latter refers back to the former of some row. How would I go about and cast this into an AGE graph easily? Ideally, this would be without doing a client-server roundtrip of first querying table rows and then creating the corresponding graph vertices and edges.

Currently, there seems to be a way to load graphs from CSV files (https://age.apache.org/age-manual/master/intro/agload.html), but this doesn't really help in this case. Also, there seems to be a helper function written in plpython available (https://github.com/sorrell/age-compose/blob/master/docker-entrypoint/initdb.d/20-initgraph.sql#L10-L37), but this isn't ideal either.

Best regards.

[jrgemignani]

The recommended way would be to use cypher commands, not SQL commands. So, you would create the graph, then create and connect the vertices and edges (relationships). It is not recommended to attempt to create the graph yourself with SQL commands.

[selendym]

(Just to clarify, I'm not trying to modify the underlying system tables directly in any way.)

So if I understand correctly, there is no easy/convenient way to populate graphs from the results of SQL queries? And the recommended way is to:

• query the server for the relevant data (with plain SQL)
• form the cypher queries/commands by string interpolation or whatever on the client side
• send the cypher queries/commands back to the server

If so, this feels quite inefficient, but perhaps I'm missing something.

[jrgemignani]

Yes, that is the recommended way, atm. Currently, there isn't a function or functionality that could cast a set of tables and data into a graph. However, it is something that we have been discussing on occasion. We should probably revisit it to see if it is feasible.

[jrgemignani]

I have added it as a task for review and -> possible <- implementation in the future. However, anything that it were to do would just be a codified selection from tables, building Cypher commands, and then executing those commands.

[selendym]

Ok, thanks for the info.

Perhaps something like this could be worth considering:

select create_vertex('g', id, label, properties) from (select id, label, properties from (...)) as r;
select create_edge('g', id, label, start_id, end_id, properties) from (select id, label, start_id, end_id, properties from (...)) as r;

where 'g' is the graph name and properties would be in json, for example.

Would you prefer this issue be closed or left open? I'm inclined to leave it open for now and see if a review leads to something in the future.