超好用的数据迁移工具——sqoop架构、安装及使用教程详解

1. 概述

sqoop是apache旗下一款“Hadoop和关系数据库服务器之间传送数据”的工具。
导入数据：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统；导出数据：从Hadoop的文件系统中导出数据到关系数据库mysql等。

2. sqoop1与sqoop2架构对比

sqoop1架构

sqoop2 架构

3. 工作机制

将导入或导出命令翻译成mapreduce程序来实现，在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。

4. sqoop安装

安装sqoop的前提是已经具备java和hadoop的环境
1）下载并解压
下载地址
http://archive.apache.org/dist/sqoop/1.4.7
sqoop1 版本详细下载地址
http://archive.apache.org/dist/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
sqoop2版本详细下载地址
http://archive.apache.org/dist/sqoop/1.99.6/sqoop-1.99.6-bin-hadoop200.tar.gz
我们这里使用sqoop1的版本，下载之后上传到/export/softwares目录下，然后进行解压

cd /export/softwares
tar -zxvf sqoop-1.4.6-cdh5.14.0.tar.gz -C ../servers/

2）修改配置文件

cd /export/servers/hadoop‐3.1.1/conf/
cp sqoop‐env‐template.sh  sqoop‐env.sh
vim sqoop‐env.sh

export HADOOP_COMMON_HOME/export/servers/hadoop‐3.1.1
export HADOOP_MAPRED_HOME=/export/servers/hadoop‐3.1.1
export HIVE_HOME=/export/servers/apache‐hive‐3.1.1‐bin

3）加入额外的依赖包
sqoop的使用需要添加两个额外的依赖包，一个是mysql的驱动包，一个是java-json的的依赖包，不然就会报错。将mysql-connector-java-5.1.40.jar、java-json.jar添加到sqoop的lib目录下。
4）验证启动

cd /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0
bin/sqoop-version

5. Sqoop的数据导入

“导入工具”导入单个表从RDBMS到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）。
命令行查看帮助
·bin/sqoop list‐databases ‐‐help
列出主机所有的数据库

bin/sqoop list‐databases ‐‐connect jdbc:mysql://192.168.1.7:3306/ ‐‐
username root ‐‐password root

查看某一个数据库下面的所有数据表

bin/sqoop list‐tables ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
username root ‐‐password root

导入数据库表数据到 HDFS
下面的命令用于从MySQL数据库服务器中的emp表导入HDFS。

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
password root ‐‐username root ‐‐table emp ‐‐m 1

为了验证在HDFS导入的数据，请使用以下命令查看导入的数据
hdfs dfs ‐ls /user/root/emp
导入到HDFS指定目录
在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。
使用参数 --target-dir来指定导出目的地
使用参数—delete-target-dir来判断导出目录是否存在，如果存在就删掉

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
username root ‐‐password root ‐‐delete‐target‐dir ‐‐table emp ‐‐target‐
dir /sqoop/emp ‐‐m 1

导入到hdfs指定目录并指定字段之间的分隔符

bin/sqoop import  ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
username root ‐‐password  root  ‐‐delete‐target‐dir ‐‐table emp  ‐‐
target‐dir /sqoop/emp2 ‐‐m 1 ‐‐fields‐terminated‐by '\t'

导入关系表到HIVE
拷贝jar包
将我们 mysql表当中的数据直接导入到hive表中的话，我们需要将hive的一个叫做hive-exec-3.1.1.jar 的jar包拷贝到sqoop的lib目录下

cp /export/servers/apache‐hive‐3.1.1‐bin/lib/hive‐exec‐3.1.1.jar   
/export/servers/sqoop‐1.4.7.bin__hadoop‐2.6.0/lib

准备hive数据库与表

hive (default)> create database sqooptohive;
hive (default)> use sqooptohive;
hive (sqooptohive)> create external table emp_hive(id int,name string,deg
string,salary int ,dept string) row format delimited fields terminated by
'\001';

开始导入

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
username root ‐‐password root ‐‐table emp ‐‐fields‐terminated‐by '\001' ‐
‐hive‐import ‐‐hive‐table sqooptohive.emp_hive ‐‐hive‐overwrite ‐‐delete‐
target‐dir ‐‐m 1

导入关系表到hive并自动创建hive表

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
username root ‐‐password root ‐‐table emp_conn ‐‐hive‐import ‐m 1 ‐‐hive‐
database sqooptohive

导入表数据子集
我们可以导入表的使用Sqoop导入工具，"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录，where子句的语法如下。
按照条件进行查找，通过—where参数来查找表emp_add当中city字段的值为sec-bad的所有数据导入到hdfs上面去

bin/sqoop import \
‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
‐‐username root ‐‐password root ‐‐table emp_add \
‐‐target‐dir /sqoop/emp_add ‐m 1  ‐‐delete‐target‐dir \
‐‐where "city = 'sec‐bad'"

sql语句查找导入hdfs
我们还可以通过 –query参数来指定我们的sql语句，通过sql语句来过滤我们的数据进行导入

bin/sqoop import \
‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐username root ‐‐password
root\
‐‐delete‐target‐dir ‐m 1 \
‐‐query 'select email from emp_conn where 1=1 and  $CONDITIONS' \
‐‐target‐dir /sqoop/emp_conn

增量导入
在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表中的数据全部导入到hive或者hdfs当中去，肯定会出现重复的数据的状况，所以我们一般都是选用一些字段进行增量的导入，为了支持增量的导入，sqoop也给我们考虑到了这种情况并且支持增量的导入数据，增量导入是仅导入新添加的表中的行的技术。它需要添加‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入。
下面的语法用于Sqoop导入命令增量选项。

• 第一种增量导入使用上面的选项来实现
  导入emp表当中id大于1202的所有数据，注意：增量导入的时候，一定不能加参数–delete-target-dir否则会报错。

  bin/sqoop import \
‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
‐‐username root \
‐‐password root \
‐‐table emp \
‐‐incremental append \
‐‐check‐column id \
‐‐last‐value 1202  \
‐m 1 \
‐‐target‐dir /sqoop/increment

• 第二种增量导入通过–where条件来实现

bin/sqoop import \
‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
‐‐username root \
‐‐password admin  \
‐‐table emp \
‐‐incremental append  \
‐‐where "create_time > '2018‐06‐17 00:00:00'  and create_time < '2018‐06‐
17 23:59:59'" \
‐‐target‐dir  /sqoop/incement2 \
‐‐check‐column id  \
‐‐m 1

6. Sqoop的数据导出

将数据从HDFS把文件导出到RDBMS数据库，导出前目标表必须存在于目标数据库中。
默认操作是从将文件中的数据使用INSERT语句插入到表中，更新模式下，是生成UPDATE语句更新表数据。数据是在HDFS当中的如下目录/sqoop/emp，数据内容如下

1201,gopal,manager,50000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1202,manisha,Proof reader,50000,TP,2018-06-15 18:54:32.0,2018-06-17
20:26:08.0,1
1203,khalil,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1204,prasanth,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 21:05:52.0,0
1205,kranthi,admin,20000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1

创建mysql表

CREATE TABLE emp_out (
  id INT(11) DEFAULT NULL,
  name VARCHAR(100) DEFAULT NULL,
  deg VARCHAR(100) DEFAULT NULL,
  salary INT(11) DEFAULT NULL,
  dept VARCHAR(10) DEFAULT NULL,
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
  is_delete BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

执行导出命令

bin/sqoop export \
‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
‐‐username root ‐‐password root \
‐‐table emp_out \
‐‐export‐dir /sqoop/emp \
‐‐input‐fields‐terminated‐by ","