Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Issue with 'database lookup' component

  1. #1
    Join Date
    Aug 2011
    Posts
    236

    Default Issue with 'database lookup' component

    Hi,

    I am passing a parameter to a transformation (edit -> settings -> parameters) this part is working. I need to use 'set variable' in order to use this parameter in a database lookup component. I can see that it is being set correctly :-

    Get Variables.0 - field [parm_id] has value [47761]

    But when I use it in the database lookup component it seems to be altered to [ 0000047761]

    2016/03/16 09:48:38 - vertica_prod - Setting preparedStatement to [SELECT history_id FROM jira_staging.jira_issue_aggr_history WHERE issue_id = ? AND valid_from = ? AND current_ind = ? ]
    2016/03/16 09:48:38 - jira_aggr_history_1 - No end Script found!
    2016/03/16 09:48:38 - jira_aggr_history_1 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016/03/16 09:48:38 - see if entry in aggr_history.0 - Checking row: [ 0000047761], [2015-12-08], [Y]

    I have no idea why it is being changed? The modified java script sets the 'Y' column value.

    Name:  db_lookup_issue.jpg
Views: 78
Size:  8.9 KB


    Anyone else seen this ?

    Thanks.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

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

    Default

    It's a little known issue... Parameters are by definition a STRING. (You can see this by right-clicking on the "Get Variables" step and selecting "Show Output Fields")
    Use the "Select Values" step to convert it into a number.

    Somehow, your string format for that field is set to "0000000000" which will force that many digits into the string representation.

    What are you using the MSJV step for? It's a slow step, and usually one of the first ones to look at for performance (just a side direction you haven't asked about yet...)

  3. #3
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi gutlez,

    Thanks for your reply - I can stop going crazy now!

    Another issue I have is that both sets of sql scripts seem to be executed regardless of my filter (was lookup successful) - how can I make sure only one is executed?

    Thanks,
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

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

    Default

    Quote Originally Posted by tnewman View Post
    Another issue I have is that both sets of sql scripts seem to be executed regardless of my filter (was lookup successful) - how can I make sure only one is executed?
    That's the nature of PDI... Steps will execute as soon as they can, so if they are not looking for input from another step, they will run right at the beginning.

    If you make both steps depend on input, then they won't fire (I think...)

  5. #5
    Join Date
    Aug 2011
    Posts
    236

    Default

    OK - Thanks gutlez - I might have to rethink my process flow.

    Really appreciate your help.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

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

    Default

    Quote Originally Posted by tnewman View Post
    I might have to rethink my process flow.
    Not necessarily.
    If your SQL script has a parameter requirement, then it needs to know what that parameter value is before it can run...

    Code:
    update table set colName=null where id=6
    can become
    Code:
    update table set colName=null where id=6 and ? is not null
    Then it won't fire until a data row arrives, so it can figure out if ? is null or not.

  7. #7
    Join Date
    May 2014
    Posts
    358

    Default

    Quote Originally Posted by gutlez View Post
    It's a little known issue... Parameters are by definition a STRING. (You can see this by right-clicking on the "Get Variables" step and selecting "Show Output Fields")
    Use the "Select Values" step to convert it into a number.

    What are you using the MSJV step for? It's a slow step, and usually one of the first ones to look at for performance (just a side direction you haven't asked about yet...)
    You can trim the string and specify a number type directly in the Get Variables step.
    The first step I would look at for performance would be the Database lookup (unless you're loading the whole table into memory). In my experience, Java script can process rows faster than the database can write them.

  8. #8
    Join Date
    Aug 2015
    Posts
    313

    Default

    Quote Originally Posted by Lukfi View Post
    In my experience, Java script can process rows faster than the database can write them.
    Hope, in all scenarios java script is NOT faster. When you are dealing with huge data then actual scenario will raise.

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.