# DuckDB (/tools/s3-gateway/quickstarts/duckdb)

## Before you start

* [DuckDB](https://duckdb.org/docs/installation/) 1.1 or later
* Your access key ID, API key and bucket name from [console.shelby.xyz](https://console.shelby.xyz)

Export your credentials and bucket name from the
[console](https://console.shelby.xyz) so the commands below can use them:

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

<Steps>
  <Step>
    ### Create a secret for Shelby

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

    ```sql
    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`.
  </Step>

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

    ```sql
    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.
  </Step>

  <Step>
    ### Query a file

    ```sql
    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.
  </Step>

  <Step>
    ### Query many files at once

    ```sql
    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.
  </Step>
</Steps>

## 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](/tools/s3-gateway/quickstarts/aws-cli).

[Compatibility matrix](/tools/s3-gateway/compatibility) ·
[Troubleshooting](/tools/s3-gateway/troubleshooting)