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

ClickHouse, a column-oriented DBMS to generate analytical reports in real time

github.com

Post reply on HN
Print view View original

11–20 of 33 posts

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#11

Post by manigandham »

georgewfraser wrote: post #6

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just…

Redshift doesn't separate compute from storage either unless you're using Spectrum. Presto isn't a database at all and can read from many data stores. The rest are all cloud-hosted with lots of moving parts. MemSQL, Vertica, Actian, Greenplum, and SQL Server are better comparisons.

ClickHouse is a column-oriented db and actually one of the most advanced, focusing on performance at all costs with lots of table storage engines that provide flexibility for your exact use-case. It also supports distributed joins and deletes but has some limitations they are working on.

It can definitely use better tooling and compatibility though, but that's the tradeoff the core team made, and it seems to be working well for the companies that can afford the time and talent.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#12

Post by dikei »

georgewfraser wrote: post #6

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just…

I wouldn't dismiss ClickHouse so quickly. There's no one-size-fit-all solution for data warehouse, everything has its own quirks.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#13

Post by manigandham »

mamcx wrote: post #10
georgewfraser wrote: post #6

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just…

> The basic techniques for implementing a fast column-store Pointers to what them are?

Recent post: https://news.ycombinator.com/item?id=18076547

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#14

Post by theshadowknows »

georgewfraser wrote: post #6

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just…

We’re looking into Snowflake at my current job. Have you used it before?

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#15

Post by manigandham »

theshadowknows wrote: post #14
georgewfraser wrote: post #6

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just…

We’re looking into Snowflake at my current job. Have you used it before?

Snowflake is great for smaller data volumes, but slow at supersized datasets. It has the best UX out of all the cloud-hosted options though, and the best JSON handling. Interesting billing model based on running time that's a good fit for focused data querying sessions using BI tools where you can scale up while in use and turn off after.

Tables are stored as S3 data making sharing, cloning, and snapshot queries easy. Major missing features are lack of streaming (although they have a auto loading option from S3 files) and no stored procedures. Transactions are also tricky and client drivers are sometimes neglected because everything is a wrapper around their HTTP/JSON interface.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#16

Post by georgewfraser »

manigandham wrote: post #11
georgewfraser wrote: post #6

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just…

Redshift doesn't separate compute from storage either unless you're using Spectrum. Presto isn't a database at all and can read from many data stores. The rest are all cloud-hosted with lots of moving parts. MemSQL, Vertica, Actian, Greenplum, and SQL Server are better comparisons. ClickHouse is a column-oriented db and actually one of the most advanced, focusing on performance at all costs with lots of table storage…

My point is there’s just no advantage to ClickHouse. The things that make it fast are in every column store. There’s other options that do everything it does and more.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#17

Post by eldargab »

dorfsmay wrote: post #5

Can it run on a cluster? Or single server only? Can you update specific rows? How fast are updates? How does it compare to monetDB.

monetDB is a sort of drop-in replacement for a regular database with all expected features and good compatibility.

On other hand ClickHouse will be incompatible with most of existing tools and it's better to learn well its limitations and workaround technics in advance. But once you dump into it substantial amounts of time series data you'll find it 10+ times faster and 2-3 times smaller than monet.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#18

Post by sin7 »

dorfsmay wrote: post #5

Can it run on a cluster? Or single server only? Can you update specific rows? How fast are updates? How does it compare to monetDB.

You can run it on a cluster or a single server. It's pretty easy to setup either way.

No updates. Fast inserts.

You can only join two tables at a time, but the joins can be chained to deal with this limitation.

I tried Monet. It wasn't very stable for me. I didn't stick with it long enough to judge it. ClickHouse has backing of Yandex. I think that makes a huge difference.

I have used Clickhouse for the past year. Thrown 3000 column by 120 million row tables on it. It worked where PostgreSQL came to a halt. Different use cases really.

I fits my use case perfectly. Large amounts of data with no updates and tons of aggregations. It's lighting fast.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#19

Post by manigandham »

georgewfraser wrote: post #16
manigandham wrote: post #11

Earlier quoted context omitted.

Redshift doesn't separate compute from storage either unless you're using Spectrum. Presto isn't a database at all and can read from many data stores. The rest are all cloud-hosted with lots of moving parts. MemSQL, Vertica, Actian, Greenplum, and SQL Server are better comparisons. ClickHouse is a column-oriented db and actually one of the most advanced, focusing on performance at all costs with lots of table storage…

My point is there’s just no advantage to ClickHouse. The things that make it fast are in every column store. There’s other options that do everything it does and more.

Speed is the advantage. It's very fast for a self-hosted system and probably only beaten by BigQuery for throughput.

You're right though that most people don't need it and can get 90% of the speed with better usability with the other options.

Re: ClickHouse, a column-oriented DBMS to generate analytical reports in real time

#20

Post by manigandham »

sin7 wrote: post #18
dorfsmay wrote: post #5

Can it run on a cluster? Or single server only? Can you update specific rows? How fast are updates? How does it compare to monetDB.

You can run it on a cluster or a single server. It's pretty easy to setup either way. No updates. Fast inserts. You can only join two tables at a time, but the joins can be chained to deal with this limitation. I tried Monet. It wasn't very stable for me. I didn't stick with it long enough to judge it. ClickHouse has backing of Yandex. I think that makes a huge difference. I have used Clickhouse for the past year. Th…

It does support updates and deletes now, but still limited.
Post reply on HN