AI 助理
文档备案控制台
官方文档
输入文档关键字查找
首页

文件存储 HDFS 版和数据库MySQL双向数据迁移

更新时间：
复制为 MD 格式
产品详情
我的收藏

本文介绍如何使用Sqoop工具实现文件存储 HDFS 版和关系型数据库MySQL之间的双向数据迁移。

前提条件

  • 已开通文件存储 HDFS 版服务并创建文件系统实例和挂载点。具体操作，请参见文件存储HDFS版快速入门。

  • 已搭建Hadoop集群。建议您使用的Hadoop版本不低于2.7.2，本文使用的Hadoop版本为Apache Hadoop 2.8.5。

  • 已为Hadoop集群所有节点安装JDK，且JDK版本不低于1.8。更多信息，下载JDK。

背景信息

Sqoop是一款开源的工具，主要用于在Hadoop和结构化数据存储（例如关系数据库）之间高效传输批量数据。既可以将一个关系型数据库（MySQL、Oracle、Postgres等）中的数据导入文件存储 HDFS 版中，也可以将文件存储 HDFS 版的数据导入到关系型数据库中。

步骤一：Hadoop集群挂载文件存储 HDFS 版实例

在Hadoop集群中配置文件存储 HDFS 版实例。具体操作，请参见挂载文件系统。

步骤二：安装Sqoop

目前Sqoop分为Sqoop1和Sqoop2两个版本，且两个版本并不兼容。本文以Sqoop1的稳定版本Sqoop 1.4.7版本为例进行介绍。

  1. 下载Sqoop 1.4.7版本。

  2. 执行以下命令，解压安装包。

    tar -zxf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /usr/local/

  3. 配置环境变量。

    1. 执行vim /etc/profile命令，打开配置文件，添加如下内容：

      export SQOOP_HOME=/usr/local/sqoop-1.4.7.bin__hadoop-2.6.0
export PATH=$PATH:$SQOOP_HOME/bin

    2. 执行source /etc/profile命令，使配置生效。

  4. 添加数据库驱动。

    1. 下载MySQL-Connector包。

      wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.48.tar.gz

    2. 解压MySQL-Connector包。

      tar -zxf mysql-connector-java-5.1.48.tar.gz

    3. 将MySQL-Connector包存放到Sqoop安装目录的lib目录下。

      cp ./mysql-connector-java-5.1.48/mysql-connector-java-5.1.48.jar ${SQOOP_HOME}/lib/

  5. 修改配置文件。

    1. 执行如下命令复制sqoop-env-template.sh，并命名为sqoop-env.sh。

      cp ${SQOOP_HOME}/conf/sqoop-env-template.sh ${SQOOP_HOME}/conf/sqoop-env.sh

    2. 执行vim ${SQOOP_HOME}/conf/sqoop-env.sh命令打开配置文件，添加如下内容。

      export HADOOP_COMMON_HOME=/usr/local/hadoop-2.8.5
export HADOOP_MAPRED_HOME=$HADOOP_COMMON_HOME
export HIVE_HOME=/usr/local/apache-hive-2.3.9-bin   #若没有安装hive可不必添加此配置

    3. 执行cp ${HIVE_HOME}/lib/hive-common-2.3.9.jar ${SQOOP_HOME}/lib/命令复制文件。

  6. 执行如下命令验证数据库是否连接成功。

    sqoop list-databases --connect jdbc:mysql://<dburi> --username 'username' --password 'password'

    参数

    说明

    dburi

    数据库的访问连接。例如 jdbc:mysql://0.0.0.0:3306/。

    username

    数据库登录用户名。

    password

    用户密码。

    如果回显信息中显示MySQL数据库的名称，则表示连接成功。命令执行成功后，返回如下结果，表示数据库连接成功。

    19/08/21 11:07:12 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/08/21 11:07:12 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
hivemeta
hue
mysql
performance_schema
test

步骤三：数据迁移

下面介绍数据迁移方式及迁移后的结果验证。

实践一：将文件存储 HDFS 版的数据迁移到MySQL

将文件存储 HDFS 版的数据迁移到MySQL，需要先在MySQL上创建好对应数据结构的表，然后在集群Sqoop节点上使用sqoop export命令进行迁移。

此处以迁移文件存储HDFS版/sqoop2mysql/table/mysqltest.txt中的数据为例，mysqltest.txt中已写入如下数据。

6,测试用户6,2019-08-10,男
7,测试用户7,2019-08-11,男
8,测试用户8,2019-08-12,男
9,测试用户9,2019-08-13,女
10,测试用户10,2019-08-14,女

  1. 执行create database sqoop_migrate;命令，创建MySQL数据库。

  2. 执行以下命令，创建表。

    use sqoop_migrate;
CREATE TABLE `employee` (
  `e_id` varchar(20) NOT NULL DEFAULT '',
  `e_name` varchar(20) NOT NULL DEFAULT '',
  `e_birth` varchar(20) NOT NULL DEFAULT '',
  `e_sex` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`e_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  3. 执行以下命令，迁移数据。

     sqoop export --connect jdbc:mysql://localhost:3306/sqoop_migrate  --username 'userName' --password 'userPW'  --num-mappers 1  --table employee  --columns "e_id,e_name,e_birth,e_sex"  --export-dir '/sqoop2mysql/table/mysqltest.txt'  --fields-terminated-by ','

    迁移命令格式：sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <hdfs-dir>

    参数

    说明

    dburi

    数据库的访问连接。例如jdbc:mysql://localhost:3306/。如果您的访问连接中含有参数，则请加上单引号，例如'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'。

    dbname

    数据库的名字，例如user。

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    hdfs-dir

    存放待迁移数据的文件存储 HDFS 版目录。

  4. 验证迁移结果。

    执行select * from employee;命令查看表数据。如果表中有如下数据，则表示迁移成功。

    mysql> select * from employee;
+------+--------------+------------+-------+
| e_id | e_name       | e_birth    | e_sex |
+------+--------------+------------+-------+
| 10   | 测试用户10   | 2019-08-14 | 女    |
| 6    | 测试用户6    | 2019-08-10 | 男    |
| 7    | 测试用户7    | 2019-08-11 | 男    |
| 8    | 测试用户8    | 2019-08-12 | 男    |
| 9    | 测试用户9    | 2019-08-13 | 女    |
+------+--------------+------------+-------+
5 rows in set (0.00 sec)

实践二：将MySQL的数据迁移到HDFS

在集群Sqoop节点上，使用sqoop import命令将MySQL中的数据迁移到文件存储 HDFS 版上。

此处以迁移MySQL中的employee表为例，employee表中已写入如下数据。

mysql> select * from employee;
+------+--------------+------------+-------+
| e_id | e_name       | e_birth    | e_sex |
+------+--------------+------------+-------+
| 10   | 测试用户10   | 2019-08-14 | 女    |
| 6    | 测试用户6    | 2019-08-10 | 男    |
| 7    | 测试用户7    | 2019-08-11 | 男    |
| 8    | 测试用户8    | 2019-08-12 | 男    |
| 9    | 测试用户9    | 2019-08-13 | 女    |
+------+--------------+------------+-------+
5 rows in set (0.00 sec)

  1. 执行以下命令迁移数据。

    sqoop import --connect jdbc:mysql://localhost:3306/sqoop_migrate --username 'userid' --password 'userPW' --table employee  --target-dir /mysql2sqoop/table/sqoop_migrate  --num-mappers 1  --columns "e_id,e_name,e_birth,e_sex"  --direct

    命令格式：sqoop import --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --check-column <col> --incremental <mode> --last-value <value> --target-dir <hdfs-dir>

    关于参数说明的更多信息，请参见Sqoop Import。

    参数

    说明

    dburi

    数据库的访问连接。例如jdbc:mysql://172.x.x.x:3306/。

    如果您的访问连接中含有参数，则请加上单引号。例如'jdbc:mysql://172.x.x.x.235:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'。

    dbname

    数据库的名字。例如user。

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    col

    迁移表中列的名称。

    mode

    该模式决定Sqoop如何定义哪些行为新的行。取值为append或lastmodified。

    value

    前一个导入中检查列的最大值。

    hdfs-dir

    文件存储 HDFS 版的写入目录。

  2. 检查迁移结果。

    1. 执行hadoop fs -ls /mysql2sqoop/table/sqoop_migrate命令，获取迁移文件信息。

      [root@master1 test]# /usr/local/hadoop-2.8.5/bin/hadoop fs -ls /mysql2sqoop/table/sqoop_migrate
Found 2 items
-rwxrwxrwx   3 root root          0 2021-11-09 11:34 /mysql2sqoop/table/sqoop_migrate/_SUCCESS
-rwxrwxrwx   3 root root        157 2021-11-09 11:34 /mysql2sqoop/table/sqoop_migrate/part-m-00000

    2. 执行hadoop fs -cat /mysql2sqoop/table/sqoop_migrate/part-m-00000命令查看文件中的内容。

      如果part-m-00000文件中有如下内容，则表示迁移成功。

      [root@master1 test]# /usr/local/hadoop-2.8.5/bin/hadoop fs -cat /mysql2sqoop/table/sqoop_migrate/part-m-00000
10,测试用户10,2019-08-14,女
6,测试用户6,2019-08-10,男
7,测试用户7,2019-08-11,男
8,测试用户8,2019-08-12,男
9,测试用户9,2019-08-13,女

实践三：将MySQL的数据迁移到Hive

在集群Sqoop节点上使用sqoop import命令可以将MySQL上的数据迁移到Hive上。

此处以迁移MySQL中的employee表为例，employee表中已写入如下数据。

mysql> select * from employee;
+------+--------------+------------+-------+
| e_id | e_name       | e_birth    | e_sex |
+------+--------------+------------+-------+
| 10   | 测试用户10   | 2019-08-14 | 女    |
| 6    | 测试用户6    | 2019-08-10 | 男    |
| 7    | 测试用户7    | 2019-08-11 | 男    |
| 8    | 测试用户8    | 2019-08-12 | 男    |
| 9    | 测试用户9    | 2019-08-13 | 女    |
+------+--------------+------------+-------+
5 rows in set (0.00 sec)

  1. 执行以下命令迁移数据。

    sqoop import --connect jdbc:mysql://localhost:3306/sqoop_migrate --username 'userid' --password 'PW'   --table employee   --hive-import --hive-database default  --create-hive-table --hive-overwrite  -m 1

    迁移命令格式：sqoop import --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --target-dir <hdfs-dir> --hive-table <hive-tablename>

    参数

    说明

    dburi

    数据库的访问连接。例如jdbc:mysql://localhost:3306/。如果您的访问连接中含有参数，则请加上单引号，例如'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'。

    dbname

    数据库的名字。例如user。

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    hdfs-dir

    文件存储 HDFS 版的写入目录。

    hive-tablename

    对应的Hive中的表名。

  2. 验证迁移结果。

    执行select * from default.employee;命令查看表数据，如果表中有如下数据，则表示迁移成功。

实践四：将Hive的数据迁移到MySQL

将Hive的数据迁移到MySQL上，需要先在MySQL上创建好对应Hive数据结构的表，然后在集群Sqoop节点上使用sqoop export命令进行迁移。

此处以迁移Hive上default.employee中的数据为例，该表中已写入如下数据。

hive> select * from default.employee;
OK
10	测试用户10	2019-08-14	女
6	测试用户6	2019-08-10	男
7	测试用户7	2019-08-11	男
8	测试用户8	2019-08-12	男
9	测试用户9	2019-08-13	女
Time taken: 1.153 seconds, Fetched: 5 row(s)

  1. 在MySQL上的sqoop_migrate库中创建要导入的表。

    use sqoop_migrate;
CREATE TABLE `employee_from_hive`(
  `id` VARCHAR(20),
  `name` VARCHAR(20) NOT NULL DEFAULT '',
  `birth` VARCHAR(20) NOT NULL DEFAULT '',
  `sex` VARCHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY(`id`)
);

  2. 执行DESCRIBE FORMATTED default.employee;命令查看表信息。

    hive> DESCRIBE FORMATTED default.employee;
OK
# col_name            data_type            comment
e_id                 string
e_name               string
e_birth              string
e_sex                string
# Detailed Table Information
Database:            default
Owner:               root
CreateTime:          Tue Nov 09 15:08:51 CST 2021
LastAccessTime:      UNKNOWN
Retention:           0
Location:            dfs://f-xxx.cn-beijing.dfs.aliyuncs.com:10290/user/hive/warehouse/employee
Table Type:          MANAGED_TABLE
Table Parameters:
        comment              Imported by sqoop on 2021/11/09 15:08:42
        numFiles             1
        numRows              0
        rawDataSize          0
        totalSize            157
        transient_lastDdlTime 1636441731
# Storage Information
SerDe Library:       org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat:         org.apache.hadoop.mapred.TextInputFormat
OutputFormat:        org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed:          No
Num Buckets:         -1
Bucket Columns:      []
Sort Columns:        []
Storage Desc Params:
        field.delim              \001
        serialization.format     \001
Time taken: 0.038 seconds, Fetched: 31 row(s)
    InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets: -1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: field.delim \u0001 line.delim \n serialization.format \u0001 Time taken: 0.038 seconds, Fetched: 35 row(s)

  3. 执行以下命令迁移数据。

    sqoop export --connect jdbc:mysql://localhost:3306/sqoop_migrate --username 'userid' --password 'userPW' --table employee_from_hive -m 1 --fields-terminated-by '\0001'  --export-dir /user/hive/warehouse/employee

    迁移命令格式：sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <hive-dir> --fields-terminated-by <Splitter>

    参数

    说明

    dburi

    数据库的访问连接。例如jdbc:mysql://localhost:3306/。如果您的访问连接中含有参数，则请加上单引号，例如'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'。

    dbname

    数据库的名字，例如user。

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    hive-dir

    存放待迁移数据的文件存储 HDFS 版目录。

    Splitter

    Hive表数据使用的分隔符。

  4. 验证迁移结果。

    执行select * from sqoop_migrate.employee_from_hive;命令查看表数据。如果表中有如下数据，则表示迁移成功。

    mysql> select * from sqoop_migrate.employee_from_hive;
+------+-----------------+------------+------+
| id   | name            | birth      | sex  |
+------+-----------------+------------+------+
|   10 | 测试用户10      | 2019-08-14 | 女   |
|    6 | 测试用户6       | 2019-08-10 | 男   |
|    7 | 测试用户7       | 2019-08-11 | 男   |
|    8 | 测试用户8       | 2019-08-12 | 男   |
|    9 | 测试用户9       | 2019-08-13 | 女   |
+------+-----------------+------------+------+
5 rows in set (0.00 sec)
上一篇：文件存储 HDFS 版和对象存储OSS双向数据迁移下一篇：迁移开源HDFS的数据到文件存储 HDFS 版