Load data from S3 using IMPORT

Learn how to import/export data in Amazon S3 on AWS using the IMPORT and EXPORT commands.

Loading data in CSV and FVB 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.

In Exasol 2025.1.9+, 2025.2.1+, and 2026.1.0+ you can import Parquet files from any S3-compatible storage, not only from AWS. In older Exasol versions, and for CSV and FBV files in all current versions, only Amazon S3 storage on AWS is supported.

To learn more, see Import Parquet files from S3 storage.

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 
CREATE OR REPLACE CONNECTION <my_connection>
    TO 'https://<bucketname>.s3[.<aws_region>].amazonaws.com'
    USER '<access_key>' IDENTIFIED BY '<secret_key>'
    [SESSION TOKEN '<session_token>']
;

Import and export 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_s3_bucket
TO 'https://public-testbucket.s3.eu-west-1.amazonaws.com'; 

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

EXPORT testtable INTO CSV AT my_s3_bucket
FILE 'testpath/test.csv';

Private objects using access key

Example:
Copy 
CREATE OR REPLACE CONNECTION my_s3_bucket
    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_s3_bucket
FILE 'testpath/test.csv';

EXPORT testtable INTO CSV AT my_s3_bucket
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_s3_bucket
TO 'https://private-testbucket.s3.eu-west-1.amazonaws.com'; 

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

Multipart upload

A file upload to an S3 bucket on AWS is done in parts. AWS allows a maximum of 10,000 parts for each upload. This means that if you want to upload a large file, you may need to configure a larger part size to keep the total number of parts below 10,000. The part size is configurable with the parameter -awsS3PartSize. The default size is 10 MiB represented in bytes (-awsS3PartSize=1048576).

The value of -awsS3PartSize will be applied to all queries. You cannot configure it separately for different queries.

To learn how to edit database parameters in an Exasol as-application deployment, see Database management.

If you need to edit these parameters in Exasol SaaS, click on Help > Support in the web console to send a support request.

Invalid parameters will prevent the database from starting. To avoid unnecessary downtime, contact Support for guidance before you add or change database parameters.

Examples:

If the part size is 10 MiB, the maximum size of the file you can upload is 97.7 GiB:

(10*1024*1024 bytes/part) * (10000 parts) / (1024*1024*1024 bytes/GiB) = 97.7 GiB

If the part size is 5 MiB, the maximum size of the file you can upload is 48.8 GiB: 

(5*1024*1024 bytes/part) * (10000 parts) / (1024*1024*1024 bytes/GiB) = 48.8 GiB

For more information about part size and limitations, see Amazon S3 Multipart Upload Limits.

To learn how to load Amazon S3 files in parallel, see Load data from S3 in parallel using UDFs.