hive sqoop 问题 Io exception: Connection timed out

来源：互联网发布：端口扫描器怎么用编辑：程序博客网时间：2024/06/06 00:20

当从数据库读数据的时候有可能会 Connection timed out，这是由于sqoop会缓存 connection的原因，当要load 数据到hive的时候会再次访问oracle数据库，但是这个时候缓存的数据库连接对象已经超时了或者被数据库干掉了，解决方法有三个：

方法一：

修改oracle数据库配置：

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

方法二：

先看sqoop 源码片段

@Override
public Connection getConnection() throws SQLException {
    if (null == this.connection) {
      this.connection = makeConnection();
    }

return this.connection;
}

第一次获取数据库连接的时候将连接保存，下次直接判断this.connection是否存在若存在复用，如果这个时候缓存的连接超时了就会报错：（Io exception: Connection timed out）

@Override
public Connection getConnection() throws SQLException {
    if (null == this.connection) {
      this.connection = makeConnection();
    }

return this.connection;
}

每次都直接创建就避免了问题

@Override
public Connection getConnection() throws SQLException {
  // if (null == this.connection) {
      this.connection = makeConnection();
  // }

return this.connection;
}

方法三：

起个线程每三十秒（oracle 默认连接超时时间应该是60秒）发送个简单的请求防止超时

遇到问题定位了下，对sqoop还不是特别熟悉，但感觉代码不是很，难比spring源码差远了。

异常详细：

13/05/07 19:17:37 ERROR manager.SqlManager: Error executing statement: java.sql.SQLRecoverableException: Io exception: Connection timed out
java.sql.SQLRecoverableException: Io exception: Connection timed out
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:458)
at oracle.jdbc.driver.T4CConnection.doRollback(T4CConnection.java:621)
at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:3389)
at org.apache.sqoop.manager.OracleManager$ConnCache.getConnection(OracleManager.java:190)
at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:283)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:525)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:548)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:191)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:175)
at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:126)
at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:174)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:415)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)
at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)
at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
at com.cloudera.sqoop.Sqoop.main(Sqoop.java:57)
13/05/07 19:17:37 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:171)
at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:174)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:415)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)
at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)
at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
at com.cloudera.sqoop.Sqoop.main(Sqoop.java:57)