1、简介
sqoop是Hadoop生态体系和RDBMS体系之间传送数据的一种工具。
工作机制: 将导入导出命令翻译成MR程序来实现,MR中主要是对inputformat和outputformat进行定制。
Hadoop生态体系包括:HDFS、HBase、Hive等
RDBMS体系包括:MySQL、Oracle等
整体架构如下:
sqoop与dataX对比
DataX 是阿里开源的一个异构数据源离线同步工具,致力于实现包括关系型数据库(MySQL、Oracle等)、HDFS、Hive、ODPS、HBase、FTP等各种异构数据源之间稳定高效的数据同步功能。
项点 | Sqoop | dataX |
---|---|---|
模式 | 分布式 | 单进程 多线程 |
流量控制 | 无 | 有 |
对Hadoop大数据支持 | 好 | 一般 |
性能 | 高 | 一般 |
2、安装
下载安装包地址:https://archive.apache.org/dist/sqoop/1.4.7/
下载包名为sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz,别下错了!否则会有大坑
修改conf目录下配置文件,将sqoop-env-template.sh文件名改为sqoop-env.sh,添加环境变量
export HADOOP_COMMON_HOME=/export/server/hadoop-3.3.0
export HADOOP_MAPRED_HOME=/export/server/hadoop-3.3.0
export HIVE_HOME=/export/soft/apache-hive-3.1.2-bin
export HIVE_CONF_DIR=/export/soft/apache-hive-3.1.2-bin/conf
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*
之后输入下面命令,查看sqoop版本信息。
/export/soft/sqoop-1.4.7/bin/sqoop version
修改mysql
让任意一台节点都能访问mysql,并修改mysql时区
use mysql;
update user set host = ‘%’ where user = 'root';
FLUSH PRIVILEGES;
set global time_zone='+8:00';
命令行输入下面命令,查看当前所有数据库来验证安装。
/export/soft/sqoop-1.4.7/bin/sqoop list-databases --connect jdbc:mysql://192.168.132.1:3306/ --username root --password 你自己的密码
3、全量数据导入
3.1、MySQL->HDFS
指定一个MR程序(–m 1)将MySQL中的schema数据库中的user表导入HDFS中的testSqoop文件夹中
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --target-dir /testSqoop --m 1
如果要使用多个MR程序实现数据导入,需要指定根据哪个字段进行横向分割数据库,使用命令
--split-by 字段名
默认HDFS中文件的分隔符为逗号,可使用
--fields-terminated-by
来自定义分隔符
3.2、MySQL->Hive
方法一:
-
MySQL表结构导入Hive
./sqoop create-hive-table --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --hive-table test.user
-
MySQL数据导入Hive
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --hive-table test.user --hive-import --m 1
方法二:
不创建表结构,直接导入
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user_copy --hive-database test --hive-import --m 1
3.3、导入部分数据
1、用where过滤
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --target-dir /testSqoopWhere --m 1 --where "age=11"
2、使用query过滤
注意:使用query不加–table参数、必须加where条件、where条件后必须加$CONDITIONS字符串、sql语句前后必须使用单引号不能使用双引号
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --target-dir /testSqoopQuery --m 1 --query 'select id,name from user where age=130 and $CONDITIONS'
4、增量数据导入
–check-column(col)
用来指定一些列,这些列在增量导入时用来检查这些数据是否作为增量数据进行导入,和RDBMS中的自增字段和时间戳类似。
这些列不能是字符类型,可以指定多列。
–incremental(mode)
append:追加,比如对大于大于指定值的记录进行追加
lastmodified:最后的修改时间,可用来追加指定日期之后的记录
–last-value(value)
指定从上次导入后列的最大值,可自定义
4.1、append模式增量导入
使用3.3中where过滤的数据进行追加,原来where过滤后只有5条数据
使用如下追加语句
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --target-dir /testSqoopWhere --m 1 --incremental append --check-column id --last-value 1
发现id>1的都被追加(即使原来已经存在的数据也被追加)并存到一个新文件中,并未改变原来where过滤的文件。
4.2、lastmodified模式增量导入
还使用3.3中where过滤的数据进行追加,追加语句如下:
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --target-dir /testSqoopWhere --m 1 --incremental lastmodified --check-column update_time --last-value "2022-03-23 20:36:24" --append
发现时间**>=**2022-03-23 20:36:24的都被追加。
注意:append模式追加时是大于last-value,而lastmodified模式追加时是大于等于last-value!!!
4.3、lastmodified模式中的append和merge-key
append:在文件夹内新建文件进行追加
merge-key:在同一个文件内进行追加合并,同步数据的数值变化和行数变化,会避免数据的重复问题。
./sqoop import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --target-dir /testSqoopWhere --m 1 --incremental lastmodified --check-column update_time --last-value "2022-04-13 14:32:13" --merge-key id
5、数据导出(HSFS/Hive->RDBMS)
数据导出前,目标表必须已存在。
export三种模式:
- 默认模式:将文件中的数据使用insert导入到RDBMS
- 更新模式:生成update语句更新数据库中的数据
- 调用模式:为每条记录创建一个存储过程调用
语法:
$ sqoop export (generic-args) (export-args)
5.1、默认模式
使用4.3中的合并后的数据文件导入到MySQL中,命令如下:
./sqoop export --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table sqoop --export-dir /testSqoopWhere/
相关参数:
1、–input-field-terminated-by ‘/t’:指定文件中的分隔符
2、–column :选择列并进行列排序。如果HDFS中的字段顺序与MySQL中的字段顺序不一致,需要使用该参数保证相同字段对应。
./sqoop export --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table sqoop ----column id,name,age,email,create_time,update_time,version,deleted --export-dir /testSqoopWhere/
3、–export-dir:导出目录。同时要具备–table或–call,–table指定导出到数据库的哪个表,–call指的是某个存储过程。
4、–input-null-string:如果没有指定,对于字符串类型的列,“null”这个字符串会被翻译成数据库中的空值
5、 --input-null-non-string:如果无论是没有指定,“null”这个字符串还是空字符串,对于非字符串类型字段都会被翻译成数据库中的空值
一般使用–input-null-string “\\N” --input-null-non-string “\\N”
5.2、更新模式
参数说明:
- –update-key:更新标识,根据某个字段进行更新
- –updatemod,指定updateonly(默认),只更新已存在的数据,不插入新纪录
修改MySQL中的sqoop表后,再导出查看变化
./sqoop export --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table sqoop --export-dir /testSqoopWhere/ --update-key id --update-mode updateonly
updateonly只是根据HDFS中id字段进行查找,发现数据不一致进行更改,HDFS中不在MySQL中的id所在行不受影响,不会进行insert操作。
allowinsert模式:update&insert
注意:数据库要设置主键,否则数据会重复。
./sqoop export --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table sqoop --export-dir /testSqoopWhere/ --update-key id --update-mode allowinsert
6、job作业
将导入、导出命令作为一个作业进程进行管理。
-
创建作业
运行命令
./sqoop job --create testjob1 -- import --connect jdbc:mysql://192.168.132.1:3306/schema --username root --password 123 --table user --target-dir /testJob
如果报错ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException,需要导入java-json.jar,自己在网上下就行,下载地址:http://www.java2s.com/Code/Jar/j/Downloadjavajsonjar.htm#google_vignette
-
查看job列表
./sqoop job --list
-
job执行
./sqoop job --exec testjob1
会要求输入密码,密码为mysql的密码。
取消密码验证需要配置conf文件中的sqoop-site.xml,在configuration标签中添加
<configuration> <property> <name>sqoop.metastore.client.record.password</name> <value>true</value> <description>If true, allow saved passwords in the metastore. </description> </property> </configuration>
-
job删除
./sqoop job --delete testjob
-
查看job
./sqoop job --show testjob