Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
Help Center/ Data Lake Insight/ Getting Started/ Using DLI to Submit a SQL Job to Query RDS for MySQL Data

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

Updated on 2025-01-10 GMT+08:00

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.

Table 1 Procedure for using DLI to submit a SQL job to query RDS for MySQL data

Procedure

Description

Step 1: Create an RDS MySQL Instance

Create an RDS for MySQL instance for the example scenario.

Step 2: Create an RDS Database Table

Log in to the RDS for MySQL DB instance and create a database and a table.

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

Create compute resources required for submitting jobs.

Step 4: Create an Enhanced Datasource Connection

Create an enhanced datasource connection to connect the DLI elastic resource pool and the RDS for MySQL DB instance.

Step 5: Create a Datasource Authentication

Create a datasource authentication to store the access credentials required by DLI to read from and write to RDS for MySQL data.

Step 6: Submit a SQL Job

Use standard SQL statements to query and analyze data.

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. View 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

In this example, assuming the job name is JobSample, RDS is used as the data source to create an RDS for MySQL DB instance.

For details, see Buying an RDS for MySQL DB Instance.

  1. Log in to the RDS management console.
  2. Select a region and a project in the upper left corner.
  3. In the navigation pane on the left, choose Instances. On the displayed page, click Buy DB Instance in the upper right corner.
  4. On the Buy DB Instance page, select a billing mode, set instance parameters, and click Buy.

    Set the parameters listed below based on your service planning.

    For how to set RDS for MySQL DB instance parameters, see Buying an RDS for MySQL DB Instance.

    Table 2 RDS for MySQL instance parameters

    Parameter

    Description

    Example Value

    Billing Mode

    Billing mode of the RDS for MySQL DB instance

    Pay-per-use

    Region

    Region where you want to create the instance

    CN East-Shanghai2

    DB Instance Name

    Instance name

    rds-demo

    DB Engine

    MySQL

    MySQL

    DB Engine Version

    If you select MySQL for DB Engine, select an engine version that best suits your service needs. You are advised to select the latest available version for more stable performance, higher security, and greater reliability.

    8.0

    DB Instance Type

    Primary/standby mode of the instance

    Single

    Storage Type

    Determines the instance read/write speed. The higher the maximum throughput, the faster the read and write speeds.

    Cloud SSD

    AZ

    For a single DB instance, you only need to select a single AZ.

    -

    Time Zone

    Select a time zone based on the region you selected. You can change it after the DB instance is created.

    Retain the default value.

    Instance Class

    vCPUs and memory. These instance classes support varying number of connections and maximum IOPS.

    2 vCPUs | 4 GB

    Storage Space

    If the storage type is cloud SSD or extreme SSD, you can enable storage autoscaling. If the available storage drops to a specified threshold, autoscaling is triggered.

    40 GB

    Disk Encryption

    Determine whether to enable disk encryption.

    Disable

    VPC

    Select an existing VPC.

    For how to recreate a VPC and subnet, refer to .

    NOTE:

    In datasource scenarios, the CIDR block of the data source cannot overlap that of the elastic resource pool.

    -

    Database Port

    Port 3306 is used by default.

    3306

    Security Group

    Enhances security by providing rules that control access to RDS from other services.

    The security group where the data source is must allow access from the CIDR block of the DLI elastic resource pool.

    -

    Password

    Set a password for logging in to the DB instance.

    -

    Administrator

    root

    root

    Administrator Password

    Administrator password

    -

    Parameter Template

    A template of parameters for creating an instance. The template contains engine configuration values that are applied to one or more instances.

    Default-MySQL-5.7

    Table Name

    Determines whether the table name is case-insensitive.

    Case insensitive

    Enterprise Project

    If the instance has been associated with an enterprise project, select the target project from the Enterprise Project drop-down list.

    default

    Quantity

    Number of instances to buy

    1

  5. Click Next. The confirmation page is displayed.
  6. Click Submit.
  7. To view and manage the DB instance, go to the Instance Management page.

    During the creation process, the DB instance status is Creating. When the creation process is complete, the instance status will change to Available. You can view the detailed progress and result of the task on the Task Center page.

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 Viewing 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.
    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

  5. Click Buy.
  6. Click Submit.
  7. In the elastic resource pool list, locate the pool you just created and click Add Queue in the Operation column.
  8. 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 are Spark and HetuEngine.

    _

    Enterprise Project

    Select an enterprise project.

    default

  9. 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

  10. 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.

    For how to create an enhanced datasource connection, see Creating an Enhanced Datasource Connection.

    NOTE:

    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:
    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.
        NOTE:

        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.

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;

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback