备案控制台
探索云世界
开发者社区 数据库 文章 正文

sqoop用法之mysql与hive数据导入导出

2022-04-29 837
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： Sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如：MySQL、Oracle、Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。对于某些NoSQL数据库它也提供了连接器。Sqoop，类似于其他ETL工具，使用元数据模型来判断数据类型并在数据从数据源转移到Hadoop时确保类型安全的数据处理。Sqoop专为大数据批量传输设计，能够分割数据集并创建Hadoop任务来处理每个区块。

一. Sqoop介绍

Sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如：MySQL、Oracle、Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。对于某些NoSQL数据库它也提供了连接器。Sqoop，类似于其他ETL工具，使用元数据模型来判断数据类型并在数据从数据源转移到Hadoop时确保类型安全的数据处理。Sqoop专为大数据批量传输设计，能够分割数据集并创建Hadoop任务来处理每个区块。


本文版本说明


hadoop版本 : hadoop-2.7.2

hive版本 : hive-2.1.0

sqoop版本:sqoop-1.4.6

二. Mysql 数据导入到 Hive

1). 将mysql的people_access_log表导入到hive表web.people_access_log，并且hive中的表不存在。


mysql中表people_access_log数据为：


1,15110101010,1577003281739,'112.168.1.2','https://www.baidu.com'
2,15110101011,1577003281749,'112.16.1.23','https://www.baidu.com'
3,15110101012,1577003281759,'193.168.1.2','https://www.taobao.com'
4,15110101013,1577003281769,'112.18.1.2','https://www.baidu.com'
5,15110101014,1577003281779,'112.168.10.2','https://www.baidu.com'
6,15110101015,1577003281789,'11.168.1.2','https://www.taobao.com'

将mysql数据导入hive的命令为：


sqoop import \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--table people_access_log \
-m 1 \
--hive-import \
--create-hive-table \
--fields-terminated-by '\t' \
--hive-table web.people_access_log


该命令会启用一个mapreduce任务，将mysql数据导入到hive表，并且指定了hive表的分隔符为\t，如果不指定则为默认分隔符^A(ctrl+A)。


参数说明

参数 说明
--connect mysql的连接信息
--username mysql的用户名
--password mysql的密码
--table 被导入的mysql源表名
-m 并行导入启用的map任务数量，与--num-mapper含义一样
--hive-import 插入数据到hive当中，使用hive默认的分隔符，可以使用--fields-terminated-by参数来指定分隔符。
-- hive-table hive当中的表名


2). 也可以通过--query条件查询Mysql数据，将查询结果导入到Hive

sqoop import \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--query 'select * from people_access_log where \$CONDITIONS and url = "https://www.baidu.com"' \
--target-dir /user/hive/warehouse/web/people_access_log \
--delete-target-dir \
--fields-terminated-by '\t' \
-m 1
参数 说明
--query 后接查询语句，条件查询需要\$CONDITIONS and连接查询条件，这里的\$表示转义$，必须有.
--delete-target-dir 如果目标hive表目录存在，则删除，相当于overwrite.

三. Hive数据导入到Mysql

还是使用上面的hive表web.people_access_log,将其导入到mysql中的people_access_log_out表中.

sqoop export \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--table people_access_log_out \
--input-fields-terminated-by '\t' \
--export-dir /user/hive/warehouse/web.db/people_access_log \
--num-mappers 1


注意：mysql表people_access_log_out需要提前建好，否则报错：ErrorException: Table 'test.people_access_log_out' doesn't exist。如果有id自增列，hive表也需要有，hive表与mysql表字段必须完全相同。

create table people_access_log_out like people_access_log;


执行完一个mr任务后，成功导入到mysql表people_access_log_out中.

四. mysql数据增量导入hive

实际中mysql数据会不断增加，这时候需要用sqoop将数据增量导入hive，然后进行海量数据分析统计。增量数据导入分两种，一是基于递增列的增量数据导入（Append方式）。二是基于时间列的增量数据导入（LastModified方式）。有几个核心参数：


  • –check-column:用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似.注意:这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时–check-column可以去指定多个列


  • –incremental:用来指定增量导入的模式，两种模式分别为Append和Lastmodified


  • –last-value:指定上一次导入中检查列指定字段最大值


1. 基于递增列Append导入


接着前面的日志表，里面每行有一个唯一标识自增列ID，在关系型数据库中以主键形式存在。之前已经将id在0~6之间的编号的订单导入到Hadoop中了（这里为HDFS），现在一段时间后我们需要将近期产生的新的订 单数据导入Hadoop中（这里为HDFS），以供后续数仓进行分析。此时我们只需要指定–incremental 参数为append，–last-value参数为6即可。表示只从id大于6后即7开始导入。


1). 创建hive表


首先我们需要创建一张与mysql结构相同的hive表，假设指定字段分隔符为\t，后面导入数据时候分隔符也需要保持一致。


2). 创建job


增量导入肯定是多次进行的，可能每隔一个小时、一天等，所以需要创建计划任务，然后定时执行即可。我们都知道hive的数据是存在hdfs上面的，我们创建sqoop job的时候需要指定hive的数据表对应的hdfs目录，然后定时执行这个job即可。


当前mysql中数据，hive中数据与mysql一样也有6条：

id user_id access_time ip url
1 15110101010 1577003281739 112.168.1.2 https://www.baidu.com
2 15110101011 1577003281749 112.16.1.23 https://www.baidu.com
3 15110101012 1577003281759 193.168.1.2 https://www.taobao.com
4 15110101013 1577003281769 112.18.1.2 https://www.baidu.com
5 15110101014 1577003281779 112.168.10.2 https://www.baidu.com
6 15110101015 1577003281789 11.168.1.2 https://www.taobao.com

增量导入有几个参数，保证下次同步的时候可以接着上次继续同步.


sqoop job --create mysql2hive_job -- import \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--table people_access_log \
--target-dir /user/hive/warehouse/web.db/people_access_log \
--check-column id \
--incremental append \
--fields-terminated-by '\t' \
--last-value 6 \
-m 1


这里通过sqoop job --create job_name命令创建了一个名为mysql2hive_job的sqoop job。


3). 执行job


创建好了job，后面只需要定时周期执行这个提前定义好的job即可。我们先往mysql里面插入2条数据。


INSERT INTO `people_access_log` (`id`,`user_id`,`access_time`,`ip`,`url`) VALUES
(7,15110101016,1577003281790,'112.168.1.3','https://www.qq.com'),
(8,15110101017,1577003281791,'112.1.1.3','https://www.microsoft.com');


这样mysql里面就会多了2条数据。此时hive里面只有id为1 ~ 6的数据，执行同步job使用以下命令。


sqoop job -exec mysql2hive_job

执行完成后，发现刚才mysql新加入的id为7 ~ 8的两条数据已经同步到hive。

hive> select * from web.people_access_log;
OK
1 15110101010 1577003281739 112.168.1.2 https://www.baidu.com
2 15110101011 1577003281749 112.16.1.23 https://www.baidu.com
3 15110101012 1577003281759 193.168.1.2 https://www.taobao.com
4 15110101013 1577003281769 112.18.1.2  https://www.baidu.com
5 15110101014 1577003281779 112.168.10.2  https://www.baidu.com
6 15110101015 1577003281789 11.168.1.2  https://www.taobao.com
7 15110101016 1577003281790 112.168.1.3 https://www.qq.com
8 15110101017 1577003281791 112.1.1.3 https://www.microsoft.com


由于实际场景中，mysql表中的数据，比如订单表等，通常是一致有数据进入的，这时候只需要将sqoop job -exec mysql2hive_job这个命令定时(比如说10分钟频率)执行一次，就能将数据10分钟同步一次到hive数据仓库。


2. Lastmodified 导入实战


append适合业务系统库，一般业务系统表会通过自增ID作为主键标识唯一性。


Lastmodified适合ETL的数据根据时间戳字段导入，表示只导入比这个时间戳大，即比这个时间晚的数据。


1). 新建一张表


在mysql中新建一张表people_access_log2，并且初始化几条数据:


CREATE TABLE `people_access_log2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `access_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` varchar(15) NOT NULL COMMENT '访客ip',
  `url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


插入数据:

insert into people_access_log2(id,user_id, ip, url) values(1,15110101010,'112.168.1.200','https://www.baidu.com');
insert into people_access_log2(id,user_id, ip, url) values(2,15110101011,'112.16.1.2','https://www.baidu.com');
insert into people_access_log2(id,user_id, ip, url) values(3,15110101012,'112.168.1.2','https://www.taobao.com');
insert into people_access_log2(id,user_id, ip, url) values(4,15110101013,'112.168.10.2','https://www.baidu.com');
insert into people_access_log2(id,user_id, ip, url) values(5,15110101014,'112.168.1.2','https://www.jd.com');
insert into people_access_log2(id,user_id, ip, url) values(6,15110101015,'112.168.12.4','https://www.qq.com');


mysql里面的数据就是这样：

id user_id access_time ip url
1 15110101010 2019-12-28 16:23:10 112.168.1.200 https://www.baidu.com
2 15110101011 2019-12-28 16:23:33 112.16.1.2 https://www.baidu.com
3 15110101012 2019-12-28 16:23:41 112.168.1.2 https://www.taobao.com
4 15110101013 2019-12-28 16:23:46 112.168.10.2 https://www.baidu.com
5 15110101014 2019-12-28 16:23:52 112.168.1.2 https://www.jd.com
6 15110101015 2019-12-28 16:23:56 112.168.12.4 https://www.qq.


2). 初始化hive表:


初始化hive数据，将mysql里面的6条数据导入hive中，并且可以自动帮助我们创建对应hive表，何乐而不为，否则我们需要自己手动创建，完成初始化工作。


sqoop import \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--table people_access_log2 \
--hive-import \
--create-hive-table \
--fields-terminated-by ',' \
--hive-table web.people_access_log2


可以看到执行该命令后，启动了二一个mapreduce任务，这样6条数据就进入hive表web.people_access_log2了:


hive> select * from web.people_access_log2;
OK
1 15110101010 2019-12-28 16:23:10.0 112.168.1.200 https://www.baidu.com
2 15110101011 2019-12-28 16:23:33.0 112.16.1.2  https://www.baidu.com
3 15110101012 2019-12-28 16:23:41.0 112.168.1.2 https://www.taobao.com
4 15110101013 2019-12-28 16:23:46.0 112.168.10.2  https://www.baidu.com
5 15110101014 2019-12-28 16:23:52.0 112.168.1.2 https://www.jd.com
6 15110101015 2019-12-28 16:23:56.0 112.168.12.4  https://www.qq.com
Time taken: 0.326 seconds, Fetched: 6 row(s)


3). 增量导入数据:


我们再次插入一条数据进入mysql的people_access_log2表:

insert into people_access_log2(id,user_id, ip, url) values(7,15110101016,'112.168.12.45','https://www.qq.com');


此时，mysql表里面已经有7条数据了，我们使用incremental的方式进行增量的导入到hive:

sqoop import \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--table people_access_log2 \
--hive-import \
--hive-table people_access_log2 \
-m 1 \
--check-column access_time \
--incremental lastmodified \
--last-value "2019-12-28 16:23:56" \

2019-12-28 16:23:56就是第6条数据的时间，这里需要指定。报错了:

19/12/28 16:17:25 ERROR tool.ImportTool: Error during import: --merge-key or --append is required when using --incremental lastmodified and the output directory exists.


注意：可以看到--merge-key or --append is required when using --incremental lastmodified意思是，这种基于时间导入模式，需要指定--merge-key或者--append参数，表示根据时间戳导入，数据是直接在末尾追加(append)还是合并(merge)，这里使用merge方式，根据id合并：


sqoop import \
--connect jdbc:mysql://master1.hadoop:3306/test \
--username root \
--password 123456 \
--table people_access_log2 \
--hive-import \
--hive-table web.people_access_log2 \
--check-column access_time \
--incremental lastmodified \
--last-value "2019-12-28 16:23:56" \
--fields-terminated-by ',' \
--merge-key id


执行该命令后，与直接导入不同，该命令启动了2个mapreduce任务，这样就把数据增量merge导入hive表了.

hive> select * from web.people_access_log2 order by id;
OK
1 15110101010 2019-12-28 16:23:10.0 112.168.1.200 https://www.baidu.com
2 15110101011 2019-12-28 16:23:33.0 112.16.1.2  https://www.baidu.com
3 15110101012 2019-12-28 16:23:41.0 112.168.1.2 https://www.taobao.com
4 15110101013 2019-12-28 16:23:46.0 112.168.10.2  https://www.baidu.com
5 15110101014 2019-12-28 16:23:52.0 112.168.1.2 https://www.jd.com
6 15110101015 2019-12-28 16:23:56.0 112.168.12.4  https://www.qq.com
6 15110101015 2019-12-28 16:23:56.0 112.168.12.4  https://www.qq.com
7 15110101016 2019-12-28 16:28:24.0 112.168.12.45 https://www.qq.com
Time taken: 0.241 seconds, Fetched: 8 row(s)


可以看到id=6的数据，有2条，它的时间刚好是--last-value指定的时间，则会导入大于等于--last-value指定时间的数据，这点需要注意。

文章标签：
云数据库 RDS MySQL 版
云原生数据库 PolarDB
分布式计算
SQL
关系型数据库
NoSQL
MySQL
Oracle
Hadoop
HIVE
安全
数据库
关键词：
云数据库 RDS MySQL 版导出
云数据库 RDS MySQL 版用法
云数据库 RDS MySQL 版数据导入
云数据库 RDS MySQL 版hive
sqoop云数据库 RDS MySQL 版
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
柯广
目录
相关文章
yuanzhengme
|
1月前
|
SQL 关系型数据库 MySQL
Sqoop【付诸实践 01】Sqoop1最新版 MySQL与HDFS\Hive\HBase 核心导入导出案例分享+多个WRAN及Exception问题处理（一篇即可学会在日常工作中使用Sqoop）
【2月更文挑战第9天】Sqoop【付诸实践 01】Sqoop1最新版 MySQL与HDFS\Hive\HBase 核心导入导出案例分享+多个WRAN及Exception问题处理（一篇即可学会在日常工作中使用Sqoop）
yuanzhengme
94 7
yuanzhengme
|
1月前
|
分布式计算 关系型数据库 MySQL
Sqoop【部署 01】CentOS Linux release 7.5 安装配置 sqoop-1.4.7 解决警告并验证（附Sqoop1+Sqoop2最新版安装包+MySQL驱动包资源）
【2月更文挑战第8天】Sqoop CentOS Linux release 7.5 安装配置 sqoop-1.4.7 解决警告并验证（附Sqoop1+Sqoop2最新版安装包+MySQL驱动包资源）
yuanzhengme
99 1
风水道人
|
28天前
|
SQL 关系型数据库 MySQL
mysql结果垂直显示-\g和\G的用法
mysql结果垂直显示-\g和\G的用法
风水道人
26 0
三分钟热度的鱼
|
1月前
|
SQL DataWorks NoSQL
DataWorks报错问题之从hive到mysql报错如何解决
DataWorks是阿里云提供的一站式大数据开发与管理平台，支持数据集成、数据开发、数据治理等功能；在本汇总中，我们梳理了DataWorks产品在使用过程中经常遇到的问题及解答，以助用户在数据处理和分析工作中提高效率，降低难度。
三分钟热度的鱼
23 1
多多数据库
|
1月前
|
存储 安全 关系型数据库
MySQL 临时表的用法和特性
MySQL 临时表的用法和特性
多多数据库
15 0
零一成城
|
2月前
|
SQL 关系型数据库 MySQL
Mysql SQL的一些特殊用法记录
1、查询group by having 中having不起作用，及解决
零一成城
15 0
晓之以理的喵~~
|
3月前
|
SQL 分布式计算 关系型数据库
Sqoop数据导入到Hive表的最佳实践
Sqoop数据导入到Hive表的最佳实践
晓之以理的喵~~
101 0
晓之以理的喵~~
|
3月前
|
SQL 数据可视化 数据挖掘
将Sqoop与Hive集成无缝的数据分析
将Sqoop与Hive集成无缝的数据分析
晓之以理的喵~~
49 0
Maynor
|
4月前
|
SQL 存储 大数据
手把手教你大数据离线综合实战 ETL+Hive+Mysql+Spark
手把手教你大数据离线综合实战 ETL+Hive+Mysql+Spark
Maynor
95 0
程序三两行
|
16天前
|
关系型数据库 MySQL 数据库
mysql卸载、下载、安装（window版本）
mysql卸载、下载、安装（window版本）
程序三两行
40 1

热门文章

最新文章

  • 1
    Sqoop数据迁移工具使用与优化技巧：面试经验与必备知识点解析
  • 2
    超1/3中国500强企业都在用的「汇联易」，为什么选用阿里云RDS？
  • 3
    MySQL全局库表查询准确定位字段
  • 4
    MySQL学习（三）
  • 5
    安装Docker&镜像容器操作&使用Docker安装部署MySQL，Redis，RabbitMQ，Nacos，Seata，Minio
  • 6
    在Python Web开发过程中：数据库与缓存，MySQL和NoSQL数据库的主要差异是什么？
  • 7
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 8
    【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
  • 9
    MySQL数据库，可以使用二进制日志（binary log）进行时间点恢复
  • 10
    MySQL学习（五）
  • 1
    【Hive】Hive的函数：UDF、UDAF、UDTF的区别？
    36
  • 2
    【Hive】请说明hive中 Sort By，Order By，Cluster By，Distrbute By各代表什么意思？
    40
  • 3
    【Hive】Hive的两张表关联，使用MapReduce怎么实现？
    27
  • 4
    【Hive】Hive优化有哪些？
    41
  • 5
    【Hive】数据倾斜怎么解决？
    22
  • 6
    Hive【基础知识 05】常用DDL操作（数据库操作+创建表+修改表+清空删除表+其他命令）
    19
  • 7
    【Hive】sort by 和 order by 的区别
    15
  • 8
    Hive【基础知识 04】【Hive 属性配置的三种方式及配置的优先级说明】
    12
  • 9
    Hive【基础知识 02-2】【Hive CLI 命令行工具使用】【详细举例-包含测试脚本文件】
    16
  • 10
    【Hive】Hive有索引吗？
    33

    • 相关课程

    更多
  • MySQL完全自学手册
  • MySQL数据库入门学习
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • 大数据Hive教程精讲

    • 相关电子书

    更多
  • Hive Bucketing in Apache Spark
  • spark替代HIVE实现ETL作业
  • 2019大数据技术公开课第五季—Hive迁移到MaxCompute最佳实践

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • clickhouse
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）