sqoop系列-sqoop MySQL 导入Hive JSON 字段乱码

问题简介

最近业务方反馈依照导入MySQL表导入Hive有部分字段变更乱码，于是乎走上了解决乱码的不归路。
集群信息
服务器系统版本：centos 7.2
cdh 版本：cdh5.9.0
hadoop 版本：2.6.0+cdh5.9.0
hive 版本：1.1.0+cdh5.9.0
sqoop 版本：1.4.6+cdh5.9.0
备注：涉及敏感信息的的变量，使用${xxxx} 代替

问题定位

首先，导入乱码想都不用想，肯定要确定mysql数据库编码是否有问题
于是乎打开导入mysql数据库检查一遍编码

show variables like 'character%'

结果很满意，编码都是utf-8

检查导入用的MySQL链接配置，已经添加了

useUnicode=true&characterEncoding=utf-8

这还乱码！！！这还乱码！！！这还乱码！！！ 不科学啊！！！！
同一张表中同样是中文字段有的乱码有的不乱，猜想是否字段类型问问题
查看表字段信息

果然，字段类型不相同，但编码都是utf-8，于是乎确定问题跟数据库编码和链接编码无关，可能跟字段类型有关。初步定位到sqoop导入MySQL字段类型为JSON会乱码。高兴了，开心了，问题貌似找到了，于是乎百度，google关键词：

sqoop 导入 MySQL 字段类型 JSON 乱码

结果惨不忍睹，跟sqoop 相关的 json 乱码没有，好不容易找到一篇文章介绍，升级jdbc版本可以解决JAVA读取MySQL JSON 字段乱码的问题；还有另一篇文档介绍：通过升级jdbc版本解决datax导入MySQL JSON 字段乱码问题。
于是乎尝试升级项目中使用的JDBC版本，但导入JSON乱码还是没解决！
度娘，google没相关文章，可能解决的办法尝试了还不行。我太难啦！！我太难了！！太难了！！
没办法了，只能去看sqoop的原码了，关于sqoop原码解读这两篇文章很良心 文章一 文章二

撸出看源码关心的点
关于导入字段处理的两个点
导入数据库类型到java的映射(org.apache.sqoop.manager.ConnManager)

/**
   * Resolve a database-specific type to the Java type that should contain it.
   * @param sqlType     sql type
   * @return the name of a Java type to hold the sql datatype, or null if none.
   */
  public String toJavaType(int sqlType) {
    // Mappings taken from:
    // http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html
    if (sqlType == Types.INTEGER) {
      return "Integer";
    } else if (sqlType == Types.VARCHAR) {
      return "String";
    } else if (sqlType == Types.CHAR) {
      return "String";
    } else if (sqlType == Types.LONGVARCHAR) {
      return "String";
    } else if (sqlType == Types.NVARCHAR) {
      return "String";
    } else if (sqlType == Types.NCHAR) {
      return "String";
    } else if (sqlType == Types.LONGNVARCHAR) {
      return "String";
    } else if (sqlType == Types.NUMERIC) {
      return "java.math.BigDecimal";
    } else if (sqlType == Types.DECIMAL) {
      return "java.math.BigDecimal";
    } else if (sqlType == Types.BIT) {
      return "Boolean";
    } else if (sqlType == Types.BOOLEAN) {
      return "Boolean";
    } else if (sqlType == Types.TINYINT) {
      return "Integer";
    } else if (sqlType == Types.SMALLINT) {
      return "Integer";
    } else if (sqlType == Types.BIGINT) {
      return "Long";
    } else if (sqlType == Types.REAL) {
      return "Float";
    } else if (sqlType == Types.FLOAT) {
      return "Double";
    } else if (sqlType == Types.DOUBLE) {
      return "Double";
    } else if (sqlType == Types.DATE) {
      return "java.sql.Date";
    } else if (sqlType == Types.TIME) {
      return "java.sql.Time";
    } else if (sqlType == Types.TIMESTAMP) {
      return "java.sql.Timestamp";
    } else if (sqlType == Types.BINARY
        || sqlType == Types.VARBINARY) {
      return BytesWritable.class.getName();
    } else if (sqlType == Types.CLOB) {
      return ClobRef.class.getName();
    } else if (sqlType == Types.BLOB
        || sqlType == Types.LONGVARBINARY) {
      return BlobRef.class.getName();
    } else {
      // TODO(aaron): Support DISTINCT, ARRAY, STRUCT, REF, JAVA_OBJECT.
      // Return null indicating database-specific manager should return a
      // java data type if it can find one for any nonstandard type.
      return null;
    }
  }

导入数据库映射到Hive的数据类型 (org.apache.sqoop.hive.HiveTypes)

/**
   * Given JDBC SQL types coming from another database, what is the best
   * mapping to a Hive-specific type?
   */
  public static String toHiveType(int sqlType) {

      switch (sqlType) {
          case Types.INTEGER:
          case Types.SMALLINT:
              return "INT";
          case Types.VARCHAR:
          case Types.CHAR:
          case Types.LONGVARCHAR:
          case Types.NVARCHAR:
          case Types.NCHAR:
          case Types.LONGNVARCHAR:
          case Types.DATE:
          case Types.TIME:
          case Types.TIMESTAMP:
          case Types.CLOB:
              return "STRING";
          case Types.NUMERIC:
          case Types.DECIMAL:
          case Types.FLOAT:
          case Types.DOUBLE:
          case Types.REAL:
              return "DOUBLE";
          case Types.BIT:
          case Types.BOOLEAN:
              return "BOOLEAN";
          case Types.TINYINT:
              return "TINYINT";
          case Types.BIGINT:
              return "BIGINT";
          default:
        // TODO(aaron): Support BINARY, VARBINARY, LONGVARBINARY, DISTINCT,
        // BLOB, ARRAY, STRUCT, REF, JAVA_OBJECT.
        return null;
      }
  }

关于SQL字段类型（java.sql.Types）里面记录了 sql 类型对应的数值
如:

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type
 * <code>CHAR</code>.
 */
        public final static int CHAR            =   1;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type
 * <code>VARCHAR</code>.
 */
        public final static int VARCHAR         =  12;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type
 * <code>LONGVARCHAR</code>.
 */
        public final static int LONGVARCHAR     =  -1;

导入获取字段类型的源码（org.apache.sqoop.orm.ClassWriter），

protected Map<String, Integer> getColumnTypes() throws IOException {
    if (options.getCall() == null) {//导出, select xxx from table limit 1, 获取rs 的类型
      return connManager.getColumnTypes(tableName, options.getSqlQuery());
    } else {//导入
      return connManager.getColumnTypesForProcedure(options.getCall());
    }
  }

在看源码的过程中，根据hive自动生成表字段类型，看得出， 导入的时候 sqoop 把json字段转换成了string类型
1）怀疑是json类型 toString的时候没有指定字符集编码使用了机器默认的字符集导致了乱码，然运维查看了集群的编码但是都是utf-8没问题
2）源码中无论是java还是hive的类型转换都没见有把 Json转成string的，但是导入的时候没报错却自动转换成string，十分奇怪；
于是乎个单元测试 确定jdbc读取出来的MySQL JSON类型的字段到底是什么类型

public class test {

    public static void main(String[] args) throws ParseException {
        try (Connection con = JDBCUtils.getMySQLConn(
                JDBCUtils.buildHiveConUrl("jdbc:mysql://${url}", ${port}, "${db}"),
                "${user}", "${pwd}")) {

            Statement stmt = con.createStatement();
            ResultSet resultSet = stmt.executeQuery("select * from ${table} limit 1  ");
            while (resultSet.next()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    System.out.println(metaData.getColumnName(i) +
                            " ---> " + metaData.getColumnTypeName(i) +
                            " ---> " + metaData.getColumnType(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

id ---> INT UNSIGNED ---> 4
uid ---> CHAR ---> 1
question_text ---> VARCHAR ---> -1
question_options ---> JSON ---> 1
question_answer ---> VARCHAR ---> -1
question_explain ---> VARCHAR ---> -1
question_type ---> INT ---> 4
question_diff ---> INT ---> 4
chapter_ids ---> JSON ---> 1
knowledge_ids ---> JSON ---> 1
tag_ids ---> JSON ---> 1
app_code ---> VARCHAR ---> 12
create_time ---> DATETIME ---> 93

结果发现jdbc在读取json给的时候getColumnTypeName叫JSON但是类型（getColumnType）居然和char是相同的（ps：解答了JSON导入hive 成了String类型）。并且通过jdbc读取出来的json字符串toSTring并没有乱码；
万分无奈，因为低版本jdbc读取json乱码，会不会是jdbc jar冲突呢
于是乎查找了 oozie 的 sqoop导入的日志，发现还真是jar版本冲突

我自己的项目中指定了jdbc的版本，但是还加载了oozie的 sharelib jdbc；
对于oozie jar 冲突有两种解决办法
1）修改自己冲突的jar命名与sharelib中的已知，指定优先使用户自定义的jar （jar名字必须与sharelib中一样，否则还是会加载 sharelib的jar）
2）替换 sharelib 中的jar 并更新jar信息，更新jar信息很重要如果不更新用到sharelib会报错；

总结

这次 sqoop MySQL导入Hive Json字段乱码排查很复杂，但到最后去发现是JDBC冲突问题，加载了旧版的jdbc导致json导出乱码；但在这次问题排查中也收获了

1. sqoop导入源码流程的熟悉，字段和类型的处理
2. 如何更新oozie共享jar，加深了对oozie的掌控
3. JDBC中对MySQL字段类型的判定和处理，CHAR和JSON是同一个类型

几个更新Oozie更新共享jar的命令

bin/oozie-setup.sh sharelib create -fs hdfs://${集群} -locallib  ${共享jar本地路径}  #从本地目录向hdfs复制sharelib
bin/oozie admin -oozie http://${oozie-server-host}:11000/oozie -sharelibupdate #更新oozie的sharelib
bin/oozie admin -oozie http://${oozie-server-host}:11000/oozie -shareliblist  #查看sharelib列表(正常应该有多条数据)