Cypher query loading complex CSV file

Question

I have been experimenting with Cypher to load a collection of CSV files to build a view of VMware's operational state. I'm able to execute the following query:

LOAD CSV WITH HEADERS FROM 'file:///Users/rmorgan/Downloads/All_vm_hierrachy.csv' as csvline
with csvline
where csvline.type = 'VirtualMachine'
MERGE (host:HostSystem { name: csvline.hostid, description: csvline.hosttype })
MERGE (pool:ResourcePool { name: csvline.resourcePool})
MERGE (parent:Folder { name: csvline.parentid, description: csvline.parenttype })
MERGE (vm:VirtualMachine { name: csvline.moid, description: csvline.name })
CREATE (host)-[:HAS_VM]->(vm)
CREATE (vm)-[:HAS_PARENT]->(parent)
CREATE (vm)-[:HAS_RESOURCEPOOL]->(pool);

This loads details about the host, I find the matching host object and then use SET to add more attributes.

LOAD CSV WITH HEADERS FROM 'file:///Users/rmorgan/Downloads/All_vm_hierrachy.csv' as csvline
with csvline
where csvline.type = 'HostSystem' and csvline.parenttype = 'ClusterComputeResource'
match (h:HostSystem {name:csvline.moid})
set h.fullName = csvline.name
merge (p:ClusterComputeResource {name: csvline.parentid, type: csvline.parenttype})
CREATE (p)-[:HAS_CHILD]->(h);

Both queries load the same file but instantiate different objects. Is there a way to combine them? I would like to put a CASE statement in there:

LOAD CSV WITH HEADERS FROM 'file:///Users/rmorgan/Downloads/All_vm_hierrachy.csv' as csvline
with csvline
CASE csvline.type = 'HostSystem' 
   // create some objects and add attributes
CASE csvline.type = 'VirtualMachine'
   // create some objects and add other attributes
END
// create some common objects / attributes irrespective of the path above

I would also like to nest these to produce more complex execution paths

LOAD CSV WITH HEADERS FROM 'file:///Users/rmorgan/Downloads/All_vm_hierrachy.csv' as csvline
with csvline
CASE csvline.type = 'HostSystem' 
   // create host object
   CASE csvline.parenttype = 'ClusterComputeResource'
   // create ClusterComputeResource object, link to host
   CASE csvline.parenttype = 'ComputeResource'
   // create ComputeResource object, link to host
CASE csvline.type = 'VirtualMachine'
   // create some objects and add other attributes
END
// create some common objects / attributes irrespective of the path above

Is this possible in Cypher? the commands seem to exist but I cannot make them work together.

Answer 1

Cool use-case.

• create indexes on unique properties you merge on
  • e.g create index on :HostSystem(name);
• don't use merge on multiple attributes, esp. on import
  • e.g. MERGE (host:HostSystem { name: csvline.hostid }) ON CREATE SET host.description= csvline.hosttype

Regarding your second question:

1. we're thinking about adding a conditional update functionality
2. there is a workaround
3. keeping your statements simpler will keep them faster

I'm currently writing a blog post about this, it is not done yet, feel free to have a look and apply the tips: https://dl.dropboxusercontent.com/u/14493611/load_csv_with_success.adoc

Esp. the part with the Eager loading which might affect you if you have a lot of data.

the workaround is, use a combination of filter and foreach to create a single or zero element collection of "work-items"

LOAD CSV WITH HEADERS FROM 'file:///Users/rmorgan/Downloads/All_vm_hierrachy.csv' as csvline
with csvline
foreach (line in 
   filter(x in [csvline] where x.type = 'HostSystem' 
                           and x.parenttype = 'ClusterComputeResource') 
   | MERGE (h:HostSystem {name:line.moid}) 
     SET h.fullName = csvline.name
     MERGE (p:ClusterComputeResource {name: csvline.parentid}) 
       ON CREATE  SET p.type= csvline.parenttype
     CREATE (p)-[:HAS_CHILD]->(h)
)

CASE ... WHEN THEN ELSE END is an expression.