Sqoop数据迁移工具

悠悠 2021-09-23 07:26 397阅读 0赞

[Sqoop 官方文档][Sqoop]

**目录**

*  1. Sqoop 数据导入 
 *  1.1. 导入 MySQL 数据到 HDFS
 *  1.1.1. 普通导入 
 *  1.1.2. 指定分隔符和导入路径
 *  1.1.3. 导入 where 条件数据
 *  1.1.4. 导入 Query 结果数据
 *  1.2. 导入 MySQL 到 HIVE
 *  1.2.1. 普通导入
 *  1.2.2. 增量导入 
 *  1.3. 导入 MySQL 数据到 HBase
 *  2. Sqoop 数据导出
 *  2.1. 导出 HDFS 数据到 MySQL
 *  2.2. 导出 HIVE 数据到 MySQL
 *  2.3. 导出 HBase 数据到 MySQL
 *  3. Sqoop Job 作业
 *  4. Sqoop 导入导出的原理剖析 
 *  4.1. Sqoop 导入原理 
 *  4.2. Sqoop 导出原理

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

可以通过 sqoop help import 方式来查看命令帮助。

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

# 1. Sqoop 数据导入  #

用于将数据导入 HDFS 。

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

sqoop import  (generic-args)  (import-args)

常用参数： 
    --connect <jdbc-uri>                jdbc 连接地址 
    --connection-manager <class-name>   连接管理 
    --driver <class-name>               驱动类 
    --hadoop-mapred-home <dir>          $HADOOP_MAPRED_HOME 
    --help                              help 信息 
    -P                                  从命令行输入密码 
    --password <password>               密码 
    --username <username>               账号 
    --verbose                           打印流程信息 
    --connection-param-file <filename>  可选参数

# #

## 1.1. 导入 MySQL 数据到 HDFS ##

### 1.1.1. 普通导入  ###

sqoop import \ 
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    -m 1

如果没有指定路径，那么默认会保存在 HDFS 上的 /user/hadoop/help\_keyword 目录。如果没有 指定分隔符，默认按照逗号分隔。

导入后查看HDFS上数据。

hdfs dfs -cat /user/hadoop/help_keyword/part-m-00000

### 1.1.2. 指定分隔符和导入路径 ###

指定导入路径的语法规则：

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

指定导入的文本文件的列分隔符：

--fields-terminated-by '\t'

具体实例：

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --target-dir /user/hadoop/my_help_keyword \
    --fields-terminated-by '\t' \
    -m 1

### 1.1.3. 导入 where 条件数据 ###

执行在各自的数据库 服务器相应的 SQL 查询，并将结果存储在 HDFS 的目标目录。

where 子句的语法

--where <condition>

具体实例：

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --where "name='STRING'" \
    --table help_keyword \
    --target-dir /sqoop/hadoop/import \
    -m 1

### 1.1.4. 导入 Query 结果数据 ###

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --target-dir /user/hadoop/import \
    --query 'select help_keyword_id,name from help_keyword WHERE name = "STRING" and $CONDITIONS' \
    --split-by help_keyword_id \
    --fields-terminated-by '\t' \
    -m 1

注意：外层使用单引号，SQL 语句当中的条件使用双引号，否则会报错，或者可以这么写：

sqoop import \
    --connect jdbc:mysql://hadoop02:3306/mysql \
    --username root \
    --password root \
    --target-dir /user/hadoop/myimport \
    --query "select help_keyword_id,name from help_keyword WHERE name = 'STRING' and \$CONDITIONS" \
    --split-by help_keyword_id \
    --fields-terminated-by '\t' \
    -m 1

## 1.2. 导入 MySQL 到 HIVE ##

### 1.2.1. 普通导入 ###

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --hive-import \
    -m 1

注意：导入数据到 hive 表，默认表在 default 库下，表名一样，采用 '\\u0001' 分隔。

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --fields-terminated-by "\t" \
    --lines-terminated-by "\n" \
    --hive-import \
    --hive-overwrite \
    --create-hive-table \
    --hive-table mydb_test.new_help_keyword \
    --delete-target-dir

注意：表会自动创建，但是库不会。所以在执行该语句之前，一定要确保 hive 的数据库 mydb\_test 是存在的，否则程序会报错。

### 1.2.2. 增量导入  ###

增量导入是仅导入表中新添加的行的技术。 它需要添加 'incremental' ,  'check-column', 和 'last-value' 选项来执行增量导入。

下面的语法结构用于 Sqoop 导入命令增量选项。

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

增量导入：

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --target-dir /user/hadoop/import \
    --incremental append \
    --check-column help_keyword_id \
    --last-value 500 \
    -m 1

此处结果不包含 help\_keyword\_id = 500 数据，数据是大于 500 的。

## 1.3. 导入 MySQL 数据到 HBase ##

示例：

sqoop import \
    --connect jdbc:mysql://cdh-master:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --hbase-table new_help_keyword \
    --column-family person \
    --hbase-row-key help_keyword_id

字段解释：

--connect jdbc:mysql://cdh-master:3306/mysql  表示远程或者本地 Mysql 服务的 URI 
    --hbase-create-table                          表示在 HBase 中建立表
    --hbase-table new_help_keyword                表示在 HBase 中建立表 new_help_keyword
    --hbase-row-key help_keyword_id               表示hbase表的rowkey是mysql表的help_keyword_id 字段
    --column-family person                        表示在表 new_help_keyword 中建立列族 person
    --username 'root'                             表示使用用户 root 连接 mysql 
    --password 'root'                             连接 mysql 的用户密码 
    --table help_keyword                          表示导出 mysql 数据库的 help_keyword 表

# 2. Sqoop 数据导出 #

## 2.1. 导出 HDFS 数据到 MySQL ##

> 注意：导出前，目标表必须存在于目标数据库中。   
> HDFS：hadoop distributed file system  
> RDBMS：Relation DataBase Manager System  
> OOP：orentied object programming  
> AOP：orentied aspect programming

默认操作是从将文件中的数据使用 INSERT 语句插入到表中 更新模式下，是生成 UPDATE 语句更新表数据。  
   
Export 语法结构：

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

Export 常用参数：

export 主要参数 
    --direct                                快速导入 
    --export-dir <dir>                      HDFS 到处数据的目录 
    -m,--num-mappers <n>                    都少个 map 线程 
    --table <table-name>                    导出哪个表 
    --call <stored-proc-name>               存储过程 
    --update-key <col-name>                 通过哪个字段来判断更新 
    --update-mode <mode>                    插入模式，默认是只更新，可以设置为 allowinsert. 
    --input-null-string <null-string>       字符类型 null 处理 
    --input-null-non-string <null-string>   非字符类型 null 处理 
    --staging-table <staging-table-name>    临时表 
    --clear-staging-table                   清空临时表 
    --batch                                 批量模式

MySQL建表。

create database sqoopdb default character set utf8 COLLATE utf8_general_ci;
    use sqoopdb;
    CREATE TABLE sqooptest (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    sex VARCHAR(20),
    age INT,
    department VARCHAR(20));

导出。

sqoop export \
    --connect jdbc:mysql://cdh-master:3306/sqoopdb \
    --username root \
    --password root \
    --table sqooptest \
    --export-dir /sqoopdata \
    --fields-terminated-by ','

## 2.2. 导出 HIVE 数据到 MySQL ##

其实跟直接导出 HDFS 数据到 MySQL 没有区别。

sqoop export \
    --connect jdbc:mysql://cdh-master:3306/sqoopdb \
    --username root \
    --password root \
    --table uv_info \
    --export-dir /user/hive/warehouse/uv/dt=2019-09-11 \
    --input-fields-terminated-by '\t'

## 2.3. 导出 HBase 数据到 MySQL ##

一般采用如下 3 种方法：

1、将 HBase 数据，扁平化成 HDFS 文件，然后再由 sqoop 导入

2、将 HBase 数据导入 Hive 表中，然后再导入 MySQL

3、直接使用 HBase 的 Java API 读取表数据，直接向 MySQL 导入，不需要使用 sqoop

# 3. Sqoop Job 作业 #

**3.1. 创建作业 Job（--create）**

创建一个名为 my\_sqoop\_job，这可以从 RDBMS 表的数据导入到 HDFS 作业。

sqoop job \
    --create my_sqoop_job \
    --import \
    --connect jdbc:mysql://cdh-master:3306/spider \
    --username root \
    --password root \
    --table test

**3.2. 查看作业 Job（--list）**

sqoop job --list

**3.3. 查看作业详细信息（--show）**

sqoop job --show my_sqoop_job

**3.4. 执行作业 Job（--exec）**

sqoop job --exec my_sqoop_job

如果想要在查看任务详细或者执行任务的时候不输入密码，那么可以做如下配置：在 sqoop 的安装目录下的 conf 目录（/home/hadoop/apps/sqoop-1.4.6/conf）中，修改配置文件 sqoop-site.xml 中的一项配置：

<property>
        <name>sqoop.metastore.client.record.password</name>
        <value>true</value>
        <description>If true, allow saved passwords in the metastore.</description>
    </property>

在 sqoop-site.xml 配置文件中，配置的 value 本来就是 true，所以不用更改，只需要放开注 释即可   
 再次执行命令就能发现，不需要输入数据库密码了。

**3.5. 删除作业 Job（--delete）**

sqoop job --delete my_sqoop_job

# 4. Sqoop 导入导出的原理剖析  #

## 4.1. Sqoop 导入原理  ##

从上面的演示例子中，我们大致能得出一个结论，sqoop 工具是通过 MapReduce 进行导入 作业的。总体来说，是把关系型数据库中的某张表的一行行记录都写入到 HDFS。  
下面是一张原理图：    
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmdlaW8_size_16_color_FFFFFF_t_70][]

上面这张图大致解释了 sqoop 在进行数据导入工作的大致流程，下面我们用文字来详细描述 一下：

> 1、第一步，Sqoop 会通过 JDBC 来获取所需要的数据库元数据，例如，导入表的列名，数据类型等。   
> 2、第二步，这些数据库的数据类型(varchar, number 等)会被映射成 Java 的数据类型(String, int 等)，根据这些信息，Sqoop 会生成一个与表名同名的类用来完成序列化工作，保存表中的每一行记录。   
> 3、第三步，Sqoop 启动 MapReducer 作业。  
> 4、第四步，启动的作业在 input 的过程中，会通过 JDBC 读取数据表中的内容，这时，会使用 Sqoop 生成的类进行反序列化操作。  
> 5、第五步，最后将这些记录写到 HDFS 中，在写入到 HDFS 的过程中，同样会使用 Sqoop 生成的类进行反序列化。

# #

## 4.2. Sqoop 导出原理 ##

Sqoop 进行数据导出，总体也是基于 mapreduce 任务。   
原理图：   
 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmdlaW8_size_16_color_FFFFFF_t_70 1][]  
   
详细文字描述：

> 1、 第一步，sqoop 依然会通过 JDBC 访问关系型数据库，得到需要导出数据的元数据信息。  
> 2、 第二步，根据获取到的元数据的信息，sqoop 生成一个 Java 类，用来进行数据的传输载体。该类必须实现序列化和反序列化。  
> 3、 第三步，启动 mapreduce 作业。  
> 4、 第四步，sqoop 利用生成的这个 java 类，并行的从 hdfs 中读取数据。  
> 5、 第五步，每个 map 作业都会根据读取到的导出表的元数据信息和读取到的数据，生成一批 insert 语句，然后多个 map 作业会并行的向数据库 mysql 中插入数据。
> 
> 所以，数据是从 hdfs 中并行的进行读取，也是并行的进入写入，那并行的读取是依赖 hdfs 的性能，而并行的写入到 mysql 中，那就要依赖于 mysql 的写入性能了。

[Sqoop]: http://sqoop.apache.org/docs/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmdlaW8_size_16_color_FFFFFF_t_70]: /images/20210923/0bf0714f6c654b1197302f8b9370082f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmdlaW8_size_16_color_FFFFFF_t_70 1]: /images/20210923/7e60707d775d4253b3fcb27a74692d69.png