如何完美解决Sqoop导入导出MySQL数据错位问题

我发现小伙伴们在使用Sqoop把数据从MySQL导入到Hive的过程中经常会遇到数据错位的问题，虽然最后都是通过添加参数的方法来解决这个问题，但是我认为这并不是一个完美的解决方案，所以花了一点时间研究了一下Sqoop。

Sqoop是连接关系型数据库和Hadoop的桥梁，主要有两个功能：

    （1）将关系型数据库的数据导入到Hadoop及其相关的系统中，如HDFS、Hive和HBase；

    （2）将数据从Hadoop系统里抽取并导出到关系型数据库中，如MySQL、Oracle。

Sqoop会把输入的命令转换成MapReduce任务，可以并行计算，因此性能非常好。

一个最简单的MySQL数据导入到Hive的例子：

sqoop import \
--connect jdbc:mysql://127.0.0.1:3306/test \
--username root \
--password '123456' \
--table employee \
--hive-import \
--fields-terminated-by '\001' \  # 指定字段分隔符
--create-hive-table \
--hive-database test \
--hive-table employee \
-m 1

如果数据中本身就存在分隔符（\001）或换行符（\n、\r），那么使用这种方式导入Hive就会存在数据错位的问题。

网上查找出的解决办法几乎都是通过添加参数来解决：

可以使用--hive-drop-import-delims选项在导入时删除这些字符，或者使用--hive-delims-replacement选项在导入时将这些字符替换为自定义的字符串，以保证数据与Hive的文本数据格式兼容。

--hive-drop-import-delims# 导入到Hive时，从字符串字段中删除\n、\r和\001；
--hive-delims-replacement：	# 在导入到Hive时，将字符串字段中的\n、\r和\001替换为用户定义的字符串。

但是这种方案存在以下三个问题：

（1）指定的分隔符只能是：\001；

（2）一些特殊数据类型不能被正确地处理，如Oracle的CLOB类型；

（3）由于会删除数据中的一些符号，这会导致目标数据与源数据不一致。

正因为存在这些问题，所以我说此操作不是一个完美的解决方案。我仔细思考了一下，发现其实引起数据错位的根本原因是采用了文本文件格式（textfile），那么数据本身存在的分隔符或换行符将会导致这行数据不能被正确的解析。我们知道Hadoop系统是支持列式存储文件格式的，而这种格式是不依赖分隔符来解析的，也不受换行符的影响，那么如果Sqoop采用列式存储格式的话是不是就可以完美解决数据错位问题了呢。经过研究和反复测试验证，得出以下结论：

（1）Sqoop采用列式存储格式（Parquet、ORC）可以完美解决数据错位问题；

（2）Sqoop本身直接支持Parquet格式，但是在export的时候会报错（这个Bug官方没有修复），只能通过Hcatlog的方式解决；

（3）Sqoop集成了Hcatlog工具，因此可以支持ORC格式。

下面给出我认为可以完美解决Sqoop导入/导出MYSQL数据错位问题的最佳实践：

# 先在Hive中创建ORC表
CREATE TABLE `test.employee`(
  `id` int, 
  `name` string, 
  `age` int)
STORED AS ORC;

# 再使用Hcatlog的方式把数据从MySQL导入到Hive ORC表
sqoop import \
--connect jdbc:mysql://127.0.0.1:3306/test \
--username root \
--password '123456' \
--table employee \
--hcatalog-database test \
--hcatalog-table employee \
-m 1


# 如果数据需要从Hive导出到MySQL则使用以下命令
sqoop export \
-Dsqoop.export.records.per.statement=10 \
--connect jdbc:mysql://127.0.0.1:3306/test \
--username root \
--password '123456' \
--table employee \
--update-key id \
--update-mode allowinsert \
--batch \
--hcatalog-database test \
--hcatalog-table employee \
-m 1

最后再说一下HCatalog方式与Sqoop方式两者的区别：

（1）数据格式

Sqoop方式支持的数据格式较少，HCatalog支持的较多，比如Sqoop方式不支持ORC格式的表，但是HCatalog支持。

（2）数据增加形式

Sqoop方式允许数据覆盖，HCatalog不允许数据覆盖，每次都只是追加。

（3）字段一致性

Sqoop方式比较随意，不要求源表和目标表字段相同（字段名称和个数都可以不相同），它抽取的方式是将字段按顺序插入，比如目标表有三个字段，源表只有一个字段，它会将数据插入到Hive表的第一个字段，其余字段为NULL。但是HCatalog不同，源表和目标表字段名需要相同，字段个数可以不相等，如果字段名不同，抽取数据的时候会报NullPointerException错误。HCatalog抽取数据时，会将字段对应到相同字段名的字段上，哪怕字段个数不相等。