Dump Query Results to OSS¶
Object Storage Service (OSS) is massive, safe, low-cost, and reliable cloud storage service. Hologres supports dumping query results to OSS via interactive commands.
Introduction¶
Dump to OSS - dump Hologres query result to specific OSS.
Synopsis¶
dump to OSS command follows the following format:
COPY (query) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <accessid> --AccessKeySecret <accesskey> --Endpoint <ossendpoint> --BucketName <bucketname> --<DirName> <dirname> --BatchSize <xxx> ' DELIMITER <'xxx'>;
Parameters¶
Parameters | Descriptions | Example |
---|---|---|
query | input query | select * from dual; |
AccessKeyId | account username | |
AccessKeySecret | account password | |
Endpoint | oss address | oss-cn-beijing-internal.aliyuncs.com |
BucketName | OSS bucket name | dummy_bucket |
DirName | output directory | testdemo/ |
BatchSize | optional. number of rows in a batch. Default is 1000 | 5000 |
DELIMITER | optional. Delimiter between columns. Default is TSV | ',' |
Limitation¶
Only Hologres superuser or users having pg_execute_server_program privilege can execute dump to OSS command. A superuser may grant pg_execute_server_program privilege to other users, as follows:
--Grant pg_execute_server_program
grant pg_execute_server_program to dummy_user;
Examples¶
The following are some examples of the usage of dump to OSS:
// Dump Hologres table content to specific OSS
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 to OSS across regions
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 ',';
Common errors and solutions¶
ERROR: syntax error at or near “)” LINE 1: COPY (select 1,2,3 from ) TO PROGRAM ‘hg_dump_to_oss2 –Acce…
Query syntax problem. Check input query.
DETAIL: child process exited with exit code 255
wrong OSS network type.
DETAIL: command not found
dump to OSS currently has to set program to be hg_dump_to_oss. Others, such an error occurs.
ERROR: program “hg_dump_to_oss …” failed,DETAIL: child process exited with exit code 101
Input AccessKeyId is invalid. Check OSS account.
ERROR: program “hg_dump_to_oss …” failed,DETAIL: child process exited with exit code 102
input AccessKeySecret is invalid. Check OSS account.
ERROR: program “hg_dump_to_oss …” failed,DETAIL: child process exited with exit code 103
Input Endpoint is invalid. Verify corresponding OSS host.
ERROR: program “hg_dump_to_oss …” failed,DETAIL: child process exited with exit code 104
Input BucketName is invalid. Verify bucket name.
ERROR: program “hg_dump_to_oss …” failed,DETAIL: child process exited with exit code 105
Missing required parameters. Refer to "Parameter Descriptions" section.
Data to dump cannot pass 5G in size
ERROR: program “hg_dump_to_oss …” failed,DETAIL: child process exited with exit code 255
Usually the connection between Holo Server and the specified OSS cannot be established. Try switch OSS domain name (as in classic network).