I Query Gigabyte Log Files With DuckDB (No Server, No Import Step)

Last week a teammate sent me a 1.2 GB CSV export and asked which API keys had tripped the rate limiter more than fifty times in the past month. The file came from a dashboard nobody in the room remembered setting up. Classic.

My old instinct would have been to reach for MySQL. Spin up a container, write a CREATE TABLE, fight LOAD DATA INFILE until the column types stopped guessing wrong, and finally run the actual query. The import script alone would have outlived the analysis. These days I open DuckDB first, not a MySQL container.

I didn't do any of that. One command, eleven seconds, answer on the screen.

SELECT api_key, count(*) AS hits
FROM read_csv('export.csv')
WHERE status = 429
GROUP BY api_key
HAVING count(*) > 50
ORDER BY hits DESC;

That's DuckDB, and you've probably seen it on Hacker News this week since the company behind it just got bought by AWS. I'll leave the billion-dollar M&A takes to other people. Here's what I actually do with it on a normal Tuesday: it's the embedded database that treats your files as tables. There's no server to start and no import step to babysit. Point SQL at a CSV, a JSON file, or a Parquet file and it answers.

Why the import pipeline was always the wrong step

MySQL is a database you run. It's great at that. For a one-off question about a file someone emailed you, the entire ceremony is dead weight: schema, migration, import script, and the container you forget to stop until your laptop melts.

DuckDB is the opposite. It's an in-process library, not a service, so there's nothing to provision and nothing to clean up. The schema is inferred from the data when you query, which means a CSV that grows a new column on line three million doesn't break anything. And it's columnar. When I write SELECT count(*) FROM 'logs.parquet', DuckDB reads the row group metadata and only the columns it needs. It doesn't open the whole file like a kid flipping through every page to find the one with the dog in it.

There's a real speed story in there, but let me be honest about the trade-offs too, because I keep seeing people oversell this thing.

DuckDB is an OLAP engine. It will happily scan hundreds of gigabytes while your fan sounds like a leaf blower, and when a file is truly enormous it spills to disk instead of dying. What it is not is a transaction store. I would not back an app with it. SQLite and MySQL still own that job, and I reach for DuckDB several times a week anyway. Different tools, different corners of my desk.

The one-liners I actually use

JSON Lines event logs are a favorite. DuckDB works out the nested fields without being told:

SELECT event, count(*) AS n
FROM read_json_auto('events.jsonl')
GROUP BY event
ORDER BY n DESC;

Everything in a folder becomes one table with a glob, which is how I get through a month of logs without writing a loop:

SELECT * FROM 'logs/2026-08-*.parquet';

And the CLI is good enough that half my analysis never touches Python. I keep a shell alias shaped like this one because I type it a dozen times during a bad week:

duckdb -c "SELECT status, count(*) AS n FROM read_csv('logs/2026-08.csv') GROUP BY status ORDER BY n DESC"

The one that still gets me is remote files. Install the httpfs extension and DuckDB will query a Parquet file on S3 or a public URL without downloading it first:

INSTALL httpfs;
LOAD httpfs;

SELECT * FROM 'https://example.com/data.parquet';

For a solo developer that borders on magic. I've analyzed public datasets this way and never once felt the urge to set up a warehouse.

Python, but only when I need it

When the answer needs to become a chart or feed a script, the Python binding hands you a DataFrame directly:

import duckdb

df = duckdb.sql(
    "SELECT * FROM 'events.parquet' WHERE user_id = 42"
).df()

The SQL does the heavy lifting, so pandas only ever sees the small answer. I don't get a two gigabyte DataFrame materializing in memory, and I can stop pretending I'll read the columns I need later.

Where I draw the line

I keep one persistent file, data.duckdb, for questions I ask more than once. It starts the same REPL but remembers tables between sessions. Everything else follows a rule I stole from a smarter person: the query is the script. The file is disposable, the SQL is not.

That's why the two or three DuckDB one-liners I reuse live in Snippet Ark now instead of a terminal history nobody scrolls. The rate limit query gets asked every month, and six months from now I'll still know why it exists. That's more than I can say for most of the imported tables in my MySQL career.