title: Using Pentaho Kettle, how do I load multiple tables from a single table while keeping referential integrity?
body: <p>Need to load data from a single file with a 100,000+ records into multiple tables on MySQL maintaining the relationships defined in the file/tables; meaning the relationships already match. The solution should work on the latest version of MySQL, and needs to use the InnoDB engine; MyISAM does not support foreign keys.</p>
  
  <p>I am a completely new to using Pentaho Data Integration (aka Kettle) and any pointers would be appreciated.</p>
  
  <p>I might add that it is a requirement that the foreign key constraints are NOT disabled. Since it's my understanding that if there is something wrong with the database's referential integrity, MySQL will not check for referential integrity when the foreign key constraints are turned back on. <strong>SOURCE:</strong> 5.1.4. Server System Variables -- <a href="http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_foreign_key_checks">foreign_key_checks</a> </p>
  
  <p>All approaches should include some from of validation and a rollback strategy should an insert fail, or fail to maintain referential integrity.</p>
  
  <p>Again, completely new to this, and doing my best to provide as much information as possible, if you have any questions, or request for clarification -- just let me know. </p>
  
  <p>If you are able to post the XML from the kjb and ktr files (jobs/transformations) that would be SUPER. Might even hunt down every comment/answer you've every made anywhere and up vote them... :-) ...really, it's really important to me to find an answer for this.</p>
  
  <p>Thanks!</p>
  
  <hr>
  
  <p><strong>SAMPLE DATA:</strong> To better elaborate with an example, lets assume I am trying to load a file containing employee name, the offices they have occupied in the past and their Job title history separated by a tab.</p>
  
  <p>File:</p>
  
  <pre><code>EmployeeName&lt;tab&gt;OfficeHistory&lt;tab&gt;JobLevelHistory
  John Smith&lt;tab&gt;501&lt;tab&gt;Engineer
  John Smith&lt;tab&gt;601&lt;tab&gt;Senior Engineer
  John Smith&lt;tab&gt;701&lt;tab&gt;Manager
  Alex Button&lt;tab&gt;601&lt;tab&gt;Senior Assistant
  Alex Button&lt;tab&gt;454&lt;tab&gt;Manager
  </code></pre>
  
  <p><strong>NOTE:</strong> The single table database is completely normalized (as much as a single table may be) -- and for example, in the case of "John Smith" there is only one John Smith; meaning there are no duplicates that would lead to conflicts in referential integrity.</p>
  
  <p>The <code>MyOffice</code> database schema has the following tables:</p>
  
  <pre><code>Employee (nId, name)
  Office (nId, number)
  JobTitle (nId, titleName)
  Employee2Office (nEmpID, nOfficeId)
  Employee2JobTitle (nEmpId, nJobTitleID)
  </code></pre>
  
  <p>So in this case. the tables should look like:</p>
  
  <pre><code>Employee
  1 John Smith
  2 Alex Button
  
  Office
  1 501
  2 601
  3 701
  4 454
  
  JobTitle
  1 Engineer
  2 Senior Engineer
  3 Manager
  4 Senior Assistant
  
  Employee2Office
  1 1
  1 2
  1 3
  2 2
  2 4
  
  Employee2JobTitle
  1 1
  1 2
  1 3
  2 4
  2 3
  </code></pre>
  
  <p><strong>Here's the MySQL DDL to create the database and tables:</strong></p>
  
  <pre><code>create database MyOffice2;
  
  use MyOffice2;
  
  CREATE TABLE Employee (
        id MEDIUMINT NOT NULL AUTO_INCREMENT,
        name CHAR(50) NOT NULL,
        PRIMARY KEY (id)
      ) ENGINE=InnoDB;
  
  CREATE TABLE Office (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    office_number INT NOT NULL,
    PRIMARY KEY (id)
  ) ENGINE=InnoDB;
  
  CREATE TABLE JobTitle (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    title CHAR(30) NOT NULL,
    PRIMARY KEY (id)
  ) ENGINE=InnoDB;
  
  CREATE TABLE Employee2JobTitle (
    employee_id MEDIUMINT NOT NULL,
    job_title_id MEDIUMINT NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (job_title_id) REFERENCES JobTitle(id),
    PRIMARY KEY (employee_id, job_title_id)
  ) ENGINE=InnoDB;
  
  CREATE TABLE Employee2Office (
    employee_id MEDIUMINT NOT NULL,
    office_id MEDIUMINT NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (office_id) REFERENCES Office(id),
    PRIMARY KEY (employee_id, office_id)
  ) ENGINE=InnoDB;
  </code></pre>
  
  <hr>
  
  <hr>
  
  <hr>
  
  <h2>My Notes in Response to Selected Answer:</h2>
  
  <p><strong>PREP:</strong></p>
  
  <ol>
  <li>(a) Use the sample data, create a CSV by changing <code>&lt;TAB&gt;</code> to comma delimited.</li>
  <li>(b) Install MySQL and create sample database using the MySQL DDL sample</li>
  <li>(c) Install Kettle (it's Java based and will run on anything that runs Java)</li>
  <li>(d) Download KTR file</li>
  </ol>
  
  <p><strong>Dataflow by Step:</strong> (My Notes)</p>
  
  <ol>
  <li>Open the KTR file in Kettle, and double clicked the "CSV file input" and browse to the CSV file that you created. The delimiter should already be set to comma. Then click OKAY.</li>
  <li>Double click "Insert Employees" and select DB connector then follow these directions on <a href="http://wiki.pentaho.com/display/EAI/.03+Database+Connections#.03DatabaseConnections-CreatingaNewDatabaseConnection">Creating a New Database Connection</a></li>
  </ol>