一、简介
sqoop （sql to hadoop）是一款开源的工具,主要用于在 Hadoop（Hive）与传统的数据库（mysql、postgresql...）间进行数据的传递，可以将一个关系型数据库（例如 : MSQL,Oracle,Postgres 等）中的数据导进到 Hadoop 的 HDFS 中，也可以将 HDFS 的数据导进到关系型数据库中。
1.1 工作机制
将导入或导出命令翻译成mapreduce程序来实现。
1.2 功能
Sqoop的主要功能如下:
- 导入数据：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统；
- 导出数据：从Hadoop的文件系统中导出数据到关系数据库
二、sqoop安装
2.1 上传安装包
这里两个安装包 sqoop-1.4.7 bin_hadoop-2.6.0.tar.gz和sqoop-1.4.7.tar.gz
因为hadoop版本为3.1.3 所以sqoop的版本太低，需要自行配置
2.2 解压并更名
# 解压
[root@hadoop install]# tar -zxf sqoop-1.4.7.tar.gz -C ../soft/
# 切换目录
[root@hadoop install]# cd ../soft/
# 更名
[root@hadoop soft]# mv sqoop-1.4.7/ sqoop147
2.3 添加jar包
切换目录到 /opt/soft/sqoop147/lib/
添加avro-1.8.1.jar
# 将hive312/lib下的两个jar包拷贝过来
[root@hadoop lib]# cp /opt/soft/hive312/lib/hive-common-3.1.2.jar ./
[root@hadoop lib]# cp /opt/soft/hive312/lib/mysql-connector-java-8.0.29.jar ./
将sqoop-1.4.7.jar 拷贝到 /opt/soft/sqoop147/
2.4 修改配置文件
切换到cd /opt/soft/sqoop147/conf
# 将配置文件复制并更名
[root@hadoop conf]# cp sqoop-env-template.sh sqoop-env.sh
# 编辑 sqoop-env.sh
[root@hadoop conf]# vim ./sqoop-env.sh
22 #Set path to where bin/hadoop is available
23 export HADOOP_COMMON_HOME=/opt/soft/hadoop313
24
25 #Set path to where hadoop-*-core.jar is available
26 export HADOOP_MAPRED_HOME=/opt/soft/hadoop313
27
28 #set the path to where bin/hbase is available
29 #export HBASE_HOME=
30
31 #Set the path to where bin/hive is available
32 export HIVE_HOME=/opt/soft/hive312
33 export HIVE_CONF_DIR=/opt/soft/hive312/conf
34
35 #Set the path for where zookeper config dir is
36 export ZOOCFGDIR=/opt/soft/zk345/conf
2.5 添加sqoop环境变量
# 编辑/etc/profile
[root@hadoop conf]# vim /etc/profile
# SQOOP_HOME
export SQOOP_HOME=/opt/soft/sqoop147
export PATH=$PATH:$SQOOP_HOME/bin
# 刷新文件
[root@hadoop conf]# source /etc/profile
2.6 安装验证
[root@hadoop conf]# sqoop version
安装验证：
[root@hadoop conf]# sqoop list-databases \
[root@hadoop conf]# --connect jdbc:mysql://192.168.153.134:3306 \
[root@hadoop conf]# --username root \
[root@hadoop conf]# --password 123123
注：直接回车会执行代码。\的作用是连接符，用于连接两行代码参数说明：
参数 | 说明 |
–connect | 连接关系型数据库的URL |
–username | 用户名 |
–password | 密码，考虑安全可使用 -P |
–driver | 指定jdbc驱动类 |
三、查看数据库、表
3.1 查看数据库
sqoop list-databases \
--connect jdbc:mysql://192.168.153.134:3306 \
--username root \
--password 1231233.2 查看数据库中的表
sqoop list-tables \
--connect jdbc:mysql://hadoop02:3306/school \
--username root \
--password 123123四、sqoop数据导入import
在 Sqoop 中，“导入”念指:从非大数据集（RDBMS）向大数据集群 （HDFS，HIVE，HBASE）中传输数据，叫做:导入，使用 import
导入单个表从 RDBMS 到 HDFS。表中的每一行被视为 HDFS 的记导入工具记录。所有记录都存储为文本文件的文本数据。
4.1 导入MySQL表数据到HDFS
1> 确定MySQL服务开启正常
2> 在MySQL中新建一张表并插入一些数据
3> 导入数据
参数说明：
参数 | 说明 |
import | 从一个数据库中将一个表格导入到HDFS |
import-all-tables | 从一个数据库中将全部表格导入到HDFS |
list-databases | 列出服务器上的可用数据库 |
list-tables | 列出数据库中的可用表 |
# 将mysql表数据导入到hdfs
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--target-dir /tmp/school/student \ --用来指定导出数据存放至HDFS的目录
--table student
--fields-terminated-by '\t' \ --指定分隔符。HDFS上默认用逗号分隔数据和字段。
--m 1 --表示map task的个数。如果不写，默认为4
注意：
使用-m 进行切分时，默认按照主键进行切割。如果表格中没有主键，需要指定切割列。
--split-by Sage
验证：
[root@hadoop02 ~]# hdfs dfs -cat /tmp/school/student02/part-m-00000
2023-03-10 02:26:38,581 INFO sasl.SaslDataTransferClient: SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false
1990-01-01,01,赵雷,男
1990-12-21,02,钱电,男
1990-05-20,03,孙风,男
1990-08-06,04,李云,男
1991-12-01,05,周梅,女
1992-03-01,06,吴兰,女
1989-07-01,07,郑竹,女
1990-01-20,08,王菊,女注意：
1> mysql的地址尽量不要使用localhost 请使用ip或者host
2> 如果不指定，导入到hdfs默认分隔符是“,"
3> 可以通过--fields-terminated-by '\t' 指定具体的分隔符
4> 如果表的数据比较大，可以并行启动多个maptask执行导入操作。如果没有主键，需要指定根据哪个字段进行切分。
4.2 导入MySQL表数据到Hive
4.2.1 先复制表结构到hive中再导入数据
复制表结构
sqoop create-hive-table \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table teacher \ --数据库school中的表
--hive-table teacher_hive --hive中新建的表名称导入到hive default库中
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table teacher \
--hive-import \
--hive-table teacher_hive \
--m 14.2.2 直接复制表结构数据到hive中
sqoop import \
# 如果不指定maptast数，需要加如下代码：
-Dorg.apache.sqoop.splitter.allow_text_splitter=top.splitter.allow_text_splitter=true
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table teacher \
--hive-import \
--hive-database teacher_hive
--m 1 \导入到hive 自定义数据库中
sqoop import \
--connect jdbc:mysql://hadoop02:3306/school \
--username root \
--password 123123 \
--table teacher \
--hive-import \
--hive-database bigdata teacher_hive4.3 导入表数据子集（where过滤）
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--where "id=01" \
--target-dir /tmp/school/student01
--table student4.4 导入表数据子集（query查询）
sqoop import
--connect jdbc:mysql://192.168.153.134:3306/school
--username root
--password 123123
--target-dir /tmp/school/users01
--query 'select name from users where cardId="1111" and $CONDITIONS'
--m 1注意：
1> 使用query sql语句来进行查找不能加参数--table，且必须要添加 where条件;
2> 并且 where 条件后面必须带一个$CONDITIONS 这个字符串
3> 并且这个 sql 语句必须用单引号，不能用双引号;
4.5 增量导入
在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表中的数据每次都全部导入到 hive 或者 hdfs 当中去这样会造成数据重复的问题。因此一般都是选用一些字段进行增量的导入， sqoop 支持增量的导入数据。
-- 所谓的增量数据指的是上次至今中间新增加的数据
-- sqoop支持两种模式的增量导入
append追加 根据数值类型字段进行追加导入， 大于指定的last-value
lastmodified 根据时间戳类型字段进行追加， 大于等于指定的last-value
注意在lastmodified模式下，还分为两种情形： append merge-key
增量导入是仅导入新添加的表中的行的技术。
--check-column（col）
用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似。
注意：这些被指定的列的类型不能使任意字符类型，如 char、varchar 等类型都是不可以的，同时-- check-column 可以去指定多个列。
--incremental（mode）
append：追加，比如对大于 last-value 指定的值之后的记录进行追加导入。
lastmodified：最后的修改时间，追加 last-value 指定的日期之后的记录。
--last-value（value）
指定自从上次导入后列的最大值(大于该指定的值),也可以自己设定某一值。
Append增量导入
原始数据：
注意：实现增量导入
mysql> desc real_estate;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| cardId | varchar(18) | NO | | NULL | |
| projectName | varchar(50) | NO | | NULL | |
| address | varchar(200) | NO | | NULL | |
| houseType | varchar(20) | NO | | NULL | |
| area | int | NO | | NULL | |
| buildTime | date | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> select * from real_estate;
+----+--------+--------------+-------------------+-----------+------+------------+
| id | cardId | projectName | address | houseType | area | buildTime |
+----+--------+--------------+-------------------+-----------+------+------------+
| 1 | 1111 | 天虹庄园 | 庄派路12号 | 三室 | 89 | 2023-01-31 |
| 2 | 2222 | 中粮家园 | 经天路21号 | 二室 | 68 | 2023-01-31 |
| 3 | 3333 | 招商公寓 | 宏运大道33号 | 四室 | 118 | 2023-01-31 |
| 4 | 4444 | 金地名筑 | 天景路12号 | 三室 | 89 | 2023-01-31 |
| 5 | 1111 | 浦发庄园 | 经天路13号 | 三室 | 98 | 2023-01-31 |
| 6 | 2222 | 中兴家园 | 通天路21号 | 二室 | 60 | 2023-01-31 |
| 7 | 1111 | 粮油公寓 | 宏运大道33号 | 四室 | 118 | 2023-01-31 |
| 8 | 2222 | 金地名筑 | 天景路12号 | 三室 | 89 | 2023-01-31 |
+----+--------+--------------+-------------------+-----------+------+------------+
8 rows in set (0.00 sec)# 导入初始数据
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table real_estate \
--target-dir /tmp/school/re \
--m 1
# hdfs中查看数据，数据成功导入
[root@hadoop02 ~]# hdfs dfs -cat /tmp/school/re/part-m-00000
2023-03-10 17:00:26,770 INFO sasl.SaslDataTransferClient: SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false
1,1111,天虹庄园,庄派路12号,三室,89,2023-01-31
2,2222,中粮家园,经天路21号,二室,68,2023-01-31
3,3333,招商公寓,宏运大道33号,四室,118,2023-01-31
4,4444,金地名筑,天景路12号,三室,89,2023-01-31
5,1111,浦发庄园,经天路13号,三室,98,2023-01-31
6,2222,中兴家园,通天路21号,二室,60,2023-01-31
7,1111,粮油公寓,宏运大道33号,四室,118,2023-01-31
8,2222,金地名筑,天景路12号,三室,89,2023-01-31
# mysql中添加数据
mysql> insert into real_estate values(9,'2222','碧桂园','北京路888号','别墅',888,'2023-02-01');
Query OK, 1 row affected (0.00 sec)
mysql> select * from real_estate;
+----+--------+--------------+-------------------+-----------+------+------------+
| id | cardId | projectName | address | houseType | area | buildTime |
+----+--------+--------------+-------------------+-----------+------+------------+
| 1 | 1111 | 天虹庄园 | 庄派路12号 | 三室 | 89 | 2023-01-31 |
| 2 | 2222 | 中粮家园 | 经天路21号 | 二室 | 68 | 2023-01-31 |
| 3 | 3333 | 招商公寓 | 宏运大道33号 | 四室 | 118 | 2023-01-31 |
| 4 | 4444 | 金地名筑 | 天景路12号 | 三室 | 89 | 2023-01-31 |
| 5 | 1111 | 浦发庄园 | 经天路13号 | 三室 | 98 | 2023-01-31 |
| 6 | 2222 | 中兴家园 | 通天路21号 | 二室 | 60 | 2023-01-31 |
| 7 | 1111 | 粮油公寓 | 宏运大道33号 | 四室 | 118 | 2023-01-31 |
| 8 | 2222 | 金地名筑 | 天景路12号 | 三室 | 89 | 2023-01-31 |
| 9 | 2222 | 碧桂园 | 北京路888号 | 别墅 | 888 | 2023-02-01 |
+----+--------+--------------+-------------------+-----------+------+------------+
9 rows in set (0.00 sec)
# 实现增量的导入
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table real_estate \
--target-dir /tmp/school/real_estate \
--incremental append \ ——increment-追加模式
--check-column id \ ——追加的字段
--last-value 8 \ ——last-value=8,输出从9开始
--m 1
验证导入数据目录，可以发现多了一个文件，里面就是增量数据。
Lastmodified增量导入
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table real_estate \
--target-dir /tmp/school/re \
--check-column buildTime \
--incremental lastmodified \
--last-value '2023-02-01' \ ——"lastmodified"模式
--m 1 \
--append导入最后插入的一条数据，但却此处却插入了两条数据。
采用lastmodified模式处理增量时，会将大于等于last-value值的数据当作增量插入。
Lastmodified模式：append、merge-key
使用lastmodified 模式进行增量处理要指定增量数据是以append 模式（附加）还是 merge-key（合并）模式添加。
sqoop import \
--connect jdbc:mysql://192.168.153.134:3306/school \
--username root \
--password 123123 \
--table real_estate \
--target-dir /tmp/school/re \
--check-column buildTime \
--incremental lastmodified \
--last-value '2023-02-01' \
--m 1 \
--merge-key idmerge-key模式进行了一次完整了mapreduce操作。
关于lasimodified中的两种模式：
append只会追加数据到一个新的文件中，并且会产生数据的重复问题
因为默认是从指定的last-value大于等于其值的数据开始导入
merge-key
五、sqoop数据导出export
将数据从 Hadoop 生态体系导出到 RDBMS 数据库导出前，目标表必须存在于目标数据库中。也就是说，导出的目标表需要自己手动提前创建，sqoop并不会帮我们创建复制表结构。
export 有三种模式：
默认操作：是从将文件中的数据使用 INSERT
更新模式：Sqoop 将生成 UPDATE
调用模式：Sqoop 将为每条记录创建一个存储过程调用。
5.1 默认模式导出HDFS数据到MySQL
默认情况下，sqoopexport 将每行输入记录转换成一条INSERT 语句，添加到目标数据库表中。如果数据库中的表具有约束条件(例如，其值必须唯一的主键列)并且已有数据存在，则必须注意避免插入违反这些约束条件的记录。如果INSERT 语句失败，导出过程将失败。此模式主要用于将记录导出到可以接收这些结果的空表中。通常用于全表数据导出。
导出时可以是将 Hive 表中的全部记录或者 HDFS 数据（可以是全部字段也可以部分字段）导出到 Mysql 目标表。
hdfs dfs -mkdir /emp_data
hdfs dfs -put emp_data.txt /emp_data
1. 手动创建MySQL中的目标表
mysql> create table employee (
id int not null primary key,
name varchar(10),
deg varchar(20),
salary int,
dept varchar(10));
2. 执行导出命令
sqoop export \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password 123123 \
--table employee \
--export-dir /emp_data/相关配置参数
--input-fields-terminated-by '\t'
指定文件中的分隔符。
--columns
选择列并控制它们的排序。当导出数据文件和目标表字段列顺序完全致的时候可以不写。否则以逗号为间隔选择和排列各个列。没有被包含在 -columns 后面列名或字段要么具备默认值，要么就允许插入空值，否则数据库会拒绝凌受 sqoop 导出的数据，导致 Sqoop 作业失败。
--export-dir
导出目录。在执行导出的时候，比如指定这个参数，同时需要具备--table 或 --call参数两者之一。
--table指的是导出数据库当中对应的表。--call指的是某个存储过程。
--input-null-string/ --input-null-non-string
如果没有指定第一个参数，对于字符串类型的列来说，“null”这个字符串就会被翻译成空值。
如果没有使用第二个参数，无论是“null”字符串还是空字符串，对于非字符串类型的字段来说，这两个类型的空串都会被翻译成空值。
如：--input-null-string "\\N"/ --input-null-non-string "\\N"
5.2 更新导出（updateonly模式）
更新导出：
updateonly 只更新已经存在的数据，不会执行insert增加新的数据。
allowinsert--update-key
更新标识，即根据某个字段进行更新。例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。
--updatemod
指定updateonly（默认模式），仅仅更新已存在的数据记录，不会插入新纪录。
sqoop export \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password 123123 \
--table updateonly \
--export-dir /updateonly _1/
新增一个文件updateonly_2，修改前三条数据并新增一条记录。
执行更新导出：
sqoop export \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password 123123 \
--table updateonly \
--export-dir /updateonly _2/
--update-key id \
--update-mode updateonlyupdateonly 只更新已经存在的数据，不会执行insert增加新的数据。
5.3 更新导出（allowinsert模式）
--update-key
更新标识，即根据某个字段进行更新。例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。
--updatemod
指定allowinsert，更新已存在的数据记录，同时插入新纪录。实质上是一个insert&update的操作。
sqoop export \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password 123123 \
--table updateonly \
--export-dir /updateonly _1/
新增一个文件updateonly_2，修改前三条数据并新增一条记录。
执行更新导出：
sqoop export \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password 123123 \
--table updateonly \
--export-dir /updateonly _2/
--update-key id \
--update-mode allowinsertallowinsert 更新已有的数据，插入新的数据，底层相当于insert&update
六、sqoop job作业
创建job
创建一个从DB数据库的emp表导入到HDFS文件的作业。
注意：import前面要有空格。
bin/sqoop --create castjob \
-- import \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password 123123 \
--target-dir /sqoopresult \
--table emp \
--m 1验证作业（--list）
bin/sqoop job --list检查作业（--show）
bin/sqoop job --show myjob执行作业（--exec）
bin/sqoop job --exec myjob免密执行job
sqoop 在创建 job 时，使用--password-file
检查sqoop的sqoop-site.xml是否存在如下配置：
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
<description>If true, allow saved passwords in the metastore.
</description>
</property>bin/sqoop job --create castjob1 -- import \
--connect jdbc:mysql://192.168.153.134:3306/userdb \
--username root \
--password-file /input/sqoop/pwd/castmysql.pwd \
--target-dirsqoopresule \
--table emp \
--m 1
