16 ms·
DuckDB-Wasm: Efficient analytical SQL in the browser
- elmolino89 5y agoNot really DuckDB-Wasm question but DuckDB: I got a data sets probably not suitable for loading into a memory table (close to 1000M rows CSV). I did split it into 20M rows chunks, read one by one into a DuckDB temporary table and exported as parquet. SELECT using glob prefix.*.parquet where mycolumb=foobar does work but can be a bit faster. Apart from sorting the input to parquet CSVs, what can he done? The CSV chunks were already sorted.
- deleted 5y ago[deleted]
- typingmonkey 5y agoDoes DuckDB support multi tab usage? How big is the wasm file that must be loaded?
- ankoh 5y agoThe WebAssembly module is 1.6 - 1.8 MB brotli-compressed depending on the Wasm feature set. We're currently investigating ways to reduce this to around 1 MB. We further use streaming instantiation which means that the WebAssembly module will be compiled while downloading it. But still, it will hurt a bit more than a 40KB library. Regarding multi-tab usage: Not today. The available filesystem apis make it difficult to implement this right now. We're looking into ways to make DuckDB-Wasm persistent but we can only read in this release.
- domoritz 5y agoOn https://shell.duckdb.org/versus https://shell.duckdb.org/versus, we have a comparison with related libraries. The WASM bundles currently is 1.8 MB but it can be instantiated while it's streaming in. The size probably makes it prohibitive to use DuckDB when your dataset is small and download size matters but we hope that future improvements in WebAssembly can get the size down.
- crimsoneer 5y agoI'm still not sure I "get" the use case for DuckDB. From what I understand, it's like a nifty, in-memory SQL, but why is that better than just running PostGRES or Microsoft SQL server locally, where your data structures and tables and stuff have a lot more permanence? Like, my workflow is either I query an exiting remote corporate DB and do my initial data munging there, or get givne a data dump that I either work on directly in Pandas, or add to a local DB and do a little more cleaning there. Not at all clear how Duck DB would hel
- 1egg0myegg0 5y agoCheck out this post for some comparisons with Pandas. https://duckdb.org/2021/05/14/sql-on-pandas.html https://duckdb.org/2021/05/14/sql-on-pandas.html DuckDB is often faster than Pandas, and it can handle larger than memory data. Plus, if you already know SQL, you don't have to become a Pandas expert to be productive in Python data munging. Pandas is still good, but now you can mix and match with SQL!
- catawbasam 5y agoNot just in-memory. It's pretty convenient if you have a set of Parquet files with common schema. Fairly snappy and doesn't have to fit in memory.
- jamesrr39 5y agoI'm using duckdb for querying parquet files as well. It's an awesome tool, so nice to just "look into" parquet files with SQL.
- deshpand 5y agoMany enterprises are coming up with patterns where they replicate the data from the database (say Redshift) into parquet files (data lake?) and directing more traffic including analytical workloads onto the parquet files. duckdb will be very useful here, instead of having to use Redshift Spectrum or whatever.
- 5y ago
- joos2010kj 5y agoAwesome!
- xnx 5y agoSimilar(?): https://sql.js.org/ https://sql.js.org/ (SQLite in wasm)
- domoritz 5y agoYes but DuckDB is optimized for analytics (columnar data and vectorized computation). Take a look at the comparison in https://shell.duckdb.org/versus https://shell.duckdb.org/versus.
- deleted 5y ago[deleted]
- obeliskora 5y agoThere was neat post https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630 a while ago about about using sqlite on static pages with large datasets that wouldn't have to be loaded entirely. Does duckdb do something similar with arrow/parquet files or its own format?
- ankoh 5y agoYes we do! DuckDB-Wasm can read files using HTTP range requests very similar to the sql.js-httpvfs from phiresky. The blog post contains a few examples how this can be used, for example, to partially query Parquet files over the network. E.g. just visit shell.duckdb.org and enter: select * from 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parqu...' limit 10;
- 1egg0myegg0 5y agoNIIIICE! Data twitter was pretty excited about that cool SQLite trick - now you can turn it up a notch!
- tomrod 5y agoIs data twitter == #datatwitter, like Econ Twitter is #econtwitter? If so, I have another cool community to follow!
- obeliskora 5y agoThat's really neat! Can you control the cache too?
- ankoh 5y agoDuckDB-Wasm uses a traditional buffer manager and evicts pages using a combination of FIFO + LRU (to distinguish sequential scans from hot pages like the Parquet metadata).
- obeliskora 5y ago
- pantsforbirds 5y agoDuckDB is one of my favorite projects ive stumbled on recently. I've had multiple use cases pop up where i wanted to do some pandas type work, but sqlite was a better fit so its really come in handy for me.
- pantsforbirds 5y agoAnyone have a good benchmark comparing DuckDb to Parquet/Avro/ORC etc.? Super curious to see how some of those workflows might compare. Obviously at scale its going to be different, but using a single parquet file/dataset as a db replacement isn't an uncommon thing in DS/ML work.
- mytherin 5y agoWhy compare DuckDB to Parquet when you can use DuckDB and Parquet [1] :) [1] https://duckdb.org/2021/06/25/querying-parquet.html https://duckdb.org/2021/06/25/querying-parquet.html
- tomnipotent 5y agoDoes DuckDB also use a PAX-like format like Parquet? Without going into code, the best I could find with a little googlefu is the HyPer/Data Blocks paper - is this a relevant read?
- mytherin 5y agoDuckDB's storage format has similar advantages as the Parquet storage format (e.g. individual columns can be read, partitions can be skipped, etc) but it is different because DuckDB's format is designed to do more than Parquet files. Parquet files are intended to store data from a single table and they are intended to be written-once, where you write the file and then never change it again. If you want to change anything in a Parquet file you re-write the file. DuckDB's storage format is intended to store an entire database (multiple tables, views, sequences, etc), and is intended to support ACID operations on those structures, such as insertions, updates, deletes, and even altering tables in the form of adding/removing columns or altering types of columns without rewriting the entire table or the entire file. Tables are partitioned into row groups much like Parquet, but unlike Parquet the individual columns of those row groups are divided into fixed-size blocks so that individual columns can be fetched from disk. The fixed-size blocks ensure that the file will not suffer from fragmentation as the database is modified. The storage is still a work in progress, and we are currently actively working on adding more support for compression and other goodies, as well as stabilizing the storage format so that we can maintain backwards compatibility between versions.
- deleted 5y ago[deleted]
- timwis 5y agoInteresting.. Would this be effective at loading a remote CSV file with a million rows, then performing basic GROUP BY COUNTs on it so I can render bar charts? I’ve been thinking of using absurd-sql for it since I saw https://news.ycombinator.com/item?id=28156831 https://news.ycombinator.com/item?id=28156831 last week
- ankoh 5y agoIt depends. Querying CSV files is particularly painful over the network since we still have to read everything for a full scan. With Parquet, you would at least only have to read the columns of group by keys and aggregate arguments. Try it out and share your experiences with us!
- texodus 5y agoI contribute to https://perspective.finos.org/ https://perspective.finos.org/ , supports all of this and quite a lot more. Here's 1,000,000 rows example I just threw together for you https://bl.ocks.org/texodus/3802a8671fa77399c7842fd0deffe925 https://bl.ocks.org/texodus/3802a8671fa77399c7842fd0deffe925 and a CSV example, you try yours right now https://bl.ocks.org/texodus/02d8fd10aef21b19d6165cf92e43e668 https://bl.ocks.org/texodus/02d8fd10aef21b19d6165cf92e43e668
- munro 5y agoCool! This is the first time hearing about DuckDB, exciting as I heavily use SQLite. And these benchmarks are showing it's 6-15 times faster than sql.js (SQLite) [1], along with another small benchmark I found [2]. I usually just slather on indexes in SQLite tho, so indexed queries may not stand up as well; and might not be as fast when it's on storage, as this is comparing in memory performance (I think?), but I'll give it a spin! Gonna throw this out there: main thing I'm looking for from an embedded DB is better on disk compression; I've been toying with RocksDB, but it's hard to tune optimally & it's really too low level for my needs. [1] > ipython import numpy as np duckdb = [0.855, 0.179, 0.151, 0.197, 0.086, 0.319, 0.236, 0.351, 0.276, 0.194, 0.086, 0.137, 0.377] sqlite = [8.441, 1.758, 0.384, 1.965, 1.294, 2.677, 4.126, 1.238, 1.080, 5.887, 1.194, 0.453, 1.272] print((np.quantile(sqlite, q=[0.1, 0.9]) / np.quantile(duckdb, q=[0.1, 0.9])).round()) [2] https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html
- sitkack 5y agoDo you need compression to get more bandwidth or are you trying to save money on storage costs? DuckDB is a column based db, so you are going to see a throughput increase for queries that only use a handful of columns.
- munro 5y agoSaving money definitely, but also delaying having to scale the hardware as long as possible. I'm at 3.5 TiB NVMe (raid 1), and it would cost another $52 USD/mo to add an additional raid of 1.5 TiB NVMe @ Hetzner, not cool. Generally I'm seeing around 6:1 compression, so going from 3.5 TiB to 21 TiB is a big deal for me. I'm manually doing zlib compression for large text columns when there's an obvious opportunity, basically DIY toast [1]. Doing that allowed one SQLite DB to go from about 205 GiB to 35 GiB. And I haven't really felt any performance impact when working with the data; but definitely feel the coding overhead. And there's still so much missed opportunity for compression. Largest RocksDB is +1 GiB/day (poorly tuned with zlib compression). I just couldn't use SQLite for that one, lots of small rows, but they compress extremely well. I never wrapped up the compression experiments on that, but look at some rough notes snappy was 430G, and lz4 level 6 was 86G. Unfortunately using RocksDB has made coding more difficult. I think one day I'm just going to snap and build a ZipFS-like extension [2], until them I'm just trying to keep an eye out, and putting out this call for help. :3 [1] https://www.postgresql.org/docs/9.5/storage-toast.html https://www.postgresql.org/docs/9.5/storage-toast.html [2] https://www.sqlite.org/zipvfs/doc/trunk/www/index.wiki https://www.sqlite.org/zipvfs/doc/trunk/www/index.wiki
- aynyc 5y agoI wish it supports ORC.
- sgarrity 5y agoHow does this compare/relate to https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web (if at all)?
- ankoh 5y agoThe author outlines many problems that you'll run into when implementing a persistent storage backend using the current browser APIs. We faced many of them ourselves but paused any further work on an IndexedDB-backend due to the lack of synchronous IndexedDB apis (e.g. check the warning here https://developer.mozilla.org/en-US/docs/Web/API/IDBDatabaseSync https://developer.mozilla.org/en-US/docs/Web/API/IDBDatabase...). He bypasses this issue using SharedArrayBuffers which would lock DuckDB-Wasm to cross-origin-isolated sites. (See the "Multithreading" section in our blog post) We might be able to lift this limitation in the future but this has some far-reaching implications affecting the query execution of DuckDB itself. To the best of my knowledge, there's just no way to do synchronous persistency efficiently right now that wont lock you to a browser or cross-origin-isolation. But this will be part of our ongoing research.
- fnord77 5y agothis looks so cool. is there pre-loaded a demo page loaded with tables so people can try out queries right away?
- 1egg0myegg0 5y agoIf you head over to the shell demo, you can run a query like the one below! https://shell.duckdb.org/ https://shell.duckdb.org/ select * from 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parqu...' limit 10;
- sedatesteak 5y agoI got excited about duckdb recently too. Used it yesterday for a new project at work and immediately ran into a not implemented exception for my (awful) column naming structure and discovered there is no pivot function. Otherwise, it's great, but obviously still a wip. For those wondering, I have a helper function for soql queries for salesforce that follows the structure object.field Referring to a tablealias.[column.name] or quotes instead of brackets was a no go.
- eyeball 5y agoCan I connect to Duckdb with a sql ide? E.g. dbeaver?
- 1egg0myegg0 5y agoYes! DuckDB (not WASM DuckDB) has a JDBC connector that works with DBeaver.
- eyeball 5y agothank you