sqoop报错：java.io.IOException: SQLException in nextKeyValu

最新推荐文章于 2024-08-11 03:08:49 发布

原创最新推荐文章于 2024-08-11 03:08:49 发布 · 1w 阅读

2 ·

CC 4.0 BY-SA版权

Sqoop 专栏收录该内容

5 篇文章

订阅专栏

本文分析了使用SQoop将MySQL数据迁移到Hive时遇到的java.io.IOException:SQLExceptioninnextKeyValue错误原因及解决办法。错误源自数据库中存在异常的时间格式数据，文章提供了排查及避免此类问题的方法。

sqoop从mysql导数据到hive的时候，报错：

java.io.IOException: SQLException in nextKeyValue

at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:266)

at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:483)

at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:76)

at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:85)

at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:139)

at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)

at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)

at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)

at org.apache.hadoop.mapred.Child$4.run(Child.java:268)

at java.security.AccessController.doPrivileged(Native Method)

at javax.security.auth.Subject.doAs(Subject.java:396)

at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)

at org.apache.hadoop.mapred.Child.main(Child.java:262)

Caused by: java.sql

查看sqoop的源码：

org/apache/sqoop/mapreduce/db/DBRecordReader.java

nextKeyValue()方法：

@Override
  public boolean nextKeyValue() throws IOException {
    try {
      if (key == null) {
        key = new LongWritable();
      }
      if (value == null) {
        value = createValue();
      }
      if (null == this.results) {
        // First time into this method, run the query.
        LOG.info("Working on split: " + split);
        this.results = executeQuery(getSelectQuery());
      }
      if (!results.next()) {
        return false;
      }

      // Set the key field value as the output key value
      key.set(pos + split.getStart());

      value.readFields(results);

      pos++;
    } catch (SQLException e) {
      LoggingUtils.logAll(LOG, e);
      if (this.statement != null) {
        try {
          statement.close();
        } catch (SQLException ex) {
          LoggingUtils.logAll(LOG, "Failed to close statement", ex);
        } finally {
          this.statement = null;
        }
      }
      if (this.connection != null) {
        try {
          connection.close();
        } catch (SQLException ex) {
          LoggingUtils.logAll(LOG, "Failed to close connection", ex);
        } finally {
          this.connection = null;
        }
      }
      if (this.results != null) {
        try {
          results.close();
        } catch (SQLException ex) {
          LoggingUtils.logAll(LOG, "Failed to close ResultsSet", ex);
        } finally {
          this.results = null;
        }
      }

      throw new IOException("SQLException in nextKeyValue", e);
    }
    return true;
  }

原因：查询中数据库中有错误时间格式的数据，数据值为 0000-00-00 00:00:00

sqoop的时候，需要排除异常数据。当然，也是数据库规范的问题，时间类型（timestamp）最好都给默认值。