最近稍微学习了一个kettle操作了各种表数据的导入导出和相互转化在mysql导入到hive时遇到了很多问题也踩了好多坑简要今天来总结一下:
场景一mysql直接导入到hive报错
首先我们要确保kettle可以正常连接到mysql出现一下界面即证明kettle对mysql的连接是正常的:
同时也需要确保kettle对hive的连接也是正常的记得提前打开hiveserver2远程连接服务,出现以下界面几位正常:(注意kettle里面是没有mysql的jar包的要拿到自己的mysql jar包传入到kettle的lib目录下)
确保两个连接都是正常后我们把mysql的连接当作表输入hive的连接当作表输出mysql要指定好数据库,hive要提前创建好表用来接收mysql导出的数据操作如下:
点击左边表输出里面的sql是可以自动生成sql语句来帮我们创建表的。此时我们点击左上角的运行会发现报了以下错误:
2024/09/11 20:27:21 - 表输出.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Because of an error, this step can't continue:
2024/09/11 20:27:21 - 表输出.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleException:
2024/09/11 20:27:21 - 表输出.0 - Error inserting row into table [m_hive] with values: [7369], [SMITH], [CLERK], [7902], [1980/12/17 00:00:00.000], [800.0], [null], [20]
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 - offending row : [empno Integer(9)], [ename String], [job String], [mgr Integer(9)], [hiredate Date], [sal Number(7, 2)], [comm Number(7, 2)], [deptno Integer(9)]
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 - Error setting value #5 [Date] on prepared statement
2024/09/11 20:27:21 - 表输出.0 - Method not supported
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:384)
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125)
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2024/09/11 20:27:21 - 表输出.0 - at java.lang.Thread.run(Thread.java:750)
2024/09/11 20:27:21 - 表输出.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2024/09/11 20:27:21 - 表输出.0 - offending row : [empno Integer(9)], [ename String], [job String], [mgr Integer(9)], [hiredate Date], [sal Number(7, 2)], [comm Number(7, 2)], [deptno Integer(9)]
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 - Error setting value #5 [Date] on prepared statement
2024/09/11 20:27:21 - 表输出.0 - Method not supported
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.core.database.Database.setValues(Database.java:1098)
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:261)
2024/09/11 20:27:21 - 表输出.0 - ... 3 more
2024/09/11 20:27:21 - 表输出.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2024/09/11 20:27:21 - 表输出.0 - Error setting value #5 [Date] on prepared statement
2024/09/11 20:27:21 - 表输出.0 - Method not supported
2024/09/11 20:27:21 - 表输出.0 -
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:5477)
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.core.database.Database.setValue(Database.java:1080)
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.core.database.Database.setValues(Database.java:1096)
2024/09/11 20:27:21 - 表输出.0 - ... 4 more
2024/09/11 20:27:21 - 表输出.0 - Caused by: java.sql.SQLFeatureNotSupportedException: Method not supported
2024/09/11 20:27:21 - 表输出.0 - at org.apache.hive.jdbc.HivePreparedStatement.setDate(HivePreparedStatement.java:460)
2024/09/11 20:27:21 - 表输出.0 - at org.pentaho.di.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:5412)
2024/09/11 20:27:21 - 表输出.0 - ... 6 more
这个错误大致是在说hive中的某些方法不被支持,这个错误相关信息在博客和其他网站上都非常少,摸索了很久后发现在 hive/conf/.hiverc下加入这样一句话就可以解决该报错
set hive.stats.column.autogather=false;
场景二mysql导入数据到hdfs报错
mysql导入导hdfs相比于直接导入到Hive流程要长很多,我们首先要把流程都搞对。
首先要做主对象树这里选择Hadoop clusters 点击add driver操作如下
然后在Kettle的以下目录下添加driver
添加后会加载一会出现以下界面说明操作成功:
接着重启kettle点击主队象树下面的Hadoop clusters点击new cluster
点击后弹出以下界面导入hadoop和Hive的一个五个配置文件
然后配置自己的主机名和端口号其他暂时不用配置示例配置如下:
然后点击next出现以下页面点击第一个选项
出现以下页面说明hdfs连接成功:
然后我们创建mysql表输出和BigData下的hadoopoutput作为输出:
选择以下配置:
运行转换 该所遇到的各种错误见下遍博客