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

Prerequisites

Use the oss_fdw extension

1. Create the oss_fdw extension. For more information, see Parameters supported by the oss_fdw extension.

   CREATE EXTENSION oss_fdw; 

2. Create a server. For more information, see Parameters in the CREATE SERVER statement.

   CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
        (host 'oss-cn-hangzhou-internal.aliyuncs.com' , id 'access_id', key 'secrect_key',bucket 'mybucket');

3. Create an OSS foreign table named ossexample. For more information, see Parameters in the CREATE FOREIGN TABLE statement.

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

   Important

   The table schema in the table creation statement is used for reference only. Make sure that the schema of the foreign table is the same as the schema of the table that corresponds to the OSS object.

4. Read data from the OSS foreign table and write the data to your RDS instance.

   • Read data from the OSS foreign table.

     SELECT * FROM ossexample;

   • Write the data in the OSS bucket to your RDS instance.

     1. Create a table named example that has the same schema as the ossexample table.

        CREATE TABLE example
                (date text, time text, open float,
                 high float, low float, volume int);

     2. Write the data in the OSS bucket to the example table.

        INSERT INTO example SELECT * FROM ossexample;

        • You can execute the EXPLAIN statement to estimate the size of OSS objects and develop a query plan.

          EXPLAIN INSERT INTO example SELECT * FROM ossexample;
                                        QUERY PLAN
          ----------------------------------------------------------------------
           Insert on example  (cost=0.00..1.10 rows=0 width=0)
             ->  Foreign Scan on ossexample  (cost=0.00..1.10 rows=1 width=998)
                   Foreign OssDir: osstest/
                   Number Of Ossfile: 2

        • You can also write the data in the example table to an OSS bucket.

          INSERT INTO ossexample SELECT * FROM example;

For more information about oss_fdw parameters, see the following section.

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 supports parameters to connect to OSS buckets and analyze data in the OSS buckets.

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('ossexample');
              name              |   size    
--------------------------------+-----------
 osstest/test.gz.1  | 739698350
 osstest/test.gz.2  | 739413041
 osstest/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.

SET oss_fdw.rds_read_one_file = 'osstest/test.gz.2';
SELECT * FROM oss_fdw_list_file('ossexample');
              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=MD5****,key=MD5****,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.