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