Sqoop1.4.7实现将Mysql数据与Hadoop3.0数据互相抽取
Sqoop1.4.7实现将Mysql数据与Hadoop3.0数据互相抽取
每一个成功人士的背后，必定曾经做出过勇敢而又孤独的决定。
放弃不难，但坚持很酷~
本文主要讲解 Sqoop 1.4.7 如何实现 Mysql 与 Hadoop 3.0 之间数据互相抽取的。 环境基于 Ambari 2.7 + HDP 3.0 部署。 之前写过一篇 Sqoop 1.4.6 如何实现 Mysql 与 Hadoop 2.x 之间数据互相抽取的，可参考：《Sqoop概述及shell操作》
一、Sqoop Shell操作
参数 | 描述 |
|---|---|
--connect <jdbc-uri> | 指定JDBC连接字符串 |
--username | 指定连接mysql用户名 |
--password | 指定连接mysql密码 |
1. 将Mysql数据导入到Hadoop中
1.1 数据导入到HDFS
参数 | 描述 |
|---|---|
table <table name> | 抽取mysql数据库中的表 |
--target-dir <path> | 指定导入hdfs的具体位置。默认生成在为/user/<user>//目录下 |
-m <数值> | 执行map任务的个数，默认是4个 |
将 mysql 数据库中的 hive 数据库中的 ROLES 表数据导入到 HDFS 中的 /tmp/root/111 目录下。执行代码如下：
sqoop import \
--connect jdbc:mysql://10.6.6.72:3309/hive \
--username root \
--password root123 \
--table ROLES \
--target-dir /tmp/root/111 \
--fields-terminated-by ',' \
-m 1
备注：-m 参数可以指定 map 任务的个数，默认是 4 个。如果指定为 1 个 map 任务的话，最终生成的 part-m-xxxxx 文件个数就为 1。在数据充足的情况下，生成的文件个数与指定 map 任务的个数是等值的。
1.2 数据导入到Hive中
参数 | 描述 |
|---|---|
--hive-import | 将表导入Hive中 |
--hive-table <table name> | 指定导入Hive的表名 |
--fields-terminated-by <char> | 指定导入到hive中的文件数据格式 |
-m <数值> | 执行map任务的个数，默认是4个 |
将 mysql 数据库中的 hive 数据库中的 ROLES 表数据导入到 Hive 数据库中，并生成 roles_test 表。执行代码如下：
sqoop import \
--connect jdbc:mysql://10.6.6.72:3309/hive \
--username root \
--password root123 \
--hive-import \
--table ROLES \
--hive-database default \
--hive-table roles_test \
--fields-terminated-by ',' \
-m 1
备注：-m 参数可以指定 map 任务的个数，默认是 4 个。如果指定为 1 个 map 任务的话，最终生成在 /warehouse/tablespace/managed/hive/roles_test/base_xxxx 目录下的 000000_x 文件个数就为 1 。在数据充足的情况下，生成的文件个数与指定 map 任务的个数是等值的。
提示：如果该步骤失败，可查看 FAQ 里面的 1 与 2 。
执行数据导入过程中，会触发 MapReduce 任务。任务执行成功以后，我们访问 Hive 验证一下数据是否导入成功。
hive> show tables;
OK
roles_test
hive> select * from roles_test;
OK
1 1545355484 admin admin
2 1545355484 public public
Time taken: 0.536 seconds, Fetched: 2 row(s)
数据导入成功。
1.3 数据导入到HBase中
参数 | 描述 |
|---|---|
--column-family <family> | 设置导入的目标列族 |
--hbase-row-key <col> | 指定要用作行键的输入列；如果没有该参数，默认为mysql表的主键 |
--hbase-create-table | 如果执行，则创建缺少的HBase表 |
--hbase-bulkload | 启用批量加载 |
将 mysql 数据库中的 hive 数据库中的 roles 表数据导入到 HBase 中，并生成 roles_test 表。执行代码如下：
sqoop import \
--connect jdbc:mysql://10.6.6.72:3309/hive \
--username root \
--password root123 \
--table ROLES \
--hbase-table roles_test \
--column-family info \
--hbase-row-key ROLE_ID \
--hbase-create-table \
--hbase-bulkload
关于参数--hbase-bulkload的解释：
实现将数据批量的导入Hbase数据库中，BulkLoad特性能够利用MR计算框架将源数据直接生成内部的HFile格式，直接将数据快速的load到HBase中。
细心的你可能会发现，使用--hbase-bulkload参数会触发MapReduce的reduce任务。
执行数据导入过程中，会触发 MapReduce 任务。任务执行成功以后，我们访问 HBase 验证一下数据是否导入成功。
hbase(main):002:0> list
TABLE
roles_test
1 row(s) in 0.1030 seconds
=> ["roles_test"]
hbase(main):003:0> scan "roles_test"
ROW COLUMN+CELL
1 column=info:CREATE_TIME, timestamp=1548319280991, value=1545355484
1 column=info:OWNER_NAME, timestamp=1548319280991, value=admin
1 column=info:ROLE_NAME, timestamp=1548319280991, value=admin
2 column=info:CREATE_TIME, timestamp=1548319282888, value=1545355484
2 column=info:OWNER_NAME, timestamp=1548319282888, value=public
2 column=info:ROLE_NAME, timestamp=1548319282888, value=public
2 row(s) in 0.0670 seconds
总结：roles_test 表的 row_key 是源表的主键 ROLE_ID 值，其余列均放入了 info 这个列族中。
2. 将Hadoop数据导出到Mysql中
Sqoop export 工具将一组文件从 HDFS 导出回 Mysql 。目标表必须已存在于数据库中。根据用户指定的分隔符读取输入文件并将其解析为一组记录。
默认操作是将这些转换为一组INSERT将记录注入数据库的语句。在“更新模式”中，Sqoop 将生成 UPDATE 替换数据库中现有记录的语句，并且在“调用模式”下，Sqoop 将为每条记录进行存储过程调用。
将 HDFS、Hive、HBase的数据导出到 Mysql 表中，都会用到下表的参数：
参数 | 描述 |
|---|---|
--table <table name> | 指定要导出的mysql目标表 |
--export-dir <path> | 指定要导出的hdfs路径 |
--input-fields-terminated-by <char> | 指定输入字段分隔符 |
-m <数值> | 执行map任务的个数，默认是4个 |
2.1 HDFS数据导出至Mysql
首先在 test 数据库中创建 roles_hdfs 数据表：
USE test;
CREATE TABLE `roles_hdfs` (
`ROLE_ID` bigint(20) NOT NULL ,
`CREATE_TIME` int(11) NOT NULL ,
`OWNER_NAME` varchar(128) DEFAULT NULL ,
`ROLE_NAME` varchar(128) DEFAULT NULL ,
PRIMARY KEY (`ROLE_ID`)
)
将HDFS上的数据导出到mysql的test数据库的roles_hdfs表中，执行代码如下：
sqoop export \
--connect jdbc:mysql://10.6.6.72:3309/test \
--username root \
--password root123 \
--table roles_hdfs \
--export-dir /tmp/root/111 \
--input-fields-terminated-by ',' \
-m 1
执行数据导入过程中，会触发 MapReduce 任务。任务成功之后，前往 mysql 数据库查看是否导入成功。
2.2 Hive数据导出至Mysql
首先在test数据库中创建roles_hive数据表：
CREATE TABLE `roles_hive` (
`ROLE_ID` bigint(20) NOT NULL ,
`CREATE_TIME` int(11) NOT NULL ,
`OWNER_NAME` varchar(128) DEFAULT NULL ,
`ROLE_NAME` varchar(128) DEFAULT NULL ,
PRIMARY KEY (`ROLE_ID`)
)
由于Hive数据存储在 HDFS 上，所以从根本上还是将 HDFS 上的文件导出到 mysql 的 test 数据库的 roles_hive 表中，执行代码如下：
sqoop export \
--connect jdbc:mysql://10.6.6.72:3309/test \
--username root \
--password root123 \
--table roles_hive \
--export-dir /warehouse/tablespace/managed/hive/roles_test/base_0000001 \
--input-fields-terminated-by ',' \
-m 1
2.3 HBase数据导出至Mysql
目前 Sqoop 不支持从 HBase 直接导出到关系型数据库。可以使用 Hive 周转一下。
2.3.1 创建hive外部表
create external table hive_hbase(id int,CREATE_TIME string,OWNER_NAME string,ROLE_NAME string)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping" = ":key,info:CREATE_TIME,info:OWNER_NAME,info:ROLE_NAME")
tblproperties("hbase.table.name" = "roles_test");
2.3.2 创建Hive内部表
创建适配于 Hive 外部表的内部表：
create table if not exists hive_export(id int, CREATE_TIME string, OWNER_NAME string, ROLE_NAME string)
row format delimited fields terminated by ',' stored as textfile;
hive_hbase 外部表的源是 HBase 表数据，当创建适配于 hive_hbase 外部表的 Hive 内部表时，指定行的格式为 “,” 。
2.3.3 将外部表的数据导入到内部表中
insert overwrite table hive_export
select * from hive_hbase;
备注：如果该步骤报错，可查看 FAQ 的 3 。
2.3.4 创建Mysql表
CREATE TABLE `roles_hbase` (
`id` bigint(20) NOT NULL,
` create_time` varchar(128) NOT NULL ,
` owner_name` varchar(128) DEFAULT NULL ,
` role_name` varchar(128) DEFAULT NULL ,
PRIMARY KEY (`id`)
)
2.3.5 执行sqoop export
sqoop export \
--connect jdbc:mysql://10.6.6.72:3309/test \
--username root \
--password root123 \
--table roles_hbase \
--export-dir /warehouse/tablespace/managed/hive/hive_export/base_0000003 \
--input-fields-terminated-by ',' \
-m 1
查看 mysql 中的 roles_hbase 表，数据成功被导入。
备注：在创建表的时候，一定要注意表字段的类型，如果指定表类型不一致，有可能会报错。
3. 总结
使用 Sqoop import / export 命令，可以实现将关系型数据库中的数据与 Hadoop 中的数据进行相互转化，其中一些转化的细节，可以指定参数实现。在执行过程中，sqoop shell 操作会转化为 MapReduce 任务来实现数据的抽取。
更多的sqoop操作，详情请参见：
http://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html
二、FAQ
1. Sqoop将Mysql数据导入到Hive中，出现类似卡住的现象
问题描述：
如下图所示：
问题分析：
在 Hive 3 的版本中，进入 hive 命令行需要执行输入用户名和密码。猜测流程被卡住的原因正是缺少用户名和密码的输入。
解决办法：
编辑所在主机的beeline-site.xml文件，执行如下命令：
vim /etc/hive/conf/beeline-site.xml
在 beeline.hs2.jdbc.url.container 配置值末尾增加登陆 hive 的用户名和密码，比如：user=hive;password=hive，如下图所示：
保存修改后，无需重启Hive服务，直接生效。此时则可以再次执行Sqoop相关命令进行尝试。
参考链接：https://community.hortonworks.com/questions/214980/sqoop-import-hung-hive-import-hdp-300.html
2. ERROR tool.ImportTool: Import failed: java.io.IOException: Hive exited with status 2
问题描述：
执行 Sqoop 命令将 Mysql 数据导入 Hive 过程中，出现错误，错误信息如下图所示：
问题分析：
程序在进入Hive以后报错，怀疑Sqoop将数据插入目标表中报错，有可能为用户权限问题。
解决办法：
将执行Sqoop shell的用户切换为hive用户，执行如下命令：
su -hive
3. 查询hive外部表数据并将查询结果插入到hive内部表失败
问题描述：
查询hive外部表数据并将查询结果插入到hive内部表失败，出现KeeperErrorCode = NoNode for /hbase/meta-region-server的错误，如下图所示：
问题分析：
经过分析报错，发现提示找不到/hbase/meta-region-server这个zookeeper节点。HBase的zookeeper.znode.parent属性值为/hbase-unsecure，自然找不到/hbase/meta-region-server节点而报错。
解决方法：
整体思路就是添加 zookeeper.znode.parent 到 Hive 配置中。
- 方法一（临时）：
su – hive
hive -hiveconf zookeeper.znode.parent=/hbase-unsecure
- 方法二（永久）：
打开管理系统的Hive配置页面，点击 “高级配置 > 自定义hive-site”，添加zookeeper.znode.parent属性，添加后如下图所示：
修改后保存配置，并重启 Hive 服务。