JSON import to Postgres

[Brandon Hilkert]

I have a 3GB json dump that I need to import into a Rails App. Writing a script that uses ActiveRecord has proved to take too long. Outside of dropping down to the SQL level, anyone have experience/suggestions doing this sort of thing in a timely manner?

Thanks,

Brandon

------------------------------------

http://brandonhilkert.com

[Walter Lee Davis]

I used a Gem to store/load a whole lot of ActiveRecord as YAML, there may be something similar that uses JSON. Is that one huge-ass file, or can it be broken into bite-size chunks?

http://rubydoc.info/gems/yaml_db/0.2.3/frames

Walter

> --
> You received this message because you are subscribed to the Google Groups "Philly.rb" group.
> To post to this group, send email to phil...@googlegroups.com.
> To unsubscribe from this group, send email to phillyrb+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/phillyrb?hl=en.

[Randy Schmidt]

On Fri, Nov 2, 2012 at 11:51 AM, Brandon Hilkert
<brandon...@gmail.com> wrote:
> I have a 3GB json dump that I need to import into a Rails App. Writing a
> script that uses ActiveRecord has proved to take too long. Outside of
> dropping down to the SQL level, anyone have experience/suggestions doing
> this sort of thing in a timely manner?

Can you convert it to a CSV or multiple CSVs easily? Maybe use raw
Ruby to convert it from JSON to CSV? I know MySQL can import stuff
from a CSV VERY fast... postgres probably can too.

Actually, it looks like:

COPY zip_codes FROM '/path/to/csv/whatevs.txt' DELIMITERS ',' CSV;

is how you do it in postgres.

--
Randy Schmidt

[Angel Pizarro]

Even using batch transactions with the activerecord-extensions gem? -a

--

[Brandon Hilkert]

Thanks for the suggestions guys. The file is one big dump from MongoDB. Unfortunately there's a bunch of embedded documents in the dump, so I can just drop it in the DB, without first parsing the embedded things and putting them in their appropriate places.

Sounds like this is going to be a pain...

------------------------------------

http://brandonhilkert.com

[Walter Lee Davis]

Could you bring it up again in a different Mongo, then iterate over all of the records and build up a new ActiveRecord (with nested children where needed) for each one?

Walter

[Brandon Hilkert]

Walter - The Rails app is currently backed by MongoDB. So the app is up and connected. I started doing what you suggested and at the rate it was going, it would've taken 10 hours or so. 

I also tried doing the same thing, and pushing the conversion of each record to Sidekiq to attempt to take advantage of the multi-threading, but it took even longer to queue the jobs and the weren't processing as fast as I expected. Doing it inline actually was faster (that surprised me...). Probably has something to do with the power of the db engines on my local machine, but it's not like we're using anything that much more powerful in production.

So i thought about just ditching AR all together, which I'm guessing will speed things up, but be a big ball of messy SQL. So I began to explore any benefits that the JSON import could offer.

And...that's where I'm at.

So in short, yeah I have access to the MongoDB instance with the data in it.

------------------------------------

http://brandonhilkert.com

[Steve Eichert]

+1 to Randy's suggestion.  I'd write a script that iterates over the documents and spits them out to CSV file(s).  Then import those CSV files into MySQL.  During import you can either import directly into the destination tables OR If there is some hierarchy that you need to maintain or other trickery load them into a few temporary tables that you can then use SQL against to copy to the right destination tables (with necessary JOIN's and such).  In my experience the trickier bit to this is keeping the identifiers and such around that you need to maintain the right relationships in the MySQL database but its usually not too hard to figure out a solution to this (add a column to the DB that you can later drop, etc.).

Cheers,

Steve

[Steve Eichert]

Sorry, replace all instances of MySQL with Postgres in my response :-)

[Angel Pizarro]

Looks like ar-extensions not supported for Rails 3. Pointed to this instead

http://rdoc.info/gems/activerecord-import/frames

[Mat Schaffer]

Would that library Chris Le presented a couple months back be useful here? I apparently didn't add it to the even page so I can't remember what it was called.

-Mat

about.me/matschaffer

--

[Chris Le]

Yep. I did a presentation on it. It's not the BEST library i've seen, but it managed to handle everything I threw at it. And I pull from all sorts of messy data sources.

--

Chris Le

Seer Interactive

@djchrisle | http://www.seerinteractive.com/team/chris/

215.967.4461 x28

[Brandon Hilkert]

Chris - Did you open source it?

------------------------------------

http://brandonhilkert.com

[Chris Le]

I didn't make any of those gems.  Here are the links:

https://github.com/seamusabshere/data_miner

https://github.com/ruport/ruport

working together in the gist: https://gist.github.com/4002603

[Xiaoyi Lu]

Unfortunately, I have to do something similar to you. Whats worse is we use mongoid-history which means there are a messy history data to clean up on top of the whole complexity. Which approach did you end up going for?

[Dwyer, Mike]

Out of curiosity, why the switch? Is that something you guys can share?

I'm looking into using a NoSQL database as part of one of my projects....

--

You received this message because you are subscribed to the Google Groups "Philly.rb" group.

To unsubscribe from this group and stop receiving emails from it, send an email to phillyrb+u...@googlegroups.com.

To post to this group, send email to phil...@googlegroups.com.

Visit this group at http://groups.google.com/group/phillyrb.
For more options, visit https://groups.google.com/d/optout.

--
Thanks,

Mike

[Chad Ostrowski]

Mike: I obviously can't speak for Xiaoyi, and I haven't implemented an app with a NoSQL store, but Sarah Mei's article Why You Should Never Use MongoDB has good thoughts on the subject.

[Xiaoyi Lu]

I dont think MongoDB is suitable for Enterprise solutions especially you need lots of reporting. There is no join in mongodb (you can hack it but you are not supposed to do also its dead slow). 

[Xiaoyi Lu]

If the project need lots of reporting which means you need lots of joins, then DO NOT use mongodb

On Tuesday, 5 August 2014 01:33:14 UTC+8, Mike Dwyer wrote:

[Justin Campbell]

Brandon, I've had great results using https://github.com/zdennis/activerecord-import to speed up bulk data imports.

-Justin

Sent from my iPhone

[Walter Lee Davis]

If your goal is simply to move a database from here to there, then https://github.com/ludicast/yaml_db is my go-to. Very simple, very reliable. I have used it to move from a test site in one database that suddenly gains "useful" data to a new production server elsewhere in another. I've used it to move from SQLite to MySQL and from MySQL to PostgreSQL.

Walter

[Brandon Hilkert]

Hey guys,

Thanks for all the suggestions. I must have posted this about 2 years. And consequently haven't used Mongo for quite awhile. But hopefully for anyone that does, they got something out of the recent comments. 

Thanks!

--

------------------------------------

The Build a Ruby Gem guide is available!

http://brandonhilkert.com

[Dwyer, Mike]

I appreciate it too... I've been considering using a NoSQL store to speed up one of my apps using it mostly as a "cache" to add/remove records to for fast access while using a mechanism for pushing updates, etc. to the main store in MySQL. It would also let the user "play" with the data until they were ready to commit it fully to MySQL.

Most of my attention has been on Redis, but I have concerns about scalability and heavy multiuser access simply because I'm not as familiar with these types of databases.

Anybody have thoughts on this?

[Chad Ostrowski]

There was a great talk about Event Store at Philly ETE. It's one of those append-only, never-delete-anything databases. You can think of it as basically a big log, which is highly optimized for writing. Then you can project that data into a relational store, a document store, a graph store, and more. All projected databases are essentially caches, which are highly optimized for reading.

I love the idea of this.

I'm not sure if there are good libraries for working with Event Store in Ruby/Rails. Just figured I would mention it, because it seems like a really smart way of dealing with data.

[Maurício Linhares]

Redis is good is you don't care about the data, can easily re-create
it and don't need multi-master replication, if any of these are
concerns for you, then it's probably not what you're looking for.

I'm finding Bill Howe's list of databases and features really
interesting, might be good for you to figure out what to do and how to
pick the best one.

From my personal experience, MongoDB is good for a read-mostly use
case, if you need lots and reads and writes you'll probably have
trouble with it.

-
Maurício Linhares
http://mauricio.github.io/ - http://twitter.com/#!/mauriciojr