Best way to run multi-statement cypher query

[folterj]

What is the best way to run a multi-statement cypher query?
I would like to run a small cypher query without running transactions in Java etc, simply for partial backup/restore purposes.

As far as I understand this would be done using CSV or cypher. I've extensively tested this.

CSV:
CALL apoc.export.csv.query("MATCH (x:X)-[r:relationship]->(y:Y) RETURN x.x as xx, properties(r) as rr, y.y as yy", "x.csv", {})
Although it's possible to both export, and interpret property maps with APOC, it's not possible to dynamic assign property map (i.e. SET r = {...}); nodes/relationship properties can only be set with explicit labelled assignment, making this method inconvenient for our purpose.

Cypher should have more support, though as I understand export is again only possible using APOC e.g.:
call apoc.export.cypher.query("MATCH (x:X) RETURN *", "x.cypher", {format:'plain', cypherFormat:'updateStructure'})

How can a cypher be imported? I have tried the following:

• all 3 export formats: plain, cypher-shell, neo4j-shell
• drag into neo4j browser cypher import (syntax error is reported on multiple statements, or cypher wrapping)
• using apoc.cypher.runFile in Neo4J Desktop browser (freezes)
• using apoc.cypher.runFile in Linux cypher-shell tool (freezes) e.g.:
  cypher-shell -u user -p pass "call apoc.cypher.runFile('x.cypher')"
  This seems to simply freeze (for less than 300 relationships/properties - I've waited about 15 minutes, I would expect this to take in the order of milliseconds)

Note contrary to documentation, runFile only works WITHOUT cypher wrapping (BEGIN / :begin etc), using format:'plain'.

Any advise appreciated.

Neo4J 3.5.1: Desktop / Linux

[michael_hunger]

You can assign dynamic maps (or parameters) to nodes and relationships with

• SET e = value
• SET e += value
• SET e += $param
• SET e += apoc.map.fromXxx(...)

How many elements do your cypher statements have?

Can you show an example?

Is it one gigantic statement ? Or many smaller ones separated by semicolons ?

Yes that's what should be documented, that the formats

• neo4j-shell is for neo4j-shell
• cypher-shell is for cypher-shell
• plain is for the other modes (and also the multipleFiles option)

Unfortunately procedures cannot mix data and schema statements that's why there is a separate runFile and runSchemaFile

[michael_hunger]

What is your original goal?

[folterj]

Thank you for the response @michael.hunger
As mentioned my original purpose is to run a partial backup/restore (without the need to code an application around it). I'm not making any structural changes - just setting properties on (existing) relationships.

CSV:
I have tested this extensively, and found that SET e = [variable from parameter containing map] (e.g. SET r=apoc.convert.fromJsonMap(row.x)) always gives this error:
Neo.ClientError.Statement.TypeError: Property values can only be of primitive types or arrays thereof

I can provide this obfuscated load in line with the previous export:
LOAD CSV WITH HEADERS FROM "file:///x.csv" AS row MATCH (x:X {x:row.xx}), (y:Y {y:row.yy}) MERGE (x)-[r:relationship]->(y) SET r=apoc.convert.fromJsonMap(row.r)

It is worth mentioning that I have boolean properties, though surely this should not be a problem?

CYPHER:
You mention the 'cypher-shell' format is for cypher-shell, which seems intuitive, however how can this be loaded other than by query incorporating the runFile call (which actually requires the 'plain' format to work)?
I have not found any way of loading or running a cypher from file, please let me know if I missed something obvious.

[michael_hunger]

1. can you share an value of such a map that you are passing in? What format is "row.x" is it really a JSON encoded in a string?
2. yes please supply such an CSV
3. cat cypher-shell-format-file.cypher | cpyher-shell -u neo4j -p <password> -a bolt://localhost

[folterj]

CSV:

1. The formatting is fine, I've also tried leaving the apoc conversion out, or not using the properties operator in export - same result
2. I was able to reproduce both a working (using a boolean property) and non-working example with a single property, as it turns out temporal type does not seem to be supported in import.

Export:
CALL apoc.export.csv.query("MATCH (x:X)-[r:relationship]->(y:Y) RETURN ID(x) as x,r,ID(y) as y", "test.csv", {})

File (note forum converts [ and ] to 😞
"x","r","y"
"1919374","{""id"":3919461,""type"":""relationship"",""start"":1919374,""end"":1919375,""properties"":{""myProperty"":{""offset"":{""totalSeconds"":0,""id"":""Z"",""rules"":{""transitions"":,""transitionRules"":,""fixedOffset"":true}},""zone"":{""totalSeconds"":0,""id"":""Z"",""rules"":{""transitions"":,""transitionRules"":,""fixedOffset"":true}},""dayOfYear"":1,""dayOfWeek"":""TUESDAY"",""month"":""JANUARY"",""dayOfMonth"":1,""year"":2019,""monthValue"":1,""hour"":0,""minute"":0,""nano"":0,""second"":0,""chronology"":{""id"":""ISO"",""calendarType"":""iso8601""}}}}","1919375"

Import:
LOAD CSV WITH HEADERS FROM "file:///test.csv" AS row MATCH (x:X) MATCH (y:Y) WHERE ID(x)=toInteger(row.x) AND ID(y)=toInteger(row.y) MERGE (x)-[r:relationship]->(y) SET r=apoc.convert.fromJsonMap(row.r).properties

Result:
Neo.ClientError.Statement.TypeError: Property values can only be of primitive types or arrays thereof

CYPHER:

3. Thank you for providing this - this is fine if it works (though it would be nice to have an argument for file input). I appreciate this is APOC, but if there is no selective export functionality in standard cypher, then this is the only way we can export.

Testing the described method unfortunately does not seem to add any relationships. Further investigation shows there appears to be an issue with the exported cypher syntax.

If the first row is (forum seems to eat the single quotes around UNIQUE IMPORT LABEL etc):
MATCH (n1:UNIQUE IMPORT LABEL{UNIQUE IMPORT ID:4762}), (n2:UNIQUE IMPORT LABEL{UNIQUE IMPORT ID:1919299}) MERGE (n1)-[r:relationship]->(n2);

Then running this query should return existing nodes:
MATCH (n1:UNIQUE IMPORT LABEL{UNIQUE IMPORT ID:4762}), (n2:UNIQUE IMPORT LABEL{UNIQUE IMPORT ID:1919299}) return n1,n2

However, it does not find the nodes. Is this depricated use of ID? The following does return them:
MATCH (n1), (n2) where ID(n1)=4762 and ID(n2)=1919299 return n1,n2

PS: APOC version 3.5.0.1

[michael_hunger]

There are two things that might not work out of the box:

1. empty arrays as it might not know which datatype that has
2. more importantly the nested "myProperty" maps datastructure you have for the date-time, you have to convert that into a localdatetime first
3. looking at type, start, end -> do you really want to set all those properties?

[folterj]

1. Empty map should not be a problem according do documentation (https://neo4j.com/docs/cypher-manual/current/clauses/set/#set-remove-properties-using-empty-map)
2. That means that importing properties that could include a temporal field simply won't work using map - if some properties need to be converted, then they need to be specified explicitly, then all properties need to be set explicitly for export & import. This is not only inconvenient, there is also the problem that in our case these are optional and won't necessarily exist for each relationship.
3. No, the query uses fromJsonMap(relationship).properties. After trail/error I found that using .properties at export results in an error in the string conversion, and only seems to work at import post-conversion (as in the example code provided above).

I would also appreciate any recommendations on the issue with cypher export. Is this a deprecation bug?

[michael_hunger]

1. Neo4j properties don't support nested map structures
2. I said empty array/list which is untyped from json
3. you'd have to construct date(year, month, day, timezone) from that json data, see manual,
4. I would probably export relationship/node properties explicitely with apoc.export.csv with individual files where the props are spread over columns

What was the cypher export again, it would be good to raise one topic per question otherwise it gets all messed up.

[folterj]

Re CSV, understood.

Re Cypher - following your suggestion, please see Export a (sub)graph to Cypher script and import it again