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

本文介绍如何使用Sqoop工具实现文件存储HDFS版和关系型数据库MySQL之间的双向数据迁移。

前提条件

  • 已开通文件存储HDFS版服务并创建文件系统实例和挂载点。具体操作,请参见文件存储HDFS版快速入门

  • 已搭建Hadoop集群。建议您使用的Hadoop版本不低于2.7.2,本文使用的Hadoop版本为Apache Hadoop 2.8.5。

  • 已为Hadoop集群所有节点安装JDK,且JDK版本不低于1.8。更多信息,下载JDK

背景信息

Sqoop是一款开源的工具,主要用于在Hadoop和结构化数据存储(例如关系数据库)之间高效传输批量数据。既可以将一个关系型数据库(MySQL、Oracle、Postgres等)中的数据导入文件存储HDFS版中,也可以将文件存储HDFS版的数据导入到关系型数据库中。

步骤一:Hadoop集群挂载文件存储HDFS版实例

在Hadoop集群中配置文件存储HDFS版实例。具体操作,请参见挂载文件系统

步骤二:安装Sqoop

目前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 /usr/local/
  3. 配置环境变量。

    1. 执行vim /etc/profile命令,打开配置文件,添加如下内容:

      export SQOOP_HOME=/usr/local/sqoop-1.4.7.bin__hadoop-2.6.0
      export PATH=$PATH:$SQOOP_HOME/bin
    2. 执行source /etc/profile命令,使配置生效。

  4. 添加数据库驱动。

    1. 下载MySQL链接包。

      wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.48.tar.gz
    2. 解压链接包。

      tar -zxf mysql-connector-java-5.1.48.tar.gz
    3. 将MySQL链接包存放到Sqoop安装目录的lib目录下。

      cp ./mysql-connector-java-5.1.48/mysql-connector-java-5.1.48.jar ${SQOOP_HOME}/lib/
  5. 修改配置文件。

    1. 执行如下命令复制sqoop-env-template.sh,并命名为sqoop-env.sh。

      cp ${SQOOP_HOME}/conf/sqoop-env-template.sh ${SQOOP_HOME}/conf/sqoop-env.sh
    2. 执行vim ${SQOOP_HOME}/conf/sqoop-env.sh命令打开配置文件,添加如下内容。

      export HADOOP_COMMON_HOME=/usr/local/hadoop-2.8.5
      export HADOOP_MAPRED_HOME=$HADOOP_COMMON_HOME
      export HIVE_HOME=/usr/local/apache-hive-2.3.9-bin   #若没有安装hive可不必添加此配置
    3. 执行cp ${HIVE_HOME}/lib/hive-common-2.3.9.jar ${SQOOP_HOME}/lib/命令复制文件。

  6. 执行如下命令验证数据库是否连接成功。

    sqoop list-databases --connect jdbc:mysql://<dburi> --username 'username' --password 'password' 

    参数

    说明

    dburi

    数据库的访问连接。例如 jdbc:mysql://0.0.0.0:3306/

    username

    数据库登录用户名。

    password

    用户密码。

    如果回显信息中显示MySQL数据库的名称,则表示连接成功。数据库成功连接

步骤三:数据迁移

下面介绍数据迁移方式及迁移后的结果验证。

实践一:将文件存储HDFS版的数据迁移到MySQL

文件存储HDFS版的数据迁移到MySQL,需要先在MySQL上创建好对应数据结构的表,然后在集群Sqoop节点上使用sqoop export命令进行迁移。
此处以迁移文件存储HDFS版/sqoop2mysql/table/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;命令,创建MySQL数据库。

  2. 执行以下命令,创建表。

    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; 
  3. 执行以下命令,迁移数据。

     sqoop export --connect jdbc:mysql://localhost:3306/sqoop_migrate  --username 'userName' --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://localhost:3306/。如果您的访问连接中含有参数,则请加上单引号,例如'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'

    dbname

    数据库的名字,例如user

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    hdfs-dir

    存放待迁移数据的文件存储HDFS版目录。

  4. 验证迁移结果。

    执行select * from employee;命令查看表数据。如果表中有如下数据,则表示迁移成功。hdfs_mysql

实践二:将MySQL的数据迁移到HDFS

在集群Sqoop节点上,使用sqoop import命令将MySQL中的数据迁移到文件存储HDFS版上。

此处以迁移MySQL中的employee表为例,employee表中已写入如下数据。MySQL迁移数据

  1. 执行以下命令迁移数据。

    sqoop import --connect jdbc:mysql://localhost: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如何定义哪些行为新的行。取值为appendlastmodified

    value

    前一个导入中检查列的最大值。

    hdfs-dir

    文件存储HDFS版的写入目录。

  2. 检查迁移结果。

    1. 执行hadoop fs -ls /mysql2sqoop/table/sqoop_migrate命令,获取迁移文件信息。MySQL迁移文件

    2. 执行hadoop fs -cat /mysql2sqoop/table/sqoop_migrate/part-m-00000命令查看文件中的内容。
      如果part-m-00000文件中有如下内容,则表示迁移成功。mysql-hdfs

实践三:将MySQL的数据迁移到Hive

在集群Sqoop节点上使用sqoop import命令可以将MySQL上的数据迁移到Hive上。

此处以迁移MySQL中的employee表为例,employee表中已写入如下数据。employee

  1. 执行以下命令迁移数据。

    sqoop import --connect jdbc:mysql://localhost: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> --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --target-dir <hdfs-dir> --hive-table <hive-tablename>

    参数

    说明

    dburi

    数据库的访问连接。例如jdbc:mysql://localhost:3306/。如果您的访问连接中含有参数,则请加上单引号,例如'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'

    dbname

    数据库的名字。例如user

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    hdfs-dir

    文件存储HDFS版的写入目录。

    hive-tablename

    对应的Hive中的表名。

  2. 验证迁移结果。

    执行select * from default.employee;命令查看表数据,如果表中有如下数据,则表示迁移成功。

实践四:将Hive的数据迁移到MySQL

将Hive的数据迁移到MySQL上,需要先在MySQL上创建好对应Hive数据结构的表,然后在集群Sqoop节点上使用sqoop export命令进行迁移。

此处以迁移Hive上default.employee中的数据为例,该表中已写入如下数据。default.employee

  1. 在MySQL上的sqoop_migrate库中创建要导入的表。

    use sqoop_migrate;
    CREATE TABLE `employee_from_hive`(
      `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. 执行DESCRIBE FORMATTED default.employee;命令查看表信息。查看表信息

  3. 执行以下命令迁移数据。

    sqoop export --connect jdbc:mysql://localhost:3306/sqoop_migrate --username 'userid' --password 'userPW' --table employee_from_hive -m 1 --fields-terminated-by '\0001'  --export-dir /user/hive/warehouse/employee

    迁移命令格式:sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <hive-dir> --fields-terminated-by <Splitter>

    参数

    说明

    dburi

    数据库的访问连接。例如jdbc:mysql://localhost:3306/。如果您的访问连接中含有参数,则请加上单引号,例如'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'

    dbname

    数据库的名字,例如user

    username

    数据库登录用户名。

    password

    用户密码。

    tablename

    MySQL数据库中表的名称。

    hive-dir

    存放待迁移数据的文件存储HDFS版目录。

    Splitter

    Hive表数据使用的分隔符。

  4. 验证迁移结果。

    执行select * from sqoop_migrate.employee_from_hive;命令查看表数据。如果表中有如下数据,则表示迁移成功。hive-mysql

阿里云首页 文件存储HDFS版 相关技术圈