Hologres:Export data to OSS

This section describes the syntax and parameters of the COPY TO statement.

• Syntax

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

  option in the preceding 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. The parentheses () before and after the query statement must be retained.
  PROGRAM	A command that you want to run. The data that you want to export 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 the 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 that you want to generate. 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 is used 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 that you want to generate 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. If you do not specify this parameter, the file is generated based on the encoding method that is used by the current client.

The following examples show how to execute the hg_dump_to_oss and COPY TO statements in Hologres.

-- Dump data from a Hologres internal table to a specific 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 specific 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 specific 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);

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 reported because the query statement is invalid. Check and correct the query statement.

• DETAIL: child process exited with exit code 255

  This error message is reported because the network type that is specified for the OSS bucket is invalid. If you access the OSS bucket over the Alibaba Cloud public cloud, use the classic network endpoint of the OSS bucket.

• DETAIL: command not found

  This error message is reported because the PROGRAM parameter in the DUMP TO OSS statement 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 reported because the value of the 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 reported because the value of the 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 reported because the value of the Endpoint parameter is invalid. Use the 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 reported because the value of the BucketName parameter is invalid. Use the valid bucket name.

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

  This error message is reported 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 reported 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 endpoints of OSS buckets, see Regions and endpoints.