Transforming Huge Database table to Another Table We are migrating lot of data from our old database to new database.

Presently, both of them are actively[writing/reading] used based on flows/activity as migration is in progress.

Here's the real challenge for me now. We have old database table which's still getting used now.


  Atleast 1,00,000 new records are getting inserted every day and we
  need to move 6months old data [01-01-2014] to new database.


We are using MySQL in both places. The new table to old table need some logical-transformation of data which will be carried through Java-JDBC, as the table structures and functionality is changed.

What are the ideal steps that I need to follow and taken care to do this transformation as quick as possible without fail.

As per my calculation, we will be migrating atleast 2,00,00,000 records.


  Some info:

Presently index exists only on ID column. creation_date exists without index
How to fetch efficiently these records, a better query to fetch??
Efficient way to transform using multi-threading
Efficient way to write the data into new database
Since existing records are static they can be converted to the new schema any time after the migration process is signed off.  Specifically, as soon as the new DB is available you can start loading it with old data.  Start with the oldest and work your way forward one day at a time.  Assuming it takes less than twenty four hours to convert one day's data, you can run this process non-stop until you have caught up with today's data.

Thereafter you can continue with a more-or-less parallel run, with two options.  Option "A" is to load each new day's data into the old DB, then run the convertion process to get it into the new DB.  Option "B" is to run the old ETL to load today's source file into the old DB and run the new ETL to load the same soruce file into the new DB.  Option "B" has the advantage of testing your new ETL process, too.

Then on cut-over day you will have at most one day's worth of data to move.

As for the convertion process itself I would suggest you read all the data for one business transaction from the old tables (i.e. a referentially in tact set), convert it, then write it entirely to a set of staging tables in the new schema.  Note not to the live tables themselves but to a parallel set of staging tables.  Then you can write lots of reconciliation checkes against these staging tables.  If there's a problem you can bulk-delete the staging tables without affecting the new DB's data, fix the convertion and re-run.

Once the reconciliation completes successfully you can bulk-copy from the staging to the live tables.  The staging tables can have additional columns to help with reconciliation that you would not want in the live tables (source surrogate ID, for example).  Retain the staging tables for a few months for any post-mortem on your go-live problems.

I know it sounds like all this staging and reconciliation is eating precious time during a cut-over.  And it is, but is nothing compared to the time it will take to post-fix two hundred million rows in an active production system.