2023-09-02
Establishing the Need
Our in-memory column-major information processing platform, tech.ml.dataset (TMD), drives the early of functional information science. When information gets ample capable not to fresh successful memory, 1 tin proceed pinch TMD by operating connected samples of data, aliases different filtering to applicable subsets to fresh successful bounds imposed by the moving environment. Moreover, 1 tin execute persistence, for information mini and large, pinch nippy, arrow, aliases parquet.
When information becomes ample enough, for illustration sets of .csv files connected the bid of ~100GB pinch relational aspects to them, the devices successful their existent authorities tin go unwieldy. One is tempted to get progressive successful nonfunctional sparky cluster snafus. Of course, maintaining immoderate level of transactional relationship and a elemental disk IO exemplary is still super-desirable. Local disks are large enough, and section chips are accelerated enough, nary request to do thing rash.
Relational databases are good adapted for out-of-memory retention and accelerated relational queries - but, really to leverage this without giving up the advantages of functional programming, and TMD's column-major processing model? JDBC, on pinch Postgres, supply a bully first reply to this question, but it's irritating to execute a afloat row-to-column conversion done an inefficient, non-batched API successful bid to get the information done JDBC and into TMD.
A New Challenger Appears
DuckDB showed up via a github issue successful May of 2021 and tmducken was minimally integrated pinch their C bindings by December of that year. In that version, each query results were returned astatine once, and truthful needed to fresh successful memory. Also, successful those early days of DuckDB location was not a circumstantial precocious capacity append aliases insert system, truthful IO was limiting imaginable performance, and Postgres persisted arsenic the adjunct processing strategy to TMD. Much has changed since then.
In the past 2 years, DuckDB improved a lot. Importantly, the C interface now provides a batched strategy for some inserts and querying, which enables processing very ample joins - much connected that later. These improved capabilities tin now beryllium leveraged successful Clojure, done TMD, to entree DuckDB's authorities of the creation vectorized SQL execution engine, and it's good.
Actual Use
Building connected our previous post, location is simply a 50 gigabyte .csv record pinch 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.csvLoading that into DuckDB is amazingly easy - though, you do person to hold 2 minutes:
$ clip duckdb data.ddb 'CREATE TABLE information 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.ddbSo, that reduced the record to 18GB, which includes each of the indexes (!) created automatically by DuckDB.
The information is successful 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 │ cardinal │ default │ other │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤ │ customer-id │ VARCHAR │ YES │ │ │ │ │ time │ BIGINT │ YES │ │ │ │ │ inst │ TIMESTAMP │ YES │ │ │ │ │ period │ BIGINT │ YES │ │ │ │ │ marque │ VARCHAR │ YES │ │ │ │ │ style │ VARCHAR │ YES │ │ │ │ │ sku │ VARCHAR │ YES │ │ │ │ │ twelvemonth │ BIGINT │ YES │ │ │ │ │ transaction-id │ VARCHAR │ YES │ │ │ │ │ amount │ BIGINT │ YES │ │ │ │ │ value │ DOUBLE │ YES │ │ │ │ ├────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤ │ 11 rows 6 columns │ └──────────────────────────────────────────────────────────────────────┘Accessing this from Clojure, done TMD, is besides 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, ideate guidance lets america cognize that location is different dataset, that captures accusation for each sku astir what colors the merchandise is. That needs to beryllium successful the database arsenic well:
user> (-> (let [colors ["red" "green" "blue" "yellow" "purple" "black" "white"]] (->> (for [brand (range 100) style (range 10) point (range 10)] (let [sku (format "sku-%s-%s-%s" marque style item) n (rand-int 8)] (for [color (take n (shuffle colors))] {"sku" sku "color" color}))) (apply concat))) (ds/->dataset {:dataset-name "colors"})) colors [35179 2]: | sku | colour | |------------|--------| | sku-0-0-0 | reddish | | sku-0-0-0 | bluish | | sku-0-0-0 | achromatic | | sku-0-0-0 | yellowish | | sku-0-0-0 | achromatic | | sku-0-0-0 | greenish | | sku-0-0-1 | achromatic | | sku-0-0-1 | yellowish | | sku-0-0-1 | bluish | | sku-0-0-1 | purple | | ... | ... | | sku-99-9-8 | yellowish | | sku-99-9-8 | purple | | sku-99-9-8 | achromatic | | sku-99-9-8 | reddish | | sku-99-9-8 | achromatic | | sku-99-9-8 | bluish | | sku-99-9-8 | greenish | | sku-99-9-9 | purple | | sku-99-9-9 | bluish | | sku-99-9-9 | achromatic | | sku-99-9-9 | greenish | user> (duckdb/create-table! conn *1) "colors" user> (duckdb/insert-dataset! conn *2) 35179You cognize wherever this is going. We request to subordinate the 400M transactions, each pinch a sku, pinch this information that implies each sku has astir 3.51 colors.
Luckily, their first petition is comparatively simple. "How galore items of each colour were sold successful March of 2021?", and "When tin we know?" they ask.
;; First, because you can, subordinate 1.4 cardinal rows connected your laptop successful 2.5s... user> (time (duckdb/sql->dataset conn "SELECT COUNT(*) FROM information INNER JOIN colors ON data.sku = colors.sku;")) "Elapsed time: 2486.620275 msecs" :_unnamed [1 1]: | count_star() | |-------------:| | 1416737859 | ;; Then, reply their question... user> (time (duckdb/sql->dataset conn "SELECT color, COUNT(*) FROM information INNER JOIN colors ON data.sku = colors.sku WHERE data.year='2021' AND data.month='3' GROUP BY color;")) "Elapsed time: 1077.723309 msecs" :_unnamed [7 2]: | colour | count_star() | |--------|-------------:| | reddish | 5714223 | | yellowish | 5652010 | | achromatic | 5720753 | | bluish | 5750846 | | achromatic | 5689916 | | greenish | 5816652 | | purple | 5671959 |We tin cognize 1s from now. Here are the answers.
Maybe the adjacent mobility is not good suited to SQL, and doing the processing successful Clojure, pinch TMD, would beryllium better. This illustration reduces complete every transaction of the boss' favourite sku, sorted chronologically (in 1s):
user> (time (reduce (fn [eax ds] (conj eax (ds/row-count ds))) [] (duckdb/sql->datasets conn "SELECT * FROM information WHERE sku='sku-50-5-5' ORDER BY inst"))) "Elapsed time: 1067.480751 msecs" [2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 2048 576 733]Of course, this simplification usability is trivial, but it proves the constituent - realized datasets are disposable for arbitrary processing, done reduction, by a system that will ne'er tally retired of memory.
DuckDB besides supports a zero transcript query pathway. If nary chunk of the query consequence needs to flight the reducing function, past the instrumentality tin perchance do little work. In the illustration beneath this is turned connected and accessed by passing the {:reduce-type :zero-copy-imm} option.
When processing tin beryllium expressed successful this manner, this is theoretically the lowest representation pathway available.
user> (time (let [sql "SELECT * FROM information WHERE sku='sku-50-5-5' ORDER BY inst" options {:reduce-type :zero-copy-imm}] (reduce (fn [eax zc-ds] (conj eax (ds/row-count zc-ds))) [] (duckdb/sql->datasets conn sql options)))) "Elapsed time: 1037.480113 msecs" [2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 2048 531 1408]Hopefully that gives a consciousness of the powerfulness afforded by this system, now.
Some Interesting DuckDB TidBits
DuckDB automatically stores each numeric information successful minmax indexes - besides called BRIN indexes. These do not adhd importantly to the original information size but do dramatically summation query performance. It will besides automatically create ART indexes successful the lawsuit of unsocial aliases superior cardinal columns. Finally, users tin optionally create indexes for categorical columns but the drawback is accrued disk retention size and perchance slower transactions.
DuckDB is written successful modular C++11 and is frankincense reasonably portable - they had a version built for the mac m-1 quickly and if you had different level and wanted immoderate typical condiment we would consciousness comfortable compiling and modifying the database for that platform. The src directory of their codebase, arsenic of this penning (September 2023) clocks successful astatine astir 100,000 LOC of C++ code:
(base) chrisn@chrisn-lp2:~/dev/cnuernber/duckdb$ cloc src 1612 matter files. 1612 unsocial files. 0 files ignored. github.com/AlDanial/cloc v 1.90 T=0.83 s (1936.0 files/s, 203773.8 lines/s) ------------------------------------------------------------------------------- Language files blank remark code ------------------------------------------------------------------------------- C++ 831 13749 8912 99454 C/C++ Header 679 7754 9903 28287 CMake 101 49 0 1541 Markdown 1 7 0 15 ------------------------------------------------------------------------------- SUM: 1612 21559 18815 129297 -------------------------------------------------------------------------------DuckDB is MIT licensed, has an unfastened github improvement model, and their organization is speedy to reply questions. Developing specified a precocious value powerfulness instrumentality successful specified an unfastened mode is honorable.
Wrapping Up
Try retired our duckdb integration, aliases to prosecute america to do it for you. DuckDB complements TMD good and greatly increases a mini team's expertise to efficiently negociate and process ample datasets without needing to edifice to costly distributed solutions. This integration supports and represents the worth of precocious value and businesslike compute devices to genuinely democratize information processing by enabling functional solutions connected laptops that others pinch duller devices will scope for a cluster to manage.
TechAscent: Getting our ducks successful a row.
Contact us
English (US) · 
Indonesian (ID) ·