Alibaba Cloud Object Storage Service (OSS) is a storage service that enables you to store, back up, and archive any amount of data in the cloud. OSS is a cost-effective, highly secure, and highly reliable cloud storage solution. This topic describes how to use an SQL statement to dump data from Hologres to a specified bucket in OSS.

Introduction

dump_to_oss: dumps data queried in Hologres to a specified bucket in OSS.

Synopsis

Hologres supports running the dump_to_oss statement in the following format:

COPY (query) TO PROGRAM 'hg_dump_to_oss --AccessKeyId dummy_id --AccessKeySecret dummy_key --Endpoint dummy_host --BucketName dummy_bucket --DirName dummy_dir --BatchSize 5000 ' DELIMITER ',';

Parameters

Parameters

Parameter Description Remarks
query The query statement. Example: select * from dual;
AccessKeyId The AccessKey ID of the account used to connect to the Hologres instance. You can view the AccessKey ID on the Security Management page.
AccessKeySecret The AccessKey secret of the account used to connect to the Hologres instance. You can view the AccessKey secret on the Security Management page.
Endpoint The classic network endpoint of the OSS bucket. Example: oss-cn-beijing-internal.aliyuncs.com. You can view the endpoint on the overview page of the OSS bucket.
BucketName The name of the OSS bucket. Example: dummy_bucket
DirName The directory to which data is dumped in the OSS bucket. Example: testdemo/
BatchSize Optional. The number of rows to be dumped to the OSS bucket at a time. Default value: 1000. Example: 5000
DELIMITER Optional. The delimiter used to separate columns in the query result. The default delimiter is a tab character. Example: ','

Instructions

Only a superuser of the Hologres instance or a user with the pg_execute_server_program permission is authorized to use the dump_to_oss statement to dump data from Hologres to an OSS bucket. A superuser can use the following statement to grant the pg_execute_server_program permission to other users:

grant pg_execute_server_program to "ID or name of an Alibaba Cloud account";

Examples

The following examples provide the SQL statements used to dump data from Hologres to OSS:

// Dump data from a Hologres internal table to a specified OSS bucket.
COPY (select * from test LIMIT 2) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-hangzhou-internal.aliyuncs.com --BucketName <holo-ingestion> --DirName <holotest>/ --BatchSize 3000' DELIMITER ',';

// Dump data from a Hologres foreign table to a specified OSS bucket.
COPY (select * from bank_data LIMIT 20) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-hangzhou-internal.aliyuncs.com --BucketName holo-ingestion --DirName holotest/ --BatchSize 3000' DELIMITER ',';

// Dump data from a Hologres table to a specified OSS bucket in another region.
COPY (select * from bank_data LIMIT 20) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-beijing-internal.aliyuncs.com --BucketName hologres-demo-oss --DirName demotest/ --BatchSize 3000' DELIMITER ',';
Note Currently, Hologres supports dumping data to an OSS bucket in another region. By using the SQL statement in the last example, you can dump data queried from a Hologres instance in the China (Hangzhou) region to an OSS bucket in the China (Beijing) region.

Common errors

  • Error message: ERROR: syntax error at or near ")"LINE 1: COPY (select 1,2,3 from ) TO PROGRAM 'hg_dump_to_oss2 --Acce...

    The error message is returned because an incorrect query statement was entered. Check and correct the query statement.

  • Error message: DETAIL: child process exited with exit code 255

    The error message is returned because an incorrect network type was specified for the OSS bucket. To access the OSS bucket on the public cloud, select a classic network.

  • Error message: DETAIL: command not found

    The error message is returned because the PROGRAM parameter was not set to hg_dump_to_oss.

  • Error message: ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 101

    The error message is returned because the entered AccessKey ID was invalid. Use the AccessKey ID of your Alibaba Cloud account.

  • Error message: ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 102

    The error message is returned because the entered AccessKey secret was invalid. Use the AccessKey secret of your Alibaba Cloud account.

  • Error message: ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 103

    The error message is returned because the entered endpoint was invalid. Enter a correct classic network endpoint of the OSS bucket.

  • Error message: ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 104

    The error message is returned because the entered bucket name was invalid. Enter a correct bucket name.

  • Error message: ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 105

    The error message is returned because a required parameter was missing. Check and set the parameters according to the parameter description.

  • The amount of data to be dumped to OSS at a time cannot exceed 5 GB.