Load data from S3 storage

Learn how to import data from S3 storage using the IMPORT command.

Access to public objects without an access key and to private objects using presigned URLs was introduced in version 7.1.16. In versions prior to 7.1.16, leaving the USER and IDENTIFIED BY fields blank triggered the database to connect with temporary IAM credentials.

For more information, see changelog entries 15504 and 15155.

Create a connection

Amazon S3 URL format support

The following Amazon S3 URL formats are supported:

URL format Example
https://<bucketname>.s3.amazonaws.com https://my_bucket.s3.amazonaws.com
https://<bucketname>.s3.<aws_region>.amazonaws.com https://my_bucket.s3.eu-west-1.amazonaws.com
s3://<bucketname> s3://my_bucket

The AWS region is automatically detected if omitted in the URL.

AWS authentication options

Authentication using access key ID and secret access key is supported for seamless access to AWS resources. Additional support for session tokens allows secure short-lived credential-based authentication for temporary access to AWS.

For more information about AWS authentication methods, see AWS Identity and Access Management in the AWS documentation.

We recommend that you always create a connection object instead of providing the authentication details openly in the IMPORT command.

Create a connection object

To prevent connection details from being exposed in logs and audit tables, we recommend that you always create a connection object using CREATE CONNECTION and authenticate using the saved connection instead of providing the details openly in the IMPORT/EXPORTstatements.

Copy 
-- Connection to Amazon S3 bucket on AWS
CREATE OR REPLACE CONNECTION <connection_name>
    TO 'https://<bucketname>.s3[.<aws_region>].amazonaws.com'
    USER '<access_key>' IDENTIFIED BY '<secret_key>'
    [SESSION TOKEN '<session_token>']
;

Import data

Exasol supports loading the following S3 object types:

  • Public objects

  • Private objects using AWS access key

  • Private objects using presigned URLs

Public objects

To load public objects, leave the USER and IDENTIFIED BY fields in the connection object empty.

Example:
Copy 
CREATE CONNECTION my_conn
TO 'https://public-testbucket.s3.eu-west-1.amazonaws.com'; 

IMPORT INTO testtable FROM CSV AT my_conn
FILE 'testpath/test.csv';

Private objects using access key

Example:
Copy 
CREATE OR REPLACE CONNECTION my_conn
    TO 'https://my_bucket.s3.eu-west-1.amazonaws.com'
    USER 'my_access_key' IDENTIFIED BY 'my_secret_key'
;

IMPORT INTO testtable FROM CSV AT my_conn
FILE 'testpath/test.csv';

Private objects using presigned URLs

To load private files using a presigned URL, leave the USER and IDENTIFIED BY fields empty in the connection object.

Add the query string (everything after the file name and question mark in the presigned URL) to the file name specified in the FILE field in the IMPORT statement.

Example:
Copy 
CREATE CONNECTION my_conn
TO 'https://private-testbucket.s3.eu-west-1.amazonaws.com'; 

IMPORT INTO testtable FROM CSV AT my_conn
FILE 'testpath/test.csv?<query_string>';