如何实现文件存储HDFS和数据库MySQL双向数据迁移？

本文档介绍如何使用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:// --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:// / --username --password --table --check-column --incremental --last-value --target-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; 使用已创建的数据库。 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 执行以下命令迁移数据。 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:// / --username --password --table --export-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为例。 验证迁移结果。 执行以下命令进入数据库。 mysql -uroot -p 执行以下命令使用数据库。 use sqoop_migrate; 执行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:// / --username --password --table --check-column --incremental --last-value --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --target-dir --hive-table

参数 说明 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:// / --username --password --table --export-dir --fields-terminated-by

参数 说明 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”。 验证迁移结果。 执行以下进入数据库。 mysql -uroot -p 执行以下命令使用数据库。 use sqoop_migrate; 执行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)