大数据技术之Sqoop

一.Sqoop简介

Sqoop主要用做数据传递，它可以将HDFS中的数据导入到关系型数据库（MySQL、Oracle等），也可以将关系型数据库的数据导进HDFS

Sqoop官方现在已经停止推进，但这不意味着Sqoop的弃用

Sqoop2的最新版本是1.99.7，但特征不完整，较少使用

Sqoop原理

将导入或者导出命令翻译成MapReduce程序来实现，主要对inputformat和outputformat进行定制

二.Sqoop安装

安装前确保系统有Java和Hadoop的环境

下载地址

http://archive.apache.org/dist/sqoop/1.4.7/

1.上传，解压

[gzhu@hadoop102 module]$ tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C .

2.修改配置文件

[gzhu@hadoop102 conf]$ mv sqoop-env-template.sh sqoop-env.sh

vim sqoop-env.sh

export HADOOP_COMMON_HOME=/opt/module/hadoop-3.1.3#Set path to where hadoop-*-core.jar is availableexport HADOOP_MAPRED_HOME=/opt/module/hadoop-3.1.3#set the path to where bin/hbase is availableexport HBASE_HOME=/opt/module/hbase-2.0.5#Set the path to where bin/hive is availableexport HIVE_HOME=/opt/module/hive#Set the path for where zookeper config dir isexport ZOOCFGDIR=/opt/module/zookeeper-3.5.7export ZOOKEEPER HOME=/opt/module/zookeeper-3.5.7

3.拷贝JDBC驱动

[gzhu@hadoop102 conf]$ cp /opt/module/mysql/mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.7/lib

4.验证Sqoop

[gzhu@hadoop102 bin]$ ./sqoop-help

出现以下内容即为成功

5.测试是否可以连接到数据库

[gzhu@hadoop102 bin]$ ./sqoop list-databases --connect jdbc:mysql://175.178.154.194:3306/ --username root --password ujhyfgc

三.Sqoop应用

1.全量导入

在Sqoop中，导入的概念是从非大数据集群（RDBMS）向大数据集群（HDFS，HIVE，HBASE）中传输数据，用import关键字

1.1 MySQL导入HDFS

（1）全量导入

如图，MySQL有张表student

执行以下命令

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--table student \ # MySQL表--target-dir /mysql/student \# HDFS目录--num-mappers 1 \ # task个数--fields-terminated-by "," # 分隔符

（2）query查询导入

bin/sqoop import --connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--target-dir /mysql/query \--num-mappers 1 \--fields-terminated-by "," \--query 'select * from student where age >= 20 and $CONDITIONS;'

如果query后面是单引号，那么$CONDITIONS前面不需要反斜杠，双引号必须加反斜杠

将数据库中的数据导入到HDFS，如果使用CONDITIONS参数，就能保证数据库中数据的顺序与 HDFS 中导入数据的顺序是一致的

（3）导入指定列和where过滤

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--target-dir /mysql/columns \--num-mappers 1 \--fields-terminated-by "," \--where "age >= 20" \# 可以不同查询直接用where过滤--table student \--columns id,name# 选择指定的列

1.2 MySQL导入到Hive

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--table student \--num-mappers 1 \--hive-import \--fields-terminated-by "\t" \--hive-overwrite \--hive-database gzhu \ # hive数据库--hive-table hive_student# hive表

其实是先将数据导入到了HDFS，再从HDFS导入到了Hive

1.3 MySQL导入到HBase

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--table student \--columns "id,name,age" \--column-family "info1" \--hbase-row-key "id" \--hbase-table "t1" \--num-mappers 1 \--split-by id

如果Hbase版本在1.0.1之上，我们先手动在Hbase创建表

2.增量导入

在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表中的数据每次都全部导入到hive或者hdfs当中去，这样会造成数据重复的问题。因此一般都是选用一些字段进行增量的导入， sqoop支持增量的导入数据

增量导入是仅导入新添加的表中的行的技术

–check-column (col)

用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似

注意:这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时– check-column可以去指定多个列

–incremental mode

• append：追加，比如对大于last-value指定的值之后的记录进行追加导入
• lastmodified：最后的修改时间，追加last-value指定的日期之后的记录

–last-value (value)

指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

2.1 append模式

我们在全量导入的时候，已经把student表全量导入到了HDFS

我们添加两条新的数据

我们检查id这一列，大于7的才会进行追加导入

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--table student \--target-dir /mysql/student \--num-mappers 1 \ --incremental append \--check-column id \--last-value 7 \--fields-terminated-by "," 

--append只会将大于last-value的值导入

2.2 lastmodified模式

如图有下表

我们新增一条数据，修改一条数据

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--table teacher \--target-dir /mysql/teacher \--incremental lastmodified \--check-column last_mod \--last-value '2022-07-06 11:00:00' \--fields-terminated-by ","\--num-mappers 1 \ --append

注意：lastmodified模式，会将大于等于last-value的值导入

在lastmodified模式下，分为–append和–merge-key模式

• append会将last-value之后新增的数据、修改的数据追加到HDFS的一个新文件里
• merge-key会将last-value之后新增、修改的数据都会增量追加进去，且都会在一个文件里面
  

bin/sqoop import \--connect jdbc:mysql://175.178.154.194:3306/sqoopdb \--username root \--password zks123456 \--table teacher \--target-dir /mysql/teacher \--incremental lastmodified \--check-column last_mod \--last-value "2022-07-06 11:00:00" \--fields-terminated-by ","\--num-mappers 1 \ --merge-key id

由于merge-key模式是进行了一次完整的mapreduce操作，因此在last-value之后新增、修改的数据都会被增量追加进去，且都会在一个文件里面

3.导出

3.1 insert导出

默认情况下，sqoop export将每行输入记录转换成一条INSERT语句，添加到目标数据库表中。如果数据库中的表具有约束条件（例如，其值必须唯一的主键列）并且已有数据存在，则必须注意避免插入违反这些约束条件的记录。如果INSERT语句失败，导出过程将失败。此模式主要用于将记录导出到可以接收这些结果的空表中。通常用于全表数据导出

导出时可以是将Hive表中的全部记录或者HDFS数据（可以是全部字段也可以部分字段）导出到MySQL目标表

Hive/HDFS => MySQL ，关键字export，不支持Hbase到MySQL，目标表一定要在MySQL存在

bin/sqoop export \--connect jdbc:mysql://175.178.154.194:3306/flink \--username root \--password zks123456 \--table student \--num-mappers 1 \--export-dir /user/hive/warehouse/gzhu.db/student \--input-fields-terminated-by ","

3.2 相关配置参数

指定文件中的分隔符

–input-fields-terminated-by ‘\t’

–columns

选择列并控制它们的排序。当导出数据文件和目标表字段列顺序完全一致的时候可以不写。没有被包含在–columns后面列名或字段要么具备默认值，要么就允许插入空值。否则数据库会拒绝接受sqoop导出的数据，导致Sqoop作业失败

假如 数据库字段按顺序是 id name phone
而HDFS数据是 1,17852,kun 很明显顺序不一致
那么我们可以通过column这样指定

–colums id,phone,name就可以了

–export-dir 导出目录，在执行导出的时候，必须指定这个参数，同时需要具备–table或–call参数两者之一，–table是指的导出数据库当中对应的表，–call是指的某个存储过程

–input-null-string 和 –input-null-non-string

Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性

在导出数据时采用–input-null-string “\N” 和–input-null-non-string “\N”两个参数

导入数据时采用–null-string “\N”和–null-non-string “\N”

3.3 update导出

updateonly

参数说明

– update-key，更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔

– updatemod，指定updateonly（默认模式），仅仅更新已存在的数据记录，不会插入新纪录

bin/sqoop export \--connect jdbc:mysql://node-1:3306/userdb \--username root --password hadoop \--table updateonly \--export-dir /updateonly_2/ \--update-key id \--update-mode updateonly

更新导出（allowinsert模式）

参数说明
– update-key，更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔

– updatemod，指定allowinsert，更新已存在的数据记录，同时插入新纪录。实质上是一个insert & update的操作

bin/sqoop export \--connect jdbc:mysql://node-1:3306/userdb \--username root --password hadoop \--table allowinsert \--export-dir /allowinsert_2/ \--update-key id \--update-mode allowinsert

四.Sqoop Job

创建作业

注意import前面有空格

bin/sqoop job --create gzhujob -- import --connect jdbc:mysql://175.178.154.194:3306/sqoopdb --username root --password zks123456 \--table customertest --target-dir /mysql/customer --num-mappers 1 --fields-terminated-by ","

查看作业

bin/sqoop job --list

检查作业

bin/sqoop job --show gzhujob

删除

bin/sqoop job --delete gzhujob

执行作业

bin/sqoop job --exec gzhujob

免密执行

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 gzhujob -- import --connect jdbc:mysql://cdh-1:3306/userdb \--username root \--password-file /input/sqoop/pwd/itcastmysql.pwd \--target-dir /sqoopresult333 \--table emp --m 1