Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Clickhouse Local

clickhouse.tech

Post reply on HN
Print view View original

51–60 of 77 posts

Re: Clickhouse Local

#51

Post by dikei »

deepsun wrote: post #39
amyjess wrote: post #35

Earlier quoted context omitted.

It's legitimately fast. My company migrated our time-series data from InfluxDB to ClickHouse last year (I personally led this, in fact), and the performance difference is night and day. While I liked a lot of what Influx could do, it was also nonstandard in bizarre ways (Clickhouse behaves more like a subset of SQL), sometimes shockingly immature, and despite appearing fast when we first started using it, so slow tha…

Clickhouse is not really time-series database, it's more general analytical DB (e.g. also can handle strings, logs, user IPs). But if you have a lot of time-series metrics (only numbers), you might be better with specialized time-series databases like Prometheus + VictoriaMetrics with Grafana for visualizing it.

Prometheus is not really a database, because the only operation allowed is query, you can't insert/delete data manually. For many use cases, that's not acceptable.

Re: Clickhouse Local

#52

Post by bsg75 »

georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

Its somewhat unique in the open source category.

As a column store engine that supports (hybrid) SQL, manages its own storage and clustering, and has only one external dependency (Zookeeper), I believe its main competitor is Vertica which can be very expensive. I assume Oracle, IBM, and MS have column stores as well and for a cost.

Greenplum is a Postgres fork, and same scale requires much more hardware. Citus is row based, and therefore will lag in scan time for many OLAP query patterns. Presto, Hive, Spark, all of the "post-hadoop" options may scale larger, but will also lag in scan time, and have significant external dependencies - mainly storage.

Clickhouse is easy to install, configure a cluster, load and query. It does have limitations, but currently all horizontally scaled database platforms do.

Re: Clickhouse Local

#53

Post by jfim »

edmundsauto wrote: post #11
georgewfraser wrote: post #8

Earlier quoted context omitted.

Snowflake, Redshift, BigQuery, Databricks, Presto.

Of those, it looks like only Presto is open source and/or free. So maybe it's a presto versus clickhouse comparison, which explains why so many choose clickhouse (it's one of only 2 options in its class).

Presto is mostly an engine that runs on top of other databases, although it does have its own query execution engine.

The basic idea behind Presto is that it federates other databases, and supports doing joins across them. From what I understand, the problem that it solved at Facebook is bridging the gap between different teams; if a team has MySQL and another has files stored on HDFS, it doesn't really matter because all you do is query Presto and it'll query both under the covers. The alternative is setting up data pipelines, and dealing with the ongoing issues of maintaining those data pipelines.

Re: Clickhouse Local

#54

Post by wochiquan »

atombender wrote: post #18
georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

Because it's one of the few mature, fast, scalable analytics-oriented databases that is also completely open source. The closest open source thing that matches its feature set is Presto, but that one is quite different. Apache Druid is supposed to be very mature, but also very difficult to set up and manage. I've not used it myself. There's also Vespa, but I don't know how well it performs with large numbers of colum…

I know a few companies that use Imply Cloud to set up and manage Druid. I linked it if you want to check it out. https://imply.io/product/imply-cloud

Re: Clickhouse Local

#55

Post by wochiquan »

oreoftw wrote: post #7
georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

Could you name those good analytical databases? I'd love to learn more.

Apache Druid: https://druid.apache.org/docs/latest/design/index.html

Re: Clickhouse Local

#56

Post by jfim »

dunkelheit wrote: post #48
georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

> There are several good analytical databases that will give you the same scan performance The notion that you will get approximately the same query performance with all column stores is false. There can easily be an order of magnitude difference depending on the implementation. Take GROUP BY as a paradigmatic example of what OLAP stores do. Of course the way to implement GROUP BY is with a hash table but little tric…

> Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation.

Actually, for low cardinality columns, _not_ using a hash table will speed up things.

For example, a dictionary-encoded column for states might have a few values in its dictionary (1 => CA, 2 => FL, 3 => NY) and the data looks like an array of numbers (eg. [1, 1, 1, 2, 1, 3, 3, 3, 1, 3, 2]). The fastest way to aggregate is to actually use an array, as the dictionary index conveniently maps to an array index.

Then, when it comes to merging several of those arrays, they're turned into hash tables.

Combine enough of those optimizations and proper data layouts, and you end up with several orders of magnitude of performance differences between engines.

Re: Clickhouse Local

#57

Post by quantumwoke »

georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

It's the first time I've seen it on HN, and a cursory search shows only a few submissions with significant discussion. Nonetheless, as other commenters have said the SQL-based query language and low external dependencies made it easy for me to integrate it into my workflow.

Re: Clickhouse Local

#58

Post by golover721 »

georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

Clickhouse is extremely optimized for performing analytics on time series style data. Things like clickstream data (hence the name). Performing queries to do funnel level analysis for example, are extremely fast compared to other analytics databases I have tried. As others have mentioned it is often compared to Apache Druid in its intent and feature set.

Re: Clickhouse Local

#59

Post by hodgesrm »

dunkelheit wrote: post #48
georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

> There are several good analytical databases that will give you the same scan performance The notion that you will get approximately the same query performance with all column stores is false. There can easily be an order of magnitude difference depending on the implementation. Take GROUP BY as a paradigmatic example of what OLAP stores do. Of course the way to implement GROUP BY is with a hash table but little tric…

In addition to excellent raw scan rates for reasons given above ClickHouse has materialized views, which can re-order and/or preaggregate data. This can speed up query response by 3 orders of magnitude over queries on the source data. See https://www.altinity.com/blog/clickhouse-materialized-views-... + ClickHouse docs for an intro.

As the parent says, try it yourself on your own data. That's all that counts.

Disclaimer: I wrote the blog article and we sell support for ClickHouse.

Re: Clickhouse Local

#60

Post by barrkel »

georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

Low latency query execution and it scales down well to small datasets, are my reasons for really liking it. Top throughout when scanning TB+ of data on multiple nodes isn't my use case; it's answering a query in web request response times over a few hundred million rows of data.

The low latency to query execution is really nice.

Post reply on HN