S3EXPORT

Exports data to an Amazon S3 bucket from the Vertica cluster.

If the S3 bucket name contains a period (.) in its path, set prepend_hash to true, or specify a file extension in the export syntax.

Syntax

S3EXPORT( source‑data USING PARAMETERS parameter=setting[,…])

Arguments

source‑data Specifies the source of the export operation.

Parameter Settings

* from_charset and to_charset values are system-dependent. See your operating system documentation for more details.
Parameter name Set to…
url

String value ≤ 236 characters that specifies the URL of the S3 bucket and object base name, one of the following:

  • HTTPS URL
  • S3 URL

URLs can contain only ASCII characters, 0x01 to 0x7F.
chunksize

Specifies in bytes the size of the buffer that stores each chunk of exported data, between 5 MB and 5 GB.

You might need to increase this value from the default if you export data from a very wide table, or a table with LONG VARBINARY or LONG VARCHAR columns. If the width of a single column’s data is exceeds the buffer width, Vertica returns an error like this:

The specified buffer of 10485760 bytesRead is too small.

See Adjusting the Export Chunk Size for Wide Tables for more information.

The maximum number of chunks allowed in an export is 10000.

Default:10485760

compression

Uses the specified filter to compress exported data. Valid settings are one of the following:

  • bzip
  • none

Default:none

delimiter

Specifies the column delimiter character.

Default:| (vertical bar)
enclosed_by

The character used to enclose string and date/time data. If you omit this parameter, no character encloses these data types.

Default:  '' (empty string)
escape_as

The character used to escape values in exported data that must be escaped, including the enclosed_by value.

Default: \ (backslash)
from_charset*

The character set in which data is currently encoded.

to_charset* The character set in which to encode the export.
null_as 'null‑string'

Specifies a string to represent null values in the source data. If this parameter is included, S3EXPORT exports all null values as null‑string. Otherwise, S3EXPORT exports null values as zero-length strings.
prepend_hash

Boolean, specifies whether to prepend unique hash values assigned to exported objects instead of the standard values.

If the S3 bucket name contains a period (.) in its path, set prepend_hash to true, or specify a file extension in the export syntax.

Default: false
record_terminator

Specifies what character marks the end of a record.

Default:\n

Examples

Export column1 data from exampleTable:

=> SELECT s3export(column1 USING PARAMETERS 
    url='s3://exampleBucket/object', 
    delimiter=',',
    chunksize='10485760',
    record_terminator='\n', 
    from_charset='ASCII',
    to_charset='UTF-8', 
    prepend_hash='true') 
OVER () FROM exampleTable;

See Also