All Products
Search
Document Center

Background and preparations

Last Updated: Apr 24, 2019

Background

Data Lake Analytics (DLA) serves as the hub for in-cloud data processing. It allows you to connect to your MySQL, SQL Server, and PostgreSQL databases created on instances in Alibaba Cloud ApsaraDB RDS for MySQL, SQL Server, and PostgreSQL respectively, or in Elastic Compute Service (ECS) through standard Java Database Connectivity (JDBC). In addition, you can query data in these databases.

This topic describes how to connect a ECS-based user-created MySQL database to DLA and then read and write data from and to the MySQL database.

Prerequisites

Note: DLA connects to a user-created ECS-based MySQL database through a VPC. Make sure that DLA is in the same VPC of the ECS instance.

Before reading and writing data from and to the user-created MySQL database through DLA, connect to the ECS instance, create a MySQL database, and write the test data to the database according to the following steps.

  1. Create a MySQL database named dla_db.

    1. MySQL [(none)]> CREATE DATABASE dla_db;
  2. Run the following SQL statements to create a user named dla_user and grant permissions to the user. In subsequent operations, you can use DLA to read data from the MySQL database as this user.

    1. MySQL [(none)]> USE mysql;
    2. MySQL [mysql]> CREATE USER 'dla_user'@'%' IDENTIFIED BY 'dla_userpasswd';
    3. MySQL [mysql]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on dla_db.* TO 'dla_user'@'%';
    4. MySQL [mysql]> flush privileges;

    Note: If you use the same primary account or sub-account for DLA and the ECS instance, you can change GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on dla_db. * TO 'dla_user'@'%'; to the following statement, allowing only DLA to connect to the MySQL database:

    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on dla_db.* TO 'dla_user'@'100.104.0.0/255.255.0.0';

  3. Run the following SQL statement to create a table named person and insert the test data:

    1. CREATE TABLE person (
    2. id int,
    3. name varchar(1023),
    4. age int
    5. );
    1. INSERT INTO person VALUES
    2. (1, 'james', 10),
    3. (2, 'bond', 20),

To enable DLA to access the user-created ECS-based MySQL database, you need to add security group rules in the ECS security group to authorize the CIDR block 100.104.0.0/16.

When adding security group rules, set Protocol to MySQL and Authorization Object to 100.104.0.0/16.

Procedure

  1. Create a MySQL schema

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