How to use hint when load csv and merge data in Neo4j

Question

Suppose 58 million nodes and 100 million relationships already existed in the database.

Then I want to load a delta batch data which contains 1.3 million nodes and 1 million relationships via LOAD CSV method like below:

USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///path/to/data.csv' AS row FIELDTERMINATOR '\t' 
MERGE (n:Person {cstid: row.cstid}) 
ON CREATE SET n.name = row.name 
ON MATCH SET n.name = row.name;

Also, there was a unique constraint on :Person(cstid), and I don't want to use it. So I tried to use the SCAN hint like below:

USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///path/to/data.csv' AS row FIELDTERMINATOR '\t' 
MERGE (n:Person {cstid: row.cstid}) 
USING SCAN n:Person
ON CREATE SET n.name = row.name 
ON MATCH SET n.name = row.name;

But Neo4j just throw an warning, terminate the request and exit directly:

WARNING: Invalid input 's': expected 'n/N' (line 3, column 2 (offset: 154))
"using scan n:Person"
  ^

This phenomenon also exists if I specify the INDEX hint:

USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///path/to/data.csv' AS row FIELDTERMINATOR '\t' 
MERGE (n:Person {cstid: row.cstid}) 
USING INDEX n:Person(cstid)
ON CREATE SET n.name = row.name 
ON MATCH SET n.name = row.name;

So... Does Neo4j support using hint in MERGE clause?

Neo4j: 3.2.0 CE

cybersam cybersam · Accepted Answer · 2017-05-23T18:21:22

No, neo4j currently does not support the USING clause with the MERGE clause.

However, this logically equivalent query should work:

USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///path/to/data.csv' AS row FIELDTERMINATOR '\t' 
OPTIONAL MATCH (n:Person {cstid: row.cstid}) 
USING SCAN n:Person
FOREACH(ignored IN CASE WHEN n IS NULL THEN [1] END | CREATE (n:Person {cstid: row.cstid}))
SET n.name = row.name;

The FOREACH clause is a hack for creating the node if it does not exist. And, since you always want to SET the name property, we just do that.

How to use hint when load csv and merge data in Neo4j

1 Answers