大数据技术之Sqoop

Jumanji_

已于 2022-07-07 13:05:15 修改

阅读量1.6k

点赞数 1

分类专栏：大数据框架文章标签：大数据 sqoop hadoop

于 2022-07-06 10:38:05 首次发布

本文链接：https://blog.csdn.net/ks_1998/article/details/125591042

版权

大数据框架专栏收录该内容

35 篇文章 23 订阅

订阅专栏

大数据技术之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 available
export HADOOP_MAPRED_HOME=/opt/module/hadoop-3.1.3
#set the path to where bin/hbase is available
export HBASE_HOME=/opt/module/hbase-2.0.5
#Set the path to where bin/hive is available
export HIVE_HOME=/opt/module/hive
#Set the path for where zookeper config dir is
export ZOOCFGDIR=/opt/module/zookeeper-3.5.7
export 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