The ClickHouse Community(clickhouse.tech)
clickhouse.tech
The ClickHouse Community
https://clickhouse.tech/blog/en/2020/the-clickhouse-community/
65 comments
Hi, I ran a Clickhouse server in production at non-trivial scale for storage and query of application logs (semi-structured JSON documents).
In our case we were consuming from a kafka topic which was the source of truth, so we opted not to use Clickhouse's built-in write replication and instead manually sharded and wrote to each replica independently (this meant replicas weren't always in perfect sync for brand-new data, but in practice this was acceptable).
The distributed query side still worked perfectly despite this weird setup, even when we were doing weird things like re-sharding or taking some replicas down for one reason or another.
I can't speak to backups as, once again, we were considering the kafka stream the source of truth and not the clickhouse datastore. We archived the data directly out of kafka, in a format more suitable to long term archival instead of live query.
My experience with Clickhouse was that the core primitives were rock solid, but you were often on shaky ground when using more niche features - we encountered crashes when trying to use bloom filters for text search, for example.
The performance of Clickhouse absolutely blew me away. It could read, filter and aggregate results as fast as the underlying disks could serve it data. I'll point out though that while Clickhouse is very good at what it does, it puts a lot of onus on the person designing the schema and writing the query to make sure it does it in the right way. In our case this worked well because we only had a small number of "shapes" of query we needed to serve, and we had a CLI tool that translated the user's query written in a simple DSL (really just a key-matches-value filter expression) into the SQL that would result in an efficient query on clickhouse's end. But for more flexibility-demanding workloads this could be a major issue as you need to know the system pretty well to write good queries.
In our case we were consuming from a kafka topic which was the source of truth, so we opted not to use Clickhouse's built-in write replication and instead manually sharded and wrote to each replica independently (this meant replicas weren't always in perfect sync for brand-new data, but in practice this was acceptable).
The distributed query side still worked perfectly despite this weird setup, even when we were doing weird things like re-sharding or taking some replicas down for one reason or another.
I can't speak to backups as, once again, we were considering the kafka stream the source of truth and not the clickhouse datastore. We archived the data directly out of kafka, in a format more suitable to long term archival instead of live query.
My experience with Clickhouse was that the core primitives were rock solid, but you were often on shaky ground when using more niche features - we encountered crashes when trying to use bloom filters for text search, for example.
The performance of Clickhouse absolutely blew me away. It could read, filter and aggregate results as fast as the underlying disks could serve it data. I'll point out though that while Clickhouse is very good at what it does, it puts a lot of onus on the person designing the schema and writing the query to make sure it does it in the right way. In our case this worked well because we only had a small number of "shapes" of query we needed to serve, and we had a CLI tool that translated the user's query written in a simple DSL (really just a key-matches-value filter expression) into the SQL that would result in an efficient query on clickhouse's end. But for more flexibility-demanding workloads this could be a major issue as you need to know the system pretty well to write good queries.
Similar experience to vulkoingim: steep learning curve but quite stable once deployed properly.
Schema management in zookeeper has been the biggest pain point for us. Occasionally individual clickhouse shards will get out of sync during a schema update, which can be hard to diagnose.
We use a heavily modified version of clickhouse-backup[1], which works well for us.
As for hands-off replica reboot: you must have an automated process to reapply the same schema which exists in zookeeper, otherwise it won't resync. If the local schema gets out of sync with that in zookeeper, then you'll have issues again.
I expect a lot of these ergonomics issues will be fixed over time. It's already much easier to use than it was 3 years ago, and even if progress on usability and reducing the learning curve is slow the database performance makes it worth it.
[1] https://github.com/AlexAkulov/clickhouse-backup
Schema management in zookeeper has been the biggest pain point for us. Occasionally individual clickhouse shards will get out of sync during a schema update, which can be hard to diagnose.
We use a heavily modified version of clickhouse-backup[1], which works well for us.
As for hands-off replica reboot: you must have an automated process to reapply the same schema which exists in zookeeper, otherwise it won't resync. If the local schema gets out of sync with that in zookeeper, then you'll have issues again.
I expect a lot of these ergonomics issues will be fixed over time. It's already much easier to use than it was 3 years ago, and even if progress on usability and reducing the learning curve is slow the database performance makes it worth it.
[1] https://github.com/AlexAkulov/clickhouse-backup
I hold out perpetual hope that ClickHouse will move to a Raft or similar based consensus model and drop the external dependence on ZooKeeper on day.
This is happening: https://github.com/ClickHouse/ClickHouse/issues/15090
They're using NuRaft from ebay to drop zookeeper. As far as I remember, it's actually already merged and available to test. Performance is ~3x slower in terms of write as they're coordinating everything themselves, but it should be _much_ easier to maintain.
I'm looking forward to this, too, and want to see more in terms of docs / support here :)
They're using NuRaft from ebay to drop zookeeper. As far as I remember, it's actually already merged and available to test. Performance is ~3x slower in terms of write as they're coordinating everything themselves, but it should be _much_ easier to maintain.
I'm looking forward to this, too, and want to see more in terms of docs / support here :)
Woah! That’s really exciting, I’d definitely tolerate some write slow-down if it means not having to run ZK.
It's going to get faster. That's why it's still experimental. ;)
people are asking for consul support for many months... it seems they refactored the dependency on zookeeper to make it easier to use something else
In my experience there is a bit of a learning curve until you understand how each of the parts fit together and how to optimize for your use case. ZooKeeper indeed was one of those pain points, but once you get it stable there is practically no need to touch it.
I found the tips in the docs on optimizing ZK really helpful [1].
Anoter mistake we did early on with ZooKeeper, that caused us a lot of pain was running it with network attached disks (we ran ClickHouse+ZK in the cloud). Once we switched to local disks, 99% problems disappeared.
[1] https://clickhouse.tech/docs/en/operations/tips/#zookeeper
I found the tips in the docs on optimizing ZK really helpful [1].
Anoter mistake we did early on with ZooKeeper, that caused us a lot of pain was running it with network attached disks (we ran ClickHouse+ZK in the cloud). Once we switched to local disks, 99% problems disappeared.
[1] https://clickhouse.tech/docs/en/operations/tips/#zookeeper
What you say is true, clustering clickhouse is hard. But we are happy in a very different way. What used to be 6+6 cluster of graphite is now served by a single clickhouse instance handling millions of unique metric datapoints everyday, that we haven't even had the need to cluster it yet. It's an order of magnitude difference in performance & cost. Scale, but just in a different way, not just horizontal but holistically.
Maybe when we need to cluster, we'll face the same issues.
Maybe when we need to cluster, we'll face the same issues.
I'm quite sure Cloudflare uses ClickHouse for their web analytics stuff (see https://blog.cloudflare.com/http-analytics-for-6m-requests-p...).
How does the setup and operational ease compared to druid. Afaik, druid also uses zookeeper. Is there any reason someone would choose druid over clickhouse?
I must say that I relied on the community since we adopted ClickHouse in production almost 2 years ago.
It was an essential part of the success of our project.
I'm grateful for their work and the passion they put assisting the community.
Well done, everyone!
I'm grateful for their work and the passion they put assisting the community.
Well done, everyone!
Sorry if this is obvious, but... What is ClickHouse?
Officially, it's a column-oriented database. This means that, internally, it stores columns together rather than rows together. In practice, it means that it's optimized for calculating analytics over large datasets.
I've found, from personal experience, that it makes a good replacement for time-series databases, even though it's technically not a time-series database. My employer migrated our KPIs and other metrics from InfluxDB to ClickHouse a couple of years ago, and the drastic improvements in performance were well worth the time it took to migrate our data. It also helped that ClickHouse uses a subset of SQL, unlike InfluxDB which uses a superficially SQL-like but practically very different proprietary language.
I've found, from personal experience, that it makes a good replacement for time-series databases, even though it's technically not a time-series database. My employer migrated our KPIs and other metrics from InfluxDB to ClickHouse a couple of years ago, and the drastic improvements in performance were well worth the time it took to migrate our data. It also helped that ClickHouse uses a subset of SQL, unlike InfluxDB which uses a superficially SQL-like but practically very different proprietary language.
You may be interested in https://github.com/influxdata/influxdb_iox
Column store db, using the DataFusion SQL engine. Persistence using parquet files directly on object stores (e.g. S3)
Still under development
Column store db, using the DataFusion SQL engine. Persistence using parquet files directly on object stores (e.g. S3)
Still under development
Every high-volume Influx/Grafana implementation I’ve used has been a disaster. I’m now at a place that uses ClickHouse and I can now see the utility of Grafana
Thanks! Very informative
from what I've read, it is not a column-oriented db. It's more like druid or pinot.
Clickhouse/Druid/Pinot are all columnstores/column-oriented databases. Clickhouse is a relational engine while Druid/Pinot are a different (and older) design using heavy indexing and pre-aggregation. All of them store table data as per-column segments though which is a defining feature leading to high compression and I/O performance.
There's also the badly named wide-column database type like Cassandra, but this is really just advanced or nested key/value rather than what people would consider "columns".
There's also the badly named wide-column database type like Cassandra, but this is really just advanced or nested key/value rather than what people would consider "columns".
In a nutshell: like MySQL but for analytic applications
omg the comments section are directly scraped from this very page.
<script>alert(1+1);</script>
<span style="font-size: 300px;"><marquee>hello</marquee></span>
<span style="font-size: 300px;"><marquee>hello</marquee></span>
This is how you do the script thing:
<img src="kitten.jpg">:^)
Edit: meh, stupid CSP…
<img src="kitten.jpg">:^)
Edit: meh, stupid CSP…
Seems like their Content Security Policy should block those from executing as "unsafe-inline" is not allowed.
[deleted]
<span style="font-size: 300px;"><marquee>fail</marquee></span>
Huh, there is no alert, but the script node is there in the DOM. I'm confused.
Hah. Someone got it working, I see.
HN: be careful with doing things like this, technically speaking this could be considered illegal.
technically speaking the moon could be made of cheese
I'm still holding a faint hope for that
I'd like to see a xkcd -"What if the moon was made of chees?", and with sections for different types of cheese.
I guess rot would be no problem, but the surface would probably liquefy during the day, and freeze at night, turning into a cheese sea (or maybe an under-crust sea beneath frozen-cheese ice crust?) affected by earth-induced tides, and possibly internal tidal heating which liquefies the inside of the moon too.
I guess rot would be no problem, but the surface would probably liquefy during the day, and freeze at night, turning into a cheese sea (or maybe an under-crust sea beneath frozen-cheese ice crust?) affected by earth-induced tides, and possibly internal tidal heating which liquefies the inside of the moon too.
How would this be illegal? I get why something like "Bobby Tables" would be, but there is no harm or bad intention visible here.
The lack of sanitizers has been illegal for more than a year in most countries. /s
Technically copying comments could be considered illegal since you can argue no explicit or implied copyright license was given for this usage.
<!--<div style="position: fixed; top: 0; left: 0; right: 0; bottom: 0; background: red;">Fix your website</div>-->
(Yep it worked)
(Yep it worked)
These comments gave me an extreme nostalgic feeling. It's like it's early 2000 again when everyone that could operate a keyboard would try to make his own dynamic site with PHP (read as: copy&paste some "guestbook" code from a tutorial). Oh, the memories and fun we had with those ...
Hehe, my first open source “project” was a comment box written in PHP, circa 2004 at the age of 13… It used a single txt file for storing the comments, so I’m afraid it wasn’t webscale™, but I didn’t know better at the time :]
Don't sell yourself short, it was probably more efficient than the wobbling tower of npm packages and ajax requests most comment systems are made of today.
It seems they are fetching the comments from the Hacker News Algolia index. Curiously they seem to un-sanitize the comment content before inserting it to the page DOM as one of the sibling comments proves.
The Algolia index contains the comments encoded as HTML entities, so they should be safe to insert as is, but seems like the page is first converting it back to HTML, which causes the script and span tags to render as HTML instead of text like here on HN.
The Algolia index contains the comments encoded as HTML entities, so they should be safe to insert as is, but seems like the page is first converting it back to HTML, which causes the script and span tags to render as HTML instead of text like here on HN.
Algolia has some rough edges. I've tried to work with them on a couple of occasions but it's a bit of a harder problem than meets the eye because apparently Algolia gets a feed directly from HN with the original comment contents as they were typed into the text box rather than the rendered HTML version of the comment, which ironically would probably be easier to deal with. I have a particular comment discussing HTML injection that I've worked with them twice to stop rendering as actual script tags, which means, yes, it actually regressed once.
[deleted]
If you want to seem some code where it is used I ran across it when looking at the Plausible Analytics repo:
https://github.com/plausible/analytics/search?q=clickhouse
They use it to store analytics data.
https://github.com/plausible/analytics/search?q=clickhouse
They use it to store analytics data.
If someone from the clickhouse team is reading this, the slack link[1] on your community page seems broken [2]
[1] https://clickhousedb.slack.com/join/shared_invite/zt-nwwakmk... [2] https://clickhouse.tech/#community
[1] https://clickhousedb.slack.com/join/shared_invite/zt-nwwakmk... [2] https://clickhouse.tech/#community
What is the difference between ClickHouse and Apache Parquet or Google Capacitor?
ClickHouse is a column-based database, also called an OLAP/analytic database.
Parquet is a column based file storage format.
Edit: capacitor also appears to be an internal storage format google uses inside of Big Query.
Parquet is a column based file storage format.
Edit: capacitor also appears to be an internal storage format google uses inside of Big Query.
https://t.me/clickhouse_en clickhouse en '__')
Is it faster for querying than using Presto on top of Hive? The page comparing ClickHouse with other analytics solutions doesn’t list Presto, which is very popular these days.
Waay faster. In my experience it has been the fastest OLAP db I've ever used, and by a wide margin. I don't think any other system gets close for the price/performance ratio you get with ClickHouse.
In this blog [1] you can find a very nice comparison of different dbs on the same dataset/same queries
The fastest Presto benchmark there [2] vs ClickHouse on a single node core i5 [3] vs ClickHouse in cluster mode [4]
[1] https://tech.marksblogg.com/benchmarks.html
[2] https://tech.marksblogg.com/billion-nyc-taxi-rides-spark-2-4...
[3] https://tech.marksblogg.com/billion-nyc-taxi-clickhouse.html NOTE: not really comparable, since it's on single machine, but I think it really shows what ClickHouse is capable of
[4] https://tech.marksblogg.com/billion-nyc-taxi-rides-clickhous...
In this blog [1] you can find a very nice comparison of different dbs on the same dataset/same queries
The fastest Presto benchmark there [2] vs ClickHouse on a single node core i5 [3] vs ClickHouse in cluster mode [4]
[1] https://tech.marksblogg.com/benchmarks.html
[2] https://tech.marksblogg.com/billion-nyc-taxi-rides-spark-2-4...
[3] https://tech.marksblogg.com/billion-nyc-taxi-clickhouse.html NOTE: not really comparable, since it's on single machine, but I think it really shows what ClickHouse is capable of
[4] https://tech.marksblogg.com/billion-nyc-taxi-rides-clickhous...
Thanks for these links. Unrelated to ClickHouse, it was interesting to read about BrytlytDB--I'd never heard of it before. Essentially a PostgreSQL DB that uses GPUs.
Thank you for the links! Do you happen to know where Facebook’s Scuba, Google’s Procella, Google’s Dremel on Capacitor and Snowflake come on that list of benchmarks?
Scuba appears to be an in-memory-only database, so it’s possibly faster (not seen benchmarks nor used it) but at the cost of persistence.
Is Procella even usable by anyone not-google?
Haven’t used dremel + capacitor, but have used Snowflake and found it lacking. Promises a lot, performance was just kind of alright, support was useless, sales team was aggressive to the point of harassment, and it was eye-wateringly expensive on top of all that. Some people love it, I will never go near it again, it’s advantages just aren’t sufficient to warrant choosing it over ClickHouse or even Redshift.
Is Procella even usable by anyone not-google?
Haven’t used dremel + capacitor, but have used Snowflake and found it lacking. Promises a lot, performance was just kind of alright, support was useless, sales team was aggressive to the point of harassment, and it was eye-wateringly expensive on top of all that. Some people love it, I will never go near it again, it’s advantages just aren’t sufficient to warrant choosing it over ClickHouse or even Redshift.
Kdb+ or it’s latest incarnation k9/shakti are likely faster, but they cost a few arms and a few legs. Once you factor price into the equation, click house is infinitely better :)
Absolutely. That's why I said price/performance might be the best. Also MapD (nowadays OmniSci) is way faster.... but it would cost you a couple of bodies probably to run on GPUs :)
Last time I looked it was significantly faster. Last stats I saw had Presto in the 1-to-several-seconds range and CH in the 10/100 ms range.
You should look at switching from hive to Iceberg. You'll see a 4-10x speed up and it'll start to give Clickhouse a run for it's money.
<a target="_blank" href="//www.youtube.com/watch?v=dQw4w9WgXcQ" style="position: absolute; top: 0; bottom: 0; left: 0; right: 0"></a> <!-- I had to do this, sorry -->
Keeping zookeeper happy seems to be a huge pain. Getting consistent backups seems to be difficult for some reason, and there is some problem with multiple replicas. As in, if you have 3 replicas and one dies, when it comes back up it will be out of sync and will refuse to resync itself automatically. So someone has to go and prod it to make it work again.
In practice, none of this has ever been an issue and only comes up when he does his "chaos engineering" tests, but it makes him very nervous.
On the otherhand, it's been orders of magnitude faster than using AWS Redshift/Apache Drill/PostgreSQL whenever we try and benchmark them on comparable hardware, so we stick with it.