Read data from and write data to ApsaraDB RDS for PostgreSQL - Data Lake Analytics - Deprecated

The method for creating an ApsaraDB RDS for PostgreSQL schema and table is different from that for creating an ApsaraDB RDS for MySQL schema and table. This topic describes how to read data from and write data to an ApsaraDB RDS for PostgreSQL database.

Procedure

Log on to the DLA console.

In the left-side navigation pane, choose Serverless Presto > SQL access point. On the SQL access point page, find your virtual private cloud (VPC) and click Log on in DMS in the Actions column. Then, execute the following statement to create an ApsaraDB RDS for PostgreSQL schema.

You can also connect to DLA by using the MySQL client or program code. Then, execute the following statement to create an ApsaraDB RDS for PostgreSQL schema:

CREATE SCHEMA `hello_postgresql_vpc_rds` WITH DBPROPERTIES 
 ( 
 CATALOG = 'postgresql', 
 LOCATION = 'jdbc:postgresql://rm-bp*******fake.pg.rds.aliyuncs.com:3433/rds_postgresql_dbname',
  USER='rds_postgresql_username',
  PASSWORD='rds_postgresql_password',
  INSTANCE_ID = 'rds_postgresql_instance_id',
  VPC_ID = 'rds_postgresql_vpcid'
 );

The following table describes the parameters in the preceding statement.


Parameter	Description
CATALOG	The type of the schema that you want to create. The value postgresql indicates that an ApsaraDB RDS for PostgreSQL schema is to be created.
LOCATION	The JDBC URL of the ApsaraDB RDS for PostgreSQL database. The URL is in the format of `jdbc:postgresql://Endpoint of the ApsaraDB RDS for PostgreSQL database:3433/rds_postgresql_dbname`.
USER	The username that is used to log on to the ApsaraDB RDS for PostgreSQL database.
PASSWORD	The password of the username.
INSTANCE_ID	The ID of the ApsaraDB RDS for PostgreSQL instance.
VPC_ID	The ID of the VPC to which the ApsaraDB RDS for PostgreSQL instance belongs.

Create a table
To create an ApsaraDB RDS for PostgreSQL table, you must specify the names of the schema and table in table_mapping.
```
create external table person (
       id int,
       name varchar(1023),
       age int
) tblproperties(
  table_mapping = 'hello_postgresql_vpc_rds.person'
);
```
After you create the table, you can connect to DLA by using a client or program code and read data from the ApsaraDB RDS for PostgreSQL database. You can also cleanse data in Object Storage Service (OSS) or Tablestore and write data to the ApsaraDB RDS for PostgreSQL database.