sqoop安装和应用

概述

介绍

Sqoop是Apache旗下一款**Hadoop和关系数据库服务器之间传送数据的工具。**主要用于在Hadoop(Hive)与传统的数据库(MySQL、Oracl、Postgres等)之间进行数据的传递，可以将一个关系型数据库中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。

---

多数使用Hadoop技术处理大数据业务的企业，有大量的数据存储在关系型数据中。由于没有工具支持，Hadoop和关系型数据库之间的数据传输是很困难的事情。传统的应用程序管理系统，即应用程序与使用RDBMS的关系数据库的交互，是产生大数据的来源之一。由RDBMS生成的这种大数据存储在关系数据库结构中的关系数据库服务器中。

当大数据存储和Hadoop生态系统的MapReduce，Hive，HBase等分析器出现时，他们需要一种工具来与关系数据库服务器进行交互，以导入和导出驻留在其中的大数据。Sqoop在Hadoop生态系统中占据一席之地，为关系数据库服务器和Hadoop的HDFS之间提供了可行的交互。

---

Sqoop是一个用于在Hadoop和关系数据库服务器之间传输数据的工具。它用于从关系数据库（如MySQL，Oracle）导入数据到Hadoop HDFS，并从Hadoop文件系统导出到关系数据库。Sqoop由Apache软件基金会提供。

Sqoop项目开始于2009年，最早是作为Hadoop的一个第三方模块存在，后来为了让使用者能够快速部署，也为了让开发人员能够更快速的迭代开发，Sqoop独立成为一个Apache项目。

Sqoop2的最新版本是1.99.7。请注意，2与1不兼容，且特征不完整，它并不打算用于生产部署。

环境准备

Sqoop相关发行版本可以通过官网https://mirror-hk.koddos.net/apache/sqoop/来获取

安装Sqoop组件需要与Hadoop环境适配。使用root用户在Master节点上进行部署

安装配置

在windows上吧下载好的sqoop-1.4.7.bin__hadoop-2.6.0.tar传到maser的/opt/software目录下，然后解压

到/usr/local/src目录下。

[root@master ~]# tar -zxvf /opt/software/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /usr/local/src/

root@master ~]#cd /usr/local/src/
[root@master ~]# mv /usr/local/src/sqoop-1.4.7.bin__hadoop-2.6.0/ /usr/local/src/sqoop


1）创建Sqoop的配置文件sqoop-env.sh。
复制sqoop-env-template.sh模板，并将模板重命名为sqoop-env.sh。
root@master ~]cd /usr/local/src/sqoop/conf/
[root@master conf]# cp sqoop-env-template.sh sqoop-env.sh
（2）修改sqoop-env.sh文件，添加Hdoop、Hbase、Hive等组件的安装路径。
注意，下面各组件的安装路径需要与实际环境中的安装路径保持一致。
#  vim  sqoop-env.sh

export HADOOP_COMMON_HOME=/usr/local/src/hadoop
export HADOOP_MAPRED_HOME=/usr/local/src/hadoop
export HBASE_HOME=/usr/local/src/hbase
export HIVE_HOME=/usr/local/src/hive
export ZOOKEEPER_HOME=/usr/local/src/zookeeper
export ZOOCFGDIR=/usr/local/src/zookeeper

（3）配置Linux系统环境变量，添加Sqoop组件的路径。

vim /etc/profile
 
#在文件末尾添加
# set sqoop environment
export SQOOP_HOME=/usr/local/src/sqoop
export PATH=$PATH:$SQOOP_HOME/bin
export CLASSPATH=$CLASSPATH:$SQOOP_HOME/lib

# 使系统环境变量生效


[root@master conf]# source /etc/profile

4）为了使Sqoop能够连接MySQL数据库，需要将/opt/software/mysql-connector-java-5.1.47.jar文件放入sqoop的lib目录中。该jar文件的版本需要与MySQL数据库的版本相对应，否则Sqoop导入数据时会报错。（mysql-connector-java-5.1.47.jar对应的是MySQL 5.7版本）

[root@master ~] cp /opt/software/mysql-connector-java-5.1.47.jar /usr/local/src/sqoop/lib/

验证Sqoop

我们可以通过某一个command来验证sqoop配置是否正确：

$ bin/sqoop help

出现一些Warning警告（警告信息已省略），并伴随着帮助命令的输出：

Available commands:

 codegen      Generate code to interact with database records

 create-hive-table   Import a table definition into Hive

 eval        Evaluate a SQL statement and display the results

 export       Export an HDFS directory to a database table

 help        List available commands

 import       Import a table from a database to HDFS

 import-all-tables   Import tables from a database to HDFS

 import-mainframe  Import datasets from a mainframe server to HDFS

 job        Work with saved jobs

 list-databases    List available databases on a server

 list-tables      List available tables in a database

 merge       Merge results of incremental imports

 metastore      Run a standalone Sqoop metastore

 version      Display version information

测试Sqoop是否能够成功连接数据库

$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password 000000

出现如下输出：

information_schema

metastore

mysql

oozie

performance_schema

启动

1）执行Sqoop前需要先启动Hadoop集群。
在master节点执行start-all.sh命令启动Hadoop集群。
root@master ~] # start-all.sh
（2）检查Hadoop集群的运行状态。
[hadoop@master ~]$ jps
1457 NameNode
1795 ResourceManager
2060 Jps
1646 SecondaryNameNode
（3）测试Sqoop是否能够正常连接MySQL数据库。
hadoop@master ~]
sqoop list-databases --connect jdbc:mysql://127.0.0.1:3306/ --username root -P	# Sqoop连接MySQL数据库

Warning: /home/hadoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /home/hadoop/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /home/hadoop/sqoop/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.


19/04/22 18:54:10 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password: 		# 此处需要输入mysql数据库的密码
19/04/22 18:54:14 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
-------------------
information_schema
hive
mysql
performance_schema
sys
​```
--------------

      能够查看到MySQL数据库中的information_schema、hive、mysql、performance_schema、sys等数据库，说明Sqoop可以正常连接MySQL。
      
      
（4）为了使Sqoop能够连接Hive，需要将hive组件/usr/local/src/hive/lib 目录下的hive-common-1.1.0.jar也放入Sqoop安装路径的lib目录中。
[hadoop@master ~]#cp /usr/local/src/hive/lib/hive-common-2.0.0.jar /usr/local/src/sqoop/lib/
[hadoop@master ~]#cp /usr/local/src/hive/lib/hive-exec-2.0.0.jar /usr/local/src/sqoop/lib/

Sqoop的简单使用案例

导入数据

在Sqoop中，“导入”概念指：从非大数据集群（RDBMS）向大数据集群（HDFS，HIVE，HBASE）中传输数据，叫做：导入，即使用import关键字。

RDBMS到HDFS

1. 确定Mysql服务开启正常

2. 在Mysql中新建一张表并插入一些数据

$ mysql -uroot -p000000

mysql> create database company;

mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));

mysql> insert into company.staff(name, sex) values('Thomas', 'Male');

mysql> insert into company.staff(name, sex) values('Catalina', 'FeMale');

3. 导入数据

​ （1）全部导入

$ bin/sqoop import \
--connect jdbc:mysql://master:3306/company \
--username root \
--password 1 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

​ (2)查询导入

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

提示：must contain '$CONDITIONS' in WHERE clause.
如果query后使用的是双引号，则$CONDITIONS前必须加转移符，防止shell识别为自己的变量。

​ (3)导入指定列

$ bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns id,sex \
--table staff

提示：columns中如果涉及到多列，用逗号分隔，分隔时不要添加空格

​ (4)使用sqoop关键字筛选查询导入数据

$ bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--table staff \
--where "id=1"

RDBMS到Hive

$ bin/sqoop import \

--connect jdbc:mysql://hadoop102:3306/company \

--username root \

--password 000000 \

--table staff \

--num-mappers 1 \

--hive-import \

--fields-terminated-by "\t" \

--hive-overwrite \

--hive-table staff_hive

提示：该过程分为两步，第一步将数据导入到HDFS，第二步将导入到HDFS的数据迁移到Hive仓库，第一步默认的临时目录是/user/atguigu/表名

RDBMS到Hbase

$ bin/sqoop import \

--connect jdbc:mysql://hadoop102:3306/company \

--username root \

--password 000000 \

--table company \

--columns "id,name,sex" \

--column-family "info" \

--hbase-create-table \

--hbase-row-key "id" \

--hbase-table "hbase_company" \

--num-mappers 1 \

--split-by id

提示：sqoop1.4.6只支持HBase1.0.1之前的版本的自动创建HBase表的功能

解决方案：手动创建HBase表

hbase> create 'hbase_company,'info'

**(5)** **在HBase****中scan****这张表得到如下内容**

hbase> scan ‘hbase_company’

应用

Sqoop是连接关系型数据库和Hadoop的桥梁，主要有两个方面==(导入和导出)==：

(1)将关系型数据库的数据导入到Hadoop 及其相关的系统中，如Hive和HBase。

(2)将数据从Hadoop系统里抽取并导出到关系型数据库。

Sqoop可以高效、可控的利用资源，可以通过调整任务数来控制任务的并发度。可以自动的完成数据映射和转换。由于导入数据库是有类型的，它可以自动根据数据库中的类型转换到Hadoop中，当然用户也可以自定义它们之间的映射关系。Sqoop支持多种数据库，如MySQL，Orcale等数据库。

操作方式

Sqoop将导入或导出命令翻译成MapReduce程序来实现在翻译出的,MapReduce中主要是对InputFormat和OutputFormat进行定制。Sqoop命令使用Sqoop客户端直接提交代码，使用CLI命令行控制台方式访问，在命令或者脚本指定用户数据库名和密码。

Sqoop工具接收到客户端的shell命令或者Java API命令后，通过Sqoop中的任务翻译器(Task Translator)将命令转换为对应的MapReduce任务，再将关系型数据库和Hadoop中的数据进行相互转移，进而完成数据的拷贝。

缺点

Sqoop架构部署简单、使用方便，但也存在一些缺点，例如命令行方式容易出错，格式紧耦合，无法支持所有数据类型，安全机制不够完善，例如密码暴漏，安装需要root权限，connector必须符合JDBC模型。

导入原理

在导入开始之前，Sqoop使用JDBC来检查将要导入的表。他检索出表中所有的列以及列的SQL数据类型。这些SQL类型（VARCHAR、INTEGER）被映射到Java数据类型（String、Integer等）,在MapReduce应用中将使用这些对应的Java类型来保存字段的值。Sqoop的代码生成器使用这些信息来创建对应表的类，用于保存从表中抽取的记录。对于导入来说，更关键的是DBWritable接口的序列化方法，这些方法能使Widget类和JDBC进行交互：

Public void readFields(resultSet _dbResults)throws SQLException;

Public void write(PreparedStatement _dbstmt)throws SQLException;

JDBC的ResultSet接口提供了一个用户从检查结果中检索记录的游标；这里的ReadFields()方法将用ResultSet中一行数据的列来填充Example对象的字段。Sqoop启动的MapReduce作业用到一个InputFormat,他可以通过JDBC从一个数据库表中读取部分内容。Hadoop提供的DataDriverDBInputFormat能够为几个Map任务对查询结果进行划分。为了获取更好的导入性能，查询会根据一个“划分列”来进行划分的。Sqoop会选择一个合适的列作为划分列（通常是表的主键）。在生成反序列化代码和配置InputFormat之后，Sqoop将作业发送到MapReduce集群。Map任务将执行查询并将ResultSet中的数据反序列化到生成类的实例，这些数据要么直接保存在SequenceFile文件中，要么在写到HDFS之前被转换成分割的文本。Sqoop不需要每次都导入整张表，用户也可以在查询中加入到where子句，以此来限定需要导入的记录：Sqoop –query 。在向HDFS导入数据时，重要的是要确保访问的是数据源的一致性快照。从一个数据库中并行读取数据的Map任务分别运行在不同的进程中。因此，他们不能共享一个数据库任务。保证一致性的最好方法就是在导入时不允许运行任何进行对表中现有数据进行更新。

导出原理

将导入或导出命令翻译成mapreduce程序来实现。

在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。

Sqoop导出功能的架构与其导入功能非常相似，在执行导出操作之前，Sqoop会根据数据库连接字符串来选择一个导出方法。一般为JDBC。然后，Sqoop会根据目标表的定义生成一个Java类。这个生成的类能够从文本文件中解析记录，并能够向表中插入类型合适的值。接着会启动一个MapReduce作业，从HDFS中读取源数据文件，使用生成的类解析记录，并且执行选定的导出方法。

基于JDBC的导出方法会产生一批insert语句，每条语句都会向目标表中插入多条记录。多个单独的线程被用于从HDFS读取数据并与数据库进行通信，以确保涉及不同系统的I/O操作能够尽可能重叠执行。虽然HDFS读取数据的MapReduce作业大多根据所处理文件的数量和大小来选择并行度（Map任务的数量），但Sqoop的导出工具允许用户明确设定任务的数量。由于导出性能会受并行的数据库写入线程数量的影响，所以Sqoop使用combinefileinput类将输入文件分组分配给少数几个map任务去执行。进程的并行特性导致导出操作往往不是原子操作。Sqoop会采用多个并行的任务导出，并且数据库系统使用固定大小的缓冲区来存储事务数据，这时一个任务中的所有操作不可能在一个事务中完成。因此，在导出操作进行过程中，提交过的中间结果都是可见的。在导出过程完成前，不要启动那些使用导出结果的应用程序，否则这些应用会看到不完整的导出结果。更有问题的是，如果任务失败，该任务将从头开始重新导入自己负责的那部分数据，因此可能会插入重复的记录。当前Sqoop还不能避免这种可能性。在启动导出作业前，应当在数据库中设置表的约束（例如，定义一个主键列）以保证数据行的唯一性。