Sqoop 简明教程

Sqoop - Export

此章节描述了如何将数据从 HDFS 导回到 RDBMS 数据库。目标表必须存在于目标数据库中。提供给 Sqoop 的文件包含记录，这些记录称为表中的行。这些记录被读入并解析成为一组记录，并使用用户指定的定界符分隔。

This chapter describes how to export data back from the HDFS to the RDBMS database. The target table must exist in the target database. The files which are given as input to the Sqoop contain records, which are called rows in table. Those are read and parsed into a set of records and delimited with user-specified delimiter.

默认操作是使用 INSERT 语句将来自输入文件中的所有记录插入到数据库表中。在更新模式下，Sqoop 生成 UPDATE 语句，该语句替换数据库中的现有记录。

The default operation is to insert all the record from the input files to the database table using the INSERT statement. In update mode, Sqoop generates the UPDATE statement that replaces the existing record into the database.

Syntax

以下是导出命令的语法。

The following is the syntax for the export command.

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

Example

让我们举一个 HDFS 文件中员工数据的示例。员工数据位于 HDFS 中“emp/”目录中的 emp_data 文件中。 emp_data 如下。

Let us take an example of the employee data in file, in HDFS. The employee data is available in emp_data file in ‘emp/’ directory in HDFS. The emp_data is as follows.

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

必须手动创建待导出的表，并且此表应存在于必须从中导出该表的数据库中。

It is mandatory that the table to be exported is created manually and is present in the database from where it has to be exported.

使用以下查询在 mysql 命令行中创建表“employee”。

The following query is used to create the table ‘employee’ in mysql command line.

$ 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));

使用以下命令将表数据（它位于 HDFS 上的 emp_data 文件中）导出到 Mysql 数据库服务器的 db 数据库中的 employee 表。

The following command is used to export the table data (which is in emp_data file on HDFS) to the employee table in db database of Mysql database server.

$ sqoop export \
--connect jdbc:mysql://localhost/db \
--username root \
--table employee \
--export-dir /emp/emp_data

使用以下命令验证 mysql 命令行中的表。

The following command is used to verify the table in mysql command line.

mysql>select * from employee;

如果已成功存储给定数据，则可以找到给定员工数据的以下表。

If the given data is stored successfully, then you can find the following table of given employee data.

+------+--------------+-------------+-------------------+--------+
| Id   | Name         | Designation | Salary            | Dept   |
+------+--------------+-------------+-------------------+--------+
| 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     |
+------+--------------+-------------+-------------------+--------+