Northwind Graph

From RDBMS to Graph, using a classic dataset


TheNorthwind Graph demonstrates how to migrate from a relational database to Neo4j. The transformation is iterative and deliberate, emphasizing the conceptual shift from relational tables to the nodes and relationships of a graph.

This guide will show you how to:

  1. Load: create data from external CSV files

  2. Index: index nodes based on label

  3. Relate: transform foreign key references into data relationships

  4. Promote: transform join records into relationships

Product Catalog


Northwind sells food products in a few categories, provided by suppliers. Let’s start by loading the product catalog tables.

The load statements to the right require public internet access.LOAD CSV will retrieve a CSV file from a valid URL, applying a Cypher statement to each row using a named map (here we’re using the name row).

image

Create Constraints

Create constraints for the nodes
CREATE CONSTRAINT product_productID IF NOT EXISTS FOR (p:Product) REQUIRE (p.productID) IS UNIQUE;
CREATE CONSTRAINT category_categoryID IF NOT EXISTS FOR (c:Category) REQUIRE (c.categoryID) IS UNIQUE;
CREATE CONSTRAINT supplier_supplierID IF NOT EXISTS FOR (s:Supplier) REQUIRE (s.supplierID) IS UNIQUE;

Load records

Load products
LOAD CSV WITH HEADERS FROM "https://data.neo4j.com/northwind/products.csv" AS row
CREATE (n:Product)
SET n = row,
  n.unitPrice = toFloat(row.unitPrice),
  n.unitsInStock = toInteger(row.unitsInStock), n.unitsOnOrder = toInteger(row.unitsOnOrder),
  n.reorderLevel = toInteger(row.reorderLevel), n.discontinued = (row.discontinued <> "0")
Load categories
LOAD CSV WITH HEADERS FROM "https://data.neo4j.com/northwind/categories.csv" AS row
CREATE (n:Category)
SET n = row
Load suppliers
LOAD CSV WITH HEADERS FROM "https://data.neo4j.com/northwind/suppliers.csv" AS row
CREATE (n:Supplier)
SET n = row

Product Catalog Graph


The products, categories and suppliers are related through foreign key references. Let’s promote those to data relationships to realize the graph.

image

Create data relationships

Connect products to categories
MATCH (p:Product),(c:Category)
WHERE p.categoryID = c.categoryID
CREATE (p)-[:PART_OF]->(c)

Note you only need to compare property values like this when first creating relationships

Calculate join, materialize relationship. (See importing guide for more details)

Connect products to suppliers
MATCH (p:Product),(s:Supplier)
WHERE p.supplierID = s.supplierID
CREATE (s)-[:SUPPLIES]->(p)

Note you only need to compare property values like this when first creating relationships

Querying Product Catalog Graph


Lets try some queries using patterns.

image

Query using patterns

List the product categories provided by each supplier
MATCH (s:Supplier)-->(:Product)-->(c:Category)
RETURN s.companyName as Company, collect(distinct c.categoryName) as Categories
List suppliers for the "Produce" category
MATCH (c:Category {categoryName:"Produce"})<--(:Product)<--(s:Supplier)
RETURN DISTINCT s.companyName as ProduceSuppliers

Find the produce suppliers.

Customer Orders


Northwind customers place orders which may detail multiple products.

customer orders

Load and index records

Create constraints for nodes
CREATE CONSTRAINT customer_customerID IF NOT EXISTS FOR (c:Customer) REQUIRE (c.customerID) IS UNIQUE;
CREATE CONSTRAINT order_orderID IF NOT EXISTS FOR (o:Order) REQUIRE (o.orderID) IS UNIQUE;
Load customers
LOAD CSV WITH HEADERS FROM "https://data.neo4j.com/northwind/customers.csv" AS row
CREATE (n:Customer)
SET n = row
Load orders
LOAD CSV WITH HEADERS FROM "https://data.neo4j.com/northwind/orders.csv" AS row
CREATE (n:Order)
SET n = row

Create data relationships


Connect customers to their orders
MATCH (c:Customer),(o:Order)
WHERE c.customerID = o.customerID
CREATE (c)-[:PURCHASED]->(o)

Note you only need to compare property values like this when first creating relationships

Customer Order Graph


Notice that Order Details are always part of an Order and that theyrelate the Order to a Product — they’re a join table. Join tables are always a sign of a data relationship, indicating shared information between two other records.

Here, we’ll directly promote each OrderDetail record into a relationship in the graph. order graph

Load and index records

Load order details and use them to connect orders to products
LOAD CSV WITH HEADERS FROM "https://data.neo4j.com/northwind/order-details.csv" AS row
MATCH (p:Product), (o:Order)
WHERE p.productID = row.productID AND o.orderID = row.orderID
CREATE (o)-[details:ORDERS]->(p)
SET details = row,
  details.quantity = toInteger(row.quantity)

Note you only need to compare property values like this when first creating relationships

Query using patterns


Find total quantity per customer in the "Produce" category
MATCH (cust:Customer)-[:PURCHASED]->(:Order)-[o:ORDERS]->(p:Product),
      (p)-[:PART_OF]->(c:Category {categoryName:"Produce"})
RETURN DISTINCT cust.contactName as CustomerName, SUM(o.quantity) AS TotalProductsPurchased

More Resources