funny thing, just learnt about clickhouse today. for experienced people that use columnar stores and pandas for analytics, which tool do you usually prefer for BI stuff ? do ya'll load data into clickhouse then analyse it using pandas. or all analysis is done via the clickhouse sql dialect. As i'm sure things like pivot tables and rolling windows are a PITA in SQL
Clickhouse Local
21–30 of 77 posts
Re: Clickhouse Local
#22funny thing, just learnt about clickhouse today. for experienced people that use columnar stores and pandas for analytics, which tool do you usually prefer for BI stuff ? do ya'll load data into clickhouse then analyse it using pandas. or all analysis is done via the clickhouse sql dialect. As i'm sure things like pivot tables and rolling windows are a PITA in SQL
Why would you bother using a database like clickhouse to store data if you're just going to analyze it in pandas? Just store it in a csv, parquet, or orc.
> As i'm sure things like pivot tables and rolling windows are a PITA in SQL
I can't speak for clickhouse, but group-by and window functions are a very standard part of any SQL analysts toolbelt.
Re: Clickhouse Local
#23Earlier quoted context omitted.
Because it's one of the few mature, fast, scalable analytics-oriented databases that is also completely open source. The closest open source thing that matches its feature set is Presto, but that one is quite different. Apache Druid is supposed to be very mature, but also very difficult to set up and manage. I've not used it myself. There's also Vespa, but I don't know how well it performs with large numbers of colum…
What about Greenplum or CitusDB?
Greenplum seems to like a way to throw more hardware at a problem. Queries are too slow with postgres? Shard your data across machines and distribute queries to speed things up by running in parallel. It's using scale as a means to compensate for low efficiency.
On the opposite side of the spectrum you have other open source projects like questDB that have full focus on core performance: constantly optimise to get as much as possible from a single processor core. You can't scale out (at least yet), but given how fast it is on single core, it will be pretty powerful if they chose to go this route.
Re: Clickhouse Local
#24Earlier quoted context omitted.
Because it's one of the few mature, fast, scalable analytics-oriented databases that is also completely open source. The closest open source thing that matches its feature set is Presto, but that one is quite different. Apache Druid is supposed to be very mature, but also very difficult to set up and manage. I've not used it myself. There's also Vespa, but I don't know how well it performs with large numbers of colum…
What about Greenplum or CitusDB?
[deleted]
Re: Clickhouse Local
#25Earlier quoted context omitted.
Could you name those good analytical databases? I'd love to learn more.
Snowflake, Redshift, BigQuery, Databricks, Presto.
According to https://tech.marksblogg.com/benchmarks.html Clickhouse has better performance than 3 of those (the other 2 haven't been tested in that benchmark)
Re: Clickhouse Local
#26Clickhouse Local is great also for importing into your Clickhouse Server, where you can validate and preprocess CSVs into Clickhouse's native table format.
Re: Clickhouse Local
#27Earlier quoted context omitted.
Snowflake, Redshift, BigQuery, Databricks, Presto.
I can say for BigQuery and Databricks from personal experience. BigQuery is much slower and is much more expensive for both storage and query. Databricks (Spark) is even slower than that (both io and compute), although you can write custom code/use libs. You seem to underestimate how heavily ClickHouse is optimized (e.g. compressed storage).
> You seem to underestimate how heavily ClickHouse is optimized (e.g. compressed storage).
Is it any more compressed than Apache Hive's ORC format (https://orc.apache.org)? Because that's increasingly accepted as a storage format in a lot of these analytical systems.
Re: Clickhouse Local
#28Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…
Could you name those good analytical databases? I'd love to learn more.
DuckDB perhaps[1]: https://www.duckdb.org
[1] I say "perhaps" because I've only just started using it having migrated from MonetDB, but have no experience of alternatives like Presto.
Re: Clickhouse Local
#29funny thing, just learnt about clickhouse today. for experienced people that use columnar stores and pandas for analytics, which tool do you usually prefer for BI stuff ? do ya'll load data into clickhouse then analyse it using pandas. or all analysis is done via the clickhouse sql dialect. As i'm sure things like pivot tables and rolling windows are a PITA in SQL
Why would you bother using a database like clickhouse to store data if you're just going to analyze it in pandas? Just store it in a csv, parquet, or orc. > As i'm sure things like pivot tables and rolling windows are a PITA in SQL I can't speak for clickhouse, but group-by and window functions are a very standard part of any SQL analysts toolbelt.
> Why would you bother using a database like clickhouse to store data if you're just going to analyze it in pandas?
Because I have more data than what fits locally, there’s a data pipeline that pushes more in, and I only need to work on a subset.
Storing everything in flat csv/parquet etc is useless when there’s more than fits on your local/single machine memory or if you want to search/subset etc some of the data or do anything that’s larger than memory without having to write spill-to-disk stuff in Python/pandas.
Re: Clickhouse Local
#30Earlier quoted context omitted.
Snowflake, Redshift, BigQuery, Databricks, Presto.
According to https://tech.marksblogg.com/benchmarks.html Clickhouse has better performance than 3 of those (the other 2 haven't been tested in that benchmark)
I would be cautious using this as a proxy for performance ranking as some items (dataset, queries) are normalized, but the hardware setup is not.