How to migrate data from a graph of tables

[kettle_anonymous]

Hi,

I am new to Kettle and Spoon. I would like to know what would be the best way to migrate data using Spoon from a set of tables that are related with several association tables - a graph of tables such as tableA - associationtableAB - table B - associationtableBC - tableC and other such relations. Migration of data would be form one MySQL database to another which may be of the same or slightly modified schema. The schema is such that tables are setup to generate sequential primary keys automatically when rows are inserted.

I have been able to migrate data from a single table of the source database to the corresponding table of the destination database, but don't know what exactly to do in Spoon for a complex graph of related tables.

I would appreciate some suggestions to get me started in the right direction.

Thanks,
Mrtz

[MattCasters]

Hi,

Kettle is by definition an ETL tool and doesn't know yet about foreign keys etc.
However, it should be possible to script the migration using Kettle.
I'm sure the main problem is the use of auto-generated keys because you need referential integrity.

My suggestion is to use Chef and do the following:
1) create an SQL step that creates the target table on the target database WITHOUT the AUTO_INCREMENT specification.
(Have it generated by a Kettle transformation)
2) copy the data using a transformation step and a transformation you created earlier.
3) create an SQL as such: "ALTER TABLE T1 MODIFY F1 INT AUTO_INCREMENT PRIMARY KEY;"

In general this keeps referential integrity and enables AUTO_INCREMENT after the copy.
So, for every table you copy you have 3 job entries to execute.

Hope this helps!

Matt

[mrtz]

Hi Matt,

Thanks. I'll look into your suggestions. Sounds encouraging, though I still have to learn about the various components such as Chef. You are correct about the referential integrity being the main problem.

Thanks,
Mrtz