Sqoop1 安装及Hive/Hbase数据与Mysql数据互导（九）

最新推荐文章于 2024-08-12 03:04:15 发布

在屋顶听歌

最新推荐文章于 2024-08-12 03:04:15 发布

阅读量1.2k

点赞数

分类专栏： sqoop 企业级HDP平台的搭建 HDP 平台的运用文章标签： sqoop

本文链接：https://blog.csdn.net/u013850277/article/details/77103172

版权

HDP 平台的运用同时被 3 个专栏收录

20 篇文章 3 订阅

订阅专栏

企业级HDP平台的搭建

14 篇文章 10 订阅

订阅专栏

sqoop

2 篇文章 0 订阅

订阅专栏

一、通过 Ambari 安装 Sqoop

通过ambari 进行自动安装，ambari 只安装了sqoop client。
网上有说如果要调用 sqoop api 来操作sqoop 则需要安装sqoop server 。目前笔者还没用到其api，所以后面有待深究。

安装步骤：
ambari–> Actions –> sqoop 进入安装，安装完后无需要进行任何配置便可。
安装完成后如下图所示：

这里写图片描述

二、验证 sqoop 安装成果

测试sqoop连接mysql数据库的情况

1、list-databases 查看所有库

sqoop list-databases --connect jdbc:mysql://hdp12:3306/test --username test --password test-123

结果如下：

information_schema
mysql
performance_schema
smartbi
sys
test
.....

2、list-tables 列出库中所有表

sqoop list-tables --connect jdbc:mysql://hdp12:3306/test --username test --password test-123 
17/08/10 14:24:20 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
t_map_gd
t_map_gz
t_map_gz_02
t_pop1
t_pop_density_gz
t_pop_num
t_sqoop_phone

三、将hive 中的数据导出到 mysql中

 全量导出到 mysql表（该表应事先创建好）

Hive 对应的表结构如下所示：

这里写图片描述

之前创建的mysql 表结构如下：

mysql> desc t_sqoop_phone;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(255) | YES  |     | NULL    |       |
| code  | varchar(255) | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| price | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

如果不知道hive表实际存储路径, 个人建议可通过hive的元数据SDS表查看将要导出的hive表所对应的具体目录

进入 Hive 元数据库，笔者将mysql 做为Hive的元数据存储库
mysql> select * from SDS;

... hdfs://hdp12.data.com.cn:8020/apps/hive/warehouse/test.db/rphone

准备动作完成，执行Hive数据导出到mysql 命令

sqoop export --connect jdbc:mysql://hdp12:3306/test --username test --password test-123 --table t_sqoop_phone --export-dir /apps/hive/warehouse/test.db/rphone

运行结果如下所示：

[root@hdp13 keytabs]# sqoop export --connect jdbc:mysql://hdp12:3306/test --username test --password test-123 --table t_sqoop_phone --export-dir /apps/hive/warehouse/test.db/rphone
Warning: /usr/hdp/2.5.3.0-37/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.5.3.0-37/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/08/10 15:03:58 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.3.0-37
17/08/10 15:03:58 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/08/10 15:03:58 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/08/10 15:03:58 INFO tool.CodeGenTool: Beginning code generation
17/08/10 15:03:58 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `t_sqoop_phone` AS t LIMIT 1
17/08/10 15:03:58 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `t_sqoop_phone` AS t LIMIT 1
17/08/10 15:03:58 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hdp/2.5.3.0-37/hadoop-mapreduce
Note: /tmp/sqoop-root/compile/272c2d528f7bf6b36d8fa0eedec95792/t_sqoop_phone.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/08/10 15:04:00 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/272c2d528f7bf6b36d8fa0eedec95792/t_sqoop_phone.jar
17/08/10 15:04:00 INFO mapreduce.ExportJobBase: Beginning export of t_sqoop_phone
17/08/10 15:04:01 INFO impl.TimelineClientImpl: Timeline service address: http://hdp05.data.com.cn:8188/ws/v1/timeline/
17/08/10 15:04:01 INFO client.RMProxy: Connecting to ResourceManager at hdp05.data.com.cn/10.123.45.5:8050
17/08/10 15:04:01 INFO client.AHSProxy: Connecting to Application History server at hdp05.data.com.cn/10.123.45.5:10200
17/08/10 15:04:02 INFO hdfs.DFSClient: Created HDFS_DELEGATION_TOKEN token 2069 for hive on 10.123.45.4:8020
17/08/10 15:04:02 INFO security.TokenCache: Got dt for hdfs://hdp04.data.com.cn:8020; Kind: HDFS_DELEGATION_TOKEN, Service: 10.123.45.4:8020, Ident: (HDFS_DELEGATION_TOKEN token 2069 for hive)
17/08/10 15:04:03 INFO input.FileInputFormat: Total input paths to process : 502
17/08/10 15:04:03 INFO input.FileInputFormat: Total input paths to process : 502
17/08/10 15:04:04 INFO mapreduce.JobSubmitter: number of splits:4
17/08/10 15:04:04 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1502263524420_0003
17/08/10 15:04:04 INFO mapreduce.JobSubmitter: Kind: HDFS_DELEGATION_TOKEN, Service: 10.123.45.4:8020, Ident: (HDFS_DELEGATION_TOKEN token 2069 for hive)
17/08/10 15:04:05 INFO impl.YarnClientImpl: Submitted application application_1502263524420_0003
17/08/10 15:04:05 INFO mapreduce.Job: The url to track the job: http://hdp05.data.com.cn:8088/proxy/application_1502263524420_0003/
17/08/10 15:04:05 INFO mapreduce.Job: Running job: job_1502263524420_0003
17/08/10 15:04:14 INFO mapreduce.Job: Job job_1502263524420_0003 running in uber mode : false
17/08/10 15:04:14 INFO mapreduce.Job:  map 0% reduce 0%
17/08/10 15:04:20 INFO mapreduce.Job:  map 100% reduce 0%
17/08/10 15:04:20 INFO mapreduce.Job: Job job_1502263524420_0003 completed successfully
17/08/10 15:04:20 INFO mapreduce.Job: Counters: 31
        File System Counters
                FILE: Number of bytes read=0
                FILE: Number of bytes written=664816
                FILE: Number of read operations=0
                FILE: Number of large read operations=0
                FILE: Number of write operations=0
                HDFS: Number of bytes read=64287
                HDFS: Number of bytes written=0
                HDFS: Number of read operations=1510
                HDFS: Number of large read operations=0
                HDFS: Number of write operations=0
        Job Counters
                Launched map tasks=4
                Other local map tasks=1
                Data-local map tasks=3
                Total time spent by all maps in occupied slots (ms)=14802
                Total time spent by all reduces in occupied slots (ms)=0
                Total time spent by all map tasks (ms)=14802
                Total vcore-milliseconds taken by all map tasks=14802
                Total megabyte-milliseconds taken by all map tasks=83364864
        Map-Reduce Framework
                Map input records=502
                Map output records=502
                Input split bytes=52347
                Spilled Records=0
                Failed Shuffles=0
                Merged Map outputs=0
                GC time elapsed (ms)=326
                CPU time spent (ms)=14850
                Physical memory (bytes) snapshot=1499045888
                Virtual memory (bytes) snapshot=27594924032
                Total committed heap usage (bytes)=1363673088
        File Input Format Counters
                Bytes Read=0
        File Output Format Counters
                Bytes Written=0
17/08/10 15:04:20 INFO mapreduce.ExportJobBase: Transferred 62.7803 KB in 19.047 seconds (3.2961 KB/sec)
17/08/10 15:04:20 INFO mapreduce.ExportJobBase: Exported 502 records.

可见导出 502 条数据到mysql 库中。
上传后的部分数据如下所示：

这里写图片描述

部分导出（如果只导出一部分字段，比如导出id,code 则命令如下所示）

sqoop export --connect jdbc:mysql://hdp12:3306/test --username test --password test-123 --table t_sqoop_phone --export-dir /apps/hive/warehouse/test.db/rphone --columns id,code

部分结果如下所示：

......
| 310  | 1310 | NULL   | NULL  |
| 32   | 1032 | NULL   | NULL  |
.......

注：如果 –export-dir /apps/hive/warehouse/test.db/rphone 这里不写根目录，如直接写apps/hive/warehouse/test.db/rphone ,sqoop会自动给你的文件目录添加到/user/hive/为前缀的目录。
hive默认的字段分隔符为’\001’；

四、Hive 数据导出关系数据库的进一步说明

该export工具将一组文件从HDFS导出回RDBMS。目标表必须已经存在于数据库中。根据用户指定的分隔符，输入文件被读取并解析成一组记录。
默认操作是将它们转换成一组INSERT 将记录注入数据库的语句。在“更新模式”中，Sqoop将生成UPDATE替换数据库中现有记录的语句，而在“调用模式”中，Sqoop将为每个记录创建一个存储过程调
hive 中有 502 条数据，如下：

这里写图片描述

执行sqoop命令将其全量导入mysql

sqoop export --connect jdbc:mysql://hdp12:3306/test --username test --password test-123  --table t_sqoop_phone --export-dir /apps/hive/warehouse/test.db/rphone

将mysql中id 大于100的都删除掉，并将name 的值修改为 huawei，如下：

这里写图片描述

执行sqoop导出命令，参数 –update-key id –update-mode updateonly ：

sqoop export --connect jdbc:mysql://hdp12:3306/test --username test --password test-123  --table t_sqoop_phone --export-dir /apps/hive/warehouse/test.db/rphone --update-key id --update-mode updateonly

mysql 结果如下：
发现id 大于100的并没有被添加进来，而id与hive中数据相对应的数据则被修改成与Hive中的一样。
执行sqoop命令，参数: –update-key id –update-mode allowinsert

sqoop export --connect jdbc:mysql://hdp12:3306/test --username test --password test-123  --table t_sqoop_phone --export-dir /apps/hive/warehouse/test.db/rphone --update-key id --update-mode allowinsert

结果如下：

这里写图片描述

根据实验结果得出默认是 allowinsert（不会更新数据，每执行一次便将Hive中所有的数据插入mysql中），updateonly 会根据Hive中数据对mysql中的数据进行更改。
根据官方文档得出sqoop 导出支持的参数如下：

表29.导出控制参数：
参数  描述
--columns <col,col,col…>             要导出到表的列
--direct                             使用直接导出快速路径
--export-dir <dir>                   用于导出的HDFS源路径
-m,--num-mappers <n>                 使用n个映射任务并行导出
--table <table-name>                 表填充
--call <stored-proc-name>            存储过程调用
--update-key <col-name> Anchor       列用于更新。如果有多个列，请使用逗号分隔的列列表。
--update-mode <mode>                 指定在数据库中找到具有不匹配键的新行时，如何执行更新。规定值mode包括 updateonly（默认）和 allowinsert。
--input-null-string <null-string>       字符串列解释为空的字符串
--input-null-non-string <null-string>   要解释为非字符串列的空字符串
--staging-table <staging-table-name>    在将数据插入到目标表之前将其分段的表格。
--clear-staging-table                   表示暂存表中存在的任何数据都可以删除。
--batch                                 使用批处理模式执行底层语句。

这里写图片描述

五、Hbase数据导出mysql

首先得建立与hbase关联的hive映射表，然后通过该Hive映射表创建Hive实际的数据表（这个表可以通过sql创建，eg:create table hive实际表 as select * from hive映射表。数据导完之后再将该表删除掉）。当hive实际表建立后，其他操作就跟Hive数据导出到mysql一样了。

六、sqoop将Mysql数据导入Hive中

命令：sqoop import –connect jdbc:mysql://xxx:3306/db –username user –password pwd –table tablename –hive-import –hive-table tablename -m 1

操作实例：
创建mysql表testSqoop

DROP TABLE IF EXISTS `testsqoop`;
CREATE TABLE `testsqoop` (
  `XH` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '序号',
  `SSQ` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '所属区'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入测试数据：

INSERT INTO `testsqoop` VALUES ('1', '海珠区');
INSERT INTO `testsqoop` VALUES ('10', '白云区');
INSERT INTO `testsqoop` VALUES ('11', '黄埔区');
INSERT INTO `testsqoop` VALUES ('12', '荔湾区');
INSERT INTO `testsqoop` VALUES ('13', '天河区');
INSERT INTO `testsqoop` VALUES ('14', '海珠区');
INSERT INTO `testsqoop` VALUES ('15', '海珠区');
INSERT INTO `testsqoop` VALUES ('16', '海珠区');
INSERT INTO `testsqoop` VALUES ('17', '荔湾区');
INSERT INTO `testsqoop` VALUES ('18', '海珠区');
INSERT INTO `testsqoop` VALUES ('19', '黄埔区');
INSERT INTO `testsqoop` VALUES ('2', '黄埔区');
INSERT INTO `testsqoop` VALUES ('20', '黄埔区');
INSERT INTO `testsqoop` VALUES ('21', '番禺区');
INSERT INTO `testsqoop` VALUES ('22', '花都区');
INSERT INTO `testsqoop` VALUES ('23', '海珠区');
INSERT INTO `testsqoop` VALUES ('24', '白云区');
INSERT INTO `testsqoop` VALUES ('25', '花都区');
INSERT INTO `testsqoop` VALUES ('26', '海珠区');
INSERT INTO `testsqoop` VALUES ('27', '番禺区');
INSERT INTO `testsqoop` VALUES ('28', '天河区');

执行导入Hive的命令（如果Hive中没有存在对应的hive表，则会依据mysql 的表来创建对应的表，字段属性也跟mysql的一致）
1、这张表不存在的情况下（默认会自动创建对应的Hive表并全量将数据加载进去）

sqoop import --connect jdbc:mysql://xxx:3306/db --username xxx --password xxx --table  testSqoop --hive-import --hive-table testSqoop -m 1

hive中数据如下所示：

hive> select * from testSqoop;
OK
1       海珠区
10      白云区
11      黄埔区
12      荔湾区
13      天河区
14      海珠区
15      海珠区
16      海珠区
17      荔湾区
18      海珠区
19      黄埔区
2       黄埔区
20      黄埔区

2、这张表存在的情况下（默认往表中追加数据）

sqoop import --connect jdbc:mysql://xxx:3306/db --username xxx --password xxx --table  testSqoop --hive-import --hive-table testSqoop -m 1

sqoop 中常用参数简要说明

遇到的问题：
* 问题一：
出现如下错误提示：

 17/12/13 10:35:58 ERROR tool.ImportTool: Error during import: No primary key could be found for table t_gz_cyyqmcb.

这里写图片描述

解决方法一：给该表添加主键便可；
解决方法二：加到sqoop 对应的参数 -m 1 ，也就是该命令只在一个mapreduce进程中进行，所以该方法会使得数据抽取的效率变低。

 sqoop import --connect jdbc:mysql://xxx:3306/db --username xxx --password xxx --table t_gz_cyyqmcb --hive-import --hive-table t_gz_cyyqmcb -m 1

问题二：有关sqoop 分割符的问题
在将mysql 中的数据导入到hive中，mysql 中的数据如下，在mysql中这是一条数据，但是数据对应的某一列数据有换行符，因而导入到mysql中对变成两条数据。
在进行sqoop import 数据时，如果不加其他参数，导入的数据默认的列分隔符是’\001’，默认的行分隔符是’\n’。也就像下面的数据那样，在导入时出现换行符时hive以为这条数据已经结束，并将其后面输入的数据当做另一条数据。

这里写图片描述

hive 默认会解析成两条数据
解决方法：
加上参数–hive-drop-import-delims来把导入数据中包含的hive默认的分隔符去掉
命令如下所示：

sqoop import --connect jdbc:mysql://xxx:3306/db --username xxx --password xxx --table  testSqoop --hive-import --hive-table testSqoop -m 1 --hive-drop-import-delims

这里写图片描述

问题三：
ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
这个是由于mysql-connector-java的bug造成的，出错时我用的是mysql-connector-java-5.1.17，更新成mysql-connector-java-5.1.36.jar就可以了。直接重命名覆盖原先的则可，无需重启mysql。
mv mysql-connector-java-5.1.31.jar mysql-connector-java-5.1.17.jar
如果一个换成某个指定版本还是报一样的错那就多试两个吧，笔者就是试了两个才可以
问题四：
- Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
- sqoop需要hive的相关配置文件，像笔者用的是Ambari管理大数据平台，因此这个问题可以很方便的通过Ambari提供的界面，在要运行sqoop命令的机器添加一个hiveClient便可，添加hiveClient后Ambari会相应的把hive相关的配置文件也给自动加上。

参考文档：
sqoop 官方操作文档