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

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

背景信息

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

准备工作

现在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 /home/hadoop/

3.配置环境变量。

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

export SQOOP_HOME=/home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0
export PATH=$PATH:$SQOOP_HOME/bin

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

4.添加数据库驱动。

(1) 下载MySQL链接包。

wget http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.38/mysql-connector-java-5.1.38.jar

(2) 将MySQL链接包存放到Sqoop安装目录的lib目录下。

cp mysql-connector-java-5.1.38.jar /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

5.修改配置文件。

(1) 执行如下命令进入/home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/conf目录。

cd /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/conf

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

cp sqoop-env-template.sh  sqoop-env.sh

(3) 执行vim sqoop-env.sh命令打开配置文件，添加如下内容。

export HADOOP_COMMON_HOME=/home/hadoop/hadoop-2.7.2
export HADOOP_MAPRED_HOME=/home/hadoop/hadoop-2.7.2
export HIVE_HOME=/home/hadoop/hive-2.1.0   #若没有安装hive、hbase可不必添加此配置
export HBASE_HOME=/home/hadoop/hbase-1.2.2   #若没有安装hive、hbase可不必添加此配置

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

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

如果回显信息中显示MySQL数据库的名称，则表示连接成功。

将MySQL的数据迁移到HDFS上

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

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

01,测试用户1,1990-01-01,男
02,测试用户2,1990-12-21,男
03,测试用户3,1990-05-20,男
04,测试用户4,1990-08-06,男
05,测试用户5,1991-12-01,女

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

sqoop import --connect jdbc:mysql://172.x.x.x: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。

2.检查迁移结果。

(1) 执行hadoop fs -ls /mysql2sqoop/table/sqoop_migrate命令，获取迁移文件，此处以part-m-00000为例。

Found 2 items
-rwxrwxrwx   3 root root          0 2019-08-21 14:42 /mysql2sqoop/table/sqoop_migrate/_SUCCESS
-rwxrwxrwx   3 root root        200 2019-08-21 14:42 /mysql2sqoop/table/sqoop_migrate/part-m-00000

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

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

01,测试用户1,1990-01-01,男
02,测试用户2,1990-12-21,男
03,测试用户3,1990-05-20,男
04,测试用户4,1990-08-06,男
05,测试用户5,1991-12-01,女

将HDFS的数据迁移到MySQL上

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

此处以迁移HDFS上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;

2.使用已创建的数据库。

use sqoop_migrate;

3.创建表。

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

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

 sqoop export --connect jdbc:mysql://172.0.0.0:3306/sqoop_migrate  --username 'userid' --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>

5.验证迁移结果。

（1）执行以下命令进入数据库。

mysql -uroot -p

（2）执行以下命令使用数据库。

use  sqoop_migrate;

（3）执行 select * from employee；命令查询表数据。

如果表中有如下数据，则表示迁移成功。

...

| 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 | 女    |
+------+---------------+------------+-------+
10 rows in set (0.00 sec)

将MySQL的数据迁移到Hive上

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

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

1,测试用户1,2019-08-10,男
2,测试用户2,2019-08-11,男
3,测试用户3,2019-08-12,男
4,测试用户4,2019-08-13,女
5,测试用户5,2019-08-14,女

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

sqoop import --connect jdbc:mysql://172.0.0.0: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> --check-column <col> --incremental <mode> --last-value <value> --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --target-dir <hdfs-dir> --hive-table <hive-tablename>

2.验证迁移结果。

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

1      测试用户1       2019-08-10      男
2      测试用户2       2019-08-11      男
3      测试用户3       2019-08-12      男
4      测试用户4       2019-08-13      女
5      测试用户5       2019-08-14      女
...
Time taken: 0.105 seconds, Fetched: 14 row(s)

将Hive的数据迁移到MySQL上

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

此处以迁移Hive上hive_test.txt中的数据为例，hive_test.txt中已写入如下数据。

1,测试用户1,2019-08-10,男
2,测试用户2,2019-08-11,男
3,测试用户3,2019-08-12,男
4,测试用户4,2019-08-13,女
5,测试用户5,2019-08-14,女

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

use sqoop_migrate ；
 CREATE TABLE `employeeOnHive`(
  `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.执行以下命令迁移数据。

sqoop export --connect jdbc:mysql://172.0.0.0:3306/sqoop_migrate --username 'userid' --password 'userPW'   --table employeeOnHive -m 1  --fields-terminated-by ','  --export-dir /user/hive/warehouse/employeeonhive

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

3.验证迁移结果。

（1）执行以下进入数据库。

mysql -uroot -p

（2）执行以下命令使用数据库。

use  sqoop_migrate;

（3）执行select * from sqoop_migrate.employeeOnHive;命令查看表数据。