sqoop完整版学习笔记

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上插入两条数据

insertinto company.staff(name,sex)value('zpr1','woman');insertinto 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表，指定一个时间戳字段；

createtable customertest(id int,name varchar(20),last_mod timestampdefaultcurrent_timestamponupdatecurrent_timestamp);

• 插入几条数据

insertinto customer(id,name)values(1,'neil')insertinto customer(id,name)values(2,'jack')insertinto customer(id,name)values(3,'tony')insertinto 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

• 我们在插入一条数据

insertinto 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中的目标表

createtable employee(id intnonull 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中的目标表

createtable updateonly(id intnonull 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中的目标表

createtable allowinsert(id INTNOTNULLPRIMARYKEY,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 字段