Written by Brian Underwood, originally posted on his blog.

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.

Enter neo4apis-activerecord!

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

Setup

Firstly, if you don’t already have neo4j on your computer, find installation instructions for your computer in theneo4j 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
For examples on how to configure mysql or sqlite, see this github gist or the official documentation

The Command

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:
    • The all_tables command finds all tables in the database and imports them.
    • By default ActiveRecord table naming conventions will be used. The --identify-model option however will use a looser set of assumptions and configure ActiveRecord models according to the tables it finds in your database.
    • The --import-all-associations option will import ActiveRecord associations and create Neo4j relationships from them. When no existing ActiveRecord models are used, those associations come from using the --identify-model option.
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 aconfig/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


The 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!


Want to learn more about graph databases? Click below to get your free copy of O’Reilly’s Graph Databases ebook and discover how to use graph technologies for your application today.

Download My Ebook Copy

 

Keywords:  


8 Comments

Rafael Oropeza says:

Hi, I’m very new to NEO4J and more newbie to Ruby. After a little effort installing neo4apis activerecord and creating Chinook database on MySql, I have the following error

/usr/lib/ruby/gems/1.9.1/gems/neo4apis-activerecord-0.6.1/lib/neo4apis/table_resolver.rb:29:in `block in identify_primary_key’: Could not find a primary key for PlaylistTrack. (Neo4Apis::TableResolver::UnfoundPrimaryKeyError)

Seems PlaylistTrack table has a composite key, using two fields: PlaylistId and TrackId. How did you fix this problem? Can you give me some idea to resolve this issue?

Thanks in advance for your help

Gosh, sorry! I wasn’t subscribed to these comments so I didn’t see them! I just found this because somebody else let me know they ran into the same problem. I don’t think I had it when I tried originally and I’m looking at it now!

Ok, I think I’ve fixed this. It’s not perfect, but it works 😉 Follow this issue for more:

https://github.com/neo4jrb/neo4apis/issues/7

And please comment there since I don’t get notifications about this post

Thameem says:

I followed your tutorial but getting the following error. I have two initial questions though. You mentioned that put the config/database.yml .. do i have to create the config directory anywhere and place the file or I have to create under the /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0 directory?
Second question is, how does the neo4apis command knows which profile to pickup from database.yml?

here is the error…

/usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/model_resolver.rb:6:in `included’: private method `include’ called for Neo4Apis::CLI::ActiveRecord:Class (NoMethodError)
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/cli/activerecord.rb:11:in `include’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/cli/activerecord.rb:11:in `’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/cli/activerecord.rb:10:in `’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/cli/activerecord.rb:9:in `’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/cli/activerecord.rb:8:in `’
from /usr/share/ruby/vendor_ruby/2.0/rubygems/core_ext/kernel_require.rb:55:in `require’
from /usr/share/ruby/vendor_ruby/2.0/rubygems/core_ext/kernel_require.rb:55:in `require’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis-activerecord.rb:4:in `’
from /usr/share/ruby/vendor_ruby/2.0/rubygems/core_ext/kernel_require.rb:135:in `require’
from /usr/share/ruby/vendor_ruby/2.0/rubygems/core_ext/kernel_require.rb:135:in `rescue in require’
from /usr/share/ruby/vendor_ruby/2.0/rubygems/core_ext/kernel_require.rb:144:in `require’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-0.6.0/bin/neo4apis:12:in `block in ‘
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-0.6.0/bin/neo4apis:10:in `map’
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-0.6.0/bin/neo4apis:10:in `’
from /usr/local/bin/neo4apis:23:in `load’
from /usr/local/bin/neo4apis:23:in `’

Good questions and sorry for the delay! I don’t get notifications about this thread.

You should be able to put in under `config/database.yml` in the project where you’re running the `neo4apis` command (you should create the `config` dir if it isn’t there). I think it assumes `development` if you’re not in a Rails app

If you have any other questions feel free to submit an issue here:

https://github.com/neo4jrb/neo4apis-activerecord

Or catch up with us on on Slack:

http://neo4j.com/blog/public-neo4j-users-slack-group/

Thameem says:

Little more improvement. After reading thru ruby docs I found out that the way you can include another module is using extend. Here is what i changed in model_resolver.rb
#included_class.include TableResolver
included_class.extend TableResolver

but now i am getting new error

/usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/model_resolver.rb:50:in `apply_identified_table_name!’: undefined method `identify_table_name’ for # (NoMethodError)
from /usr/local/share/ruby/gems/2.0/gems/neo4apis-activerecord-0.7.0/lib/neo4apis/model_resolver.rb:14:in `block in get_model’

Looks like its not able to find the method sitting inside table_resolver.rb. Any help would be appreciated.

Sorry for responding so late! Did you ever figure this out?

If you’re still having trouble report an issue here:

https://github.com/neo4jrb/neo4apis-activerecord

and I can take a look. Thanks!

Noel da Costa says:

Doesn’t work with MAMP

1 Trackback

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe

Upcoming Event

 

From the CEO

Emil's Blog


Have a Graph Question?

Stackoverflow
Slack
Contact Us

Share your Graph Story?

Email us: content@neotechnology.com


Popular Graph Topics