Create a PolarDB table and read and write data from the table

Create a PolarDB table

1. Log on to the Data Lake Analytics console.

2. In the left-side navigation pane, click SQL access point, find the required VPC, and then click Log on in DMS in the Actions column. On the page of DMS for Data Lake Analytics (DLA), create a table named polardb_person for the person table in your PolarDB database.

    create external table polardb_person (
           id int,
           name varchar(1023),
           age int
    )TBLPROPERTIES (
        TABLE_MAPPING = 'person'
    );

   TABLE_MAPPING: indicates table name mapping. In this example, the person table in the PolarDB database maps the polardb_person table in the DLA console.

Read data from a PolarDB table

After you create a PolarDB schema and a table, you can connect to the DLA console by using the MySQL client or MySQL CLI tool. Then, you can compile SQL statements to read data from the table.

In this example, the MySQL CLI tool is used to connect to the DLA console so that data is read from the PolarDB table.

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

Write data to a PolarDB table

In this example, the MySQL CLI tool is used to connect to the DLA console so that data is written to the person table in the PolarDB database.

mysql> insert into polardb_person 
  values (5, 'Alan', 50), (6, 'Alice', 60);

 mysql> select * from polardb_person;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lucy  |   40 |
|    5 | Alan  |   50 |
|    6 | Alice  |   60 |