kettle常见报错集锦
报错一
1.1报错信息
ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Because of an error, this step can't continue:
2018/07/26 11:28:53 - sql_data_output.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : org.pentaho.di.core.exception.KettleException:
2018/07/26 11:28:53 - sql_data_output.0 - Error batch inserting rows into table [users].
2018/07/26 11:28:53 - sql_data_output.0 - Errors encountered (first 10):
2018/07/26 11:28:53 - sql_data_output.0 -
2018/07/26 11:28:53 - sql_data_output.0 -
2018/07/26 11:28:53 - sql_data_output.0 - Error updating batch
2018/07/26 11:28:53 - sql_data_output.0 - Field 'rec_source' doesn't have a default value
2018/07/26 11:28:53 - sql_data_output.0 -
2018/07/26 11:28:53 - sql_data_output.0 -
2018/07/26 11:28:53 - sql_data_output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:349)
2018/07/26 11:28:53 - sql_data_output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125)
2018/07/26 11:28:53 - sql_data_output.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2018/07/26 11:28:53 - sql_data_output.0 - at java.lang.Thread.run(Thread.java:748)
2018/07/26 11:28:53 - sql_data_output.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException:
1.2 原因：表字段rec_source没有设置正确的值，所以报错
1.3 方法：在相应的转换中为字段设置值
报错二
2.1 报错信息
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Unexpected batch update error committing the database connection.
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - Error updating batch
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - Data truncation: Out of range value for column 'confirm_status_key' at row 1
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 -
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1425)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1414)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:586)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:97)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at java.lang.Thread.run(Thread.java:745)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - Caused by: java.sql.BatchUpdateException: Data truncation: Out of range value for column 'confirm_status_key' at row 1
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at com.mysql.jdbc.Util.getInstance(Util.java:408)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1401)
2018/10/17 17:43:12 - TableOutput-fact_cfc_user_open_advert.0 - ... 3 more
2.2 报错原因
查看目标表的字段设置，如下：
mysql> desc crm_fact_cfc_user_open_advert;
+---------------------+------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------------+------+-----+-------------------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| open_id | bigint(20) unsigned | NO | MUL | NULL | |
| serial_id | varchar(64) | NO | PRI | 0 | |
| advert_url_id | bigint(20) unsigned | NO | | 0 | |
| order_channel_key | decimal(14,2) unsigned | NO | | 1.00 | |
| user_id | bigint(20) unsigned | NO | MUL | 0 | |
| user_type_key | int(10) | NO | | NULL | |
| confirm_status_key | decimal(14,2) unsigned | NO | | 0.00 | |
| confirm_time | datetime | YES | | NULL | |
| activity_status_key | decimal(14,2) unsigned | NO | | 0.00 | |
| order_id | bigint(20) unsigned | YES | | 0 | |
| gmt_create | datetime | NO | | CURRENT_TIMESTAMP | |
| gmt_modify | datetime | NO | | CURRENT_TIMESTAMP | |
| is_deleted | tinyint(4) unsigned | NO | | 0 | |
| rec_source | varchar(20) | NO | | | |
| hash_diff | varchar(32) | YES | | NULL | |
| ETLCreatedDate | datetime | NO | | CURRENT_TIMESTAMP | |
| ETLModifiedDate | datetime | NO | | CURRENT_TIMESTAMP | |
| process_id | bigint(20) | YES | | NULL | |
+---------------------+------------------------+------+-----+-------------------+----------------+
19 rows in set (0.00 sec)
可以发现虽然confirm_status_key字段有一定长度，但是为啥还会报错呢？因为confirm_status_key是可取-1的，但是建表语句中的confirm_status_key是有unsigned修饰的，导致超出范围。所以报错
2.3 解决办法
修改建表语句，去除所有的unsigned修饰的字段。
