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

By | 2021年4月22日
0 Comment

本文档介绍如何使用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'
    参数 说明
    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,女

  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。

    参数 说明
    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为例。
  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>

    参数 说明
    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为例。
  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>

    参数 说明
    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中的表名。
  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>

    参数 说明
    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”。
  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)

请关注公众号获取更多资料

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注