AnalyticDB: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. For information about data type mappings between ORC and AnalyticDB for PostgreSQL, see the "Data type mappings between ORC and AnalyticDB for PostgreSQL" section of the Data type mappings for OSS foreign tables topic.

• Parquet binary objects. For information about data type mappings between Parquet and AnalyticDB for PostgreSQL, see the "Data type mappings between Parquet and AnalyticDB for PostgreSQL" section of the Data type mappings for OSS foreign tables topic.

• Arvo binary objects. For information about data type mappings between Arvo and AnalyticDB for PostgreSQL, see the "Data type mappings between Avro and AnalyticDB for PostgreSQL" section of the Data type mappings for OSS foreign tables topic.

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 Object Management page, obtain an object path.

5. In the left-side navigation pane, click Overview.

6. In the Port section of the Overview page, obtain an endpoint and the corresponding bucket domain name.

   We recommend that you use the endpoint of Access from ECS over the VPC (internal network) for data access.

Obtain the AccessKey ID and AccessKey secret

For information about how to obtain the AccessKey ID and AccessKey secret, see Create 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 the configurations of an OSS server. For more information, see ALTER SERVER.

Examples of modifying the configurations of an OSS server:

• Modify a parameter.

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

• Add a parameter.

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

• Remove a parameter.

  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.

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 the "Create an OSS foreign table" section of this topic.

• 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');

FAQ

Q: Can data stored in OSS also be deleted if I delete data from an OSS foreign table?

A: No, data stored in OSS cannot be deleted if you delete data from an OSS foreign table.

References

• Use partitioned OSS foreign tables

• Use OSS foreign tables to import and export data

• Best practices for performing operations on OSS foreign tables

• Data type mappings for OSS foreign tables