Case analysis of Tencent cloud EMR big data real time OLAP analysis


Case analysis of Tencent cloud EMR big data real time OLAP analysis

OLAP (on line analytical processing) is the main application form of data warehouse system, which helps analysts analyze data from multiple angles and mine data value. Based on the real-time analysis scene of massive big data of QQ music, this paper tries to restore a different big data cloud solution through the case interpretation of the deep cooperation between QQ music and Tencent cloud EMR products.

1、 Background

QQ music is a leading music streaming media product of Tencent music. The platform has created a three-dimensional pan music entertainment ecosystem of “listening, watching and playing”, providing diversified music life experience for users with more than 800 million registered users, and enjoying the massive Music Library of more than 30 million songs on the platform. Behind the high-quality service, there are trillions of new music content and behavior data every day, and Pb data computing service.

Massive data means a higher standard of data analysis business, for offline analysis of timeliness, real-time and near real-time ad hoc real-time interactive analysis, put forward higher requirements. How to optimize pan music content creation and sharing ecology through user behavior and music content tag data, so as to bring better music experience for hundreds of millions of users? It is a great challenge and opportunity for QQ music big data team.

Tencent cloud elastic MapReduce (EMR) combines cloud technology and community open source technology to provide secure, low-cost, highly reliable and scalable cloud pan Hadoop services. EMR helps to build enterprise big data platform architecture, which is suitable for HBase online business, data warehouse, real-time streaming computing and other big data scenarios.

Based on business needs, QQ music big data team built and optimized the OLAP real-time big data analysis platform based on Clickhouse, and cooperated with Tencent cloud EMR team in-depth scenarios to build cloud solutions for big data. The following will analyze the evolution of OLAP system architecture of QQ music big data team through case interpretation, and constantly explore the value behind music data.

2、 Challenges of big data analysis

Earlier in the traditional offline data warehouse stage, QQ music used hive as the main tool for big data analysis to analyze TB to Pb data, but there are the following points that can be improved:

Case analysis of Tencent cloud EMR big data real time OLAP analysis

1. Low timeliness

For music services, real-time drilling and analysis of PV, UV, user circles, types of songs, dau and other data, the value of the analysis results depends on the timeliness. In the scenario of core daily reports and statistical reports, the offline analysis based on hive can only meet the timeliness of T + 1, and the demand for real-time daily reports and analysis is becoming more and more intense.

2. Low usability

Based on hive off-line data analysis platform, it has high technical threshold for product, operation and marketing personnel, which can not meet the self-service real-time interactive analysis needs; when reporting and extracting analysis data, the development can not obtain and verify the results in real time, and it often takes several hours to query and analyze logs.

3. Low process efficiency

Data analysis needs to be completed by the data analysis team. Through the process steps of scheduling, communication, modeling, analysis and visualization, the time required is calculated in weeks, and the landing may be several weeks. The analysis results are not timely, which affects and slows down the decision-making progress.

In order to deal with the above problems, improve the process efficiency, improve the timeliness and ease of use of data analysis and processing, the ad hoc data analysis and data visualization ability support needs to be optimized and improved, so that the problem has second level response, the analysis is more in-depth, and the data analysis is more efficient.

3、 Evolution of QQ music big data architecture technology

Based on the basic components such as Clickhouse + superset and the cloud capabilities of Tencent cloud EMR products, QQ music big data team has built a high availability and low latency real-time OLAP analysis and calculation visualization platform.

The cluster adds trillion data per day, reaching tens of thousands of CPU cores and Pb level data. The overall implementation of second level real-time data analysis, extraction, drilling, monitoring data basic services, greatly improving the work efficiency of big data analysis and processing.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

Through OLAP analysis platform, it greatly reduces the threshold of data exploration, achieves national Bi and national data service, realizes efficient analysis of various indicators such as real-time PV, UV, revenue, user circle, popular songs, etc., analyzes and locates the full link data at second level, strengthens the data reporting specification, and forms a good positive cycle.

1. Introduction to Clickhouse

Released by yandex, Russia’s largest search engine, Clickhouse is a column based, open-source, lightweight database management system for OLAP. It can generate analysis data reports in real time using SQL queries, and is suitable for real-time big data analysis of Pb data level.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

In terms of scene applicability and performance, Clickhouse has outstanding advantages in many components of OLAP.

(1) Scene application

Clickhouse is mainly a data warehouse for OLAP application scenarios. It stores data in the form of database table, which can analyze data simply and efficiently. Combined with superset, it can output analysis data chart in a visual way.

(2) Performance

Clickhouse has excellent real-time analysis capabilities. Taking a single table with outstanding performance as an example, using a single table of 100g, 300 million rows of data, cluster size of 8 cores, 20g * 3, simple query is completed at millisecond level, complex query is at second level, query speed is 3-6 times faster than presto and sparksql, and 30-100 times higher than hive.

Compared with presto, impala, hawkq and Greenplum, Clickhouse has realized super high speed query and highlighted its superior performance with its distributed computing, multi-core computing, vectorized execution and SIMD, code generation technology and column storage.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

2. Technological breakthrough points of Clickhouse architecture system

In the face of tens of thousands of nuclear cluster scale and Pb level data volume, after numerous times of upgrading and optimization of technical architecture and performance optimization of technical teams of QQ music big data team and Tencent cloud EMR, a high availability, high performance and high security OLAP computing and analysis platform is gradually formed.

(1) Zookeeper based on SSD disk

Clickhouse relies on zookeeper to realize the coordination of distributed systems. When the concurrent write volume of Clickhouse is large, zookeeper does not process metadata storage in time, which will lead to delay of synchronization between replicas of Clickhouse and reduce the overall performance of the cluster.

Solution:Zookeeper with SSD disk can greatly improve IO performance. When the number of tables is less than 100 and the data level is at TB level, HDD disk can also be used. SSD disk is recommended for other situations.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

(2) Data write consistency

After the data failed to be written to Clickhouse, the contents were repeated, which caused the analysis results in different systems, such as hive offline data warehouse, which were inconsistent with the calculation results in the Clickhouse cluster.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

Solution:Based on the unified global load balancing scheduling strategy, the data can still be written to the same shard after data failure, so as to realize data idempotent writing, so as to ensure the data consistency in Clickhouse.

(3) Real time offline data writing

Clickhouse data mainly comes from real-time pipelining reporting data and offline data intermediate analysis result data. How to write trillions of basic data efficiently and safely in the architecture is a huge challenge.

Solution:Based on the tube message queue, the distribution and consumption of unified data are completed, and the power synchronization of data is realized based on the above consistency strategy, so that the real-time and offline data can be written efficiently.  

Case analysis of Tencent cloud EMR big data real time OLAP analysis

(4) Table partition number optimization

Some offline data warehouses adopt hourly partition. If the original hourly partition is used to update and synchronize, a large number of files and file descriptors will be opened by select query in Clickhouse, which will lead to low performance.

Solution:The official click house also suggests that the number of table partitions should not exceed 10000. The data synchronization architecture mentioned above can complete the conversion from hour partition to day partition, and at the same time complete data idempotent consumption in the program.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

(5) Read / write separation architecture

Frequent write operations will consume a lot of CPU / memory / network card resources. Background merge threads cannot get effective resources, which reduces the speed of merge parts, and the construction of mergetree is not timely, which will affect the reading efficiency and reduce the cluster performance.

Solution:The temporary node of Clickhouse completes the construction of data partition file in advance, and dynamically loads it to the online service cluster, so as to alleviate the performance problems of Clickhouse in a large number of concurrent write scenarios, and realize an efficient read / write separation architecture. The specific steps and architecture are as follows:

a) Using k8s’s flexible construction deployment capability, the temporary Clickhouse node is built, and the data is written into the node to complete the data merge, sorting and other construction work;

b) The completed data is associated with the formal business cluster according to the mergetree structure.

Of course, for some small amount of data synchronous writing, more than 10000 pieces of batch writing can be used.                Case analysis of Tencent cloud EMR big data real time OLAP analysis

(6) Cross table query localization

In the Clickhouse cluster, the performance of global in / global join statement is low when cross table select query is performed. The reason is that a temporary table will be generated during such operations, and the table will be synchronized across devices, resulting in slow query speed.

Solution:Using consistent hash, the same primary key data is written into the same data fragment, and the cross table joint query is completed in the local local table. The data are all from the local storage, so as to improve the query speed.

This optimization scheme also has some potential problems. At present, Clickhouse does not provide the ability to reshard data. When the amount of primary key data stored by shard continues to increase and reaches the upper limit of disk capacity and needs to be split, the CK cluster can only be rebuilt again based on the original data, which has a high cost.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

3. Self service data analysis visualization platform based on superset

Apache superset (incubation) is a modern, enterprise level business intelligence web application. It provides high-performance OLAP online data analysis service for business processing Pb level data, provides rich data visualization sets, and supports interfaces of multiple components including Clickhouse, spark, hive, etc.

QQ music big data team combines its own business characteristics to enrich and improve its functions, combined with Tencent cloud EMR cloud elastic ability, deeply participates in superset open source co construction:

Case analysis of Tencent cloud EMR big data real time OLAP analysis

Clickhouse has many advantages in the field of real-time analysis. In the OLAP analysis scenario, combined with the superset component which is eye-catching in the field of data visualization, it provides rich data visualization sets for data analysis and processing in the overview of QQ music business indicators and two-dimensional variable analysis.

At present, more than 60% of the tens of thousands of charts in superset are created by non data personnel such as product, R & D, operation, researcher, finance, etc., so as to realize data analysis of the whole people and popularization of data.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

4、 QQ music and Tencent cloud EMR build cloud OLAP

In terms of ad hoc analysis and data visualization, open source community version has many advantages, such as rich ecological support, open source guarantee stability, software security and so on. But at the same time, the open source version also has some shortcomings, such as high technical complexity, long cycle, complex component operation and maintenance, lack of practical commercial application experience accumulation and so on.

Tencent cloud EMR supports open source community version OLAP and provides mature data capability. Out of the box, click House + superset combination scheme uses the latest stable version of the community. At the same time, it simplifies the complicated configuration and operation and maintenance operation, and ensures the high availability and data security of the cluster.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

Compared with the direct use of community open source components, the productized Clickhouse + superset has many advantages

  • Build quickly, create a big data cluster with hundreds of nodes in 10 minutes to avoid complex configuration.
  • Elastic expansion, 10 minutes for horizontal expansion.
  • Automation infrastructure operation and maintenance facilities, more than 100 monitoring indicators coverage, abnormal events reach second level.
  • Professional cloud after-sales team provides 7 * 24-hour support to improve technical anti risk ability.

Tencent cloud EMR commercial products not only retain the advantages of open source community components, but also make up for the existing shortcomings, which is the trend of commercial application.

At present, on the basis of self built architecture, QQ music business carries out real-time OLAP analysis of Pb level data with Tencent cloud EMR product flexibility, automatic management, and continuous support of business commercialization. It has excellent query performance and is widely used in business scenarios of real-time analysis and query, making management easier and more focused on business.

QQ music also has more cooperation with other components of EMR and big data matrix, combined with open source and deep secondary development, to create a big data ecology by combining the mode of hybrid architecture with business scenarios. In the recommendation scenario, QQ music flexibly selects the HBase component cluster of Tencent EMR products, and uses multiple components to cooperate to support frequent updating and reading of tag storage to meet the needs of different big data business scenarios.


Cloud big data infrastructure products with its technology openness, full link coverage and flexibility have been unanimously recognized by the data it team of Internet enterprises. More and more enterprises are gradually aware of the implicit value identification brought by strong technical support of cloud manufacturers. With the help of cloud big data infrastructure, data-driven business innovation and operation innovation has become the industry consensus and mainstream trend of the new generation of Internet enterprises.

Case analysis of Tencent cloud EMR big data real time OLAP analysis

QQ music big data team and Tencent cloud EMR products have explored business cooperation in depth to achieve mutual benefit and win-win results and release multiple data values from all aspects including big data infrastructure, full link data tool chain and domain data value application. Tencent cloud EMR will continue to polish Clickhouse + superset, committed to providing cloud data capabilities for more industries and business scenarios.

Click: , learn more about Tencent cloud EMR~

Recommended Today

Choose react or angular 2

Original addressChoosing between React vs. Angular 2The following is the translation of this article, which can be used at your choiceReactperhapsAngular2We need to help when we need to. React has become a cool representative in 2015, but angular.js has changed from a front-end framework that people love to a terrible devil (and not so terrible…) […]