:Create a MySQL table and read and write data from and to the table

Create a MySQL table

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

Query data from the MySQL table

1. Log on to the Data Lake Analytics console.

2. In the left-side navigation pane, click Endpoint. In the endpoint list, locate the row that contains the target endpoint and click Log on in DMS in the Actions column. On the DMS for Data Lake Analytics page that appears, query data from the MySQL table named person.

   You can also use the MySQL client to connect to the mapping database hello_mysql_vpc_rds in DLA, and to read and write data from and to the 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)

Cleanse data from OSS and write the data to ApsaraDB for RDS

DLA is seldom used to read data from ApsaraDB for RDS. ApsaraDB for RDS can handle only a limited amount of data and is not applicable for big data analysis. A more common DLA application scenario is to analyze massive amounts of data that is stored in OSS or Table Store, and then write the result back to ApsaraDB for RDS for frontend services to use.

Take the person table as an example. Execute the following statements to convert the first 10 customer records in the oss_db table, and then insert them into the hello_mysql_vpc_rds.person table:

For more information about how to read data from OSS in DLA, see Use DLA to read data from OSS.

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> select * from person;
+------+--------------------+------+
| id   | name               | age  |
+------+--------------------+------+
|    1 | james              |   10 |
|    2 | bond               |   20 |
|    3 | jack               |   30 |
 |    4 | lucy               |   40 |
|    1 | Customer#000000001 |   21 |
 |    3 | Customer#000000003 |   23 |
|    5 | Customer#000000005 |   25 |
|    2 | Customer#000000002 |   22 |
|    4 | Customer#000000004 |   24 |
|    7 | Customer#000000007 |   27 |
|    6 | Customer#000000006 |   26 |
|    9 | Customer#000000009 |   29 |
|    8 | Customer#000000008 |   28 |
|   10 | Customer#000000010 |   30 |
+------+--------------------+------+
14 rows in set (0.26 sec)