Migrating Data from RDS to DLI

Prerequisites

• You have created a DLI SQL queue. For details about how to create a DLI queue, see Creating a Queue.
  

  When you create a queue, set its Type to For SQL.

• You have created an RDS for MySQL DB instance. For details about how to create an RDS cluster, see Buy a DB Instance.
  • In this example, the RDS DB engine is MySQL.
  • In this example, the DB engine version is 5.7.
• You have created a CDM cluster. For details about how to create a CDM cluster, see Creating a CDM Cluster.
  

  • If the destination data source is an on-premises database, you need the Internet or Direct Connect. When using the Internet, ensure that an EIP has been bound to the CDM cluster, the security group of CDM allows outbound traffic from the host where the off-cloud data source is located, the host where the data source is located can access the Internet, and the connection port has been enabled in the firewall rules.
  • If the data source is RDS or MRS on a cloud, the network must meet the following requirements:

    i. If the CDM cluster and the cloud service are in different regions, a public network or a dedicated connection is required for enabling communication between the CDM cluster and the cloud service. If the Internet is used for communication, ensure that an EIP has been bound to the CDM cluster, the host where the data source is located can access the Internet, and the port has been enabled in the firewall rules.

    ii. If the CDM cluster and the cloud service are in the same region, VPC, subnet, and security group, they can communicate with each other by default. If the CDM cluster and the cloud service are in the same VPC but in different subnets or security groups, you must configure routing rules and security group rules.

    For details about how to configure routes, see Configure routes. For details about how to configure security groups, see section Security Group Configuration Examples.

    iii. The cloud service instance and the CDM cluster belong to the same enterprise project. If they do not, you can modify the enterprise project of the workspace.

  In this example, the VPC, subnet, and security group of the CDM cluster are the same as those of the RDS for MySQL DB instance.

Step 1: Prepare Data

• Create databases and tables on the RDS for MySQL DB instance.
  1. Log in to the RDS console. On the displayed page, locate the target DB instance and choose More > Log In in the Operation column.
  2. On the displayed login page, enter the correct username and password and click Log In.
  3. On the Databases page, click Create Database. In the displayed dialog box, enter testrdsdb as the database name and retain default values of rest parameters. Then, click OK.
  4. In the Operation column of row where the created database locates, click SQL Window and enter the following statement to create a table:

     CREATE TABLE tabletest (
     	`id` VARCHAR(32) NOT NULL,
     	`name` VARCHAR(32) NOT NULL,
     	PRIMARY KEY (`id`)
     )	ENGINE = InnoDB
     	DEFAULT CHARACTER SET = utf8mb4;

  5. Run the following statements to insert data to the created table:

     insert into tabletest VALUES ('123','abc');
     insert into tabletest VALUES ('456','efg');
     insert into tabletest VALUES ('789','hij');

  6. Run the following statement to query table data:

     select * from tabletest;

     Figure 1 Querying table data
     
• Create a database and table on DLI.
  1. Log in to the DLI management console and click SQL Editor. On the displayed page, set Engine to spark and Queue to the created SQL queue.

     Enter the following statement in the editing window to create a database, for example, the migrated DLI database testdb: For details about the syntax for creating a DLI database, see Creating a Database.

     create database testdb;

  2. In SQL Editor, select testdb for Database and run the following table creation statement to create a table in the database. For details about the table creation syntax, see Creating a DLI Table Using the DataSource Syntax.

     create table tabletest(id string,name string);

Step 2: Migrate Data

1. Create a CDM connection to MRS Hive.
   1. Create a connection to the RDS database.
      1. Log in to the CDM console, choose Cluster Management. On the displayed page, locate the created CDM cluster, and click Job Management in the Operation column.
      2. If this is your first time crating a connection to RDS for MySQL, upload the MySQL driver. Choose the Links tab and click Driver Management. The Driver Management page is displayed.
      3. Download the MySQL driver to your local PC by referring to Managing Drivers and decompress the driver package to obtain the JAR file.

         For example, download the mysql-connector-java-5.1.48.zip package and decompress it to obtain the driver file mysql-connector-java-5.1.48.jar.

      4. Return to the Driver Management page. Locate the MYSQL driver and click Upload in the Operation column. In the Import Driver File dialog box, click Select File to upload the driver file obtained in 1.a.iii.
      5. On the Driver Management page, click Back to return to the Links tab. Click Create Link, select RDS for MySQL, and click Next.
      6. Configure the connection. The following table describes the required parameters.

         Table 1 Connection parameters

         Parameter	Value
         Name	Name of the RDS data source, for example, source_rds
         Database Server	Click Select next to the text box and click the name of the created RDS DB instance. The database server address is automatically entered.
         Port	Port number of the RDS DB instance. The value is automatically entered after you select the database server.
         Database Name	Name of the RDS DB instance you want to migrate. The testrdsdb database created in 3 is used in this example.
         Username	Username used for accessing the database. This account must have the permissions required to read and write data tables and metadata. In this example, the default user root for creating the RDS for MySQL DB instance is used.
         Password	Password of the user.

         For other parameters, retain the default values. For details, see Link to Relational Databases. Click Save to complete the configuration.

         Figure 2 Configuring the connection to the RDS for MySQL DB instance
         
   2. Create a connection to the DLI.
      1. Log in to the CDM console, choose Cluster Management. On the displayed page, locate the created CDM cluster, and click Job Management in the Operation column.
      2. On the Job Management page, click the Links tab, and click Create Link. On the displayed page, select Data Lake Insight and click Next.
         Figure 3 Selecting the DLI connector
         
      1. Create a connection to link CDM to DLI. For details about parameter settings, see Link to DLI.
         Figure 4 Selecting the DLI connector
         

         After the configuration is complete, click Save.

2. Create a CDM migration job.
   1. Log in to the CDM console, choose Cluster Management. On the displayed page, locate the created CDM cluster, and click Job Management in the Operation column.
   2. On the Job Management page, choose the Table/File Migration tab and click Create Job.
   3. On the Create Job page, specify job information.
      Figure 5 Configuring the migration job
      
      1. Job Name: Name of the data migration job, for example, rds_to_dli
      2. Set parameters required for Source Job Configuration.

         Table 2 Source job configuration parameters

         Parameter	Value
         Source Link Name	Select the name of the data source created in 1.a.
         Use SQL Statement	When Use SQL Statement is set to Yes, enter an SQL statement here. CDM exports data based on the SQL statement. In this example, set this parameter to No.
         Schema/Table Space	Select the name of the RDS for MySQL DB instance you want to migrate to DLI. For example, the testrdsdb database.
         Table Name	Name of the table you want to migrate. In this example, use tabletest created in 4.

         For details about parameter settings, see From PostgreSQL/SQL Server.

      3. Set parameters required for Destination Job Configuration.

         Table 3 Destination job configuration parameters

         Parameter	Value
         Destination Link Name	Select the DLI data source connection.
         Resource Queue	Select a created DLI SQL queue.
         Database	Select a created DLI database. In this example, database testdb created in Create a database and table on DLI is selected.
         Table	Select the name of a table in the database. In this example, table tabletest created in Create a database and table on DLI is created.
         Clear data before import	Whether to clear data in the destination table before data import. In this example, set this parameter to No. If this parameter is set to Yes, data in the destination table will be cleared before the task is started.

         For details about parameter settings, see To DLI.

      4. Click Next. The Map Field page is displayed. CDM automatically matches the source and destination fields.
         • If the field mapping is incorrect, you can drag the fields to adjust the mapping.
         • If the type is automatically created at the migration destination, you need to configure the type and name of each field.
         • CDM allows for field conversion during migration. For details, see Field Conversion.
           Figure 6 Field mapping
           
      5. Click Next and set task parameters. Generally, retain the default values of all parameters.

         In this step, you can configure the following optional functions:

         • Retry Upon Failure: If the job fails to be executed, you can determine whether to automatically retry. Retain the default value Never.
         • Group: Select the group to which the job belongs. The default group is DEFAULT. On the Job Management page, jobs can be displayed, started, or exported by group.
         • Scheduled Execution: For details about how to configure scheduled execution, see Scheduling Job Execution. Retain the default value No.
         • Concurrent Extractors: Enter the number of extractors to be concurrently executed. Retain the default value 1.
         • Write Dirty Data: Specify this parameter if data that fails to be processed or filtered out during job execution needs to be written to OBS. Before writing dirty data, create an OBS link. You can view the data on OBS later. Retain the default value No so that dirty data is not recorded.
      6. Click Save and Run. On the Job Management page, you can view the job execution progress and result.
         Figure 7 Job progress and execution result
         

Step 3: Query Results

After the migration job is complete, log in to the DLI management console and click SQL Editor. In the displayed page, set Engine to spark, Queue to the created SQL queue, and Database to the database created in Create a database and table on DLI. Execute the following query statement and check whether the table data has been migrated to the tabletest table:

select * from tabletest;

Figure 8 Querying data in the table