在大数据时代,数据的迁移和转换变得尤为重要。Sqoop作为Apache Hadoop生态系统中的一员,它提供了在Hadoop和关系型数据库之间高效传输大量数据的能力。本文将详细介绍Sqoop的基本理论、安装配置、使用方法以及在实际工作中如何利用Sqoop进行数据的导入导出。
一、Sqoop概述
Sqoop是SQL-to-Hadoop的缩写,它是一个用于在Hadoop和关系型数据库管理系统(RDBMS)之间传输数据的工具。Sqoop通过使用MapReduce作业来实现数据的批量导入和导出,从而加快了数据传输速度。因此它本质上是一种批处理工具,而不是实时数据传输解决方案。
Sqoop 是一个数据导入导出工具,可以将MySQL的数据导入到HDFS,Hive,Hbase中。
也可以将HDFS,Hive 中的数据导出到mysql。
1.1、Sqoop与相关技术
- Flume:日志数据抽取工具,与Sqoop不同,Flume更侧重于日志数据的实时收集。
- MySQL:关系型数据库,Sqoop可以将MySQL中的数据导入到Hadoop生态系统中。
- HDFS:Hadoop分布式文件系统,用于存储海量数据。
- Hive:数据仓库工具,可以将SQL语句转化为MapReduce作业进行数据分析。
- HBase:非关系型数据库,Sqoop同样支持将数据导入导出到HBase。
1.2、Sqoop的关键特性
- 高速数据传输: 利用Hadoop的分布式处理能力快速移动数据。
- 易用性: 提供简单的命令行界面进行数据传输操作。
- 与Hadoop和关系型数据库的集成: 与各种数据库和Hadoop生态系统无缝协作。
二、Sqoop的安装与配置
2.1、安装步骤
2.1.1)上传与解压
上传至/opt/modules/文件夹
tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/installs/
2.2.2)重命名
切换到/opt/installs/目录下
mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop
2.2、配置环境变量
在/etc/profile
文件中添加以下内容:
export SQOOP_HOME=/opt/installs/sqoop
export PATH=$PATH:$SQOOP_HOME/bin
然后执行source /etc/profile
使配置生效。
2.3、修改配置文件
拷贝sqoop-env-template.sh
为sqoop-env.sh
并修改相关变量。
cp sqoop-env-template.sh sqoop-env.sh
2.4、添加MySQL驱动
通过网盘分享的文件:mysql-connector-java-8.0.26.jar
将MySQL的JDBC驱动包放入Sqoop的lib
目录下。
三、实际使用
3.1、基本命令
3.1.1)sqoop help
显示Sqoop命令的帮助信息。
3.2.2)sqoop list-databases
列出RDBMS中的所有数据库。
(1)展示一个数据库中的所有数据库在命令窗口运行
sqoop list-databases \
--connect jdbc:mysql://bigdata01:3306/ \
--username root \
--password 123456
如果出现拒绝连接的情况
1)驱动包导入错
2)IP写错了
3)mysql 没启动 肯定连不上
通过这个类,找是在哪个jar包中:commons-lang.xxx .jar
find /opt/installs -name commons-lang-2.6.jar
拷贝一个到 sqoop的lib 下
cp /opt/installs/hive/lib/commons-lang-2.6.jar /opt/installs/sqoop/lib/
再次执行以上的连接命令:
(2) 将命令写在一个文件中
a.conf
list-databases
--connect
jdbc:mysql://bigdata01:3306
--username
root
--password
123456
命令窗口执行:
sqoop --options-file a.conf
这个a.conf 中不使用 \ ,不要将语句写成一行。前面也不要写sqoop
(3)连接超时
修改一下mysql的配置:
mysql的连接超时时间,默认是8小时,可以修改为1天。修改我们的 /etc/my.cnf 中的文件即可:
wait_timeout=31536000
interactive_timeout=31536000
添加完之后,要重启mysql服务
systemctl restart mysqld
3.2、Import 命令
先说一些通用的参数:
Argument | Description |
==--connect== | 指定JDBC连接字符串 |
--connection-manager | 指定连接管理类 |
--driver | 指定连接的驱动程序 |
-P | 从控制台读入密码(可以防止密码显示中控制台) |
==--password== | 指定访问数据库的密码 |
==--username== | 指定访问数据库的用户名 |
展示数据库中的所有表:
sqoop list-tables \
--connect jdbc:mysql://bigdata01:3306/hive \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456
创建一个数据库sqoop,并且导入数据:
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
3.2.1)import 导入命令的参数
Argument | Description |
--append | 通过追加的方式导入到HDFS |
--as-avrodatafile | 导入为 Avro Data 文件格式 |
--as-sequencefile | 导入为 SequenceFiles文件格式 |
--as-textfile | 导入为文本格式 (默认值) |
--as-parquetfile | 导入为 Parquet 文件格式 |
--columns | 指定要导入的列 |
--delete-target-dir | 如果目标文件夹存在,则删除 |
--fetch-size | 一次从数据库读取的数量大小 |
-m,--num-mappers | m 用来指定map tasks的数量,用来做并行导入 |
-e,--query | 指定要查询的SQL语句 |
--split-by | 用来指定分片的列 |
--table | 需要导入的表名 |
--target-dir | HDFS 的目标文件夹 |
--where | 用来指定导入数据的where条件 |
-z,--compress | 是否要压缩 |
--compression-codec | 使用Hadoop压缩 (默认是 gzip) |
四、Sqoop的数据导入
4.1、将mysql的emp这个表的数据导入到hdfs上
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--table emp \
--target-dir /home \
--delete-target-dir
1、一定要记得的是 Sqoop的执行脚本,底层使用的MR中的map,所以输出文件的名字都是part-m 开头
2、为什么是4个文件呢,原因是sqoop 底层默认开启了4个map任务,所以有四个输出,可以自己修改map任务的数量
3、数据切割为什么不均匀?
不是按照条数平均切割的,而是按照主键,以及主键的值进行切割的。
4.2、将mysql的数据导入到HDFS
1、如果想将一个表中的数据,只导入一部分,可以加where 条件
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--table emp \
--target-dir /home \
--columns 'ename,job' \
--where 'empno < 7788' \
--delete-target-dir
2、Sqoop 也支持SQL语句
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--query 'select * from emp where empno < 7788 and $CONDITIONS' \
--target-dir /home \
--delete-target-dir \
--split-by empno
1、query的时候,sql语句中必须含有and $CONDITIONS
2、query的时候,必须使用split-by
3、指定split-by的时候,查询结果必须包含这个列,否则报:无法识别的列假如需要将结果展示到一个文件中,指定map数量为 1
-m 1
如果使用了m 1 ,也就是说我的map只有一个,只有一个,就意味着不需要分割为4份了,所以也就不需要指定分割字段是什么了。
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--query 'select * from emp where empno < 7788 and $CONDITIONS' \
--target-dir /home \
--delete-target-dir \
-m 1
如果你使用--query 编写了一个SQL语句,表名,列名,where条件都不需要写了,因为都在SQL语句中了,但是一定要注意的是 SQL语句中必须添加 $CONDITIONS ,而这个字段,必须跟split-by 一起使用,才行。
假如你的表中没有主键,就导入hdfs的话,会报错:
先将emp表中的主键给删除掉:
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--table emp \
--target-dir /home \
--columns 'ename,job' \
--where 'empno < 7788' \
--delete-target-dir
修改一下:
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--table emp \
--target-dir /home \
--columns 'ename,job' \
--where 'empno < 7788' \
--delete-target-dir \
-m 1
如果没有主键,map任务就不知道按照哪个字段切割数据了,所以要么指定一个字段,要么就只生成一个文件。
所以 --split-by 和 -m 1 二选一即可
看一个问题:
sqoop import \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--target-dir /home/sqoop/ \
--delete-target-dir \
--query 'select empno,ename,sal from emp where empno < 7788 and $CONDITIONS' \
--split-by ename
切割的字段是ename ,而ename列是字符串报错:
Caused by: Generating splits for a textual index column allowed only in case of "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" property passed as a parameter
修改脚本如下:
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--target-dir /home/sqoop/ \
--delete-target-dir \
--query 'select empno,ename,sal from emp where empno < 7788 and $CONDITIONS' \
--split-by ename
看另一个问题:
ClassNotFoundException class 表名 not found
解决办法:
第一种方案:进入sqoop的lib下面
运行如下代码:
配置中需要添加--bindir ./sqoop import --bindir ./ --connect jdbc:mysql://localhost:3306/db --username root --password 123456 --table user -m 1
第二种解决办法: 将如下内容添加到环境变量中 /etc/profile
export SQOOP_CLASSPATH=/opt/installs/sqoop/lib
4.3、MySQL数据导入到Hive 【是 sqoop 的优点,其他工具暂时还没有】
Argument | Description |
--hive-home | 覆盖环境配置中的$HIVE_HOME,默认可以不配置 |
--hive-import | 指定导入数据到Hive中 |
--hive-overwrite | 覆盖当前已有的数据 |
--create-hive-table | 是否创建hive表,如果已经存在,则会失败 |
--hive-table | 设置要导入的Hive中的表名 |
我们经常会将mysql数据(业务数据) 导入hive中,进行分析
sqoop import --connect jdbc:mysql://bigdata01:3306/sqoop \
--driver com.mysql.cj.jdbc.Driver \
--username root \
--password 123456 \
--table emp \
--hive-import \
--hive-overwrite \
--hive-table emp \
--hive-database yhdb \
-m 1
缺少Jar包:
cp /opt/installs/hive/lib/hive-common-3.1.2.jar /opt/installs/sqoop/lib/
cp /usr/local/hive/lib/hive-exec-3.1.2.jar /usr/local/sqoop/lib/ ---此包暂时不拷贝
假如一个sqoop脚本执行一半报错了,再执行的时候就会报如上错误,相当于是hdfs中的 user/root/emp 是一个临时文件,如果sqoop导入成功,会删除这个临时文件的,但是由于没有成功,所以临时文件也没删除,第二次必定报错,解决方案:删了吧!
hdfs dfs -rm -R /user/root/emp
数据库必须存在,否则报错!!!
hive中的表,不需要事先创建好,导入的时候会自行创建。
如果出现以上问题,就将emp文件夹删除即可。
mysql 导入数据到 hive,hive 中的数据库必须提前有,hive 中的表无需创建,会自动生成的。
4.4增量导入数据
4.4.1)什么是全量,什么是增量
(1)全量导入
一次性将一个数据库的所有数据,导入到另一个地方。
(2)增量导入
每次导入的时候只需要将新增的数据导入到另一个地方即可。
一般的做法是:第一次全量导入,后面每个一段时间进行一次增量导入。
4.4.2)实现增量导入的两种方案
(1)使用where 条件进行增量导入,必须有一个时间字段。
假如今天想导入昨天全天的数据:
select * from emp where dt>='2024-09-02 00:00:00' and dt <='2024-02-02 23:59:59'
本质上其实就是每天执行的where条件的数据不一样而已
可以配合我们的shell脚本一起使用
创建一个表
create table sales_order(
orderId int primary key,
order_date date
)
编写一个脚本 import.sh
#!/bin/bash
# 获取昨天的日期
# yesterday=`date -d "1 days ago" "+%Y-%m-%d"`
yesterday=$1
sqoop import --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root --password 123456 \
--query "select * from sales_order where order_date='${yesterday}' and \$CONDITIONS" \
--target-dir /home/dt=${yesterday} \
--delete-target-dir \
-m 1 \
--fields-terminated-by '\t'
这个脚本放在哪里无所谓,需要给定权限
chmod 777 import.sh
使用的时候
./import.sh 2022-08-08
(2)可以使用 last-value 这样一个小的功能实现增量导入
sqoop import --connect jdbc:mysql://bigdata01:3306/yhdb \
--username root \
--password 123456 \
--table sales_order \
--driver com.mysql.cj.jdbc.Driver \
--target-dir /home/sales_order/dt=202402 \
-m 1 \
--check-column orderId \
--incremental append \
--last-value 0 \
--fields-terminated-by '\t'
sqoop import --connect jdbc:mysql://192.168.52.12:3306/sqoop \
--username root \
--password 123456 \
--table sales_order \
--driver com.mysql.cj.jdbc.Driver \
--target-dir /home/sales_order/dt=202306 \
--split-by orderId \
-m 1 \
--check-column orderId \
--incremental append \
--last-value 4 \
--fields-terminated-by '\t'
使用last-value 实现增量导入
优点:不需要依赖日期字段
缺点:每次导入之后都要记录last-value的值。
五、Sqoop的数据导出
5.1、从HDFS导出到MySQL
先在hdfs上创建一些数据:
a.txt
1,18
2,20
3,30
上传至hdfs
hdfs dfs -put a.txt /home
mysql中创建数据库:
CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
编写Sqoop的脚本文件:
sqoop export --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table user \
--export-dir '/home/a.txt' \
--input-fields-terminated-by ',' \
--columns 'id,age' \
-m 1
假如从hdfs导出到mysql的时候,出现了列的顺序不一致问题,可以通过追加--columns 来解决
从hdfs导出到mysql时,mysql中的表需要事先创建出来
5.2、从Hive导出到MySQL[ 最重要的 ]
先创建一个hive表,表中必须有数据:
create table if not exists par3(
`id` int,
`age` int
)
row format delimited
fields terminated by ',';
导入数据
load data local inpath '/home/a.txt' into table par3;
在mysql中创建数据库:
CREATE TABLE `user3` (
`id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
编写Sqoop脚本:
sqoop export \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table user3 \
--hcatalog-database yhdb \
--hcatalog-table par3 \
-m 1
假如出现了这个错误:
只需要在sqoop-env.sh 中 添加 export HCAT_HOME=/opt/installs/hive/hcatalog 即可。
sqoop导出hive数据到mysql时,需要事先把mysql的表建好。
六、结论
Apache Sqoop是批量数据传输中的关系型数据库和Hadoop之间传输大量数据的强大工具。它能够高效地处理大量数据,使其成为数据仓库场景中ETL流程的首选。无论您是在移动数据进行分析、数据仓库还是大数据处理,Sqoop都提供了简单且有效的命令行操作的强大解决方案,合理利用Sqoop可以大大提高数据处理的效率。
视频链接
22-mysql导入hdfs的一些常见参数的使用_哔哩哔哩_bilibili