I’ve mix phx.new ed many applications and when doing so I often start with wondering how to organize my code. I love how Phoenix pushes you to think about th...
Loading SQL to Neo4j Like Magic
When using neo4j for the first time, most people want to import data from another database to start playing around. There are a lot of options including LOAD CSV, batch-import, and even using Groovy. All of these require some setup and configuration. I wanted to create the simplest SQL to Neo4j import process possible.
You may be thinking: “Brian, I’m not a Ruby programmer! I don’t know anything about ActiveRecord”.
No worries! I’ll get you there in 2 simple steps: setup and running the command
Firstly, if you don’t already have neo4j on your computer, find installation instructions for your computer in the neo4j manual.
Simply use RubyGems (RubyGems installation):
gem install neo4apis-activerecord
Then install the database adapter gem:
gem install pg
This can be
pg for PostgreSQL,
mysql2 for MySQL, or
sqlite3 for SQLite
Then create a
config/database.yml file which looks something like this:
development: adapter: postgresql encoding: unicode pool: 5 host: localhost port: 5432 database: your_database_name username: james password: reallysecret
Then to import all your data it is as simple as:
neo4apis activerecord all_tables --identify-model --import-all-associations
Let’s break that command down:
all_tablescommand finds all tables in the database and imports them.
- By default ActiveRecord table naming conventions will be used. The
--identify-modeloption however will use a looser set of assumptions and configure ActiveRecord models according to the tables it finds in your database.
--import-all-associationsoption will import ActiveRecord associations and create Neo4j relationships from them. When no existing ActiveRecord models are used, those associations come from using the
Using the above command, I was able to easily and cleanly import the Chinook Database (which doesn’t follow ActiveRecord table naming) into neo4j:
The One Small Catch
Is it perfect? Close, but not quite!
Taking the Chinook Database as an example, the
Customer table has a
SupportRepId column which references the
Employee table. There’s no way to know from examining the column name what table it is refering to. These cases require a little bit of configuration on your part. To do that, you should create a
config/environment.rb file like this:
config = YAML.load(File.read('config/database.yml'))['development'] ActiveRecord::Base.establish_connection(config) class Customer < ActiveRecord::Base belongs_to :support_rep, foreign_key: 'SupportRepId', class_name: 'Employee' end
config and the
establish_connection lines are there because
neo4apis-activerecord assumes that this file makes the connection to ActiveRecord and won’t try to do it itself. The
belongs_to is part of ActiveRecord`s well documented and heavily used API.
And of course, for those of you using ActiveRecord already this will all work out of the box!
If you want to get really down and dirty with the Ruby programming, there’s even an API for doing your own custom import of your ActiveRecord models. See the README for details!
2020Back to Top ↑
For just over a year I’ve been obsessed on-and-off with a project ever since I stayed in the town of Skibbereen, Ireland. Taking data from the 1901 and 1911...
A while ago my colleague Michael suggested to me that I draw out some examples of how my record linkage algorithm did it’s thing. In order to do that, I’ve ...
Last night I ran a very successful workshop at the Friends of Neo4j Stockholm meetup group. The format was based on a workshop that I attended in San Franci...
In my last two posts I covered the process of importing data from StackOverflow and GitHub for the purpose of creating a combined MDM database. Now we get t...
In my last post I said I would “bring in another data source, show how I linked the data together, and demonstrate the sort of bigger picture that one can ge...
Joining multiple disparate data-sources, commonly dubbed Master Data Management (MDM), is usually not a fun exercise. I would like to show you how to use a g...
When using neo4j for the first time, most people want to import data from another database to start playing around. There are a lot of options including LOA...
I am he as you are he as you are me and we are all together – The Beatles
When I told the people of Northern Ireland that I was an atheist, a woman in the audience stood up and said, ‘Yes, but is it the God of the Catholics or t...
“Wilkins! Yes! I’ve considered decorating these walls with some graffiti of my own, and writing it in the Universal Character.. but it is too depressing...