Theory and practice of using ClickHouse in real applications. Alexander Zaitsev (2018)

Despite the fact that there is now a lot of data almost everywhere, analytical databases are still quite exotic. They are poorly known and even worse able to use them effectively. Many continue to "eat cactus" with MySQL or PostgreSQL, which are designed for other scenarios, suffer with NoSQL, or overpay for commercial solutions. ClickHouse changes the rules of the game and significantly lowers the threshold for entering the world of analytical DBMS.

Report from BackEnd Conf 2018 and it is published with the permission of the speaker.

Who am I and why am I talking about ClickHouse? I'm a development director at LifeStreet, which uses ClickHouse. Also, I'm the founder of Altinity. It is a Yandex partner that promotes ClickHouse and helps Yandex make ClickHouse more successful. Also ready to share knowledge about ClickHouse.

And I'm not the brother of Petya Zaitsev. I am often asked about this. No, we are not brothers.

“Everyone knows” that ClickHouse:

• Very fast,
• Very comfortable
• Used in Yandex.

A little less is known in which companies and how it is used.

I will tell you why, where and how ClickHouse is used, except for Yandex.

I will tell you how specific tasks are solved with the help of ClickHouse in different companies, what ClickHouse tools you can use for your tasks, and how they were used in different companies.

I picked up three examples that show ClickHouse from different angles. I think it will be interesting.

The first question is: “Why do we need ClickHouse?”. It seems to be a fairly obvious question, but there are more than one answer to it.

• The first answer is for performance. ClickHouse is very fast. Analytics on ClickHouse is also very fast. It can often be used where something else is very slow or very bad.
• The second answer is cost. And first of all, the cost of scaling. For example, Vertica is an absolutely great database. It works very well if you don't have a lot of terabytes of data. But when it comes to hundreds of terabytes or petabytes, the cost of a license and support goes into a fairly significant amount. And it's expensive. And ClickHouse is free.
• The third answer is operating cost. This is a slightly different approach. RedShift is a great analog. On RedShift, you can make a decision very quickly. It will work well, but at the same time, every hour, every day, and every month, you will pay Amazon quite dearly, because this is a significantly expensive service. Google BigQuery too. If someone used it, then he knows that there you can run several requests and get a bill for hundreds of dollars all of a sudden.

ClickHouse does not have these problems.

Where is ClickHouse used now? In addition to Yandex, ClickHouse is used in a bunch of different businesses and companies.

• First of all, this is web application analytics, i.e. this is a use case that came from Yandex.
• Many AdTech companies use ClickHouse.
• Numerous companies that need to analyze transaction logs from different sources.
• Several companies use ClickHouse to monitor security logs. They upload them to ClickHouse, make reports, and get the results they need.
• Companies are starting to use it in financial analysis, i.e. gradually big businesses are also approaching ClickHouse.
• cloudflare. If someone follows ClickHouse, then they have probably heard the name of this company. This is one of the essential contributors from the community. And they have a very serious ClickHouse installation. For example, they made Kafka Engine for ClickHouse.
• Telecommunications companies began to use. Several companies use ClickHouse either as proof on concept or already in production.
• One company uses ClickHouse to monitor production processes. They test microcircuits, write off a bunch of parameters, there are about 2 characteristics. And then they analyze whether the game is good or bad.
• Blockchain analytics. There is such a Russian company as Bloxy.info. This is an analysis of the ethereum network. They also did this on ClickHouse.

And the size doesn't matter. There are many companies that use one small server. And he allows them to solve their problems. And even more companies use large clusters of many servers or dozens of servers.

And if you look at the records, then:

• Yandex: 500+ servers, they store 25 billion records a day there.
• LifeStreet: 60 servers, approximately 75 billion records per day. There are fewer servers, more records than in Yandex.
• CloudFlare: 36 servers, they save 200 billion records a day. They have even fewer servers and store even more data.
• Bloomberg: 102 servers, about a trillion entries per day. Record holder.

Geographically, this is also a lot. This map here shows a heatmap of where ClickHouse is being used in the world. Russia, China, America stand out clearly here. There are few European countries. And there are 4 clusters.

This is a comparative analysis, there is no need to look for absolute figures. This is an analysis of visitors who read English-language materials on the Altinity website, because there are no Russian-speaking ones there. And Russia, Ukraine, Belarus, i.e. the Russian-speaking part of the community, these are the most numerous users. Then comes the US and Canada. China is very much catching up. There was almost no China there six months ago, now China has already overtaken Europe and continues to grow. Old Europe is also not far behind, and the leader in the use of ClickHouse is, oddly enough, France.

Why am I telling all this? To show that ClickHouse is becoming a standard solution for big data analysis and is already used in a lot of places. If you use it, you are in the right trend. If you are not using it yet, then you can not be afraid that you will be left alone and no one will help you, because many are already doing this.

These are examples of real ClickHouse use in several companies.

• The first example is an ad network: migration from Vertica to ClickHouse. And I know a few companies that have transitioned from Vertica or are in the process of transitioning.
• The second example is transactional storage on ClickHouse. This is an example built on antipatterns. Everything that should not be done in ClickHouse on the advice of developers is done here. And it's done so effectively that it works. And it works much better than the typical transactional solution.
• The third example is distributed computing on ClickHouse. There was a question about how ClickHouse can be integrated into the Hadoop ecosystem. I will show an example of how a company did something similar to a map reduce container on ClickHouse, keeping track of data localization, etc., to calculate a very non-trivial task.

• LifeStreet is an Ad Tech company that has all the technology that comes with an ad network.
• She is engaged in ad optimization, programmatic bidding.
• Lots of data: about 10 billion events per day. At the same time, events there can be divided into several sub-events.
• There are many clients of this data, and these are not only people, much more - these are various algorithms that are engaged in programmatic bidding.

The company has come a long and thorny path. And I talked about it on HighLoad. First, LifeStreet moved from MySQL (with a short stop at Oracle) to Vertica. And you can find a story about it.

And everything was very good, but it quickly became clear that the data is growing and Vertica is expensive. Therefore, various alternatives were sought. Some of them are listed here. And in fact, we did proof of concept or sometimes performance testing of almost all databases that were available on the market from the 13th to the 16th year and were approximately suitable in terms of functionality. And I also talked about some of them on HighLoad.

The task was to migrate from Vertica in the first place, because the data grew. And they grew exponentially over the years. Then they went on the shelf, but nonetheless. And predicting this growth, business requirements for the amount of data on which some kind of analytics needed to be done, it was clear that petabytes would soon be discussed. And paying for petabytes is already very expensive, so we were looking for an alternative where to go.

Where to go? And for a long time it was not at all clear where to go, because on the one hand there are commercial databases, they seem to work well. Some work almost as well as Vertica, some worse. But they are all expensive, nothing cheaper and better could not be found.

On the other hand, there are open source solutions, which are not very numerous, i.e. for analytics, they can be counted on the fingers. And they are free or cheap, but slow. And they often lack the necessary and useful functionality.

And there was nothing to combine the good that is in commercial databases and all the free that is in open source.

There was nothing until, unexpectedly, Yandex pulled out ClickHouse, like a magician from a hat, like a rabbit. And it was an unexpected decision, they still ask the question: “Why?”, But nevertheless.

And right away in the summer of 2016, we began to look at what ClickHouse is. And it turned out that sometimes it can be faster than Vertica. We tested different scenarios on different requests. And if the query used only one table, that is, without any joins (join), then ClickHouse was twice as fast as Vertica.

I was not too lazy and looked at Yandex tests the other day. It's the same there: ClickHouse is twice as fast as Vertica, so they often talk about it.

But if there are joins in the queries, then everything turns out not very unambiguously. And ClickHouse can be twice as slow as Vertica. And if you slightly correct the request and rewrite it, then they are approximately equal. Not bad. And free.

And having received the test results, and looking at it from different angles, LifeStreet went to ClickHouse.

This is the 16th year, I remind you. It was like a joke about mice that cried and pricked themselves, but continued to eat the cactus. And this was described in detail, there is a video about this, etc.

Therefore, I will not talk about it in detail, I will only talk about the results and a few interesting things that I did not talk about then.

The results are:

• Successful migration and more than a year the system is already working in production.
• Productivity and flexibility have increased. Of the 10 billion records that we could afford to store per day and then for a short time, LifeStreet now stores 75 billion records per day and can do this for 3 months or more. If you count at the peak, then this is up to a million events per second. More than a million SQL queries a day arrive in this system, mostly from different robots.
• Despite the fact that more servers were used for ClickHouse than for Vertica, they also saved on hardware, because rather expensive SAS disks were used in Vertica. ClickHouse used SATA. And why? Because in Vertica insert is synchronous. And synchronization requires that the disks do not slow down too much, and also that the network does not slow down too much, that is, a rather expensive operation. And in ClickHouse insert is asynchronous. Moreover, you can always write everything locally, there are no additional costs for this, so data can be inserted into ClickHouse much faster than into Vertika, even on slower drives. And reading is about the same. Reading on SATA, if they are in RAID, then this is all fast enough.
• Not limited by license, i.e. 3 petabytes of data in 60 servers (20 servers is one replica) and 6 trillion records in facts and aggregations. Nothing like this could be afforded at Vertica.

I now turn to practical things in this example.

• The first is an efficient scheme. A lot depends on the schema.
• The second is efficient SQL generation.

A typical OLAP query is a select. Some of the columns go to group by, some of the columns go to aggregate functions. There is where, which can be represented as a slice of a cube. The entire group by can be thought of as a projection. And that's why it's called multivariate data analysis.

And often this is modeled in the form of a star-scheme, when there is a central fact and characteristics of this fact along the sides, along the rays.

And in terms of physical design, how it fits on the table, they usually do a normalized representation. You can denormalize, but it's expensive on disk and not very efficient on queries. Therefore, they usually make a normalized representation, i.e. a fact table and many, many dimension tables.

But it doesn't work well in ClickHouse. There are two reasons:

• The first is because ClickHouse has not very good joins, i.e. there are joins, but they are bad. While bad.
• The second is that the tables are not updated. Usually in these plates, which are around the star-circuit, something needs to be changed. For example, customer name, company name, etc. And it doesn't work.

And there is a way out of this in ClickHouse. even two:

• The first is the use of dictionaries. External Dictionaries is what helps 99% solve the problem with the star-schema, with updates and so on.
• The second is the use of arrays. Arrays also help to get rid of joins and problems with normalization.

• No join required.
• Upgradable. Since March 2018, an undocumented opportunity has appeared (you will not find this in the documentation) to update dictionaries partially, i.e. those entries that have changed. Practically, it is like a table.
• Always in memory, so joins with a dictionary work faster than if it were a table that is on disk and it is not yet a fact that it is in the cache, most likely not.

• You don't need joins either.
• This is a compact 1-to-many representation.
• And in my opinion, arrays are made for geeks. These are lambda functions and so on.

This is not for red words. This is a very powerful functionality that allows you to do many things in a very simple and elegant way.

Typical examples that help solve arrays. These examples are simple and clear enough:

• Search by tags. If you have hashtags there and want to find some posts by hashtag.
• Search by key-value pairs. There are also some attributes with a value.
• Storing lists of keys that you need to translate into something else.

All these tasks can be solved without arrays. Tags can be put in some line and selected with a regular expression or in a separate table, but then you have to do joins.

And in ClickHouse, you don’t need to do anything, it’s enough to describe the string array for hashtags or make a nested structure for key-value systems.

Nested structure may not be the best name. These are two arrays that have a common part in the name and some related characteristics.

And it's very easy to search by tag. Have a function has, which checks that the array contains an element. Everyone, found all the entries that relate to our conference.

Search by subid is a bit more complicated. We need to first find the index of the key, and then take the element with this index and check that this value is what we need. However, it is very simple and compact.

The regular expression that you would like to write if you kept it all in one line, it would be, firstly, clumsy. And, secondly, it worked much longer than two arrays.

Another example. You have an array where you store the ID. And you can translate them into names. Function arrayMap. This is a typical lambda function. You pass lambda expressions there. And she pulls out the value of the name for each ID from the dictionary.

Search can be done in the same way. A predicate function is passed that checks what the elements match.

These things greatly simplify the circuit and solve a bunch of problems.

But the next problem we're facing, and which I'd like to mention, is efficient queries.

• ClickHouse does not have a query planner. Absolutely not.
• Nevertheless, complex queries still need to be planned. In which cases?
• If there are multiple joins in the query, you wrap them in subselects. And the order in which they are executed matters.
• And the second - if the request is distributed. Because in a distributed query, only the innermost subselect is executed distributed, and everything else is passed to one server that you connected to and executed there. Therefore, if you have distributed queries with many joins (join), then you need to choose the order.

And even in simpler cases, sometimes it is also necessary to do the work of the scheduler and rewrite queries a little.

Here is an example. On the left side is a query that shows the top 5 countries. And it takes 2,5 seconds, in my opinion. And on the right side, the same query, but slightly rewritten. Instead of grouping by string, we began to group by key (int). And it's faster. And then we connected a dictionary to the result. Instead of 2,5 seconds, the request takes 1,5 seconds. This is good.

A similar example with rewriting filters. Here is a request for Russia. It runs for 5 seconds. If we rewrite it in such a way that we compare again not a string, but numbers with some set of those keys that relate to Russia, then it will be much faster.

There are many such tricks. And they allow you to significantly speed up queries that you think are already running fast, or, conversely, running slowly. They can be made even faster.

• Maximum work in distributed mode.
• Sorting by minimum types, as I did by ints.
• If there are any joins (join), dictionaries, then it is better to do them as a last resort, when you already have data at least partially grouped, then the join operation or dictionary call will be called less times and it will be faster.
• Replacing filters.

There are other techniques, and not just those that I have demonstrated. And all of them can sometimes significantly speed up the execution of queries.

Let's move on to the next example. Company X from USA. What is she doing?

There was a task:

• Offline linking of advertising transactions.
• Modeling different binding models.

What is the scenario?

An ordinary visitor comes to the site, for example, 20 times a month from different ads, or just like that sometimes comes without any ads, because he remembers this site. Looks at some products, puts them in the basket, takes them out of the basket. And, in the end, something buys.

Reasonable questions: "Who should pay for advertising, if necessary?" and “What advertising influenced him, if any?”. That is, why did he buy and how to get people like this person to buy too?

In order to solve this problem, you need to connect the events that occur on the website in the right way, that is, somehow build a connection between them. Then they are sent for analysis to DWH. And based on this analysis, build models of who and what ads to show.

An ad transaction is a set of related user events that start from showing an ad, then something happens, then maybe a purchase, and then there may be purchases within a purchase. For example, if this is a mobile application or a mobile game, then usually the installation of the application takes place for free, and if something is done there, then money may be required for this. And the more a person spends in the application, the more valuable it is. But for this you need to connect everything.

There are many binding models.

The most popular are:

• Last Interaction, where interaction is either a click or an impression.
• First Interaction, i.e. the first thing that brought a person to the site.
• Linear combination - all equally.
• Attenuation.
• Etc.

And how did it all work in the first place? There was Runtime and Cassandra. Cassandra was used as transaction storage, i.e. all related transactions were stored in it. And when some event comes in Runtime, for example, showing some page or something else, then a request was made to Cassandra - is there such a person or not. Then the transactions that relate to it were obtained. And the connection was made.

And if it's lucky that the request has a transaction id, then it's easy. But usually no luck. Therefore, it was necessary to find the last transaction or the transaction with the last click, etc.

And it all worked very well as long as the binding was to the last click. Because there are, say, 10 million clicks per day, 300 million per month, if we set a window for a month. And since in Cassandra it has to be all in memory in order to run fast, because the Runtime needs to respond quickly, it took about 10-15 servers.

And when they wanted to link a transaction to the display, it immediately turned out not so fun. And why? It can be seen that 30 times more events need to be stored. And, accordingly, you need 30 times more servers. And it turns out that this is some kind of astronomical figure. To keep up to 500 servers in order to do the linking, despite the fact that there are significantly fewer servers in Runtime, then this is some kind of wrong figure. And they began to think what to do.

And we went to ClickHouse. And how to do it on ClickHouse? At first glance, it seems that this is a set of anti-patterns.

• The transaction grows, we hook up more and more events to it, i.e. it is mutable, and ClickHouse does not work very well with mutable objects.
• When a visitor comes to us, we need to pull out his transactions by key, by his visit id. This is also a point query, they don't do that in ClickHouse. Usually ClickHouse has big …scans, but here we need to get some records. Also an antipattern.
• In addition, the transaction was in json, but they didn’t want to rewrite it, so they wanted to store json in an unstructured way, and if necessary, pull something out of it. And this is also an antipattern.

That is, a set of antipatterns.

But nevertheless it turned out to make a system that worked very well.

What was done? ClickHouse appeared, into which logs were thrown, divided into records. A attributed service appeared that received logs from ClickHouse. After that, for each entry, by visit id, I received transactions that might not have been processed yet and plus snapshots, i.e. transactions already connected, namely the result of previous work. I already made logic out of them, chose the correct transaction, connected new events. Logged again. The log went back to ClickHouse, i.e. it is a constantly cyclical system. And besides, I went to DWH to analyze it there.

It was in this form that it did not work very well. And to make it easier for ClickHouse, when there was a request by visit id, they grouped these requests into blocks of 1-000 visit ids and pulled out all transactions for 2-000 people. And then it all worked.

If you look inside ClickHouse, then there are only 3 main tables that serve all this.

The first table into which logs are uploaded, and the logs are uploaded almost without processing.

Second table. Through the materialized view, from these logs, events that have not yet been attributed, i.e., unrelated ones, were bitten out. And through the materialized view, transactions were pulled out of these logs to build a snapshot. That is, a special materialized view built a snapshot, namely the last accumulated state of the transaction.

Here is the text written in SQL. I would like to comment on a few important things in it.

The first important thing is the ability to pull out columns and fields from json in ClickHouse. That is, ClickHouse has some methods for working with json. They are very, very primitive.

visitParamExtractInt allows you to extract attributes from json, i.e. the first hit works. And this way you can pull out transaction id or visit id. This time.

Secondly, a tricky materialized field is used here. What does it mean? This means that you cannot insert it into the table, i.e. it is not inserted, it is calculated and stored upon insertion. When pasting, ClickHouse does the work for you. And what you need later is already pulled out of json.

In this case, materialized view is for raw rows. And the first table with practically raw logs is just used. And what does he do? Firstly, it changes the sorting, i.e. sorting now goes by visit id, because we need to quickly pull out his transaction for a specific person.

The second important thing is index_granularity. If you've seen MergeTree, it's usually 8 by default index_granularity. What it is? This is the index sparseness parameter. In ClickHouse the index is sparse, it never indexes every entry. It does this every 192. And this is good when a lot of data is required to be calculated, but bad when a little, because there is a large overhead. And if we reduce the index granularity, then we reduce the overhead. It cannot be reduced to one, because there may not be enough memory. The index is always stored in memory.

Snapshot also uses some other interesting ClickHouse features.

First, it is AggregatingMergeTree. And AggregatingMergeTree stores argMax, i.e. this is the state of the transaction corresponding to the last timestamp. Transactions are generated all the time for a given visitor. And in the very last state of this transaction, we added an event and we have a new state. It hit ClickHouse again. And through argMax in this materialized view, we can always get the current state.

• The binding is "decoupled" from the Runtime.
• Up to 3 billion transactions per month are stored and processed. This is an order of magnitude more than it was in Cassandra, i.e. in a typical transactional system.
• Cluster of 2x5 ClickHouse servers. 5 servers and each server has a replica. This is even less than it was in Cassandra in order to do click based attribution, and here we have impression based. That is, instead of increasing the number of servers by 30 times, they managed to reduce them.

And the last example is financial company Y, which analyzed the correlations of changes in stock prices.

And the task was:

• There are approximately 5 shares.
• Quotes every 100 milliseconds are known.
• The data has been accumulated over 10 years. Apparently, for some companies more, for some less.
• There are approximately 100 billion rows in total.

And it was necessary to calculate the correlation of changes.

Here are two stocks and their quotes. If one goes up and the other goes up, then this is a positive correlation, i.e. one goes up and the other goes up. If one goes up, as at the end of the graph, and the other goes down, then this is a negative correlation, i.e. when one rises, the other falls.

Analyzing these mutual changes, one can make predictions in the financial market.

But the task is difficult. What is being done for this? We have 100 billion records that have: time, stock, and price. We need to calculate first 100 billion times the runningDifference from the price algorithm. RunningDifference is a function in ClickHouse that sequentially calculates the difference between two strings.

And after that, you need to calculate the correlation, and the correlation must be calculated for each pair. For 5 shares, pairs are 000 million. And this is a lot, i.e. 12,5 times it is necessary to calculate just such a correlation function.

And if someone forgot, then ͞x and ͞y is a checkmate. sampling expectation. That is, it is necessary not only to calculate the roots and sums, but also one more sums inside these sums. A bunch of calculations need to be done 12,5 million times, and even grouped by hours. We also have a lot of hours. And you have to do it in 60 seconds. It's a joke.

It was necessary to have time at least somehow, because all this worked very, very slowly before ClickHouse came.

They tried to calculate it on Hadoop, on Spark, on Greenplum. And all this was very slow or expensive. That is, it was possible to somehow calculate, but then it was expensive.

And then ClickHouse came along and things got much better.

I remind you that we have a problem with data locality, because correlations cannot be localized. We cannot put some of the data on one server, some on another and calculate, we must have all the data everywhere.

What did they do? Initially, the data is localized. Each server stores data on the pricing of a certain set of shares. And they don't overlap. Therefore, it is possible to calculate logReturn in parallel and independently, all this happens so far in parallel and distributed.

Then we decided to reduce these data, while not losing expressiveness. Reduce using arrays, i.e. for each period of time, make an array of stocks and an array of prices. Therefore, it takes up much less data space. And they are a little easier to work with. These are almost parallel operations, i.e. we partially read in parallel and then write to the server.

After that, it can be replicated. The letter "r" means that we replicated this data. That is, we have the same data on all three servers - these are the arrays.

And then with a special script from this set of 12,5 million correlations that need to be calculated, you can make packages. That is, 2 tasks with 500 pairs of correlations. And this task is to be calculated on a specific ClickHouse server. He has all the data, because the data is the same and he can calculate them sequentially.

Once again, this is what it looks like. First, we have all the data in this structure: time, shares, price. Then we calculated logReturn, i.e. data of the same structure, but instead of the price we already have logReturn. Then they were redone, i.e. we got the time and groupArray for stocks and prices. Sreplicated. And after that, we generated a bunch of tasks and fed them to ClickHouse so that it would count them. And it works.

On proof of concept, the task was a subtask, i.e., less data was taken. And only three servers.

These first two stages: calculating Log_return and wrapping in arrays took about an hour.

And the calculation of the correlation is about 50 hours. But 50 hours is not enough, because they used to work for weeks. It was a big success. And if you count, then 70 times per second everything was counted on this cluster.

But the most important thing is that this system is practically without bottlenecks, i.e., it scales almost linearly. And they checked it out. Successfully scaled it up.

• The right scheme is half the success. And the right scheme is the use of all the necessary ClickHouse technologies.
• Summing/AggregatingMergeTrees are technologies that allow you to aggregate or consider a state snapshot as a special case. And it greatly simplifies a lot of things.
• Materialized Views allow you to bypass the one index limit. Maybe I didn’t say it very clearly, but when we loaded the logs, the raw logs were in the table with one index, and the attribute logs were in the table, i.e. the same data, only filtered, but the index was completely others. It seems to be the same data, but different sorting. And Materialized Views allows you, if you need it, to bypass such a ClickHouse limitation.
• Reduce index granularity for point queries.
• And distribute the data smartly, try to localize the data within the server as much as possible. And try to ensure that requests also use localization where possible as much as possible.

And summing up this short speech, we can say that ClickHouse has now firmly occupied the territory of both commercial databases and open source databases, i.e., specifically for analytics. He fits perfectly into this landscape. And what's more, it slowly starts to crowd out others, because when you have ClickHouse, you don't need InfiniDB. Vertika may not be needed soon if they make normal SQL support. Enjoy!

—Thanks for the report! Very interesting! Were there any comparisons with Apache Phoenix?

No, I haven't heard anyone compare. We and Yandex try to keep track of all ClickHouse comparisons with different databases. Because if suddenly something turns out to be faster than ClickHouse, then Lesha Milovidov cannot sleep at night and starts to quickly speed it up. I have not heard of such a comparison.

• (Aleksey Milovidov) Apache Phoenix is ​​a SQL engine powered by Hbase. Hbase is mainly for key-value work scenario. There, in each line, there can be an arbitrary number of columns with arbitrary names. This can be said about such systems as Hbase, Cassandra. And it is precisely heavy analytical queries that will not work normally for them. Or you might think that they work fine if you haven't had any experience with ClickHouse.

• Thank you

  • Good afternoon I am already quite interested in this topic, because I have an analytical subsystem. But when I look at ClickHouse, I get the feeling that ClickHouse is very well suited for event analysis, mutable. And if I need to analyze a lot of business data with a bunch of large tables, then ClickHouse, as far as I understand, is not very suitable for me? Especially if they change. Is this correct or are there examples that can refute this?

  • This is right. And this is true of most specialized analytical databases. They are tailored for the fact that there are one or more large tables that are mutable, and for many small ones that change slowly. That is, ClickHouse is not like Oracle, where you can put everything and build some very complex queries. In order to use ClickHouse effectively, you need to build a scheme in a way that works well in ClickHouse. That is, avoid excessive normalization, use dictionaries, try to make fewer long links. And if the schema is built in this way, then similar business tasks can be solved on ClickHouse much more efficiently than on a traditional relational database.

Thanks for the report! I have a question about the latest financial case. They had analytics. It was necessary to compare how they go up and down. And I understand that you built the system specifically for this analytics? If tomorrow, for example, they need some other report on this data, do they need to re-build the schema and upload the data? That is, to do some kind of preprocessing to get the request?

Of course, this is the use of ClickHouse for a very specific task. It could more traditionally be solved within Hadoop. For Hadoop, this is an ideal task. But on Hadoop it is very slow. And my goal is to demonstrate that ClickHouse can solve tasks that are usually solved by completely different means, but at the same time do it much more efficiently. It's tailored for a specific task. It is clear that if there is a problem with something similar, then it can be solved in a similar way.

It's clear. You said that 50 hours were processed. Is it from the very beginning, when did you load the data or get the results?

Yes Yes.

OK thank you very much.

This is on a 3 server cluster.

Greetings! Thanks for the report! Everything is very interesting. I will not ask a little about the functionality, but about the use of ClickHouse in terms of stability. That is, did you have any, did you have to restore? How does ClickHouse behave in this case? And did it happen that you had a replica as well? For example, we encountered a problem with ClickHouse when it still gets out of its limit and falls.

Of course, there are no ideal systems. And ClickHouse also has its own problems. But have you heard about Yandex.Metrica not working for a long time? Probably not. It has been working reliably since 2012-2013 on ClickHouse. I can say the same about my experience. We have never had complete failures. Some partial things could happen, but they were never critical enough to seriously affect the business. It never happened. ClickHouse is quite reliable and does not crash randomly. You don't have to worry about it. It's not a raw thing. This has been proven by many companies.

Hello! You said that you need to think over the data schema right away. What if it happened? My data is pouring and pouring. Six months pass, and I understand that it’s impossible to live like this, I need to re-upload the data and do something with them.

This depends of course on your system. There are several ways to do this with virtually no stop. For example, you can create a Materialized View in which to make a different data structure if it can be uniquely mapped. That is, if it allows mapping using ClickHouse, i.e. extract some things, change the primary key, change partitioning, then you can make a Materialized View. Overwrite your old data there, new ones will be written automatically. And then just switch to using the Materialized View, then switch the record and kill the old table. This is generally a non-stop method.

Thank you.

Source: habr.com