We've run into a problem with inconsistent data typing from the Database Lookup step between version 7.1 and 8.2.
Using a postgresql database, any integer/bigint key lookup value typed to integer in the lookup step outputs as a BigNumber in the data stream. This lookup pattern is one we've used consistently through our ETL process and now causes issues when inserting into any of our tables with 8.2.
We tried using a Select Value step, but the data type conversion fails. The error below is for a Select values step, but the error is the same on the table Insert / Update step:
2019/04/16 10:33:23 - Select values.0 - ERROR (version 8.2.0.0-342, build 8.2.0.0-342 from 2018-11-14 10.30.55 by buildguy) : Unexpected error
2019/04/16 10:33:23 - Select values.0 - ERROR (version 8.2.0.0-342, build 8.2.0.0-342 from 2018-11-14 10.30.55 by buildguy) : org.pentaho.di.core.exception.KettleConversionException:
2019/04/16 10:33:23 - Select values.0 -
2019/04/16 10:33:23 - Select values.0 - Unexpected conversion error while converting value [test1 BigNumber(16)] to an Integer
2019/04/16 10:33:23 - Select values.0 - java.lang.Long cannot be cast to java.math.BigDecimal
2019/04/16 10:33:23 - Select values.0 -
2019/04/16 10:33:23 - Select values.0 -
2019/04/16 10:33:23 - Select values.0 - at org.pentaho.di.trans.steps.selectvalues.SelectValues.metadataValues(SelectValues.java:332)
2019/04/16 10:33:23 - Select values.0 - at org.pentaho.di.trans.steps.selectvalues.SelectValues.processRow(SelectValues.java:381)
2019/04/16 10:33:23 - Select values.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2019/04/16 10:33:23 - Select values.0 - at java.lang.Thread.run(Thread.java:748)
The only workaround we've found is using a JavaScript step to handle the datatype conversion.
I'm attaching a simple ktr that demonstrates the issues. Three tables are created, populated and then then the transformation runs. The transformation as a whole works perfectly fine in 7.1 but fails in 8.2.
Any thoughts?
#Pentaho