本文档介绍如何使用Sqoop工具实现文件存储HDFS和关系型数据库MySQL之间的双向数据迁移。
背景信息
Sqoop是一款开源的工具，主要用于在Hadoop和结构化数据存储（如关系数据库）之间高效传输批量数据 。既可以将一个关系型数据库（MySQL 、Oracle 、Postgres等）中的数据导入HDFS中，也可以将HDFS的数据导入到关系型数据库中。
准备工作
现在Sqoop分为Sqoop1和Sqoop2，两个版本并不兼容。本案例选择使用sqoop1的稳定版本Sqoop 1.4.7 版本。
tar -zxf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /home/hadoop/
3.配置环境变量。
export SQOOP_HOME=/home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0 export PATH=$PATH:$SQOOP_HOME/bin
4.添加数据库驱动。
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.修改配置文件。
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上。
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.检查迁移结果。
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
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命令进行迁移。
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上。
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命令进行迁移。
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;命令查看表数据。
如果表中有如下数据，则表示迁移成功。
+----+---------------+------------+-----+ | 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)
https://help.aliyun.com/document_detail/135296.html
