sqoop java.lang.NullPointerException 和 No columns to generate for ClassWriter

使用sqoop导入导出MySQL的时候基本上没遇到什么问题，但是对于oracle数据库来说就有一些区别，下列便是使用sqoop连接oracle数据库的时候遇到的一点坑：

sqoop list-tables：

$ sqoop list-tables --connect jdbc:oracle:thin:@172.27.9.170:1521:ora11g --username MINER --password xxxxxx

Warning: /usr/hdp/2.3.2.0-2950/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.3.2.0-2950/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/07/31 11:49:31 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950
17/07/31 11:49:31 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/07/31 11:49:31 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
17/07/31 11:49:31 INFO manager.SqlManager: Using default fetchSize of 1000
17/07/31 11:49:52 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:759)
at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
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)

java.lang.NullPointerException，从这里看应该是缺少jdbc，进入sqooplib下查看：

$ cd /usr/hdp/2.3.2.0-2950/sqoop/lib$ ls -lh

发现没有ojdbc，下载ojdbc6.jar并上传到/usr/hdp/2.3.2.0-2950/sqoop/lib目录下，再来尝试。

$ sqoop list-databases --connect jdbc:oracle:thin:@172.27.9.170:1521:ora11g --username MINER --password miner123. 

发现仍然报错：

Warning: /usr/hdp/2.3.2.0-2950/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.3.2.0-2950/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/07/31 11:55:34 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950
17/07/31 11:55:34 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/07/31 11:55:34 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
17/07/31 11:55:34 INFO manager.SqlManager: Using default fetchSize of 1000
17/07/31 11:55:51 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.manager.OracleManager.listDatabases(OracleManager.java:695)
at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
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)

这次应该是没有找到ojdbc，使用参数手动指定–driver oracle.jdbc.driver.OracleDriver后执行成功：

$ sqoop list-tables --connect jdbc:oracle:thin:@172.27.9.170:1521:ora11g --username XDT --password xxxxxx --driver oracle.jdbc.driver.OracleDriver

但是问题又来了，使用sqoop导入oracle数据到hive表的时候又出现了问题：

sqoop import

$ sqoop import --connect jdbc:oracle:thin:@172.27.9.170:1521:ora11g --username XDT --password xxxxxx  --driver oracle.jdbc.driver.OracleDriver --table  K2 --hive-import --hive-table K2_test --hive-overwrite -m 1

Warning: /usr/hdp/2.3.2.0-2950/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.3.2.0-2950/accumulo does not exist! Accumulo imports will fail.
Please set \$ACCUMULO_HOME to the root of your Accumulo installation.
17/07/31 12:29:13 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950
17/07/31 12:29:13 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/07/31 12:29:13 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
17/07/31 12:29:13 INFO tool.BaseSqoopTool: delimiters with –fields-terminated-by, etc.
17/07/31 12:29:13 WARN sqoop.ConnFactory: Parameter –driver is set to an explicit driver however appropriate connection manager is not being set (via –connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
17/07/31 12:29:13 INFO manager.SqlManager: Using default fetchSize of 1000
17/07/31 12:29:13 INFO tool.CodeGenTool: Beginning code generation
17/07/31 12:29:28 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM K2 AS t WHERE 1=0
17/07/31 12:29:28 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:758)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
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:70)
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)
17/07/31 12:29:28 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:70)
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)

实际上刚刚成功地使用list-databases的时候，有一条输出：

WARN sqoop.ConnFactory: Parameter –driver is set to an explicit driver however appropriate connection manager is not being set (via –connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.

但是没有注意，so 取消–driver oracle.jdbc.driver.OracleDriver这条参数即可。

$ sqoop import --connect jdbc:oracle:thin:@172.27.9.170:1521:ora11g --username XDT --password xxxxxx  --table  K2 --hive-import --hive-table K2_test --fields-terminated-by ':' -m 1