Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Paent Child problem. How do i solve it

  1. #1
    Join Date
    Sep 2012
    Posts
    7

    Default Paent Child problem. How do i solve it

    Hi,

    I have a source table (from a MS DB) that contains a field "ParentID", which references the PK of "ID"

    The target Postgres table is filled (new "ID" PK inserted from sequence) and the "ParentID" is set to null.
    The stream has a the new PK inserted within it.

    So now at this stage i have a valid stream of

    "ID", "ParentID", "NewTargetID"
    100 NULL 1
    101 100 2

    How do i do a "Stream Lookup" (or maybe something else) and lookup the values to create a new stream field
    to give me a new field of "NewParentID"

    "ID", "ParentID", "NewTargetID", "NewParentID"
    100 NULL 1 NULL -< (How do i generate this ?)
    101 100 2 1 -< (How do i generate this ?)

    Thanks

    David

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

    Default

    If you don't have too much data I would indeed load everything in memory with a Stream value lookup and retrieve the NewTargetID of the parent.

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    A simple key translation in a hierarchical table?

    Normally this is done entirely in the database.
    You add a column in the target table for every key column in the source table you want to transform.
    I would let the target DBMS even assign the new PK.

    source (ID, parentID, ...)
    target (ID, parentID, oldID, oldParentID, ...)

    After populating the target table you ask the DBMS to calculate the parentID:

    Code:
    UPDATE target t SET parentID = (SELECT ID FROM target WHERE oldID = t.oldParentID
    Now you can drop the old columns:

    Code:
    ALTER TABLE target DROP COLUMN oldID, oldParentID
    Last edited by marabu; 09-13-2012 at 10:42 AM. Reason: typos
    So long, and thanks for all the fish.

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

    Default

    Not so marabu, since the new IDs are intended for another database.

  5. #5
    Join Date
    Sep 2012
    Posts
    7

    Default

    Thanks Marubu,

    Much appreciated, though I was hoping to find a solution in which i could generate the "NewParentID" efficiently in the stream itself as i need to do some more processing further on down the transformation.

    Is there any other solution for this. It seems quite convoluted to write the old Ids into the new table structure itself.

    David.

  6. #6
    Join Date
    Sep 2012
    Posts
    71

    Default

    David,

    I posted a possible solution on my blog here:

    http://funpdi.blogspot.com/2012/09/p...ld-lookup.html

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by MattCasters View Post
    Not so marabu, since the new IDs are intended for another database.
    That's what I understood, Matt.

    Instead of adding the sequence in a PDI step, we can let Postgres use its own sequence mechanism.

    We even can use PDI to lookup the new parentID values, but we don't have to, we have a choice.

    Or did I get something wrong?
    Last edited by marabu; 09-17-2012 at 12:00 PM. Reason: lost context
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Sep 2012
    Posts
    7

    Default

    Thanks Matt,

    This solution worked well. Without your help i would have been looking at it from a completely different angle.

    Regards

    David

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.