DuckDB

Before you start

DuckDB 1.1 or later
Your access key ID, API key and bucket name from console.shelby.xyz

Export your credentials and bucket name from the console so the commands below can use them:

export SHELBY_ACCESS_KEY_ID="<your access key ID, SHLB…>"
export SHELBY_SECRET_ACCESS_KEY="<your API key>"
export SHELBY_BUCKET="<your bucket name>"

Create a secret for Shelby

Start duckdb in the shell where you exported the variables, then run:

INSTALL httpfs;
LOAD httpfs;

CREATE SECRET shelby (
    TYPE s3,
    KEY_ID getenv('SHELBY_ACCESS_KEY_ID'),
    SECRET getenv('SHELBY_SECRET_ACCESS_KEY'),
    REGION 'default',
    ENDPOINT 's3.beta.shelby.xyz',
    URL_STYLE 'path'
);
SET s3_uploader_max_parts_per_file = 3000;

URL_STYLE 'path' is required. Shelby's endpoint doesn't serve bucket names as hostnames, so DuckDB's default virtual-host addressing can't reach your bucket.
s3_uploader_max_parts_per_file = 3000 matches Shelby's part limit. DuckDB sizes upload parts by dividing s3_uploader_max_filesize (800 GB by default) by it.
The secret lasts for this session. A PERSISTENT secret would store your credentials in plain text under ~/.duckdb.

Write files

Create a sample table and write it to Shelby as Parquet, as CSV, and as Parquet partitioned by year. In the COPY statements, replace shelby-managed-account-1 with your bucket name:

CREATE TABLE readings AS
    SELECT i AS id, 2023 + i % 3 AS year, round(((i * 7919) % 1000) / 10.0, 1) AS value
    FROM range(3000) t(i);

COPY readings TO 's3://shelby-managed-account-1/data/readings.parquet' (FORMAT parquet);
COPY readings TO 's3://shelby-managed-account-1/data/readings.csv' (FORMAT csv);
COPY readings TO 's3://shelby-managed-account-1/data/by_year' (FORMAT parquet, PARTITION_BY (year));

The last statement writes one Parquet file per year, under by_year/year=2023/ and so on. DuckDB uploads large files in parts, sized to fit the part limit set above.

Query a file

SET VARIABLE bucket = 's3://' || getenv('SHELBY_BUCKET');

SELECT count(*), sum(value) FROM read_parquet(getvariable('bucket') || '/data/readings.parquet');

SELECT * FROM read_csv(getvariable('bucket') || '/data/readings.csv') LIMIT 10;

A file is readable as soon as the COPY that wrote it returns.

Query many files at once

SELECT year, count(*), sum(value)
FROM read_parquet(getvariable('bucket') || '/data/by_year/*/*.parquet', hive_partitioning = true)
GROUP BY year
ORDER BY year;

DuckDB reads only the byte ranges it needs, so a query over a large Parquet file downloads a fraction of it.

What's different with DuckDB

Globs can miss files written in the last few seconds. A glob lists the bucket, and a new file can take a few seconds to appear in listings. If a partition is missing, run the query again. Reading a file by its exact path works straight away.
DuckDB can't delete objects. Delete them with another client, such as the AWS CLI.

Compatibility matrix · Troubleshooting