Skip to main content

Model a graph and load it: constraints, indexes, LOAD CSV

The Veille team mini-graph fits in eleven nodes: perfect to grasp Cypher, insufficient for production. Inès asks Sami to load the real corpus, the 200,853 articles of the News dataset, into Neo4j so that Karim can then write the recommendation. This module teaches him how to move from a flat table (news.csv) to a relevant graph, how to place constraints and indexes in the right order, and how to load the whole thing in twenty-five seconds with LOAD CSV WITH HEADERS and CALL { } IN TRANSACTIONS.

From table to graph: three questions​

A CSV file lists articles with their columns; a graph connects entities. The transition happens by asking three questions of each column.

  • Are we going to filter or traverse it? If yes, it deserves to become a node. A category we are going to list, filter and follow is a node; an identifier we never look at stays a property.
  • Does it link two entities? Then it is a relationship. "Article X is published in category Y" is not a property, it is a PUBLIE_DANS relationship.
  • Do we just want to display it? It stays a property of the node it belongs to. The article's title, date and link are properties: we do not search "all articles with exactly this title", we display them once the article is found.

Two naming conventions keep the graph readable.

  • Labels: one word, singular, in PascalCase — Article, Categorie, Auteur. Never Articles, never article.
  • Relationships: uppercase, often a verb in the third person — PUBLIE_DANS, ECRIT_PAR, HABITE. The arrow direction goes from subject to complement: "the article is published in the category", hence (:Article)-[:PUBLIE_DANS]->(:Categorie).

These conventions are not decorative: they let a Cypher pattern read like a sentence. MATCH (a:Article)-[:ECRIT_PAR]->(au:Auteur) reads effortlessly as "article a is written by author au". That is the first gain over SQL.

The Veille model​

Three entities, two relationships, no ambiguity.

(:Article {id, titre, date, lien})-[:PUBLIE_DANS]->(:Categorie {nom})
(:Article)-[:ECRIT_PAR]->(:Auteur {nom})

Every article has a category (mandatory relationship) and zero, one or several authors (optional and multiple relationship). Five properties in total: id and nom serve as uniqueness keys, titre, date and lien are there for display and sorting. Nothing more. We do not duplicate the title on the author, nor the category on the article: the graph does it for us.

Why separate Categorie from the article's category field?

In Elasticsearch, category stays a keyword string on the document: it is the object we search over. In Neo4j, we want to list categories, count their articles, jump from an author to their favorite categories — hence turning it into a node. The two engines model different things because they answer different families of questions.

The news.csv file, prerequisite for the load​

The Cypher script downloads nothing: it reads a CSV file already present on the Neo4j container's disk. That file is written by the Elasticsearch importer (module 3) at the moment it indexes the 200,853 documents.

  • Host volume: kits/42-elasticsearch-neo4j/neo4j/import/news.csv.
  • Container volume: /import/news.csv, path file:///news.csv inside LOAD CSV.
  • Written by: ./lab.sh import-news, at the same time it sends the documents into the news index.
  • Columns (in this order, with header): id, headline, category, authors, date, link.
You must have run import-news beforehand

Without the news.csv file, LOAD CSV fails with "Couldn't load the external resource". The correct sequence is always ./lab.sh up, then ./lab.sh import-news (which writes the CSV), then only ./lab.sh cypher 11-charger-news.cypher. If the CSV is missing, the error points to the /import directory mounted in the container: Cypher is not broken, the order of the steps is.

The 11-charger-news.cypher script, block by block​

The kit ships neo4j/cypher/11-charger-news.cypher. Run it with one command.

./lab.sh cypher 11-charger-news.cypher

On a properly sized machine, the script finishes in about twenty-five seconds. Let's comb through it.

Block 1 — constraints and indexes, before the data​

CREATE CONSTRAINT article_id IF NOT EXISTS FOR (a:Article) REQUIRE a.id IS UNIQUE;
CREATE CONSTRAINT categorie_nom IF NOT EXISTS FOR (c:Categorie) REQUIRE c.nom IS UNIQUE;
CREATE CONSTRAINT auteur_nom IF NOT EXISTS FOR (au:Auteur) REQUIRE au.nom IS UNIQUE;
CREATE INDEX article_date IF NOT EXISTS FOR (a:Article) ON (a.date);

Three uniqueness constraints, one per label, on the property that identifies the entity (id for an article, nom for a category and an author). A uniqueness constraint implicitly creates an index on the same property: the future MERGE (a:Article {id: 42}) becomes a key lookup, not a full scan. Without these constraints, each MERGE on the 200,853 rows would check for existence with a sweep, and the load would take several hours instead of twenty-five seconds.

The separate index on Article.date is not required by the load itself: it prepares module 12, where we will frequently filter by period. IF NOT EXISTS makes the whole set idempotent — rerunning the script has no effect if everything is already in place.

Constraints come before the data

The rule is absolute for a massive load: constraint first, then the MERGE. Adding the constraint after the fact works but forces Neo4j to validate a posteriori on millions of rows. The opposite rule — "data first, indexes later" — comes from the SQL world and does not apply to Neo4j.

Block 2 — articles and categories, in batches of 5,000 rows​

LOAD CSV WITH HEADERS FROM 'file:///news.csv' AS ligne
CALL {
  WITH ligne
  MERGE (a:Article {id: toInteger(ligne.id)})
    SET a.titre = ligne.headline,
        a.date  = date(ligne.date),
        a.lien  = ligne.link
  MERGE (c:Categorie {nom: ligne.category})
  MERGE (a)-[:PUBLIE_DANS]->(c)
} IN TRANSACTIONS OF 5000 ROWS;

Seven lines, three ideas.

First, LOAD CSV WITH HEADERS reads the file and turns each line into a map (map) whose keys are the headers. Every field is a string: that is why we write toInteger(ligne.id) (uniqueness goes through an integer, not the string "42"), and date(ligne.date) (the ISO format yyyy-MM-dd is recognized by the date() function, which creates a true temporal type we can compare with <, sort and index).

Next, the CALL { ... } subquery isolates the work to run for each line. It contains three MERGEs: the Article node (created if absent, otherwise enriched with SET), the Categorie node, and finally the relationship between the two. MERGE on the relationship guarantees that the same article will not be attached twice to its category even if the script is rerun.

Finally, IN TRANSACTIONS OF 5000 ROWS asks Neo4j to commit every 5,000 rows rather than run one giant transaction. This batch is a trade-off: too small, the transaction overhead dominates; too large, memory blows up. 5,000 is a value that works everywhere on a workstation with 2 GB of heap.

Block 3 — authors, splitting co-signatures​

Articles in the News set sometimes mix several authors in the same field, with two separators: the comma ("Lee Moran, Ron Dicker") and the word "and" ("Lee Moran and Ron Dicker"). We need to split.

LOAD CSV WITH HEADERS FROM 'file:///news.csv' AS ligne
WITH ligne WHERE ligne.authors <> ''
CALL {
  WITH ligne
  MATCH (a:Article {id: toInteger(ligne.id)})
  UNWIND [x IN split(replace(ligne.authors, ' and ', ', '), ', ') WHERE trim(x) <> ''] AS nom
  MERGE (au:Auteur {nom: trim(nom)})
  MERGE (a)-[:ECRIT_PAR]->(au)
} IN TRANSACTIONS OF 5000 ROWS;

Two novelties compared to the previous block.

The WHERE ligne.authors <> '' filter ignores articles without an author (about 8% of the corpus): we do not create an empty author. It is important that it sits outside the CALL { ... }, before IN TRANSACTIONS, otherwise Neo4j refuses the syntax.

Co-author handling fits on one line: replace(ligne.authors, ' and ', ', ') unifies the separators, split(..., ', ') produces a list, the comprehension [x IN ... WHERE trim(x) <> ''] filters out empty entries, and UNWIND re-unfolds the list into one row per author. For each name, MERGE (au:Auteur {nom: trim(nom)}) creates the author if absent, and MERGE (a)-[:ECRIT_PAR]->(au) the relationship with the article. trim(nom) strips stray whitespace ("Lee Moran" and "Lee Moran " are the same author, not two).

The MATCH (a:Article {id: toInteger(ligne.id)}) works because the previous block already created every article. If the order of the two blocks were reversed, the MATCH would fail silently and no relationship would be created.

Block 4 — the tally​

MATCH (a:Article)   WITH count(a) AS articles
MATCH (c:Categorie) WITH articles, count(c) AS categories
MATCH (au:Auteur)   RETURN articles, categories, count(au) AS auteurs;

The MATCH ... WITH ... MATCH ... chain counts each label while isolating scopes: WITH articles carries the previous counter into the new scope. At the finish line:

articles | categories | auteurs
---------+------------+--------
  200853 |         41 |   23082

The kit has been tested end to end: these three numbers must appear identically in your console. If they differ, there is only one cause: news.csv was regenerated with a non-zero NEWS_LIMIT, or the load was interrupted along the way. ./lab.sh cypher 99-reset.cypher then rerun.

Neo4j Browser: the mandatory :auto prefix​

The script runs perfectly when launched from the terminal via ./lab.sh cypher 11-charger-news.cypher. But if you paste block 2 directly into Neo4j Browser (http://localhost:7474) and run it, Neo4j answers:

A query with 'CALL { ... } IN TRANSACTIONS' can only be executed
in an implicit transaction, but tried to execute in an explicit transaction.

The reason: by default, Neo4j Browser wraps every query in an explicit transaction (the invisible begin / commit). Yet CALL { } IN TRANSACTIONS manages its own batch commits; the two regimes are incompatible. You must therefore prefix the query with :auto to tell Browser to let the subquery manage.

:auto LOAD CSV WITH HEADERS FROM 'file:///news.csv' AS ligne
CALL {
  WITH ligne
  MERGE (a:Article {id: toInteger(ligne.id)})
    SET a.titre = ligne.headline
  MERGE (c:Categorie {nom: ligne.category})
  MERGE (a)-[:PUBLIE_DANS]->(c)
} IN TRANSACTIONS OF 5000 ROWS;

./lab.sh cypher goes through cypher-shell with the -f option, which already opens an implicit transaction: no need for :auto there. It is one of those cases where the kit silently helps.

:auto only in Browser

:auto is a Browser client command, not a Cypher language feature. It disappears in cypher-shell and in the drivers (Python, Java, JS) because those clients already control the transaction mode.

Verifying the graph​

Three useful queries to validate the load and spot anomalies before moving on to module 12.

Global statistics with APOC — number of nodes and relationships per label:

CALL apoc.meta.stats() YIELD labels, relTypes, nodeCount, relCount
RETURN nodeCount, relCount, labels, relTypes;

labels returns a map {Article: 200853, Categorie: 41, Auteur: 23082} and relTypes a count per relationship type. That is the quick sanity check that replaces a SHOW STATS: two seconds, the whole graph.

Schema in visual form — Neo4j Browser draws nodes and relationships with:

CALL db.schema.visualization();

Expected: three circles (Article, Categorie, Auteur) connected by two arrows (PUBLIE_DANS, ECRIT_PAR). If you see extra labels (for example Personne from an older script), the database has not been reset.

Constraints and indexes — the exhaustive list:

SHOW CONSTRAINTS;
SHOW INDEXES;

You should find article_id, categorie_nom, auteur_nom (uniqueness constraints) and article_date (index). Every constraint also appears in SHOW INDEXES since it creates one implicitly.

Resetting: 99-reset.cypher​

A botched manipulation happens to everyone. The reset script is short and uses APOC to avoid the traps.

./lab.sh cypher 99-reset.cypher

Its content:

CALL apoc.periodic.iterate(
  'MATCH (n) RETURN n',
  'DETACH DELETE n',
  {batchSize: 10000, parallel: false}
) YIELD batches, total
RETURN batches AS lots, total AS noeuds_supprimes;

CALL apoc.schema.assert({}, {}, true) YIELD label, key, action
RETURN label, key, action;

apoc.periodic.iterate takes a producer query (MATCH (n) RETURN n) and a consumer query (DETACH DELETE n), which it applies in batches of 10,000: each batch is committed, memory never climbs. parallel: false preserves order and avoids locking on the same relationships. Without APOC, you would have to write a loop by hand with CALL { } IN TRANSACTIONS.

The second procedure, apoc.schema.assert({}, {}, true) with the third argument at true, drops all existing constraints and indexes. On exit, the database is empty of data and of schema: perfect to start over. Without it, rerunning 11-charger-news.cypher would reuse the constraints already there — which is fine but prevents testing the "fresh install" path.

Try it 1 — Top five categories by article count​

Write the Cypher query that lists the five categories with the most articles, along with their count.

Solution
MATCH (a:Article)-[:PUBLIE_DANS]->(c:Categorie)
RETURN c.nom AS categorie, count(a) AS articles
ORDER BY articles DESC
LIMIT 5;

Expected: POLITICS 32,739, WELLNESS 17,827, ENTERTAINMENT 16,058, TRAVEL 9,887, STYLE & BEAUTY 9,649. The same numbers as Elasticsearch's terms, obtained by a direct traversal of the relationships.

Try it 2 — 2018 articles in POLITICS​

Find the articles published in 2018 in the POLITICS category, sorted from most recent to oldest, limited to five.

Solution
MATCH (a:Article)-[:PUBLIE_DANS]->(:Categorie {nom: 'POLITICS'})
WHERE a.date >= date('2018-01-01') AND a.date <= date('2018-12-31')
RETURN a.titre, a.date
ORDER BY a.date DESC
LIMIT 5;

The filter a.date >= date('2018-01-01') benefits from the article_date index set in block 1: the query starts directly on the expected range instead of scanning the 200,853 articles.

Try it 3 — Co-authors of Lee Moran​

Count the authors who have co-signed at least one article with Lee Moran (distinct authors, excluding Lee Moran himself).

Solution
MATCH (lee:Auteur {nom: 'Lee Moran'})<-[:ECRIT_PAR]-(a:Article)-[:ECRIT_PAR]->(autre:Auteur)
WHERE autre <> lee
RETURN count(DISTINCT autre) AS co_auteurs;

The pattern (lee)<-[:ECRIT_PAR]-(a)-[:ECRIT_PAR]->(autre) walks up the articles signed by Lee, then back down to their other authors. count(DISTINCT ...) avoids duplicates when two authors share several articles. (Your number may differ slightly depending on how author names were cleaned.)

Key takeaways​

  • An entity you filter or traverse becomes a node; an entity you display stays a property.
  • Labels singular in PascalCase (Article), relationships in UPPERCASE_VERB (PUBLIE_DANS).
  • Uniqueness constraints come before the data: they create a free index and make a massive MERGE viable (25 s instead of several hours).
  • LOAD CSV WITH HEADERS FROM 'file:///...' reads from the /import volume mounted on neo4j/import/ — so ./lab.sh import-news is a strict prerequisite.
  • CALL { ... } IN TRANSACTIONS OF 5000 ROWS commits in batches; in Neo4j Browser you must prefix with :auto, not in ./lab.sh cypher.
  • The Veille graph contains 200,853 articles, 41 categories, 23,082 authors — three numbers to remember for what comes next.
  • apoc.periodic.iterate + apoc.schema.assert = clean reset in one command (99-reset.cypher).

Troubleshooting​

  • LOAD CSV returns "Couldn't load the external resource" → the news.csv file is not in neo4j/import/ → run ./lab.sh import-news then rerun ./lab.sh cypher 11-charger-news.cypher.
  • Error "A query with CALL { ... } IN TRANSACTIONS can only be executed in an implicit transaction" → you are in Neo4j Browser → prefix the query with :auto, or go through ./lab.sh cypher 11-charger-news.cypher.
  • The load takes several minutes and never finishes → constraints were not created before the MERGEs → ./lab.sh cypher 99-reset.cypher then rerun 11-charger-news.cypher in the right order.
  • apoc.periodic.iterate returns "Unknown procedure" → the neo4j container started without APOC → ./lab.sh logs neo4j (search for "Loaded apoc"), otherwise ./lab.sh reset then ./lab.sh up.

Further reading​

Next module: exploit the News graph with advanced Cypher — variable-length paths, aggregations, WITH/UNWIND, PROFILE and real neighborhood-based recommendation logic.