sqoop完整版学习笔记

1、安装

提前准备好Java和Hadoop环境

1.1、修改配置文件

解压Sqoop之后，到conf目录下拷贝一份sqoop-env-template.sh，添加如下配置

1.2、将mysql驱动包拷入

将你的mysql驱动包拷入sqoop的lib目录下

我们可以通过command来验证sqoop配置是否正确

bin/sqoop help

2、Mysql to HDFS

bin/sqoop list-databases \
--connect jdbc：mysql：//master:3306/ \
--username root \
--password 123456 

2.1、查询Mysql所有数据库

通常用于sqoop与mysql连通测试

sqoop list-databases \
--connect jdbc:mysql://master:3306/ \
--username root \
--password 123456

2.2、查询指定数据库中所有数据表

bin/sqoop list-tables \
--connect jdbc:mysql://master:3306/shtd_store \
--username root
--password 123456

2.3、全量导入mysql表到HDFS

如果不指定分隔符 默认分隔符为“，”

可以通过–fields-terminated-by

bin/sqoop import \
--connect jdbc:mysql://master/company \
--username root \
--password 123456 \
--target-dir /user/test/result \ #存放在HDFS目录下
--fields-terminated-by '\t' #以制表符分隔数据
--table staff \
--m 1

-m 为并行度

如果指定为>=2,表中却没有主键，我们需要指定按那个字段来分

bin/sqoop import \
--connect jdbc:mysql://master/company \
--username root \
--password 123456 \
--target-dir /user/test/result \ #存放在HDFS目录下
--fields-terminated-by '\t' #以制表符分隔数据
--split-by id #按id来分隔
--table staff \
--m 2

3、Mysql to hive

3.1、Mysql数据全量导入到Hive

1、方式一：先复制表结构到hive再导入数据

将关系型数据库表结构复制到hive中

bin/sqoop create-hive-table \
--connect jdbc:mysql://master:3306/company
--table staff \ #mysql 的staff表
--username root \
--password 123456 \
--hive-table ods.staff  #自动在 hive 下 obs库下创建一个对应的user表

导入数据

bin/sqoop import \
--connect jdbc:mysql://master:3306/company \
--username root \
--password 123456 \
--table staff \
--hive-table ods.staff \
--hive-import \ #执行hive的数据导入
-m 1

2、方式二：直接复制表结构数据到hive中

bin/sqoop import \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--table staff \
--hive-import \ 
-m 1 \
--hive-database ods; # sqoop 1.4.2无法使用此命令

2、导入命令

bin/sqoop import \
--connect jdbc:mysql://master:3306/shtd_store \
--username root \
--password 123456 \12
--table CUSTOMER \        # 待导入的表     
--target-dir /sqoop_hive  \   # 临时目录位置
--hive-database ods \  # 导入到 Hive 的 sqoop_test 数据库，数据库需要预先创建。不指定则默认为 default 库
--hive-import \               # 导入到 Hive
--hive-overwrite \            # 如果 Hive 表中有数据则覆盖，这会清除表中原有的数据，然后再写入
-m 1                          # 并行度

4、导入

4.2、导入表数据子集（where 过滤）

–where 可以指定从关系型数据库导入数据时的查询条件。

它执行在数据库中相应的sql查询，并将结果存在hdfs的目标目录

bin/sqoop import \
--connect jdbc:mysql://master:3306/company \
--username root \
--password 123456 \
--where "name='Thomas'" \
--target-dir /user/test/resule5 \
--table staff \
--m 1

4.3、导入表数据子集（query查询）

1、使用query sql语句来进行查询不能加参数–table

2、必须要添加where条件

3、where条件后面必须要带一个$CONDITIONS 这个字符串；

4、sql语句必须为单引号

bin/sqoop import \
--connect jdbc:mysql://master:3306/company \
--username root \
--password 123456 \
--query 'select name from staff WHERE $CONDITIONS' \
--target-dir /user/test/resule5 \
--m 1

4.4、增量导入

–check-column(col)

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

注意：被指定的列类型不能为任意字符型，同时–check-column可以去指定多个列

–incremental（mode）

append：追加，从指定值后进行追加导入

lastmodified：最后的修改时间，从指定日期之后记录

–last-value（value）

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

bin/sqoop import \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--target-dir /user/test/result7 \
--table staff  \
--m 1

1、append（追加）

我们往staff上插入两条数据

insert into company.staff(name,sex) value('zpr1','woman');
insert into company.staff(name,sex) value('zy1','woman');

bin/sqoop import \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--target-dir /user/test/result7 \
--table staff \
-m 1
--incremental append \
--check-column id \ #按 id
--last-value 6 #这里默认以 6的后一位插入

1、lastmodified模式导入（最后修改时间）

• 我们先创建一个customer表，指定一个时间戳字段；

create table customertest(id int,name varchar(20),last_mod timestamp default current_timestamp on update current_timestamp);

• 插入几条数据

insert into customer(id,name) values(1,'neil')
insert into customer(id,name) values(2,'jack')
insert into customer(id,name) values(3,'tony')
insert into customer(id,name) values(4,'nick')

• 导入到hdfs

bin/sqoop import \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--target-dir /user/test/result8
--table costomer
--m 1

• 我们在插入一条数据

insert into customer(id,name) values(5,'simple');

• 使用incremental 的方式进行增量导入

bin/sqoop import \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--target-dir /user/test/result8 \
--table costomer \
--check-column last_mod \ #根据last_mode字段
--incremental lastmodified \ #增量导入 上次修改的日期
--last-value "2022-04-28 17:08:06" #最后时间
--m 1 \
--append #追加

注意 这里的last_value是大于等于 设置的时间 注意数据重复问题

2、merge-key（合并）

• 为了演示，我们首先去更新ID为1的name字段

update customer set name = 'Neil' where id = 1;

bin/sqoop import \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--target-dir /user/test/result8 \
--table costomer \
--check-column last_mod \ #检测last_mode字段
--incremental lastmodified \ #增量导入 上次修改的日期
--last-value "2022-04-28 17:08:06" \ #最后时间
--merge-key id #根据id合并
--m 1 \
--merge-key id

5、导出

export 三种模式：

​ 默认操作是将文件中的数据使用INSERT语句插入到表中

​ 更新模式：Sqoop将生成UPDATE替换数据库中现有的记录的语句

​ 调用模式：Sqoop将为每条记录创建一个存储过程的调用

相关配置参数

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

​ 指定文件中的分隔符

​ --columns

​ 文件和目标表字段顺序一致时，不用写

​ --export-dir

​ 导出的目录

​ --input-null-string

​ --input-null-non-string

​ 对于数据中的空值，我们使用 --input-null-string “\ \n”

5.1 默认模式导出 HDFS to Mysql

注意：导出的目标表需要自己手动提前创建，sqoop不会帮我们创建复制表结构

​ 1、准备HDFS数据

1201,gopal,manager,50000,TP
1202,manisha,preader,50000,TP
1203,kalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
1206,satishpr,grpdes,20000,GR

​ 2、手动创建mysql中的目标表

create table employee(id int no null primart key,name varchar(20),deg varchar(20),salary int,dept varchar(10))

​ 3、从hdfs中导出

bin/sqoop export \
--connect jdbc:mysql://master:3306/company \
--username root \
--password 123456 \
--table employee \
--export-dir /emp_data

5.2 更新导出（updateonly模式） HDFS to Mysql

只更新已经存在的数据，不会执行insert增加的新数据

参数说明

​ --update-key 更新表示，根据某个字段来进行更新

​ --updatemod，指定updateonly，仅仅更新已存在的数据记录，不会插入新的记录

​ 1、准备HDFS数据，建立updateonly_1.txt文件上传

1201,gopal,manager,50000
1202,manisha,preader,50000
1203,kalil,php dev,30000

​ 2、手动创建mysql中的目标表

create table updateonly(id int no null primart key,name varchar(20),deg varchar(20),salary int)

​ 3、从hdfs中导出

bin/sqoop export \
--connect jdbc:mysql://master:3306/company \
--username root \
--password 123456 \
--table updateonly \
--export-dir /updateonly_1/

​ 4、新建一个文件，建立updateonly_2文件上传

#这里修改了前三条数据并
1201,gopal,manager,1212
1202,manisha,preader,1313
1203,kalil,php dev,1414
1204,allen,java,1515

​ 5、执行更新导出

bin/sqoop export \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--export-dir /updateonly_2/ \
--update-key id \
--update-mode updateonly

5.2 更新导出（allowinsert模式） HDFS to Mysql

参数说明

​ --update-key 根据某个字段来进行更新

​ --updatemode，指定allowinsert，更新已存在的数据记录，同时插入新记录，实质是一个insert & update的操作

​ 1、准备HDFS数据，建立allowinsert_1.txt文件上传

1201,gopal,manager,50000
1202,manisha,preader,50000
1203,kalil,php dev,30000

​ 2、手动创建mysql中的目标表

create table allowinsert(id INT NOT NULL PRIMARY KEY,name VARCHART(20),deg VARCHAR(20),Ssalary INT);

​ 4、新建一个文件，建立updateonly_2文件上传

#这里修改了前三条数据并
1201,gopal,manager,1212
1202,manisha,preader,1313
1203,kalil,php dev,1414
1204,allen,java,1515

​ 5、执行更新导出

bin/sqoop export \
--connect jdbc:mysql://master:3306/company
--username root \
--password 123456 \
--export-dir /updateonly_2/ \
--update-key id \
--update-mode allowinsert

6、job作业

6.1、创建job

#注意import前有空格
bin/sqoop job 
--create taskjob
-- import
--connect jdbc:mysql://master:3306/company \
--username root \
--target-dir /user/test/ \
--table staff  \
-m 1

6.2、验证作业（–list）

–list 参数用来验证保存的作业

bin/sqoop job --list

6.3、验证作业（–show）

–show 用于检查或验证待定的工作，以及详细信息

bin/sqoop job --show taskjob

6.4、执行作业（–exec）

–exec用于执行保存的作业

bin/sqoop job --exec taskjob

sqoop 需要输入mysql密码

6.5、免密执行

7、脚本打包

1、创建一个.opt文件

mkdir opt

touch opt/job.opt

2、编写sqoop脚本

#修改
vi job.opt
#编写
export
--connect jdbc

3、执行sqoop脚本

bin/sqoop --options-file opt/job.opt

常见报错

1、jar包不匹配错误

将hive的libthrift-*.jar 放到 sqoop的目录下

2、临时文件已存在错误

解决办法：1、 --target-dir 重新指定路径

​ 2、删掉hdfs上的临时文件

3、并行度问题

代码如下：

如果使用并行度大于2时 表中又没有主键

需要使用 --split-by 字段