Sqoop配置与基本操作

一、Sqoop配置

1、解压文件

#解压sqoop压缩文件
	[root@chust01 hadoop]# tar -zxvf /opt/download/hadoop/sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C ./
#查看解压后的文件，改名为简易版名称sqoop146
	[root@chust01 hadoop]# ls
	[root@chust01 hadoop]# mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop146
	[root@chust01 hadoop]# ls
#查看权限，权限低的话可以授权为777权限
	[root@chust01 hadoop]# ls -la

2、拷贝jar包

#进入lib下面
	[root@chust01 hadoop]# cd sqoop146/lib/
#拷贝mysql
	[root@chust01 lib]# cp /opt/download/hadoop/mysql-connector-java-5.1.32.jar ./
#拷贝hadoop-common
	[root@chust01 lib]# cp /opt/software/hadoop/hadoop260/share/hadoop/common/hadoop-common-2.6.0-cdh5.14.2.jar ./
#拷贝hadoop-hdfs
	[root@chust01 lib]# cp /opt/software/hadoop/hadoop260/share/hadoop/hdfs/hadoop-hdfs-2.6.0-cdh5.14.2.jar ./
#拷贝hadoop-mapreduce-client-core
	[root@chust01 lib]# cp /opt/software/hadoop/hadoop260/share/hadoop/mapreduce/hadoop-mapreduce-client-core-2.6.0-cdh5.14.2.jar ./

3、配置sqoop.env

#进入到conf/目录下，查看env文件，复制一份出来改个名字然后配置，防止报错
	[root@chust01 lib]# cd ../conf/
	[root@chust01 conf]# ls
	oraoop-site-template.xml  sqoop-env-template.sh    sqoop-site.xml
	sqoop-env-template.cmd    sqoop-site-template.xml
	[root@chust01 conf]# cp sqoop-env-template.sh sqoop-env.sh
	[root@chust01 conf]# ls
	oraoop-site-template.xml  sqoop-env-template.cmd  sqoop-site-template.xml
	sqoop-env.sh              sqoop-env-template.sh   sqoop-site.xml
#编辑sqoop-env.sh
	[root@chust01 conf]# vi sqoop-env.sh
#sqoop-env.sh中5个地方的路径配置上，并且删掉前面的#号
	#Set path to where bin/hadoop is available
	export HADOOP_COMMON_HOME=/opt/software/hadoop/hadoop260
	#Set path to where hadoop-*-core.jar is available
	export HADOOP_MAPRED_HOME=/opt/software/hadoop/hadoop260/share/hadoop/mapreduce
	#set the path to where bin/hbase is available
	export HBASE_HOME=/opt/software/hadoop/hbase120
	#Set the path to where bin/hive is available
	export HIVE_HOME=/opt/software/hadoop/hive110
	#Set the path for where zookeper config dir is
	export ZOOCFGDIR=/opt/software/hadoop/zookeeper345

4、配置环境变量

	#在sqoop主目录新建一个日志目录mylog，出了问题可以查看该日志
		[root@chust01 sqoop146]# mkdir mylog
	#配置常规项bin和日志目录mylog
		export JAVA_HOME=/opt/software/jdk180
		export HADOOP_HOME=/opt/software/hadoop/hadoop260
		export HIVE_HOME=/opt/software/hadoop/hive110
		export ZK_HOME=/opt/software/hadoop/zookeeper345
		export HBASE_HOME=/opt/software/hadoop/hbase120
		export SQOOP_HOME=/opt/software/hadoop/sqoop146export PATH=$SQOOP_HOME/bin:$HBASE_HOME/bin:$ZK_HOME/bin:$ZK_HOME/sbin:$HIVE_HOME/bin:$HIVE_HOME/lib:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$JAVA_HOME/bin:$PATH
		export CLASS_PATH=.:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar
		export LOGDIR=$SQOOP_HOME/mylog/
	#激活，把配置信息读到内存中
		[root@chust01 conf]# source /etc/profile
		[root@chust01 conf]# echo $SQOOP_HOME

5、启动mysql，hadoop，hive服务

	[root@chust01 conf]# systemctl status mysql
	[root@chust01 conf]# start-all.sh
	[root@chust01 conf]# nohup hive --service metastore>/dev/null 2>&1 &
	[root@chust01 conf]# nohup hive --service hiveserver2>/dev/null 2>&1 &

6、测试

#测试命令，连上和登陆数据库，连接上之后会出现当前数据库的列表，否则失败。在虚拟机中中，回车默认是提交符，需要增加\转义符

sqoop list-databases \
--connect jdbc:mysql://192.168.221.140:3306 \
--username root \
--password kb10

二、Sqoop基本操作

sqoop 是 apache 旗下一款“Hadoop 和关系数据库服务器之间传送数据”的工具，本质还是一个命令行工具，核心功能就是导入和导出：
导入数据：MySQL导入数据到 Hadoop 的 HDFS、HIVE、HBASE 等数据存储系统
导出数据：从 Hadoop 的文件系统中导出数据到关系数据库mysql 等

1、mysql->hdfs，把mysql（RDBMS）的数据导入到hdfs

测试1：
全部导入

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--table staff \
--target-dir '/kb10/test1119' \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by '\t'

测试2：
导入指定部分的列

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--target-dir '/kb10/test111901' \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by '\t' \
--columns name,sex \
--table staff

测试3：
导入部分行，select查询结果导入，如果用的双引号，$CONDITIONS前面要加转义符，否则会被识别为shell的变量报错。

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--target-dir '/kb10/test111902' \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by '\t' \
--query 'select name,sex from staff where id<=1 and $CONDITIONS;'

测试4：
使用sqoop关键词筛选查询导入数据，使用where子句

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--target-dir '/kb10/test111903' \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by '\t' \
--table staff \
--where 'id=1'

2、hdfs->mysql，使用sqoop把hdfs里的文件导出到mysql（RDBMS）里

需要现在mysql里新建一个表，这里新建了一个数据库company，新建表的名字叫hive_staff
a).在MySQL创建需要的表结构：

	create table hive_staff(
	id int,
	name varchar(50),
	sex varchar(20),
	);

b).写下面的命令

sqoop export \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--table hive_staff \
--num-mappers 1 \
--export-dir '/kb10/test1119' \
--input-fields-terminated-by '\t'

如果文件是压缩格式的，使用hcatalog才能传；如果不是，也可以用hcatalog传
需要先配环境变量，目录为hive目录下的hcatalog，配置完即可使用hcatalog-database、table直接传hive表至MySQL里。不过要注意MySQL里varchar设定的字符串范围

sqoop export \
--connect jdbc:mysql://single:3306/dim_intes \
--username root \
--password root \
--table dim_users \
--num-mappers 3 \
--hcatalog-database dwd_intes \
--hcatalog-table dwd_users

3、mysql->hive，从mysql（RDBMS）中导入到HIVE

该过程分为两步，第一步将数据导入到 HDFS，第二步将导入到 HDFS 的数据迁移到Hive 仓库，第一步默认的临时目录暂时未知，需要再研究下

• 新建好表

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--table staff \
--num-mappers 1 \
--hive-import \
--fields-terminated-by '\t' \
--hive-overwrite \
--delete-target-dir \
--hive-table xym.staff_hive

sqoop import  \
--connect jdbc:mysql://192.168.221.140:3306/retail_db  \
--username root  \
--password kb10 \
--table orders \
--hive-import \
--create-hive-table \
--hive-database retail_db \
--hive-table orders \
--m 3

4、mysql->hbase，把mysql（RDBMS）的数据导入到 Hbase

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--table staff \
--columns 'id,name,sex' \
--hbase-create-table \
--hbase-table kb10:hbase_staff2 \
--column-family 'info' \
--hbase-row-key 'id' \
--num-mappers 1 \
--split-by id \
--hbase-bulkload

split-by是类似分区的
bulkload是用sqoop去完成HDFS路径的转移和指定

4.1、bulkload可能遇到的问题

1、使用hbase中的reduce默认配置为1
2、数据量超过某个范围会失败
解决方案：
1、预分区表(启动多个reduce)

create "ns:bulkloadtable","family",{SPLITS=>["1","2","3","4","5","6","7","8","9"]}

2、修改单核HRegion的大小上限(分裂阀值，默认大小为1G)

<property>
<name>hbase.hregion.max.filesize</name>
<value>10737418240</value>
</property>

3、修改单分区单列簇的HFile文件数量上限(默认32个)

<property>
<name>hbase.mapreduce.bulkload.max.hfiles.perRegion.perFamily</name>
<value>3200</value>
</property>

5、hbase->hive，hbase导出到hive

这个步骤不需要连接操作mysql，但需要提前把hive和hbase里的jar包互相拷贝，相应的jar包如下(在hive的bin目录下)：

#hive的lib下面的jar包拷到hbase的lib下面
[root@chust01 lib]# cp hive-hbase-handler-1.1.0-cdh5.14.2.jar /opt/software/hadoop/hbase120/lib/
#hbase的lib下面的jar包拷到hive的lib下面
[root@chust01 lib]# cp /opt/software/hadoop/hbase120/lib/hbase-client-1.2.0-cdh5.14.2.jar ./
[root@chust01 lib]# cp /opt/software/hadoop/hbase120/lib/hbase-hadoop2-compat-1.2.0-cdh5.14.2.jar ./
[root@chust01 lib]# cp /opt/software/hadoop/hbase120/lib/hbase-hadoop-compat-1.2.0-cdh5.14.2.jar ./
[root@chust01 lib]# cp /opt/software/hadoop/hbase120/lib/hbase-it-1.2.0-cdh5.14.2.jar ./
[root@chust01 lib]# cp /opt/software/hadoop/hbase120/lib/hbase-server-1.2.0-cdh5.14.2.jar ./

这个操作不需要连接mysql数据库，直接在hive里创建表结构，然后把hbase的数据映射过去即可

create external table hbase_student(sid int,student_id int,course_id int,score int)
stored by  'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties("hbase.columns.mapping" = ":key,scores:student_id,scores:course_id,scores:score")
tblproperties("hbase.table.name" = "kb10:mysql_stu")

6、mysql->hdfs，根据指定的字段条件增量导出

–incremental append只支持新增不支持更新
–table TABLE_NAME 或 --query SELECT_COMMAND
–split-by 和 -m结合实现numberReduceTasks并行
–check-column 和 --last-value 结合实现–check-column ：where sid>5

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--query "select * from staff where \$CONDITIONS" \
--target-dir /kb10/test111904 \
--split-by id \
--num-mappers 2 \
--check-column id \
--incremental append \
--last-value 0

在mysql里插入增量，接着上一条的value值(可在上一步执行完后看到value值)
insert into TABLE_NAME(…) values(…),(),(),();
insert into staff(name,sex) values(‘x2’,‘男’),(‘x4’,‘男’),(‘x6’,‘男’),(‘x7’,‘男’);

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--query "select * from staff where \$CONDITIONS" \
--target-dir /kb10/test111904 \
--split-by id \
--num-mappers 2 \
--check-column id \
--incremental append \
--last-value 2

7、mysql->hdfs，根据时间戳增量导出

–incremental lastmodified修改和增加
–check 必须为timstamp类型

create table lmtest(id int auto_increment primary key,name varchar(20),time timestamp);
insert into lmtest(name) value('henry'),('pola'),('mike'),('ariel'),('jerry');
insert into lmtest(name) value('liudehua'),('zhangxueyou'),('liming');

sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password kb10 \
--query "select id,name,time from lmtest where \$CONDITIONS" \
--target-dir '/kb10/lm1118' \
--split-by id \
-m 1 \
--check-column time \
--incremental lastmodified \
--merge-key id \
--last-value "2020-11-19 20:25:20.0"

插入下面的数据后，修改上面的last-value，再次执行，即可把增量数据导入进去

insert into lmtest(name) value('xxxx'),('ssssss'),('xxxxxxaaa');

三、创建sqoop job

1、设置免密登陆

sqoop job一般用于定时任务等情况，如果每次都输入密码的话，会有些麻烦，因此可以设置免密登陆。方法如下：
a).编辑sqoop的conf/目录下的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>

b).然后设置一个路径，以echo的方式将数据库的密码写入隐藏文件.mysql.password，路径可以自由设置，我这边直接设置在了conf/下了

echo -n “kb10” >/opt/software/hadoop/sqoop146/conf/.mysql.password

c).最后在使用时，直接用--password file:///opt/software/hadoop/sqoop146/conf/.mysql.password \替代--password kb10，file:///后面的是密码文件的绝对路径。

2、创建sqoop job

sqoop job --create torderincrementjob -- import \
--connect jdbc:mysql://192.168.221.140:3306/company \
--username root \
--password-file file:///opt/software/hadoop/sqoop146/conf/.mysql.password \
--table staff \
--target-dir /kb10/score1118 \
-m 1 \
--fields-terminated-by "," \
--lines-terminated-by "\n" \
--null-string '\\N' \
--null-non-string '\\N' \
--incremental append \
--check-column id \
--last-value 10

然后在数据库里添加数据insert into score(student_id,course_id,score) values(4,1,85),(4,3,83),(4,5,85)
执行：sqoop job -exec torderincrementjob
数据库里每次更新数据后，直接输入上面的exec执行代码即可，当然也可以设置定时执行，目前还没操作过，暂时就不写这段了。
附上sqoop job的操作指令：
#查看sqoop job：sqoop job --listor：sqoop job --show JOB_NAME
#删除sqoop job：sqoop job --delete JOB_NAME
#执行sqoop job：sqoop job --exec JOB_NAME

四、常用指令解释

–input-fields-terminated-by 字段间的分隔符
–input-lines-terminated-by 行之间的分隔符

–append 将数据追加到 HDFS 中已经存在的 DataSet 中
–m 或–num-mappers 启动 N 个 map 来并行导入数据，默认 4 个。
–query 将查询结果作为数据导入，使用时必须伴随–target-dir，–hive-table，如果查询中有where 条件，则条件后必须加上$CONDITIONS 关键字
–split-by 按照某一列来切分表的工作单元，不能与–autoreset-to-one-mapper 连用
–target-dir 后面跟指定 HDFS 路径
–where 从关系数据库导入数据时的查询条件
–null-string string 类型的列如果 null，替换为指定字符串
–null-non-string 非 string 类型的列如果 null，替换为指定字符串
–check-column 作为增量导入判断的列名
–incremental 和append 或 lastmodified配合
–last-value 指定某一个值，用于标记增量导入的位置

sqoop create-hive-table 生成与关系数据库表结构对应的 hive 表结构
–hive-overwrite覆盖掉hive表中已经存在的数据
–hive-table TABLE_NAME要创建的hive表，默认使用mysql表名

五、定时任务完整流程

现有一份数据表，在mysql中不断更新有数据，现在要求定时把mysql里新增的数据追加到HBase库里，然后映射为Hive表，方案如下：
1、新建数据库exam（HBase、Hive里都建同名数据库），创建一张mysql表，id为主键自增列，表格主题内容为name，增加一列时间戳（作为增量导入的根据）。插入几行数据

use exam;

create table cron_test(
id int auto_increment primary key,
name varchar(20),
regTime timestamp
);

insert into cron_test(name) values('xiaoming'),('angela'),('huagbo'),('xuzhheng');
insert into cron_test(name) values('mas'),('safasf'),('weqw'),('fdsf');
insert into cron_test(name) values('asdas'),('adad2'),('aaa'),('ddddd');

2、新建HBase表，列簇为info，并映射到Hive里

#新建hbase表 hb_cron_test
create 'exam:hb_cron_test','info'

#新建hive外部表 映射hbase 中的 exam:hb_cron_test
create external table hv_cron_test(
id int,
name string,
regTime timestamp
)
stored by  'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties("hbase.columns.mapping" = ":key,info:name,info:regTime")
tblproperties("hbase.table.name" = "exam:hb_cron_test")

3、新建导入任务，把MySQL数据导入到HBase

#导到hbase
sqoop import \
--connect jdbc:mysql://192.168.221.140:3306/exam \
--username root \
--password kb10 \
--table cron_test \
--columns id,name,regTime \
--hbase-table exam:hb_cron_test \
--hbase-row-key id \
--column-family info \
--incremental append \
--check-column id \
--last-value 0

4、创建job，名字为myjob（仅第一行和上面的导入数据不一样，import前必须要有一个空格）

#创建job
sqoop job --create myjob -- import \
--connect jdbc:mysql://192.168.221.140:3306/exam \
--username root \
--password kb10 \
--table cron_test \
--columns id,name,regTime \
--hbase-table exam:hb_cron_test \
--hbase-row-key id \
--column-family info \
--incremental append \
--check-column id \
--last-value 0

5、创建shell脚本myjob.sh

	#!/bin/bash
	source /etc/profile
	function now(){
		echo $1" myjob at "`date +"%Y-%m-%D %H:%M:%S"`
	}
	now start
	sqoop job -exec myjob
	now end

6、参考本文（3.1设置保存密码），先手动执行一次脚本，记住密码：./mhhjob.sh
cd /opt/software/hadoop/sqoop146/conf/
vi 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>

7、创建配置文件，通过文件去执行

• 设置为1分钟执行一次

	vi mhh.log
	*/1 * * * * bash /root/crontab/myjob.sh

8、查看任务，可以看到，当前并没有任何定时任务

crontab -l
no crontab for root

9、挂载任务

	crontab mmh.log 

10、执行任务

	tail -f /var/log/cron

11、在mysql里增加下面的数据，然后在HBase、Hive里重新查询，就可以看到数据已经增量导入进去了！

insert into cron_test(name) values('xiaoming1'),('angela1'),('huagbo1'),('xuzhheng1');
insert into cron_test(name) values('12312'),('123123'),('qweqwe'),('aaadsad');