Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Ignore rows with foreign key issues

  1. #1
    Join Date
    May 2015
    Posts
    4

    Default Ignore rows with foreign key issues

    We are considering the use of Pentaho Data Integration in our project to align 2 different databases and keep them aligned via a scheduled job. In the destination database suppose we have 2 tables (table A and table B). Table B has a foreign key constraint pointing to table A. I try to load all the rows for table A and check if the row is suitable for the destination table. If not, I simply discard it from the stream. Now, if one or more rows loaded for destination table B contain a reference to previously discarded rows, they should simply be ignored. So far, I get a foreign key error. The long solution should be to check if parent rows exist in table A, but in some cases we have more then 10 constraints, so this is not performant.
    How can we procede?
    Thank you!

  2. #2
    Join Date
    Aug 2015
    Posts
    313

    Default

    I hope it is good practice to load child table data before we should load data into master table.

    anyhow in your case, better to OFF foreign keys constraints before child table data loading, which database you are using?

  3. #3
    Join Date
    Jul 2009
    Posts
    476

    Default

    The Database Lookup step is the closest match for the requirements that you describe. It has a "Do not pass the row if the lookup fails" checkbox. If this box is checked, and your parent table doesn't have the key, then the row will be discarded from the stream. This step also has an "Enable cache?" checkbox along with a "Cache size in rows (0=cache everything)" textbox that might boost performance.

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.