Querying Files in Amazon S3

Since MotherDuck is hosted in the cloud, one of the benefits of MotherDuck is better and faster interoperability with Amazon S3. MotherDuck's "hybrid mode" automatically routes queries that query Amazon S3 to MotherDuck's execution runtime in the cloud rather than executing these queries locally.

note

MotherDuck supports several cloud storage providers, including Azure, Google Cloud and Cloudflare R2.

MotherDuck supports the DuckDB dialect to query data stored in Amazon S3. Such queries are automatically routed to MotherDuck's cloud execution engines for faster and more efficient execution.

Here are some examples of querying data in Amazon S3:

SELECT * FROM read_parquet('s3://<bucket>/<file>');
SELECT * FROM read_parquet(['s3://<bucket>/<file>', ... ,'s3://<bucket>/<file>']);
SELECT * FROM read_parquet('s3://<bucket>/*');
SELECT * FROM 's3://<bucket>/<directory>/*';
SELECT * FROM iceberg_scan('s3://<bucket>/<directory>', ALLOW_MOVED_PATHS=true);
SELECT * FROM delta_scan('s3://<bucket>/<directory>');

See Apache Iceberg for more information on reading Iceberg data.

See Delta Lake for more information on reading Delta Lake data.

Accessing private files in Amazon S3

Protected Amazon S3 files require an AWS access key and secret. You can configure MotherDuck using CREATE SECRET

SSL Certificate Verification and S3 Bucket Names

Because of SSL certificate verification requirements, S3 bucket names that contain dots (.) cannot be accessed using virtual-hosted style URLs. This is due to AWS's SSL wildcard certificate (*.s3.amazonaws.com) which only validates single-level subdomains. When a bucket name contains dots, it creates multi-level subdomains that don't match the wildcard pattern, causing SSL verification to fail.

If your bucket name contains dots, you have two options:

1. Rename your bucket to remove dots (e.g., use dashes instead)
2. Use path-style URLs by adding the URL_STYLE 'path' option to your secret:

CREATE OR REPLACE SECRET my_secret (
    TYPE s3,
    URL_STYLE 'path',
    SCOPE 's3://my.bucket.with.dots'
);

For more information, see Amazon S3 Virtual Hosting documentation.