Pi-KVM — an open IP-KVM project on Raspberry Pi

Despite the abundance of data nearly everywhere, analytical databases are still quite exotic. They are poorly understood and even less effectively utilized. Many continue to "eat the cactus" with MySQL or PostgreSQL, which are designed for different scenarios, struggle with NoSQL, or pay too much for commercial solutions. ClickHouse changes the game and significantly lowers the barrier to entry into the world of analytical DBMS.

This report is from BackEnd Conf 2018 and is published with the speaker's permission.

Who am I and why am I talking about ClickHouse? I am the Director of Development at LifeStreet, a company that uses ClickHouse. Additionally, I am the founder of Altinity. This is a partner of Yandex, promoting ClickHouse and helping Yandex make ClickHouse more successful. I am also ready to share knowledge about ClickHouse.

And no, I am not the brother of Petya Zaytsev. I am often asked about this. No, we are not brothers.

"It is well known" that ClickHouse is:

• Very fast,
• Very convenient,
• Used at Yandex.

It is somewhat less known in which companies and how it is used.

I will tell you for what, where, and how ClickHouse is used, besides Yandex.

I will explain how specific tasks are solved using ClickHouse in different companies, what tools ClickHouse you can use for your tasks, and how they have been used in various companies.

I have selected three examples that showcase ClickHouse from different perspectives. I think this will be interesting.

The first question is: "Why is ClickHouse needed?" 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 works very slowly or very poorly.
• The second answer is cost. First and foremost, the cost of scaling. For example, Vertica is a completely excellent database. It works very well if you don't have too many terabytes of data. But when it comes to hundreds of terabytes or petabytes, the cost of licensing and support becomes quite substantial. And that is expensive. Whereas ClickHouse is free.
• The third response is the operational cost. This approach takes a slightly different angle. RedShift is a great alternative. You can quickly implement a solution on RedShift. It will work well, but every hour, every day, and every month, you'll be paying Amazon a significant amount because it's a considerably expensive service. The same goes for Google BigQuery. Anyone who has used it knows that you can run several queries and suddenly receive a bill for hundreds of dollars.

These issues do not exist in ClickHouse.

Where is ClickHouse currently used? Besides Yandex, ClickHouse is employed by a multitude of different businesses and companies.

• Primarily, it is used for web application analytics, i.e., this use case originated from Yandex.
• Many AdTech companies utilize ClickHouse.
• Numerous companies that need to analyze operational logs from various sources.
• Several companies use ClickHouse for monitoring security logs. They upload them to ClickHouse, generate reports, and obtain the results they need.
• Companies are beginning to use it for financial analysis, meaning that gradually larger businesses are also adopting ClickHouse.
• CloudFlare. If anyone has been following ClickHouse, they have likely heard of this company. They are a significant contributor from the community and have a very serious ClickHouse installation. For example, they created the Kafka Engine for ClickHouse.
• Telecommunications companies have started to use it. Several companies use ClickHouse either as a proof of concept or already in production.
• One company uses ClickHouse to monitor manufacturing processes. They test chips, logging a multitude of parameters, around 2,000 characteristics. They then analyze whether it's a good batch or a bad one.
• Blockchain analytics. There is a Russian company called Bloxy.info. They analyze the Ethereum network. They also implemented this on ClickHouse.

Moreover, size does not matter. Many companies use a single small server, which allows them to solve their problems. Even more companies utilize large clusters of many servers or dozens of servers.

And looking at the records:

• Yandex: 500+ servers, storing 25 billion records per day.
• LifeStreet: 60 servers, approximately 75 billion records per day. Fewer servers, but more records than Yandex.
• CloudFlare: 36 servers, they retain 200 billion records per day. They have even fewer servers and even more data that they store.
• Bloomberg: 102 server, approximately one trillion records per day. A record-holder for number of records.

Geographically, this is quite a lot as well. This map shows a heatmap of where ClickHouse is used around the world. Russia, China, and America stand out prominently. There are few European countries. Four clusters can be identified.

This is a comparative analysis; there is no need to look for absolute figures. This analysis is of visitors who read English-language materials on the Altinity website, as there are no Russian-speaking visitors. Russia, Ukraine, and Belarus, i.e., the Russian-speaking part of the community, are the most numerous users. The USA and Canada follow. China is catching up quickly. Six months ago, there was almost no representation from China; now it has surpassed Europe and continues to grow. Old Europe is also not far behind, with France surprisingly leading in ClickHouse usage.

Why am I telling all this? To show that ClickHouse is becoming the standard solution for big data analysis and is already widely used in many places. If you are using it, you are on the right trend. If you are not using it yet, there is no need to worry that you will be left alone and no one will help you, as many people are already engaged with it.

These are examples of real-world usage of ClickHouse in several companies.

• The first example is an ad network: migration from Vertica to ClickHouse. I know several companies that have transitioned from Vertica or are in the process of doing so.
• The second example is a transactional storage solution built on ClickHouse. This example is built on anti-patterns. Everything that should not be done in ClickHouse, according to developer recommendations, has been done here. Despite this, it works so efficiently that it performs much better than a 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 created something akin to a map-reduce container on ClickHouse, ensuring data localization, among other things, to tackle a very non-trivial task.

• LifeStreet – It is an Ad Tech company with all the technologies related to the ad network.
• It focuses on ad optimization and programmatic bidding.
• A lot of data: around 10 billion events per day. Additionally, these events can be divided into several sub-events.
• Many clients use this data, and it’s not just people; there are many more – various algorithms engaged in programmatic bidding.

The company has undergone a long and challenging journey. I spoke about it at HighLoad. Initially, LifeStreet migrated from MySQL (with a brief pause at Oracle) to Vertica. You can find a story about it.

Everything was going very well, but it quickly became clear that data was growing and Vertica was expensive. Therefore, various alternatives were sought. Some of them are listed here. In fact, we conducted proof of concept or sometimes performance testing on almost all databases available on the market from 2013 to 2016 that approximately met the functionality. I also discussed some of them at HighLoad.

The task was to migrate from Vertica primarily because the data was growing. And it grew exponentially for several years. Then it plateaued, but nonetheless. Predicting this growth, the business requirements for the volume of data needed for analytics made it clear that discussions about petabytes were forthcoming. And paying for petabytes is already very expensive, so alternatives were sought.

Where to go? For a long time, it was completely unclear where to turn because, on one hand, there are commercial databases that seem to work quite well. Some perform almost as well as Vertica, while others are not as good. But they are all expensive; nothing cheaper and better could be found.

On the other hand, there are open source solutions, but there aren’t many of them, meaning for analytics they can be counted on one hand. They are free or cheap but operate slowly, and they often lack the necessary and useful functionality.

Thus, there was nothing that combined the advantages of commercial databases with all the free offerings available in open source.

There was nothing until Yandex unexpectedly pulled ClickHouse out like a rabbit from a magician's hat. It was an unexpected solution, and people still ask: 'Why?', but nevertheless.

And right in the summer of 2016, we started to look into what ClickHouse was. It turned out that sometimes it could be faster than Vertica. We tested different scenarios on various queries. If a query only used one table, i.e., without any joins, then ClickHouse was twice as fast as Vertica.

I took the time to check out some tests from Yandex recently. The same results: ClickHouse is twice as fast as Vertica, which is why they often mention it.

However, if the queries involve joins, the results are not straightforward. ClickHouse can be twice as slow as Vertica. But if you tweak the query a bit and rewrite it, they are roughly equal. Not bad. And free.

After obtaining the test results and looking at it from different angles, LifeStreet decided to go with ClickHouse.

This was in 2016, just to remind you. It reminded me of the joke about the mice who cried and pricked themselves but continued to eat the cactus. This was discussed in detail, including a video about it, etc.

Therefore, I won't go into detail about this, I'll only share the results and a few interesting points that I didn't mention back then.

The results are as follows:

• A successful migration, and the system has been running in production for over a year now.
• Performance and flexibility have increased. From the 10 billion records we could afford to store per day, and not for long, LifeStreet now stores 75 billion records a day and can maintain this for 3 months or more. At peak times, this results in up to a million events stored per second. More than a million SQL queries arrive in this system every day, primarily from various bots.
• Despite the fact that more servers are now being used for ClickHouse than for Vertica, there has been savings on hardware because Vertica used quite expensive SAS drives. ClickHouse employed SATA drives. Why? Because in Vertica, inserts are synchronous. The synchronization requires the disks not to lag significantly, as well as the network not to slow down, which makes it a rather expensive operation. In ClickHouse, inserts are asynchronous. Moreover, you can always write locally without any additional costs, which means that data can be inserted into ClickHouse much faster than into Vertica even on not the fastest disks. Reading performance is approximately the same. Reading on SATA, if they are in a RAID setup, is quite fast.
• Unlimited by license, i.e. 3 petabytes of data across 60 servers (20 servers make up one replica) and 6 trillion records in facts and aggregates. Nothing like this could be afforded with Vertica.

Now I will move on to practical aspects in this example.

• The first is an effective schema. Much depends on the schema.
• The second is generating efficient SQL.

A typical OLAP query is a select. Some columns go into group by, while some columns go into aggregate functions. There is a where clause, which can be seen as a slice of the cube. The entire group by can be viewed as a projection. Therefore, this is called multidimensional data analysis.

Often, this is modeled as a star schema, where there is a central fact and characteristics of this fact radiating outwards.

From the perspective of physical design, how it lays out on the table, a normalized representation is usually created. You can denormalize, but it is costly in terms of disk space and not very efficient for queries. Thus, a normalized representation is typically made, i.e. a fact table and many dimension tables.

However, this does not work well in ClickHouse. There are two reasons:

• The first is that ClickHouse has poor joins, i.e. joins are present, but they are ineffective. Currently, they are subpar.
• The second reason is that tables are not updated. Usually, in these tables surrounding the star schema, something needs to be changed. For example, the customer name, company name, and so on. And this does not work.

However, there is a solution in ClickHouse. In fact, there are two solutions:

• The first is the use of dictionaries. External Dictionaries help to solve the problems associated with the star schema, including updates, in 99% of cases.
• The second is the use of arrays. Arrays also help eliminate joins and problems with normalization.

• No join is needed.
• Updateable. Since March 2018, an undocumented feature (you won't find it in the documentation) has been available to partially update dictionaries, i.e. only the records that have changed. Essentially, this functions like a table.
• Always in memory, therefore joins with dictionaries work faster than if it were a table stored on disk, which may not even be cached, and most likely isn't.

• Also, no join is needed.
• This is a compact representation of one to many.
• In my opinion, arrays are made for geeks. This includes lambda functions and more.

This is not just for show. It is a very powerful functionality that allows you to accomplish many things very simply and elegantly.

Typical examples that help solve arrays. These examples are simple and quite illustrative:

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

All these tasks can be solved without arrays. Tags can be placed in a single line and selected using a regular expression or stored in a separate table, but then you will have to do joins.

In ClickHouse, you don't need to do anything; just describe an array of strings for hashtags or create a nested structure for systems like key-value.

A nested structure may not be the most suitable name. It’s two arrays that have a common part in the name and some related characteristics.

And searching by tag is very simple. There is a function has, which checks if an element exists in the array. That's it, we've found all the entries related to our conference.

Searching by subid is slightly more complex. First, we need to find the index of the key, and then take the element at that index and check if that value is what we need. Nevertheless, it's very simple and compact.

The regular expression you would want to write if you stored everything in one line would be, firstly, awkward. And, secondly, would take much longer to execute than two arrays.

Another example. You have an array where you store IDs. And you can convert them to names. The function arrayMap. This is a typical lambda function. You pass lambda expressions to it. And it retrieves the name value for each ID from the dictionary.

Similarly, searching can also be done. A predicate function is passed that checks what the elements correspond to.

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

But the next problem we encountered and that I would like to mention is efficient queries.

• There is no query planner in ClickHouse. Not at all.
• However, complex queries still need to be planned. In what cases?
• If there are multiple joins in the query that you are wrapping in subselects, the order in which they are executed matters.
• Secondly, if the query is distributed. Because in a distributed query, only the innermost subselect is executed in a distributed manner, while everything else is sent to a single server that you are connected to and executed there. Therefore, if you have distributed queries with many joins, you need to choose the order.

Even in simpler cases, it's sometimes advisable to do the work of the planner and rewrite the queries a little.

Here is an example. On the left, there's a query showing the top 5 countries. It runs for about 2.5 seconds, I think. And on the right is the same query, but slightly rewritten. Instead of grouping by string, we started grouping by key (int). And this is faster. Then we joined a dictionary to the result. Instead of 2.5 seconds, the query runs in 1.5 seconds. That's good.

A similar example involves rewriting filters. Here’s a query for Russia. It runs for 5 seconds. If we rewrite it in such a way that we compare not a string, but numbers with a set of keys that pertain to Russia, it will be much faster.

There are many tricks like this. They can significantly speed up queries that seem to be working fast, or on the contrary, those that are running slowly. They can be made even faster.

• Maximize work in distributed mode.
• Sorting by minimal types, as I have done by integers.
• If there are any joins, dictionaries, it's better to do them last when you already have the data at least partially grouped. Then the join operation or dictionary call will be invoked fewer times, making it faster.
• Replacing filters.

There are other techniques as well, not just those I've demonstrated. All of them can sometimes significantly speed up query execution.

Let's move on to the next example. Company X from the USA. What does it do?

The task was:

• Offline linking of advertising transactions.
• Modeling different linking scenarios.

What does the scenario consist of?

A typical visitor accesses the website, for example, 20 times a month from different ads or sometimes just comes without any ads, because they remember the site. They look at certain products, add them to their cart, take them out of the cart, and, in the end, buy something.

Reasonable questions arise: "Who needs to pay for the ad, if at all?" and "Which ad influenced them, if any?" In other words, why did they make a purchase, and how can we encourage people similar to this person to buy as well?

To solve this problem, we need to connect the events happening on the website correctly, establishing a relationship between them. Then we need to send this data for analysis to the DWH and based on that analysis, build models to determine who and what ads to show.

An advertising transaction is a set of related user events that starts with the display of an ad, then something happens, and later, perhaps, a purchase occurs, followed by more purchases within that purchase. For example, if it’s a mobile app or a mobile game, usually the app installation is free, but if further actions are taken, it may require spending money. The more a person spends within the app, the more valuable they are. But this requires establishing those connections.

There are many models for establishing connections.

The most popular are:

• Last Interaction, where interaction is either a click or an impression.
• First Interaction, meaning the first thing that brought the person to the website.
• Linear combination – equal weight for everyone.
• Decay.
• And others.

How did all this originally work? There was Runtime and Cassandra. Cassandra was used as transaction storage, meaning all related transactions were stored in it. When an event came into Runtime, such as displaying a page or something else, a query was made to Cassandra to check if such a person exists. Then the transactions related to that person were retrieved, and the connections were established.

And if you were lucky that the request contained a transaction id, it was easy. But usually, luck was not on your side. Therefore, it was necessary to find the last transaction or the transaction related to the last click, etc.

And everything was working very well while linking was to the last click. Because there were, say, 10 million clicks a day, 300 million a month, if you set a monthly window. And since in Cassandra everything needs to be in memory for it to operate quickly, as Runtime needs to respond fast, about 10-15 servers were required.

But when we wanted to link the transaction to the display, it suddenly became less fun. Why? Clearly, because we need to store 30 times more events. Accordingly, we need about 30 times more servers. It turns out that this figure is astronomical. Maintaining up to 500 servers for linking, when there are significantly fewer servers in Runtime, seems incorrect. So we started to think about what to do.

And we turned to ClickHouse. But how to do this in ClickHouse? At first glance, it seems like a set of anti-patterns.

• The transaction grows, we attach more and more new events to it, i.e., it is mutable, but ClickHouse doesn’t work well with mutable objects.
• When a visitor comes to us, we need to pull out their transactions by key, by their visit id. This is also a point query; this is not how it’s done in ClickHouse. Usually, ClickHouse performs large scans, but here we need to retrieve several records. This too is an anti-pattern.
• Moreover, the transaction was in JSON, but we didn’t want to rewrite it, so we wanted to store JSON in an unstructured way and extract something from it when needed. This is also an anti-pattern.

That is, a set of anti-patterns.

However, it turned out to create a system that worked very well.

What was done? ClickHouse was introduced, where logs divided into records were sent. An attributed service appeared that fetched logs from ClickHouse. After that, for each record by visit id, it fetched transactions that might still be unprocessed, along with snapshots, i.e., already linked transactions, specifically the results of previous work. From these, the logic was formed, the correct transaction was selected, new events were integrated, and written back to the log. The log was sent back to ClickHouse, i.e., this is a constantly cyclical system. Additionally, it was sent to DWH for analysis.

This is how it didn't work very well. To make it easier for ClickHouse when querying by visit id, we grouped these queries into blocks of 1,000-2,000 visit ids and pulled all transactions for 1,000-2,000 people. Then everything started working.

If you look inside ClickHouse, there are only three main tables that service everything.

The first table is where the logs are uploaded, and the logs are uploaded almost without any processing.

The second table. Through a materialized view, un-attributed events were extracted from these logs, meaning those that were not related. And transactions were pulled from these logs through a materialized view to build a snapshot. That is, a special materialized view built the snapshot, specifically the last accumulated state of the transaction.

Here, there is a SQL text written. I would like to comment on several important things in it.

The first important thing is the ability in ClickHouse to extract columns from JSON. That is, ClickHouse has some methods for working with JSON. They are very, very primitive.

visitParamExtractInt allows attributes to be extracted from JSON, meaning the first hit triggers. This way you can extract the transaction id or visit id. That's one.

The second thing is that a clever materialized field is used here. What does that mean? It means that you cannot insert it into the table, meaning it is not inserted, it is computed and stored during insertion. When inserting, ClickHouse does the work for you. And what you might need later is extracted from JSON.

In this case, the materialized view is for unprocessed rows. It uses the first table with practically raw logs. What does it do? First of all, it changes the sorting, that is, the sorting is now done by visit id because we need to quickly pull the transaction for a specific person.

The second important thing is index_granularity. If you have seen MergeTree, the default index_granularity is usually set to 8,192. What is this? This is a sparsity parameter for the index. In ClickHouse, the index is sparse; it never indexes every record. It does this every 8,192 records. This is good when a lot of data needs to be calculated, but bad when there is a little because of significant overhead. Reducing index granularity decreases overhead. It cannot be reduced to one because it may lack memory. The index is always stored in memory.

Snapshots use some other interesting features of ClickHouse.

First of all, there's AggregatingMergeTree. In AggregatingMergeTree, argMax is stored, meaning this is the state of the transaction corresponding to the last timestamp. New transactions are constantly generated for each visitor. In the very latest state of this transaction, we added an event, and we have a new state. It went back into ClickHouse. Through argMax in this materialized view, we can always retrieve the current state.

• The binding is 'disconnected' from Runtime.
• Up to 3 billion transactions are stored and processed each month. This is an order of magnitude more than was in Cassandra, hence in a typical transactional system.
• A cluster of 2x5 ClickHouse servers. There are 5 servers, and each server has a replica. This is even less than was in Cassandra to achieve click-based attribution, while here we have impression-based. Instead of increasing the number of servers by 30 times, we managed to reduce it.

The last example is a financial company Y, which analyzed correlations in stock price changes.

The task was as follows:

• There are about 5,000 stocks.
• Prices are known every 100 milliseconds.
• Data has accumulated over 10 years. Apparently, for some companies it's more, for others less.
• In total, about 100 billion rows.

And it was necessary to calculate the correlation of changes.

Here there are two stocks and their prices. If one goes up and the other also goes up, this is a positive correlation, meaning one is rising while the other is rising too. If one goes up, as at the end of the graph, and the other goes down, it’s a negative correlation, meaning when one rises, the other falls.

By analyzing these mutual changes, predictions can be made in the financial market.

But the task is complex. What is done for this? We have 100 billion records containing: time, stock, and price. First, we need to calculate 100 billion times the runningDifference using the price algorithm. RunningDifference is a function in ClickHouse that sequentially calculates the difference between two rows.

After that, we need to calculate the correlation, and it must be calculated for each pair. For 5,000 stocks, there are 12.5 million pairs. This is a lot, meaning we need to calculate this correlation function 12.5 times.

And if anyone forgot, x and y are the expected values from the sample. That is, it's necessary not only to calculate the roots and sums, but also to compute additional sums within those sums. A huge amount of calculations needs to be performed 12.5 million times, and they also need to be grouped by hours. And we have quite a few hours too. And it all needs to be done within 60 seconds. This is a joke.

We had to manage somehow because everything was running very, very slowly before ClickHouse arrived.

They tried to calculate this on Hadoop, on Spark, on Greenplum. And everything was either very slow or expensive. That is, it was somehow possible to calculate, but it ended up being costly.

Then ClickHouse came along, and everything got much better.

I remind you, the issue we have is with data locality, so correlations cannot be localized. We cannot split part of the data onto one server and part onto another and calculate; we need all the data everywhere.

What did they do? Initially, the data was localized. Each server holds data on the pricing of a specific set of stocks. And they do not overlap. Therefore, it's possible to calculate logReturn in parallel and independently; everything happens in parallel and is distributed.

Next, they decided to reduce this data while not losing expressiveness. To reduce using arrays, i.e., for each time segment to create an array of stocks and an array of prices. This way, the data takes up much less space. And it's somewhat easier to work with them. These are almost parallel operations, that is, we partially calculate them in parallel and then write them to the server.

After this, it can be replicated. The letter 'r' indicates that we replicated this data. That is, we have identical data on all three servers - these arrays.

And then, using a special script, from this set of 12.5 million correlations that need to be calculated, packages can be formed. That is, 2,500 tasks of 5,000 pairs of correlations. And this task is computed on a specific ClickHouse server. It has all the data because the data is identical, and it can compute them sequentially.

Once again, here's how it looks. First, we have all the data in this structure: time, stocks, price. Then we calculated the logReturn, meaning we maintained the same structure, but instead of price, we had logReturn. After that, we restructured it to obtain time and groupArray based on stocks and prices. We replicated it. Then we generated a bunch of tasks and fed them to ClickHouse for computation. And it works.

In the proof of concept, the task was a subtask, meaning we took less data. And it was only across three servers.

These first two stages: calculating log_return and wrapping it into arrays took about an hour each.

Calculating the correlation took about 50 hours. But 50 hours is not much because previously it took them weeks. This was a major success. And if you calculate, it was done 70 times a second on this cluster.

But the most important thing is that this system has virtually no bottlenecks, meaning it scales almost linearly. And they verified this. They successfully scaled it.

• The right schema is half the success. And the right schema involves using all the necessary ClickHouse technologies.
• Summing/AggregatingMergeTrees are technologies that allow for aggregation or computing a snapshot state as a specific case. And this significantly simplifies many things.
• Materialized Views allow you to bypass the limitation of a single index. Maybe I didn't explain this very clearly, but when we loaded the logs, the raw logs were in a table with one index, while the attribute logs were in another table, meaning the same data but filtered, and the index was completely different. It seems like the same data, but sorted differently. Materialized Views allow you, if needed, to bypass such a ClickHouse limitation.
• Reduce the granularity of the index for point queries.
• And distribute data wisely; try to localize data as much as possible within the server. Also, ensure that queries utilize localization wherever possible.

In summary, it can be said that ClickHouse has firmly established itself in the territory of both commercial databases and open source databases, specifically for analytics. It fits wonderfully into this landscape. Moreover, it is gradually beginning to outpace others because when you have ClickHouse, you don’t need InfiniDB. Vertica may soon become redundant if they manage to provide proper SQL support. Enjoy!

—Thank you for the presentation! Very interesting! Were there any comparisons made to Apache Phoenix?

- No, I haven't heard of anyone making comparisons. We at Yandex try to monitor all comparisons of ClickHouse with different databases. Because if something turns out to be faster than ClickHouse, Alexey Milovidov can’t sleep at night and starts to speed it up quickly. I haven’t heard of such a comparison.

• (Alexey Milovidov) Apache Phoenix is a SQL engine on Hbase. Hbase is primarily designed for key-value type use cases. In it, each row can have an arbitrary number of columns with arbitrary names. This can be said about systems like Hbase and Cassandra. And heavy analytical queries do not work well on them. Or you might think they work fine if you have not had any experience with ClickHouse.

• Thank you

  • Good afternoon! I have been quite interested in this topic for a while as I have an analytical subsystem. But when I look at ClickHouse, I get the impression that ClickHouse is very well suited for event analysis and mutable data. If I need to analyze a lot of business data with many large tables, then ClickHouse, as I understand it, may not be very suitable for me? Especially if they change. Is that correct, or are there examples that might contradict this?

  • That's correct. And this holds true for most specialized analytical databases. They are designed for having one or more large mutable tables and many small ones that change slowly. In other words, ClickHouse is not like Oracle, where you can place everything and construct very complex queries. To use ClickHouse effectively, the schema must be structured in a way that works well with ClickHouse. This means avoiding excessive normalization, using dictionaries, and trying to have fewer long relationships. If the schema is built this way, then similar business tasks in ClickHouse can be solved much more efficiently than in a traditional relational database.

Thank you for the presentation! I have a question about the last financial case. They had analytics. It was necessary to compare what goes up and down. From my understanding, you built the system specifically for this analytics? If tomorrow, for example, they need another report based on this data, will they need to rebuild the schema and load the data again? In other words, will they have to perform some preprocessing to generate the query?

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

Understood. You mentioned that it took 50 hours to process. Was this from the very start, when the data was loaded, or was it when you received the results?

Yes, yes.

Well, thank you very much.

This is on a 3-server cluster.

Greetings! Thank you for the presentation! It's all very interesting. I'm going to ask a question not about functionality but about using ClickHouse from a stability perspective. Have you encountered any issues requiring recovery? How does ClickHouse behave in such cases? Has there been a situation where it crashed, including the replica? For example, we have encountered problems with ClickHouse when it exceeds its limits and fails.

Of course, there are no perfect systems. ClickHouse has its problems as well. But have you heard about Yandex.Metrica having prolonged downtimes? Probably not. It has been reliably running on ClickHouse since around 2012-2013. I can share my experience too. We have never experienced complete failures. There have been some partial issues, but they were never critical enough to seriously impact the business. Such occurrences have never happened. ClickHouse is quite reliable and doesn’t crash randomly. You don’t have to worry about that. It’s not an immature product. Many companies have proven its stability.

Hello! You mentioned that it's important to think through the data schema from the start. What if that hasn’t happened? My data is pouring in continuously. Six months go by, and I realize that this is unsustainable; I need to reload the data and do something about it.

That, of course, depends on your system. There are several ways to do this almost without any downtime. For instance, you can create a Materialized View with a different data structure if it can be mapped unambiguously. This means if it allows for mapping with ClickHouse’s tools, such as extracting certain elements, changing the primary key, or altering partitioning, you can create a Materialized View. You would write your old data there, and new entries would be added automatically. Then you can simply switch to using the Materialized View, redirect new writes, and drop the old table. This approach can be done without any downtime.

Thank you.

Source: habr.com