21–30 of 167 posts
Say I want to fix logistic Regression. Can I do that in database? Or have to extract it out as a dataframe? Cna I stream the data?
It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular:
- It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database
- Each database is a single file on disk
- It ships as an "amalgamation" build - a single giant C++ file (SQLite is a single giant C file)
Impressively, if you run "pip install duckdb" it Just Works - you can then "import duckdb" and start using it, with an interface that looks very similar to the sqlite3 module that ships with Python.
The key reason this exists is that it's a column store, with vectorized operations across columns - making it ideal for analytical workloads. This blog entry has some benchmarks that illustrate how well it works in that regard: https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html
It's also backed up by some strong computer science. It's by the academic researchers behind MonetDB and includes implementations of a bunch of interesting papers: https://duckdb.org/docs/why_duckdb#standing-on-the-shoulders...
It's a really interesting piece of software, and unlike many other "new databases" it feels like it fills a very genuine gap in my toolbox.
I was hoping from the title that it aims for postgres SQL compatibility, but I can't find it explicitly mentioned in the docs. This really makes me think I really want something like sqlite://memory which completely disregards speed or even persistence. Instead you could say for example "open an in-memory database that behaves like postgres 9" and run your tests against it. With typical fixtures of 10 or so rows, you…
Man, I’ve wanted the “compatible SQL engine with only RAM storage” for testing for YEARS. Closest I got was some shenanigans with MSSQL’s LocalDB.
Earlier quoted context omitted.
Foreign keys enabled by default or by configuration setting would be another big feature missing from SQLite3, but I couldn't find any mention of foreign keys in DuckDB's documentation.
It doesn't look like it provides FK looking at the create table syntax. Just PKs, unique, and check on an expression similar to sqlite.
Earlier quoted context omitted.
Those solutions still have a high overhead. There's acid compliance, serialisation in memory, maintaining indices, and many other layers. Compare it to an ideal testing solution with no initialisation cost and insert being literally: parse the query, add a new entry to a list, done.
But you want to be testing against something that is as close as possible to the deployment environment. So if that means acid, indices etc, then that's what it is.
Strict typing has been the one big feature missing from SQLite, that this presumably brings.
Foreign keys enabled by default or by configuration setting would be another big feature missing from SQLite3, but I couldn't find any mention of foreign keys in DuckDB's documentation.
Earlier quoted context omitted.
It doesn't look like it provides FK looking at the create table syntax. Just PKs, unique, and check on an expression similar to sqlite.
What? SQLite3 absolutely has FKs.
PRAGMA foreign_keys = on;
Earlier quoted context omitted.
Foreign keys enabled by default or by configuration setting would be another big feature missing from SQLite3, but I couldn't find any mention of foreign keys in DuckDB's documentation.
SQLite has foreign keys, but they are disabled by default for backwards-compatibility reasons. https://sqlite.org/foreignkeys.html
Say I want to fix logistic Regression. Can I do that in database? Or have to extract it out as a dataframe? Cna I stream the data?
If you want to do linear regression aggregations with any DB, one thing you can do is store the coifficents and then aggregate them on request. You sacrafice some accuracy for speed.
I spent a while looking at this today. It's really interesting. It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular: - It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database - Each database is a single file on disk - It ships as an "amalgamation" build - a single gian…