Directly running DuckDB queries on data stored in SQLite files | Hacker News Reader

Directly running DuckDB queries on data stored in SQLite files

twitter.com

75 pointseatonphil23 comments

Exciting stuff!

I’ve been using duckdb instead of Pandas these days because it’s much faster on larger datasets plus I can write more compact and complex SQL than I can Pandas constructs. The fact that duckdb can query in memory Pandas dataframes faster than Pandas itself is a plus.

It’s like having a local performant database engine that can query and join across Parquet, CSV, Pandas and now SQLite.

Kinda like a quick efficient local Spark.

I've built an offline desktop for this: https://superintendent.app

It is a wrapper on Sqlite, but I'm looking to switch to duckdb because its dialect is more comprehensive.

Previously, I was using ruby, sometimes python, and sometimes postgresql to process CSV. But it wasn't convenient enough. Most of the times I just tried to use Excel, but formula is so hard to use.

Can't wait to see what simonw comes up for using this with datasette!

This would be super useful for something I’m working on using datasette, querying across three tables with over 1.5m rows. Going to try the same queries on this tomorrow.

Setting up a plugin hook that lets you use alternative database backends is getting more and more tempting...

Like PHP PDO? https://www.php.net/manual/en/book.pdo.php

Or are you talking about something else?

Exactly what I have been hoping to see as well. Analytical queries are a tad too slow for datasette, so I have had to materialize the more interesting roll-ups.

I really like the DuckDB philosophy, and the maintainers were really responsive and helpful with my docs questions. Didn't end up using it because the compressed database files were much larger than SQLite for my data (weird use case, admittedly), and the .NET libraries were poor. I look forward to trying it out again, though.

> the compressed database files were much larger than SQLite for my data

Just curious, what format did you use?

If it's Parquet, it does column compression via Snappy by default (which more optimized for speed than size).

You can change the compression to ZSTD or GZIP for higher compression ratios.

https://duckdb.org/docs/data/parquet

I tried all of the available compression formats in parquet, natively in duckdb, and compressing raw duckdb files as a whole. IIRC just GZIPing SQLite files beat them all. The SQLite files were much smaller uncompressed (~220mb vs 800mb) which was probably most of the difference.

Can you still query a gzipped sqlite file? What’s the sense in gzipping it if not?

Do other formats (Snappy?) allow this?

You can query snappy parquet files directly.

You can also query gzip and zstd parquet files directly.

> ...the .NET libraries were poor.

DuckDB's Node bindings leave a lot to be desired, as well. WASM bindings have a different API surface, too. From my reading of the code and docs, it looks like duckdb 3p bindings for anything other than python is in dire need fit and polish.

We're still on a journey to explore what APIs work best with JavaScript but the differences between WASM and Node are on purpose. DuckDB-Wasm has isolated wasm heap memory and runs in a separate web worker. That's why we serialize everything as Arrow IPC buffer and pass the ArrayBuffer through the workers message API as transferrable. On Node.js, we can interact with DuckDB much more easily and don't want to pay the price for the IPC stream every time. The truth is, we tried quite a few different APIs and this turned out the be a good trade off between efficiency and convenience.

But I'm happy to discuss this further if you have any suggestions.

ClickHouse can do this since maybe a year already: https://clickhouse.com/docs/en/engines/database-engines/sqli...

ClickHouse is a full DBMS server.

DuckDB, similar to SQLite, is an in-process query engine for local data.

ClickHouse can also run as a query engine for local data, see clickhouse-local.

Would love to see a benchmark comparing SQLite, DuckDB and this SQLite/DuckDB combo with the same queries and data.

Is this adapter copying the contents of the SQLite db into DuckDBs own memory layout in order to optimise the queries or is it just “proxying” to the SQLite library?

It is just scanning the data (in parallel), not copying it into DuckDB!

I'm excited to use duckdb in DataStation/dsq but the Go bindings aren't yet super mature and I haven't had time to work on them myself yet.

We have too many database products.

Reply on news.ycombinator.com