AnalyticDB for PostgreSQL:Use OSS foreign tables for data lake analysis

Description

OSS FDW is developed based on PostgreSQL FDW. OSS FDW allows you to perform the following operations:

• Import OSS data to row-oriented or column-oriented tables of AnalyticDB for PostgreSQL instances for accelerated data analysis.
• Query and analyze large amounts of OSS data.
• Join OSS foreign tables with AnalyticDB for PostgreSQL tables for data analysis.

OSS FDW supports the following data objects in a variety of business scenarios:

• Uncompressed CSV, TEXT, JSON, and JSON Lines objects.
• GZIP- and standard Snappy-compressed CSV and TEXT objects.
• GZIP-compressed JSON and JSON Lines objects.
• ORC binary objects.
• Parquet binary objects.
• Arvo binary objects.

Preparations

Prepare OSS data

Prepare a sample file named example.csv.

Obtain the OSS bucket information

The following procedure demonstrates how to obtain the bucket name, object path, endpoint, and bucket domain name.

1. Log on to the OSS console.
2. In the left-side navigation pane, click Buckets.
3. On the Buckets page, click the name of a bucket.
   You can obtain a bucket name on the Buckets page.
4. On the Files page, obtain an object path.
5. In the left-side navigation pane, click Overview.
6. In the Domain Names section of the Overview page, obtain an endpoint and a bucket domain name.
   We recommend that you use the domain name of VPC Access from ECS (Internal Network) for data access.

Obtain the AccessKey ID and AccessKey secret

For more information about how to obtain the AccessKey ID and AccessKey secret, see Obtain an AccessKey pair.

Create an OSS server

Execute a CREATE SERVER statement to create an OSS server. You must specify a name for the OSS server that you want to access. For more information about CREATE SERVER, see CREATE SERVER.

Syntax

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameters

The following table describes the parameters involved in OPTIONS.

Example

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
  );

You can also execute an ALTER SERVER statement to modify configurations of an OSS server. For more information, see ALTER SERVER.

Examples for modifying configurations of an OSS server:

• Modify a parameter of the OSS server.

  ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');

• Add a parameter to the OSS server.

  ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');

• Remove a parameter from the OSS server.

  ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);

You can also execute a DROP SERVER statement to delete an OSS server. For more information, see DROP SERVER.

Create a user mapping to the OSS server

After you create an OSS server, you must create a user that accesses the OSS server. You can execute a CREATE USER MAPPING statement to create a user mapping between an AnalyticDB for PostgreSQL database user and the user that accesses the OSS server. For more information, see CREATE USER MAPPING.

Syntax

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER servername
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameters

The following table describes the parameters involved in OPTIONS.

Example

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI5t7Ge***************',
        key 'FikziJd2La*******************'
    );

You can also execute a DROP USER MAPPING statement to delete a user. For more information, see DROP USER MAPPING.

Create an OSS foreign table

After you create an OSS server and a user to access the server, you can execute a CREATE FOREIGN TABLE statement to create an OSS foreign table. For more information, see CREATE FOREIGN TABLE.

Syntax

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

Parameters

The following table describes the parameters involved in OPTIONS.

Example

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');

You can also execute a DROP FOREIGN TABLE statement to delete an OSS foreign table. For more information, see DROP FOREIGN TABLE.

Import OSS data to an AnalyticDB for PostgreSQL table

1. Upload the prepared sample file example.csv to OSS. For more information, see Upload objects.
   Note

   • We recommend that you use the same data encoding format for data objects and databases to eliminate the encoding process and improve efficiency. The default database encoding format is UTF-8.
   • All compute nodes of AnalyticDB for PostgreSQL use a polling mechanism to read data objects of OSS in parallel.
   • AnalyticDB for PostgreSQL can read multiple CSV and TEXT objects in parallel. By default, four objects can be read in parallel. To maximize the read efficiency, we recommend that you set the number of objects that can be read in parallel to an integer multiple of the number of compute node cores. The number of compute node cores is the product of the number of compute nodes and the number of cores per compute node.
2. Connect to an AnalyticDB for PostgreSQL database. For more information, see Use client tools to connect to an instance.
3. Create an OSS server.

   CREATE SERVER oss_serv
       FOREIGN DATA WRAPPER oss_fdw
       OPTIONS (
           endpoint 'oss-cn-********.aliyuncs.com',
           bucket 'adb-pg'
     );

4. Create a user to access the OSS server.

   CREATE USER MAPPING FOR PUBLIC
       SERVER oss_serv
       OPTIONS (
           id 'LTAI5t7Ge***************',
           key 'FikziJd2La*******************'
       );

5. Create an OSS foreign table named ossexample.

   CREATE FOREIGN TABLE ossexample (
       date text,
       time text,
       open float,
       high float,
       low float,
       volume int
   ) SERVER oss_serv OPTIONS (dir 'oss_adb/', format 'csv');

6. Execute an INSERT or CREATE TABLE AS statement to import OSS data.
   • INSERT
     1. To execute an INSERT statement to import data, create an AnalyticDB for PostgreSQL table named adbexample that has the same schema as the foreign table.

        CREATE TABLE adbexample (
            date text,
            time text,
            open float,
            high float,
            low float,
            volume int
        ) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5);

     2. Execute an INSERT statement to import data of the foreign table ossexample to the AnalyticDB for PostgreSQL table adbexample.

        INSERT INTO adbexample SELECT * FROM ossexample;

   • CREATE TABLE AS

     Execute a CREATE TABLE AS statement to create an AnalyticDB for PostgreSQL table named adbexample and import data from the foreign table ossexample.

     CREATE TABLE adbexample AS SELECT * FROM ossexample DISTRIBUTED BY (volume);

Query and analyze OSS data

You can query the data of OSS foreign tables in the same way as that for AnalyticDB for PostgreSQL tables. The following query methods can be used:

• Query data by specifying a key-value pair.

  SELECT * FROM ossexample WHERE volume = 5;

• Query data by using an aggregate function.

  SELECT count(*) FROM ossexample WHERE volume = 5;

• Query data by specifying columns and using GROUP BY and LIMIT clauses.

  SELECT low, sum(volume)
    FROM ossexample
   GROUP BY low
   ORDER BY low
   limit 5;

Join the OSS foreign table with an AnalyticDB for PostgreSQL table for data analysis

1. Create an AnalyticDB for PostgreSQL table named example for join analysis and insert data into the table.

   CREATE TABLE example (id int, volume int);
   INSERT INTO example VALUES(1,1), (2,3), (4,5);

2. Join the OSS foreign table ossexample with the AnalyticDB for PostgreSQL table example for data query.

   SELECT example.volume, min(high), max(low)
   FROM
   ossexample,
   example
   WHERE ossexample.volume = example.volume
   GROUP BY(example.volume)
   ORDER BY example.volume;

Fault tolerance

OSS FDW uses the log_errors and segment_reject_limit parameters to provide the fault tolerance feature so that the scanning of OSS foreign tables is not interrupted by errors in raw data.

For more information about the log_errors and segment_reject_limit parameters, see Create an OSS foreign table.

• Create an OSS foreign table that supports fault tolerance.

  CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
      SERVER oss_serv
      OPTIONS (log_errors 'true',         -- Record the information of error rows.
               segment_reject_limit '10', -- Specify the threshold to stop scanning. If the number of error rows exceeds 10, the scanning stops. 
               dir 'error_sales/',        -- Specify the OSS object directory that the foreign table matches.
               format 'csv',              -- Specify CSV as the format to parse objects.
               encoding 'utf8');          -- Specify the encoding format. 

• Query the logs of error rows.

  SELECT * FROM gp_read_error_log('oss_error_sales');

• Delete the logs of error rows.

  SELECT gp_truncate_error_log('oss_error_sales');