Apache sqoop简单使用介绍

介绍

sqoop是RDBMS（mysql,oracle等）和Hadoop生态体系（hdfs,hive,hbase）间数据传输的工具，
通过将导入导出命令翻译为mapreduce命令来执行

安装及配置

① 解压

② 修改环境变量
cd到conf文件夹下，重命名sqoop-env-template.sh为sqoop-env.sh,并添加安装的hadoop，hive，hbase等安装路径

export HADOOP_COMMON_HOME= /export/servers/hadoop-2.7.5 
export HADOOP_MAPRED_HOME= /export/servers/hadoop-2.7.5
export HIVE_HOME= /export/servers/hive

③ 测试

列出所有数据库

cd /export/servers/sqoop-1.4.6-cdh5.14.0/

bin/sqoop list-databases \
--connect jdbc:mysql://localhost:3306/ \
--username root --password ******

缺少三个依赖包，下载并复制到lib文件夹下

mysql连接
jason包
hive执行包 （hive依赖包下有hive-exec，复制到sqoop依赖文件夹下）

导入

全量导入

全量导入到HDFS

bin/sqoop import \				#导入命令
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \  #db位置和数据库名称
--username root \				#db用户名
--password ******\				#db密码
--target-dir /sqoopresult1 \	#hdfs保存路径
--table emp \					#MySQL中源数据表名
--m 1							#启动map个数

---

命令解释

–fields-terminated-by ‘\t’ 可以指定分隔符，默认逗号

–m 1 指定启动map数量，若是多个map，需指定map分割字段，推荐数字型主键

–split-by id 指定分割字段

全量导入到HIVE

方式一 先导入表结构，再导入数据

导入表结构

bin/sqoop create-hive-table \		#导入表结构
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \ #数据库
--table emp_add \					#数据库表名
--username root \	
--password ******\
--hive-table default.emp_add_sp		#导入目标的hive数据库.表名

导入表数据

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com:3306/sqooptest \
--username root \
--password ******\
--table emp_add \
--hive-table default.emp_add_sp \
--hive-import \
--m 1

默认分隔符\001

方式二 直接导入表

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com:3306/sqooptest \
--username root \
--password ******\
--table emp_conn \			#表名emp_conn
--hive-import \
--m 1 \
--hive-database default;	#数据库名为default

子集导入

where过滤

bin/sqoop import \					#导入命令
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \  #db位置和数据库名称
--username root \					#db用户名
--password ******\					#db密码
--target-dir /whereresult1 \		#hdfs保存路径
--where "dept='TP'"\				#字段过滤，仅导入符合要求的数据
--table emp \
--m 1								#启动map个数

query查询

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /queryresult1 \
--query 'select id,street,city from emp_add where city="sec-bad"and $CONDITIONS' \
--m 1

规定

• 出现–query不能出现–table（与–query内的from重复）
• SQL语句不能以双引号包括，只能用单引号
• 单引号内似乎不能出现单引号，是以单引号内含双引号交叉出现的，否则报错（实测）
• query语句中必须有where条件，无需则添加where 1=1之类
• where语句最后必须添加$CONDITIONS

增量导入

–check-column

指定一些列作为增量检查的字段，只能是时间或数字格式，可以指定多列

–incremental

append：追加，指定值之后的数追加导入

–last-value

自上次导入列后的最大值，或者自己设定

append模式

准备数据

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /appendresult1 \
--table emp_add \
--m 1

增加数据

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /appendresult1 \
--table emp_add \
--incremental append \ #有append和lastmodified两个选项，lastmodified如下
--check-column id \		#以ID列作为增量检查的字段
--last-value 1205 \		#原文件最后一个ID是1205，对大于此值的新增
--m 1

会在之前文件夹下新增一个文件，即为增量数据

lastmodified模式

append选项

准备数据

create table customertest(id int,name varchar(20),last_mod timestamp default current_timestamp on update current_timestamp);
#以上语法对timestamp列按系统时间默认，更新则也会自动修改此时间
insert into customertest(id,name) values(1,'neil');
insert into customertest(id,name) values(2,'jack');
insert into customertest(id,name) values(3,'martin');
insert into customertest(id,name) values(4,'tony');
insert into customertest(id,name) values(5,'eric');

导入

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /lastmodifiedresult1 \
--table customertest \
--m 1

增加

insert into customertest(id,name) values(6,'james');

导入增加数据

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table customertest \
--target-dir /lastmodifiedresult1 \
--check-column last_mod \
--incremental lastmodified \		
--last-value "2019-10-17 20:53:52" \	
--m 1 \
--append		#对大于等于last-value的新增,新建文件

merge-key选项

更新

update customertest set name = 'Neil' where id = 1;

导入增加数据

bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table customertest \
--target-dir /lastmodifiedresult1 \
--check-column last_mod \
--incremental lastmodified \		
--last-value "2019-10-17 20:53:52" \
--m 1 \
--merge-key id	#新增值新增，更新值更新，合并到一个新的-r-的文件中

导出

默认模式导出

HDFS->mysql 底层为insert一条条插入

myqsl需先手动建立对应的表

use sqooptest;
CREATE TABLE employee ( 
   id INT NOT NULL PRIMARY KEY, 
   name VARCHAR(20), 
   deg VARCHAR(20),
   salary INT,
   dept VARCHAR(10));

准备导出数据

vim empdata

1201,gopal,manager,50000,TP
1202,manisha,preader,50000,TP
1203,kalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
1206,satishp,grpdes,20000,GR

hadoop fs -put empdata /sqoopoutput

数据导出

bin/sqoop export \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \					#mysql中的表名
--export-dir /sqoopoutput/empdata	#导出数据的存放位置

其他参数

–input-fields-terminated-by ‘\t’ 指定分隔符

–columns 选择列并控制他们的排序，当源文件和数据库表列顺序一致时不需要

更新导出

updateonly

修改源数据

导出更新数据
updateonly只更新原有记录有变化的部分，不会更新新增数据

bin/sqoop export \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \
--export-dir /sqoopoutput/empdata2 \
--update-key id \
--update-mode updateonly			#updateonly只更新更改，不会更新新增

allowinsert

修改源数据

导出更新数据
更新和新增的变化都会被导入

bin/sqoop export \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \
--export-dir /sqoopoutput/empdata3 \
--update-key id \
--update-mode allowinsert			#updateonly新增和更改都会更新

sqoop job作业

创建job

bin/sqoop job --create updatejob -- export \	
#create后为job名称，inport/export前有空格
#其他语法同正常的导入导出操作
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \
--export-dir /sqoopoutput/empdata2 \
--update-key id \
--update-mode allowinsert

查看job

bin/sqoop job --list

查看job详细信息

bin/sqoop job --show jobname

执行job

bin/sqoop job --exec jobname

免密执行job

①新建文件保存密码，权限设置为400

②sqoop-site.xml设置

<property>
  <name>sqoop.metastore.client.record.password</name>
  <value>true</value>
  <description>If true, allow saved passwords in the metastore.
  </description>
</property>

③创建job时语法调整

--password ****** 			 #不再使用--password	
--password-file /密码地址	 #使用--password-file指定密码地址