Neo4j creating relationships using csv

Question

I am trying to create relationships between 2 types of nodes using csv file loaded. I have already created all Movies and Keywords nodes. I created also indexes on :Movie(title) and :Keyword(word).

My csv file looks like:

"title"|year|"word" //header

"Into the Wild"|2007|"1990s" //line with title, year and keyword

"Into the Wild"|2007|"abandoned-bus"

My query:

LOAD CSV WITH HEADERS FROM "file:/home/gondil/temp.csv" AS csv
FIELDTERMINATOR '|'
MATCH (m:Movie {title:csv.title,year: toInt(csv.year)}), (k:Keyword {word:csv.word})
MERGE (m)-[:Has {weight:1}]->(k);

Query runs for about one hour and than it shows error "Unknown error". What a redundant Error description.

I thought it is due to 160K keywords and over 1M movies and over 4M lines in csv. So I shorten a csv to just one line and it is still running for about 15 minutes with no stop.

Where is the problem? How to write a query for creating relationships between 2 already created nodes?

I can also delete all nodes and build my database other way but it will be better to not delete all that created nodes.

Note: I shouldn't have a hardware problems cause I use Super PC from our faculty.

Answer 1

Be sure to have schema indexes in place to speed up looking up start nodes. Before running the import do a:

CREATE INDEX ON :Movie(title)
CREATE INDEX ON :Keyword(word)

Make sure the indexes are populated and online (check with :schema command).

Refactor your Cypher command into two queries, to make use of the indexes - for now a index consists only of a label and one property:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:/home/gondil/temp.csv" AS csv
FIELDTERMINATOR '|'
MERGE (m:Movie {title:csv.title })
ON CREATE SET m.year = toInt(csv.year)
MERGE (k:Keyword {word:csv.word})

second pass over the file

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:/home/gondil/temp.csv" AS csv
FIELDTERMINATOR '|'
MATCH (m:Movie {title:csv.title })
MATCH (k:Keyword {word:csv.word})
MERGE (m)-[:Has {weight:1}]->(k);