Load data from S3 storage

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

Loading data in CSV/FBV files in Amazon S3 buckets on AWS is supported in all current Exasol versions. In Exasol 2025.1.0 and later you can also import Parquet files from S3 storage on AWS.

The latest version of Exasol supports importing Parquet files from any S3-compatible storage, not only from AWS. In older Exasol versions, and for CSV/FBV files in all current versions, only Amazon S3 storage on AWS is supported.

To learn more about importing Parquet files, including how to connect to other S3-compatible storage, see Importing Parquet files.

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.

Other S3-compatible storage services (Parquet only)

For information about URLs, ports, authentication methods, etc. for other S3-compatible storage services, refer to the documentation for the respective service.

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>']
;

For S3-compatible storage other than AWS (Parquet import only), you must specify the URL to the storage service in the connection string using the EndPoint parameter.

If a proxy is specified in the -etlProxy database parameter you can disable it by setting the connection string parameter UseProxy=0. If this parameter is not set, the proxy will be used (if specified).

Copy 
-- Connection to bucket on S3-compatible storage service
CREATE OR REPLACE CONNECTION <connection_name>
    TO 's3://<bucketname>;EndPoint=https://<hostname>:9000'
    USER '<user>' IDENTIFIED BY '<password>'
    [SESSION TOKEN '<session_token>']
;

-- Connection to bucket on local S3-compatible storage (no proxy)
CREATE OR REPLACE CONNECTION <connection_name>
    TO 's3://<bucketname>;EndPoint=localhost:9000;UseProxy=0'
    USER '<user>' IDENTIFIED BY '<password>'
    [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>';