9 ms·

ChDB: Embedded OLAP SQL Engine Powered by ClickHouse

111 points by nalgeon 3y ago

adulion 3y ago
why would you use this over duckdb?
- andrenotgiant 3y ago
  I don't have an opinion, but this article gives a fairly unbiased comparison of DuckDB vs Clickhouse-local, which I imagine exhibits similar performance characteristics as ChDB, just without the embedded part: https://www.vantage.sh/blog/clickhouse-local-vs-duckdb https://www.vantage.sh/blog/clickhouse-local-vs-duckdb
- richraposa 3y ago
  I suppose if you had data in a format that DuckDb doesn't work with, like Protobuf, Avro, ORC, Arrow, etc. ClickHouse reads and writes data in over 70 formats
- srikanthccv 3y ago
  One reason is that it supports a wide range of formats.
- monstrado 3y ago
  One reason would be if you're already fluent in ClickHouse's SQL dialect. Although they maintain great standard SQL compatibility, they also have a great deal of special functions/aggregates/etc that are ClickHouse specific. Other reasons include their wide range of input formats, special table functions (e.g. query a URL).
- ryadh 3y ago
  Here is an example, DB Pilot recently switched from duckdb to ChDB, mostly for faster queries and broader data formats support: https://dbpilot.io/changelog#embedded-clickhouse-and-standalone-duckdb-support-2023-08-31 https://dbpilot.io/changelog#embedded-clickhouse-and-standal... Disclaimer: I work at ClickHouse
- nasretdinov 3y ago
  I think it makes sense if you are just considering whether or not to use ClickHouse, it's a very easy place to start. If you then outgrow embedded you won't have to move to another database afterwards, you can probably just attach the tables to a "real" ClickHouse instance and continue using it without having to do lengthy data migration.
- qxip 3y ago
  Disclaimer: I am a chdb maintainer! duckdb is currently thinner and has lots of active contributors and mature integrations, while chdb is still in its early stages BUT if you already love ClickHouse (like we do) chdb is a great choice as it inherits all the ClickHouse stability, performance and more importantly, all the 70+ supported formats for the embedded use case without any of the server/client requirements, making it perfect for fast in-process and serverless OLAP executions. Note chdb is based on ClickHouse codebase but completely community powered so there's no feud with DuckDB (I'm a quackhead, too!) which actually offers lots of great inspiration and many integration opportunities with ClickHouse/chdb for combined compute and processing of datasets. I personally love both and use them together all the time in my colab "OLAPps"
  - adulion 3y ago
    does chdb have support for recursive CTES? It and the ability to do `from 'https://example.com/.csv https://example.com/.csv` is why i love duckdb
    - richraposa 3y ago
      ClickHouse 10 years before DuckDB existed: SELECT * FROM url('https://example.com/*.csv https://example.com/*.csv')
      - adulion 3y ago
        not as simple as from 'a.csv' and 10 years without recursive CTES?
  - apavlo 3y ago
    Where can I find an SVG version of this logo? https://github.com/chdb-io/chdb/raw/main/docs/_static/snake-chdb-dark.png https://github.com/chdb-io/chdb/raw/main/docs/_static/snake-...
    - qxip 3y ago
      Here you go: https://github.com/chdb-io/chdb/blob/92024bde839a8e9a42f68aefa41135a9e41ab668/docs/_static/chdb.svg https://github.com/chdb-io/chdb/blob/92024bde839a8e9a42f68ae...
edublancas 3y ago
chDB is a great project! We recently worked with the chDB team to add support for Jupyter notebooks: https://jupysql.ploomber.io/en/latest/integrations/chdb.html https://jupysql.ploomber.io/en/latest/integrations/chdb.html
simonw 3y ago
I absolutely love this trend of "pip install x" as a distribution mechanism for this kind of thing. People badmouth Python packaging a lot, but once you've learned how to use a virtual environment (admittedly a steeper learning curve than I'd like it to be) having binary wheels for so much of this kind of stuff is a huge win. See also Playwright, DuckDB, ziglang... I wrote a bit about those here https://simonwillison.net/2022/May/23/bundling-binary-tools-in-python-wheels/ https://simonwillison.net/2022/May/23/bundling-binary-tools-...
- pachico 3y ago
  I'm tempted to say that the very same reason why you need different environments is precisely because the package system is not healthy. And the fact that it's not easy to use doesn't really make things better.
  - simonw 3y ago
    What packaging systems are there that don't encourage different environments for different projects? I think Python's biggest weakness on this front is mainly that virtual environments came along too late - if they'd been designed into how pip etc worked from the beginning we would be in much better shape.
    - maxmcd 3y ago
      Any os-level package manager. Also: "npm -g", "pip install", "cargo install", etc.. Lots of programming languages or build systems will look for dependencies in globally-known locations. Other than cargo, python's virtualenv, node_modules, and some other modern languages/systems I would imagine most are not project-scoped. Go had a single global dependency tree until go.mod, etc...
      - SahAssar 3y ago
        "npm" is a environment/scoped package manager. Saying "npm -g" when -g is a flag tell it to run in a non-default way (globally) is a bad argument.
bluehat974 3y ago
Do they provide wasm version to use in browser? (like duckdb-wasm)
- qxip 3y ago
  no chdb wasm versions are planned or even possible at the moment. This ClickHouse thread is still relevant: https://github.com/ClickHouse/ClickHouse/pull/37871 https://github.com/ClickHouse/ClickHouse/pull/37871
- tempest_ 3y ago
  What is the use case for a browser based OLAP database ?
  - texodus 3y ago
    Something like https://github.com/finos/perspective https://github.com/finos/perspective ? We use an OLAP(-y) WASM engine to provide query-ability to our data visualization tool, and doing the calculations in the browser is cheaper and simpler than a server-side database for datasets that fit in browser memory.
deleted 3y ago
[deleted]
wodenokoto 3y ago
Is there a command line tool as well, or do I need to access it via Python?
- ryadh 3y ago
  ChDB is the in-process version for Python. You can try clickhouse-local if you want a CLI experience, or clickhouse-client against a clickhouse-server for the server experience https://clickhouse.com/docs/en/operations/utilities/clickhouse-local https://clickhouse.com/docs/en/operations/utilities/clickhou... https://clickhouse.com/docs/en/getting-started/quick-start https://clickhouse.com/docs/en/getting-started/quick-start
- qxip 3y ago
  You can find a basic REPL/CLI for chdb here: https://github.com/chdb-io/chdb-cli https://github.com/chdb-io/chdb-cli
auxten 3y ago
Author of chDB here. For those who are interested, there is a blog post that explains how we created chDB. https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-engine-on-a-bicycle https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-....

111 points by nalgeon 3y ago

adulion 3y ago

why would you use this over duckdb?

andrenotgiant 3y ago
I don't have an opinion, but this article gives a fairly unbiased comparison of DuckDB vs Clickhouse-local, which I imagine exhibits similar performance characteristics as ChDB, just without the embedded part: https://www.vantage.sh/blog/clickhouse-local-vs-duckdb https://www.vantage.sh/blog/clickhouse-local-vs-duckdb
richraposa 3y ago
I suppose if you had data in a format that DuckDb doesn't work with, like Protobuf, Avro, ORC, Arrow, etc. ClickHouse reads and writes data in over 70 formats
srikanthccv 3y ago
One reason is that it supports a wide range of formats.
monstrado 3y ago
One reason would be if you're already fluent in ClickHouse's SQL dialect. Although they maintain great standard SQL compatibility, they also have a great deal of special functions/aggregates/etc that are ClickHouse specific. Other reasons include their wide range of input formats, special table functions (e.g. query a URL).
ryadh 3y ago
Here is an example, DB Pilot recently switched from duckdb to ChDB, mostly for faster queries and broader data formats support: https://dbpilot.io/changelog#embedded-clickhouse-and-standalone-duckdb-support-2023-08-31 https://dbpilot.io/changelog#embedded-clickhouse-and-standal... Disclaimer: I work at ClickHouse
nasretdinov 3y ago
I think it makes sense if you are just considering whether or not to use ClickHouse, it's a very easy place to start. If you then outgrow embedded you won't have to move to another database afterwards, you can probably just attach the tables to a "real" ClickHouse instance and continue using it without having to do lengthy data migration.
qxip 3y ago
Disclaimer: I am a chdb maintainer! duckdb is currently thinner and has lots of active contributors and mature integrations, while chdb is still in its early stages BUT if you already love ClickHouse (like we do) chdb is a great choice as it inherits all the ClickHouse stability, performance and more importantly, all the 70+ supported formats for the embedded use case without any of the server/client requirements, making it perfect for fast in-process and serverless OLAP executions. Note chdb is based on ClickHouse codebase but completely community powered so there's no feud with DuckDB (I'm a quackhead, too!) which actually offers lots of great inspiration and many integration opportunities with ClickHouse/chdb for combined compute and processing of datasets. I personally love both and use them together all the time in my colab "OLAPps"
- adulion 3y ago
  does chdb have support for recursive CTES? It and the ability to do `from 'https://example.com/.csv https://example.com/.csv` is why i love duckdb
  - richraposa 3y ago
    ClickHouse 10 years before DuckDB existed: SELECT * FROM url('https://example.com/*.csv https://example.com/*.csv')
    - adulion 3y ago
      not as simple as from 'a.csv' and 10 years without recursive CTES?
- apavlo 3y ago
  Where can I find an SVG version of this logo? https://github.com/chdb-io/chdb/raw/main/docs/_static/snake-chdb-dark.png https://github.com/chdb-io/chdb/raw/main/docs/_static/snake-...
  - qxip 3y ago
    Here you go: https://github.com/chdb-io/chdb/blob/92024bde839a8e9a42f68aefa41135a9e41ab668/docs/_static/chdb.svg https://github.com/chdb-io/chdb/blob/92024bde839a8e9a42f68ae...

edublancas 3y ago

chDB is a great project! We recently worked with the chDB team to add support for Jupyter notebooks: https://jupysql.ploomber.io/en/latest/integrations/chdb.html https://jupysql.ploomber.io/en/latest/integrations/chdb.html

simonw 3y ago

I absolutely love this trend of "pip install x" as a distribution mechanism for this kind of thing. People badmouth Python packaging a lot, but once you've learned how to use a virtual environment (admittedly a steeper learning curve than I'd like it to be) having binary wheels for so much of this kind of stuff is a huge win. See also Playwright, DuckDB, ziglang... I wrote a bit about those here https://simonwillison.net/2022/May/23/bundling-binary-tools-in-python-wheels/ https://simonwillison.net/2022/May/23/bundling-binary-tools-...

pachico 3y ago
I'm tempted to say that the very same reason why you need different environments is precisely because the package system is not healthy. And the fact that it's not easy to use doesn't really make things better.
- simonw 3y ago
  What packaging systems are there that don't encourage different environments for different projects? I think Python's biggest weakness on this front is mainly that virtual environments came along too late - if they'd been designed into how pip etc worked from the beginning we would be in much better shape.
  - maxmcd 3y ago
    Any os-level package manager. Also: "npm -g", "pip install", "cargo install", etc.. Lots of programming languages or build systems will look for dependencies in globally-known locations. Other than cargo, python's virtualenv, node_modules, and some other modern languages/systems I would imagine most are not project-scoped. Go had a single global dependency tree until go.mod, etc...
    - SahAssar 3y ago
      "npm" is a environment/scoped package manager. Saying "npm -g" when -g is a flag tell it to run in a non-default way (globally) is a bad argument.

bluehat974 3y ago

Do they provide wasm version to use in browser? (like duckdb-wasm)

qxip 3y ago
no chdb wasm versions are planned or even possible at the moment. This ClickHouse thread is still relevant: https://github.com/ClickHouse/ClickHouse/pull/37871 https://github.com/ClickHouse/ClickHouse/pull/37871
tempest_ 3y ago
What is the use case for a browser based OLAP database ?
- texodus 3y ago
  Something like https://github.com/finos/perspective https://github.com/finos/perspective ? We use an OLAP(-y) WASM engine to provide query-ability to our data visualization tool, and doing the calculations in the browser is cheaper and simpler than a server-side database for datasets that fit in browser memory.

deleted 3y ago

[deleted]

wodenokoto 3y ago

Is there a command line tool as well, or do I need to access it via Python?

ryadh 3y ago
ChDB is the in-process version for Python. You can try clickhouse-local if you want a CLI experience, or clickhouse-client against a clickhouse-server for the server experience https://clickhouse.com/docs/en/operations/utilities/clickhouse-local https://clickhouse.com/docs/en/operations/utilities/clickhou... https://clickhouse.com/docs/en/getting-started/quick-start https://clickhouse.com/docs/en/getting-started/quick-start
qxip 3y ago
You can find a basic REPL/CLI for chdb here: https://github.com/chdb-io/chdb-cli https://github.com/chdb-io/chdb-cli

auxten 3y ago

Author of chDB here. For those who are interested, there is a blog post that explains how we created chDB. https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-engine-on-a-bicycle https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-....