Data Lake Analytics, as the Data processing hub on the cloud, has recently added support for PolarDB, which is the next generation relational distributed cloud native database developed by Ali Cloud, 100% compatible with MySQL, with storage capacity up to 100T. Performance up to 6 times that of MySQL. This tutorial takes you through DLA’s PolarDB support.

Creating a database

The syntax for creating an underlying mapping to a PolarDB facade in DLA is as follows:

CREATE SCHEMA porlardb_test WITH DBPROPERTIES (
  CATALOG = 'mysql', 
  LOCATION = 'jdbc:mysql://pc-bp1dlebalabala.rwlb.rds.aliyuncs.com:3306/dla_test',
  USER = 'dla_test_1',
  PASSWORD = 'the-fake-password',
  VPC_ID = 'vpc-2zeij924vxd303kwifake',
  INSTANCE_ID = 'rm-2zer0vg58mfo5fake'
);Copy the code

There are two more attributes: VPC_ID and INSTANCE_ID. VPC_ID is the VPC ID of your PolarDB, as shown below:

These two additional information are required for table building because the user’s database is in the user’s own VPC. By default, DLA cannot access the resources in the user’s VPC. In order to enable DLA to access the PolarDB data, we need to use aliyun’s VPC reverse access technology.

Permission statement: When you create the library in this way, you agree to use the VPC reverse access technology to read and write your PolarDB.

You also need to add the IP address segment 100.104.0.0/16 to your PolarDB whitelist. This is the IP address segment that our VPC reversely access, as shown below:

Careful readers will also notice that our CATALOG is written for mysql, not polarDB. This is because PolarDB is 100% compatible with mysql, so we can access it directly using mysql protocol.

Create a table

After the database is built, we can create the table, we will first create the following person table in your PolarDB for testing:

create table person (
       id int,
       name varchar(1023),
       age int
);Copy the code

And insert test data into it:

insert into person 
  values (1, 'james', 10), 
         (2, 'bond', 20), 
         (3, 'jack', 30), 
         (4, 'lucy', 40);Copy the code

Then you can create the corresponding mapping table in the DLA database:

create external table person (
       id int,
       name varchar(1023),
       age int
);Copy the code

In this way, we connect to the DLA database through the MySQL client, so that we can query the data in the PolarDB database:

mysql> select * from person; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | james | 10 | | 2 | bond | 20 | | 3 | jack | 30  | | 4 | lucy | 40 | +------+-------+------+ 4 rowsin set (0.35 sec)Copy the code

conclusion

Today mainly introduced if in THE DLA inside the query PolarDB data, because PolarDB itself compatible with MySQL protocol, so in the USE of DLA with MySQL is basically the same, so the introduction here is relatively simple, more content is left to the reader to explore.

Yq.aliyun.com/articles/68…