One of the most common questions we get at Neo4j is how to move from a SQL database to a Graph Database like Neo4j. The previous solution for accomplishing this was to export the SQL tables into CSV files and then importing the CSV files with neo4j-import or LOAD CSV. There’s a much better way: JDBC!
Neo4j JDBC Support
There are two distinct ways you can use JDBC within Neo4j:
- Access Neo4j Data via JDBC. Do you have existing code that accesses your SQL database using JDBC, and you want to move that code to access Neo4j instead? Neo4j has a JDBC Driver. Just update your code to use the awesome power of the Cypher query language instead of SQL, and switch over the JDBC driver you’re using, and you’re off to the races!
- Import SQL Databases into Neo4j. Do you have data in your SQL database that you want to move into a Graph? The APOC library for Neo4j has a set of procedures in
apoc.load.jdbcto make this simple. This blog post will cover this use case.
Loading Sample Northwind SQL tables into MySQL
In order to run the code snippets in the following sections, you’ll need to have the Northwind SQL tables in a MySQL database accessible from your Neo4j server. I’ve published a GitHub Gist of the SQL script which you can execute in MySQL Workbench or using the command-line client.
In order to run this, I created a blank MySQL database in Docker:
|
1 |
docker run -P -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=northwind -e MYSQL_USER=northwind -e MYSQL_PASSWORD=my-secret-pw mysql |
Loading data from RDBMS into Neo4j using JDBC
With the APOC JDBC support, you can load data from any type of database which supports JDBC. In this post, we’ll talk about moving data from a MySQL database to Neo4j, but you can apply this concept to any other type of database: PostgreSQL, Oracle, Hive, etc. You can use it for other NoSQL databases too, but APOC has direct support for MongoDB, Couchbase and more.
1. Install APOC and JDBC Driver into Neo4j plugins directory
Note: This step is not necessary if you’re using the Neo4j Sandbox and MySQL or PostgreSQL. Each Sandbox comes with APOC and the JDBC drivers for these database systems.
All JAR files placed in the Neo4j plugins directory are made available for use by Neo4j. We need to copy the APOC library and JDBC drivers into this directory.
First, download APOC. Be sure to grab the download that is for your version of Neo4j.
Next, download the JDBC driver. Then, copy the file into your plugins directory:
|
1 |
cp mysql-connector-java-5.1.36.jar ~/neo4j/plugins/ |
Finally, restart Neo4j on your system.
2. Register the JDBC Driver with APOC
Open up the Neo4j Browser web interface:
In the Neo4j Browser, enter a Cypher statement to load the required JDBC driver:
|
1 |
CALL apoc.load.driver("com.mysql.jdbc.Driver"); |
3. Start pulling Northwind SQL tables into Neo4j with JDBC and Cypher
Run the following Cypher queries, courtesy of William Lyon, separately in the Neo4j Browser:
|
1 2 3 4 5 6 |
// Create Product nodes based on each row of the Products table CALL apoc.load.jdbc("jdbc:mysql://:3306/northwind?user=northwind&password=my-secret-pw","Products") YIELD row CREATE (p:Product {ProductID: row.ProductID}) SET p.ProductName = row.ProductName, p.CategoryID = row.CategoryID, p.SupplierID = row.SupplierID; |
|
1 2 3 4 5 |
// Create Orders nodes CALL apoc.load.jdbc("jdbc:mysql://:3306/northwind?user=northwind&password=my-secret-pw","Orders") YIELD row CREATE (o:Order {OrderID: row.OrderID}) SET o.CustomerID = row.CustomerID, o.EmployeeID = row.EmployeeID; |
|
1 2 3 4 5 6 7 8 |
// Create OrderDetails relationships CALL apoc.load.jdbc("jdbc:mysql://:3306/northwind?user=northwind&password=my-secret-pw","OrderDetails") YIELD row MATCH (p:Product {ProductID: row.ProductID}) MATCH (o:Order {OrderID: row.OrderID}) CREATE (o)-[r:CONTAINS]->(p) SET r.UnitPrice = row.UnitPrice, r.Quantity = row.Quantity, r.Discount = row.Discount; |
|
1 2 3 4 |
// create PLACED relationships MATCH (o:Order) MATCH (c:Customer {CustomerID: o.CustomerID}) CREATE (c)-[:PLACED]->(o); |
Running Cypher Queries on Imported Data
Here’s a simple Cypher query for collaborative filtering product recommendations:
|
1 2 3 4 5 |
// simple collaborative filtering product recommendations MATCH (c:Customer) WHERE c.ContactName = "Roland Mendel" MATCH (c)-[:PLACED]->(o:Order)-[:CONTAINS]->(p:Product) MATCH (p)<-[:CONTAINS]-(:Order)(:Order)-[:CONTAINS]->(p2:Product) RETURN p2.ProductName, count(*) AS weight ORDER BY weight DESC LIMIT 10; |
Results:
Next Steps
If this was your first experience with Neo4j, you probably want to learn more about Neo4j’s Cypher query language. Neo4j has some great (free) online training you can take to learn more. You can also use the Cypher Refcard to power your journey to becoming a Graphista.
Be First to Comment