Example Typical Scenario: Migrating Data from RDS to DLI

Prerequisites

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

  Set Type to For SQL when buying a queue.

• You have created an RDS for MySQL DB instance. For 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 how to create a CDM cluster, see Creating a CDM Cluster.
  

  • To connect the cluster to an on-premises database as the destination data source, you can use either Internet or Direct Connect. If the Internet is used, make sure that an EIP has been bound to the CDM cluster, the security group of CDM allows outbound traffic from the host where the on-premises 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, the network must meet the following requirements:

    i. If the CDM cluster and the cloud service are in different regions, they must be connected through either the Internet or Direct Connect. If the Internet is used, make sure 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, instances in the same VPC, subnet, and security group 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 and security group rules.

    For how to configure routing rules, see Configure routes. For how to configure security group rules, see Security Group Configuration Examples.

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

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

Step 1: Prepare Data

• Create a database and table on the RDS for MySQL DB instance.
  1. Log in to the RDS management console. In the navigation pane on the left, choose Instances. On the displayed page, locate the target DB instance and click Log In in the Operation column.
  2. In the displayed login dialog box, 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 for the rest parameters. Then, click OK.
  4. In the Operation column of row where the created database is, 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. Insert data into the table.

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

  6. 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. In the navigation pane on the left, choose SQL Editor. On the displayed page, set Engine to Spark and Queues to the created SQL queue.

     Create a database, for example, testdb. For the syntax to create a DLI database, see Creating a Database.

     create database testdb;

  2. On the SQL Editor page, set Databases to testdb and run the following table creation statement to create a table in the database. For 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.
   1. Create a connection to the RDS database.
      1. Log in to the CDM console. In the navigation pane on the left, 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 creating a connection to RDS for MySQL, you need to upload the MySQL driver. Click the Links tab and click Driver Management.
      3. Download the MySQL driver to your local PC by referring to Managing Drivers and extract the driver package to obtain the JAR file.

         For example, download the mysql-connector-java-5.1.48.zip package and extract 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 and upload the driver file obtained in 1.a.iii.
      5. On the Driver Management page, click Back in the lower left corner to return to the Links tab. Click Create Link, select RDS for MySQL, and click Next.
      6. Configure the connection as follows:

         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 filled in after you select the database server.
         Database Name	Name of the RDS DB instance you want to migrate. In this example, the testrdsdb database created in 3 is used.
         Username	Username used for accessing the database. This user must have the permissions 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.

         Figure 2 Configuring the connection to the RDS for MySQL DB instance
         
   2. Create a connection to DLI.
      1. Log in to the CDM console. In the navigation pane on the left, 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
         

         Click Save.

2. Create a CDM migration job.
   1. Log in to the CDM console. In the navigation pane on the left, 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 Table/File Migration tab. On the displayed tab, click Create Job.
   3. On the Create Job page, set job parameters.
      Figure 5 Configuring the migration job
      
      1. Job Name: Name of the data migration job, for example, rds_to_dli.
      2. Set the parameters in the Source Job Configuration area as follows:

         Table 2 Source job parameters

         Parameter	Value
         Source Link Name	Select the name of the data source created in 1.a.
         Use SQL Statement	When set to Yes, enter a SQL statement. CDM exports data based on the statement. In this example, set it to No.
         Schema/Table Space	Select the name of the RDS for MySQL database 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 the parameters in the Destination Job Configuration area as follows:

         Table 3 Destination job parameters

         Parameter	Value
         Destination Link Name	Select the DLI data source connection.
         Resource Queue	Select a created DLI SQL queue.
         Database Name	Select a created DLI database. In this example, the database testdb created in Create a database and table on DLI is selected.
         Table Name	Select the name of a table in the database. In this example, the table tabletest created in Create a database and table on DLI is selected.
         Clear data before import	Whether to clear data in the destination table before data import. In this example, set it to No. If 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.
         • You can drag any unmatched fields to match them.
         • 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. Typically, retain the default values for all parameters.

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

         • 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 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: Set 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, meaning 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

Once the migration job is complete, check whether the RDS for MySQL table data has been migrated to the tabletest table. Specifically, do as follows: Log in to the DLI management console and choose SQL Editor. On the displayed page, set Engine to Spark, Queues to the created SQL queue, and Databases to the database created in Create a database and table on DLI. Then, execute the following query statement:

select * from tabletest;

Figure 8 Querying data in the table