Real time OLAP, from 0 to 1

Time:2021-9-22 team’s technology evolution process and production optimization practice in real-time OLAP.

Author Gao Zhengyan

This paper mainly introduces the technology evolution process and production optimization practice of team in real-time OLAP, as follows:

  1. Business background
  2. Opportunities and challenges
  3. Architecture evolution
  4. Architecture optimization
  5. Future outlook

1、 Business background

1.1 business introduction – ABCD

Real time OLAP, from 0 to 1 is a blockchain technology solution provider. Our business is mainly divided into four parts. To sum up, it is ABCD: A is artificial intelligence machine learning, B is blockchain, C represents cloud and D is data. These modules are not only independent of each other, but also can be combined with each other. In recent years, the accelerated development of artificial intelligence and blockchain is closely related to the support provided by big data.

1.2 business introduction – blockchain technology solution provider

Real time OLAP, from 0 to 1

Generally speaking, blockchain can be understood as an irreversible distributed ledger. Our role is to enable everyone to better browse the ledger and mine the information data behind the ledger. At present, the data volume of bitcoin is about billions to tens of billions, and the data volume is about tens of T. of course, we also have some other businesses, such as Ethereum currency, smart contract analysis service, etc.

Overall, we are a provider of blockchain technology solutions, providing mining services. Like banks in the financial industry, we also have a lot of OLAP needs. For example, when hackers attack the exchange or supply chain for asset transfer or money laundering, they need to go through operations on the chain. We can analyze them on the chain, track and count data on transactions, so as to provide assistance to the police.

2、 Opportunities and challenges

2.1 previous architecture

Real time OLAP, from 0 to 1

About 2018, there are few competitors, and our overall structure is as follows. The bottom layer is the node of the blockchain, which is continuously parsed to MySQL through the parser, then extracted from Mysql to hive or presto, ran various scheduled tasks from spark to analyze data, and then obtained reports or data through visual query. The architecture problem is also obvious:

  • Unable to process data in real time
  • There is a single point problem. For example, if a link suddenly hangs up, the whole link will have problems

2.2 needs and challenges encountered

Real time OLAP, from 0 to 1

  • efficiency, efficiency problems are very common. Our tables are in the order of billions. Running this kind of SQL may take a long time. SQL queries are relatively slow, which seriously affects the statistical efficiency.
  • real time, the data is not real-time and will not be updated until a certain time. For example, yesterday’s data can only be seen today.
  • monitorFor real-time requirements, such as real-time risk control, whenever a block appears in the blockchain, we need to analyze it, but the occurrence time of the block is random. Lack of complete monitoring, sometimes the operation suddenly breaks down or fails to meet the indicators, and we can’t know in time.

2.3 what should we consider for technology selection

Real time OLAP, from 0 to 1

What do we need to consider when selecting technology? The first is capacity reduction. The market in 2020 is not very good. Everyone is trying to reduce costs and live better. In the case of limited cost, how can we do more, we must improve our efficiency and ensure the quality at the same time. Therefore, we need to find a balance between cost efficiency and quality.

3、 Architecture evolution

3.1 technical selection

Real time OLAP, from 0 to 1

As the saying goes, the tools are well selected and we get off work early. We have been thinking about whether to introduce Flink for a long time. What are its advantages compared with spark?

After our actual research, we found that Flink still has many advantages, such as flexible window, accurate semantics, low latency, and support second level and real-time data processing. Because the team itself is more proficient in Python, we chose pyflink at that time, supported by a professional development team. In recent versions, there have been great changes and many functions have been realized. In terms of real-time OLAP database, we use Clickhouse.

3.2 why use Clickhouse

Real time OLAP, from 0 to 1

Why use Clickhouse? The first is fast and efficient query. Byte beating, big companies like Tencent, Kwai and so on are all used. At the same time, we also have technical accumulation in C + +, which is easy to use and the cost is not too high.

3.3 real time OLAP architecture

Real time OLAP, from 0 to 1

Based on the above technology selection, we have formed the above architecture. The bottom layer is the data source, including the nodes of the blockchain. It is parsed to Kafka through the parser. Kafka is responsible for docking Flink and spark tasks, and then Flink outputs the data to MySQL and Clickhouse, supporting report export, data statistics, data synchronization, OLAP statistics, etc.

In terms of data governance, we refer to the layering of the industry, which is divided into original layer, detail layer, summary layer and application layer. We also have machine learning tasks, which are deployed on the k8s platform.

3.4 architecture evolution

Our architecture evolution process is shown in the following figure. From spark and hive in 2018 to tableau visualization later, we contacted Flink this year and began to use Clickhouse in the second half of the year. Later, Flink has more tasks. We developed a simple scheduling platform. Developers only need to upload tasks and will run tasks regularly or in real time.

Real time OLAP, from 0 to 1

3.5 thinking on architecture evolution

Real time OLAP, from 0 to 1

  • Why is the evolution so slow? Because the development of blockchain has not reached a certain level, it is impossible to have hundreds of millions or petabytes of data like some large companies. Our data volume is not so large. Blockchain is a new thing without a certain history. Another problem is the problem of resources. Due to the shortage of personnel, the personnel cost is also controlled.
  • We have summarized what kind of enterprise it is suitable for. First, there is a certain data scale. Compared with an enterprise, MySQL has only tens of millions of data. Mysql, redis and mongodb can all be used, so it is not suitable for this architecture. Secondly, certain cost control is required. The cost of this whole set will be much lower than that of spark. We should have technical reserves and develop and understand relevant things.
  • Characteristics of blockchain data. There is a large amount of data, historical data is basically unchanged, real-time data is relatively more valuable, and there is a certain correlation between data and time.

3.6 value generated by real-time OLAP

Real time OLAP, from 0 to 1

After the real-time OLAP goes online, the business needs are basically met, and the cost is controllable.

  • What is suitable is the best. Don’t blindly pursue new technologies, such as data lake. Although it is good, our data level is actually not used.
  • We don’t consider building a technology center. Our company is small and medium-sized. It’s easy for departments to communicate. There are not many barriers and we haven’t developed to a certain organizational scale. Therefore, we don’t plan to develop a technology center and data center, and don’t blindly follow the trend.
  • The effect we achieved is to shorten the development time and reduce the running time of jobs.

4、 Architecture optimization

4.1 Flink and Clickhouse

Real time OLAP, from 0 to 1

There is some linkage between Flink and Clickhouse. We have customized three jobs.

  • Custom sink.
  • Clickhouse needs to insert a lot of data at one time. It needs to control the writing frequency and give priority to writing to the local table, which takes a lot of time.
  • We mainly use it in the transaction analysis of smart contracts. There are more and more new data, and there are a lot of data every few seconds. There are many data associations.

4.2 problems encountered by Clickhouse

Real time OLAP, from 0 to 1

  • Failure and fault tolerance during bulk import.
  • Optimization of upsert.
  • Common UDF has been developed. Do you know that Clickhouse does not support UDF officially? The Clickhouse can only be guaranteed by patching.

We are also doing some open source follow-up and patch attempts to bring together UDFs commonly used in business and technology.

4.3 batch import strategy

Real time OLAP, from 0 to 1

  • Historical data can be considered as cold data and will not change frequently. When importing, it is segmented according to size and sorted according to the primary key, which is similar to bitcoin. The underlying checker and fixer work, and alarm and repair are carried out in time during the import process. For example, if the import of a certain data fails, how to better find it in time can only be monitored by human flesh before.
  • For real-time data, we need to constantly analyze real-time data. You may not be familiar with the concept of reorganization. Here, the longest chain in the figure above is also the most important chain. The chain above it is a reorganized and forked chain. When an attacker or miner digs the chain above, the final result will lead to the abandonment of the chain, No reward.

If the computing power exceeds 51%, it will achieve such an effect and become the longest chain. This is relatively difficult to accumulate. At this time, we will think that the data import fails. At the same time, we will use the rollback function to roll back and reorganize it until the most complete chain is met. Of course, we will also set up some records and checkpoint. The concept of checkpoint here is also different from that of flick.

It is a checkpoint in the blockchain. The blockchain has a currency called BCH, which defines a checkpoint. When a certain length is met, it can no longer roll back, avoiding the attack of the attacker. We mainly use checkpoint to record information to prevent rollback. At the same time, we also record the failure or success of batch insertion according to the level / table. If it fails, we will retry and alarm rollback.

4.4 optimization of upsert

Real time OLAP, from 0 to 1

Clickhouse does not support upsert. It is mainly compatible with the SDK. Previously, it directly wrote data to MySQL. The goal is to modify the corresponding SDK through SQL statements, add a join to the temporary small table, and perform upsert operations through the join temporary small table.

For example, the blockchain address account balance, like the bank account balance, must be very accurate.

4.5 kubernetes optimization

Real time OLAP, from 0 to 1

Kubernetes optimization. Kubernetes is a complete platform.

  • For highly available storage, in the early days, we deployed services in kubernetes as much as possible, including Flink cluster, basic business components, currency node and Clickhouse node. In this regard, Clickhouse is good, convenient and compatible, and supports highly available operations.
  • Horizontal expansion is supported.
  • In terms of service discovery, we have made some customization.

4.6 how to ensure consistency?

Real time OLAP, from 0 to 1

  • Use final to query and wait for data consolidation.
  • In terms of data, idempotency is realized to ensure uniqueness. A group of data is sorted out through primary key sorting, and then written.
  • Repair and backfill in time when writing exceptions to ensure final consistency.

4.7 monitoring

Real time OLAP, from 0 to 1

Prometheus is used as a monitoring tool. Easy to use and low cost.

5、 Future outlook

5.1 from 1 to 2

Real time OLAP, from 0 to 1

  • Expand more business and data. In the past, our business model was relatively single, with only data statistics. Later, we will mine more information, including chain tracking and financial audit.
  • Only by making more money and living as much as possible can we do more things and explore more profit models.
  • Follow up the ecology of Flink and pyflink, actively participate in open source work and optimize relevant operations. Explore multiple aspects of sink work, the practice of native kubernetes.

5.2 from 2 to 3

Real time OLAP, from 0 to 1

  • Specification, regulation means and operation of data modeling.
  • Flink is combined with machine learning.
  • For real-time online training, Flink is a very good choice for real-time monitoring. Large companies already have relevant practices. Including alarm and other operations.

Copyright notice:The content of this article is spontaneously contributed by Alibaba cloud real name registered users, and the copyright belongs to the original author. Alibaba cloud developer community does not own its copyright or bear corresponding legal liabilities. Please refer to Alibaba cloud developer community user service agreement and Alibaba cloud developer community intellectual property protection guidelines for specific rules. If you find any content suspected of plagiarism in the community, fill in the infringement complaint form to report. Once verified, the community will immediately delete the content suspected of infringement.