AnalyticDB for PostgreSQL, formerly referred to as HybridDB for PostgreSQL, is a massively parallel processing (MPP) data warehousing service that is designed to analyze large volumes of data online. It is compatible with the PostgreSQL and Oracle database ecosystems. It also supports row-oriented storage and column-oriented storage. Data Lake Analytics (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 basic information such as IP address whitelists, networks, and accounts are configured. For more information, see Quick Start.

      Your AnalyticDB for PostgreSQL instance is in a 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.
      • This function 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. A database and a table are created in your AnalyticDB for PostgreSQL instance. For more information, see and .

      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 AnalyticDB for PostgreSQL database is reset. For more information, see t1916495.html#concept_2420223.

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

Step 1: Create a schema and a table

  1. Connect DLA to your AnalyticDB for PostgreSQL instance.

  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 account that is used to access the AnalyticDB for PostgreSQL database.
    PASSWORD The password that is used to access the AnalyticDB for PostgreSQL database.
    VPC_ID The ID of the virtual private cloud (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 execute the MSCK REPAIR DATABASE command in DLA to automatically identify the tables and synchronize the database schema 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 a 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)