大数据技术之Sqoop——SQL to Hadoop

一、简介

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 123123

3.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 1

4.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_hive

4.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 student

4.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 id

merge-key模式进行了一次完整了mapreduce操作。

关于lasimodified中的两种模式：

append只会追加数据到一个新的文件中，并且会产生数据的重复问题

因为默认是从指定的last-value大于等于其值的数据开始导入

merge-key 把增量的数据合并到一个文件中。处理追加增量数据之外，如果之前的数据有变化，也可以进行修改操作。底层相当于进行了一次完整的mr作业，数据不会重复。

五、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 更新已有的数据，插入新的数据，底层相当于insert&update

--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 updateonly

updateonly 只更新已经存在的数据，不会执行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 allowinsert

allowinsert 更新已有的数据，插入新的数据，底层相当于insert&update

六、sqoop job作业

1. 创建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

1. 验证作业（--list）

bin/sqoop job --list

1. 检查作业（--show）

bin/sqoop job --show myjob

1. 执行作业（--exec）

bin/sqoop job --exec myjob

1. 免密执行job

sqoop 在创建 job 时，使用--password-file 参数，可以避免输入 mysql 密码，如果使用--password将出现警告，并且每次都要手动输入密码才能执行job，sqoop规定密码文件必须存放在 HDFS 上，并且权限必须是 400。

检查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