关系型数据库到Hadoop数据迁移 —sqoop批量数据导入
技术战略发展部
张延凤1
1.Email: zhangyanfeng@https://www.doczj.com/doc/5c2990708.html,
目录
1. 目的 (4)
2. sqoop版本及安装 (4)
2.1 sqoop版本及安装 (4)
2.2 sqoop安装 (4)
3. sqoop导入到hive (5)
3.1 hive简单介绍 (5)
3.2 hive metadata DB更换成mysql (5)
3.3 hive metadata DB更换成mysql (6)
3.4 增量导入示例 (7)
3.5 验证导入到Hive中的数据 (7)
3.6 Hive查询出来的结果导入到HDFS指定目录下 (8)
3.7 分隔符的故事 (8)
3.8 问题总结 (9)
4. sqoop导入数据到HDFS (9)
4.1 导入示例 (9)
4.2 增量导入示例 (9)
5. Sqoop导入数据到HBase (10)
5.1 HBase简单介绍 (10)
5.2 导入示例 (10)
5.3 增量导入示例 (10)
5.4 问题总结 (11)
6. Hive和HBase整合 (11)
6.1 为什么需要整合Hive和HBase (11)
6.2 整合步骤 (11)
6.3 问题总结 (12)
1.目的
sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如 ： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop中，也可以将hadoop中的数据转移到关系型数据库中。下文中我们将以iSpace存储在
MySQL中的生产数据导入到hadoop为例性进行说明。
本质上sqoop是一个hadoop的一个jobClient，负责定义hadoop job，然后将job 提交到hadoop集群，只不过这个jobClient为了支持了能通过命令行来配置各种各样的job，做了很多处理。
2.sqoop版本及安装
2.1sqoop版本及安装
我们使用的hadoop版本是Cloudera 的Hadoop 2.0.0-cdh4.0.0 ，sqoop的版本是
1.4.1-cdh4.0.0。
Sqoop user guide 地址：
https://www.doczj.com/doc/5c2990708.html,/cdh4/cdh/4/sqoop/SqoopUserGuide.html
如果在apache上的hadoop安装sqoop，请留意版本问题。download以后，直接解压即可
2.2sqoop安装
sqoop安装依赖于hadoop，我们在CDH4环境下对sqoop进行安装，关于CDH4的安装不是本文介绍的重点，此处从略。
我们在ubuntu 10.04 64位上安装了CDH4，选择集群中的一个节点，执行sudo apt-
get install sqoop，sqoop默认安装路径是 /usr/lib/sqoop,执行一下 sqoop version
命令，显示sqoop 1.4.1-cdh4.0.0 ，即sqoop安装成功。
ispace 生产数据存储在mysql中，所以我们还需要把mysql 驱动mysql-connector-
5.1.8.jar，下载复制到sqoop下的lib文件夹中。
在/usr/lib/sqoop/bin 目录下，执行，
sqoop list-databases --connect jdbc:mysql:// 192.168.161.121:3306 --username root –P
结果会列出所连接到的mysql中所有database。下面我们就可以利用sqoop批量导入
数据了。
3.sqoop导入到hive
3.1hive简单介绍
hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据
库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。
其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必
开发专门的MapReduce应用，十分适合数据仓库的统计分析。特别提醒hive8.0以前
只有全量插入，我们安装的是hive-0.8.1+59，已经支持增量插入。
Hive不支持一条一条的用insert语句进行插入操作，也不支持update的操作。数据是
以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个
表，要么建立新的表，导入新的数据。
3.2hive metadata DB更换成mysql
Hive默认是采用Derby来存储其Metadata信息的，由于derby是个文件数据库，所
以无法同时启动多个Hive进程，无法提供多用户访问，这样的体验是非常糟糕的。我
们把metadata DB更换为为mysql。修改hive的配置文件hive-site.xml
3.3hive metadata DB更换成mysql
在hive中建立mysql相应的hive table
sqoop create-hive-table --connect jdbc:mysql://10.1.202.99:3306/t
--table message --hive-table message --username root -P
把mysql中数据导入到hive中已建立好的hive table中
sqoop import --connect jdbc:mysql://10.1.202.99:3306/t
--table user --hive-import --username root -P
3.4增量导入示例
把mysql中数据增量导入到hive中已建立好的hive table中
sqoop import --connect jdbc:mysql://10.1.202.99:3306/t
--incremental append --table notice --hive-import --hive-table notice
--check-column modified --last-value '2012-06-27 00:00:00'
--username root -P
--incremental append 定义为增量导入，需要与--check-column 和--last-value 配合使用。
--check-column 定义增量指定的判断字段。
--last-value 定义上次导入到hive中 指定字段中最后的一个值，此次导入会从此值之后进行导入。
3.5验证导入到Hive中的数据
Hive提供了一个类SQL的查询语言HQL来执行MapReduce job，所以hive对实时数据不支持。进入hive的shell，可以对导入的数据进行查询，若你已经安装了hue，hue提供了一个更高级的hive用户接口，使得用户可以提交查询并且监控MapReduce job的执行。如下图所示：
3.6Hive查询出来的结果导入到HDFS指定目录下
INSERT OVERWRITE DIRECTORY '/tmp/hdfs_notice_out ' SELECT content FROM
notice a WHERE id<10;
3.7分隔符的故事
Mysql中string中含有大量字符，会跟hive的默认分隔符进行匹配，导致数据查询不出来，针对此情况可以把一些字符 默认不导入到hive中，其中包括\n, \r, and \01，示例：
sqoop import --connect jdbc:mysql://10.1.202.99:3306/t
--table notice --hive-import --hive-table notice
--hive-drop-import-delims --username root -P
--hive-drop-import-delims 定义不导入字段中含有的特殊字符包括\n, \r, and \01。
导入数据在HDFS中存储，默认分隔符可读性比较差，我们可以指定分隔符
sqoop import --connect jdbc:mysql://192.168.161.121:3306/t
--table notice --hive-import --hive-table notice
--hive-drop-import-delims --fields-terminated-by \,
--username root –P
--hive-drop-import-delims 定义不导入字段中含有的特殊字符包括\n, \r, and \01。
--field-terminated-by 自定义导入到HDFS中字段的分隔符。
3.8问题总结
1.Hive在数据导出这块，我们测试出了一个bug，查血结果导出到HDFS中，目前不支持指定输出的分割符，只能用默认的，ctrl-A是导出结果唯一指定的分隔符。
2.Hive 不支持二进制格式的数据的处理。
4.sqoop导入数据到HDFS
4.1 导入示例
把mysql中数据导入到HDFS指定目录下
sqoop import --connect jdbc:mysql://10.1.202.99:3306/t
--target-dir /testSqoop/out -m 4 --split-by user_id --table inbox
--username root -P
--targer-dir 定义导入到HDFS中目录。
-m 指定map的个数，根据集群的规模设定
此处注意 --target-dir 必须指定一个未建立的目录，如果目录已经存在则会报错。如果想往一个已经存在的目录下存储文件，只能采取增量的形式进行导入，其根本原因也是HDFS 不支持修改造成的。
4.2 增量导入示例
把mysql中数据增量导入到HDFS指定目录下
sqoop import --append --connect jdbc:mysql://10.1.202.99:3306/t
--target-dir testSqoop/out -m 4 --split-by id -
-table notice --where "id>18000"
--username root -P
--targer-dir 定义导入到HDFS中目录。
--where 定义增量导入的条件
5.Sqoop导入数据到HBase
5.1 HBase简单介绍
HBase – Hadoop Database，是一个高可靠性、高性能、面向列、可伸缩的分布式存储系统，利用HBase技术可在廉价PC Server上搭建起大规模结构化存储集群。
5.2导入示例
把mysql中的数据导入到HBase数据库中
sqoop import --connect jdbc:mysql://192.168.161.121:3306/t
--table notice --hbase-table notice --column-family cf_notice
--hbase-row-key notice_id --hbase-create-table
--username root -P
--column-family 定义导入到hbase中的表的列明。--hbase-row-key 定义导入到hbase 中的表的row key，需要唯一 性，因此指定表在mysql中的主键，暂不支持复合主键。
5.3 增量导入示例
把mysql中数据增量导入到HBase数据库中
sqoop import --connect jdbc:mysql://10.1.202.99:3306/t --query
"SELECT * FROM notice n where n.id between 1 AND 100 AND \$CONDITIONS"
--hbase-table notice --column-family cf_notice_01
--hbase-row-key id --split-by id -m 3
--username root -P
--query 定义增量导入的查询条件。
5.4问题总结
Sqoop导入数据到HBase中，对于--hbase-row-key 指定mysql中table的主键字段，sqoop不支持复合主键，修改sqoop源码可以让sqoop支持对mysql中拥有复合主键的
table，导入到HBase中。
6.Hive和HBase整合
6.1为什么需要整合Hive和HBase
HBase是基于列存储的数据库，但由于HBase没有类sql查询方式,所以操作和计算数据
非常不方便。另外，hive是基于HDFS建立的数据仓库，为了提高统计效率，我们可以把数
据存储在HBase中，让hive建立基于HBase的数据仓库。
6.2整合步骤
修改hive 的配置文件hive-site.xml增加如下配置
cdh4.0.0.jar,file:///usr/lib/hive/lib/hbase.jar,file:///usr/lib/hive/lib/zookeeper.jar
对于HBase中已有的table，可以建立一个映射表，让hive能查询到HBase中的数据。
CREATE EXTERNAL TABLE apr_log_link_visit_action(value map
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = "rf_apr_log_link_visit_action:,:key");
6.3问题总结
整合后HBase中的数据可以通过hive本身提供的shell进行查询，暂时无法通过hue封
装的hive接口进行查询。原因是在hue中无法配置它封装的hive所使用的zookeeper的信
息。因为Hue的配置文件都存储在二进制数据库中，暂时我们没有对此处进行修改。