3 ms·

It's hardly comparable with ClickHouse. Even loading a table with 100M rows is not an easy endeavor in Pinot: https://github.com/ClickHouse/ClickBench/blob/main

It's hardly comparable with ClickHouse. Even loading a table with 100M rows is not an easy endeavor in Pinot: https://github.com/ClickHouse/ClickBench/blob/main/pinot/benchmark.sh https://github.com/ClickHouse/ClickBench/blob/main/pinot/ben...

fiddlerwoaroof 3y ago
Yeah, ClickHouse seems to me to be best in class, but I think Pinot is still aiming at the same general market segment.
- riku_iki 3y ago
  that benchmark is very limited in use cases (no heavy grouping, no joins, very small dataset), I think it is more for self-advertisement purposes
  - fiddlerwoaroof 3y ago
    I’m speaking from experience, not from those benchmarks.
    - riku_iki 3y ago
      somehow I have different experience, anything unlike some simple select few fields from t where k = ? usually causes all kinds of errors and stability issues on good size of data.
      - glogla 3y ago
        You can be both right. I would say that ClickHouse is a focused system - it is really really good at the things it's good at, and it is not good at the rest. Anecdote: I tested ClickHouse as possible replacement of Trino+DeltaLake+S3 for some of our use cases. When querying a precomputed flat tables it was easily 10x to 100x faster. When running complex ETL to prepare those tables, I gave up when CTAS with six CTEs that takes Trino 30 seconds to compute on the fly turned into six intermediate tables that took 40 minutes to compute, and I wasn't even halfway done. The tricky part is, how do you know whether your use case fits? But you have to ask this question about all the "specialized tools", including Pinot.
  - PeterCorless 3y ago
    JOINs are a non-trivial problem with data in the terabyte-to-petabyte range. Apache Pinot had to re-architect itself with a multi-stage query engine in order to handle native query-time JOINs. There was a separate blog released today that dealt with that specifically: https://startree.ai/blog/query-time-joins-in-apache-pinot-1-0 https://startree.ai/blog/query-time-joins-in-apache-pinot-1-... [Edit: if users just naively throw query-time JOINs at a problem, they might not get results in the time they want — a non-optimized real-time JOIN took upwards of 39 seconds. With predicate pushdowns, and partition-aware JOINs, suddenly results could be done in 3.3 seconds — faster by an order of magnitude. Still kinda long for a typical page or mobile app refresh but survivable. And yes, even faster subsecond results were possible with additional compute parallelism, but that has a cloud services cost associated with it. Pre-ingestion JOINs, such as through Apache Flink, would be generally more performant than query-time JOINs. And that's how most people do them right now anyway. So just because you can do query-time JOINs doesn't mean you should if you haven't thought about it ahead of time. If you can, optimize the data partioning to ensure best performance. The good news is that users now have a lot more flexibility in how they want to do JOINs.]
    - riku_iki 3y ago
      > JOINs are a non-trivial problem with data in the terabyte-to-petabyte range. but that benchmark is 250G, plus all established players I think support joins well, I work with BQ closely and it works very well. > So just because you can do query-time JOINs doesn't mean you should if you haven't thought about it ahead of time. prebuilding denormalized table can significantly increase your datasize, so it is tradeoff and depends on your data structure and cardinality.
      - saberience 3y ago
        Are you comparing BigQuery to Pinot? They are for totally different use cases.
        riku_iki 3y ago
        I don't agree with "totally", many usecases, especially where joins are involved totally overlap.

fiddlerwoaroof 3y ago

Yeah, ClickHouse seems to me to be best in class, but I think Pinot is still aiming at the same general market segment.

riku_iki 3y ago
that benchmark is very limited in use cases (no heavy grouping, no joins, very small dataset), I think it is more for self-advertisement purposes
- fiddlerwoaroof 3y ago
  I’m speaking from experience, not from those benchmarks.
  - riku_iki 3y ago
    somehow I have different experience, anything unlike some simple select few fields from t where k = ? usually causes all kinds of errors and stability issues on good size of data.
    - glogla 3y ago
      You can be both right. I would say that ClickHouse is a focused system - it is really really good at the things it's good at, and it is not good at the rest. Anecdote: I tested ClickHouse as possible replacement of Trino+DeltaLake+S3 for some of our use cases. When querying a precomputed flat tables it was easily 10x to 100x faster. When running complex ETL to prepare those tables, I gave up when CTAS with six CTEs that takes Trino 30 seconds to compute on the fly turned into six intermediate tables that took 40 minutes to compute, and I wasn't even halfway done. The tricky part is, how do you know whether your use case fits? But you have to ask this question about all the "specialized tools", including Pinot.
- PeterCorless 3y ago
  JOINs are a non-trivial problem with data in the terabyte-to-petabyte range. Apache Pinot had to re-architect itself with a multi-stage query engine in order to handle native query-time JOINs. There was a separate blog released today that dealt with that specifically: https://startree.ai/blog/query-time-joins-in-apache-pinot-1-0 https://startree.ai/blog/query-time-joins-in-apache-pinot-1-... [Edit: if users just naively throw query-time JOINs at a problem, they might not get results in the time they want — a non-optimized real-time JOIN took upwards of 39 seconds. With predicate pushdowns, and partition-aware JOINs, suddenly results could be done in 3.3 seconds — faster by an order of magnitude. Still kinda long for a typical page or mobile app refresh but survivable. And yes, even faster subsecond results were possible with additional compute parallelism, but that has a cloud services cost associated with it. Pre-ingestion JOINs, such as through Apache Flink, would be generally more performant than query-time JOINs. And that's how most people do them right now anyway. So just because you can do query-time JOINs doesn't mean you should if you haven't thought about it ahead of time. If you can, optimize the data partioning to ensure best performance. The good news is that users now have a lot more flexibility in how they want to do JOINs.]
  - riku_iki 3y ago
    > JOINs are a non-trivial problem with data in the terabyte-to-petabyte range. but that benchmark is 250G, plus all established players I think support joins well, I work with BQ closely and it works very well. > So just because you can do query-time JOINs doesn't mean you should if you haven't thought about it ahead of time. prebuilding denormalized table can significantly increase your datasize, so it is tradeoff and depends on your data structure and cardinality.
    - saberience 3y ago
      Are you comparing BigQuery to Pinot? They are for totally different use cases.
      - riku_iki 3y ago
        I don't agree with "totally", many usecases, especially where joins are involved totally overlap.