Hacker News Reader

Comment by dzonga | Hacker News Reader

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

View on HN

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

thank you!

> 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.

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.

Because the more that you can offload onto the database means less people re-inventing from first principles every time they need to do something (and potentially making correctness mistakes).

Additionally, if I have my data in an actual database, I can attach tools like Tableau and Superset directly, as opposed to having to take it from storage and then put it in a database anyway before being able to visualise/use it.

You misinterpret my comment as being against databases, when in fact I'm simply against the notion of utilizing a purpose-built OLAP database (Clickhouse) merely as the storage layer for yet another analytical platform (Pandas). In this hypothetical scenario, before the data ever makes it into Clickhouse, there's a very high likelihood it would already live in an OLTP database such as Postgres or perhaps parquet files living on S3 designed to be read by Athena or Redshift Spectrum etc.

Why would you bother with load balancers, clusters, and distributed architectures when you could just deploy apache and PHP on a cheap commodity server?

Reply on news.ycombinator.com