Use OSS foreign tables to export data - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL allows you to export data to Object Storage Service (OSS) by using OSS foreign tables.

Supported object formats

You can use OSS foreign tables to export data only to the following data objects:

Uncompressed CSV and TEXT objects.
GZIP-compressed CSV and TEXT objects.
ORC binary objects.
For information about data type mappings between ORC and AnalyticDB for PostgreSQL, see Data type mappings between ORC and AnalyticDB for PostgreSQL.

Create an OSS server and a user mapping to the OSS server

Before you use OSS foreign tables, you must create an OSS server and a user mapping to the OSS server.

For more information about how to create an OSS server, see the "Create an OSS server" section of the Use OSS foreign tables for data lake analysis topic.
For more information about how to create a user mapping to the OSS server, see the " Create a user mapping to the OSS server" section of the Use OSS foreign tables for data lake analysis topic.

Create an OSS foreign table

You can create an OSS foreign table to export data to OSS.

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


Parameter	Data type	Required	Description
table_name	String	Yes	The name of the OSS foreign table.
column_name	String	Yes	The name of the column.
data_type	String	Yes	The data type of the column.

The following table describes the parameters involved in OPTIONS.


Parameter	Data type	Required	Description
prefix	STRING	Yes, one of the two parameters	The prefix of the object path. Regular expressions are not supported. If you set the prefix parameter to osstest/adbpg/ossfdw, data is exported to the osstest/adbpg/ directory and the object name starts with ossfdw.
dir	STRING	Yes, one of the two parameters	The OSS directory that stores data objects. The directory must end with a forward slash `(/)`. Example: `test/test/`.
bucket	STRING	No	The name of the bucket that stores data objects. Note If the bucket parameter is specified when you create an OSS server, you can leave this parameter empty. If the bucket parameter is specified for both the OSS server and the OSS foreign table, the bucket parameter specified for the OSS foreign table takes effect.
format	STRING	Yes	The object format. Valid values: csv text orc
filetype	STRING	No	The object type. Default value: plain. Valid values: plain: The system only reads the raw binary data. gzip: The system reads the raw binary data and decompresses the package by using GZIP. Note This parameter takes effect only for CSV and TEXT objects.
fragment_size	NUMERIC	No	The maximum size of data that can be exported to an OSS object. Default value: 256. Unit: MB. If the size of data to be exported to OSS exceeds the value of this parameter, additional data is written to another object. Note A row of data is not exported across objects. The size of an object that stores exported data may be slightly greater than the value of this parameter.

The following table describes the parameters involved in OPTIONS for CSV and TEXT objects.


Parameter	Data type	Required	Description
gzip_level	NUMERIC	No	The GZIP compression level for CSV or TEXT objects. Valid values: 1 to 9. Default value: 1. Note The gzip_level parameter takes effect only when the filetype parameter is set to gzip. A higher compression level results in a slightly smaller object size but a significantly longer time to complete the export. Test results show that the object sizes between higher compression levels present a less difference. However, it takes significantly longer to export data. In general scenarios, we recommend that you use the default value.
force_quote_all	BOOLEAN	No	Specifies whether to forcefully enclose all fields of the exported CSV data in quotation marks (""). Valid values: true: forcefully encloses all fields in quotation marks (""). false: does not forcefully enclose all fields in quotation marks (""). Note This parameter takes effect only for CSV objects.

The following table describes the parameters involved in OPTIONS for ORC objects.


Parameter	Data type	Required	Description
orc_stripe_size	NUMERIC	No	The size of each stripe in the exported ORC objects. Unit: MB. Default value: 64.

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

Naming conventions for exported objects

When data is exported, multiple compute nodes write data to the same directory in parallel. The objects exported by using OSS foreign tables are named in the following format:

{tablename | prefix } _{timestamp}_{random_key}_{seg}{segment_id}_{fileno}.{ext}[.gz]

The following table describes the parameters.


Parameter	Description
`{tablename \| prefix }`	The prefix of the object name. The presentation of the prefix varies based on whether the prefix or dir parameter is used when you create an OSS foreign table. If the `prefix` parameter is used, the object name is prefixed by the specified parameter value. If the dir parameter is used, the object name is prefixed by the name of the OSS foreign table.
`{timestamp}`	The timestamp when data is exported. Format: `YYYYMMDDHH24MISS`.
`{random_key}`	The value of a random key.
`{seg}{segment_id}`	The combination of seg and the number of the compute node. For example, seg1 indicates that the object is exported from compute node 1.
`{fileno}`	The number of the object segment. This parameter starts from 0.
`{ext}`	The format of the exported object. Valid values: `.csv` `.txt` `.orc`
`[.gz]`	Indicates that the exported object is compressed by using GZIP.

Examples:

Export data to a GZIP-compressed CSV object and use the dir parameter to specify the object directory.

CREATE FOREIGN TABLE fdw_t_out_1(a int)
SERVER oss_serv
OPTIONS (format 'csv', filetype 'gzip', dir 'test/');

The object is named in the following format:

fdw_t_out_1_20200805110207_1718599661_seg-1_0.csv.gz

Export data to an ORC object and use the prefix parameter to specify the object path prefix.

CREATE FOREIGN TABLE fdw_t_out_2(a int)
SERVER oss_serv
OPTIONS (format 'orc', prefix 'test/my_orc_test');