Data Lake Analytics (DLA) serves as a hub for cloud data processing. It can be connected to ApsaraDB RDS, ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, and ApsaraDB RDS for PostgreSQL as well as self-managed MySQL, SQL Server, PostgreSQL databases that are hosted on Elastic Compute Service (ECS) instances by using standard Java Database Connectivity (JDBC). DLA can also query data from these databases.

Prerequisites

Before you use DLA to read and write data from and to a self-managed MySQL database that is hosted on an ECS instance, you must enable DLA to access the ECS instance. For more information, see OverviewGuidelines on instance connection. Then, you must perform the following operations to create a MySQL database and write test data to the database.

Notice DLA is connected to a self-managed MySQL database that is hosted on an ECS instance over a virtual private cloud (VPC). Therefore, you must make sure that DLA resides in the same region as the ECS instance.
  1. Create a MySQL database named dla_db.
     MySQL [(none)]> CREATE DATABASE dla_db;
  2. Execute the following SQL statements to add the dla_user user and grant appropriate permissions to the dla_user user. Then, use DLA to read data from the MySQL database as this user.
     MySQL [(none)]> USE mysql;
     MySQL [mysql]> CREATE USER 'dla_user'@'%' IDENTIFIED BY 'dla_userpasswd';
     MySQL [mysql]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on dla_db. * TO 'dla_user'@'%';
     MySQL [mysql]> flush privileges;
    Notice If the Alibaba Cloud account or Resource Access Management (RAM) user for DLA is the same as that for the ECS instance, you can use GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on dla_db.* TO 'dla_user'@'100.104.0.0/255.255.0.0'; instead of GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on dla_db.* TO 'dla_user'@'%'; . Only DLA is allowed to access the MySQL database. This implements fine-grained permission control.
  3. Execute the following SQL statements to create a table named person in the MySQL database and write test data to the table:
     CREATE TABLE person (
       id int,
       name varchar(1023),
       age int
     );
     INSERT INTO person VALUES 
     (1, 'james', 10), 
     (2, 'bond', 20),
Note
  • To allow DLA to access the self-managed MySQL database that is hosted on the ECS instance, you must add a security group rule to allow access from the CIDR block 100.104.0.0/16. For more information, see Add security group rules.
  • When you add the security group rule, make sure that you select MySQL for the protocol type and set the authorization object to 100.104.0.0/16.