5 ms·

> * real-time. There are probably 20+ vendors selling real-time OLAP databases. They all claim that they are cheaper, faster, better than Snowflake/Redshift. Ju

by glogla 4y ago

> * real-time. There are probably 20+ vendors selling real-time OLAP databases. They all claim that they are cheaper, faster, better than Snowflake/Redshift. Just name a few: Druid, Pinot, ClickHouse, StarRocks etc. Let's see who will be the winner in 2023. These are faster but they are also very limited. Most of these have very limited support for joins, can't do proper SQL (e.g. no CTEs in ClickHouse) and require a lot of careful design for your data (e.g. StarRocks can do smart pre-aggregation but you have to tell it which columns are dimensions and which are values). That is fine for limited use cases - like "dedicated engineering team is building specific application" - but doesn't really work for general purpose data platforms. Snowflake I think is successful because it is pretty good at handling everything you throw at it, even if it costs a lot of money.

zX41ZdbW 4y ago
> e.g. no CTEs in ClickHouse ClickHouse has support for CTE. For example, this HTML page I've created for work is using CTEs to query ClickHouse, check out the view-source: https://aretestsgreenyet.com/ https://aretestsgreenyet.com/
zX41ZdbW 4y ago
ClickHouse is the one that is suitable for a wide range of applications. See: https://clickhouse.com/docs/en/about-us/adopters/ https://clickhouse.com/docs/en/about-us/adopters/ The main advantage of ClickHouse is that it's a normal SQL relational DBMS, compared to Druid, Pinot, and others.
berkle4455 4y ago
Clickhouse has joins and CTEs just fine. It is however a column oriented database and joins are generally discouraged instead in favor of denormalized wide tables. That said it has extremely fast lookup mechanisms via joinGet() or dictionaryGet()
- AdamProut 4y ago
  Most SQL Analytical databases don't discourage joins (think BigQuery, Redshift, Snowflake, etc.) and all of them are columnstores. I think discouraging joins is something very specific to Clickhouse, Druid, Pinot and others that have very limited support for joins.
  - deleted 4y ago
    [deleted]
  - hodgesrm 4y ago
    ClickHouse at least supports local joins quite well. Perhaps another way to put it is that BigQuery, Redshift, and Snowflake are not optimized for real-time response on large, wide tables. ClickHouse has features that allow it to pack multiple entities in a single table, then pull data out in a single scan. This includes tricks like simulating joins using aggregation. [0] It's a great design for feeding tenant dashboards with fixed latency (say 2s or less) and predictable cost. This use case is shared by many SaaS offerings. Over time I think the differences will become less as current database engines converge on commonly required features. ClickHouse join types have expanded over the last year and features like join reordering are in the 2023 roadmap. [1] Conversely incumbent cloud databases are adding features to support real-time analytics. I work on ClickHouse at Altinity and can't speak for Druid and Pinot. Perhaps someone else with detailed knowledge can chip in. [0] https://www.databricks.com/dataaisummit/session/opening-floodgates-enabling-fast-unmediated-end-user-access-trillion-row https://www.databricks.com/dataaisummit/session/opening-floo... [1] https://github.com/ClickHouse/ClickHouse/issues/44767 https://github.com/ClickHouse/ClickHouse/issues/44767
- glogla 4y ago
  That is interesting, because dbt-clickhouse claim it doesn't do ephemeral models (i.e. CTE includes) on purpose because ClickHouse doesn't support it. But I'm looking at changelog and CTEs are there since 2020. Hmmm. I guess it has to do more with lack of optimizer that can do more advanced stuff like join reordering, pushdown or subexpression reuse (though even Spark and Trino don't have that one).
  - genzgd 4y ago
    I'm curious as to where you see the claim that dbt-clickhouse doesn't support ephemeral models because ClickHouse doesn't support CTEs. To be honest I don't know the historical reason why dbt-clickhouse doesn't support ephemeral models (ClickHouse Inc. took over this community project early last year). In any case I've opened a new issue to add support. https://github.com/ClickHouse/dbt-clickhouse/issues/126 https://github.com/ClickHouse/dbt-clickhouse/issues/126

by glogla 4y ago

zX41ZdbW 4y ago

> e.g. no CTEs in ClickHouse ClickHouse has support for CTE. For example, this HTML page I've created for work is using CTEs to query ClickHouse, check out the view-source: https://aretestsgreenyet.com/ https://aretestsgreenyet.com/

zX41ZdbW 4y ago

ClickHouse is the one that is suitable for a wide range of applications. See: https://clickhouse.com/docs/en/about-us/adopters/ https://clickhouse.com/docs/en/about-us/adopters/ The main advantage of ClickHouse is that it's a normal SQL relational DBMS, compared to Druid, Pinot, and others.

berkle4455 4y ago

Clickhouse has joins and CTEs just fine. It is however a column oriented database and joins are generally discouraged instead in favor of denormalized wide tables. That said it has extremely fast lookup mechanisms via joinGet() or dictionaryGet()

AdamProut 4y ago
Most SQL Analytical databases don't discourage joins (think BigQuery, Redshift, Snowflake, etc.) and all of them are columnstores. I think discouraging joins is something very specific to Clickhouse, Druid, Pinot and others that have very limited support for joins.
- deleted 4y ago
  [deleted]
- hodgesrm 4y ago
  ClickHouse at least supports local joins quite well. Perhaps another way to put it is that BigQuery, Redshift, and Snowflake are not optimized for real-time response on large, wide tables. ClickHouse has features that allow it to pack multiple entities in a single table, then pull data out in a single scan. This includes tricks like simulating joins using aggregation. [0] It's a great design for feeding tenant dashboards with fixed latency (say 2s or less) and predictable cost. This use case is shared by many SaaS offerings. Over time I think the differences will become less as current database engines converge on commonly required features. ClickHouse join types have expanded over the last year and features like join reordering are in the 2023 roadmap. [1] Conversely incumbent cloud databases are adding features to support real-time analytics. I work on ClickHouse at Altinity and can't speak for Druid and Pinot. Perhaps someone else with detailed knowledge can chip in. [0] https://www.databricks.com/dataaisummit/session/opening-floodgates-enabling-fast-unmediated-end-user-access-trillion-row https://www.databricks.com/dataaisummit/session/opening-floo... [1] https://github.com/ClickHouse/ClickHouse/issues/44767 https://github.com/ClickHouse/ClickHouse/issues/44767
glogla 4y ago
That is interesting, because dbt-clickhouse claim it doesn't do ephemeral models (i.e. CTE includes) on purpose because ClickHouse doesn't support it. But I'm looking at changelog and CTEs are there since 2020. Hmmm. I guess it has to do more with lack of optimizer that can do more advanced stuff like join reordering, pushdown or subexpression reuse (though even Spark and Trino don't have that one).
- genzgd 4y ago
  I'm curious as to where you see the claim that dbt-clickhouse doesn't support ephemeral models because ClickHouse doesn't support CTEs. To be honest I don't know the historical reason why dbt-clickhouse doesn't support ephemeral models (ClickHouse Inc. took over this community project early last year). In any case I've opened a new issue to add support. https://github.com/ClickHouse/dbt-clickhouse/issues/126 https://github.com/ClickHouse/dbt-clickhouse/issues/126