kettle 写入数据库报错

kettle 报错这个是什么原因
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Because of an error, this step can't continue:
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleException:
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Error batch inserting rows into table [fr_wg_case_paid_sum_daily].
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Errors encountered (first 10):
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 -
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 -
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Error updating batch
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Incorrect integer value: 'WJ06468' for column 'pa_se_no' at row 1
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 -
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 -
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:349)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at java.lang.Thread.run(Thread.java:748)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException:
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Error updating batch
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Incorrect integer value: 'WJ06468' for column 'pa_se_no' at row 1
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 -
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1427)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:296)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - ... 3 more
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Caused by: java.sql.BatchUpdateException: Incorrect integer value: 'WJ06468' for column 'pa_se_no' at row 1
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.Util.getInstance(Util.java:408)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1154)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1641)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1307)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:954)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:292)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - ... 3 more
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - Caused by: java.sql.SQLException: Incorrect integer value: 'WJ06468' for column 'pa_se_no' at row 1
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1281)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:787)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2133)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2067)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5175)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1602)
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - ... 8 more
2023/05/08 15:00:37 - 查询交行还款数据.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Because of an error, this step can't continue:
2023/05/08 15:00:37 - 查询交行还款数据.0 - ERROR executing query
2023/05/08 15:00:37 - 查询交行还款数据.0 - Statement cancelled due to client request
2023/05/08 15:00:37 - 查询交行还款数据.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2023/05/08 15:00:37 - 查询交行还款数据.0 - ERROR executing query
2023/05/08 15:00:37 - 查询交行还款数据.0 - Statement cancelled due to client request
2023/05/08 15:00:37 - 查询交行还款数据.0 -
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1824)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.pentaho.di.trans.steps.databasejoin.DatabaseJoin.lookupValues(DatabaseJoin.java:94)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.pentaho.di.trans.steps.databasejoin.DatabaseJoin.processRow(DatabaseJoin.java:159)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at java.lang.Thread.run(Thread.java:748)
2023/05/08 15:00:37 - 查询交行还款数据.0 - Caused by: com.mysql.jdbc.exceptions.MySQLStatementCancelledException: Statement cancelled due to client request
2023/05/08 15:00:37 - 查询交行还款数据.0 - at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1304)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:787)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2020)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
2023/05/08 15:00:37 - 查询交行还款数据.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1812)
2023/05/08 15:00:37 - 查询交行还款数据.0 - ... 4 more
2023/05/08 15:00:37 - 查询交行还款数据.0 - 完成处理 (I=1300, O=0, R=3, W=1298, U=0, E=1)
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - 转换被检测
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - 转换正在杀死其他步骤!
2023/05/08 15:00:37 - 写进fr_wg_case_paid_sum_daily.0 - 完成处理 (I=0, O=999, R=1000, W=0, U=0, E=1)
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : 错误被检测到!
2023/05/08 15:00:37 - Spoon - 转换完成!!
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : 错误被检测到!
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : 错误被检测到!
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - 转换被检测
2023/05/08 15:00:37 - 交行写入fr_wg_case_paid_sum_daily - 转换正在杀死其他步骤!

从日志中可以看到数据有问题，要插入的数据类型和表定义的数据类型不一致

从错误日志可以看出,kettle在执行TableOutput步骤插入数据到fr_wg_case_paid_sum_daily表时报错了。具体的原因是:
Incorrect integer value: 'WJ06468' for column 'pa_se_no' at row 1
这是因为pa_se_no这一列需要整数类型,而这行数据的pa_se_no的值是'WJ06468',是一个字符串,不符合整数类型,所以插入报错了。
解决方法:

在kettle中,检查fr_wg_case_paid_sum_daily表的pa_se_no列,确保其类型是整数,而不是字符串。如果是字符串,修改为整数类型。
检查输入数据,找到pa_se_no为'WJ06468'那一行数据,修改其值为整数,然后重新运行kettle。
在kettle的TableOutput步骤中,配置字段类型,将pa_se_no列的类型设置为整数,这样kettle会在运行时自动将'WJ06468'转换为0,从而避免报错。
所以总结下,这个报错的原因是输入数据类型与数据库表列类型不匹配所致。解决方式是修改输入数据使其符合表结构,或者在kettle中添加类型转换来配合输入数据。
希望这个解释能帮助您解决问题,如果还有其他疑问请再次提出,我很乐意继续帮助您。
Claude 非常感谢您的细致解答,我按您的方法去检查选项2,发现输入数据的pa_se_no字段的确是字符型,修改为数字后重新运行成功解决了问题!感谢您的帮助!!
不客气,很高兴我的解答能真正帮助到您,解决kettle报错问题。kettle作为数据ETL工具,输入输出数据类型匹配是一个比较常见的问题。熟练掌握kettle,需要了解它的运行原理,遇到问题能够快速定位并解决