S3EXPORT_PARTITION

The AWS library is deprecated.

The S3EXPORT_PARTITION function allows Vertica output to be used by the Amazon Elastic MapReduce (EMR) feature. Since EMR stores and consumes data from S3 using the partition key included in the key of the S3 file, S3EXPORT_PARTITION exports data by adding the partition key in the url/filename.

Syntax

S3EXPORT_PARTITION ( expression USING PARAMETERS { parameter=setting} [,...] )

Parameters

expression
Specifies the source of the export operation.
url
The URL of the S3 bucket and object base name. Also include the partition key as part of the URL to export data so it is usable by EMR.

The URL can be either the HTTPS URL or the S3 URL. URL length is limited to a maximum of 236 characters.

URLs should contain only ASCII characters, 0x01 to 0x7F.

delimiter
Specifies the column delimiter character.

Default: | (vertical bar)

chunksize
Determines the buffer size used to send bytes to S3. Valid settings can range between 5 MB and 5 GB.

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

Default: 10485760

record_terminator
Specifies what character marks the end of a record.

Default: \n

from_charset
Specifies the character set in which your data is currently encoded.
to_charset
Specifies the character set in which you want to encode your export.
prepend_hash
Prepends the unique hash values assigned to exported objects instead of the standard appendation.

If your S3 bucket contains a period in its path, set the prepend_hash parameter to true.

Default: false

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

Examples

In the following example, st and yr are the partition keys:

=> SELECT s3EXPORT_PARTITION(* USING PARAMETERS url='s3://db001/bystate.date')
   OVER (PARTITION by st, yr) from T;
 rows   | url
--------+------------------------------------------------------------------------------
 184647 | https://db001/st=MA/yr=2005/bystate.77fcab9836b93a04.dat
 282633 | https://db001/st=VA/yr=2007/bystate.77fcab9836b93a05.dat
 282633 | https://db001/st=VA/yr=2009/bystate.77fcab9836b93a05.dat
(3 rows)

See also