本文档介绍如何使用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)