Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Kettle 3.2: Updating specific records in the result set (Execute SQL Script?)

  1. #1
    Join Date
    Nov 2013
    Posts
    16

    Default Kettle 3.2: Updating specific records in the result set (Execute SQL Script?)

    Hi Everyone,

    Here's what I'm trying to do: I'm querying from a source database and got my result set, below is an example:

    Field A Field B
    1 A
    2 B
    3 C

    I want to update the 'A' to 'Andy', that's it, the rest stays the same. But I only have read/write access to the source database as it contains sensitive information. So which step should I use in Kettle 3.2 to update specific records in my result set?

    Notes:
    I'm not updating fields/columns, I want to update specific records, so that eliminates the 'Update' and 'Insert/Update' steps. Also, I DO NOT WANT to actually update the source database, so would the 'Execute SQL Script' step work? Would it update the records that I want from the previous 'Table Input' step, but everything in the source database remains untouched? Any other steps should I consider?

    I hope I was clear, any help would be greatly appreciated. Thanks very much.

    David
    Last edited by zzhang33; 05-29-2014 at 08:23 PM.

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

    Default

    Quote Originally Posted by zzhang33 View Post
    I hope I was clear
    Not at all, if you ask me. There are some contradictions in your description, so I'm still a bit lost after reading three times.

    Either you have a Table Input / Table Output scenario with two different tables or you just want to update a single table using Execute SQL Script.
    Which is it now?
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Nov 2013
    Posts
    382

    Default

    Just an educated guess as I don't understand either the requirements ...

    Maybe you mean that you want to:

    1. Get rows using a Table Input step, reading from some table on some database. This just reads, no updates anywhere.
    2. Modify the results you got in step 1 while in the transaction. Use Formula, Calc, Set values, ... or any other similar steps to change your data. No updates anywhere, only the data on the result rows is modified.
    3. ... and now what? What do you want to do with this modified data? Write to a file? => Text Output step ... Insert into a new table on some other database? => Output table step ... etc. You are in full control on where to write your data and what to write.

    If you can clarify a little bit more it will be easier to help you

  4. #4
    Join Date
    Nov 2013
    Posts
    16

    Default

    Thanks for the replies, sorry I wasn't clear. I'm going to try one more time to explain this, because it is confusing.

    I'm working on an ETL project using Pentaho Kettle 3.2 by extracting from multiple source databases, transforming, and ultimately loading into one single target database (DB --> DB)

    DATABASE A
    Name:  Database A.jpg
Views: 54
Size:  52.8 KBName:  Database B.jpg
Views: 54
Size:  61.9 KB DATABASE B

    Above are the result sets of two unique databases that I'm querying from. I have to do an DB lookup to the target database which has a table that contains all the EventTypeIds. However as you can see in the screenshots, both databases' EventTypeIds have a duplicate in 'F', this will cause a conflict when I run my ETL job. The EventTypeIds must be unique. Each source database have their own ETL job.

    How do I change all occurrences of 'F' in Database A's EventTypeId column to 'Foreign'? (every other EventTypeId such as '+' or 'B' remains unchanged)
    How do I change all occurrences of 'F' in Database B's EventTypeId column to 'Future'? (every other EventTypeId such as 'P' or 'C' remains unchanged)

    I realize an obvious answer would be "Why don't you just use Update/Set/Where in your database"? I can't, I'm not allowed to modify anything in the source databases, so that option is eliminated. The ONLY option is using Kettle, so what step(s) should I use to update the letter 'F' to a different word?

    I appreciate any input, I know this is complicated but I'm leveraging from an existing ETL job and that's just how it works. Or else I have to start from scratch. Thanks.

    David
    Last edited by zzhang33; 05-30-2014 at 12:45 PM.

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

    Default

    What about a Value Mapper step or a User Defined Java Expression?
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,686

    Default

    Since you are using PDI3.2, try using a filter rows step (or a Switch/Case) to reroute the data that you need to change.
    Then use an Add Constants step to add the new value, and a Select Values step to "rename" the new value column in place of the old column. The Meta of both columns MUST be EXACTLY the same.

    You can then recombine the flows before your table output.

    This works in versions of PDI 3.1 and up, and newer versions have better options.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7
    Join Date
    Nov 2013
    Posts
    382

    Default

    Another easy solution is to use a CASE in your sql select statement.

    Select ....,
    case eventypeid
    when 'F' then 'Foreign'
    else cast(eventypeid as charvar(10)) -- or whatever
    end,
    ...

    On the output database a single UPDATE should work.

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.