Hey there, I''m not super familiar with MySQL, so please forgive me if I say something stupid. I have a large database table (it includes every zipcode in the U.S., along with its city,state,latitude,longitude), and it seems that we are frequently dropping/creating/migrating our database. It takes a very long time to migrate the zipcodes table (I do so from a .csv, creating a Zipcode object row by row). I''d like to dump the table with MySQLdump. Then, when migrating, rather than run the ruby code that creates a bunch of objects, instead load the resulting dump file. [With the hopes that that will be a quicker process...think that''s the case?] I don''t know how to load the dump file using Rails migrations, and I''d like to. Any thoughts? Re: any part of this process? Is this a silly thing to do? How would you guys suggest I go about migrating tables w/large amounts of data? Thanks in advance, any advice is MUCH APPRECIATED!! --Jared
I would create a new database, load that table in the new database, and create a view from your database used by rails to the new db you created. That way you will only be recreating the view and not the whole table. If you are dealing with indexes on the table, be sure to add the indexes before you load the data. Faster. -- Darian Shimy http://www.darianshimy.com http://twitter.com/dshimy On Fri, Nov 13, 2009 at 1:40 PM, jrgoodner <jrgoodner-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hey there, I''m not super familiar with MySQL, so please forgive me if > I say something stupid. > > I have a large database table (it includes every zipcode in the U.S., > along with its city,state,latitude,longitude), and it seems that we > are frequently dropping/creating/migrating our database. It takes a > very long time to migrate the zipcodes table (I do so from a .csv, > creating a Zipcode object row by row). > > I''d like to dump the table with MySQLdump. Then, when migrating, > rather than run the ruby code that creates a bunch of objects, instead > load the resulting dump file. [With the hopes that that will be a > quicker process...think that''s the case?] > > I don''t know how to load the dump file using Rails migrations, and I''d > like to. Any thoughts? Re: any part of this process? Is this a > silly thing to do? How would you guys suggest I go about migrating > tables w/large amounts of data? > > Thanks in advance, any advice is MUCH APPRECIATED!! > > --Jared > > >-- Darian Shimy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks for the swift reply! So the application will utilize two databases, one just for the zipcodes table? How would I go about that? Know of any decent references for something like this? On Nov 13, 1:50 pm, Darian Shimy <dsh...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I would create a new database, load that table in the new database, and > create a view from your database used by rails to the new db you created. > That way you will only be recreating the view and not the whole table. > > If you are dealing with indexes on the table, be sure to add the indexes > before you load the data. Faster. > -- > Darian Shimy > > http://www.darianshimy.comhttp://twitter.com/dshimy > > > > On Fri, Nov 13, 2009 at 1:40 PM, jrgoodner <jrgood...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Hey there, I''m not super familiar with MySQL, so please forgive me if > > I say something stupid. > > > I have a large database table (it includes every zipcode in the U.S., > > along with its city,state,latitude,longitude), and it seems that we > > are frequently dropping/creating/migrating our database. It takes a > > very long time to migrate the zipcodes table (I do so from a .csv, > > creating a Zipcode object row by row). > > > I''d like to dump the table with MySQLdump. Then, when migrating, > > rather than run the ruby code that creates a bunch of objects, instead > > load the resulting dump file. [With the hopes that that will be a > > quicker process...think that''s the case?] > > > I don''t know how to load the dump file using Rails migrations, and I''d > > like to. Any thoughts? Re: any part of this process? Is this a > > silly thing to do? How would you guys suggest I go about migrating > > tables w/large amounts of data? > > > Thanks in advance, any advice is MUCH APPRECIATED!! > > > --Jared > > -- > Darian Shimy
See if the following will help: # Create two databases in the same server create database zipcode; create database rails; # Create the zipcode table in the zipcode database use zipcode; create table zipdata...; # Switch over to the rails app database and create the view use rails; create or replace view zipdata as select * from zipcode.zipdata. # query the view like you would any other table (you can even do inserts) select * from zipdata; Let me know if that helps. On Fri, Nov 13, 2009 at 3:09 PM, jrgoodner <jrgoodner-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Thanks for the swift reply! > > So the application will utilize two databases, one just for the > zipcodes table? How would I go about that? Know of any decent > references for something like this? > > On Nov 13, 1:50 pm, Darian Shimy <dsh...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I would create a new database, load that table in the new database, and > > create a view from your database used by rails to the new db you created. > > That way you will only be recreating the view and not the whole table. > > > > If you are dealing with indexes on the table, be sure to add the indexes > > before you load the data. Faster. > > -- > > Darian Shimy > > > > http://www.darianshimy.comhttp://twitter.com/dshimy > > > > > > > > On Fri, Nov 13, 2009 at 1:40 PM, jrgoodner <jrgood...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > Hey there, I''m not super familiar with MySQL, so please forgive me if > > > I say something stupid. > > > > > I have a large database table (it includes every zipcode in the U.S., > > > along with its city,state,latitude,longitude), and it seems that we > > > are frequently dropping/creating/migrating our database. It takes a > > > very long time to migrate the zipcodes table (I do so from a .csv, > > > creating a Zipcode object row by row). > > > > > I''d like to dump the table with MySQLdump. Then, when migrating, > > > rather than run the ruby code that creates a bunch of objects, instead > > > load the resulting dump file. [With the hopes that that will be a > > > quicker process...think that''s the case?] > > > > > I don''t know how to load the dump file using Rails migrations, and I''d > > > like to. Any thoughts? Re: any part of this process? Is this a > > > silly thing to do? How would you guys suggest I go about migrating > > > tables w/large amounts of data? > > > > > Thanks in advance, any advice is MUCH APPRECIATED!! > > > > > --Jared > > > > -- > > Darian Shimy > > >-- Darian Shimy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
jrgoodner wrote:> Hey there, I''m not super familiar with MySQL, so please forgive me if > I say something stupid. > > I have a large database table (it includes every zipcode in the U.S., > along with its city,state,latitude,longitude), and it seems that we > are frequently dropping/creating/migrating our database. It takes a > very long time to migrate the zipcodes table (I do so from a .csv, > creating a Zipcode object row by row). > > I''d like to dump the table with MySQLdump. Then, when migrating, > rather than run the ruby code that creates a bunch of objects, instead > load the resulting dump file. [With the hopes that that will be a > quicker process...think that''s the case?] > > I don''t know how to load the dump file using Rails migrations, and I''d > like to.No you wouldn''t. :) There are many good reasons not to put seed data in migrations. What I recommend doing is using seed_fu or similar, but use ar_extensions or even an execute statement so you''re not doing a separate query for each record. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
jrgoodner wrote:
<blockquote
cite="mid:8c419e58-4bad-41e1-982a-acfb39e21060-HBwaniIyY4InfFbFyY4wNFYGCWtFR9XvxddQ6fo/n9Y@public.gmane.orgom"
type="cite">
<pre wrap="">Hey there, I''m not super familiar with
MySQL, so please forgive me if
I say something stupid.
I have a large database table (it includes every zipcode in the U.S.,
along with its city,state,latitude,longitude), and it seems that we
are frequently dropping/creating/migrating our database. It takes a
very long time to migrate the zipcodes table (I do so from a .csv,
creating a Zipcode object row by row).
I''d like to dump the table with MySQLdump. Then, when migrating,
rather than run the ruby code that creates a bunch of objects, instead
load the resulting dump file. [With the hopes that that will be a
quicker process...think that''s the case?]
I don''t know how to load the dump file using Rails migrations, and
I''d
like to. Any thoughts? Re: any part of this process? Is this a
silly thing to do? How would you guys suggest I go about migrating
tables w/large amounts of data?
Thanks in advance, any advice is MUCH APPRECIATED!!
--Jared
</pre>
</blockquote>
I load a MySQLdump file with a simple:<br>
<br>
result = system "mysql -u #{username} -p#{password} -q -e \"source
#{backupfile}\" #{database}"<br>
<br>
<br>
<br>
--~--~---------~--~----~------------~-------~--~----~<br>
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group. <br> To post to this group,
send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
<br> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org <br>
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en<br>
-~----------~----~----~----~------~----~------~--~---<br>
</body>
</html>
<br>