Alibaba Cloud Object Storage Service (OSS) is a secure, cost-effective, and reliable cloud storage service. This topic describes how to run the copy to
and hg_dump_to_oss
commands to export query results from Hologres to a specified OSS bucket.
Limits
Hologres instances allow only users who are granted the
pg_execute_server_program
permission and superusers to run thehg_dump_to_oss
command to export data from Hologres to OSS. Superusers can execute one of the following statements to grant thepg_execute_server_program
permission to users:-- If the current database uses the simple permission model, execute the following statement: call spm_grant('pg_execute_server_program','Alibaba Cloud account ID, Alibaba Mail address, or RAM user'); -- If the current database uses the standard PostgreSQL authorization model, execute the following statement: grant pg_execute_server_program to Alibaba Cloud account ID, Alibaba Mail address, or RAM user;
The amount of data that you can export from Hologres to OSS at a time is up to 5 GB.
Command description
COPY commands:
COPY
commands are used to transmit data between PostgreSQL tables and standard file systems. Hologres supportsCOPY
commands. In Hologres, you can run theCOPY TO
command to copy the query results of a SELECT statement to an output medium, such as a file. You can run theCOPY FROM
command to copy data from a file to a table.hg_dump_to_oss command: You can run this command to dump Hologres query results to a specified OSS bucket.
COPY commands
This section describes the syntax and parameters of the COPY TO
command.
Syntax
COPY ( query ) TO { PROGRAM 'command' STDOUT } [ [ WITH ] ( option [, ...] ) ]
The option in the syntax can be one of the following parameters:
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. Do not delete the parentheses before and after the query statement.
PROGRAM
A command that you want to run. The data to be exported is written to the standard input of the command.
STDOUT
The output of the command. The output is synchronized to the application on the client.
FORMAT
The format of the data after export. Valid values: TEXT, CSV, and BINARY. Default value: TEXT.
DELIMITER
The delimiter that you want to use to separate fields in each row in the file to be generated. The specified delimiter must be a single-byte character. Specify this parameter based on data formats:
If the data format is TEXT, the default delimiter is a tab character (\t).
If the data format is CSV, the default delimiter is a comma (,).
If the data format is BINARY, do not specify this parameter.
NULL
The string that you want to use to represent a null value. Specify this parameter based on data formats:
If the data format is TEXT, the default string is \N.
If the data format is CSV, the default string is an unquoted empty string.
If the data format is BINARY, do not specify this parameter.
HEADER
Specifies whether the file to be generated contains a header row. Values in the row are fields in the Hologres query results that you want to export. Specify this parameter only when the data format is CSV.
QUOTE
The character that appears before a character that is the same as the value of the quote parameter. The specified character must be a single-byte character. The default character is the same as the value of the quote parameter. Specify this parameter only when the data format is CSV.
ENCODING
The encoding method that you want to use for the file to be generated. If you do not specify this parameter, the file is generated based on the encoding method that is used by the current client.
hg_dump_to_oss command
This section describes the syntax and parameters of the hg_dump_to_oss
command. In Hologres, the hg_dump_to_oss
command is used together 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);
NoteThe value of the DirName parameter cannot start with a character such as a forward slash (
/
) or a backslash (\
).Parameters
Parameter
Description
Example
query
The query statement.
select * from dual;
AccessKeyId
The AccessKey ID of your Alibaba Cloud account.
You can obtain the AccessKey ID from the Security Management page.
N/A
AccessKeySecret
The AccessKey secret of your Alibaba Cloud account.
You can obtain the AccessKey secret from the Security Management page.
N/A
Endpoint
The classic network endpoint of the OSS bucket.
You can view the classic network endpoint on the details page of the bucket. To go to the details page of the bucket, log on to the OSS console and click the bucket name on the Buckets page.
oss-cn-beijing-internal.aliyuncs.com
BucketName
The name of the OSS bucket.
dummy_bucket
DirName
The directory to which data is exported in the OSS bucket.
testdemo/
FileName
Optional. The name of the exported file in the OSS bucket.
file_name
BatchSize
The number of rows that are processed by the
hg_dump_to_oss
command at a time. Default value: 1000.5000
DELIMITER
The delimiter that you want to use to separate fields in the query results. The default delimiter is a tab character (\t).
','
Examples
The following examples show you how to run the hg_dump_to_oss
and COPY TO
commands in Hologres.
-- Dump data from a Hologres internal table to a specified OSS bucket.
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 bucket.
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);
-- Dump data from a Hologres table to a specified OSS bucket in another region.
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);
Hologres allows you to export data to OSS buckets across regions. For example, you can export data that is queried from a Hologres instance in the China (Hangzhou) region to an OSS bucket in the China (Beijing) region.
Common errors
The following items describe 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...
This error message is returned because the query statement that is specified in the query parameter is invalid. Check and correct the query statement.
DETAIL: child process exited with exit code 255
This error message is returned because the network type that is specified for the OSS bucket is invalid. To access the OSS bucket in the Alibaba Cloud public cloud, use the classic network endpoint of the OSS bucket.
DETAIL: command not found
This error message is returned because the PROGRAM parameter in the
DUMP TO OSS
command is not set to hg_dump_to_oss.ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 101
This error message is returned because the specified AccessKeyId parameter is invalid. Use the AccessKey ID of your Alibaba Cloud account.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 102
This error message is returned because the specified AccessKeySecret parameter is invalid. Use the AccessKey secret of your Alibaba Cloud account.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 103
This error message is returned because the specified Endpoint parameter is invalid. Use the correct classic network endpoint of the OSS bucket.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 104
This error message is returned because the specified BucketName parameter is invalid. Use the correct bucket name.
ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 105
This error message is returned because a required parameter is missing. Check whether all required parameters are correctly set.
ERROR: program "hg_dump_to_oss ...” failed DETAIL: child process exited with exit code 255
This error message is returned because the server on which your Hologres instance resides cannot connect to the network on which the specified OSS bucket resides. Change the specified endpoint of the OSS bucket. For example, you can use the classic network endpoint of the OSS bucket. For more information about the endpoints of OSS buckets, see Regions and endpoints.