Sqoop快速入门【导入数据到HDFS与导出数据到数据库】

以你之姓@ 2022-04-24 11:04 472阅读 0赞

# **1、Sqoop概述** #

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0Y2F0c19jbg_size_16_color_FFFFFF_t_70][]

## 2、工作机制 ##

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

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

## 3、sqoop安装 ##

(1)安装sqoop的前提是已经具备java和hadoop的环境

(2)下载并解压sqoop

(3)修改配置文件

$ cd $SQOOP\_HOME/conf  
$ mv sqoop-env-template.sh sqoop-env.sh  
打开sqoop-env.sh并编辑下面几行：

export HADOOP_COMMON_HOME=/root/apps/hadoop-2.6.1/ 
    export HADOOP_MAPRED_HOME=/root/apps/hadoop-2.6.1/
    export HIVE_HOME=/root/apps/hive-1.2.1

(4)加入mysql的jdbc驱动包至$SQOOP\_HOME/lib/下

(5)验证启动

$ cd $SQOOP_HOME/bin
    $ sqoop-version

验证sqoop到mysql业务库之间的连通性：  
bin/sqoop-list-databases --connect jdbc:mysql://localhost:3306 --username root --password root  
bin/sqoop-list-tables --connect jdbc:mysql://localhost:3306/userdb --username root --password root

## 4、Sqoop的数据导入 ##

“导入工具”导入单个表**从RDBMS到HDFS**。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）

下面的语法用于将数据导入HDFS  
**$ sqoop import (generic-args) (import-args)**

让我们举一个名为emp，emp\_add和emp\_contact的三个表的示例，这些表位于MySQL数据库服务器中名为userdb的数据库中。

表数据

在mysql中有一个库userdb中三个表：emp, emp\_add和emp\_conn

**表emp:**

<table style="width:437px;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#dddddd;vertical-align:top;width:49.6pt;"> id </td> 
 <td style="vertical-align:top;width:72.8pt;"> name </td> 
 <td style="vertical-align:top;width:96pt;"> deg </td> 
 <td style="vertical-align:top;width:61.2pt;"> salary </td> 
 <td style="vertical-align:top;width:47.95pt;"> dept </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:49.6pt;"> 1201 </td> 
 <td style="vertical-align:top;width:72.8pt;"> gopal </td> 
 <td style="vertical-align:top;width:96pt;"> manager </td> 
 <td style="vertical-align:top;width:61.2pt;"> 50,000 </td> 
 <td style="vertical-align:top;width:47.95pt;"> TP </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:49.6pt;"> 1202 </td> 
 <td style="vertical-align:top;width:72.8pt;"> manisha </td> 
 <td style="vertical-align:top;width:96pt;"> Proof reader </td> 
 <td style="vertical-align:top;width:61.2pt;"> 50,000 </td> 
 <td style="vertical-align:top;width:47.95pt;"> TP </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:49.6pt;"> 1203 </td> 
 <td style="vertical-align:top;width:72.8pt;"> khalil </td> 
 <td style="vertical-align:top;width:96pt;"> php dev </td> 
 <td style="vertical-align:top;width:61.2pt;"> 30,000 </td> 
 <td style="vertical-align:top;width:47.95pt;"> AC </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:49.6pt;"> 1204 </td> 
 <td style="vertical-align:top;width:72.8pt;"> prasanth </td> 
 <td style="vertical-align:top;width:96pt;"> php dev </td> 
 <td style="vertical-align:top;width:61.2pt;"> 30,000 </td> 
 <td style="vertical-align:top;width:47.95pt;"> AC </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:49.6pt;"> 1205 </td> 
 <td style="vertical-align:top;width:72.8pt;"> kranthi </td> 
 <td style="vertical-align:top;width:96pt;"> admin </td> 
 <td style="vertical-align:top;width:61.2pt;"> 20,000 </td> 
 <td style="vertical-align:top;width:47.95pt;"> TP </td> 
 </tr> 
 </tbody> 
</table>

**表emp\_add:**

<table style="width:435px;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#dddddd;vertical-align:top;width:70.05pt;"> id </td> 
 <td style="vertical-align:top;width:72.3pt;"> hno </td> 
 <td style="vertical-align:top;width:87.6pt;"> street </td> 
 <td style="vertical-align:top;width:96.35pt;"> city </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:70.05pt;"> 1201 </td> 
 <td style="vertical-align:top;width:72.3pt;"> 288A </td> 
 <td style="vertical-align:top;width:87.6pt;"> vgiri </td> 
 <td style="vertical-align:top;width:96.35pt;"> jublee </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:70.05pt;"> 1202 </td> 
 <td style="vertical-align:top;width:72.3pt;"> 108I </td> 
 <td style="vertical-align:top;width:87.6pt;"> aoc </td> 
 <td style="vertical-align:top;width:96.35pt;"> sec-bad </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:70.05pt;"> 1203 </td> 
 <td style="vertical-align:top;width:72.3pt;"> 144Z </td> 
 <td style="vertical-align:top;width:87.6pt;"> pgutta </td> 
 <td style="vertical-align:top;width:96.35pt;"> hyd </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:70.05pt;"> 1204 </td> 
 <td style="vertical-align:top;width:72.3pt;"> 78B </td> 
 <td style="vertical-align:top;width:87.6pt;"> old city </td> 
 <td style="vertical-align:top;width:96.35pt;"> sec-bad </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:70.05pt;"> 1205 </td> 
 <td style="vertical-align:top;width:72.3pt;"> 720X </td> 
 <td style="vertical-align:top;width:87.6pt;"> hitec </td> 
 <td style="vertical-align:top;width:96.35pt;"> sec-bad </td> 
 </tr> 
 </tbody> 
</table>

**表emp\_conn:**

<table style="width:433px;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#dddddd;vertical-align:top;width:72.55pt;"> id </td> 
 <td style="vertical-align:top;width:106.55pt;"> phno </td> 
 <td style="vertical-align:top;width:145.85pt;"> email </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:72.55pt;"> 1201 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 2356742 </td> 
 <td style="vertical-align:top;width:145.85pt;"> gopal@tp.com </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:72.55pt;"> 1202 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 1661663 </td> 
 <td style="vertical-align:top;width:145.85pt;"> manisha@tp.com </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:72.55pt;"> 1203 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 8887776 </td> 
 <td style="vertical-align:top;width:145.85pt;"> khalil@ac.com </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:72.55pt;"> 1204 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 9988774 </td> 
 <td style="vertical-align:top;width:145.85pt;"> prasanth@ac.com </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:72.55pt;"> 1205 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 1231231 </td> 
 <td style="vertical-align:top;width:145.85pt;"> kranthi@tp.com </td> 
 </tr> 
 </tbody> 
</table>

### **4.1    导入表数据到HDFS** ###

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

在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。

以下是指定目标目录选项的Sqoop导入命令的语法。

\--target-dir <new or exist directory in HDFS>

下面的命令是用来导入emp表数据到HDFS目录下'/sqooptest'目录。

bin/sqoop import \
    --connect jdbc:mysql://hdp-01:3306/userdb \
    --username root \
    --password root \
    --target-dir \
    /sqooptest \
    --fields-terminated-by ',' \
    --table emp \
    --split-by id \
    --m 2

**注意：如果报错，说emp类找不到**，则可以手动从sqoop生成的编译目录(/tmp/sqoop-root/compile)中，找到这个emp.class和emp.jar，拷贝到sqoop的lib目录下，或修改$HADOOP\_HOME/etc/hadoop/mapred-site.xml 修改为yarn运行即可。

如果设置了 --m 1，则意味着只会启动一个maptask执行数据导入  
如果不设置 --m 1，则默认为启动4个map task执行数据导入，则需要指定一个列来作为划分map task任务的依据

验证结果：

hadoop fs -cat /sqooptest/part-m-\*

它向您显示emp表数据和字段用逗号（，）分隔。

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

### 4.2   导入关系表到HIVE ###

bin/sqoop import \
    --connect "jdbc:mysql://hdp-01:3306/userdb?useUnicode=true&characterEncoding=utf-8" \
    --username root \
    --password root \
    --hive-import \
    --fields-terminated-by ',' \
    --table emp \
    --split-by id \
    --m 2

实际上是与4.1相似，先将数据导入HDFS的临时目录，后调用hive元数据操作API接口，执行建表、将数据从临时目录导入到hive目录的操作

### 4.3   导入表数据子集 ###

我们可以导入表的使用Sqoop导入工具，"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。

where子句的语法如下:

--where <condition>

下面的命令用来导入emp\_add表数据的子集。子集查询检索员工ID和地址，居住城市为：Secunderabad

bin/sqoop import \
    --connect jdbc:mysql://hdp-01:3306/userdb \
    --username root \
    --password root \
    --where "city ='sec-bad'" \
    --target-dir /wherequery \
    --table emp_add \
     --m 1

**按需导入  --query**

bin/sqoop import \
    --connect jdbc:mysql://hdp-01:3306/userdb \
    --username root \
    --password root \
    --target-dir /wherequery2 \
    --query 'select id,name,deg from emp WHERE id>1207 and $CONDITIONS' \
    --split-by id \
    --fields-terminated-by '\t' \
    --m 2

**and $CONDITIONS**务必需要加上，相当于and 1 = 1

下面的命令用来验证数据从emp\_add表导入/wherequery目录

$HADOOP_HOME/bin/hadoop fs -cat /wherequery/part-m-*

它用逗号（，）分隔 emp\_add表数据和字段。

1202, 108I, aoc, sec-bad
    1204, 78B, oldcity, sec-bad
    1205, 720C, hitech, sec-bad

## 4.4    增量导入 ##

**增量导入是仅导入新添加的表中的行的技术**  
**sqoop支持两种增量MySql导入到hive的模式**  
**一种是append，即通过指定一个递增的列**，比如：

--incremental append  --check-column num_id --last-value 1000

只导入num\_id字段大于1000的数据。

**另一种是可以根据时间戳**，比如：

--incremental lastmodified --check-column created --last-value '2019-04-22 11:11:11'

就是只导入created 比'2019-04-22 11:11:11'更大的数据。

**4.4.1    append模式**  
它需要添加'incremental', 'check-column', 和 'last-value'选项来执行增量导入。  
下面的语法用于Sqoop导入命令增量选项

--incremental <mode>
 --check-column <column name>
 --last value <last check column value>

例子：   只导入id字段大于1208的数据，不包括1208

bin/sqoop import \
    --connect jdbc:mysql://hdp-01:3306/userdb \
    --target-dir /sqooptest \
    --username root \
    --password root \
    --table emp --m 1 \
    --incremental append \
    --check-column id \
    --last-value 1208

### 4.5    导入所有表格 ###

如何将所有表从RDBMS数据库服务器导入到HDFS。每个表格数据存储在一个单独的目录中，并且目录名称与表格名称相同。

## 语法 ##

$ sqoop import-all-tables (generic-args) (import-args) 
    $ sqoop-import-all-tables (generic-args) (import-args)

举一个从userdb数据库导入所有表的例子，数据库userdb包含的表的列表如下所示：

+--------------------+
     |      Tables        |
     +--------------------+
     |      emp           |
     |      emp_add       |
     |      emp_contact   |
     +--------------------+

以下命令用于从userdb数据库中导入所有表

$ sqoop import-all-tables \
    --connect jdbc:mysql://localhost/userdb \
    --username root

注 - 如果使用的是全部导入表，则该数据库中的每个表都必须具有主键字段。  
以下命令用于验证HDFS中的所有表数据到userdb数据库。

$ $HADOOP_HOME/bin/hadoop fs -ls

它将向您显示userdb数据库中的表名称列表作为目录。

输出:

drwxr-xr-x - hadoop supergroup 0 2014-12-22 22:50 _sqoop
    drwxr-xr-x - hadoop supergroup 0 2014-12-23 01:46 emp
    drwxr-xr-x - hadoop supergroup 0 2014-12-23 01:50 emp_add
    drwxr-xr-x - hadoop supergroup 0 2014-12-23 01:52 emp_contact

# 5、Sqoop的数据导出 #

**将数据从HDFS把文件导出到RDBMS数据库**  
**导出前，目标表必须存在于目标数据库中**  
     输入给Sqoop的文件包含记录，这些记录在表中称为行，这些被读取并解析成一组记录并用用户指定的分隔符分隔。  
     默认操作是从将文件中的数据使用INSERT语句插入到表中  
     更新模式下，是生成UPDATE语句更新表数据

**语法**  
以下是导出命令的语法

$ sqoop export (generic-args) (export-args) 
    $ sqoop-export (generic-args) (export-args)

以HDFS中的文件中的员工数据为例，雇员数据在HDFS的'emp /'目录中的emp\_data文件中可用，该emp\_data如下：

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, satish p,  grp des, 20000, GR

必须手动创建要导出的表，并将其导出到数据库中。  
**1、首先需要手动创建mysql中的目标表**

$ mysql
    mysql> USE db;
    mysql> CREATE TABLE employee ( 
       id INT NOT NULL PRIMARY KEY, 
       name VARCHAR(20), 
       deg VARCHAR(20),
       salary INT,
       dept VARCHAR(10));

**2、然后执行导出命令**

bin/sqoop export \
    --connect jdbc:mysql://hdp-01:3306/userdb \
    --username root \
    --password root \
    --table employee \
    --input-fields-terminated-by ',' \
    --export-dir /sqooptest/

**3、验证表mysql命令行**

plus：HDFS中的数据仍然还在

## 6、Job ##

本章介绍如何创建和维护Sqoop作业。Sqoop作业创建并保存导入和导出命令，它指定参数来识别和调用保存的作业。这种重新调用或重新执行用于增量导入，它可以将更新的行从RDBMS表导入HDFS。

语法  
以下是创建Sqoop作业的语法

$ sqoop job (generic-args) (job-args)
       [-- [subtool-name] (subtool-args)]
    $ sqoop-job (generic-args) (job-args)
       [-- [subtool-name] (subtool-args)]

**创建作业（--create）**  
我们在这里创建一个名为myjob的作业，它可以将表数据从RDBMS表导入HDFS。以下命令用于创建将数据从db数据库中的employee表导入到HDFS文件的作业。

$ sqoop job --create myjob \
    -- import \
    --connect jdbc:mysql://localhost/db \
    --username root \
    --table employee --m 1

**验证作业（--list）**  
'--list'参数用于验证保存的作业。以下命令用于验证保存的Sqoop作业列表。

$ sqoop job --list

它显示保存的作业列表

Available jobs: 
       myjob

**检查作业（ --显示）**  
'--show'参数用于检查或验证特定作业及其细节。以下命令和示例输出用于验证名为myjob的作业。

$ sqoop job --show myjob

它显示了myjob中使用的工具及其选项。

Job: myjob 
     Tool: import Options:
     ---------------------------- 
     direct.import = true
     codegen.input.delimiters.record = 0
     hdfs.append.dir = false 
     db.table = employee
     ...
     incremental.last.value = 1206
     ...

**执行作业（--exec）**  
'--exec'选项用于执行保存的作业。以下命令用于执行名为myjob的保存作业。

$ sqoop job --exec myjob

显示以下输出。

10/08/19 13:08:45 INFO tool.CodeGenTool: Beginning code generation 
    ...

##    7、Codegen ##

本章介绍'codegen'工具的重要性。从面向对象的应用程序的角度来看，每个数据库表都有一个dao类，它包含用于初始化对象的'getter'和'setter'方法。该工具（-codegen）自动生成dao类。

它根据表模式结构在Java中生成dao类。Java定义被实例化为导入过程的一部分。这个工具的主要用途是检查Java是否丢失了Java代码。如果是这样，它将使用字段之间的默认分隔符创建Java的新版本。

语法  
以下是Sqoop codegen命令的语法。

$ sqoop codegen (generic-args) (codegen-args) 
    $ sqoop-codegen (generic-args) (codegen-args)

例  
让我们举一个例子来为userdb数据库中的emp表生成Java代码。

以下命令用于执行给定示例。

$ sqoop codegen \
    --connect jdbc:mysql://localhost/userdb \
    --username root \ 
    --table emp

如果该命令执行成功，则它将在终端上产生以下输出。

14/12/23 02:34:40 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
    14/12/23 02:34:41 INFO tool.CodeGenTool: Beginning code generation
    ……………….
    14/12/23 02:34:42 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop
    Note: /tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/emp.java uses or 
       overrides a deprecated API.
    Note: Recompile with -Xlint:deprecation for details.
    
    14/12/23 02:34:47 INFO orm.CompilationManager: Writing jar file: 
       /tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/emp.jar

验证  
让我们看看输出。该粗体路径是emp表生成和存储的Java代码的位置。让我们使用以下命令来验证该位置中的文件。

$ cd /tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/
    $ ls
    emp.class
    emp.jar
    emp.java

如果要深入验证，请比较userdb数据库中的emp表和/tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/.目录中的emp.java。

## 8、Eval ##

本章介绍如何使用Sqoop'eval'工具。它允许**用户针对各自的数据库服务器执行用户定义的查询，并在控制台中预览结果**。所以，用户可以期望导入结果表数据。使用eval，我们可以评估任何类型的可以是DDL或DML语句的SQL查询。

语法  
以下语法用于Sqoop eval命令。

$ sqoop eval (generic-args) (eval-args) 
    $ sqoop-eval (generic-args) (eval-args)

选择查询评估  
使用eval工具，我们可以评估任何类型的SQL查询。让我们举一个在db数据库的employee表中选择有限行的例子。以下命令用于评估使用SQL查询的给定示例。

$ sqoop eval \
    --connect jdbc:mysql://localhost/db \
    --username root \ 
    --query “SELECT * FROM employee LIMIT 3”

如果该命令执行成功，则它将在终端上产生以下输出。

\+------+--------------+-------------+-------------------+--------+  
| Id   | Name         | Designation | Salary            | Dept   |  
\+------+--------------+-------------+-------------------+--------+  
| 1201 | gopal        | manager     | 50000             | TP     |  
| 1202 | manisha      | preader     | 50000             | TP     |  
| 1203 | khalil       | php dev     | 30000             | AC     |  
\+------+--------------+-------------+-------------------+--------+  
插入查询评估  
Sqoop评估工具可适用于建模和定义SQL语句。这意味着，我们也可以使用eval来插入语句。以下命令用于在db数据库的employee表中插入新行。

$ sqoop eval \
    --connect jdbc:mysql://localhost/db \
    --username root \ 
    -e “INSERT INTO employee VALUES(1207,‘Raju’,‘UI dev’,15000,‘TP’)”

如果该命令成功执行，则会在控制台上显示更新行的状态。

否则，您可以在MySQL控制台上验证雇员表。以下命令用于使用select'query来验证db数据库的employee表的行。

mysql>  
mysql> use db;  
mysql> SELECT \* FROM employee;  
\+------+--------------+-------------+-------------------+--------+  
| Id   | Name         | Designation | Salary            | Dept   |  
\+------+--------------+-------------+-------------------+--------+  
| 1201 | gopal        | manager     | 50000             | TP     |  
| 1202 | manisha      | preader     | 50000             | TP     |  
| 1203 | khalil       | php dev     | 30000             | AC     |  
| 1204 | prasanth     | php dev     | 30000             | AC     |  
| 1205 | kranthi      | admin       | 20000             | TP     |  
| 1206 | satish p     | grp des     | 20000             | GR     |  
| 1207 | Raju         | UI dev      | 15000             | TP     |  
\+------+--------------+-------------+-------------------+--------+

--------------------

部分内容参考博客：

[https://blog.csdn.net/ancony\_/article/details/80012908][https_blog.csdn.net_ancony_article_details_80012908]

如有侵权，联系删除

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0Y2F0c19jbg_size_16_color_FFFFFF_t_70]: /images/20220216/8582b6c1e04540e8a175722fcf9e48c8.png
[https_blog.csdn.net_ancony_article_details_80012908]: https://blog.csdn.net/ancony_/article/details/80012908