S3EXPORT
The AWS library is deprecated. To export delimited data to S3 or any other destination, use EXPORT TO DELIMITED.
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. |
Parameters
url
|
String value ≤ 236 characters that specifies the URL of the S3 bucket and object base name, one of the following:
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. The maximum number of chunks allowed in an export is Default: |
compression
|
Uses the specified filter to compress exported data. Valid settings are one of the following:
Default: |
delimiter
|
Specifies the column delimiter character. Default: |
enclosed_by
|
The character used to enclose string and date/time data. If you omit this parameter, no character encloses these data types. Default: |
escape_as
|
The character used to escape values in exported data that must be escaped, including the Default: |
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. If the S3 bucket name contains a period ( Default: |
record_terminator
|
Character that marks the end of a record. Default: |
header
|
Boolean, specifies whether to include the headers of the exported columns in the first row. Default: |
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;