Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Database lookup fails if Database Insert is running in the same transformation

  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Default Database lookup fails if Database Insert is running in the same transformation

    PDI: 4.4.0-stable

    I have a MySQL table (personids) that I update while running my transformation using Table Output. It has

    person_id
    person_index
    customer_id

    The table updates successfully. I can run view it using the MySQL shell to see the tuples that I wrote.

    While the same transformation is running, I do subsequent Database Value lookups on the same table using the customer_id as the table field so I can extract the person_id.

    The problem is that my Database lookup is failing to see that the personids table is already populated so I am not getting any valid person_id. If I stop the transformation and rerun it without deleting my personids table though, my lookup succeeds.

    Any thoughts?
    Thanks,
    -phylanx

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by phylanx View Post
    While the same transformation is running, I do subsequent Database Value lookups on the same table using the customer_id as the table field so I can extract the person_id.
    You're doing it wrong.

    Each DB connection (the Table Output and the DB lookup) have a distinct connection to the DB, and the batch might not have cleared from the table output before you're trying to do the lookup.

    Options:
    1) set Tranformation DB Transactional. This will use one DB Connection for your entire tranformation, meaning that the lookups will not be trying to run at the same time as the writes. NOTE: This will be slower
    2) Use Combination Lookup / Update
    3) Return IDs from Table Output, and use them directly, instead of trying to look them up from the DB.
    4) Break this into two transformations in a job. One transformation update the person_id / customer_id table, the other does the lookup.

    There may be other options, but these are the ones that I see off the top of my head.

    Perhaps if you told us what the overall transform was trying to do, rather than the specific part that you think isn't working correctly, we might be able to give you better advice.
    **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

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.