ApsaraDB RDS:Use the oss_fdw extension to read and write foreign data text files

Prerequisites

Examples

# Create an oss_fdw extension for your RDS instance.
create extension oss_fdw;  
-- Create an OSS server. 
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou.aliyuncs.com' , id 'xxx', key 'xxx',bucket 'mybucket');
-- Create a foreign OSS table named ossexample.
CREATE FOREIGN TABLE ossexample 
    (date text, time text, open float,
     high float, low float, volume int) 
     SERVER ossserver 
     OPTIONS ( dir 'osstest/', delimiter ',' ,
         format 'csv', encoding 'utf8', PARSE_ERRORS '100');
-- Create a table named example on your RDS instance. The table is used to store the data that is imported into your RDS instance. 
create table example
        (date text, time text, open float,
         high float, low float, volume int);
-- Import data from the ossexample table into the example table. 
insert into example select * from ossexample;

-- Use the oss_fdw extension to estimate the size of the ossexample table and formulate a query plan. 
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)
-- Export data from the example table to the ossexample table. 
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 more information about the parameters in the preceding examples, see the following sections.

Parameters supported by the oss_fdw extension

Similar to other foreign data wrappers (FDW), the oss_fdw extension encapsulates foreign data that is stored in OSS buckets. You can use the oss_fdw extension to read data from OSS buckets. This process is similar to the process of reading data tables. The oss_fdw extension provides some unique parameters that are used to access a specified OSS bucket and parse the OSS objects in the OSS bucket.

Parameters in the CREATE SERVER statement

The following table describes the fault tolerance parameters that are provided by OSS. If network connectivity is poor, you can adjust the values of these parameters to ensure successful import and export.

Parameters in the CREATE FOREIGN TABLE statement

Parameters in the CREATE FOREIGN TABLE statement

• oss_flush_block_size: the buffer size of the data that can be written to the OSS bucket at a time. Valid values: 1 to 128. Unit: MB. Default value: 32.

• oss_file_max_size: the maximum amount of data that can be written to an object in the OSS bucket. If the amount of data that needs to be written reaches the maximum value, the data that remains is written to a new object. Valid values: 8 to 4000. Unit: MB. Default value: 1024.

• num_parallel_worker: the maximum number of threads that can run in parallel to compress the data written to the OSS bucket. Valid values: 1 to 8. Default value: 3.

Auxiliary functions

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

• This function is used to obtain the name and size of the OSS object that the specified foreign table matches.

• The size is measured in bytes.

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 parameters

oss_fdw.rds_read_one_file: specifies the OSS object that a foreign table matches. This parameter is supported only when you import data from the OSS bucket into your RDS instance. If you specify this parameter, only the OSS object that the specified foreign table matches is imported.

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)

Usage notes

• The oss_fdw extension is developed based on the PostgreSQL FOREIGN TABLE framework to manage foreign tables.

• Data import performance varies based on the available PostgreSQL and OSS resources. The PostgreSQL resources are CPU, I/O, and memory.

• To import data at high performance, make sure that your RDS instance and the OSS bucket reside in the same region. For more information, see OSS domain names.

• If the error "ERROR: oss endpoint userendpoint not in aliyun white list" is reported when SQL statements are read from the foreign table, we recommend that you use the public OSS endpoint that is provided for the specified region. For more information, see Regions and endpoints.

Troubleshooting

If an import or export error occurs, the following error information is logged:

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

• error_code: the error code that is returned by OSS.

• error_msg: the error message that is returned by OSS.

• req_id: the UUID of the request that has failed. If you require assistance in solving a problem, you can submit a ticket that contains the req_id value of the failed request.

For more information about various errors, see the following documentation. You can handle time-out errors by reconfiguring the parameters related to the oss_ext extension.

• OSS documentation

• CREATE FOREIGN TABLE

• OSS error handling

• OSS error responses

Encryption of AccessKey ID and AccessKey secret

If you do not encrypt the values of the id and key parameters in the CREATE SERVER statement, other users can obtain your AccessKey pair in plaintext by executing the select * from pg_foreign_server statement. You can use symmetric encryption to encrypt the values of the id and key parameters. Use different keys for different RDS instances. This further protects your AccessKey pair. However, you cannot add data types as you can in Greenplum. This prevents incompatibility with earlier versions.

The following snippet provides the encrypted values of the id and key parameters:

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}

Each encrypted value starts with an MD5 string. The total length divided by 8 is 3. After these encrypted values are exported, they will not be encrypted again. Take note that you cannot create an AccessKey ID or AccessKey secret that starts with an MD5 string.