Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: DB comparison test

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

    Default DB comparison test

    I am working through Kettle both in the GUI and the backend and am implementing a fairly complex ETL at the moment. I was just wanting to check how people do one of the main steps used here.


    The step involves taking an parameter "Code" and trying to retrieve the associated record from a separate table. If the "Code" does match, then return the primary_key "ID" from the record, otherwise insert it into the table.



    I am trying to do this with the Insert/Update at the moment, is this the best way? How do I get the ID back from this step?

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

    Default RE: DB comparison test

    Hi,

    You probably need the Combination Lookup for this one.
    I can't otherwise think why you would have a setup in which CODE is the primary key and you still would like to return a new key.

    Thanks,

    Matt

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

    Default RE: DB comparison test

    OK. Maybe I am looking at this the wrong way (did initially before working out insert/update as well

    The ETL divides up a large table into smaller normalised tables. Some of the fields in the initial table are used to check if existing entries are in one of the smaller target tables. If not, then it is insert, otherwise it returns the id of the entry. This id is used in other lookups of smaller tables.

    Is there a better way of doing this? Insert vs. update is not the issue, but how do I keep the reference consistent across the tables?

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

    Default RE: DB comparison test

    What can I say? Normally people would use a Combination Lookup (junk-dimension) for this to generate the new ID's and store a couple of codes with a description or something. That step gives you back the generated key from auto-increment field or from a sequence.

    If you don't want to use this step, then I'm sure you can design a work-around involving multiple transformations, some filters and db lookups.

    All the best,

    Matt

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.