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

TechAscent - DuckDB - Data power tools for your laptop, now in ClojureBlog Home Contact

2023-09-02 DuckDB - Data power tools for your laptop, now in Clojure Establishing the Need Our 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 Appears DuckDB 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 Use Building 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 │ │ │ │ │ transaction-id │ VARCHAR │ YES │ │ │ │ │ quantity │ BIGINT │ YES │ │ │ │ │ price │ DOUBLE │ YES │ │ │ │ ├────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤ │ 11 rows 6 columns │ └──────────────────────────────────────────────────────────────────────┘ Accessing this from Clojure, through TMD, is also easy: user> (require '[tmducken.duckdb :as duckdb]) nil user> (require '[tech.v3.dataset :as ds]) nil user> (duckdb/initialize!) Sep 06, 2023 11:00:12 AM clojure.tools.logging$eval7454$fn__7457 invoke INFO: Attempting to load duckdb from "./binaries/libduckdb.so" true user> (def db (duckdb/open-db "data.ddb")) #'user/db user> (def conn (duckdb/connect db)) #'user/conn user> (time (duckdb/sql->dataset conn "SELECT COUNT(*) AS n FROM data")) "Elapsed time: 10.305756 msecs" :_unnamed [1 1]:

| n | |----------:| | 400000000 | Now, imagine management lets us know that there is another dataset, that captures information for each sku about what colors the product is. That needs to be in the database as well: user> (-> (let [colors ["red" "green" "blue" "yellow" "purple" "black" "white"]] (->> (for [brand (range 100) style (range 10) item (range 10)] (let [sku (format "sku-%s-%s-%s" brand style item) n (rand-int 8)] (for [color...

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

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

NHS apologises and admits Palantir have access to identifiable patient data

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills