5 ms·

Readme could link to or explain what clickhouse is, for those of us who might not know.

by JOnAgain 3y ago

mdekkers 3y ago
Clickhouse is a really cool and stupidly fast columnar database
- tempest_ 3y ago
  What problems can I solve with a columnar database? What type of data benefits from that type of Database?
  - cplli 3y ago
    Personally tried it, it can handle logs nicely. And from their page, many more things https://clickhouse.com/use-cases https://clickhouse.com/use-cases
    - craigching 3y ago
      Uber wrote a blog on using Clickhouse to store logs: https://www.uber.com/blog/logging/ https://www.uber.com/blog/logging/
  - Dachande663 3y ago
    Cloudflare use it to ingest 6M/s https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/ https://blog.cloudflare.com/http-analytics-for-6m-requests-p...
    - jgrahamc 3y ago
      Way more than that now.
  - datatrashfire 3y ago
    Row based databases are optimized for accessing compete rows and joins. Columnar storage is optimized for accessing all, or many column values across rows. This makes aggregates and applying transformation logic faster with columnar storage than row based storage. Ie they are great for data warehouses and other analytical workloads. Ps, great and still highly relevant resource covering all the major database system designs, their advantages and drawbacks: https://www.oreilly.com/library/view/designing-data-intensive-applications/9781491903063/ https://www.oreilly.com/library/view/designing-data-intensiv...
  - FridgeSeal 3y ago
    Less about the data itself and more about the specific operations you want to do on it. Large aggregations, massive datasets, large joins, and workloads that are ready heavy and eschew row-level mutations. They get used for data analysis frequently, time series data and associated analysis meshes quite nicely too. ClickHouse itself was originally built to support arbitrary analytical queries on clickstream data at pretty massive scale. Cloudflare uses it for live analytics, Uber uses it for logs.
  - Exuma 3y ago
    Imagine you have a small business that tracks in the order of 10's - 100's of millions of events (pageviews, clicks, whatever), and you have reporting you want to run. Trying to do this in PG/MySQL would likely need to use materialized views so your reports don't take a long time to run. You could store your event data in CH directly, or use ELT/ETL process to sync/copy it into clickhouse just for reporting. Then, your queries would be very fast. It's must faster (for certain types of queries, mainly timeseries queries or queries involving aggregation of many rows). It's faster because of how the data is stored on disk. It's NOT good for fetching/updating/deleting single rows however. It's originally designed to handle hundreds of columns, and billions of rows, but I think it can still apply to much smaller use cases that value performance. I'm implementing it currently in a similar scenario, and I'm using AirByte OSS version to ELT from postgres. Then I'm using tableau or some other BI tool to analyze that data much more effectively (I will be trying to perform complex aggregations/group by reports on 100mm rows)
  - linuxdude314 3y ago
    That’s a longer subject that fits in a comment here. If you are _actually_ interested I suggest using google search to find some good sites that go over what a column oriented database does/is used for. This isn’t hard; I’ll get you started: https://www.kdnuggets.com/2021/02/understanding-nosql-database-types-column-oriented-databases.html https://www.kdnuggets.com/2021/02/understanding-nosql-databa...
    - Exuma 3y ago
      Or he, you know, could just ask, because that is the spirit of discussion.
  - pjot 3y ago
    An over simplification: Columnar stores are optimized for reads. Row stores are optimized for writes.
  - anonacct37 3y ago
    This is an overly simplistic but also correct answer: clickhouse was developed for analytics on clickstreams. Technically the overall idea is that if you have lots of queries that only read certain columns and your database stores rows contiguously it's a waste to read a whole row and then discard columns. Also compression (such as run length or delta or even ztsd) often works better if you give it a block of data that's from one column (such as a timestamp or tag value).
  - esafak 3y ago
    Columnar databases let you do fast aggregations and read only the columns you are interested in. They are for analyzing data.
- schoolornot 3y ago
  I understand why OLAP writes are faster but is there any reason why OLTPs can't achieve similar read performance with denormalized and sharded data?
  - rscrawfo 3y ago
    Aggregation is a huge reason. For rolling up data, something can’t like Clickhouse can’t be beat by oltp
linuxdude314 3y ago
That’s a bit silly. If you don’t know what something is, you can google it pretty easily. Everyone doesn’t need to cater to the lowest common denominator of knowledge.

Readme could link to or explain what clickhouse is, for those of us who might not know.

mdekkers 3y ago

Clickhouse is a really cool and stupidly fast columnar database

tempest_ 3y ago
What problems can I solve with a columnar database? What type of data benefits from that type of Database?
- cplli 3y ago
  Personally tried it, it can handle logs nicely. And from their page, many more things https://clickhouse.com/use-cases https://clickhouse.com/use-cases
  - craigching 3y ago
    Uber wrote a blog on using Clickhouse to store logs: https://www.uber.com/blog/logging/ https://www.uber.com/blog/logging/
- Dachande663 3y ago
  Cloudflare use it to ingest 6M/s https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/ https://blog.cloudflare.com/http-analytics-for-6m-requests-p...
  - jgrahamc 3y ago
    Way more than that now.
- datatrashfire 3y ago
  Row based databases are optimized for accessing compete rows and joins. Columnar storage is optimized for accessing all, or many column values across rows. This makes aggregates and applying transformation logic faster with columnar storage than row based storage. Ie they are great for data warehouses and other analytical workloads. Ps, great and still highly relevant resource covering all the major database system designs, their advantages and drawbacks: https://www.oreilly.com/library/view/designing-data-intensive-applications/9781491903063/ https://www.oreilly.com/library/view/designing-data-intensiv...
- FridgeSeal 3y ago
  Less about the data itself and more about the specific operations you want to do on it. Large aggregations, massive datasets, large joins, and workloads that are ready heavy and eschew row-level mutations. They get used for data analysis frequently, time series data and associated analysis meshes quite nicely too. ClickHouse itself was originally built to support arbitrary analytical queries on clickstream data at pretty massive scale. Cloudflare uses it for live analytics, Uber uses it for logs.
- Exuma 3y ago
  Imagine you have a small business that tracks in the order of 10's - 100's of millions of events (pageviews, clicks, whatever), and you have reporting you want to run. Trying to do this in PG/MySQL would likely need to use materialized views so your reports don't take a long time to run. You could store your event data in CH directly, or use ELT/ETL process to sync/copy it into clickhouse just for reporting. Then, your queries would be very fast. It's must faster (for certain types of queries, mainly timeseries queries or queries involving aggregation of many rows). It's faster because of how the data is stored on disk. It's NOT good for fetching/updating/deleting single rows however. It's originally designed to handle hundreds of columns, and billions of rows, but I think it can still apply to much smaller use cases that value performance. I'm implementing it currently in a similar scenario, and I'm using AirByte OSS version to ELT from postgres. Then I'm using tableau or some other BI tool to analyze that data much more effectively (I will be trying to perform complex aggregations/group by reports on 100mm rows)
- linuxdude314 3y ago
  That’s a longer subject that fits in a comment here. If you are _actually_ interested I suggest using google search to find some good sites that go over what a column oriented database does/is used for. This isn’t hard; I’ll get you started: https://www.kdnuggets.com/2021/02/understanding-nosql-database-types-column-oriented-databases.html https://www.kdnuggets.com/2021/02/understanding-nosql-databa...
  - Exuma 3y ago
    Or he, you know, could just ask, because that is the spirit of discussion.
- pjot 3y ago
  An over simplification: Columnar stores are optimized for reads. Row stores are optimized for writes.
- anonacct37 3y ago
  This is an overly simplistic but also correct answer: clickhouse was developed for analytics on clickstreams. Technically the overall idea is that if you have lots of queries that only read certain columns and your database stores rows contiguously it's a waste to read a whole row and then discard columns. Also compression (such as run length or delta or even ztsd) often works better if you give it a block of data that's from one column (such as a timestamp or tag value).
- esafak 3y ago
  Columnar databases let you do fast aggregations and read only the columns you are interested in. They are for analyzing data.
schoolornot 3y ago
I understand why OLAP writes are faster but is there any reason why OLTPs can't achieve similar read performance with denormalized and sharded data?
- rscrawfo 3y ago
  Aggregation is a huge reason. For rolling up data, something can’t like Clickhouse can’t be beat by oltp

linuxdude314 3y ago

That’s a bit silly. If you don’t know what something is, you can google it pretty easily. Everyone doesn’t need to cater to the lowest common denominator of knowledge.