Pentaho Data Integration - Kettle

Error Converting Vertica DATE data-type in Table Output Step

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Blocker Blocker
  • Resolution: Fixed
  • Affects Version/s: 4.3.0 GA (4.5.0 GA Suite Release)
  • Fix Version/s: 4.4.0 GA (4.8.0 GA Suite Release)
  • Component/s: Transformation
  • Labels:
    None
  • Environment:
    Windows 7, Vertica 6.0.0 JDBC driver, Vertica 6 server
  • PDI Sub-component:
  • QA Validation Status:
    Validated by QA
  • CI Build:
    PDI EE Assembly 4.4.4 612

Description

When using a transformation to output to a text file from SQL (Table Input step -> Text File Output step) it fails when there is a field of data type DATE.

Example SQL:

SELECT CAST('2012-10-05' AS DATE) AS "someDate";

Error Log:

2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unexpected error
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Couldn't get row from result set
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unable to get value 'Date' from database resultset, index 0
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : [Vertica][JDBC](10140) Error converting value to Timestamp.
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2824)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2796)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2770)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:257)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at java.lang.Thread.run(Unknown Source)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unable to get value 'Date' from database resultset, index 0
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : [Vertica][JDBC](10140) Error converting value to Timestamp.
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1900)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.DatabaseMeta.getValueFromResultSet(DatabaseMeta.java:2613)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2816)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 6 more
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: java.sql.SQLException: [Vertica][JDBC](10140) Error converting value to Timestamp.
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at com.vertica.utilities.conversion.TypeConverter.toTimestamp(Unknown Source)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at com.vertica.jdbc.SForwardResultSet.getTimestamp(Unknown Source)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1887)
2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 8 more


 
  1. File
    VERTICA_5.ktr
    20/Oct/12 10:30 AM
    11 kB
    Sean Flatley
  2. File
    VERTICA_6.ktr
    20/Oct/12 10:30 AM
    11 kB
    Sean Flatley

Issue Links

duplicates

Bug - A problem which impairs or prevents the functions of the product. PDI-8750 Vertica 5.1 JDBC Driver - Cannot load Timestamp field

  • Blocker - Blocks development and/or testing work, production could not run.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Bug - A problem which impairs or prevents the functions of the product. PDI-8753 java stack trace when have a table with a date or timestamp Unable to get value 'Date' from database (Vertica)

  • Blocker - Blocks development and/or testing work, production could not run.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Sean Flatley added a comment -
Changes checked into TRUNK, 4.4.0, 4.4.0-GA and 4.4.1 at revision 17501.
Show
Sean Flatley added a comment - Changes checked into TRUNK, 4.4.0, 4.4.0-GA and 4.4.1 at revision 17501.
Hide
Permalink
Sean Flatley added a comment - - edited
Validation

   - The attached job can be used to create a
      table to read and write to. The table has a date
      key and a date column.

   - Enginerring has a QA Vertica 5 database. Check the IWiki
      for details.

   - I have a VM that has Vertica 6. So does Matt Burgess.
      See us if you are validating with Vertica 5.


This matrix shows what database type and jar to use when validating
with Vertica 5 and 6.
       

Database / Kettle Connection Type / JDBC Driver / DatabaseMeta
------------------------------------------------------------------------------------------------------------------------
Vertica 5 / Vertica / vertica_4.1.17_jdk_5.jar / VerticaDatabaseMeta
Vertica 6 / Vertica5++ / vertica-jdk5-6.0.0-0.jar / Vertica5DatabaseMeta


   - The attached transformations can be used to validate.

           VERTICA_5.ktr
           VERTICA_6.ktr
Show
Sean Flatley added a comment - - edited Validation    - The attached job can be used to create a       table to read and write to. The table has a date       key and a date column.    - Enginerring has a QA Vertica 5 database. Check the IWiki       for details.    - I have a VM that has Vertica 6. So does Matt Burgess.       See us if you are validating with Vertica 5. This matrix shows what database type and jar to use when validating with Vertica 5 and 6.         Database / Kettle Connection Type / JDBC Driver / DatabaseMeta ------------------------------------------------------------------------------------------------------------------------ Vertica 5 / Vertica / vertica_4.1.17_jdk_5.jar / VerticaDatabaseMeta Vertica 6 / Vertica5++ / vertica-jdk5-6.0.0-0.jar / Vertica5DatabaseMeta    - The attached transformations can be used to validate.            VERTICA_5.ktr            VERTICA_6.ktr
Hide
Permalink
mark warner added a comment -
Can you please clarify if this is resolved in 4.8 and if so how?
Show
mark warner added a comment - Can you please clarify if this is resolved in 4.8 and if so how?
Hide
Permalink
Jens Bleuel added a comment -
Mark, it was fixed by return false by supportsTimeStampToDateConversion() and will be in the coming 4.8 suite release (PDI 4.4.0)
If you want to test it, you can check a nightly build on http://ci.pentaho.com/view/Data%20Integration/job/Kettle-4.4/
Show
Jens Bleuel added a comment - Mark, it was fixed by return false by supportsTimeStampToDateConversion() and will be in the coming 4.8 suite release (PDI 4.4.0) If you want to test it, you can check a nightly build on http://ci.pentaho.com/view/Data%20Integration/job/Kettle-4.4/
Hide
Permalink
Golda David added a comment -
Tested both Vertica 5 and 6 databases and the output format for the DATE field was converted properly.

This issue is resolved in PDI 4.4 GA
Show
Golda David added a comment - Tested both Vertica 5 and 6 databases and the output format for the DATE field was converted properly. This issue is resolved in PDI 4.4 GA

People

  • Assignee:
    Golda David
    Reporter:
    Mike Sloper
Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: