使用Sqoop将Mysql数据导入Hive踩坑

背景：
由于公司这个项目存在阿里云上，不能由DBA来维护，所以想要把数据从Mysql抽取到Hive上就需要我们自己来动手。。。
数据量不是很大，但是分了巨多的库；需要导入的只有7张表，但是大概有94个库，每个库下边都有这七张表。
初步设计方案是在把Mysql不同数据库的数据存放在Hive表不同的分区，就是一张hive表大概有90多个分区。

最初想法：
1.先用sqoop复制表结构；
2.循环数据库添加分区并向分区中导入数据。
命令：
##sqoop复制表结构
sqoop create-hive-table
-D mapred.job.queue.name=root.line.ploan
–connect jdbc:mysql://$host:$port/$database
–username $user
–password $password
–table $tablename
–hive-database $hive_database
–hive-table $tablename
–hive-partition-key db
–fields-terminated-by “\t”
–lines-terminated-by “\n”

##Hive添加分区
hive -e ’
use sirrus;
elter table $tablenameaddpartition(db{=}^{\prime }$database’);
exit;
’
##向分区插入数据
sqoop import
-D mapred.job.queue.name=root.line.ploan \ ##设置MR执行队列
–connect jdbc:mysql://$host:$port/$database \ ##设置Mysql连接
–username $user \ ##设置Mysql登陆用户
–password $password \ ##设置Mysq登陆l密码
–table $tablename \ ##要导入HIVE的Mysql表名
–fields-terminated-by “\t” \ ##导入HDFS后的列分割符
–delete-target-dir \ ##如果目标目录已存在，则先删除
–hive-import \ ##指定导入到Hive中
–m 1 \ ##设定启动Mapper个数
–hive-partition-key db \ ##指定导入Hive的分区字段
–hive-partition-value $database \ ##指定导入Hive的哪个分区
–hive-database sirrus \ ##指定导入Hive表所在的库
–hive-table $tablename ##指定要导入的Hive表

问题：

再看发生的问题之前大家先看一段代码：

##Hive处理数据库类型的方法
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;  
 }  

}

1.直接运行上面sqoop命令，查询，失败。。。。（类型自动转化） sqoop复制的Mysql表结构到HIVE，发现部分字段类型发生了变化；

tinyint(1) -> boolean
decimal -> double
datetime -> string 等

由上面代码可以知道，hive将Mysql中的数据类型进行了转化：
integer、smallint -> int
varchar、char、longvarchar、nvarchar、nchar、longnvarchar、date、time、timestamp、clob -> string;
numeric、decimal、float、double、real -> double;
bit、boolean -> boolean;
tinyint -> tinyint;
bigint -> bigint.
如果包含decimal数据类型可能会失精度，所以不建议使用sqoop自动创建Hive表（如果包含上面几种字段的话）

2.先在Hive按照Mysql字段类型创建好表，不自动建表，执行，数据导完，查询（tinyint（1）为boolean类型）
发现有列是null。。。什么鬼，排查发现这几列是tinyint（1）类型，明明是tinyint啊，hive上也是tinyint啊，怎么肥四？？
查阅资料发现
MySQL保存boolean值时用1代表TRUE，0代表FALSE。boolean在mysql里的类型为tinyint(1)。

果然书到用时方恨少。。
两种解决方案：
将 --connect jdbc:mysql://$host:$port/$database修改为--connectjdbc:mysql://$host:$port/$database?tinyInt1isBit=false
–map-column-hive ${列名}=tinyint
3.特殊字符处理
这下总可以了吧，修改好继续执行，检查又发现一个表里莫名其妙多出一条数据,而且有的列没有数据。。。
原因：
设置的Hive列分割符为 \t，行分割符为\n，而mysql中有一条数据存在 \t,\n
解决方案：
设置hive列分割符为\001,但是行分割符仅支持\n,所以需要添加
–hive-drop-import-delims \ ##处理特殊字符

4.导到最后一张表的时候突然抱错（不支持类型处理）。
ERROR org.apache.sqoop.tool.ImportTool: Encountered IOException running import job: java.io.IOException: [color=darkred]Hive does not support the SQL type for column VARBINARY[/color]
比较简单，从上面的java代码就知道了：
HIVE做类型转换的时候，没有写VARBINARY类型的处理
只需做一下映射即可
解决方案：
–map-column-hive ${列名}=STRING

5.Hive空值处理
终于ok，555.。。。hive查询验证一下所有表，完美没问题
impala查询测试。结果正确。但是报错。。。

Error converting column: 12 to DECIMAL(10,2)
Error converting column: 13 to TIMESTAMP

查询hdfs文件发现，对应列的位置存储的是 NULL 字符串。但是字段类型为decimal和timestamp不匹配所以显示null，不能显示NULL字符串，虽然结果正确，数据却是不正确的。
解决方案：对Hive进行空值处理
–null-string ‘\N’
–null-non-string ‘\N’ \
记住是’\N’ . 不是’\N’ 原因应该都知道。

重新导入，终于DONE。。。