Trying chDB, an embeddable ClickHouse engine | Hacker News Reader

Trying chDB, an embeddable ClickHouse engine

antonz.org

157 pointsPaulHoule16 comments

I'd argue we probably want to have more embedded full-fledged DBs. SQLite and DuckDB are great choice while your project is small, but when you need to scale up you'd have to migrate to another DB and/or create a lot of workarounds. With prohects like ChDB you have a distributed OLAP engine already, all it takes to migrate is to rsync a bunch of files to the ClickHouse cluster, ATTACH TABLEs, and you're done.

I also really like how seamless is UDF integration. The way UDFs work in ClickHouse is you create a separate binary that can accept the input for the UDF via stdin and produce an output to stdout, so it's quite flexible, but also a pain to set up. This library does all this plumbing seamlessly for you, which is super nice.

That's right! Under the hood we're doing the same thing when a UDF function is created so its still language agnostic, but for python it offers much nicer and needed wrapper - designed for actual users and not for showcase. If this will translate just as well to other chdb bindings (go, rust, node, bun, etc) allowing them to attach native functions, UDF might become a major force for chdb adoption.

The embedded db is cool, but what's the security boundary around UDFs?

I figure running "standard" SQL is safe, because it's just some functions implemented in the database (SPJ). It's not malware (maybe only slow ;-)). But UDFs that could be python or worse risk a binary are more of an open ended danger.

I think in this case as it's an embedded database the security of the DB equals the security of the app, so no issues here.

In stabdalone ClickHouse users can't define UDFs themselves, they need to be installed on the server.

> I also really like how seamless is UDF integration.

users need to setup that binary on servers somehow? I think some wasm UDFs would be much more seamless, like ScyllaDb recently implemented.

The chDB team is great! We worked with them recently to add chDB support to JupyterLab via a %%sql magic: https://jupysql.ploomber.io/en/latest/integrations/chdb.html

Would love to see some info in docs re: concurrent access. Single writer multi reader? Multi writer? Has anyone tried this out and can comment on Python usage with threading?

That's pretty cool, I wonder how it compares to DuckDB

In terms of SQL syntax supported, you can compare them here[1]. As for performance, it probably comes down to "clickhouse-local vs. DuckDB", and there was an interesting post about this some time ago[2].

[1]: https://codapi.org/sql

[2]: https://www.vantage.sh/blog/clickhouse-local-vs-duckdb

    @chdb_udf()
    def split_part(s, sep, idx):
        idx = int(idx)-1
        return s.split(sep)[idx]
    
    second = chdb.query("select split_part('a;b;c', ';', 2)")

Is there some reason that registration of a UDF deserves to be global?

Could somebody help me understand the usecase for this? Is it mainly for people who prefer to analyze data with SQL rather than something like Pandas?

A JDBC driver would be nice.

Reply on news.ycombinator.com