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

1、Sqoop概述

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

2、工作机制

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

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

3、sqoop安装

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

(2)下载并解压sqoop

(3)修改配置文件

cd SQOOP_HOME/conf

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:

表emp_add:

表emp_conn:

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

如有侵权，联系删除

本文来源itcats_cn，由javajgs_com转载发布，观点不代表Java架构师必看的立场，转载请标明来源出处