欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

sqoop导入数据出现问题归整

程序员文章站 2024-02-11 21:25:46
...

问题1

执行sqoop命令

 sqoop import --connect jdbc:mysql://192.168.0.189:3306/demo --username root --password 898 --table sys_user

错误内容如下所示

ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set aaa@qq.com is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
java.sql.SQLException: Streaming result set aaa@qq.com is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
	at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2735)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1899)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1524)
	at com.mysql.jdbc.ConnectionImpl.getMaxBytesPerChar(ConnectionImpl.java:3003)
	at com.mysql.jdbc.Field.getMaxBytesPerCharacter(Field.java:602)
	at com.mysql.jdbc.ResultSetMetaData.getPrecision(ResultSetMetaData.java:445)
	at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:305)
	at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
	at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
	at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:328)
	at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1853)
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1653)
	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:243)
19/05/24 16:13:43 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1659)
	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

分析原因

网上找了一通,说这个是由于mysql-connector-java的bug造成的。由使用Ambari安装的sqoop,mysql-connector-java.jar版本给去掉了。
目录结构如下
sqoop导入数据出现问题归整
重新下载mysql-connector-java-5.1.32-bin.jar包
下载地址为
http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.32.tar.gz
下载完后解压,在解压的目录下可以找到mysql-connector-java-5.1.32-bin.jar。
sqoop导入数据出现问题归整

哈哈,完美解决!
mysql-connector-java.jar版本问题。

问题2

报错信息:“Generating splits for a textual index column allowed only in case of “-Dorg.apache.sqoop.splitter.allow_text_splitter=true” property passed as a parameter”。

主要问题是“–split-by id”这个参数指定的id是一个文本格式,所以需要在命令中加入选项"-Dorg.apache.sqoop.splitter.allow_text_splitter=true",补齐命令:

ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Generating splits for a textual index column allowed only in case of "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" property passed as a parameter
	at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:204)
	at org.apache.hadoop.mapreduce.JobSubmitter.writeNewSplits(JobSubmitter.java:301)
	at org.apache.hadoop.mapreduce.JobSubmitter.writeSplits(JobSubmitter.java:318)
	at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:196)
	at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1290)
	at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1287)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
	at org.apache.hadoop.mapreduce.Job.submit(Job.java:1287)
	at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1308)
	at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:200)
	at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:173)
	at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:270)
	at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
	at org.apache.sqoop.manager.MySQLManager.importTable(MySQLManager.java:127)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:507)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:243)
Caused by: Generating splits for a textual index column allowed only in case of "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" property passed as a parameter
	at org.apache.sqoop.mapreduce.db.TextSplitter.split(TextSplitter.java:67)
	at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:201)
	... 23 more

看我的要导出的表,确实是主键userId为文本格式。
所有还是乖乖的加上这个配置项吧
sqoop导入数据出现问题归整

 sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" --connect jdbc:mysql://192.168.0.189:3306/demo --username root --password 898 --table sys_user

哈哈,完美解决!
导出表主键为文本类型导致。