七十五、Sqoop——Sqoop导入数据和导出数据（内有每条语句的截图，一目了然）

sqoop安装好后先测试一下

[root@gree128 conf]# sqoop version
[root@gree128 conf]# sqoop help
[root@gree128 conf]# sqoop help list-databases

展示mysql的数据库
[root@gree128 ~]# sqoop list-databases \
--connect jdbc:mysql://gree128:3306 \
--username root \
--password 123456

-------------------------------------------
[root@gree128 ~]# sqoop list-tables \
> --connect jdbc:mysql://gree128:3306/mybatisdb \
> --username root \
> --password root

----------------将mysql数据库中的表数据导入到HDFS---------------------------
sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--delete-target-dir \
--target-dir /sqoop/d1 \
--m 1

--------------将表数据导入到HDFS,并且分为两个文件-----------------------------
sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--delete-target-dir \
--target-dir /sqoop/demo2 \
--split-by classId \
--fields-terminated-by '\t' \
--m 2

------------mysql 导入数据到hive (一,先创建hive表，导入数据)-------------------------------
先创建表kb15.student_mysql，数据结构与mysql中student相同
[root@gree128 ~]# sqoop create-hive-table \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--hive-table kb15.student_mysql 

hive> show tables;
OK
student_mysql

hive> desc student_mysql;
OK
id                      int                                         
name                    string                                      
age                     int                                         
gender                  string                                      
telephone               string                                      
email                   string                                      
classid                 int           

[root@gree128 ~]# sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--hive-table kb15.student_mysql \
--hive-import \
--m 1

---------------mysql 导入数据到hive (二,创建hive表导入数据，一次性完成)----------------------------
导入到student表
[root@gree128 ~]# sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--hive-import \
--hive-database kb15 \
--m 1

查找条件  where
-------------where------------------------------
[root@gree128 ~]# sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--where "classId=1" \
--target-dir /sqoop/d3 \
--delete-target-dir \
--m 1

[root@gree128 ~]# sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--where "telephone='13613309876'" \
--target-dir /sqoop/d4 \
--delete-target-dir \
--m 1

[root@gree128 ~]# sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--where "telephone='13613309876' and classId=2" \
--target-dir /sqoop/d5 \
--delete-target-dir \
--m 1

-------------- query -----------------------------
sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--query 'select id,name,age,gender,telephone,email,classId from student where id>3 and classId=4 and $CONDITIONS' \
--target-dir /sqoop/d6 \
--delete-target-dir \
--m 1

sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--query 'select id,name,age,gender,telephone,email,classId from student where id>3 and classId=4 and $CONDITIONS' \
--target-dir /sqoop/d7 \
--delete-target-dir \
--fields-terminated-by '\t' \
--split-by classId \
--m 1

-----------增量导入--------------------------------
sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--delete-target-dir \
--target-dir /sqoop/in1 \
--m 1
insert into student(name,age,gender,telephone,email,classId) values
("刘勇",24,"男","13695847598","liuyong@qq.com",1),
("刘小勇",2,"男","1360000000","liuxiaoyong@qq.com",1)

sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--target-dir /sqoop/in1 \
--incremental append \
--check-column id \
--last-value 4 \
--m 1

-------------incremental 增量导入 lastmodified   <append  ,  merge-key> ------------------------------
create table student3(
id int,
name varchar(32),
last_mod timestamp default current_timestamp on update current_timestamp
)

insert into student3(id,name) values(1,'zhangxiaohua'),(2,'litiechui');

select * from student3;

sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student3 \
--delete-target-dir \
--target-dir /sqoop/in2 \
--m 1

insert into student3(id,name) values(3,'zhaodaqiang'),(4,'chenxiaowang');

sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student3 \
--target-dir /sqoop/in2 \
--incremental lastmodified \
--check-column last_mod \
--last-value "2021-11-24 17:08:34" \
--append \
--m 1

-----------------incremental增量导入   lastmodified   <append  ,  merge-key> --------------------------

sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student3 \
--delete-target-dir \
--target-dir /sqoop/incre3 \
--m 1

insert into student3(id,name) values(5,'zhaoweiwei'),(6,'liangshanbo');

导入全部的数据
sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student3 \
--target-dir /sqoop/incre3 \
--incremental lastmodified \
--check-column last_mod \
--last-value "2021-11-24 17:12:59" \
--merge-key id \
--m 1

导入新增的数据
sqoop import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student3 \
--target-dir /sqoop/incre3 \
--incremental lastmodified \
--check-column last_mod \
--last-value "2021-11-24 17:12:34" \
--append \
--m 1

------------导出 export---- allowinsert updateonly ---------------------------
[root@gree128 tmp]# vi ./teacher.txt 

1,gree,jy,dsj
2,cat,leader,dsj

[root@gree128 tmp]# hdfs dfs -mkdir  -p/sqoop/exportdata/
[root@gree128 tmp]# hdfs dfs -put ./teacher.txt /sqoop/exportdata/

[root@gree128 tmp]# sqoop export \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table teacher \
--export-dir /sqoop/exportdata/ \
--update-key id \
--update-mode allowinsert

先修改表数据，在运行语句
[root@gree128 tmp]# sqoop export \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table teacher \
--export-dir /sqoop/exportdata2/ \
--update-key id \
--update-mode updateonly  （表数据只读）

[root@gree128 tmp]# sqoop export \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table teacher \
--export-dir /sqoop/exportdata3/ \
--update-key id \
--update-mode allowinsert

-----------job --------------------------------

sqoop job --create demojob -- import \
--connect jdbc:mysql://gree128:3306/mybatisdb \
--username root \
--password 123456 \
--table student \
--delete-target-dir \
--target-dir /sqoop/job/ \
--m 1

sqoop job的基本语句

[root@gree128 tmp]# sqoop job --list
[root@gree128 tmp]# sqoop job --exec demojob          执行
[root@gree128 tmp]# sqoop job --delete demojob
[root@gree128 tmp]# sqoop job --show demojob