安装 配置
[root@gree139 ~]# tar -zxf /opt/install/sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/soft/
[root@gree139 ~]# cd /opt/soft
[root@gree139 soft]# cd ./sqoop146/conf/
[root@gree139 conf]# mv sqoop-env-template.sh sqoop-env.sh
[root@gree139 conf]# vi ./sqoop-env.sh
配置之前所学
#Set path to where bin/hadoop is available
23 export HADOOP_COMMON_HOME=/opt/soft/hadoop260
24
25 #Set path to where hadoop-*-core.jar is available
26 export HADOOP_MAPRED_HOME=/opt/soft/hadoop260
27
28 #set the path to where bin/hbase is available
29 export Hbase_HOME=/opt/soft/hbase120
30
31 #Set the path to where bin/hive is available
32 export HIVE_HOME=/opt/soft/hive110
33
34 #Set the path for where zookeper config dir is
35 export ZOOCFGDIR=/opt/soft/zookeeper345/conf
[root@gree139 conf]# cp /opt/soft/hive110/lib/mysql-connector-java-5.1.25.jar ../lib/
[root@gree139 conf]# vi /etc/profile
#sqoop
export SQOOP_HOME=/opt/soft/sqoop146
export PATH=$PATH:$SQOOP_HOME/bin
[root@gree139 conf]# sqoop version
[root@gree139 conf]# sqoop help
[root@gree139 conf]# sqoop help list-databases
-------------------------------------------
[root@gree139 ~]# sqoop list-databases --connect jdbc:mysql://gree139:3306 --username root --password root
查看数据库
[root@gree139 ~]# sqoop list-databases
--connect jdbc:mysql://gree139:3306
--username root
--password root
查看数据库中的表
-------------------------------------------
[root@gree139 ~]# sqoop list-tables
> --connect jdbc:mysql://gree139:3306/mybatisdb
> --username root
> --password root
----------------将mysql数据库中的表数据导入到HDFS---------------------------
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--delete-target-dir
--target-dir /sqoop/demo1
--m 1
-------------------------------------------
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--delete-target-dir
--target-dir /sqoop/demo2
--split-by classId
--fields-terminated-by 't'
--m 2
------------mysql 导入数据到hive (一,先创建hive表，导入数据)-------------------------------
在hive中创建表kb15.student_mysql，数据结构与mysql中student相同
[root@gree139 ~]# sqoop create-hive-table
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--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@gree139 ~]# sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--hive-table kb15.student_mysql
--hive-import
--m 1
---------------mysql 导入数据到hive (二,创建hive表导入数据，一次性完成)----------------------------
[root@gree139 ~]# sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--hive-import
--hive-database kb15
--m 1
-------------where------------------------------
[root@gree139 ~]# sqoop import
> --connect jdbc:mysql://gree139:3306/mybatisdb
> --username root
> --password root
> --table student
> --where "classId=1"
> --target-dir /sqoop/demo3
> --delete-target-dir
> --m 1
[root@gree139 ~]# sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--where "telephone='1392323224'"
--target-dir /sqoop/demo4
--delete-target-dir
--m 1
[root@gree139 ~]# sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--where "telephone='1392323224' and classId=1"
--target-dir /sqoop/demo5
--delete-target-dir
--m 1
-------------- query -----------------------------
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--query 'select id,name,age,gender,telephone,email,classId from student where id>3 and classId=1 and $CONDITIONS'
--target-dir /sqoop/demo6
--delete-target-dir
--m 1
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--query 'select id,name,age,gender,telephone,email,classId from student where id>3 and classId=1 and $CONDITIONS'
--target-dir /sqoop/demo7
--delete-target-dir
--fields-terminated-by 't'
--split-by classId
--m 1
-----------增量导入--------------------------------
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student
--delete-target-dir
--target-dir /sqoop/incre1
--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://gree139:3306/mybatisdb
--username root
--password root
--table student
--target-dir /sqoop/incre1
--incremental append
--check-column id
--last-value 9
--m 1
-------------incremental增量导入 lastmodified ------------------------------
create table student2(
id int,
name varchar(32),
last_mod timestamp default current_timestamp on update current_timestamp
)
insert into student2(id,name) values(1,'zhangxiaohua'),(2,'litiechui');
select * from student2;
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student2
--delete-target-dir
--target-dir /sqoop/incre2
--m 1
insert into student2(id,name) values(3,'zhaodaqiang'),(4,'chenxiaowang');
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student2
--target-dir /sqoop/incre2
--incremental lastmodified
--check-column last_mod
--last-value "2021-11-23 11:56:25"
--append
--m 1
-----------------incremental增量导入 lastmodified --------------------------
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student2
--delete-target-dir
--target-dir /sqoop/incre3
--m 1
insert into student2(id,name) values(5,'zhaoweiwei'),(6,'liangshanbo');
UPDATE student2 set `name`='zhanghua' WHERe id=1;
sqoop import
--connect jdbc:mysql://gree139:3306/mybatisdb
--username root
--password root
--table student2
--target-dir /sqoop/incre3
--incremental lastmodified
--check-column last_mod
--last-value "2021-11-23 12:00:56"
--merge-key id
--m 1
