You have to make a distinction betweet query engines, datastores and batch compute.

Trino is a federated query engine and lacks secondary indices, but can do all sorts of big scale stuff like distributed merge sort and spool to disk. The typical use case is querying your data lake (say Iceberg or Hive catalog with a bunch of Parquet format files in S3, and a separate ingestion system). It is extremely mature and well understood with lots of extensions.

ClickHouse, Pinot, StarRocks, and Druid are full on OLAP datastores that can handle varying rates of ingestion (Druid is by far the slowest, the rest are fast like 100kqps writes per table is nothing fast). ClickHouse is the most used but sucks at joins and stateful data. Druid, Pinot and StarRocks can do joins and handle stateful data. In my experience Druid is the worst, Pinot is relatively immature and has minimal industry traction, and StarRocks is the most mature and has lots of traction, both in China and in ethnic Chinese analytics teams in US companies. They can all support high qps for trivial queries (thousands of simple queries in tens of ms, given enough hardware), but depending on data partitioning it can get slow quick handling a bunch of concurrent queries that are scanning the same physical servers. But people have PostGres vertically scaled to 100kqps plus and 100+TB too. In principle the use case is dashboards and charts for your real time UI; tier down to S3 with Trino for more flexible, bigger queries.

The cool thing about all these systems (the fast OLAP) systems is that they are all converging with Trino where they are moving their storage layer to object storage, which is way more flexible. No more hot storage nodes, and infinite storage. And then use Paimon or Iceberg v3 as your data lake and you get near real time stateful storage.

ElasticSearch is amazing but is really optimized for full text search and aggregations, and while it can scale to huge sizes, it does not give you joins and forces you into a very particular approach to materialized views. Also, not columnar... and nested documented dont scale well in my experience.

Spark on the other hand is just your old batch compute framework at this point. It is very flexible, and you are writing a series of SQLish transformations, but for many simpler use cases Trino is much faster and easier to use. Way bigger learning curve than just writing SQL and hoping your query engine has a good optimizer!

The relationship depends on the use case and is not linear. For web analytics and log management ClickHouse is a great replacement for ElasticSearch, for example.

I would put it closest to Pinot though pinot makes different architectural choices about storage as a difference that stands out to me. Then i’d rank trino as a second closest in only some ways. Spark the furthest for sure.

ClickHouse I would say is more for analytics and data warehouse types of loads. So not a direct competitor to those tools except maybe for Pinot.

It is very easy to ingest data into CH. We connected exchange topics and it just worked with zero code.

But the thing with CH is that it is pretty much a Russian product so you should not use it for production anymore.