DuckDB: it's not quack science

**TL;DR:** DuckDB is an open-source, embedded, SQL-supporting analytical database that can function as a command-line tool for directly querying files, or as a library embedded within applications. It is more flexible and efficient than traditional Unix pipelines for rapid data analysis scenarios. ## DuckDB Overview DuckDB is a relational database open-sourced under the MIT license, using PostgreSQL's SQL dialect. Its codebase is approximately one million lines of C++, has no external dependencies, and compiles to a single binary of about 50 MB. It runs without requiring root or sudo. Presenter Gabor Sarn demonstrates DuckDB's flexibility from three angles: command-line tool, portable database, and database server. ## Command-Line Tool ### Directly Querying Files After installation, entering `duckdb` opens a REPL. DuckDB allows you to reference CSV/TSV files directly in queries without pre-creating tables or defining schemas. For example, to list distinct countries served by high-speed rail: ```sql SELECT DISTINCT country FROM 'stations.tsv'; ``` It auto-detects headers and column types, loads them as temporary tables, and yields the same result as the Unix pipeline `cat stations.tsv | tail -n +2 | cut -f2 | sort -u`, but more succinctly. ### Working in Pipelines DuckDB supports Unix pipeline composition: `duckdb -c "SELECT ..."` can accept stdin or output to stdout, acting as a pipeline step. For more complex analyses (e.g., JOINing two files), DuckDB requires just one line of SQL: ```sql SELECT * FROM 'trains.tsv' JOIN 'stations.tsv' USING (station); ``` Achieving the same with pure Unix tools would require pre-sorting, header handling, awk concatenation, and other tedious steps that become unmaintainable as query complexity increases. ### Pivot and Friendly SQL DuckDB supports extensions beyond standard SQL, such as PIVOT operations. In the demo, it creates a station-to-station distance matrix using data from London, then uses the `EXCLUDE` clause to batch-convert units (kilometers to miles): ```sql SELECT station1, * EXCLUDE (station1) / 1.61::INTEGER FROM pivot_result; ``` Similarly, UNPIVOT restores wide tables to long tables, simplified by using the column star (`COLUMNS(*)`). ### CLI Summary Under the CLI, DuckDB: runs well in memory without persistent storage; supports automatic parallelization; can spill to disk; is type-safe and easy to debug. The presenter compared row-counting performance: on macOS, DuckDB was once faster than BSD's `wc`, but by 2026 the Rust-rewritten `wc` (uutils) is noticeably ahead. ## Portable Database DuckDB can be embedded into various applications. It provides drivers for Python, Go, Rust, Java, JavaScript, R, and supports WebAssembly for browser use. Usage is similar to a regular library: ```python import duckdb con = duckdb.connect('my.db') con.execute("FROM 'services*' LOAD DATA...") ``` ### Performance Demo Using a Dutch train dataset (160 million rows, 21 GB CSV), DuckDB loads at about 1 GB/s on a modern laptop. Querying the average delay after loading takes only 150 milliseconds. Results can be directly converted to a pandas DataFrame for visualization. ## Friendly SQL DuckDB aims to smooth the SQL experience. The demo shows a syntax that places a CSV file directly in the `FROM` clause (the talk cut off here, but context indicates that friendly SQL extensions make previously tedious cross-column operations concise). --- **Source:** YouTube: DuckDB: it's not quack science (https://www.youtube.com/watch?v=q6S0Vv4u3iM)