Rust + DuckDB – Fast meets Faster. Yowza.

There are many things I love in this life. God. Family. The Great Outdoors. Writing code. Reading good books. Writing on Substack. Friends. Pizza. Fishing. Ah well, you get the point. What a time to be alive, the world in tatters, AI upending everything we have taken for gospel for the last … well forever. Because I’m an old curmudgeon, I learned to write Perl as my introduction to code, used to run LAMP stacks on macs underneath my desk and watching everyone to try hack it. That being said, while everyone else is ringing their hands in the error and retching like Gollum at how AI has stolen our precious, our code; I just ignore it all, and do both, use AI for coding when required or expected, and still writing Rust by hand when I get chance for fun.

Rust + DuckDB.

Two of my favorite technologies to mess with these days are Rust and DuckDB. I still find that the picky Rust complier and borrow-checker is a good friend when it comes to writing good code, I enjoy the verbose syntax Rust has, it’s speed, the way I am forced to write simple and straight forward, to the point solutions. DuckDB has become my goto for data processing, especially SQL type processing because of its simplicity, and incredible speed. What better than to bring these two solutions together, the only problem being that both Rust and DuckDB are so versatile, it’s about finding a good use-case where we need, or case get away with, using both.

Maybe we have some CSV file(s) like this, just classic flat file data.

Can you use Rust + DuckDB to process them? Of course.

use duckdb::Connection;
use std::{
    error::Error,
    fs,
    path::{Path, PathBuf},
};

fn main() -> Result<(), Box<dyn Error>> {
    let csv_files = csv_files(Path::new("data"))?;

    let conn = Connection::open_in_memory()?;
    let csv_glob = "data/*.csv";

    println!("Processing {} CSV file(s):", csv_files.len());
    for file in &csv_files {
        println!("  - {}", file.display());
    }

    let mut statement = conn.prepare(
        "
        SELECT
            filename,
            COUNT(*) AS orders,
            ROUND(SUM(amount), 2) AS revenue
        FROM read_csv_auto(?, filename = true)
        GROUP BY filename
        ORDER BY filename
        ",
    )?;
    let rows = statement.query_map([csv_glob], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, i64>(1)?,
            row.get::<_, f64>(2)?,
        ))
    })?;

    println!("\nRevenue by file");
    println!("{:<24} {:>8} {:>12}", "File", "Orders", "Revenue");
    for row in rows {
        let (filename, orders, revenue) = row?;
        println!("{:<24} {:>8} {:>12.2}", filename, orders, revenue);
    }

    let mut statement = conn.prepare(
        "
        SELECT
            region,
            COUNT(*) AS orders,
            ROUND(SUM(amount), 2) AS revenue
        FROM read_csv_auto(?, union_by_name = true)
        GROUP BY region
        ORDER BY revenue DESC
        ",
    )?;
    let rows = statement.query_map([csv_glob], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, i64>(1)?,
            row.get::<_, f64>(2)?,
        ))
    })?;

    println!("\nRevenue by region");
    println!("{:<12} {:>8} {:>12}", "Region", "Orders", "Revenue");
    for row in rows {
        let (region, orders, revenue) = row?;
        println!("{:<12} {:>8} {:>12.2}", region, orders, revenue);
    }

    Ok(())
}

fn csv_files(data_dir: &Path) -> Result<Vec<PathBuf>, Box<dyn Error>> {
    let mut files = fs::read_dir(data_dir)?
        .filter_map(Result::ok)
        .map(|entry| entry.path())
        .filter(|path| path.extension().is_some_and(|extension| extension == "csv"))
        .collect::<Vec<_>>();
    files.sort();
    Ok(files)
}

I have always loved how simple and easy is to use DuckDB in any language, even Rust. Heck, all you have to do is add a simple let conn = Connection::open_in_memory()?; and then you are pretty much off to the races, able to extend Rust by using DuckDB to do any sort of data munging, opening, manipulation, or analysis. We can send commands to be run with a simple statement.

conn.execute_batch(
        r"CREATE TABLE pickles (
              id   INTEGER PRIMARY KEY DEFAULT NEXTVAL('seq'),
              pickles_name TEXT NOT NULL,
              pickles_data BLOB
          );",
    )?;

You can also prepare any sort of SQL statement

let mut stmt = conn.prepare("SELECT * FROM angry_dba")?;

And then fiddle with the data back in Rust as needed.

let rows = statement.query_map([csv_glob], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, i64>(1)?,
            row.get::<_, f64>(2)?,
        ))
    })?;

But, what we tend to forget is how powerful something like DuckDB is out of the box, it isn’t about the fact that we can simply run SQL with Rust in a few lines of code, although that might be nice, but it’s more about how we can EXTEND the power of a Rust program with the almost unlimited extensions provided by DuckDB. If you aren’t familiar with DuckDB as more than just a SQL on top of CSV tool, then you need to do some reading. It’s truly amazing what you can do with it. Let’s face it, the best thing about Rust is its verbose nature, but that can be the worst part as well. With DuckDB we get simplicity and extensibility of working with tabular data in a straight forward and simple manner.

Fast + Faster.