单机版Sqoop安装、配置、导入使用笔记

安装 配置
[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   <append  ,  merge-key> ------------------------------
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   <append  ,  merge-key> --------------------------

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