Migrate data between Apsara File Storage for HDFS and a MySQL database

To migrate data from Apsara File Storage for HDFS to MySQL, first create a table with a matching schema in MySQL. Then, run the sqoop export command on a Sqoop node.

This example shows how to migrate data from the mysqltest.txt file in the /sqoop2mysql/table/ HDFS directory. The file contains the following data.

6,test_user6,2019-08-10,Male
7,test_user7,2019-08-11,Male
8,test_user8,2019-08-12,Male
9,test_user9,2019-08-13,Female
10,test_user10,2019-08-14,Female

1. Run the create database sqoop_migrate; command to create a MySQL database.

2. Run the following commands to create a table.

   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. Run the following command to migrate the data.

    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 ',' 

   Command syntax: sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <hdfs-dir>

   Parameter	Description
   dburi	The database connection URL. For example: jdbc:mysql://localhost:3306/. If your connection URL contains parameters, enclose it in single quotation marks. For example: 'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'.
   dbname	The name of the database. Example: user.
   username	The username for logging in to the database.
   password	The user's password.
   tablename	The name of the table in the MySQL database.
   hdfs-dir	The Apsara File Storage for HDFS directory that stores the data to be migrated.

4. Verify the migration.

   Run the select * from employee; command to view the table data. If the table contains the following data, the migration was successful.

   mysql> select * from employee;
   +------+-------------+------------+--------+
   | e_id | e_name      | e_birth    | e_sex  |
   +------+-------------+------------+--------+
   | 10   | test_user10 | 2019-08-14 | Female |
   | 6    | test_user6  | 2019-08-10 | Male   |
   | 7    | test_user7  | 2019-08-11 | Male   |
   | 8    | test_user8  | 2019-08-12 | Male   |
   | 9    | test_user9  | 2019-08-13 | Female |
   +------+-------------+------------+--------+
   5 rows in set (0.00 sec)

On a Sqoop node, use the sqoop import command to migrate data from MySQL to Apsara File Storage for HDFS.

This example shows how to migrate the employee table from MySQL. The table contains the following data.

mysql> select * from employee;
+------+-------------+------------+--------+
| e_id | e_name      | e_birth    | e_sex  |
+------+-------------+------------+--------+
| 10   | test_user10 | 2019-08-14 | Female |
| 6    | test_user6  | 2019-08-10 | Male   |
| 7    | test_user7  | 2019-08-11 | Male   |
| 8    | test_user8  | 2019-08-12 | Male   |
| 9    | test_user9  | 2019-08-13 | Female |
+------+-------------+------------+--------+
5 rows in set (0.00 sec)

1. Run the following command to migrate the data.

   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

   Command syntax: 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>

   For more information about the parameters, see Sqoop Import.

   Parameter	Description
   dburi	The database connection URL. For example: jdbc:mysql://172.x.x.x:3306/. If your connection URL contains parameters, enclose it in single quotation marks. For example: 'jdbc:mysql://172.x.x.x.235:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'.
   dbname	The name of the database. Example: user.
   username	The username for logging in to the database.
   password	The user's password.
   tablename	The name of the table in the MySQL database.
   col	The name of a column in the table to be migrated.
   mode	Specifies how Sqoop identifies new rows. Valid values are append and lastmodified.
   value	The maximum value of the check column from the previous import.
   hdfs-dir	The target directory in Apsara File Storage for HDFS.

2. Check the migration result.

   1. Run the hadoop fs -ls /mysql2sqoop/table/sqoop_migrate command to list the migrated files.

      [root@master1 test]# /usr/local/hadoop-2.8.5/bin/hadoop fs -ls /mysql2sqoop/table/sqoop_migrate
      Found 2 items
      -rwxrwxrwx   3 root root          0 2021-11-09 11:34 /mysql2sqoop/table/sqoop_migrate/_SUCCESS
      -rwxrwxrwx   3 root root        157 2021-11-09 11:34 /mysql2sqoop/table/sqoop_migrate/part-m-00000

   2. Run the hadoop fs -cat /mysql2sqoop/table/sqoop_migrate/part-m-00000 command to view the contents of the file.

      If the part-m-00000 file contains the following content, the migration was successful.

      [root@master1 test]# /usr/local/hadoop-2.8.5/bin/hadoop fs -cat /mysql2sqoop/table/sqoop_migrate/part-m-00000
      10,test_user10,2019-08-14,Female
      6,test_user6,2019-08-10,Male
      7,test_user7,2019-08-11,Male
      8,test_user8,2019-08-12,Male
      9,test_user9,2019-08-13,Female

On a Sqoop node, use the sqoop import command to migrate data from MySQL to Hive.

This example shows how to migrate the employee table from MySQL. The table contains the following data.

mysql> select * from employee;
+------+-------------+------------+--------+
| e_id | e_name      | e_birth    | e_sex  |
+------+-------------+------------+--------+
| 10   | test_user10 | 2019-08-14 | Female |
| 6    | test_user6  | 2019-08-10 | Male   |
| 7    | test_user7  | 2019-08-11 | Male   |
| 8    | test_user8  | 2019-08-12 | Male   |
| 9    | test_user9  | 2019-08-13 | Female |
+------+-------------+------------+--------+
5 rows in set (0.00 sec)

1. Run the following command to migrate the data.

   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

   Command syntax: 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>

   Parameter	Description
   dburi	The database connection URL. For example: jdbc:mysql://localhost:3306/. If your connection URL contains parameters, enclose it in single quotation marks. For example: 'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'.
   dbname	The name of the database. Example: user.
   username	The username for logging in to the database.
   password	The user's password.
   tablename	The name of the table in the MySQL database.
   hdfs-dir	The target directory in Apsara File Storage for HDFS.
   hive-tablename	The name of the corresponding table in Hive.

2. Verify the migration.

   Run the select * from default.employee; command to view the table data. If the table contains the expected data, the migration was successful.

To migrate data from Hive to MySQL, first create a table with a matching schema in MySQL. Then, run the sqoop export command on a Sqoop node.

This example shows how to migrate data from the default.employee table in Hive. The table contains the following data.

hive> select * from default.employee;
OK
10	test_user10	2019-08-14	Female
6	test_user6	2019-08-10	Male
7	test_user7	2019-08-11	Male
8	test_user8	2019-08-12	Male
9	test_user9	2019-08-13	Female
Time taken: 1.153 seconds, Fetched: 5 row(s)

1. Create the target table in the sqoop_migrate database in MySQL.

   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. Run the DESCRIBE FORMATTED default.employee; command to view the table information.

   hive> DESCRIBE FORMATTED default.employee;
   OK
   # col_name            data_type            comment
   e_id                 string
   e_name               string
   e_birth              string
   e_sex                string
   
   # Detailed Table Information
   Database:            default
   Owner:               root
   CreateTime:          Tue Nov 09 15:08:51 CST 2021
   LastAccessTime:      UNKNOWN
   Retention:           0
   Location:            dfs://f-xxx.cn-beijing.dfs.aliyuncs.com:10290/user/hive/warehouse/employee
   Table Type:          MANAGED_TABLE
   Table Parameters:
           comment              Imported by sqoop on 2021/11/09 15:08:42
           numFiles             1
           numRows              0
           rawDataSize          0
           totalSize            157
           transient_lastDdlTime 1636441731
   
   # Storage Information
   SerDe Library:       org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
   InputFormat:         org.apache.hadoop.mapred.TextInputFormat
   OutputFormat:        org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
   Compressed:          No
   Num Buckets:         -1
   Bucket Columns:      []
   Sort Columns:        []
   Storage Desc Params:
           field.delim          \u0001
           line.delim           \n
           serialization.format \u0001
   Time taken: 0.038 seconds, Fetched: 35 row(s)

3. Run the following command to migrate the data.

   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

   Command syntax: sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <hive-dir> --fields-terminated-by <delimiter>

   Parameter	Description
   dburi	The database connection URL. For example: jdbc:mysql://localhost:3306/. If your connection URL contains parameters, enclose it in single quotation marks. For example: 'jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8'.
   dbname	The name of the database. Example: user.
   username	The username for logging in to the database.
   password	The user's password.
   tablename	The name of the table in the MySQL database.
   hive-dir	The Apsara File Storage for HDFS directory that stores the data to be migrated.
   delimiter	The delimiter used by the data in the Hive table.

4. Verify the migration.

   Run the select * from sqoop_migrate.employee_from_hive; command to view the table data. If the table contains the following data, the migration was successful.

   mysql> select * from sqoop_migrate.employee_from_hive;
   +------+-----------------+------------+------+
   | id   | name            | birth      | sex  |
   +------+-----------------+------------+------+
   |   10 | test_user10     | 2019-08-14 | Female |
   |    6 | test_user6      | 2019-08-10 | Male   |
   |    7 | test_user7      | 2019-08-11 | Male   |
   |    8 | test_user8      | 2019-08-12 | Male   |
   |    9 | test_user9      | 2019-08-13 | Female |
   +------+-----------------+------------+------+
   5 rows in set (0.00 sec)