Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Question about the Insert/Update Step Commit size

  1. #1
    Join Date
    Jun 2014
    Posts
    28

    Default Question about the Insert/Update Step Commit size

    If I set the commit size to be greater than 1 does the step check what is in the commit buffer before going to the database to determine if it should do an insert or an update?
    For example I have a table where I want to only have 1 instance of a customer + product combination , if the first entry in my data stream is customer id 1 and product 100 and that combination does not exist in the database I expect an insert to be performed. What happens if the 10th entry in my data stream is also customer id 1 and product 100 and I have commit size set to 100.
    There are no database constraints forcing only 1 customer per product in the database, will the insert update step insert 2 rows for customer 1 , product 100 or just 1 row?

  2. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    You specify the key columns in the Insert/Update step, and when a row in the ETL stream goest through the step, PDI does a select on the table using those key columns. If it finds a matching row, it will update the row as you specify in the step. If it doesn't find the row in the database, it will insert it.

    The commit size doesn't matter here, because the select statement from the Insert/Update step is issued from the same database session that creates and updates the rows. So even if the matching row was created by the step and not yet committed, the select statement will find it.

  3. #3
    Join Date
    Jun 2014
    Posts
    28

    Default

    Excellent! Thanks for the quick response.

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.