Sqoop的用法

hdfs 和 mysql 相互导入和导出

------------- hdfs---> mysql----------------
--建表
create table sqp_order(
creat_date date,
user_name varchar(20),
total_volume decimal(10,2)
)
---全都导
sqoop export \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--table sqp_order \
-m 1 \
--export-dir /kb12/sqp_order \
--fields-terminated-by '\t' 


------------ mysql -> hdfs ------------------
# 全导入
sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--table sqp_order \
-m 1 \
--delete-target-dir \
--target-dir /kb12/sqp_order2 \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'
	

# 列裁剪
sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--table sqp_order \
--columns user_name \
-m 1 \
--delete-target-dir \
--target-dir /kb12/sqp_order3 \
--fields-terminated-by ',' \
--lines-terminated-by '\n'	

	
#行裁剪
sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--table sqp_order \
--columns user_name,total_volume \
--where "total_volume>=200" \
-m 2 \
--split-by user_name \
--delete-target-dir \
--target-dir /kb12/sqp_order4 \
--fields-terminated-by ',' \
--lines-terminated-by '\n' 
	
	
sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--query "select user_name,total_volume from sqp_order where total_volume>=300 and \$CONDITIONS" \
-m 2 \
--split-by user_name \
--delete-target-dir \
--target-dir /kb12/sqp_order5 \
--fields-terminated-by ',' \
--lines-terminated-by '\n' 


#增量导入 append|merge
sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--query "select * from studentinfo where \$CONDITIONS" \
-m 1 \
--target-dir /kb12/sqp_order6 \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--check-column stuId \
--incremental append \
--last-value 0 

insert into studentinfo values
(50,'11',32,'男','14568758231',25201,6),
(51,'12',28,'男','15314381031',23489,7);


sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \
--query "select * from studentinfo where \$CONDITIONS" \
-m 1 \
--target-dir /kb12/sqp_order6 \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--check-column stuId \
--incremental append \
--last-value 48 

#批量增加lastmodified(针对日期增加)
create table sqp_incr_time(
incrName varchar(20),
incrTime timestamp
);

insert into sqp_incr_time(incrName) values('m'),('h'),('c'),('zhangsan');
insert into sqp_incr_time() values 


sqoop import \
--connect jdbc:mysql://mch:3306/test \
--username root \
--password 12345678 \ 
--query "select * from sqp_incr_time where \$CONDITIONS" \
-m 1 \
--target-dir /kb12/sqp_order7 \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--check-column incrTime \
--incremental lastmodified \
--last-value '0000-00-00 00:00:00'