AnalyticDB for PostgreSQL, formerly referred to as HybridDB for PostgreSQL, is a massively parallel processing (MPP) data warehousing service. It is designed to analyze large volumes of data online. AnalyticDB for PostgreSQL is compatible with the PostgreSQL and Oracle database ecosystems. AnalyticDB for PostgreSQL supports row-oriented storage and column-oriented storage. DLA allows you to access an AnalyticDB for PostgreSQL data source and use standard SQL statements to read data from and write data to this data source. Prerequisites:

  • AnalyticDB for PostgreSQL

    1. An AnalyticDB for PostgreSQL instance is created and the basic information such as IP address whitelists, networks, and accounts are configured. For more information, see Get started with AnalyticDB for PostgreSQL.

      Your AnalyticDB for PostgreSQL instance is in a virtual private cloud (VPC), and DLA cannot access resources in the VPC by default. To enable DLA to access your AnalyticDB for PostgreSQL instance, you must use the reverse access technique for VPCs. Before you use reverse access, add the Classless Inter-Domain Routing (CIDR) block 100.104.0.0/16 to an IP address whitelist of your AnalyticDB for PostgreSQL instance.

      Note
      • After you add the CIDR block 100.104.0.0/16 to an IP address whitelist of your AnalyticDB for PostgreSQL instance, DLA can use the reverse access technique of VPC to access your AnalyticDB for PostgreSQL instance.
      • The reverse access technique can be used only in the China (Shenzhen) region. Therefore, your AnalyticDB for PostgreSQL instance and DLA must reside in the China (Shenzhen) region.
    2. The required database and table are created in your AnalyticDB for PostgreSQL instance. For more information, see Manage databases and Create and manage tables.

      create database adp_demo;
      ​CREATE TABLE products (
            product_no integer PRIMARY KEY,
            name text,
            price numeric)
      DISTRIBUTED BY (product_no);

      In this topic, the adp_demo database and the products table are created in your AnalyticDB for PostgreSQL instance.

    3. The test data is written to the products table by executing the INSERT INTO statement.

      ​INSERT INTO products (product_no, name, price) VALUES
       (1, 'Cheese', 9.99),
       (2, 'Bread', 1.99),
       (3, 'Milk', 2.99);​
  • DLA

    1. DLA is activated. For more information, see Activate DLA.

    2. The password of the Alibaba Cloud account that is used to access the database is reset.

    3. An endpoint is created. For more information, see Create an endpoint.

Step 1: Create a schema and a table

  1. Connect to DLA.
  2. Create an AnalyticDB for PostgreSQL schema in DLA by using the following statement:

    CREATE SCHEMA  dla_adbpg_test_db WITH DBPROPERTIES (
     CATALOG = 'adbpg',
     LOCATION = 'jdbc:postgresql://gp-bp13******.gpdb.rds.aliyuncs.com:3432/db-name',
     USER = 'user-name',
     PASSWORD = 'password',
     INSTANCE_ID = 'gp-bp*******',
     VPC_ID = 'vpc-bp********'
     );​
    Parameter Description
    CATALOG The schema you created. The value adbpg indicates that an AnalyticDB for PostgreSQL schema is created.
    LOCATION The connection information of your AnalyticDB for PostgreSQL instance. The value is in the format of jdbc:postgresql://Internal endpoint of your AnalyticDB for PostgreSQL instance:3432/db-name.
    USER The database account of your AnalyticDB for PostgreSQL instance.
    PASSWORD The password of the database account.
    VPC_ID The ID of the VPC to which your AnalyticDB for PostgreSQL instance belongs.
    INSTANCE_ID The ID of your AnalyticDB for PostgreSQL instance.
  3. Create the products table in the dla_adbpg_test_db schema by using SQL statements.

    • Syntax 1

      ​create external table dla_tablename (
           column1 datatype,
           column2 datatype,
           column3 datatype
      ) tblproperties(
      table_mapping = 'schema_name.adbpg_tablename'
      );​
    • Syntax 2

      create external table dla_tablename like mapping('schema_name.adbpg_tablename');
    • Syntax 3

      msck repair database dla_adbpg_db

      If tables in the AnalyticDB for PostgreSQL database contain large amounts of data or you cannot determine the tables that are created in the database, you can run the MSCK REPAIR DATABASE command in DLA to automatically identify the tables and synchronize the table schema in AnalyticDB for PostgreSQL to the DLA schema.

      Note An AnalyticDB for PostgreSQL database contains multiple schemas and these schemas may have tables with identical names. The MSCK REPAIR DATABASE command can identify tables only in the public schema.
      ​create external table products (
           product_no int,
           name varchar(1023),
           price double
      ) tblproperties(
      table_mapping = 'public.products'
      );​

      After you create the table, you can execute the SELECT statement to read data from the AnalyticDB for PostgreSQL database in DLA.

Write data to the table

You can execute the INSERT INTO statement to write a data record to the products table in the AnalyticDB for PostgreSQL database.

insert into dla_adbpg_test_db.products values(4, 'Apple', 10.1)