sqoop import for orders table

[rishit606]

hi durga, i am facing error while trying to import orders table from retail_db database in mysql .

[testsandbox@sandbox ~]$ sqoop import --connect jdbc:mysql://sandbox.hortonworks.com/retail_db \

--table orders \ --fields-terminated-by '^A' \ --lines-terminated-by '\n' \ --hive-home /user/hive/warehouse \ --hive-import \ --hive-table orders \ --hive-overwrite \ --username retail_dba \ --password hadoop Warning: /usr/hdp/2.4.0.0-169/accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. 16/08/24 08:49:17 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.4.0.0-169 16/08/24 08:49:17 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 16/08/24 08:49:17 WARN sqoop.SqoopOptions: Character argument ^A has multiple characters; only the first will be used. 16/08/24 08:49:19 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 16/08/24 08:49:19 INFO tool.CodeGenTool: Beginning code generation SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.4.0.0-169/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.4.0.0-169/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] 16/08/24 08:49:20 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM orders AS t LIMIT 1 16/08/24 08:49:20 ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@9fa6604 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 com.mysql.jdbc.RowDataDynamic@9fa6604 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:286) at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241) at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227) at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1845) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) at org.apache.sqoop.Sqoop.run(Sqoop.java:148) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235) at org.apache.sqoop.Sqoop.main(Sqoop.java:244) 16/08/24 08:49:20 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:1651) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) at org.apache.sqoop.Sqoop.run(Sqoop.java:148) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235) at org.apache.sqoop.Sqoop.main(Sqoop.java:244) i got same message when used import-all .

note that all other tables has been imported except orders.

kindly suggest.

thanks, rishit Shah

[Pushkr]

@rishit606 have you tried using correct port in connect cluase -

--connectjdbc:mysql://sandbox.hortonworks.com:3306/retail_db

or tried adding --driver clause as - --driver com.mysql.jdbc.Driver

[gbhardwaja]

For anyone facing below issue: 17/08/20 13:01:31 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: java.util.concurrent.ExecutionException: java.lang.NoSuchMethodError: org.apache.commons.io.FileUtils.isSymlink(Ljava/io/File;)Z

proposed solution which worked for me: check if mapred-site.xml is present in /etc/hadoop, if not then create a one or copy mapred-site-template to mapred-site.xml. and try again sqoop command like: ./sqoop import --connect jdbc:mysql://localhost/sample --driver com.mysql.jdbc.Driver --username root --password root --table departments;