DuckDB – Data power tools for your laptop, now in Clojure (2023)

Blog HomeContact2023-09-02DuckDB - Data power tools for your laptop, now in ClojureEstablishing the NeedOur in-memory column-major data processing platform, tech.ml.dataset (TMD), drives the future of functional data science. When data gets large enough not to fit in memory, one can continue with TMD by operating on samples of data, or otherwise filtering to relevant subsets to fit in bounds imposed by the working environment. Moreover, one can accomplish persistence, for data small and large, with nippy, arrow, or parquet.When data becomes large enough, for example sets of .csv files on the order of ~100GB with relational aspects to them, the tools in their current state can become unwieldy. One is tempted to get involved in nonfunctional sparky cluster snafus. Of course, maintaining some level of transactional interaction and a simple disk IO model is still super-desirable. Local disks are big enough, and local chips are fast enough, no need to do anything rash.Relational databases are well adapted for out-of-memory storage and fast relational queries - but, how to leverage this without giving up the advantages of functional programming, and TMD's column-major processing model? JDBC, along with Postgres, provide a good first answer to this question, but it's irritating to perform a full row-to-column conversion through an inefficient, non-batched API in order to get the data through JDBC and into TMD.A New Challenger AppearsDuckDB showed up via a github issue in May of 2021 and tmducken was minimally integrated with their C bindings by December of that year. In that version, all query results were returned at once, and so needed to fit in memory. Also, in those early days of DuckDB there was not a specific high performance append or insert system, so IO was limiting potential performance, and Postgres persisted as the adjunct processing system to TMD. Much has changed since then.In the last two years, DuckDB improved a lot. Importantly, the C interface now provides a batched system for both inserts and querying, which enables processing very large joins - more on that later. These improved capabilities can now be leveraged in Clojure, through TMD, to access DuckDB's state of the art vectorized SQL execution engine, and it's good.Actual UseBuilding on our previous post, there is a 50 gigabyte .csv file with 3 years of transaction data, totaling 400,000,000 rows:$ ll -h data.csv -rw-rw-r-- 1 harold harold 50G Aug 8 09:49 data.csv Loading that into DuckDB is surprisingly easy - though, you do have to wait 2 minutes:$ time duckdb data.ddb 'CREATE TABLE data AS FROM "data.csv";' 100% ▕████████████████████████████████████████████████████████████▏ real 1m50.091s user 21m42.693s sys 0m57.887s $ ll -h data.ddb -rw-rw-r-- 1 harold harold 18G Sep 6 10:57 data.ddb So, that reduced the file to 18GB, which includes all of the indexes (!) created automatically by DuckDB.The data is in there:$ duckdb data.ddb v0.8.1 6536a77232 Enter ".help" for usage hints. D SELECT COUNT(*) AS n FROM data; ┌───────────┐ │ n │ │ int64 │ ├───────────┤ │ 400000000 │ └───────────┘ D DESCRIBE TABLE data; ┌────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤ │ customer-id │ VARCHAR │ YES │ │ │ │ │ day │ BIGINT │ YES │ │ │ │ │ inst │ TIMESTAMP │ YES │ │ │ │ │ month │ BIGINT │ YES │ │ │ │ │ brand │ VARCHAR │ YES │ │ │ │ │ style │ VARCHAR │ YES │ │ │ │ │ sku │ VARCHAR │ YES │ │ │ │ │ year │ BIGINT │ YES │ │