Using DLI to Submit a SQL Job to Query RDS for MySQL Data

Scenario

DLI can query data stored in RDS. This section describes how to use DLI to submit a SQL job to query RDS for MySQL data.

In this example, we will create an RDS for MySQL DB instance, create a database and table, create a DLI elastic resource pool and add a queue to it, create an enhanced datasource connection to connect the DLI elastic resource pool and the RDS for MySQL DB instance, and submit a SQL job to access the data in the RDS table.

Procedure

Table 1 shows the process for submitting a SQL job to query RDS for MySQL data.

Complete the preparations in Preparations before performing the following operations.

Preparations

• Register a Huawei ID and enable Huawei Cloud services. Make sure your account is not in arrears or frozen.
• Configure an agency for DLI.
  To use DLI, you need to access services such as Object Storage Service (OBS), Virtual Private Cloud (VPC), and Simple Message Notification (SMN). If it is your first time using DLI, you will need to configure an agency to allow access to these dependent services.

  1. Log in to the DLI management console using your account. In the navigation pane on the left, choose Global Configuration > Service Authorization.
  2. On the agency settings page, select the agency permissions under Basic Usage, Datasource, and O&M and click Update.
  3. Check and understand the notes for updating the agency, and click OK. The DLI agency permissions are updated.
     Figure 1 Configuring an agency for DLI
     
  4. Once configured, you can check the agency dli_management_agency in the agency list on the IAM console.

Step 1: Create an RDS MySQL Instance

Step 2: Create an RDS Database Table

1. Log in to the RDS management console.
2. In the upper left corner of the management console, select the target region and project.
3. On the Instances page, locate the DB instance you just created and record its floating IP address.
   Figure 2 Checking the floating IP address
   
4. Locate the RDS for MySQL DB instance you created, click More in the Operation column, and select Log In. On the displayed page, enter the username and password for logging in to the instance and click Test Connection. After Connection is successful is displayed, click Log In.
   Figure 3 RDS console
   
   Figure 4 Logging in to an instance
   
5. Click Create Database. In the displayed dialog box, enter database name dli_demo. Then, click OK.
6. Click SQL Query and run the following SQL statement to create a table:

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

Step 3: Create an Elastic Resource Pool and Add Queues to the Pool

To execute SQL jobs in datasource scenarios, you must use your own SQL queue as the existing default queue cannot be used. In this example, create an elastic resource pool named dli_resource_pool and a queue named dli_queue_01.

1. Log in to the DLI management console.
2. In the navigation pane on the left, choose Resources > Resource Pool.
3. On the displayed page, click Buy Resource Pool in the upper right corner.
4. On the displayed page, set the parameters.
5. In this example, we will buy the resource pool in the CN East-Shanghai2 region. Table 3 describes the parameters.

   Table 3 Parameters

   Parameter	Description	Example Value
   Region	Select a region where you want to buy the elastic resource pool.	CN East-Shanghai2
   Project	Project uniquely preset by the system for each region	Default
   Name	Name of the elastic resource pool	dli_resource_pool
   Specifications	Specifications of the elastic resource pool	Standard
   CU Range	The maximum and minimum CUs allowed for the elastic resource pool	64-64
   CIDR Block	CIDR block the elastic resource pool belongs to. If you use an enhanced datasource connection, this CIDR block cannot overlap that of the data source. Once set, this CIDR block cannot be changed.	172.16.0.0/19
   Enterprise Project	Select an enterprise project for the elastic resource pool.	default

6. Click Buy.
7. Click Submit.
8. In the elastic resource pool list, locate the pool you just created and click Add Queue in the Operation column.
9. Set the basic parameters listed below.

   Table 4 Basic parameters for adding a queue

   Parameter	Description	Example Value
   Name	Name of the queue to add	dli_queue_01
   Type	Type of the queue To execute SQL jobs, select For SQL. To execute Flink or Spark jobs, select For general purpose.	_
   Engine	SQL queue engine. The options include Spark and Trino.	_
   Enterprise Project	Select an enterprise project.	default

10. Click Next and configure scaling policies for the queue.

    Click Create to add a scaling policy with varying priority, period, minimum CUs, and maximum CUs.

    Figure 5 shows the scaling policy configured in this example.

    Figure 5 Configuring a scaling policy when adding a queue
    

    Table 5 Scaling policy parameters

    Parameter	Description	Example Value
    Priority	Priority of the scaling policy in the current elastic resource pool. A larger value indicates a higher priority. In this example, only one scaling policy is configured, so its priority is set to 1 by default.	1
    Period	The first scaling policy is the default policy, and its Period parameter configuration cannot be deleted or modified. The period for the scaling policy is from 00 to 24.	00–24
    Min CU	Minimum number of CUs allowed by the scaling policy	16
    Max CU	Maximum number of CUs allowed by the scaling policy	64

11. Click OK.

Step 4: Create an Enhanced Datasource Connection

1. Create a rule on the security group of the RDS DB instance to allow access from the CIDR block of the DLI queue.
   1. Go to the RDS management console and choose Instances in the navigation pane on the left. In the instance list, click the name of the RDS for MySQL DB instance you created to access its basic information page.
   2. In the navigation pane on the left, choose Connectivity & Security. In the Security Group Rules area, find the Inbound Rules tab and click Add Inbound Rule.

      For example, if the CIDR block of the queue is 172.16.0.0/19, add the rule as follows:

      • Set Priority to 1 and Action to Allow.
      • Type: Select IPv4.
      • Protocol & Port: Select Protocols/TCP (Custom) as the protocol and leave the port number blank.
      • Source: Select IP Address and enter 172.16.0.0/19.

      Click OK. The security group rule is added.

2. Create an enhanced datasource connection between RDS for MySQL and DLI.
   

   The CIDR block of the elastic resource pool bound to a datasource connection cannot overlap that of the data source.

   1. Go to the DLI management console and choose Datasource Connections in the navigation pane on the left.
   2. On the displayed Enhanced tab, click Create. Set the following parameters:
      • Connection Name: dlirds
      • Resource Pool: Select the elastic resource pool created in Step 3: Create an Elastic Resource Pool and Add Queues to the Pool.
      • VPC: Select the VPC where the RDS for MySQL DB instance is, that is, the VPC selected in Step 2: Create an RDS Database Table.
      • Subnet: Select the subnet where the RDS for MySQL DB instance is, that is, the subnet selected in Step 2: Create an RDS Database Table.

        To check the subnet information, go to the RDS console, choose Instances in the navigation pane on the left, click the name of the RDS for MySQL DB instance you created, and find Subnet in the Connection Information area on the displayed page.

   3. Click OK.
   4. In the Enhanced tab, click the created connection dlirds to view its VPC Peering ID and Connection Status. If the connection status is Active, the connection is successful.
   5. Test if the queue can connect to the RDS for MySQL DB instance.
      1. In the navigation pane on the left, choose Resources > Queue Management. On the displayed page, locate the queue added in Step 3: Create an Elastic Resource Pool and Add Queues to the Pool, click More in the Operation column, and select Test Address Connectivity.
         Figure 6 Testing address connectivity
         
      2. Enter the floating IP address of the RDS for MySQL DB instance recorded in Step 2: Create an RDS Database Table.
         

         On the Instance Management page, click the target DB instance. On the displayed page, choose Connection Information > Floating IP Address to obtain the floating IP address.

         • If the IP address is reachable, the DLI queue can connect to the RDS for MySQL DB instance through the created enhanced datasource connection.
           Figure 7 Testing address connectivity
           
         • If the IP address is unreachable, rectify the fault by referring to Why Is a Datasource Connection Successfully Created But the Network Connectivity Test Fails?

           Once the fault is rectified, retest the network connectivity.

Step 5: Create a Datasource Authentication

When analyzing across multiple sources, you are not advised to configure authentication information directly in a job as it can lead to password leakage. Instead, you are advised to use datasource authentication provided by DLI to securely store data source authentication information.

To connect a Spark SQL job to an RDS for MySQL data source, you can create a password-type datasource authentication.

1. Log in to the DLI management console.
2. In the navigation pane on the left, choose Datasource Connections. On the displayed page, click Datasource Authentication.
3. Click Create.

   Set authentication parameters based on Table 6.

   Table 6 Datasource authentication parameters

   Parameter	Description
   Type	Select Password.
   Authentication Certificate	Name of the datasource authentication to create Only numbers, letters, and underscores (_) are allowed. The name cannot contain only numbers or start with an underscore (_). The value can contain a maximum of 128 characters.
   Username	Username for logging in to the RDS for MySQL DB instance
   Password	Password for logging in to the RDS for MySQL DB instance

   Figure 8 Creating a Password-type datasource authentication
   

Step 6: Submit a SQL Job

In this example, a SQL job accesses an RDS table using a datasource connection.

1. On the DLI management console, choose SQL Editor in the navigation pane on the left.
2. In the editing window on the right of the SQL Editor page, enter the following SQL statement to create database db1 and click Execute.

   create database db1;

3. On the top of the editing window, choose the dli_queue_01 queue and the db1 database. Enter the following SQL statements to create a table, insert data to the table, and query the data. Click Execute.
   View the query result to verify that the query is successful and the datasource connection works.

   CREATE TABLE IF NOT EXISTS rds_test USING JDBC OPTIONS (
   'url' = 'jdbc:mysql://{{ip}}:{{port}}',  // Private IP address and port of RDS
     'driver' = 'com.mysql.jdbc.Driver',
   'dbtable' = 'dli_demo.tabletest', // Name of the created DB instance and table name
       'passwdauth'="xxxxx" // Name of the datasource authentication of the password type created on DLI. If datasource authentication is used, you do not need to set the username and password for the job.
   )
   
   insert into rds_test VALUES ('123','abc');
   
   
   SELECT * from rds_test;