ClickHouse: An open-source column-oriented database management system • Hacker News Board

Re: ClickHouse: An open-source column-oriented database management system

#51

Post by momothereal » Fri, May 28, 2021, 4:02 PM UTC

mason55 wrote: post #50

momothereal wrote: post #13

People who've used Clickhouse or other OLAP databases in production & at scale, how do you "interconnect" it with relational data? I'm currently experimenting with Clickhouse, because my dataflow is increasing in size (40M rows right now, doubling every month or so) and my current setup (MongoDB) is at its limits. I would like to migrate the 40M rows to CH, but I also need the metadata for the rows to be in something…

Really depends what you're trying to do with your OLAP database. If you're using it purely for reporting purposes then you really don't want to interconnect it with your OLTP database to support real-time queries. Reason number one is that you don't want some unexpected analytics workload to suddenly impact your production Postgres. Reason number two is that your analytics data model and your OLTP data model are freq…

Alright, here is an example. My data is a stream of events, each row has the values (event_id, person_id, date). This is the table with 40M rows, with inserts between 2/s and 10/s.

person_id is a foreign ID for a "person table" that fits well in the relational model. A "person" has various attributes (name, DOB, email) as well as a one-to-many relation to "groups". Groups are a collection of users with additional attributes (e.g. group name).

Now, what if I want to answer questions like:

   - how many total events for all persons in group X
   - who are the top 10 users in number of events in group X
   - which are the top 10 groups in number of events

In this case, the person/group tables are part of the core business logic, they aren't specific to the events table. It doesn't make sense to store it in Clickhouse. Also, this person/group data gets updated sparsely, but "freshness" should be kept at a minimum (The simple approach to the first question would be

   - Get all the user IDs in group X
   - Filter events by those user IDs

But what if there are tens of thousands of users in group X? And hundreds of groups? Are megabyte-long queries supported in Clickhouse?

Re: ClickHouse: An open-source column-oriented database management system

#52

Post by mason55 » Fri, May 28, 2021, 4:56 PM UTC

momothereal wrote: post #51

mason55 wrote: post #50

Earlier quoted context omitted.

Really depends what you're trying to do with your OLAP database. If you're using it purely for reporting purposes then you really don't want to interconnect it with your OLTP database to support real-time queries. Reason number one is that you don't want some unexpected analytics workload to suddenly impact your production Postgres. Reason number two is that your analytics data model and your OLTP data model are freq…

Alright, here is an example. My data is a stream of events, each row has the values (event_id, person_id, date). This is the table with 40M rows, with inserts between 2/s and 10/s. person_id is a foreign ID for a "person table" that fits well in the relational model. A "person" has various attributes (name, DOB, email) as well as a one-to-many relation to "groups". Groups are a collection of users with additional att…

Some things to think about

1. Are the questions you're asking completely ad hoc? Or can you mostly define them ahead of time? If it's the former then you should be looking at getting your OLTP data into Clickhouse. If it's the latter then you should be looking to aggregate the data to various levels and get it out of Clickhouse.

All three of your sample questions lend themselves quite nicely to pre-aggregation. I'm sure your actual questions are more complex, but what I'd do to address all three of your examples is every night I'd roll up the raw events into (person_id, date, event_count) and send it back over to Postgres. Then every week you roll up the previous seven days into (person_id, week, event_count). Each month you roll the weeks up and each year you roll the months up. If you need the data more frequently than daily then you can go down to hourly or whatever it is you need.

Now you've got your data back into Postgres but at a reasonable granularity. Depending on the cardinality of the user-*group relationship you might have to do some magic to pre-aggregate that if the join is big as well, which could turn into a challenge as group membership changes (you'd need to re-aggregate all your group metrics any time group membership changed) but it's still better than trying to join across the Clickhouse/Postgres boundary.

If you really do need to support totally ad hoc questions all the time then you should figure out how to get your Postgres data into Clickhouse. If the data really gets update infrequently then it shouldn't be a problem to get changes in user/group membership into Clickhouse quickly, then you can do all your joins and analysis completely in Clickhouse.

2. Do you really want the current group memberships? Or do you want the group memberships at the time the event occurred? It's a subtle difference and there's not usually one right answer (or the answer is "I need both").

Re: ClickHouse: An open-source column-oriented database management system

#53

Post by nexuist » Fri, May 28, 2021, 5:45 PM UTC

_f9cu wrote: post #47

Column oriented - a table.

See the docs: https://clickhouse.tech/docs/en/

> Different orders for storing data are better suited to different scenarios. The data access scenario refers to what queries are made, how often, and in what proportion; how much data is read for each type of query – rows, columns, and bytes; the relationship between reading and updating data; the working size of the data and how locally it is used; whether transactions are used, and how isolated they are; requirements for data replication and logical integrity; requirements for latency and throughput for each type of query, and so on.

Re: ClickHouse: An open-source column-oriented database management system

#54

Post by kirankn » Fri, May 28, 2021, 7:24 PM UTC

momothereal wrote: post #51

mason55 wrote: post #50

Earlier quoted context omitted.

Really depends what you're trying to do with your OLAP database. If you're using it purely for reporting purposes then you really don't want to interconnect it with your OLTP database to support real-time queries. Reason number one is that you don't want some unexpected analytics workload to suddenly impact your production Postgres. Reason number two is that your analytics data model and your OLTP data model are freq…

Alright, here is an example. My data is a stream of events, each row has the values (event_id, person_id, date). This is the table with 40M rows, with inserts between 2/s and 10/s. person_id is a foreign ID for a "person table" that fits well in the relational model. A "person" has various attributes (name, DOB, email) as well as a one-to-many relation to "groups". Groups are a collection of users with additional att…

If you have something like Kafka before you insert your events into CH, you could consider enriching the event data by connecting with your Postgres data. That way, you still retain a flat CH table which should work just fine.

Re: ClickHouse: An open-source column-oriented database management system

#55

Post by hashhar » Sun, May 30, 2021, 3:27 AM UTC

LaserToy wrote: post #49

hashhar wrote: post #30

Earlier quoted context omitted.

Take a look at query engines like Trino (formerly PrestoSQL) [ https://trino.io/ ]. (Disclaimer: I'm a contributor to Trino). I used it at a previous job to combine data from MongoDB, Kafka, S3 and Postgres to great effect. It tries to push-down as many operations as possible to the source too to improve performance. Full ANSI SQL support over multiple number of backends (Kafka, Cassandra, Postgres, ClickHouse, S3 an…

The problem with Trino is that it is not that easy to scale to possible RPS of Clickhouse, it introduces tons a of latency and push downs are faaaar from perfect. Uber has a smart solution for Pinot, when they run it as a single node proxies

I wouldn't use Trino if you are looking to ONLY query a single database like Clickhouse or Postgres etc (unless you want an ANSI SQL abstraction over your choice of database). Obviously ClickHouse and Postgres will have lower latency when hit directly because you can bypass the analysis, planning, optimization and scheudling that Trino does.

It does federation better than ClickHouse and that's where it shines. Joins across disparate systems - even between relational and non-relational systems. And obviously for the MPP queries on distributed filesystems.

Re: ClickHouse: An open-source column-oriented database management system

#56

Post by LaserToy » Mon, May 31, 2021, 4:19 AM UTC

hashhar wrote: post #55

LaserToy wrote: post #49

Earlier quoted context omitted.

The problem with Trino is that it is not that easy to scale to possible RPS of Clickhouse, it introduces tons a of latency and push downs are faaaar from perfect. Uber has a smart solution for Pinot, when they run it as a single node proxies

I wouldn't use Trino if you are looking to ONLY query a single database like Clickhouse or Postgres etc (unless you want an ANSI SQL abstraction over your choice of database). Obviously ClickHouse and Postgres will have lower latency when hit directly because you can bypass the analysis, planning, optimization and scheudling that Trino does. It does federation better than ClickHouse and that's where it shines. Joins…

I know. I worked on Presto :)

Re: ClickHouse: An open-source column-oriented database management system

#57

Post by joshstrange » Tue, Jun 01, 2021, 2:03 PM UTC

qeternity wrote: post #27

pibi wrote: post #15

Great software. We are managing terabytes of stocks data and realtime market scanners queries across all market (billions of books and timesales) with hundreds of concurrent requests. We were using kdb before, but clickhouse is more scalable, way cheaper and much more easy to grasp for a newbie.

You’re ingesting real time data into Clickhouse?

I know it's been a few days but what was the driver behind this question? My company uses CH and has near-real-time data being added (~5min delay from live), is this a problem and/or is there something about CH that doesn't play nice with this method? Or were you just interested in this particular case of inserting real time data?

Re: ClickHouse: An open-source column-oriented database management system

#58

Post by joshstrange » Tue, Jun 01, 2021, 2:05 PM UTC

hocuspocus wrote: post #25

My team is (over|ab)using Elasticsearch and I've had my eye on ClickHouse for a while. However we're going to migrate everything to AWS and I wonder if RedShift could be a good alternative too, since it's now supporting JSON and semi-structured data apparently.

My company has/in-in-the-process of moving from ES to CH and from my (limited) perspective it's been going well. I greatly prefer writing CH queries over ES. I can't speak to RedShift but I wanted to throw in the data point of an ES->CH move.