Sqoop使用分析

Sqoop的Mysql数据导出实现分两种，一种是使用JDBC方式从Mysql中获取数据，一种是使用MysqlDump命令从MySql中获取数据，默认是 JDBC方式获取数据，如果要使用dump方式获取数据，需要添加 -direct 参数。

先说第一种：

配置语句时，需要添加 $CONDITIONS 点位符，比如：SELECT id FROM user WHERE $CONDITIONS，Sqoop在内部实现时会把它替换成需要的查询条件。

Sqoop起动后会先查询元数据，它会把 $CONDITIONS 替换为 (1=0) ，然后用得到的SQL语句查询数据库。这块Sqoop的实现不太好，对于导出一个表的情况，它会使用这个SQL查询三次数据库，分别是：获取 colInfo（最终得到columnTypes信息）、查询ColumnNames信息、生成QueryResult类时 generateFields操作获取columnTypeNames时。

Sqoop会对获取的Fields做校验，列不能重复，它还会处理数据库的字段到Java属性名的转换

QueryResult类是通过构建类文件，然后获取JavaCompiler，然后编译加载，为了提高处理性能，这块不是使用反射 实现，这个生成类内部处理mysql到hdfs属性值为空和分隔符的处理。

接着它会进行下面一个Sql查询操作，查询结果集为MIN(split列),MAX(split列)，查询条件的处理逻辑为 $CONDITIONS 替换为(1=1)，然后组合 (举例： SELECT MIN(id), MAX(id) FROM (SELECT ID,NAME,PASSPORT WHERE (1=1) ) AS t1 )，这样就查询出来此次导出数据最大的split列值和最小的split列值。

对于为整数、布尔值、时间格式、Float等 的分区列，进行split构建比较容易，这里就不多说，对于Text文本的处理方式需要解释一下，其先会对之前获取到的Min和Max的字串寻找它们最大 的相同字串，然后对于后面的字段转化为BigDecimal，结合char占两个字节（65536），进行处理，算法在 TextSplitter类中，比较简单，就是一个进制转换的问题。拆分好后，需要把Split的值再转换为String，然后加上公共 前缀，就构成了查询区间了。

其对数据的获取是在DataDrivenDBRecordReader中，在查询时会把 $CONDITIONS 替换成 split 的范围比如 ( id >= 1) && (id<10)，使用JDBC获取到游标，然 后移动游标处理数据。

第二种方法与第一种方式有下面的差别：

初始化元数据，它是在构建的查询语句后面添加 limit 1 ，比如：SELECT t.* FROM `user` AS t LIMIT 1，因为dump方式查询指定获取列是 t.*，当使用limit 0时，数据库不会给它返回必须的元数据信息。

dump方式在map进行数据的获取，其会构建mysqldump命令，然后使用java去调用，输入输出流和错误流，其实现了 org.apache.sqoop.util.AsyncSink抽象类，用来处理输入输出流和错误流。

优化策略：

Sqoop查询无数据会进行三次相同的Sql查询，可以合并查询，不过由于查询很快，这块不需要修改实现。

分区列选择对于查询元数据和导出的查询都有影响，应该对索引做调优，避免对分区列的排序操作，加快元数据查询速度和导出数据的速度，尽量选择自增加的主键ID做Split列，区分度好并且可以顺序读取数据。

导出操作的查询语句中，$CONDITIONS 会被替换为范围区间，创建索引时，要考虑这个查询的优化。

索引建议，考虑三个规则（使查询数据集较少、减少点的查询、避免排序操作），Sqoop场景下，如果分区列不是主键（自增加）时，把分 区列做为联合索引的第一个字段，其它被选择的查询条件做为索引的其它字段，可优化此查询。

分区列的选择，要避免Split后数据不均衡。

从实现上来看-m参数是可以增加任务的并行度的，但数据库的读线程是一定的，所以-m过大对于数据库会是一个压力，当然可以限制任务的同时最多拥有资源量。在Sqoop的场景下，数据库才是一个影响并发的瓶颈，增加job数意义不大。

下面列出Sqoop目前1.4.6版本存在的两个问题。

查看Sqoop源码，发现其存在两个比较严重的问题。

问题 1、数据分片与Mapper的问题

Sqoop在抽取时可以指定-m的参数，但这个-m的参数是控制mapper的数量的，但它也决定了最后能够生成的文件的数目，调节这个值可以实现对结果文件大小的控制，但是，如果产生的文件的格式不能够被分割，那么对这个数据的下游性能有很大影响，同时Sqoop在启动时会启动-m个MapperTask，会对数据库产生m的并发读取，需要修改Sqoop的实现，合并多个Split到同一个Mapper中。

个人建议可以加个 -split-per-map 参数，比如设置-m=4 -split-per-map=2,则对结果集分 8 片，每个Mapper处理两片数据，最后共产生 8 个文件。

问题 2、分片效率低

Sqoop在做分片处理时有问题，其实现会使用Select Max(splitKey),Min(splitKey) From ( –select参数 ) as t1查询分片信息，在Mysql下，这样的查询会产生一个以split-id为主键的临时表，如果数据量不大，临时表数据可以在内存中，处理速度还可以保证。但如果数据量很大，内存中已经存放不下时，这些数据会被保存为MyISAM表存放到磁盘文件中，如果数据量再大一些，磁盘文件已经存放不下临时表时，拆分数据会失败。如果数据量大，即使没有查询也会很慢，大约会占用整个导出时间的45%，优化空间很大，如果不修改实现的话，不适合做大数据量表的全量数据导出操作。

解决方案一：

配置–boundary-query参数，指定使用的查询语句

解决方案二：

修改：org.apache.sqoop.mapreduce.DataDrivenImportJob的

@Contract(“null, _ -> !null”)private String buildBoundaryQuery(String col, String query)

修改代码如下

/**
   * Build the boundary query for the column of the result set created by
   * the given query.
   * @param col column name whose boundaries we're interested in.
   * @param query sub-query used to create the result set.
   * @return input boundary query as a string
   */
  private String buildBoundaryQuery(String col, String query) {
    if (col == null || options.getNumMappers() == 1) {
      return "";
    }

    // Replace table name with alias 't1' if column name is a fully
    // qualified name.  This is needed because "tableName"."columnName"
    // in the input boundary query causes a SQL syntax error in most dbs
    // including Oracle and MySQL.
    String alias = "t1";
    int dot = col.lastIndexOf('.');
    String qualifiedName = (dot == -1) ? col : alias + col.substring(dot);

    ConnManager mgr = getContext().getConnManager();
    String ret = mgr.getInputBoundsQuery(qualifiedName, query);
    if (ret != null) {
      return ret;
    }

//    return "SELECT MIN(" + qualifiedName + "), MAX(" + qualifiedName + ") "//        + "FROM (" + query + ") AS " + alias;
    return initBoundaryQuery(qualifiedName, query, alias);
  }

  private String initBoundaryQuery(String qualifiedName, String query, String alias) {
    StringBuilder regex = new StringBuilder();
    regex.append("(\\s[A|a][S|s][\\s][`]?");
    for (char c : qualifiedName.toCharArray()) {
      regex.append('[').append(c).append(']');
    }
    regex.append("[`|\\s|,])");
    final Matcher matcher1 = Pattern.compile(regex.toString()).matcher(query);
    final boolean asCheckOk = !matcher1.find();
    if(asCheckOk) {
      final Matcher matcher2 = Pattern.compile("(\\s[F|f][R|r][O|o][M|m]\\s)").matcher(query);
      int count = 0;
      while (matcher2.find()) {
        count++;
      }
      boolean fromCheckOk = count == 1;
      if(fromCheckOk) {
        final Matcher matcher = Pattern.compile("(\\s[F|f][R|r][O|o][M|m]\\s[\\s\\S]*)").matcher(query);
        while (matcher.find()) {
          return "SELECT MIN(" + qualifiedName + "), MAX(" + qualifiedName + ") "
                  + matcher.group();
        }
      }
    }
    return "SELECT MIN(" + qualifiedName + "), MAX(" + qualifiedName + ") "
            + "FROM (" + query + ") AS " + alias;
  }

问题 3、对非整形和布尔型的字段分区可能有数据丢失风险

Sqoop实现分区数据替换时，没有使用Prepared statement来做，而是简单的在查询时会把 $CONDITIONS 替换成 split 的范围比如 ( id >= xxxx) && (id<yyyy)，但当String进行分区时，得到的xxxx和yyyy有很大可能是乱码，然后就会引起查询问题，这个在使用非direct方式时，可以通过修改为Prepared Statement解决（未测试）。但在Direct方式下其导出数据使用的是mysqldump方式，通过命令行传递查询参数，就无法解决了。（其实可以修改它的Split算法，使得范围区间不会产生乱码），所以建议不要使用非整形的列做拆分列。

问题4、Mysql中数据影响导出

Mysql在的timestamp列允许 “0000:00:00 00:00:00″ 这样的数据存储，在JDBC的实现中，Timestamp的格式是会被转化为java.sql.Timestamp的对象的，但java.sql.Timestamp对象无法表示 “0000:00:00 00:00:00″，所以在调用java.sql.Timestamp getTimestamp(int columnIndex) throws SQLException;这个方法时 SQLException 会被抛出来，Sqoop的JDBC方式导出数据到HDFS的实现就是采用这个方法去读取Timestamp的数据，当数据中出现这样的时间存储时，就直接抛出了SQLException异常，这个异常没有被捕获，导致整个导出失败。

我们可以在Sqoop做相应的修改，让它避免抛出异常，使任务可以执行下去。

// 代码在// org.apache.sqoop.orm.ClassWriter private void myGenerateDbRead(Map<String, Integer> columnTypes,//                                  String[] colNames,//                                  StringBuilder sb,//                                  int methodNumber,//                                  int size,//                                  boolean wrapInMethod)
...
  if("java.sql.Timestamp".equals(javaType)) {
    sb.append("    try {\n");
  }
  sb.append("   this." + col + " = JdbcWritableBridge." +  getterMethod
      + "(" + (i + 1) + ", __dbResults);\n");
  if("java.sql.Timestamp".equals(javaType)) {
    sb.append("    } catch (SQLException e) {\n    this." + col + " = null;\n    }");
  }
...

问题4、Sqoop导出时数据中特殊字符的替换

Sqoop抽取时可以对Hive默认的分隔符做替换，它们是\n \r \01，可以使用 –hive-drop-import-delims做替换，但是它的实现是写死的，如果我们采用的不是Hive默认的分隔符，那么它就不会做相应的替换操作，在Hive中很多人习惯使用\t做列分隔，因为mysql的客户端导出文本默认就是以\t导出的，Sqoop不会对这个数据进行替换。

有两种方法可以解决这个问题。

方法1：修改Sqoop实现，代码在 org.apache.sqoop.lib.FieldFormatter方法2：由Mysql做替换，Sql语句可以写为: replace(colname, “\t”, “”) as colname

问题5、sqoop导入mysql数据出错

这个是由于mysql-connector-java的bug造成的，出错时我用的是mysql-connector-java-5.1.10-bin.jar，更新成mysql-connector-java-5.1.32-bin.jar就可以了。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

报错信息如下

14/12/03 16:37:58 ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result  set com.mysql.jdbc.RowDataDynamic@ 54b0a583  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@ 54b0a583  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.
...
14/ 12/ 03  16: 37: 58 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException:  No columns  to generate  for ClassWriter
...