Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to migrate data from a graph of tables

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default How to migrate data from a graph of tables

    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

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How to migrate data from a graph of tables

    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

  3. #3
    Join Date
    Mar 2006
    Posts
    1

    Default RE: How to migrate data from a graph of tables

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.