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   ------------------------------
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   --------------------------

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