Amazon S3

This article describes how to create a connection to Amazon S3 using a SQL command.

To read from and write to your Amazon S3 buckets in SQLake, you must first create a connection that provides the appropriate credentials to access your buckets.

Note that when you integrate Upsolver with your AWS account, there is an S3 connection created by default, but you may still want to create your own connection for specific access configurations.

See: Deploying Upsolver on AWS

Syntax

CREATE S3 CONNECTION 
    <connection_identifier> 
    [{ AWS_ROLE = '<role_arn>' 
       EXTERNAL_ID = '<external_id>'
     | AWS_ACCESS_KEY_ID = '<key_id>' 
       AWS_SECRET_ACCESS_KEY = '<key>' }]
    [ PATH_DISPLAY_FILTER[S] = { '<path>' | ('<path>' [, ...]) } ]
    [ READ_ONLY = { TRUE | FALSE } ]
    [{ ENCRYPTION_KMS_KEY = '<key>' ]
     | ENCRYPTION_CUSTOMER_MANAGED_KEY = '<key>' }]
    [ COMMENT = '<comment>' ];

Jump to

• AWS_ROLE

• EXTERNAL_ID

• AWS_ACCESS_KEY_ID

• AWS_SECRET_ACCESS_KEY

• PATH_DISPLAY_FILTER[S]

• READ_ONLY

• ENCRYPTION_KMS_KEY

• ENCRYPTION_CUSTOMER_MANAGED_KEY

• COMMENT

Connection options

AWS_ROLE — editable

Type: text

(Optional) The AWS IAM role ARN. Used in conjunction with EXTERNAL_ID.

If omitted, the role created when integrating Upsolver with the AWS account is used.

To learn how to provide a role with the proper credentials, see: Configure access to S3

EXTERNAL_ID — editable

Type: text

(Optional) The external ID of the role to assume. Used in conjunction with AWS_ROLE.

If omitted, the role created when integrating Upsolver with the AWS account is used.

AWS_ACCESS_KEY_ID — editable

Type: text

(Optional) The AWS access key ID. Used in conjunction with AWS_SECRET_ACCESS_KEY.

If omitted, the role created when integrating Upsolver with the AWS account is used.

AWS_SECRET_ACCESS_KEY — editable

Type: text

(Optional) The AWS secret key corresponding to the provided AWS_ACCESS_KEY_ID.

If omitted, the role created when integrating Upsolver with the AWS account is used.

PATH_DISPLAY_FILTER[S] — editable

Type: text | list

(Optional) A single path or the list of paths to show. If not provided, all buckets are shown.

Paths should be provided in the following format: s3://bucket/prefix. This shows anything beginning with the given prefix.

To filter by a specific folder, use the following format: s3://bucket/folder-path/

READ_ONLY — editable

Type: boolean

Default: false

(Optional) Whether or not the connection is read-only.

When true, Upsolver is not able to write data to or delete data from, the bucket.

ENCRYPTION_KMS_KEY — editable

Type: text

(Optional) The ARN of the KMS key to use.

If omitted, uses the default encryption defined on the bucket in AWS.

ENCRYPTION_CUSTOMER_MANAGED_KEY — editable

Type: text

(Optional) The Base64 text representation of the encryption key to use.

If omitted, uses the default encryption defined on the bucket in AWS.

COMMENT — editable

Type: text

(Optional) A description or comment regarding this connection.

Examples

Minimum example

CREATE S3 CONNECTION my_s3_connection;

This example uses the default credentials from Upsolver's integration with AWS.

See: Deploying Upsolver on AWS

Full example

CREATE S3 CONNECTION s3_example
    AWS_ROLE = 'arn:aws:iam::123456789012:role/upsolver-sqlake-role'
    PATH_DISPLAY_FILTERS = ('s3://bucket1/', 's3://bucket2/folder-path/')
    READ_ONLY = TRUE
    ENCRYPTION_KMS_KEY = 'arn:aws:kms:us-west-2:111122223333:key/1234abcd-12ab-34cd-56ef-1234567890ab'
    COMMENT = 'My new S3 connection';