本文主要是介绍七十五、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
这篇关于七十五、Sqoop——Sqoop导入数据和导出数据（内有每条语句的截图，一目了然）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
