关系型数据库到hadoop数据迁移

1、关系型数据库到Hadoop数据迁移sqoop批量数据导入技术战略发展部张延凤1Email:zha ngya nfengn Neusoft目录1. 目的4.2. sqoop版本及安装42.1 sqoop版本及安装42.2 sqoop 安装4.3. sqoop导入至U hive53.1 hive简单介绍5.3.2 hive metadata DB更换成mysql 53.3 hive metadata DB更换成mysql 63.4 增量导入示例.7.3.5 验证导入到 Hive中的数据73.6 Hive查询出来的结果导入到HDFS指定目录下83.7 分隔符的故事.8.3.8 问题总结94. sqo

2、op导入数据到HDFS94.1 导入示例9.4.2 增量导入示例.9.5. Sqoop导入数据至UHBase105.1 HBase简单介绍1.05.2 导入示例105.3 增量导入示例.05.4 问题总结1.16. Hive 和 HBase 整合1.16.1 为什么需要整合Hive和HBase 116.2 整合步骤1.1问题总结12Neusofti. 目的sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具，可以将一个 关系型数据库（例如：MySQLOracle ,Post等中的数据导进到 Hadoop中，也可以将hadoop中的数据转移到关系型数据库中。下文中我们将以iSp

3、ace存储在MySQL中的生产数据导入到hadoop为例性进行说明。本质上sqoop是一个hadoop 的一个jobClient，负责定义adoop job，然后将ob提交到hadoop集群，只不过这个jobClient为了支持了能通过命令行来配置各种各样 的job，做了很多处理。1. sqoop版本及安装1.1 sqoop版本及安装我们使用的 hadoop 版本是 Cloudera Hadoop 2.0.0-cdh4.0.0，的:版:本是1.4.1-cdh4.0.0。Sqoop user guide地址：http:/archive.cloudera.eom/cdh4/cdh/4/sqoop/

4、SqoopUserGuide.html如果在apache上的hadoop安装sqoop，请留意版本问题。down以后，直接解压即可1.2 sqoop 安装sqoop安装依赖于hadoop ，我们在CDH4环境下对sqoop进行安装，关于 CDH4的 安装不是本文介绍的重点，此处从略。我们在ubuntu 10.04 64 位上安装了 CDH4，选择集群中的一个节点，执行 sudo即t-get in stall sqoop,默qOc安装路径是/usr/lib/sqoop,执行一下 sqoop version命令，显示sqoop 1.4.1-cdh4.0.0sqQO|即卩安装成功。ispace生产数

5、据存储在mysql中，所以我们还需要把 mysql 驱动nysql-connector-5.1.8.jar，下载复制到oop下的lib文件夹中。在 /usr/lib/sqoop/bin目录下，执行，sqoop list-databases-connect jdbc:mysql: 21:3306 -username root- P结果会列出所连接到的 mysql中所有database。下面我们就可以利用sqoop批量导入数据了。2. sqoop 导入至U hive2.1 hive简单介绍hive 是基于 Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数

6、据 库表，并提供完整的sql查询功能，可以将 sql语句转换为MapReduce 任务进行运行。 其优点是学习成本低，可以通过类SQL语句快速实现简单的 MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。特别提醒hive8.0以前只有全量插入，我们安装的是hive-0.8.1+59 ，已经支持增量插入。Hive不支持一条一条的用insert语句进行插入操作，也不支持 update的操作。数据是 以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。2.2 hive metadata DB 更换

7、成 mysqlHive默认是采用Derby来存储其Metadata信息的，由于derby是个文件数据库，所 以无法同时启动多个 Hive进程，无法提供多用户访问，这样的体验是非常糟糕的。我 们把metadata DB 更换为为 mysql。修改hive的配置文件 hive-site.xmlvproperty><name> javax.jdo.option.ConnectionURL</name></value><Value>jdbc:mysql:/9:3306/hive?ceateDatabaselfNotExist二t

8、rue<description>JDBC connect string for a JDBC metastore</description></property><property><name> javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value><description>Driver class name for a JDBC metastore</description&

9、gt;</property><property><name> javax.jdo.option.ConnectionUserName</name><value>hadoop</value><description>username to use against metastore database</description></property><property><name> javax.jdo.option.ConnectionPassword</nam

10、e><value>123456</value><description>password to use against metastore database</description> </property>2.3 hive metadata DB 更换成 mysql在hive中建立 mysql相应的hive tablesqoop create-hive-table-connect jdbc:mysql:/9:3306/t -table message -hive-table message -usernam

11、e root -PNeusoft把mysql中数据导入到hive中已建立好的hive table 中sqoop import -connect jdbc:mysql:/9:3306/t-table user-hive-import-username root -P2.4 增量导入示例把mysql中数据增量导入到 hive中已建立好的hive table 中sqoop import -connect jdbc:mysql:/9:3306/t-incremental append-table notice -hive-import -hive-table n

12、otice-check-column modified -last-value '2012-06-27 00:00:00'-username root -P-i ncreme ntal appe nd定义为增量导入,-check-colu mn禾口 -last-value配用。-check-colu mn定义增量指定的判断字段。-last-value定义上次导入到hive中指定字段中最后的一个值，此次导入会从此值之后进行导入。2.5 验证导入到Hive中的数据Hive提供了一个类SQL的查询语言HQL来执行MapReduce job，所以ive对实时数据不支持。进入hive的s

13、hell，可以对导入的数据进行查询，若你已经安装了 hue， hue提供了一个更高级的hive用户接口，使得用户可以提交查询并且监控MapReduce job 的执行。如下图所示：NeusoftNeusoftNeusoftNeusoft2.6 Hive查询出来的结果导入到 HDFS指定目录下INSERT OVERWRITE DIRECTORY 7tmp/hdfs_notice_out ' SELECT content FROM notice a WHERE id<10;2.7 分隔符的故事Mysql中string中含有大量字符，会跟 hive的默认分隔符进行匹配，导致数据查询不出

14、 来，针对此情况可以把一些字符默认不导入到其中包括n, r, and 01 ，示例:sqoop import -connect jdbc:mysql:/9:3306/t-table notice-hive-import -hive-table notice-hive-drop-import-delims-username root -P-hive-drop-import-delims定义不导入字段中含有的特殊字符包括and 01。导入数据在HDFS中存储，默认分隔符可读性比较差，我们可以指定分隔符sqoop import -connect jdbc:mysql:/192.1

15、68.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定义不导入字段中含有的特殊字符包括and 01 o-field-termi nated-by自定义导入至中字段的分隔符。2.8 问题总结I.Hive在数据导出这块，我们测试出了一个bug，查血结果导出到HDFS中，目前不支持指定输出的分割符，只能用默认的，是导出结果唯一指定的分隔符。2.Hiv

16、e不支持二进制格式的数据的处理。3. sqoop 导入数据到 HDFS3.1 导入示例把mysql中数据导入到HDFS指定目录下sqoop import -connect jdbc:mysql:/9:3306/t-target-dir /testSqoop/out-m 4 -split-by user_id -table inbox-username root -P-targer-dir定义导入到 中目录。-m指定map的个数，根据集群的规模设定此处注意 -target-dir必须指定一个未建立的目录，如果目录已经存在则会报错。如果想往一个已经存在的目录下存储文件，只能采取

17、增量的形式进行导入，其根本原因也是HDFS不支持修改造成的。3.2 增量导入示例把mysql中数据增量导入到HDFS指定目录下sqoop import -append -connect jdbc:mysql:/9:3306/t-target-dir testSqoop/out-m 4 -split-by id -table notice-where "id>18000"-username root -P-targer-dir定义导入到中目录。-where定义增量导入的条件4. Sqoop 导入数据到 HBase5.1HBase简单介绍HBaseHa

18、doop Database，是一个咼可靠性、咼性能、面向列、可伸缩的分布式存储系统，利用HBase技术可在廉价PC Server上搭建起大规模结构化存储集群。5.2 导入示例把mysql中的数据导入到HBase数据库中sqoop import -connect jdbc:mysql:/21:3306/t-table notice -hbase-table notice-column-family cf_notice-hbase-row-key notice_id-hbase-create-table-username root -P-column-family定义导入

19、到e中的表的列明。-hbase-row-key定义导入到中的表的row key，需要唯一性，因此指定表在中的主键，暂不支持复合主键。5.3 增量导入示例把mysql中数据增量导入到HBase数据库中sqoop import -connect jdbc:mysql:/9: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 i

20、d -split-by id -m 3-username root -PNeusoft-query定义增量导入的查询条件。5.4 问题总结Sqoop 导入数据到 HBase中，对于 -hbase-row-key 指定mysql中table的主键字段, sqoop不支持复合主键，修改 sqoop源码可以让sqoop支持对mysql中拥有复合主键的 table ，导入至HBase中。5. Hive 和 HBase 整合5.1 为什么需要整合 Hive 和HBaseHBase是基于列存储的数据库，但由于 HBase没有类sql查询方式，所以操作和计算数据非常不方便。另外，是基于HDFS建立的数据仓库

21、，为了提高统计效率，我们可以把数据存储在HBase中，让hive建立基于HBase的数据仓库。5.2 整合步骤修改hive的配置文件hive-site.xml增加如下配置<property><name> hive.querylog .lo cation</name><value>/usr/lib/hive/logs</value></property><property><name> hive.aux.jars.path</name><value>file:/usr/lib/hive/lib/hive-hbase-handler-0.8.1-cdh4.0.0.jar,file:/usr/lib/hive/lib/hbase.j