Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Clickhouse Local

clickhouse.tech

Post reply on HN
Print view View original

31–40 of 77 posts

Re: Clickhouse Local

#31

Post by RhodesianHunter »

meritt wrote: post #22
dzonga wrote: post #21

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

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 with load balancers, clusters, and distributed architectures when you could just deploy apache and PHP on a cheap commodity server?

Re: Clickhouse Local

#32

Post by RhodesianHunter »

georgewfraser wrote: post #6

Why 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…

Performance at scale for certain workloads.

Re: Clickhouse Local

#33

Post by Shelnutt2 »

meritt wrote: post #22
dzonga wrote: post #21

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

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.

Another option to consider here is storing your data in TileDB[1] which allows you to access it via Python/Pandas, MariaDB for SQL (embeddable or standalone), Spark, R and more. With embedded MariaDB[2] you can query directly into pandas with minimum overhead. TileDB is similar to parquet in that it can capture sparse dataframe usage, however it is more general in that TileDB supports multi-dimensional datasets and dense arrays. Other major features built directly into TileDB include handling updates, time traveling and partitioning at the library level, removing the need for using extra services like Delta Lake to deal with the numerous Parquet files you may create. TileDB also has native support for S3 and in the next release we'll have native Azure Blob Storage support.

[1] https://github.com/TileDB-Inc/TileDB

[2] https://docs.tiledb.com/developer/api-usage/embedded-sql

Disclosure: I am a member of the TileDB, Inc. team

Re: Clickhouse Local

#34

Post by meritt »

FridgeSeal wrote: post #29
meritt wrote: post #22

Earlier quoted context omitted.

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…

Where did I suggest storing the data on a local machine? Common usage when using pandas/spark/etc is storing source data in parquet/orc/etc and they fully support data partitioning. That doesn't need to come from a local machine by any means.

My bigger point to the OP was why bother using a DBMS that is specifically tuned for running fast analytical queries if they only intended to use it as a storage layer and pushing all of the analysis into Python. Use a solution focused on storage, not analysis, if that's the use case.

Re: Clickhouse Local

#35

Post by amyjess »

georgewfraser wrote: post #6

Why 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…

It's legitimately fast.

My company migrated our time-series data from InfluxDB to ClickHouse last year (I personally led this, in fact), and the performance difference is night and day.

While I liked a lot of what Influx could do, it was also nonstandard in bizarre ways (Clickhouse behaves more like a subset of SQL), sometimes shockingly immature, and despite appearing fast when we first started using it, so slow that it was a considerable bottleneck.

Re: Clickhouse Local

#36

Post by atombender »

quod_2058 wrote: post #23
lflux wrote: post #20

Earlier quoted context omitted.

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…

Note that Greenplum supports column-oriented tables. I haven't used it myself, so I can't comment on whether it's slower than ClickHouse.

Re: Clickhouse Local

#37

Post by atombender »

lflux wrote: post #20
atombender wrote: post #18

Earlier 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?

CitusDB is not relevant here, I believe, as it still uses Postgres' table storage, so it's not columnar. It might be good for analytical workloads, but I very much doubt it will perform anywhere close to ClickHouse.

Greenplum: I've not used it, but it does support columnar tables, so maybe it's comparable.

Re: Clickhouse Local

#38

Post by dzonga »

Shelnutt2 wrote: post #33
meritt wrote: post #22

Earlier quoted context omitted.

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.

Another option to consider here is storing your data in TileDB[1] which allows you to access it via Python/Pandas, MariaDB for SQL (embeddable or standalone), Spark, R and more. With embedded MariaDB[2] you can query directly into pandas with minimum overhead. TileDB is similar to parquet in that it can capture sparse dataframe usage, however it is more general in that TileDB supports multi-dimensional datasets and d…

thank you!

Re: Clickhouse Local

#39

Post by deepsun »

amyjess wrote: post #35
georgewfraser wrote: post #6

Why 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…

It's legitimately fast. My company migrated our time-series data from InfluxDB to ClickHouse last year (I personally led this, in fact), and the performance difference is night and day. While I liked a lot of what Influx could do, it was also nonstandard in bizarre ways (Clickhouse behaves more like a subset of SQL), sometimes shockingly immature, and despite appearing fast when we first started using it, so slow tha…

Clickhouse is not really time-series database, it's more general analytical DB (e.g. also can handle strings, logs, user IPs).

But if you have a lot of time-series metrics (only numbers), you might be better with specialized time-series databases like Prometheus + VictoriaMetrics with Grafana for visualizing it.

Re: Clickhouse Local

#40

Post by deepsun »

derefr wrote: post #27
deepsun wrote: post #12

Earlier quoted context omitted.

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.

Yes, looks like it. According to these posts, ORC only uses snappy or zlib compression, while Clickhouse uses double-delta, Gorilla, and T64 algorithms.

https://engineering.fb.com/core-data/even-faster-data-at-the...

https://www.altinity.com/blog/2019/7/new-encodings-to-improv...

Post reply on HN