Clickhouse - A New Entry Into the Data Base and Data Storage Market

Listen to the Data Eng podcast: https://www.dataengineeringpodcast.com/snuba-event-data-warehouse-episode-108/ (11mins in)

• https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/

Transcript

James Cunningham

Yeah, so I'd say as far as all the decisions that we made in order to go into this new platform, one of the biggest leaders was that we had a big push for having environments be kind of like a first class filtration, we had to build a new dimensionality of data across all this denormalized data, essentially doubled the storage that we had. And then we said to ourselves, like all this is great, this looks cool. environments are dope. But what happens we want to add another dimension and have dimension or we're just going to continue to, I guess, like, extrapolate across this data set and eventually end up with 100 terabytes of, you know, five different dimensions of data. So we said ourselves That we kind of needed a flat event model that we'd be able to kind of search across and to ourselves, you know, there are a few other pieces that we want. And on top of that, we want to be able to search across these arbitrary fields that we really, really looked into whether those are custom tags or something that we kind of promote, whether that is like releases or traces or searching across messages. We didn't want that to take as long as it did. And some of the other parts is that we have all this data stored in, you know, this tag store and all these searches that we have to go through. But we have in a completely different side for time series data that again, had to have that dimensionality in it. If we search across these arbitrary fields, the next thing that a customer would ask for is, Hey, can I please see a pretty graph. So if we could boil down that search, and that time series data into the same system, we'd be destroying two systems with one rewrite.

Ted Kaemming
And also like as part of that process, I mean, you kind of always have this Standard checkpoints, you know, like the replication and durability is obviously really important for us ease of maintenance is huge, low cost as well for us. So even that just kind of ruled out some like the hosted magic storage solutions, like those kinds of pressures.

Tobias Macey
And as you were deciding how to architect this new system, can you talk through some of the initial list of possible components that you were evaluating and what the process was for determining whether something was going to stay or go in the final right?

James Cunningham
Yeah, of course. Um, so our first, I guess, thing that we kind of crossed off is no more orientation, Postgres to serve as well, probably wouldn't, you know, we hope that we could engineer a good solution on top of it, but ultimately, we decided we probably needed a different shape of database to get the query across. We've kind of had like, five major options. We had document stores, you know, we had Some sort of Google proprietary blend, because we are completely on GCP. We had, you know, more more generic distributed query stuff, you know, a little bit of Spark, maybe a little bit of presto, we took a look at other distributed databases, we ran a good amount of Cassandra and my old gig. So I know how to run that. And we also said, like, Oh, hey, we could just like, put data down on distance ourselves and not have to worry about this. Some of the other like, serious considered things that we had was a was a column restore some of these other ones that we actually like kick the tires on, was to do we kick the tires on Pino, and Druid. And ultimately, we found click house as a commerce store. And we kind of just started running it. And it was one of the easiest ones to kick the tires on. Some of these other like, I guess, you know, columnar stores built on top of distributed file systems. It really did take a good amount of bricks to put down in order to get to your first query. And some of the things that we wanted was figuring out operational costs on that. We want to be able to iterate across question You wanted to be able to kind of pare down all the dependencies that the service had. You know, while we weren't afraid to run a few JVM, or to run it, you know, a little bit of HDFS, that was something that realistically, I might not want to have to have, you know, an entire engineer dedicated to running something like that. And on the antithesis of that, you know, we can choose some of this Google proprietary blend, but how did it feel to go from having century only require Redis and Postgres to now saying, you can only run the new version on Google? Yeah, as a little bit silly. So we ended up really just getting through an MVP of I think, both Kudo and click house, and one of the one of the biggest ones that really did kick us and for anyone listening, go ahead and correct me if I'm wrong. But one of my memories was that one of our engineers, you know, started loading data into q2, and you didn't really know when it was there. It was great for you know, being able to being able to crunch down about your numbers, but one of our biggest things that you did kind of hint at Is that we do need real time data and to be able to write into this data store, and then to be able to read it on a consistent basis with one of the things we need it, we have the ability to have a feature called alert rules and what you say, hey, only tell me if, you know, any event with the tag, you know, foom in got in and the value equals to what it was only maybe like 10 events in the last hour. And you want to be able to read that pretty quickly so that when that 10th event comes in, you're not waiting minutes until that alert shows up and click houses able to do that. And so that kind of just got its way up to number one.

Ted Kaemming
Yeah, I think also in general, like, at century we try and kind of bias a little bit towards relatively simple solutions. And it seemed like click house there was, at least to us, based on our backgrounds, it seemed more straightforward to get running. And I think that as well. appealed to us quite a bit. The documentation is pretty solid. It's also open source. You know, a lot of us will be but you know, click house has a pretty active repository. They've been very responsive when we've had questions or issues, they're very public about their development plan. So I think a lot of these things just kind of kind of worked out in its favor.

Tobias Macey
Yeah, it's definitely from what I've been able to understand a fairly new entrant into the overall database and data storage market. But I've heard of a few different stories of people using it in fairly high load environments. So I heard about the work that you're doing with Snoop, as far as I understand. CloudFlare is also using it for some of their use cases. And they definitely operate at some pretty massive scale with high data volume. So it seems like a pretty impressive system that has a lot of different capabilities. And I was pretty impressed when I had some of the folks from all tend to be on the podcast A while ago to talk about their experience of working it and working with some of their clients on getting it deployed. And I'm curious what some of the other types of systems you are able to replace with click house were given that you as you said, you have these four different systems that you had to be able to replicate event data to Were you able to collapse them all down into this one storage engine.

Ted Kaemming
Yeah. So like in our code base, the those four different things, the TSP ...