Export data to OSS - Hologres - Alibaba Cloud Documentation Center

Alibaba Cloud Object Storage Service (OSS) is a secure, cost-effective, and reliable cloud storage service. This topic describes how to use the copy to command and the hg_dump_to_oss command to export query results to a specified OSS location.

Limits

Only Superusers of the current Hologres instance or users with the pg_execute_server_program permission can use hg_dump_to_oss to export Hologres data to OSS. A Superuser can grant the pg_execute_server_program permission to other users by running the following commands.

-- If the DB uses the simple permission model, run the following statement
CALL spm_grant('pg_execute_server_program','Alibaba Cloud account ID/email/RAM user');

-- If the DB uses the standard PostgreSQL authorization model, run the following statement
GRANT pg_execute_server_program TO Alibaba Cloud account ID/email/RAM user;

The data volume for a single import to OSS cannot exceed 5 GB.

Command overview

COPY command: The COPY command moves data between PostgreSQL tables and the standard file system. Hologres supports the COPY command. The COPY TO statement copies the results of a SELECT query to a file or another output medium. The COPY FROM statement copies data from a file to a table.
hg_dump_to_oss command: Dumps the results of a query in Hologres to a specified OSS location.

COPY command

The following sections describe the syntax and parameters of the COPY TO command:

Syntax

COPY ( query ) TO { PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

The option can be one of the following:

FORMAT [format_name]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER
QUOTE 'quote_character'
ESCAPE 'escape_character'
ENCODING 'encoding_name'

Parameters

Parameter	Description
query	The query statement to be executed. The parentheses around the query statement are required.
PROGRAM	A command to execute. The output is written to the standard input of the command.
STDOUT	Specifies that the output is sent to the client application.
FORMAT	Selects the data format. The format can be text, csv, or binary. The default is text.
DELIMITER	Specifies the character that separates columns within each row of the file. This must be a single one-byte character. The following list describes the default characters for different data formats: For the text format, the default is a tab character (\t). For the csv format, the default is a comma (,). This option is not supported for the binary format.
NULL	Specifies the string that represents a null value. The following list describes the default strings for different data formats: For the text format, the default is \N. For the csv format, the default is an unquoted empty string. This option is not supported for the binary format.
HEADER	Specifies that the file has a header row with column names. Only CSV files support this option.
QUOTE	Specify the character that must appear before a data character matching the QUOTE value. This must be a single single-byte character. The default is the same as the QUOTE value. Only the CSV format supports this option.
ENCODING	Specifies that the file is encoded in encoding_name. If this option is omitted, the current client encoding is used.

hg_dump_to_oss command

The following sections describe the syntax and parameters of the hg_dump_to_oss command. In Hologres, the hg_dump_to_oss command is used with the COPY TO command.

Syntax

COPY (query) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <accessid> --AccessKeySecret <accesskey> --Endpoint <ossendpoint> --BucketName <bucketname> --DirName <dirname> --FileName <filename> --BatchSize <xxx> ' (DELIMITER ',', HEADER true, FORMAT CSV);

Note

Do not add characters such as / or \ before <dirname>.

Parameters

Parameter	Description	Example
query	The query statement to be executed.	`select * from dual;`
AccessKeyId	The AccessKey ID of your account. You can go to the AccessKey Management page to obtain an AccessKey ID.	None
AccessKeySecret	The AccessKey secret of your account.	None
Endpoint	The endpoint of the OSS classic network. You can click the name of the target bucket on the Bucket List page to view the bucket details.	oss-cn-beijing-internal.aliyuncs.com
BucketName	The name of the OSS bucket.	dummy_bucket
DirName	The folder in OSS where the output results are stored.	testdemo/
FileName	(Optional) The name of the file in OSS.	file_name
BatchSize	The number of rows for each `hg_dump_to_oss` execution. The default is 1000.	5000
DELIMITER	The separator between columns in the result. The default is a tab character (Tab-separated Values, TSV).	','

Usage examples

The following examples show how to use the hg_dump_to_oss command with the COPY TO command in Hologres.

-- Dump data from a Hologres internal table to a specified OSS location
COPY (SELECT * FROM holo_test LIMIT 2) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-hangzhou-internal.aliyuncs.com --BucketName hologres-demo --DirName holotest/ --FileName file_name --BatchSize 3000' DELIMITER ',';

-- Dump data from a Hologres foreign table to a specified OSS location
COPY (SELECT * FROM foreign_holo_test LIMIT 20) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-hangzhou-internal.aliyuncs.com --BucketName hologres-demo --DirName holotest/ --FileName file_name --BatchSize 3000' (DELIMITER ',', HEADER true);

-- Perform a cross-region dump to a specified OSS location
COPY (SELECT * FROM holo_test_1 LIMIT 20) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-beijing-internal.aliyuncs.com --BucketName hologres-demo --DirName holotest/ --FileName file_name --BatchSize 3000' (DELIMITER ',', HEADER true, FORMAT CSV);

Note

Hologres supports cross-region data export to a specified OSS location. For example, you can export data queried from an instance in the China (Hangzhou) region to OSS in the China (Beijing) region.

FAQ

The following list describes common errors and their solutions:

ERROR: syntax error at or near ")"LINE 1: COPY (select 1,2,3 from ) TO PROGRAM 'hg_dump_to_oss2 --Acce...
The input query is invalid. Check the query statement.
DETAIL: child process exited with exit code 255
The selected OSS network type is incorrect. If you are using the public cloud, select classic network.
DETAIL: command not found
You need to set the program parameter for DUMP TO OSS to hg_dump_to_oss. Otherwise, this error occurs.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 101
The input AccessKeyId is invalid. Use the AccessKey ID of your account.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 102
The input AccessKeySecret is invalid. Use the AccessKey secret of your account.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 103
The input Endpoint is invalid. Confirm the endpoint for the corresponding OSS classic network.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 104
The input BucketName is invalid. Confirm the bucket name.
ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 105
A required parameter is missing. Check the parameter descriptions and make sure that all required parameters are configured.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 255
This error usually occurs because the Hologres server cannot connect to the specified OSS network. You can change the OSS domain name. For example, select classic network for the OSS network type. For more information about OSS domain names, see Regions and endpoints.