Read and write ApsaraDB RDS MySQL data - Data Lake Analytics - Deprecated

Background

DLA serves as a hub for in-cloud data processing. It allows you to query and analyze data in ApsaraDB RDS for MySQL, SQL Server, and PostgreSQL through standard JDBC and write the result back to ApsaraDB for RDS.

Taking ApsaraDB RDS for MySQL (MySQL) as an example, this topic describes how to read and write data from and to a MySQL database in DLA.

Note

Before you create a MySQL schema, SQL Server schema or PostgreSQL schema in DLA, you must add the CIDR block 100.104.0.0/16 to the whitelist of your ApsaraDB for RDS instance.
Your ApsaraDB for RDS instance is in a VPC, and by default DLA cannot access resources in the VPC. To enable DLA to access your ApsaraDB for RDS instance, you need to use the reverse VPC technology, that is, add the CIDR block 100.104.0.0/16 to the whitelist of your ApsaraDB for RDS instance.
When you use the method described in this topic to create a MySQL schema, you agree that we can use the reverse VPC technology to read and write data from and to your ApsaraDB for RDS instance.

Prerequisites

Before using DLA to read and write data from and to your ApsaraDB for RDS instance, you must have prepared test data in your ApsaraDB for RDS instance by performing the following steps:

Create an ApsaraDB RDS for MySQL instance
Configure an IP address whitelist for an ApsaraDB RDS for MySQL instance
Create accounts and databases for an ApsaraDB RDS for MySQL instance
Connect to an ApsaraDB RDS for MySQL instance
Create a table and insert the test data to the table.
Run the following statement to create a table named person in your MySQL database:
```
create table person (
id int,
name varchar(1023),
age int
);
```
Insert the test data to the person table:
```
insert into person
values
(1, 'james', 10),
(2, 'bond', 20),
(3, 'jack', 30),
(4, 'lucy', 40);
```

Step 1: Create an ApsaraDB RDS for MySQL schema

Log on to the Data Lake Analytics (DLA) console.

In the left-side navigation pane, choose Serverless Presto > SQL access point. On the SQL access point page, find your private virtual cloud (VPC) in the VPC Network section and click Log on in DMS in the Actions column. Then, execute the following statement to create an ApsaraDB RDS for MySQL schema:

You can also connect to DLA by using the MySQL client or program code. Then, execute the following statement to create an ApsaraDB RDS for MySQL schema:

CREATE SCHEMA hello_mysql_vpc_rds WITH DBPROPERTIES (
   CATALOG = 'mysql', 
   LOCATION = 'jdbc:mysql://rm-******fofake.mysql.rds.aliyuncs.com:3306/rds_mysql_dbname',
   USER = 'rds_mysqldb_username',
   PASSWORD = 'rds_mysqldb_password',
   INSTANCE_ID = 'rds_mysql_instance_id',
   VPC_ID = 'rds_mysqldb_vpcid'
 );


Parameter	Description
CATALOG	The type of the schema that you want to create. The value mysql indicates that an ApsaraDB RDS for MySQL schema is to be created.
LOCATION	The JDBC URL of the ApsaraDB RDS for MySQL database. The URL is in the format of `jdbc:mysql://Endpoint of the ApsaraDB RDS for MySQL database:3306/rds_mysql_dbname`.
USER	The username that is used to log on to the ApsaraDB RDS for MySQL database.
PASSWORD	The password of the username.
INSTANCE_ID	The ID of the ApsaraDB RDS for MySQL instance.
VPC_ID	The ID of the VPC to which the ApsaraDB RDS for MySQL instance belongs.

Step 2: Create a table

create external table person (
id int,
name varchar(1023),
age int
) tblproperties (
table_mapping = "person"
);

Step 3: Query data from the ApsaraDB RDS for MySQL database

After the table is created in DLA, you can execute the following SELECT statement to query data from the ApsaraDB RDS for MySQL database.

mysql> select * from person;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lucy  |   40 |
+------+-------+------+
4 rows in set (0.35 sec)

Step 4: Write OSS data to the ApsaraDB RDS for MySQL database

DLA is not typically used to read data from an ApsaraDB RDS for MySQL database for analysis. This is because an ApsaraDB for RDS database stores a limited amount of data and is not suitable for big data analysis. In most scenarios, DLA is used to analyze large amounts of data stored in OSS and Tablestore and then write back the result data to the ApsaraDB RDS for MySQL database for frontend business to use.

The following statement is used to convert 10 records of a customer in oss_db and insert these records into the hello_mysql_vpc_rds.person table.

mysql> insert into hello_mysql_vpc_rds.person
select c_custkey, c_name, c_custkey + 20 from oss_db.customer limit 10;
+------+
| rows |
+------+
|   10 |
+------+
1 row in set (4.57 sec)

mysql> insert into hello_mysql_vpc_rds.person
select c_custkey, c_name, c_custkey + 20 from oss_db.customer limit 10;
+------+
| rows |
+------+
|   10 |
+------+
1 row in set (4.57 sec)