Reading millions of lines from a RDF file to Postgres

177 views
Skip to first unread message

Rohit Mishra

unread,
Mar 28, 2013, 3:11:17 AM3/28/13
to bangal...@googlegroups.com
Hi, 

I am working on importing DBpedia RDF dumps in my postgres database. The title dump file that I am working on has > 9 million rows. I know that the right approach to use this kind of data is to use a graph db, but I do not want to complicate my stack with something like Neo4j now. 

I am reading the RDF dump using the RDF gem and doing a couple of ActiveRecord transactions for insertion and association. But this process is slow, and will take many days to complete. How can I speed up this process?

Thanks,

Swanand Pagnis

unread,
Mar 28, 2013, 3:24:33 AM3/28/13
to bangal...@googlegroups.com
Hadoop. :-) It is built for this purpose.


--
You received this message because you are subscribed to the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bangalorerug...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Anand Chitipothu

unread,
Mar 28, 2013, 4:44:15 AM3/28/13
to bangal...@googlegroups.com


On Thursday, March 28, 2013, Rohit Mishra wrote:
Hi, 

I am working on importing DBpedia RDF dumps in my postgres database. The title dump file that I am working on has > 9 million rows. I know that the right approach to use this kind of data is to use a graph db, but I do not want to complicate my stack with something like Neo4j now. 

I am reading the RDF dump using the RDF gem and doing a couple of ActiveRecord transactions for insertion and association. But this process is slow, and will take many days to complete. How can I speed up this process?

If your tables have any indexes, remove them. That'll speed up your inserts considerably fast. You can add them back  later  once you are done with loading the data.

Anand


--
Anand
http://anandology.com/

Rohit Mishra

unread,
Apr 4, 2013, 6:40:26 AM4/4/13
to bangal...@googlegroups.com
Thanks. I have removed the indexes, but it is still pretty slow. 

I read more on this, and one of the suggested methods to speed things up is to disable autocommits. I haven't been able to find any documentation on how to do this till now. If anyone of you have any pointers on how to do this, please share.  

Anand


--
Anand
http://anandology.com/

Thanks,
Rohit

--
Rohit Mishra

Hemant Kumar

unread,
Apr 4, 2013, 7:01:57 AM4/4/13
to bangal...@googlegroups.com
Hi Rohit,

On Thu, Apr 4, 2013 at 4:10 PM, Rohit Mishra <ro...@rohitmishra.me> wrote:

Thanks. I have removed the indexes, but it is still pretty slow. 

I read more on this, and one of the suggested methods to speed things up is to disable autocommits. I haven't been able to find any documentation on how to do this till now. If anyone of you have any pointers on how to do this, please share.  


You can disable autocommits via:


 

Rohit Mishra

unread,
Apr 4, 2013, 7:17:00 AM4/4/13
to bangal...@googlegroups.com
This page says that autocommits are disabled for the entire session. Since, I have to do this import on production, is this the right thing to do? Preferably, if I can do this only for the particular rake task, it will be great. 

My apologies if I am missing something obvious. 

Rohit

 

--
You received this message because you are subscribed to a topic in the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/bangalorerug/o392x1sFtEE/unsubscribe?hl=en-GB.
To unsubscribe from this group and all of its topics, send an email to bangalorerug...@googlegroups.com.

Dhruva Sagar

unread,
Apr 4, 2013, 7:47:53 AM4/4/13
to bangal...@googlegroups.com


--
You received this message because you are subscribed to the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bangalorerug...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 



--
Thanks & Regards,
Dhruva Sagar

----------------------------
Senior Ruby/Rails Consultant

Rohit Mishra

unread,
Apr 4, 2013, 11:15:41 AM4/4/13
to bangal...@googlegroups.com
I need to process the text lines with the RDF gem before inserting them in to Postgres, so using the copy or prepare command won't work in this case. 

Leena S N

unread,
Apr 4, 2013, 1:19:51 PM4/4/13
to bangal...@googlegroups.com
Hi Rohit,

If am not wrong, grouping the entire code in rake task with Model.transaction block should disable the autocommit. This way the transaction is  started by code, so DB should wait on the code to commit or rollback.

You can explicitly disable the autocommit at the connection level, by using ActiveRecord::Base.connection.execute method with 'set the autocommit=0' which should disable the autocommit only for the current session. 

Hope this helps.


--
Leena S N
http://www.multunus.com/

Rohit Mishra

unread,
Apr 5, 2013, 5:23:30 AM4/5/13
to bangal...@googlegroups.com
Hi Leena,

Thanks for your help. I will try using transactions. I don't want to disable autocommit for the whole session because this will effectother production code. 

Rohit

Leena S N

unread,
Apr 5, 2013, 2:35:41 PM4/5/13
to bangal...@googlegroups.com
Rohit,

I think there is some confusion with the second option I was suggesting and I would like to clarify. What I meant was to turn off the autocommit from the rake task. In that way it would affect only the the session initiated by rake task, not the sessions initiated by the production app. I believe you can do that with ActiveRecord::Base.connection.execute( 'SET AUTOCOMMIT=0' ) or with Model.connection.execute( 'SET AUTOCOMMIT=0' ) 

Rohit Mishra

unread,
Apr 6, 2013, 9:45:07 AM4/6/13
to bangal...@googlegroups.com, lee...@gmail.com
Leena, 

Thanks. This should be a great option. Let me try this out and report back the results. 

Rohit
Hi Rohit,

To unsubscribe from this group and all of its topics, send an email to bangalorerug+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bangalorerug+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 



--
Thanks & Regards,

--
You received this message because you are subscribed to the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bangalorerug+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 


--
Leena S N
http://www.multunus.com/

--
You received this message because you are subscribed to a topic in the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/bangalorerug/o392x1sFtEE/unsubscribe?hl=en-GB.
To unsubscribe from this group and all of its topics, send an email to bangalorerug...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "BANGALORE RUG-Ruby Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bangalorerug...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
Reply all
Reply to author
Forward
0 new messages