6 ms·

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

by meritt 7y ago

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.

FridgeSeal 7y ago
> 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.
- meritt 7y ago
  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.
  - FridgeSeal 7y ago
    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.
    - meritt 7y ago
      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.
RhodesianHunter 7y ago
Why would you bother with load balancers, clusters, and distributed architectures when you could just deploy apache and PHP on a cheap commodity server?
Shelnutt2 7y ago
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 https://github.com/TileDB-Inc/TileDB [2] https://docs.tiledb.com/developer/api-usage/embedded-sql https://docs.tiledb.com/developer/api-usage/embedded-sql Disclosure: I am a member of the TileDB, Inc. team
- dzonga 7y ago
  thank you!

by meritt 7y ago

FridgeSeal 7y ago

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

meritt 7y ago
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.
- FridgeSeal 7y ago
  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.
  - meritt 7y ago
    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.

RhodesianHunter 7y ago

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

Shelnutt2 7y ago

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 https://github.com/TileDB-Inc/TileDB [2] https://docs.tiledb.com/developer/api-usage/embedded-sql https://docs.tiledb.com/developer/api-usage/embedded-sql Disclosure: I am a member of the TileDB, Inc. team

dzonga 7y ago
thank you!