:Background and preparations

Background

Data Lake Analytics (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.

Notes

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.

Permission statement: 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:

1. Create a MySQL instance

2. Configure the whitelist

3. Create an account and a database

4. Connect to the instance

5. 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);

Procedure

On the DMS for Data Lake Analytics page, compile SQL statements to create a MySQL schema, a MySQL table, and read data from the MySQL file. Alternatively, connect to DLA through the MySQL client or MySQL CLI tool, and then compile SQL statements to create a MySQL schema, a MySQL table, and read data from the MySQL file.

1. Create a MySQL schema

2. Create a MySQL table and read and write data from and to the table