Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Table Output with batch updates fails with Vertica - 4.2 and 4.3ga

  1. #1
    Join Date
    Dec 2010
    Posts
    8

    Default Table Output with batch updates fails with Vertica - on Kettle 4.2 , 4.3, and 4.4

    I'm moving data from mysql to vertica - simple transform with table input step to table output step. As 'Use batch inserts for update' is selected by default, the transform fails after the first commit with the stack trace below. This fails with either the JNDI connection or the generic connection (as standard vertica connection cannot connect). Everything works as expected without batch inserts selected:

    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : [Vertica][JDBC](11460) Exactly one result expected, but 0 results created from query "INSERT INTO temp_publisher_advertiser_restrictions (advertiser_id, publisher, priority) VALUES ( ?, ?, ?)". Query not executed.
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:434)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:117)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Thread.java:680)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : [Vertica][JDBC](11460) Exactly one result expected, but 0 results created from query "INSERT INTO temp_publisher_advertiser_restrictions (advertiser_id, publisher, priority) VALUES ( ?, ?, ?)". Query not executed.
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1415)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:273)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.sql.SQLException: [Vertica][JDBC](11460) Exactly one result expected, but 0 results created from query "INSERT INTO temp_publisher_advertiser_restrictions (advertiser_id, publisher, priority) VALUES ( ?, ?, ?)". Query not executed.
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.vertica.jdbc.SStatement.checkCondition(Unknown Source)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.vertica.jdbc.SPreparedStatement.executeWithParams(Unknown Source)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.vertica.jdbc.SPreparedStatement.executeUpdate(Unknown Source)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1364)
    2012/09/20 10:57:23 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 4 more
    Last edited by tcaudill; 01-04-2013 at 12:51 PM. Reason: Updated Title to include latest Kettle release

  2. #2
    Join Date
    Oct 2012
    Posts
    1

    Default

    I'm having the same issue. Have you find a work around for this?

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Vertica completely changed their driver in v5 so from PDI4 on we'll have a "Vertica 5+" entry in the databases list.
    This type uses that new driver class.

  4. #4
    Join Date
    Dec 2010
    Posts
    8

    Default Table Output using 'batch updates for inserts' still fails with Kettle 4.4

    I still see the issue with Kettle 4.4, Vertica 6, and the Vertica 5 JDBC driver (I selected Vertica 5+ connection type). Note that the problem does not occur when 'Use batch updates for inserts' is not selected.

    With a commit size of 300 and 'Use batch updates for inserts' selected, it appears to fail immediately after the first batch commit:


    2013/01/04 08:32:22 - Table output.0 - Prepared statement : INSERT INTO public.random_customers (id, name, firstname, zip, city, birthdate, street, housenr, statecode, state) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    2013/01/04 08:32:22 - Modified Java Script Value.0 - No end Script found!
    2013/01/04 08:32:22 - Modified Java Script Value.0 - Signaling 'output done' to 1 output rowsets.
    2013/01/04 08:32:22 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=250, W=250, U=0, E=0)
    2013/01/04 08:32:22 - Modified Java Script Value.1 - No end Script found!
    2013/01/04 08:32:22 - Modified Java Script Value.1 - Signaling 'output done' to 1 output rowsets.
    2013/01/04 08:32:22 - Modified Java Script Value.1 - Finished processing (I=0, O=0, R=250, W=250, U=0, E=0)
    2013/01/04 08:32:22 - vertica - Commit on database connection [vertica]
    2013/01/04 08:32:22 - vertica - Rollback on database connection [vertica]
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : Because of an error, this step can't continue:
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : org.pentaho.di.core.exception.KettleException:
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : Error inserting row into table [random_customers] with values: [301], [wkyvb-name], [eer-firstname], [18220], [slc-city], [1982/12/24 00:00:00.000], [bacl-street], [39], [AL], [ALABAMA]
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) :
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : Error inserting/updating row
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : [Vertica][JDBC](11460) Exactly one result expected, but 0 results created from query "INSERT INTO public.random_customers (id, name, firstname, zip, city, birthdate, street, housenr, statecode, state) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)". Query not executed.
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) :
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) :
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:445)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:128)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at java.lang.Thread.run(Thread.java:662)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : Error inserting/updating row
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : [Vertica][JDBC](11460) Exactly one result expected, but 0 results created from query "INSERT INTO public.random_customers (id, name, firstname, zip, city, birthdate, street, housenr, statecode, state) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)". Query not executed.
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) :
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1411)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:284)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : ... 3 more
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : Caused by: java.sql.SQLException: [Vertica][JDBC](11460) Exactly one result expected, but 0 results created from query "INSERT INTO public.random_customers (id, name, firstname, zip, city, birthdate, street, housenr, statecode, state) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)". Query not executed.
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at com.vertica.jdbc.SStatement.checkCondition(Unknown Source)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at com.vertica.jdbc.SPreparedStatement.executeWithParams(Unknown Source)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at com.vertica.jdbc.SPreparedStatement.executeUpdate(Unknown Source)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1360)
    2013/01/04 08:32:22 - Table output.0 - ERROR (version 4.4.0, build 1 from 2013-01-02 12.09.15 by tcaudill) : ... 4 more

  5. #5
    Join Date
    Dec 2010
    Posts
    8

    Default

    One more note - The table output will work if the commit size is greater than the number of rows output.

  6. #6
    Join Date
    Apr 2009
    Posts
    12

    Default

    Quote Originally Posted by tcaudill View Post
    One more note - The table output will work if the commit size is greater than the number of rows output.
    I agree, this issue is not yet solved. In my experience with Vertica 6 CE and PDI 4.3 it is best to stick with the "vertica_4.1.19_jdk_5.jar" JDBC Driver.

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    That's not the only bug with the Vertica JDBC driver. Maybe you should consider putting pressure on Vertica/HP to fix this.

  8. #8
    Join Date
    Dec 2010
    Posts
    8

    Default

    Yes, I am finding issues with vertica on other steps (i.e., db lookup) as well. We are currently on Community Edition, but are on our way to the Enterprise Edition so I'll be able to engage HP more aggressively on this and other issues with PDI.

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.