Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Usage of Execute SQL script step - Pentaho

  1. #1

    Default Usage of Execute SQL script step - Pentaho

    Hi Team,

    I was trying to use the “Execute SQL script” step to execute a Copy statement - feature of Vertica and I would like to perform the following actions in the transformation:

    1. Execute an SQL (the copy statement of Vertica database)
    2. Based on the result of execution (Need to either lookup a table/file to note the rejects), rollback : if any record is rejected
    commit: when all records pass through

    My DB connection points to a Vertica Table and I have set the option of Make the transformation database transactional in the Misc tab as well. Despite setting this property to true, I note that multiple Execute SQL steps are opening up multiple connections and are not really considering it to be a single transaction.

    Here are few links that have pointed to similar issues in the past which doesn’t have the solution mentioned clearly:

    http://stackoverflow.com/questions/2...aho-kettle-pdi
    http://forums.pentaho.com/showthread...2-MySQL-5-0-45 (I know that this is a very old thread, still relevant to our context)

    Request you to help me if you have some solution to the same.

    Thanks and Regards,
    Harshini Ravi

  2. #2
    Join Date
    May 2012
    Posts
    110

    Default

    Hello Harshin,

    You can use an SQL Script if you want, l would use a Table input step followed by a Table output step with an Error step to trap rejects.
    Attached Images Attached Images  
    Regards Vince

  3. #3

    Default

    Hi Vince,

    Thanks for your response. The Execute SQL script step is executing a Vertica database specific "Copy" statement. However, when I use the Table output step, it in turns generates an "Insert into" statement. I tried to compare the performance stats and the Table output step takes atleast twice the time the Copy statement takes to load data into the target table.

    Regards,
    Harshini

  4. #4
    Join Date
    May 2012
    Posts
    110

    Default

    Harshini,

    Yes it will be slower, but it will take care of your second requirement: Based on the result of execution (Need to either lookup a table/file to note the rejects), rollback : if any record is rejected commit: when all records pass through
    Regards Vince

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.