---
sidebar_position: 5
title: Querying Files in Amazon S3
description: Query Parquet, CSV, and JSON files in S3 with automatic cloud execution routing.
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

Since MotherDuck is hosted in the cloud, one of the benefits of MotherDuck is better and faster interoperability with Amazon S3. MotherDuck's [Dual Execution](/concepts/architecture-and-capabilities#dual-execution) automatically routes queries against cloud storage to MotherDuck's execution runtime in the cloud rather than executing them locally.

:::note
MotherDuck supports several cloud storage providers, including [Azure](/integrations/cloud-storage/azure-blob-storage.mdx), [Google Cloud](/integrations/cloud-storage/google-cloud-storage.mdx) and [Cloudflare R2](/integrations/cloud-storage/cloudflare-r2).
:::

:::info How MotherDuck accesses cloud storage
When you query cloud storage while connected to MotherDuck (for example, `read_parquet('s3://...')`), the query runs on MotherDuck's cloud execution engine, not on your local machine. MotherDuck connects to your storage provider directly from the cloud.

To authenticate, MotherDuck can use **any** of your secrets, including temporary, in-memory secrets created in your local DuckDB session. This means even if you create a secret locally without `IN MOTHERDUCK` or `PERSISTENT`, MotherDuck's cloud service can still use it to read your data. Your local DuckDB client does not connect to cloud storage directly.

For details on secret storage options and how secrets are resolved, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/).
:::

MotherDuck supports the [DuckDB dialect](https://duckdb.org/docs/guides/import/s3_import) 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:

```sql
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](/integrations/file-formats/apache-iceberg.mdx) for more information on reading Iceberg data.

See [Delta Lake](/integrations/file-formats/delta-lake.mdx) for more information on reading Delta Lake data.

## Accessing private files in S3

Protected Amazon S3 files require an AWS access key and secret. You can configure MotherDuck using [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md)

### 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:

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

For more information, see [Amazon S3 Virtual Hosting documentation](https://docs.aws.amazon.com/AmazonS3/latest/userguide/VirtualHosting.html).