Embedding DuckDB in a Maven App (and Using It for Things That Aren’t Databases)

Author: Geertjan Wielenga

Original post on Foojay: Read More

DuckDB is described as “SQLite for analytics,” which is true: it’s an in-process database engine that runs inside your application, with no server to install or manage. What’s less obvious from that description is that you can get value out of it without ever creating a database at all. Because it can query CSV, JSON, and Parquet files directly — local or over HTTP — it works perfectly well as an embedded data-crunching library that happens to speak SQL.

This post covers getting it into a Maven project and using it that way.

Setup

One dependency. The native engine is bundled inside the jar, so there’s nothing else to install:

<dependency>
   <groupId>org.duckdb</groupId>
   <artifactId>duckdb_jdbc</artifactId>
   <version>1.5.5.0</version>
</dependency>

It exposes a standard JDBC interface, so if you’ve written Java database code before, there’s no new API to learn. The connection string jdbc:duckdb: (with nothing after the colon) gives you a purely in-memory instance — nothing is written to disk, and everything disappears when the connection closes.

Querying a file on the internet with SQL

Here’s a complete Java application. It runs an aggregation over a CSV file hosted on GitHub — no download step, no schema definition, no table creation:

import java.sql.*;

public class DuckDB {

    public static void main(String[] args) throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:duckdb:"); Statement s = c.createStatement()) {
            ResultSet rs = s.executeQuery("""
       SELECT Species, count(*) AS n,
       round(avg(TRY_CAST("Body Mass (g)" AS DOUBLE)), 1) AS avg_mass        
       FROM 'https://raw.githubusercontent.com/allisonhorst/palmerpenguins/main/inst/extdata/penguins_raw.csv'
       GROUP BY Species ORDER BY n DESC""");
            while (rs.next()) {
                System.out.printf("%-45s %4d %8.1f%n",
                        rs.getString(1), rs.getInt(2), rs.getDouble(3));
            }
        }
    }
}

Querying a file on the internet with SQL

The interesting part is the FROM clause. DuckDB treats the URL as a table: it fetches the file, sniffs the delimiter and column types, and runs the query against it. Swap in a local path and it works the same way. Globs work too — FROM 'logs/*.parquet' queries a whole directory of Parquet files as one dataset.

For anything beyond plain HTTPS (S3, for instance), you’ll need the httpfs extension first:

s.execute("INSTALL httpfs; LOAD httpfs;");

Where this is actually useful

None of this replaces your application database, and it isn’t meant to.

Where it makes sense:

• Replacing hand-rolled CSV parsing. If your app ingests CSV or JSON files, you’ve probably written parsing code, type-coercion code, and then loops to filter and group the results. A DuckDB query does all three in one step, and its CSV reader handles the messy edge cases (quoting, encodings, ragged rows) better than most homegrown parsers.

• In-memory aggregation of data you already have. Anywhere you’d write nested loops with HashMap<String, List<...>> to group and summarize objects, you can often dump the data through DuckDB and express the logic as SQL. Whether that’s clearer depends on the logic and on your team — for a three-line group-by it’s arguably overkill; for anything resembling a pivot or window function it usually wins.

• Generating test data. SELECT * FROM generate_series(1, 100_000_000) materializes and aggregates a hundred million rows in about a second on a laptop. Handy for load-testing downstream code without fixture files.

• Reading Parquet from plain Java. The usual route to Parquet in Java involves a slice of the Hadoop ecosystem. This is one dependency.

Summing up

The story isn’t that DuckDB is magic — it’s that a competent columnar engine is now a Maven dependency away, and it’s willing to treat any file (or URL) as a table.

If your Java code contains a parsing-and-aggregating section you’ve never much liked, it might be a few lines of SQL instead.

The post Embedding DuckDB in a Maven App (and Using It for Things That Aren’t Databases) appeared first on foojay.