Read and write external data files by using the oss_fdw plugin

Prerequisites

The RDS instance runs PPAS 10.

oss_fdw example

# Create a plugin for apsaradb RDS for PPAS
select rds_manage_extension('create','oss_fdw');
# Create a server 
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou.aliyuncs.com', id 'xxx', key 'xxx', bucket 'mybucket');
# Create an oss external table.
CREATE FOREIGN TABLE ossexample 
    (date text, time text, open float,
     high float, low float, volume int) 
     SERVER ossserver 
     OPTIONS ( filepath 'osstest/example.csv', delimiter ',' ,
         format 'csv', encoding 'utf8', PARSE_ERRORS '100');
# Create a table for which data is loaded.
create table example
        (date text, time text, open float,
         high float, low float, volume int)
# Load data from ossexample to example.
insert into example select * from ossexample;

# oss_fdw estimates the file size in oss and formulates a query plan correctly.
explain insert into example select * from ossexample;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Insert on example (cost=0.00..1.60 rows=6 width=92)
   ->  Foreign Scan on ossexample (cost=0.00..1.60 rows=6 width=92)
         Foreign OssFile: osstest/example.csv.0
         Foreign OssFile Size: 728
(4 rows)
# Write the data in the example table to OSS.
insert into ossexample select * from example;
explain insert into ossexample select * from example;
                           QUERY PLAN
-----------------------------------------------------------------
 Insert on ossexample (cost=0.00..16.60 rows=660 width=92)
   ->  Seq Scan on example (cost=0.00..16.60 rows=660 width=92)
(2 rows)

For a description of the parameters, see the following section.

oss_fdw parameters

The oss_fdw plug-in uses a method similar to other Foreign Data Wrapper (FDW) interfaces to encapsulate external data stored in OSS. You can use oss_fdw to read data stored in OSS. This process is similar to reading data tables. oss_fdw provides unique parameters to connect and parse file data in OSS.

CREATE SERVER parameters

The following fault tolerance parameters can be used for data import and export. If network connectivity is poor, you can adjust these parameters to ensure successful import and export.

CREATE FOREIGN TABLE parameters

Export mode parameters for CREATE FOREIGN TABLE

• oss_flush_block_size: the buffer size for the data written to OSS at a time. Default value: 32 MB. Valid values: 1 MB to 128 MB.
• oss_file_max_size: the maximum file size for the data written to OSS (subsequent data is written in another file when the maximum file size is exceeded). Default value: 1024 MB. Valid values: 8 MB to 4000 MB.
• num_parallel_worker: the number of parallel compression threads in which the OSS data is written. Valid values: 1 to 8. Default value: 3.

Auxiliary functions

FUNCTION oss_fdw_list_file (relname text, schema text DEFAULT 'public')

• Obtains the name and size of the OSS file that an external table matches.
• The unit of file size is Byte.

select * from oss_fdw_list_file('t_oss');
              name              |   size    
--------------------------------+-----------
 oss_test/test.gz.1  | 739698350
 oss_test/test.gz.2  | 739413041
 oss_test/test.gz.3  | 739562048
(3 rows)

Auxiliary features

oss_fdw.rds_read_one_file: In read mode, it is used to specify a file to match the external table. If the file is specified, the external table only matches this file during data import.

Example: set oss_fdw.rds_read_one_file = 'oss_test/example16.csv.1';

set oss_fdw.rds_read_one_file = 'oss_test/test.gz.2';
select * from oss_fdw_list_file('t_oss');
              name              |   size    
--------------------------------+-----------
  oss_test/test.gz.2  | 739413041
(1 rows)

oss_fdw notes

• oss_fdw is an external table plug-in developed based on the PostgreSQL FOREIGN TABLE framework.
• The data import performance is related to the CPU IO MEM MET of PPAS instances and OSS.
• To guarantee that data is correctly imported, you need to make sure that the Region of the DTS server is the same as that of the OSS server. For more information, see OSS endpoint information.
• If the SQL of the external table is read, ERROR: oss endpoint userendpoint not in aliyun white list, we recommend that you use the endpoint shared by Alibaba Cloud in each zone. For more information, see Regions and endpoints. If the problem persists, submit a ticket.

Error handling

When an import or export error occurs, the log displays the following error information:

• code: the HTTP status code of the request that has failed.

• error_code: the error code returned by OSS.

• error_msg: the error message returned by OSS.

• req_id: the UUID that identifies the request. If you cannot solve the problem, you can seek help from OSS development engineers by providing the req_id.

For more information about error types, see the following topics. Timeout errors can be handled by using oss_ext parameters.

• Object Storage Service

• PostgreSQL CREATE FOREIGN TABLE manual

• OSS error handling

• OSS error response

ID and key encryption

If id and key parameters for CREATE SERVER are not encrypted, executing the select * from pg_foreign_server statement will display the information in plaintext. Your ID and key will be exposed. The symmetric encryption can be performed on the id and key to hide the id and key. (different instances use different keys to maximize the protection of user information.) however, you cannot use methods like GP to add a data type.

The encrypted information is as follows:

postgres=# select * from pg_foreign_server ;
  srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                                              srvoptions
-----------+----------+--------+---------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 ossserver |       10 |  16390 |         |            |        | {host=oss-cn-hangzhou-zmf.aliyuncs.com,id=MD5xxxxxxxx,key=MD5xxxxxxxx,bucket=067862}

The encrypted information is preceded by MD5 (total length: len,len%8==3). Therefore, encryption is not performed again when the exported data is imported. But you cannot create the key and id preceded by MD5.