Create Edges between Vertices with matching property contents

[EdVanDance]

Hello 😃

I'm trying to build a graph from XML data. The XML data is basically a tree structure, but it's using cross references through ID attributes.
So the XML structure itself is flat and it's also split across multiple files.

My idea was to import all elements as Vertices and use the Database to create the edges.

Example Data after XML import:

ParentVertex
{
  guid: "5945c961-e74d-478f-8afe-da53cf4189e3"
}

ChildVertex
{
  guid: "xxxx-xxxx-...",
  parent_id: "5945c961-e74d-478f-8afe-da53cf4189e3"
}

There shall be edges between all vertices where parent_id == guid. In the example ChildVertex --> ParentVertex.

I got the desired result with gremlin using this:

g.V().as('child').values('parent_id').as('pid').
   V().as('parent').values('id').as('id').
   where('pid', eq('id')).
   addE('Part_Of').from('child').to('parent')"

With couple of thousand of pairs this already takes almost an hour.

Could you please help me to improve the performance? I'm pretty new to graph databases, so please bear with me.

Is there something I can do on the schema side, like using indexes? At least with the specific query I used, having indexes on guid and/or parent_id didn't help.
Gremlin isn't mandatory, I would also happily switch to SQL, but I couldn't figure out how to achieve the desired result with SQL.

Any suggestions are welcome 😄

Greetings,
Ed

[lvca]

I think the fastest way (without writing code) is using SQL.

First, very important is creating an index on guid (so lookups are O(logN) ):

create vertex type V
create property V.guid string
create index on V (guid) unique

Let's say the vertices are stored by using the type "V". I don't know how you import the nodes. Are you creating all the vertices first and then attaching the relationship?

This is a script that takes 2 parameters parentGuid and childGuid, lookups for the nodes, and creates an edge:

let $parent = select from V where guid = $parentGuid;
let $child = select from V where guid = $childGuid;
let e = create edge Part_Of  from $chile to $parent;
return $e;

I hope this helps and gives you some hints.

[EdVanDance]

I'm creating the nodes through SQL statements using the HTTP/JSON API. After all the nodes are created I'm trying to create all relationships with a single query.

What I'm trying to do as pseudo code:

for child in all_nodes
  for parent in all_nodes
    if child.parent_id == parent.id
      create edge from child to parent

That is what the gremlin query is doing. But it's pretty slow. I was hoping there is a "native" SQL query, which leverages indexes and performs better.

Edit: I forgot to mention. The nodes can be of different types, representing the types from the XML input.

[EdVanDance]

I had a look at SQL Scripting.

I thought that would do the trick:

let children = select from V where parent_id is not null;

foreach (child in $children){
  let parents = select from V where guid = $child.parent_id;

  foreach (parent in $parents) {
    let e = create edge Part_Of from $child to $parent;
  }
}

But it fails with Cannot execute command Invalid vertex for edge creation: {} and I don't understand why. Could you explain please?

If I remove let e =, because I actually don't need the result I get a StackOverflowError Exception. This is also something I don't understand.

[lvca]

Your script seems correct to me. Could you please send your database? It doesn't need to be the whole database and data can be fake. I'd need it to run this script and check what's going on. If doesn't contain sensitive data you can attach it to this issue or send it to support at arcadedata.com with reference to this issue in the subject.

[EdVanDance]

Here is a SQL script that you can run in studio on a new database. It adds just two Vertex pairs, but it's enough to reproduce.

CREATE VERTEX TYPE V;
CREATE PROPERTY V.guid STRING;
CREATE PROPERTY V.parent_id STRING;
CREATE INDEX ON V (guid) UNIQUE;

CREATE EDGE TYPE Part_Of;

INSERT INTO V CONTENT [{'guid': '0'}, {'guid': '0.0', 'parent_id': '0'}];
INSERT INTO V CONTENT [{'guid': '1'}, {'guid': '1.1', 'parent_id': '1'}];

[lvca]

I was able to reproduce it and found the issue: $parent is an actually reserved variable used to refer to the context's parent (if any). Contexts are linked in a tree for execution. Replace parent with p and it will work.

[lvca]

Also, please let me know how fast this works in comparison to the Gremlin version

[lvca]

Also, the script should throw an exception when you do this:

foreach (parent in $parents) {
}

Working on this.

[lvca]

Fixed in #1489

[EdVanDance]

I was able to reproduce it and found the issue: $parent is an actually reserved variable used to refer to the context's parent (if any). Contexts are linked in a tree for execution. Replace parent with p and it will work.

Ah I totally forgot about that. Now I remember that I saw that in the docs.

Also, please let me know how fast this works in comparison to the Gremlin version

The sqlscript is faster, but not significantly. I used some synthetic data for comparison. I was hoping for at least an order of magnitude less execution time, but that's not the case. And on top of that the comparison isn't fair I think. Since with gremlin I don't have to specify the Vertextype, so I can handle the edge creation with a single query. With sqlscript I would need a query for each source/target type combination.

Thank you very much for helping me out here. Very much appreciated.