Querying Data From S3 With 3 Lines In Your Terminal

Step 1: Install DuckDB

If you're on macOS, you can use Homebrew. This is our first line of code.

Copy code
brew install duckdb

For Windows, Linux, and other installation methods, check out the official DuckDB installation page.

Step 2: Launch DuckDB

Just type duckdb into your terminal to start the command-line interface (CLI).

Copy code
duckdb

You'll be greeted with the DuckDB prompt. That's line two.

Step 3: Run Your Query!

You can run a SELECT statement and point it directly to the S3 URL of your file. DuckDB handles the rest.

Copy code
-- This is our third and final line!
SELECT *
FROM 's3://duckdb-blobs/data/lineitem.parquet'
LIMIT 10;

That's it. DuckDB connects to S3, streams the data, and shows you the results right in your terminal. Notice what you didn't have to do:

• You didn't have to download the file.
• You didn't have to define a table schema.
• You didn't even have to tell DuckDB it was a Parquet file.

DuckDB automatically infers the file format and schema on the fly, making ad-hoc exploration fast and efficient.

The Modern Solution for Private Data: CREATE SECRET

The httpfs extension automatically discovers your AWS credentials from your environment, just like the AWS CLI or boto3 would. You can create a secret using the credential_chain provider, which checks for standard environment variables (AWS_ACCESS_KEY_ID, etc.) and the ~/.aws/credentials file. If you have the AWS CLI configured, it works out of the box.

Copy code
-- Create a secret that automatically finds AWS credentials
CREATE SECRET my_aws_secret (
    TYPE S3,
    PROVIDER credential_chain
);

-- Now, query your private data!
SELECT
    error_code,
    count(*) AS error_count
FROM 's3://my-private-bucket/logs/2023-10-26/*.csv'
GROUP BY error_code
ORDER BY error_count DESC;

If you need to specify credentials manually, you can use the CONFIG provider.

Copy code
-- Manually configure credentials if they are not in a standard location
CREATE SECRET my_manual_secret (
    TYPE S3,
    PROVIDER CONFIG,
    KEY_ID 'YOUR_AWS_ACCESS_KEY_ID',
    SECRET 'YOUR_AWS_SECRET_ACCESS_KEY',
    REGION 'us-east-1'
);

You're now running aggregates over your private log files directly from your terminal. No data pipelines, no heavyweight tools, just fast SQL.

Querying S3-Compatible Storage

DuckDB's S3 support isn't limited to AWS. You can also query files in S3-compatible object storage like Google Cloud Storage (GCS) and MinIO by configuring the endpoint and credentials accordingly.

What is DuckDB?

DuckDB is a fast, in-process analytical database, often described as "SQLite for analytics." It's lightweight, requires no server, and can query remote files directly.

Can DuckDB query files in a private S3 bucket?

Yes. By using the httpfs extension, DuckDB can automatically use your local AWS credentials (from environment variables or the ~/.aws/credentials file) to query files in private S3 buckets.

What file formats does DuckDB support for S3 queries?

DuckDB supports various file formats, including Parquet, CSV, and JSON. It can often infer the file format and schema automatically.

What is MotherDuck?

MotherDuck is a serverless, scalable, and collaborative analytics platform built on DuckDB. It allows you to transition from local queries to larger-scale analyses in the cloud, using the same DuckDB syntax.