使用kettle将mysql数据导入hive报错解决方法

冰与果汁鸽

于 2024-09-12 18:58:57 发布

阅读量88

点赞数 1

文章标签： mysql hive 数据库 sql big data hdfs 大数据

本文链接：https://blog.csdn.net/m0_65545337/article/details/142183433

版权

如题，启动任务后报错如下：

2024-09-11 20:33:59.421 INFO  <mysqltohive2 - 表输出> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  完成处理 (I=0, O=1, R=2, W=0, U=0, E=1)
2024-09-11 20:33:59.427 ERROR <mysqltohive2 - 表输出> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  错误被检测到!
2024-09-11 20:33:59.427 ERROR <mysqltohive2 - 表输出> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  错误被检测到!
2024-09-11 20:33:59.440 ERROR <mysqltohive2 - 表输出> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  错误被检测到!
2024-09-11 20:33:59.451 WARN  <mysqltohive2 - 表输出> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  转换被检测 
2024-09-11 20:33:59.451 WARN  <mysqltohive2 - 表输出> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  转换正在杀死其他步骤!
2024-09-11 20:36:34.361 INFO  <Thread-103> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  为了转换解除补丁开始  [mysqltohive2]
2024-09-11 20:36:34.456 INFO  <mysqltohive2 - 表输入> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  Finished reading query, closing connection.
2024-09-11 20:36:34.458 INFO  <mysqltohive2 - 表输入> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  完成处理 (I=14, O=0, R=0, W=14, U=0, E=0)
2024-09-11 20:36:38.837 ERROR <mysqltohive2 - 插入 / 更新> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  Error in step, asking everyone to stop because of:
2024-09-11 20:36:38.838 ERROR <mysqltohive2 - 插入 / 更新> [E:\JAVA_CODE\spoon\kettle_ktr\mysqltohive2.ktr]  org.pentaho.di.core.exception.KettleDatabaseException: 
Error inserting/updating row
Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask

	at org.pentaho.di.core.database.Database.insertRow(Database.java:1331)
	at org.pentaho.di.core.database.Database.insertRow(Database.java:1255)
	at org.pentaho.di.core.database.Database.insertRow(Database.java:1243)
	at org.pentaho.di.core.database.Database.insertRow(Database.java:1231)
	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:114)
	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:299)
	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
	at java.lang.Thread.run(Thread.java:750)
Caused by: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
	at org.apache.hive.jdbc.HiveStatement.waitForOperationToComplete(HiveStatement.java:401)
	at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:266)
	at org.apache.hive.jdbc.HiveStatement.executeUpdate(HiveStatement.java:511)
	at org.apache.hive.jdbc.HivePreparedStatement.executeUpdate(HivePreparedStatement.java:122)
	at org.pentaho.di.core.database.Database.insertRow(Database.java:1298)
	... 7 more

每次启动任务只能成功导入一到两条数据。

解决方法：

方法一：

取消使用批量插入选项，勾选忽略插入错误，如图所示:

方法二：

在hive配置文件中找到以下配置项，将值改为false：

  <property>
    <name>hive.stats.column.autogather</name>
    <value>false</value>
    <description>A flag to gather column statistics automatically.</description>
  </property>

重启hive，一劳永逸。

该配置项用于自动统计收集列信息，以帮助hive优化器制定更好的查询策略，如果有性能要求，选择方法一。