6 ms·

Why choose Druid over clickhouse?

manigandham 7y ago
Most people shouldn't. Clickhouse or other column-store data warehouses (redshift, bigquery, etc) are very fast and have all the features to handle time-series and other data. Druid is good if you (1) make use of native integrations like Kafka (2) Need every field indexed for fast seeking to a few rows (3) can use the JSON API to make up for the still-in-beta SQL interface (4) don't need every single event/row as they are pre-aggregated (5) always have a time-column to partition by (6) want to use the native S3 tiering for older data (7) dont need joins and complex analysis Imply's distribution is better than core Druid but it's still more operationally complex than Clickhouse and alternatives.
- leetbulb 7y ago
  1) Most people handling large data streams are already using Kafka or similar. If you aren't, Druid's has pretty wide support for event ingress, including HTTP. 2) If you're looking to provide this type of analytics in the first place, you probably do want this. Being able to execute extremely fast, granular, ad-hoc queries over highly dimensional data is very powerful. I designed a reporting frontend that really took advantage of this and I always felt guilty when people would complimented me how fast it drilled down in to 10+ dimensions. 3) There are plenty of mature libraries for the Druid API. 4) This is almost inherent with any OLAP system. Although, even with high-cardinality data, Druid performs extremely well. Either way, you should have a backing data warehouse and offline, batch jobs if you need to perform BI analytics on row-by-row / non-aggregated data. Remember, OLAP sits on top of a data warehouse. 5) Yes, but technically you just need a key that increments. 6) Not sure if this is an argument against Druid. I've found the S3 deep storage / tiering to be very efficient and powerful, especially because you can create and push segments directly to storage and not run it through an indexer. S3 is also just an object store protocol specification now. There are lots of people who run S3-compatible object stores in-house. HDFS is also natively supported and another widely used storage backend in this space. Also, there are plenty of community extensions for other object stores. 7) Again, OLAP. Data flowing in to your OLAP layer should already be denormalized and ready for indexing. Also, you can join data in to your existing indices with Hadoop, etc. Druid support joins and lookups, although I've never used them. ClickHouse and other similar systems also don't do very well with joins. Maybe we have different definitions of "complex analysis," but in my experience, you can do some pretty crazy stuff with queries including writing your own JS functions, and if you're really dedicated, you can write your own extensions. One thing that I feel like a lot of people miss is that Druid is specifically an OLAP layer designed for large scale, and I mean beyond Netflix size scale (they use Druid). Every individual component of Druid is designed to scale out independently and play nicely with your existing infrastructure. Similar to ES, you have nodes that have specific roles such as event ingress / real-time compute, query handlers, query brokers, historical data / cache / compute, etc. Then you also have a bunch of supporting architecture provided for you for (re)indexing data, coordinating the entire cluster, etc. Druid is huge, not an AIO (OLAP, OLTP, DWH, etc) analytics solution, and it takes more than one person to run a larger cluster, even though I did it for a few years.
  - manigandham 7y ago
    Relational databases with joins and full SQL support are still unmatched in flexibility, and functionality like materialized views, aggregation pipelines (and table engines for Clickhouse) allows you to do everything that Druid does with aggregated summaries while still having all the other querying abilities. Druid has a slight edge in data scale-out and indexed seeks but modern data warehouses are adding similar tiering features, along with with field indexing, full-text search, nested data records, and even in-memory rowstores for OLTP support. They're all converging on the same feature set and eventually Druid will just become another data warehouse option, although I'd still recommend Clickhouse or MemSQL at that point.
    - sanderjd 7y ago
      Question for everyone in this thread: what resources do you use to follow this stuff? Are there blogs or conferences or ... that I should follow if I'm interested in this topic?
      - manigandham 7y ago
        Distributed SQL data warehouses? Start with the papers: - C-Store (column-oriented database storage and processing): http://db.lcs.mit.edu/projects/cstore/vldb.pdf http://db.lcs.mit.edu/projects/cstore/vldb.pdf and https://sookocheff.com/post/databases/c-store/ https://sookocheff.com/post/databases/c-store/ - BigQuery (the original Dremel-based query engine): https://cloud.google.com/files/BigQueryTechnicalWP.pdf https://cloud.google.com/files/BigQueryTechnicalWP.pdf - Snowflake paper: https://www.snowflake.com/resource/sigmod-2016-paper-snowflake-elastic-data-warehouse/ https://www.snowflake.com/resource/sigmod-2016-paper-snowfla... - Andy Pavlo's CMU distributed OLAP databases lecture: https://www.youtube.com/watch?v=dPMc7FZ3Gqo&t=1s https://www.youtube.com/watch?v=dPMc7FZ3Gqo&t=1s - MemSQL architecture: https://www.youtube.com/watch?v=HoompXCG5Mk https://www.youtube.com/watch?v=HoompXCG5Mk Start there and just read more about architecture of the various products. You'll learn the basics of distributed nodes that store data in column-oriented tables partitioned by a key and how to run queries over them using various performance techniques. Then you can dive into the unique differences of the various databases.
- DevKoala 7y ago
  You are right about Clickhouse, but other data warehouses are not optimized for the same use case of Druid and Clickhouse, OLAP. For example, RedShift and BigQuery cannot be put behind a user facing backend. BigQuery has a default limit of 50 concurrent queries, if that's your user limit, perfect. RedShift takes seconds for queries that Druid and Clickhouse can answer in milliseconds.
  - manigandham 7y ago
    All data warehouses are designed for OLAP, that's their purpose. It doesn't require low latency though. Redshift is an always running cluster of scale-out distributed postgres forked by AWS so it can and does return results in milliseconds, very similar to Clickhouse although still not as advanced in performance techniques. Bigquery is a completely managed model that uses far greater scale-out architecture designed for througput (petabytes in seconds) rather than latency, although it has real-time streaming, BI Engine (memory cache) and materialized views so you can get pretty close today. Snowflake is another option that runs on top of major clouds using instances to access object storage and also has low latency when your cluster is running.
    - DevKoala 7y ago
      Not all data warehouses are designed for OLAP. As the performance of these systems increase, it is possible to satisfy OLAP use cases with some warehouses such a BQ, but they are still not ideal as your number of concurrent queries scale and your response time requirements remain constant. The BQ team certainly doesn't consider their product an OLAP solution, neither the RedShift team. AWS is actually working on their own OLAP solution, Timestream; that is how they pitch it, as their OLAP solution. BigQuery's petabyte aggregations in seconds is a false claim. I just launched a query to count the number of records of a 1.4 PB dataset and it has taken over a minute to just schedule the query, I have 1000+ compute units available too. I have been able to perform a similar query in seconds on top of Druid; the cluster had a run rate of $25k/month at the time, while I believe we pay 40k+/month just on our BQ deal. Maybe under ideal conditions BigQuery can run such a query, but I have never seen it do such things. I have experience with Snowflake, it is great if you are in AWS, but I wouldn't choose it over BigQuery. I feel that Snowflake's strength is in their data sharing protocol, which allows us to tap into the datasets of partners without major data transfer costs. We use it for that reason only.
- shaklee3 7y ago
  Clickhouse has had native Kafka ingest support for over a year.
leetbulb 7y ago
If you are unable to use a product of Russia, among other reasons.
- polskibus 7y ago
  What institutions impose such limitations for open source software?
  - leetbulb 7y ago
    I can think of plenty that directly or indirectly work with government.
polote 7y ago
It is crazy, every post which talks about Druid, there is someone asking if clickhouse is better
- polskibus 7y ago
  The in depth pros and cons are one of the most valuable bits of knowledge I find on HN. Everyone including Chief Databaseologist Andy Pavlo recommends starting with Clickhouse for you OLAP needs (in https://www.youtube.com/watch?v=dPMc7FZ3Gqo&list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi&index=24 https://www.youtube.com/watch?v=dPMc7FZ3Gqo&list=PLSE8ODhjZX...). When this is the case, Druid needs strong arguments to secure mindshare in the future. It is crazy to write a comparison of Druid and sth else these days and not mention Clickhouse at all.
  - polote 7y ago
    I'm not saying you shouldn't, in my previous company they went all-in in clickhouse and it just changed their lives. But it feels like ads when you have the same person every time telling you the same thing
    - streblo 7y ago
      Changed their lives for the better? Could you elaborate?
PanosJee 7y ago
My preference is TimescaleDB Good "old" Postgre for the win
- GordonS 7y ago
  I've used TimescaleDB with 100 million plus rows, and it's been really fast. It's also been a huge boon to be able to use a single system for both "normal" database stuff as well as time-series.
  - valyala 7y ago
    Did you try VictoriaMetrics for time series data? It easily handles 2 trillion plus rows (20000 times more than TimescaleDB in your setup) on a single node [1]. [1] https://github.com/VictoriaMetrics/VictoriaMetrics/wiki/CaseStudies#adsterra https://github.com/VictoriaMetrics/VictoriaMetrics/wiki/Case...
    - GordonS 7y ago
      No, never even heard of it. There really are a bewildering number of options in this space! I haven't actually tried stressing TimescaleDB, but I imagine it would hold up well if given 50GB of RAM to play with. My point really though, is that TimescaleDB is a great fit for a lot of use cases, with basically no learning curve if you're already au fait with Postgres. And of course if you're already using Postgres elsewhere in your system, it's one less component to manage. I have some industrial IoT use case in mind though, and I'll definitely look more into VictoriaMetrics, so thanks for the tip!
DevKoala 7y ago
I have gone from Druid to Clickhouse. At this point, I don't really know anymore.
- code_biologist 7y ago
  Can you speak briefly to the change? What motivated it? How's it been working out? For context, I'm an engineer that uses Postgres heavily, with some basic BigQuery + Redshift. I don't understand what the benefit of something like Clickhouse are over a standard warehouse.
  - DevKoala 7y ago
    Druid and Clickhouse are OLAP systems. The key here is “online” as in they are optimized to answer queries in a matter of milliseconds. Maybe things have improved, but RedShift is not really an OLAP system. The last time I used RedShift was back in 2015 and it took it tens of seconds or even minutes to answer the queries that Druid could answer in just a few seconds; the Druid cluster was a third of the cost too, AWS was charging us too much. I love BigQuery, but I look at it more as a background processor for queries over huge datasets. With BigQuery you have some real limitations on the number of concurrent queries. Even if BQ was able to answer in seconds, your users would be reaching your concurrent query limit all the time. In RedShift you can perform joins, but in Druid and Clickhouse you cannot, your data has to be denormalized. There are quasi-joins but they are not the same. If you are going to swap Redshift with Druid or Clickhouse, then you need to denormalize the data or design around the specific quasi-join concepts of these databases that abstract dictionary lookups. Druid is operations heavy compared to Clickhouse, while RedShift is just a few button presses. I started using Druid in 2014 and back then to set up a cluster you needed a decent understanding of orchestration. There are plenty of “minimal ops” solutions for Druid nowadays, but IMHO all they are doing is abstracting the problem and not really solving it. To run a Druid cluster, you need 5 or 6 node types and to arrive at the proper configuration of resources for your data needs will take you a lot of time. Clickhouse requires a single node type. Ingesting data is easy in RedShift, but you will have to work a bit more with Druid and Clickhouse. Two of the druid node types are dedicated to ingestion, so your data pipelines should allocate these dynamically. Loading data into Clickhouse is as simple as asking it to ingest a file, but since it is big data, you need a dynamic place to stage such file right? All I am saying is that I wish there was an easier way to pick up the data from private S3 buckets. Performance is a wash between Druid and Clickhouse for me. I never did a proper head to head between the two. However, the product I built on top of Clikhouse is a next get version of the product I built on top of Druid, and in my experience Clickhouse achieves the same performance for a third of the costs. However, you must account that cloud computing has gotten cheaper over the years and my numbers for Druid come from 2017 which is the last time I approved a bill. I forgot to mention PG. I have put small analytic datasets, 80 million rows or so, in Postgres with good results, but even then the response time was never less than a few seconds.

Most people shouldn't. Clickhouse or other column-store data warehouses (redshift, bigquery, etc) are very fast and have all the features to handle time-series and other data. Druid is good if you (1) make use of native integrations like Kafka (2) Need every field indexed for fast seeking to a few rows (3) can use the JSON API to make up for the still-in-beta SQL interface (4) don't need every single event/row as they are pre-aggregated (5) always have a time-column to partition by (6) want to use the native S3 tiering for older data (7) dont need joins and complex analysis Imply's distribution is better than core Druid but it's still more operationally complex than Clickhouse and alternatives.

leetbulb 7y ago
1) Most people handling large data streams are already using Kafka or similar. If you aren't, Druid's has pretty wide support for event ingress, including HTTP. 2) If you're looking to provide this type of analytics in the first place, you probably do want this. Being able to execute extremely fast, granular, ad-hoc queries over highly dimensional data is very powerful. I designed a reporting frontend that really took advantage of this and I always felt guilty when people would complimented me how fast it drilled down in to 10+ dimensions. 3) There are plenty of mature libraries for the Druid API. 4) This is almost inherent with any OLAP system. Although, even with high-cardinality data, Druid performs extremely well. Either way, you should have a backing data warehouse and offline, batch jobs if you need to perform BI analytics on row-by-row / non-aggregated data. Remember, OLAP sits on top of a data warehouse. 5) Yes, but technically you just need a key that increments. 6) Not sure if this is an argument against Druid. I've found the S3 deep storage / tiering to be very efficient and powerful, especially because you can create and push segments directly to storage and not run it through an indexer. S3 is also just an object store protocol specification now. There are lots of people who run S3-compatible object stores in-house. HDFS is also natively supported and another widely used storage backend in this space. Also, there are plenty of community extensions for other object stores. 7) Again, OLAP. Data flowing in to your OLAP layer should already be denormalized and ready for indexing. Also, you can join data in to your existing indices with Hadoop, etc. Druid support joins and lookups, although I've never used them. ClickHouse and other similar systems also don't do very well with joins. Maybe we have different definitions of "complex analysis," but in my experience, you can do some pretty crazy stuff with queries including writing your own JS functions, and if you're really dedicated, you can write your own extensions. One thing that I feel like a lot of people miss is that Druid is specifically an OLAP layer designed for large scale, and I mean beyond Netflix size scale (they use Druid). Every individual component of Druid is designed to scale out independently and play nicely with your existing infrastructure. Similar to ES, you have nodes that have specific roles such as event ingress / real-time compute, query handlers, query brokers, historical data / cache / compute, etc. Then you also have a bunch of supporting architecture provided for you for (re)indexing data, coordinating the entire cluster, etc. Druid is huge, not an AIO (OLAP, OLTP, DWH, etc) analytics solution, and it takes more than one person to run a larger cluster, even though I did it for a few years.
- manigandham 7y ago
  Relational databases with joins and full SQL support are still unmatched in flexibility, and functionality like materialized views, aggregation pipelines (and table engines for Clickhouse) allows you to do everything that Druid does with aggregated summaries while still having all the other querying abilities. Druid has a slight edge in data scale-out and indexed seeks but modern data warehouses are adding similar tiering features, along with with field indexing, full-text search, nested data records, and even in-memory rowstores for OLTP support. They're all converging on the same feature set and eventually Druid will just become another data warehouse option, although I'd still recommend Clickhouse or MemSQL at that point.
  - sanderjd 7y ago
    Question for everyone in this thread: what resources do you use to follow this stuff? Are there blogs or conferences or ... that I should follow if I'm interested in this topic?
    - manigandham 7y ago
      Distributed SQL data warehouses? Start with the papers: - C-Store (column-oriented database storage and processing): http://db.lcs.mit.edu/projects/cstore/vldb.pdf http://db.lcs.mit.edu/projects/cstore/vldb.pdf and https://sookocheff.com/post/databases/c-store/ https://sookocheff.com/post/databases/c-store/ - BigQuery (the original Dremel-based query engine): https://cloud.google.com/files/BigQueryTechnicalWP.pdf https://cloud.google.com/files/BigQueryTechnicalWP.pdf - Snowflake paper: https://www.snowflake.com/resource/sigmod-2016-paper-snowflake-elastic-data-warehouse/ https://www.snowflake.com/resource/sigmod-2016-paper-snowfla... - Andy Pavlo's CMU distributed OLAP databases lecture: https://www.youtube.com/watch?v=dPMc7FZ3Gqo&t=1s https://www.youtube.com/watch?v=dPMc7FZ3Gqo&t=1s - MemSQL architecture: https://www.youtube.com/watch?v=HoompXCG5Mk https://www.youtube.com/watch?v=HoompXCG5Mk Start there and just read more about architecture of the various products. You'll learn the basics of distributed nodes that store data in column-oriented tables partitioned by a key and how to run queries over them using various performance techniques. Then you can dive into the unique differences of the various databases.
DevKoala 7y ago
You are right about Clickhouse, but other data warehouses are not optimized for the same use case of Druid and Clickhouse, OLAP. For example, RedShift and BigQuery cannot be put behind a user facing backend. BigQuery has a default limit of 50 concurrent queries, if that's your user limit, perfect. RedShift takes seconds for queries that Druid and Clickhouse can answer in milliseconds.
- manigandham 7y ago
  All data warehouses are designed for OLAP, that's their purpose. It doesn't require low latency though. Redshift is an always running cluster of scale-out distributed postgres forked by AWS so it can and does return results in milliseconds, very similar to Clickhouse although still not as advanced in performance techniques. Bigquery is a completely managed model that uses far greater scale-out architecture designed for througput (petabytes in seconds) rather than latency, although it has real-time streaming, BI Engine (memory cache) and materialized views so you can get pretty close today. Snowflake is another option that runs on top of major clouds using instances to access object storage and also has low latency when your cluster is running.
  - DevKoala 7y ago
    Not all data warehouses are designed for OLAP. As the performance of these systems increase, it is possible to satisfy OLAP use cases with some warehouses such a BQ, but they are still not ideal as your number of concurrent queries scale and your response time requirements remain constant. The BQ team certainly doesn't consider their product an OLAP solution, neither the RedShift team. AWS is actually working on their own OLAP solution, Timestream; that is how they pitch it, as their OLAP solution. BigQuery's petabyte aggregations in seconds is a false claim. I just launched a query to count the number of records of a 1.4 PB dataset and it has taken over a minute to just schedule the query, I have 1000+ compute units available too. I have been able to perform a similar query in seconds on top of Druid; the cluster had a run rate of $25k/month at the time, while I believe we pay 40k+/month just on our BQ deal. Maybe under ideal conditions BigQuery can run such a query, but I have never seen it do such things. I have experience with Snowflake, it is great if you are in AWS, but I wouldn't choose it over BigQuery. I feel that Snowflake's strength is in their data sharing protocol, which allows us to tap into the datasets of partners without major data transfer costs. We use it for that reason only.
shaklee3 7y ago
Clickhouse has had native Kafka ingest support for over a year.

leetbulb 7y ago

If you are unable to use a product of Russia, among other reasons.

polskibus 7y ago
What institutions impose such limitations for open source software?
- leetbulb 7y ago
  I can think of plenty that directly or indirectly work with government.

polote 7y ago

It is crazy, every post which talks about Druid, there is someone asking if clickhouse is better

polskibus 7y ago
The in depth pros and cons are one of the most valuable bits of knowledge I find on HN. Everyone including Chief Databaseologist Andy Pavlo recommends starting with Clickhouse for you OLAP needs (in https://www.youtube.com/watch?v=dPMc7FZ3Gqo&list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi&index=24 https://www.youtube.com/watch?v=dPMc7FZ3Gqo&list=PLSE8ODhjZX...). When this is the case, Druid needs strong arguments to secure mindshare in the future. It is crazy to write a comparison of Druid and sth else these days and not mention Clickhouse at all.
- polote 7y ago
  I'm not saying you shouldn't, in my previous company they went all-in in clickhouse and it just changed their lives. But it feels like ads when you have the same person every time telling you the same thing
  - streblo 7y ago
    Changed their lives for the better? Could you elaborate?

PanosJee 7y ago

My preference is TimescaleDB Good "old" Postgre for the win

GordonS 7y ago
I've used TimescaleDB with 100 million plus rows, and it's been really fast. It's also been a huge boon to be able to use a single system for both "normal" database stuff as well as time-series.
- valyala 7y ago
  Did you try VictoriaMetrics for time series data? It easily handles 2 trillion plus rows (20000 times more than TimescaleDB in your setup) on a single node [1]. [1] https://github.com/VictoriaMetrics/VictoriaMetrics/wiki/CaseStudies#adsterra https://github.com/VictoriaMetrics/VictoriaMetrics/wiki/Case...
  - GordonS 7y ago
    No, never even heard of it. There really are a bewildering number of options in this space! I haven't actually tried stressing TimescaleDB, but I imagine it would hold up well if given 50GB of RAM to play with. My point really though, is that TimescaleDB is a great fit for a lot of use cases, with basically no learning curve if you're already au fait with Postgres. And of course if you're already using Postgres elsewhere in your system, it's one less component to manage. I have some industrial IoT use case in mind though, and I'll definitely look more into VictoriaMetrics, so thanks for the tip!

DevKoala 7y ago

I have gone from Druid to Clickhouse. At this point, I don't really know anymore.

code_biologist 7y ago
Can you speak briefly to the change? What motivated it? How's it been working out? For context, I'm an engineer that uses Postgres heavily, with some basic BigQuery + Redshift. I don't understand what the benefit of something like Clickhouse are over a standard warehouse.
- DevKoala 7y ago
  Druid and Clickhouse are OLAP systems. The key here is “online” as in they are optimized to answer queries in a matter of milliseconds. Maybe things have improved, but RedShift is not really an OLAP system. The last time I used RedShift was back in 2015 and it took it tens of seconds or even minutes to answer the queries that Druid could answer in just a few seconds; the Druid cluster was a third of the cost too, AWS was charging us too much. I love BigQuery, but I look at it more as a background processor for queries over huge datasets. With BigQuery you have some real limitations on the number of concurrent queries. Even if BQ was able to answer in seconds, your users would be reaching your concurrent query limit all the time. In RedShift you can perform joins, but in Druid and Clickhouse you cannot, your data has to be denormalized. There are quasi-joins but they are not the same. If you are going to swap Redshift with Druid or Clickhouse, then you need to denormalize the data or design around the specific quasi-join concepts of these databases that abstract dictionary lookups. Druid is operations heavy compared to Clickhouse, while RedShift is just a few button presses. I started using Druid in 2014 and back then to set up a cluster you needed a decent understanding of orchestration. There are plenty of “minimal ops” solutions for Druid nowadays, but IMHO all they are doing is abstracting the problem and not really solving it. To run a Druid cluster, you need 5 or 6 node types and to arrive at the proper configuration of resources for your data needs will take you a lot of time. Clickhouse requires a single node type. Ingesting data is easy in RedShift, but you will have to work a bit more with Druid and Clickhouse. Two of the druid node types are dedicated to ingestion, so your data pipelines should allocate these dynamically. Loading data into Clickhouse is as simple as asking it to ingest a file, but since it is big data, you need a dynamic place to stage such file right? All I am saying is that I wish there was an easier way to pick up the data from private S3 buckets. Performance is a wash between Druid and Clickhouse for me. I never did a proper head to head between the two. However, the product I built on top of Clikhouse is a next get version of the product I built on top of Druid, and in my experience Clickhouse achieves the same performance for a third of the costs. However, you must account that cloud computing has gotten cheaper over the years and my numbers for Druid come from 2017 which is the last time I approved a bill. I forgot to mention PG. I have put small analytic datasets, 80 million rows or so, in Postgres with good results, but even then the response time was never less than a few seconds.