S3EXPORT

The AWS library is deprecated.

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

Syntax

S3EXPORT( source-data USING PARAMETERS parameter=setting[,...])

Arguments

source-data
Specifies the source of the export operation.

Parameters

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
Size in bytes 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.

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'
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.

Default: false

record_terminator
Character that marks the end of a record.

Default: \n

header
Boolean, specifies whether to include the headers of the exported columns in the first row.

Default: false

from_charset and to_charset values are system-dependent. See your operating system documentation for more details.

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'
    header='true')            )
OVER () FROM exampleTable;

See also