AWS library functions

• 1: AWS_GET_CONFIG
• 2: AWS_SET_CONFIG
• 3: S3EXPORT
• 4: S3EXPORT_PARTITION

1 - AWS_GET_CONFIG

Returns the current Amazon Web Services (AWS) credentials set by AWS_SET_CONFIG or ALTER SESSION.

Syntax

AWS_GET_CONFIG( 'parameter' )

Parameters

aws_id

Retrieves the value for the 20-character AWS access key used to authenticate your account

aws_secret

Retrieves the value for the 40-character AWS secret access key used to authenticate your account

aws_session_token

The AWS temporary security token generated by running the AWS STS command get-session-token. This AWS STS command generates temporary credentials you can use to implement multi-factor authentication for security purposes. For more information on get-session-token, see the AWS documentation.

aws_region

Retrieves the region where your AWS bucket is located. See the AWS Documentation for the full list of values.

Default: us-east-1

aws_ca_path

Retrieves the path Vertica uses to look up SSL server certificates..

aws_ca_bundle

Retrieves the path Vertica uses to look up an SSL server certificate bundle.

aws_proxy

A string value that lets you set an HTTP/HTTPS proxy for the AWS library.

aws_verbose

When enabled, logs libcurl debug messages to dbLog.

aws_max_send_speed

Retrieves the value for the maximum transfer speed when sending data to AWS S3, in bytes per second.

aws_max_recv_speed

Retrieves the value for the maximum transfer speed for receiving data to AWS S3, in bytes per second.

Examples

This example retrieves a stored AWS access key in a session.

=> SELECT AWS_GET_CONFIG('aws_id');
   aws_get_config
----------------------
 AKABCOEXAMPLEPKPXYZQ
(1 row)

See also

• AWS Library Parameters

• Vertica AWS Library

2 - AWS_SET_CONFIG

Sets the values of AWS library S3-Compatible user-defined session parameters for the current session. This function is designed to be used with a table that contains those values, rather than by setting values explicitly in the function call.

Syntax

AWS_SET_CONFIG( 'parameter' , 'value')

Parameters

aws_id

The 20-character AWS access key used to authenticate your account.

aws_secret

The 40-character AWS secret access key used to authenticate your account.

aws_session_token

The AWS temporary security token generated by running the AWS STS command get-session-token. This AWS STS command generates temporary credentials you can use to implement multi-factor authentication for security purposes. For more information on get-session-token see the AWS documentation.

aws_region

Specifies the region where your AWS bucket is located. See the AWS Documentation for the full list of values.

You can configure aws_region with only one region. To access buckets in multiple regions, reset the parameter each time you change regions.

Default: us-east-1

aws_ca_path

The path Vertica uses to look up SSL server certificates.

Default: System-dependent

aws_ca_bundle

The path Vertica uses to look up an SSL server certificate bundle.

Default: System-dependent

aws_proxy

A string value that lets you set an HTTP/HTTPS proxy for the AWS library.

aws_verbose

When enabled, logs libcurl debug messages to dbLog.

Default: false

aws_max_send_speed

The maximum transfer speed for sending data to AWS S3, in bytes per second.

Default: unlimited

aws_max_recv_speed

The maximum transfer speed when receiving data to AWS S3, in bytes per second.

Default: unlimited

Examples

Configure session parameters for an AWS access key and secret access key with credentials in the keychain table:

=> SELECT AWS_SET_CONFIG('aws_id', accesskey),
          AWS_SET_CONFIG('aws_secret', secretaccesskey)
   FROM keychain;
 AWS_SET_CONFIG | AWS_SET_CONFIG
----------------+----------------
 aws_id         | aws_secret
(1 row)

See also

• AWS_GET_CONFIG

• AWS Library Parameters

• Vertica AWS Library

3 - S3EXPORT

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.

Note

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'

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.

Important

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

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

• AWS_SET_CONFIG
• AWS library S3-Compatible user-defined session parameters
• Export data to Amazon S3 using the AWS library

4 - 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} [,...] )

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

• AWS_SET_CONFIG

• AWS_GET_CONFIG

• AWS Library Parameters

• Vertica AWS Library