数据集成工具——数据搬运工sqoop

SQOOP（1.4.6）

1、sqoop架构

sqoop架构非常简单，是hadoop生态系统的架构最简单的框架。

sqoop1由client端直接接入hadoop，任务通过解析生成对应的maprecue执行

2、SQOOP安装

1）、上传并解压

tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /usr/local/soft/

2）、修改文件夹名字

mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop-1.4.6

3）、修改配置文件

# 切换到sqoop配置文件目录
cd /usr/local/soft/sqoop-1.4.6/conf
# 复制配置文件并重命名
cp sqoop-env-template.sh sqoop-env.sh
# vim sqoop-env.sh 编辑配置文件，并加入以下内容
export HADOOP_COMMON_HOME=/usr/local/soft/hadoop-2.7.6
export HADOOP_MAPRED_HOME=/usr/local/soft/hadoop-2.7.6/share/hadoop/mapreduce
export HBASE_HOME=/usr/local/soft/hbase-1.4.6
export HIVE_HOME=/usr/local/soft/hive-1.2.1
export ZOOCFGDIR=/usr/local/soft/zookeeper-3.4.6/conf
export ZOOKEEPER_HOME=//usr/local/soft/zookeeper-3.4.6

# 切换到bin目录
cd /usr/local/soft/sqoop-1.4.6/bin
# vim configure-sqoop 修改配置文件，注释掉没用的内容（就是为了去掉警告信息）

4）、修改环境变量

vim /etc/profile
# 将sqoop的目录加入环境变量

5）、添加MySQL连接驱动

# 从HIVE中复制MySQL连接驱动到$SQOOP_HOME/lib
cp /usr/local/soft/hive-1.2.1/lib/mysql-connector-java-5.1.49.jar /usr/local/soft/sqoop-1.4.6/lib/

6）、测试

# 打印sqoop版本
sqoop version

# 测试MySQL连通性
sqoop list-databases -connect jdbc:mysql://master:3306/ -username root -password 123456

3、准备MySQL数据

1）、登录MySQL数据库

mysql -u root -p123456;

2）、创建student数据库

create database student;

3）、切换数据库并导入数据

# mysql shell中执行
use student;
source /root/student.sql;
source /root/score.sql;

4）、另外一种导入数据的方式

# linux shell中执行
mysql -u root -p123456 student</root/student.sql
mysql -u root -p123456 student</root/score.sql

5）、导出MySQL数据库

mysqldump -u root -p123456 数据库名>任意一个文件名.sql

---

4、import

从传统的关系型数据库导入HDFS、HIVE、HBASE…

1）、sqoop_mysql_hdfs

①编写脚本，保存为sqoop_mysql_hdfs.conf

import
--connect
jdbc:mysql://master:3306/student
--username
root
--password
123456
--table
student
--m
2
--split-by
age
--target-dir
/sqoop/data/student
--fields-terminated-by
','

②、执行脚本

sqoop --options-file sqoop_mysql_hdfs.conf

③、注意事项：

1、–m 表示指定生成多少个Map任务，不是越多越好，因为MySQL Server的承载能力有限

2、当指定的Map任务数>1，那么需要结合--split-by参数，指定分割键，以确定每个map任务到底读取哪一部分数据，最好指定数值型的列

3、如果指定的分割键数据分布不均，可能导致数据倾斜问题

4、编写脚本的时候，注意：例如：--username参数，参数值不能和参数名同一行

--username root  // 错误的

// 应该分成两行
--username
root

5、运行的时候会报错InterruptedException，hadoop2.7.6自带的问题，忽略即可

21/01/25 14:32:32 WARN hdfs.DFSClient: Caught exception 
java.lang.InterruptedException
	at java.lang.Object.wait(Native Method)
	at java.lang.Thread.join(Thread.java:1252)
	at java.lang.Thread.join(Thread.java:1326)
	at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.closeResponder(DFSOutputStream.java:716)
	at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.endBlock(DFSOutputStream.java:476)
	at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.run(DFSOutputStream.java:652)

6、实际上sqoop在读取mysql数据的时候，用的是JDBC的方式，所以当数据量大的时候，效率不是很高

7、sqoop底层通过MapReduce完成数据导入导出，只需要Map任务，不许需要Reduce任务

2）、sqoop_mysql_hive

①、编写脚本，并保存为sqoop_mysql_hive.conf文件

import 
--connect
jdbc:mysql://master:3306/student
--username
root
--password
123456
--table
score
--fields-terminated-by
"\t"
--lines-terminated-by 
"\n"
--m
2
--split-by
student_id
--hive-import
--hive-overwrite
--create-hive-table
--hive-database
student
--hive-table
score

②、执行脚本

sqoop --options-file sqoop_mysql_hive.conf

3）、–direct

加上这个参数，可以在导出MySQL数据的时候，使用MySQL提供的导出工具mysqldump，加快导出速度，提高效率

①、分发mysqldump

需要将master上的/usr/bin/mysqldump分发至 node1、node2的/usr/bin目录下

scp /usr/bin/mysqldump node1:/usr/bin/
scp /usr/bin/mysqldump node2:/usr/bin/

②、编写脚本，并保存为sqoop_mysql_direct_hive.conf文件

import
--connect
jdbc:mysql://master:3306/student
--username
root
--password
123456
--table
score
--fields-terminated-by
"\t"
--lines-terminated-by
"\n"
--m
2
--split-by
student_id
--hive-import
--hive-overwrite
--hive-database
student
--hive-table
score
--direct

③、执行脚本

sqoop --options-file sqoop_mysql_direct_hive.conf

4）、-e参数的使用

①、编写脚本，并保存为sqoop_mysql_-e_hive.conf文件

import
--connect
jdbc:mysql://master:3306/student
--username
root
--password
123456
--fields-terminated-by
"\t"
--lines-terminated-by
"\n"
--m
2
--split-by
student_id
--e
"select * from score where student_id=1500100011 and $CONDITIONS"
--target-dir
/testQ
--hive-import
--hive-overwrite
--create-hive-table
--hive-database
student
--hive-table
score2

②、执行脚本

sqoop --options-file sqoop_mysql_-e_hive.conf

5）、sqoop_mysql_hbase

①、编写脚本，并保存为sqoop_mysql_hbase.conf

import
--connect
jdbc:mysql://master:3306/student
--username
root
--password
123456
--table
student
--hbase-table
student
--hbase-row-key
id
--m
1
--column-family
info

②、在HBase中创建student表

create 'student','info'

③、执行脚本

sqoop --options-file sqoop_mysql_hbase.conf

5、export

1）、sqoop_hdfs_mysql

①、编写脚本，并保存为sqoop_hdfs_mysql.conf

export
--connect
jdbc:mysql://master:3306/student?useUnicode=true&characterEncoding=UTF-8
--username
root
--password
123456
-m
1
--columns
id,name,age,gender,clazz
--export-dir
/sqoop/data/student/
--fields-terminated-by
','
--table
student

注意：先清空MySQL student表中的数据，不然会造成主键冲突

②、执行脚本

sqoop --options-file sqoop_hdfs_mysql.conf

6、–check-column

用来指定一些列，这些列在增量导入时用来检查数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似.
注意:这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时–check-column可以去指定多个列

–incremental
用来指定增量导入的模式，两种模式分别为Append和Lastmodified

–last-value
指定上一次导入中检查列指定字段最大值

1）、mysql建表语句：

create table student
 (
     id int(10) not null AUTO_INCREMENT,
     name char(5),
     age int,
     gender char(2),
     clazz char(4),
     primary key (id),
     last_mod timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) DEFAULT CHARSET=utf8;

2）、指定字段的取值范围，增量导数据

直接在命令行执行

sqoop import \
--connect jdbc:mysql://master:3306/student?characterEncoding=UTF-8 \
--username root \
--driver com.mysql.jdbc.Driver \
--password 123456 \
--table student \
--target-dir /sqoop/data/student \
--split-by id \
--m 2 \
--fields-terminated-by ',' \
--incremental append\
--check-column id \
--last-value 1500100996

3）、追加数据

直接在命令行执行

sqoop import \
--connect jdbc:mysql://master:3306/student?characterEncoding=UTF-8 \
--username root \
--driver com.mysql.jdbc.Driver \
--password 123456 \
--target-dir /sqoop/data/student \
--table student \
--fields-terminated-by ',' \
--check-column last_mod \
--incremental lastmodified \
--last-value "2021-01-25 19:30:36" \
--m 1 \
--append 

4）、对3）去重

直接在命令行执行

sqoop import \
--connect jdbc:mysql://master:3306/student?characterEncoding=UTF-8 \
--username root \
--driver com.mysql.jdbc.Driver \
--password 123456 \
--target-dir /sqoop/data/student \
--table student \
--fields-terminated-by ',' \
--check-column last_mod \
--incremental lastmodified \
--last-value "2021-01-25 19:30:36" \
--m 1 \
--merge-key id 

7、sqoop命令附录：

1）、sqoop工具

2）、导入工具

3）、导出工具