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

ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

altinity.com

Post reply on HN
Print view View original

31–40 of 91 posts

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#31

Post by atombender »

Is ClickHouse good for event data when you want to do rollups? For example, say all my events are of the form:

{event: "viewedArticle", article_id: 63534, user_id: 42, topic: "news", time: "2020-01-06"}

I want to be able to build aggregations which shows number of "viewedArticle" events grouped by hour, grouped by topic, counting unique user_ids within each bucket.

Or let's say I want the top K articles viewed each day, filtered by a topic.

That's something that's trivial with Elasticsearch, which has a hierarchical aggregation DSL. Is ClickHouse good at this?

Whenever I see time-series databases such as InfluxDB mentioned, they look like they're focused on measurements, not discrete rows. You can attach the event data as "labels", but this isn't efficient when the cardinality of each column is very high (e.g. article IDs or user IDs in the above example).

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#32

Post by FridgeSeal »

tkyjonathan wrote: post #23

This is going to be your next cloud agnostic data warehouse.

For what it’s worth, I’ve used Clickhouse and Snowflake and I strongly prefer Clickhouse.

Performance was superior, client libraries and built-in HTTP interface was a god-send, it supported geospatial queries. I had perpetual issues with getting Snowflake to properly escape strings in CSV, handle JSON in anything approaching a sensible way, there’s claims that it integrates properly with Kafka as a consumer, but it most certainly does not. The UX is horrible to boot.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#33

Post by FridgeSeal »

endymi0n wrote: post #27
kresten wrote: post #3

What’s the tldr on why it is fast?

I'd argue on ClickHouse not even being that fast (compared to comparable technology like Snowflake, Redshift or BigQuery) but actually the ScyllaDB example being completely misleading. Scylla is probably one of the fastest OLTP datastores, yet they're benchmarking an analytics query — which is pretty easy to crack by any columnar datastore. The actual point here is that you can execute millions of (different!) indivi…

Why do you say CH isn’t as fast as Snowflake? Because my experience is the opposite.

Also, Snowflake is so painful to use and has a bunch of weird edge cases. It’s also so expensive.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#34

Post by manigandham »

atombender wrote: post #31

Is ClickHouse good for event data when you want to do rollups? For example, say all my events are of the form: {event: "viewedArticle", article_id: 63534, user_id: 42, topic: "news", time: "2020-01-06"} I want to be able to build aggregations which shows number of "viewedArticle" events grouped by hour, grouped by topic, counting unique user_ids within each bucket. Or let's say I want the top K articles viewed each d…

Yes. Clickhouse is a column-oriented relational database among many others like MemSQL, Vertica, Redshift, BigQuery, Snowflake, Greenplum, etc. They're all focused on analytical queries over very large datasets using SQL.

An aggregation with several `group by` statements is no challenge and all of these databases also support approximate counting via HyperLogLog for faster results.

Clickhouse has some unique features where each table can have a separate 'engine' including some that automatically apply aggregations. Start with a normal table though since it'll be plenty fast enough for most use cases.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#35

Post by manigandham »

FridgeSeal wrote: post #32
tkyjonathan wrote: post #23

This is going to be your next cloud agnostic data warehouse.

For what it’s worth, I’ve used Clickhouse and Snowflake and I strongly prefer Clickhouse. Performance was superior, client libraries and built-in HTTP interface was a god-send, it supported geospatial queries. I had perpetual issues with getting Snowflake to properly escape strings in CSV, handle JSON in anything approaching a sensible way, there’s claims that it integrates properly with Kafka as a consumer, but it m…

What were the issues with JSON? Snowflake is the best cloud data warehouse when it comes to support for unstructured data and far better than Redshift, Bigquery or the others. Snowflake also has geo support.

It doesn't support streaming data though so things like Kafka aren't a good fit yet. They have a connector but it's basically a little app that automates consuming from Kafka, writing files to S3, then loading them into your tables.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#36

Post by bdcravens »

brobinson wrote: post #13
eecc wrote: post #5

Earlier quoted context omitted.

Precomputed materialized views

Yeah, this post could have been titled "Why Materialized Views are Awesome"

Clickhouse is also crazy fast without materialized views - I've only done some PoC's against it, but in loading a largish data set of raw invoice CSVs, I was very impressed with the performance compared to our standard RDBMS.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#37

Post by manigandham »

patelh wrote: post #15

Not exactly a good comparison if you don't generate the data the same way for the test setup. Your generated data is more compressible by clickhouse, that skews the comparison. Would have been better to not change the test data if you wanted to do a comparison.

It doesn't matter. ScyllaDB is a Cassandra clone, an advanced nested key/value database that stores data per-row and requires slow iteration to scan through an entire table.

Column-oriented databases will always be much faster at analytical queries because of the difference in physical layout and vectorized processing. Scylla's has very impressive OLTP performance but really shouldn't be compared to OLAP databases at all. That original 1B rows/sec blog post by them is kind of a strange benchmark to begin with.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#39

Post by atombender »

manigandham wrote: post #34
atombender wrote: post #31

Is ClickHouse good for event data when you want to do rollups? For example, say all my events are of the form: {event: "viewedArticle", article_id: 63534, user_id: 42, topic: "news", time: "2020-01-06"} I want to be able to build aggregations which shows number of "viewedArticle" events grouped by hour, grouped by topic, counting unique user_ids within each bucket. Or let's say I want the top K articles viewed each d…

Yes. Clickhouse is a column-oriented relational database among many others like MemSQL, Vertica, Redshift, BigQuery, Snowflake, Greenplum, etc. They're all focused on analytical queries over very large datasets using SQL. An aggregation with several `group by` statements is no challenge and all of these databases also support approximate counting via HyperLogLog for faster results. Clickhouse has some unique features…

Thanks! Looks like the only downside is that, as it returns rows as results, you end up getting a lot of duplicate column data back and need to "nest" the nested buckets yourself.

For example, a result like:

  topic;time;count
  news;2020-01-01;44
  news;2020-01-02;31
Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate through the flattened results and generate nested structures. This is something Elasticsearch gives you out of the box.

Last I looked at Clickhouse, it had master/slave replication only, and if you want shards of data distributed across a cluster it's something you need to manually manage?

Post reply on HN