Full-Text Search with DuckDB(peterdohertys.website)
peterdohertys.website
Full-Text Search with DuckDB
https://peterdohertys.website/blog-posts/full-text-search-w-duckdb.html
38 comments
This is awesome. Is your code open source? It would be cool to make a textbook for SQL in this format.
Yes, code here: https://github.com/nlothian/llm-sql-benchmark
There is a V2 branch with some minor fixes etc I'm working on.
There is a V2 branch with some minor fixes etc I'm working on.
I'm relatively new to DuckDB (coming from SQLite) and I love it so far. Some parts are magical (described in the previous article by the same author: https://peterdohertys.website/blog-posts/dab-of-duck.html)
You can point DuckDB to almost any data source and boom, you get an SQL table that you can search, sum, or join to any other data. Or you can attach existing databases from completely independent db systems, and query and join them as one, without having to first importing anything.
It feels exhilarating (if you're into that sort of thing!)
You can point DuckDB to almost any data source and boom, you get an SQL table that you can search, sum, or join to any other data. Or you can attach existing databases from completely independent db systems, and query and join them as one, without having to first importing anything.
It feels exhilarating (if you're into that sort of thing!)
> without having to first importing anything
Replying to myself to add this caveat: as I found out the hard way, the limit of such a setup is that query performance depend on the performance and indexing choices of the underlying data stores.
If you have no control over those, and you don't have real time requirements, you're usually better off first importing everything into duckdb (which is usually quite fast) and doing your queries from there.
Replying to myself to add this caveat: as I found out the hard way, the limit of such a setup is that query performance depend on the performance and indexing choices of the underlying data stores.
If you have no control over those, and you don't have real time requirements, you're usually better off first importing everything into duckdb (which is usually quite fast) and doing your queries from there.
We wrapped exactly this into a GUI - attach MySQL and PostgreSQL, files/ s3 as sources, query them together with DuckDB. No imports.
https://streams.dbconvert.com/cross-database-sql
My honeymoon with duckdb wore off pretty quickly when I need to compile it, myself, into a single-file concordance. I understand it's open source, so I'm free to be ignored. But, it's positioning itself as a drop-in replacement for SQLite; a large part of SQLite's appeal is its ergonomics — its single-fileness — letting me deliver a rational object to my users.
EDIT: "drop-in replacement like SQLite", not "for SQLite".
EDIT: "drop-in replacement like SQLite", not "for SQLite".
> it's positioning itself as a drop-in replacement for SQLite
While SQLite is often used for comparison (“SQLite for OLAP”), I’ve never seen DuckDB market itself as a “drop-in” replacement. Where did you see that?
While SQLite is often used for comparison (“SQLite for OLAP”), I’ve never seen DuckDB market itself as a “drop-in” replacement. Where did you see that?
[deleted]
I’ve had some fun experiments with duckdb that involve compiling extensions and the like. Ecosystem is still surprisingly quite young and I end up discovering bugs not made it into official releases yet but fixed on main. It’s compounded by the annoying fact that few extension authors release continuously and you’re usually only seeing a new version drop once every few months.
It’s not THAT hard to do if you’re not afraid of compiling in the C/C++ ecosystem. But people should be aware that a lot of stuff is still really nascent and in active development in the extension ecosystem. I wish more extension authors made at least nightly builds available, that would save a ton of pain and drive a lot more adoption IMO
It’s not THAT hard to do if you’re not afraid of compiling in the C/C++ ecosystem. But people should be aware that a lot of stuff is still really nascent and in active development in the extension ecosystem. I wish more extension authors made at least nightly builds available, that would save a ton of pain and drive a lot more adoption IMO
Sqllite and duckdb serve pretty different niches; duckdb is less embeddable but on the OLAP side it’s by far the best today. I wouldn’t ever see them as competing for the same app, though
DuckDB is amazing. It has completely replaced my need of using Cloudwatch or Loki etc for logs. I simply emit parquet to s3 now and use DuckDB to query locally. No HA compute or paying cloud tax for log search.
https://github.com/amr8t/blobsearch
https://github.com/amr8t/blobsearch
How big is your logs volume?
You pay for transfers from s3 to local right?
I started with s3 and moved to R2. Log volumes are low enough that its covered in the free usage tier.
It seems that DuckDB by default downloads and runs extensions at runtime when you use certain features? This seems unnecessarily risky.
https://duckdb.org/docs/current/extensions/overview#autoload...
I would love to have more detail on this mechanism.
https://duckdb.org/docs/current/extensions/overview#autoload...
I would love to have more detail on this mechanism.
I believe as it states that’s only for the core extensions listed here: https://duckdb.org/docs/current/core_extensions/overview
all are by the DuckDB team except three third-party owners. I’m unfamiliar with Vortex, but presume it’s like LanceDB and MotherDuck with a serious company behind it. and presumably the DuckDB team trusts them not to ship malware in their extension
I think it’s a UX trade off that benefits users with minimal security downsides. and you can configure this behavior. some docs here: https://duckdb.org/docs/current/operations_manual/securing_d...
all are by the DuckDB team except three third-party owners. I’m unfamiliar with Vortex, but presume it’s like LanceDB and MotherDuck with a serious company behind it. and presumably the DuckDB team trusts them not to ship malware in their extension
I think it’s a UX trade off that benefits users with minimal security downsides. and you can configure this behavior. some docs here: https://duckdb.org/docs/current/operations_manual/securing_d...
Thanks for the link. Good to know that they are at least signed by a key. But I really like my software not changing on me at all. I'd rather have all of the modules I need locally and static.
Also creates fun situations like getting on a plane then realizing that your extension isn't available!
It seems that nixpkgs at least fails to run the extension but more by luck than design. I hope they find a way to vendor the extensions locally.
Also creates fun situations like getting on a plane then realizing that your extension isn't available!
It seems that nixpkgs at least fails to run the extension but more by luck than design. I hope they find a way to vendor the extensions locally.
[deleted]
You can disable extensions and download them in advance and load those from file path. This is how I’m pinning extensions for a self hosted version of duckdb I setup at work.
Has anyone used DuckDB (or anything else) to create an open source way to publish a mailbox so that a regular person can browse it and search it?
I'm aware of jmail.world, but they haven't (yet?) published the source code.
I had Claude hack something together recently: https://healdsburg-youcubed-emails.vercel.app/
It works fine for this small set of emails, although the search isn't great, and there was more preprocessing that I would have liked. (I would prefer to be able to point a single binary at a pst or mbox file, and have it magically serve it like this, even if it means I need a VPS to serve it.)
I'm aware of jmail.world, but they haven't (yet?) published the source code.
I had Claude hack something together recently: https://healdsburg-youcubed-emails.vercel.app/
It works fine for this small set of emails, although the search isn't great, and there was more preprocessing that I would have liked. (I would prefer to be able to point a single binary at a pst or mbox file, and have it magically serve it like this, even if it means I need a VPS to serve it.)
Simon Willison wonderful Datasette plus the mbox-to-sqlite extension is exactly what you want.
https://datasette.io/
https://github.com/simonw/mbox-to-sqlite
https://datasette.io/
https://github.com/simonw/mbox-to-sqlite
This is good but the UI is nothing like a regular mail client, so would be difficult to use for non-technical users.
Maybe https://github.com/wesm/msgvault will do what you need?
Thanks.
msgvault seems really good. The tui is fast and FTS5 search works well.
I will definitely use it.
But it doesn't allow me to make a mailbox accessible to a wide audience, because:
- AFAICT there's no web version
- inline images don't show up
msgvault seems really good. The tui is fast and FTS5 search works well.
I will definitely use it.
But it doesn't allow me to make a mailbox accessible to a wide audience, because:
- AFAICT there's no web version
- inline images don't show up
Hmm... on second thoughts, msgvault has a pretty good HTTP API so it shouldn't be too difficult to create a nice web UI.
What’s your use case for this?
Making it easy for members of the public to search and browse email sent by or to government employees.
These emails aren't published by default but email archives are often included in responses to public record requests.
Ideally anyone who receives one of these archives would be able easily inspect it themselves, and also make it available to others.
These emails aren't published by default but email archives are often included in responses to public record requests.
Ideally anyone who receives one of these archives would be able easily inspect it themselves, and also make it available to others.
Here's one: a client of mine has a bunch of SnapLogic pipelines that are configured to send errors via email, and there is no other persistent logging system. This results in tens of thousands of emails that are insanely hard to search and parse for any useful auditing.
For people interested, you can do FTS in DuckDB without using the official module. I copy-pasted the relevant parts and slightly changed it for my need.
https://github.com/nraynaud/sojourner/blob/main/PDS/db_creat...
Example: https://nraynaud.github.io/sojourner/PDS/explore.html
https://github.com/nraynaud/sojourner/blob/main/PDS/db_creat...
Example: https://nraynaud.github.io/sojourner/PDS/explore.html
[deleted]
Anyone tried both duckdb and clickhouse local?
Clickhouse seems less marketed, but seems quite similar.
Clickhouse seems less marketed, but seems quite similar.
I build a whole LLM benchmark system around it that lets you run the whole benchmark in your browser: https://sql-benchmark.nicklothian.com/#sample-queries-and-sq...
Click on a cell and you can run the SQL the LLM generated vs what the solution is: https://sql-benchmark.nicklothian.com/?highlight=ggml-org_ge...
The hardest part is getting people to understand that it is interactive! People expect a document-looking webpage to be static, but we can do so much better!