S3EXPORT_PARTITION

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} [,…] )
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 value:| (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 value: 10485760

record_terminator

Specifies what character marks the end of a record.

Default value: \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 value: 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 | s3://db001/st=MA/yr=2005/bystate.77fcab9836b93a04.dat 282633 | s3://db001/st=VA/yr=2007/bystate.77fcab9836b93a05.dat 282633 | s3://db001/st=VA/yr=2009/bystate.77fcab9836b93a05.dat (3 rows)

See Also