DuckDB

DuckDB is a flexible database system that can use an S3 bucket to hold data for querying. DuckDB provides a CLI and APIs for a variety of popular programming languages, including Python, R, and Node.js.

DuckDB provides documentation on connecting to S3 buckets and querying from S3 buckets. This documentation page is specific to OSN S3 buckets.

Installation

Installation depends on your client and programming language. See the DuckDB website for installation requirements. The instructions in this document were tested using the Python client.

Configuration

DuckDB uses SQL to set up S3 credentials in this form:

CREATE SECRET (
    TYPE s3,
    PROVIDER config,
    KEY_ID 'EXAMPLEKEY1234',
    SECRET 'EXAMPLESECRET1234567',
    ENDPOINT 'https://uma1.osn.mghpcc.org'
);

Replace the KEY_ID, SECRET, and ENDPOINT with values specific to your bucket. Don’t include your bucket name in the endpoint.

Using DuckDB over S3

DuckDB can read files in parquet or DuckDB form over S3. However, DuckDB can’t write an update to a DuckDB file over S3. To write a DuckDB file to S3, refer to other utilities like RClone or the AWS CLI.

DuckDB can write parquet files over S3, but not update existing files. Since DuckDB can “glob” parquet files, this is fine in practice as long as you construct new writes to not overlap with existing data. In this example, we’ll write a whole table from an existing csv to parquet through the DuckDB Python API. While this example uses Python, the commands are entirely in SQL, so it should translate easily to the other DuckDB APIs.

import duckdb

# Create the credentials
with duckdb.connect("my-existing-db.db") as con:
    con.sql("CREATE OR REPLACE SECRET s3 ("
                "TYPE s3,"
                "PROVIDER config,"
                "KEY_ID 'EXAMPLEKEY1234',"
                "SECRET 'EXAMPLESECRET1234567',"
                "ENDPOINT 'https://uma1.osn.mghpcc.org');"
            )
    
    con.sql(f"COPY existing-table TO 's3://my-bucket/existing-table.parquet' (FORMAT parquet)")

To read your copied table, connect to the bucket and select your desired colums:

with duckdb.connect() as con:
    # ADD YOUR CREDENTIALS HERE #
    
    # Selects all parquet files with a glob
    query = con.sql(f"SELECT * FROM 's3://my-bucket/*.parquet'")

    # Selects a specific parquet file
    query = con.sql(f"SELECT * FROM 's3://my-bucket/existing-table.parquet'")

    # Add additional aggregation, filtering, or format translation

DuckDB can also create hive partitions for data in an S3 bucket. This is a great structure if you intend to filter queries in a predictable manner. For example, if your table holds data on subjects belonging to different animal species and you intend to always filter by species, you can create a species partition:

with duckdb.connect("my-existing-db.db") as con:
    # ADD YOUR CREDENTIALS HERE #

    con.sql(f"COPY animals TO 's3://my-bucket/' ("
                f"FORMAT parquet, "
                f"PARTITION_BY (Species), "
                f"APPEND"
            ");"
            )

To read back a specific partition, include the partition in the bucket string:

with duckdb.connect() as con:
    # ADD YOUR CREDENTIALS HERE #

    query = con.sql(f"SELECT * FROM read_parquet('s3://my-bucket/Species=procyon-lotor/*.parquet', hive_partitioning = true) AS AnimalData")

    # Add additional aggregation, filtering, or format translation

To read back all data without keeping the partitioning, use a glob corresponding to the depth of your partitioning:

with duckdb.connect() as con:
    # ADD YOUR CREDENTIALS HERE #

    query = con.sql(f"SELECT * FROM read_parquet('s3://my-bucket/*/*.parquet', hive_partitioning = true) AS AnimalData")

    # Add additional aggregation, filtering, or format translation