本文档介绍如何使用Sqoop工具实现文件存储HDFS和关系型数据库MySQL之间的双向数据迁移。
背景信息
Sqoop是一款开源的工具，主要用于在Hadoop和结构化数据存储（如关系数据库）之间高效传输批量数据 。既可以将一个关系型数据库（MySQL 、Oracle 、Postgres等）中的数据导入HDFS中，也可以将HDFS的数据导入到关系型数据库中。
准备工作
现在Sqoop分为Sqoop1和Sqoop2，两个版本并不兼容。本案例选择使用sqoop1的稳定版本Sqoop 1.4.7 版本。
- 下载Sqoop 1.4.7 版本。
- 解压安装包。
tar -zxf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /home/hadoop/
- 配置环境变量。
- 执行
vim /etc/profile命令，打开配置文件，添加如下内容。
export SQOOP_HOME=/home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0
export PATH=$PATH:$SQOOP_HOME/bin
- 执行
source /etc/profile命令，使配置生效。
- 添加数据库驱动。
- 下载MySQL链接包。
wget http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.38/mysql-connector-java-5.1.38.jar
- 将MySQL链接包存放到Sqoop安装目录的lib目录下。
cp mysql-connector-java-5.1.38.jar /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/lib/
- 修改配置文件。
- 执行如下命令进入/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
- 执行如下命令复制sqoop-env-template.sh，并命名为sqoop-env.sh。
cp sqoop-env-template.sh sqoop-env.sh
- 执行
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可不必添加此配置
- 执行如下命令验证数据库是否连接成功。
sqoop list-databases --connect jdbc:mysql://<dburi> --username 'username' --password 'password'
| 参数 |
说明 |
| dburi |
数据库的访问连接，例如： jdbc:mysql://0.0.0.0:3306/。 |
| username |
数据库登录用户名。 |
| 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,女
- 执行以下命令迁移数据。
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。
| 参数 |
说明 |
| 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的写入目录，此处以/mysql2sqoop/table/sqoop_migrate为例。 |
- 检查迁移结果。
- 执行
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
- 执行
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,女
- 创建数据库。
create database 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
- 执行以下命令迁移数据。
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>
| 参数 |
说明 |
| 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数据库中表的名称。 |
| hdfs-dir |
存放待迁移数据的HDFS目录，此处以/sqoop2mysql/table/mysqltest.txt为例。 |
- 验证迁移结果。
- 执行以下命令进入数据库。
- 执行以下命令使用数据库。
- 执行
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,女
- 执行以下命令迁移数据。
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>
| 参数 |
说明 |
| 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的写入目录。 |
| hive-tablename |
对应的Hive中的表名。 |
- 验证迁移结果。
执行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,女
- 在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`)
);
- 执行以下命令迁移数据。
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>
| 参数 |
说明 |
| 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数据库中表的名称。 |
| hive-dir |
存放待迁移数据的HDFS目录，此处以/sqoop2mysql/table/mysqltest.txt为例。 |
| Splitter |
Hive中表中数据分隔符。hive默认为'\001'。 |
- 验证迁移结果。
- 执行以下进入数据库。
- 执行以下命令使用数据库。
- 执行
select * from sqoop_migrate.employeeOnHive;命令查看表数据。
如果表中有如下数据，则表示迁移成功。
+----+---------------+------------+-----+
| id | name | birth | sex |
+----+---------------+------------+-----+
| 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 | 女 |
+----+---------------+------------+-----+
5 rows in set (0.00 sec)
评论