kettle常见报错集锦

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修饰的字段。