Real-time multi-dimensional analysis method and system based on mass data

Abstract

The embodiment of the invention provides an OLAP (On-Line Analysis Processing) real-time multi-dimensional analysis method based on mass data, and the method comprises at least one of the following four optimization operations: ClickHouse design optimization: storing source data needing to be analyzed by using ClickHouse, and adding index strategy optimization query by newly adding data updating and inserting functions; a concurrent strategy is adjusted and optimized, wherein an independent Python process is adopted for each concurrent I / O operation of AsyncIO by using a multi-process module of Python; data preprocessing optimization: optimizing a data structure by using a column data storage method and combining Pandas with a distributed computing framework; and data serialization and deserialization performance optimization: serialization and deserialization in a PyAr row protocol are adjusted according to the characteristics of the data set and the network bandwidth. According to the technical scheme, the real-time analysis performance of the OLAP real-time multi-dimensional analysis system can be effectively improved.

Description

Technical Field

[0001] The present application relates to the field of computers, and in particular to a real-time multidimensional analysis method based on massive data, an OL AP real-time multidimensional analysis system, a computer device, a computer-readable storage medium, and a computer program product. Background Art

[0002] In today's big data era, the amount of data that enterprises and institutions need to process is increasing. Massive data is prevalent in various fields, such as telecommunications, the Internet, finance, and medical care. These data are often multi-dimensional, multi-granular, and multi-layered. Effectively analyzing these data is of great significance for decision support, fault prediction, business intelligence, and other aspects.

[0003] Traditional relational databases have performance bottlenecks when processing Online Analytical Processing (OLAP) tasks. ClickHouse is a high-performance open source column-based database management system (DBMS) designed specifically for real-time analysis of large data sets. ClickHouse allows real-time generation of analytical data reports using SQL queries and can support processing large-scale data. Therefore, ClickHouse was born as a column-based database management system for OLAP.

[0004] In the big data processing scenario, in the efficient real-time multidimensional analysis method of massive data based on ClickHouse, there are still system performance defects such as slow query speed, large memory requirements, and insufficiently optimized data structure in ClickHouse design, concurrency strategy, data preprocessing in the big data environment, and serialization and deserialization in the big data environment. Therefore, it is necessary to optimize the design of the real-time multidimensional analysis method based on ClickHouse and its system to overcome the above system performance defects. Summary of the invention

[0005] In order to overcome the system performance defects such as slow query speed, large memory requirements, and insufficiently optimized data structure in big data processing scenarios, the embodiments of the present application provide a real-time multidimensional analysis method based on massive data, an OLAP real-time multidimensional analysis system, a computer device, a computer-readable storage medium, and a computer program product to achieve the technical effect of improving the data processing speed and data analysis efficiency in OLAP analysis, and being able to quickly process massive data, thereby effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0006] In a first aspect, an embodiment of the present application provides a real-time multidimensional analysis method based on massive data, wherein the real-time multidimensional analysis method is used in an OLAP real-time multidimensional analysis system, wherein the OLAP real-time multidimensional analysis system is a big data analysis system that uses ClickHouse as a database management system, Pandas as a data processing and analysis library, an asynchronous non-blocking IOAsyncIO network mode, and interacts with data through a PyArrow protocol;

[0007] The OLAP real-time multidimensional analysis method includes: at least one of the following four optimization operations:

[0008] ClickHouse design optimization: Use ClickHouse to store the source data that needs to be analyzed, and optimize queries by adding data update and insertion functions and adding index strategies;

[0009] Concurrency strategy adjustment and optimization: By using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO;

[0010] Data preprocessing optimization: Use columnar data storage methods and combine Pandas with distributed computing frameworks to optimize data structures;

[0011] Data serialization and deserialization performance optimization: adjust the serialization and deserialization in the PyAr row protocol according to the characteristics of the data set and the network bandwidth.

[0012] Optionally, in an implementation of the first aspect, the data update and insert functions are implemented in the following manner:

[0013] After lightly pre-aggregating the data in the experimental dimension on the offline side, synchronize it to ClickHouse in real time;

[0014] For special data that requires detailed data, synchronize it to ClickHouse in real time.

[0015] Optionally, in an implementation of the first aspect, the adding of the index strategy to optimize the query function includes:

[0016] When querying data, we add index strategies and apply memory computing methods to cache common query calculation results into memory to optimize query functions.

[0017] Optionally, in an implementation of the first aspect, the cache is a segmented cache of data at different levels, which is cached locally in a parquet format through PyArrow.

[0018] Optionally, in an implementation of the first aspect, the memory computing method includes: a method of accelerating analysis results by memory computing with the goal of minimizing DB query and network IO operations;

[0019] The common query calculation results are presented in the form of data cubes in the memory, and are used to generate data report results in multiple analysis dimensions.

[0020] Optionally, in an implementation of the first aspect, the data structure of the data cube in the memory is aggregated by the following data aggregation operation:

[0021] The original data is grouped according to business rules, and the original data is aggregated using an aggregation function, and a unique label is assigned to each group;

[0022] Re-execute the aggregation operation on each group and merge the aggregation results into a data structure;

[0023] Obtain the aggregation type, set the corresponding mapping relationship according to the value of the aggregation result, and apply the aggregation result to the corresponding label;

[0024] Use the combination function pd.concat to concatenate multiple data structures together, sort them from high to low according to the charge field, and obtain the data structure of the final result.

[0025] Optionally, in an implementation of the first aspect, the OLAP real-time multidimensional analysis system also includes: a data service platform; the data service platform establishes a network connection with Pandas through AsyncIO, provides HTTP / gRPC service access to data, and certain specific dimension / indicator data is directly obtained from the data service platform through data services.

[0026] In a second aspect, an OLAP real-time multidimensional analysis system based on massive data is provided in an embodiment of the present application. The OLAP real-time multidimensional analysis system is a big data analysis system that uses ClickHouse as a database management system, Pandas as a data processing and analysis library, an asynchronous non-blocking IOAsyncIO network mode, and interacts with data through the PyArrow protocol;

[0027] The OLAP real-time multidimensional analysis system is optimized by at least one of the following four optimization operations:

[0028] ClickHouse design optimization: Use ClickHouse to store the source data that needs to be analyzed, and optimize the design of ClickHouse by adding data update and insertion functions and adding index strategies to optimize query functions;

[0029] Concurrency strategy adjustment and optimization: By using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO;

[0030] Data preprocessing optimization: Use columnar data storage methods and combine Pandas with distributed computing frameworks to optimize data structures;

[0031] Data serialization and deserialization performance optimization: Adjust the serialization and deserialization strategies and processes in the PyArrow protocol according to the characteristics of the data set and network bandwidth.

[0032] In a third aspect, an embodiment of the present application provides a computer device, including:

[0033] at least one processor; and

[0034] a memory communicatively coupled to the at least one processor;

[0035] Wherein: the memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the real-time multidimensional analysis method described in the first aspect above.

[0036] Another aspect of an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer instructions, and when the computer instructions are executed by a processor, the real-time multidimensional analysis method described in the first aspect above is implemented.

[0037] Another aspect of an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the real-time multidimensional analysis method described in the first aspect above.

[0038] The technical solution in the embodiments of the present application can achieve the following technical effects:

[0039] The above-mentioned real-time multidimensional analysis method based on massive data is designed and optimized from the following four aspects: database design optimization, concurrency strategy optimization, data preprocessing optimization in a big data environment, and data serialization and deserialization performance optimization, respectively, targeting the performance defects of ClickHouse when processing large-scale data and complex tasks; by using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO; in view of the high memory requirement and weak distributed function of Pandas in a big data environment, a columnar data storage method is used to combine Pandas with a distributed computing framework to optimize the data structure; in view of the performance problems of PyArrow during serialization and deserialization in a big data environment, the serialization and deserialization strategies and processes in the PyArrow protocol are adjusted according to the characteristics of the data set and the network bandwidth. One or more of these technical means overcome the corresponding system performance defects, so as to improve the data processing speed and data analysis efficiency in OLAP analysis, and can quickly process massive data, thereby effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] The accompanying drawings exemplarily illustrate the embodiments and constitute a part of the specification, and together with the text description of the specification, are used to explain the exemplary implementation of the embodiments. The embodiments shown are for illustrative purposes only and do not limit the scope of the claims. In all drawings, the same reference numerals refer to similar but not necessarily identical elements.

[0041] Figure 1 A schematic diagram of a system architecture of an OLAP real-time multidimensional analysis system based on massive data in an embodiment of the present application;

[0042] Figure 2 This is a flow chart of an embodiment of the OLAP real-time multidimensional analysis method based on massive data in an embodiment of the present application;

[0043] Figure 3 Schematic diagram of the hardware architecture of a computer device in an embodiment of the present application. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solutions and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application.

[0045] It should be noted that the descriptions involving "first", "second", etc. in the embodiments of the present application are only for descriptive purposes and cannot be understood as indicating or implying their relative importance or implicitly indicating the number of technical features indicated. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In addition, the technical solutions between the various embodiments can be combined with each other, but they must be based on the ability of ordinary technicians in the field to implement them. When the combination of technical solutions is contradictory or cannot be implemented, it should be deemed that such combination of technical solutions does not exist and is not within the scope of protection required by this application.

[0046] In the description of the present application, it should be understood that the numerical labels before the steps do not indicate the order in which the steps are executed, but are only used to facilitate the description of the present application and to distinguish each step, and therefore should not be understood as a limitation on the present application.

[0047] In order to facilitate understanding of the technical solutions in the embodiments of the present application, the technical terms involved in the embodiments of the present application, such as OLAP, ClickHouse, AsyncIO, Pandas, PyArrow, pd.concat, parquet format, etc., are described in detail below, as follows:

[0048] OLAP: Its full English name is Online Analytical Processing, and its Chinese translation is Online Processing Analysis. It is a technology and method for processing large amounts of data, mainly used in online analytical processing (Online). The OLAP system allows users to quickly analyze and mine data, thereby realizing real-time query, aggregation, perspective and other functions of data. This technology makes data analysis more convenient and efficient, especially in the financial, retail, medical and other industries. The OLAP system mainly includes the following parts: multidimensional data model, data warehouse, data aggregation and modeling tools, user interface; among them, the multidimensional data model is a data structure used to store and manage data, which can decompose data into multiple dimensions (Dimensions). n), such as time, location, product, etc., as well as the value (metric) of each dimension. Through the multidimensional data model, multi-angle analysis and slicing and dicing operations can be easily realized on the data; the data warehouse is the data storage layer of the OLAP system, which is mainly used to store, manage and update data. The data warehouse needs to adopt a specific data organization method, such as RDBMS (relational database management system) to support fast horizontal and vertical access; data aggregation and modeling tools can help users perform various aggregation operations on data, such as weighted average, counting, summing, averaging, etc. In addition, these tools can also be used to create and modify multidimensional data models, thereby increasing the flexibility and adaptability of the analysis model; the user interface is the interactive entrance of the OLAP system, which allows users to perform data analysis and visualization operations through an intuitive interface. Common OLAP user interfaces include Web interfaces, desktop applications and some dedicated OLAP software; in general, OLAP is a powerful data processing technology that can provide real-time, multi-dimensional data analysis functions without affecting data integrity, thereby helping enterprises and individuals better understand and manage complex data.

[0049] ClickHouse: is a high-performance open source column-based database management system (DBMS) designed specifically for real-time analysis of large-scale data sets. It has the ability to query hundreds of millions of data in a few seconds, and can maintain good processing performance for PB (1PB = 1024 × 1024GB) level data. ClickHouse is widely praised in the database field for its high-speed query, linear scalability, high data compression rate and support for a variety of SQL query statements.

[0050] AsyncIO: It is a standard library of Python, which is used to write single-threaded concurrent code to easily handle a large number of concurrent connections through non-blocking I / O operations and the use of coroutines, so as to achieve effective and efficient multitasking. It is generally used in network and real-time system applications.

[0051] Pandas: is a powerful Python data processing and data analysis library that provides a variety of tools for processing data structures and performing data analysis, including data structures for manipulating and cleaning data (such as Series and DataFrame), as well as a large number of data manipulation functions such as grouping, filtering, merging, and aggregation.

[0052] PyArrow: is a Python interface for the distributed computing framework (Apache Arrow) for big data. It provides tools for efficiently reading, writing, and processing big data structures and data streams, including support for importing and exporting large data sets (such as Parquet and Apache Arrow), and efficient integration with Python data analysis libraries such as Pandas and Nu mPy.

[0053] Parquet format: is an efficient, scalable, and compressible columnar file format designed for the Apache Hadoop ecosystem for storing and transmitting big data. It is scalable, and Parquet files can contain multiple tables, which is suitable for big data scenarios; compressibility, Parquet files can be highly compressed, making data transmission and storage more efficient; readability, the metadata information of Parquet files can be parsed by Apache Avro, which is easy to understand and audit; compatibility, Parquet files can be read and written by many different tools and technologies, such as Apache Hadoop, Apache Spark, Google BigQuery, etc.; ease of use: Python language has a large number of third-party libraries that support Parquet files, such as pandas, arrow, etc. Due to the above advantages, Parquet files have become a standard file format in the field of big data. When using a distributed database such as ClickHouse, data is usually stored in Parquet format to better support efficient data query and analysis.

[0054] In the big data processing scenario, technologies such as Clickhouse, AsyncIO, Pandas, and PyArrow are used. However, there are still system performance defects such as slow query speed, large memory requirements, and insufficiently optimized data structure in the OLAP real-time analysis of massive data, resulting in inefficient real-time analysis performance.

[0055] First, ClickHouse brings convenience to large-scale data analysis, but this convenience comes at the expense of data update and insertion performance. In addition, when the data query task is very complex and involves multiple dimensions, ClickHouse's processing capabilities may also be limited.

[0056] Second, AsyncIO allows developers to handle multiple tasks simultaneously in a single thread, improving the efficiency of I / O operations. However, when AsyncIO is used for CPU-intensive tasks, the effect is not ideal. GIL is limited by multi-threading, and computationally intensive tasks are generally calculated in parallel through multiple processes. Here, asyncio mainly achieves high performance by improving network I / O efficiency.

[0057] Third, Pandas is very powerful in data cleaning and preprocessing, but its memory requirements are quite high. When processing massive amounts of data, the limitation of single machine memory may become a bottleneck for Pandas in big data processing. In addition, Pandas lacks good support for distributed processing of big data.

[0058] Fourth, PyArrow provides effective support for data serialization and deserialization. However, when the data set is very large and distributed, PyArrow may face the problem of degraded serialization and deserialization performance.

[0059] In response to the system performance defects described in one to four above, in order to realize the inventive concept of improving the real-time analysis performance of the OLAP real-time multidimensional analysis system, the applicant proposed the OLAP real-time multidimensional analysis method and system based on massive data in this application from four angles: overcoming the performance defects of ClickHouse when processing large-scale data and complex tasks, due to the limitation of multithreading by GIL, the high memory requirement and weak distributed function of Pandas in the big data environment, and the performance inefficiency of PyArrow during serialization and deserialization in the big data environment, so as to overcome the system performance defects in ClickHouse, AsyncIO, Pandas and PyArrow mentioned above, and achieve the function and effect of improving data processing speed and data analysis efficiency in OLAP real-time multidimensional analysis, and can quickly process massive data, thereby achieving the technical purpose of effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0060] In order to facilitate understanding of the technical solution of the OLAP real-time multidimensional analysis method and system based on massive data in the embodiment of the present application, it will be described in detail as follows in conjunction with the accompanying drawings:

[0061] Embodiment 1

[0062] Figure 1 This is a schematic diagram of the system architecture of an OLAP real-time multidimensional analysis system based on massive data in an embodiment of the present application.

[0063] like Figure 1As shown, the OLAP real-time multidimensional analysis system includes ClickHouse and Pandas, where ClickHouse is used as the database management system and Pandas is used as the data processing and analysis library. The asynchronous non-blocking IOAsyncIO network mode is used to establish a network connection between ClickHouse and Pandas, and data is exchanged through the PyArrow protocol.

[0064] In order to improve the performance and efficiency of the OLAP system, the above OLAP real-time multidimensional analysis system was optimized, including the following four aspects:

[0065] Database design optimization: In view of the performance defects of ClickHouse when processing large-scale data and complex tasks, ClickHouse is used to store the source data that needs to be analyzed, and the design of ClickHouse is optimized by adding data update and insertion functions and adding index strategies to optimize query functions. Through the design and optimization of Click House, the performance loss of the system during complex operations can be effectively reduced, and the operating efficiency of the system can be improved;

[0066] Concurrency strategy adjustment and optimization: By using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO. This optimization of the concurrency strategy enables the system to fully utilize the advantages of parallel computing when processing a large number of concurrent requests and improve the system's processing speed.

[0067] Data preprocessing optimization: In order to solve the problem of high memory requirements and weak distributed functions of Pandas in the big data environment, we use the columnar data storage method and combine Pandas with the distributed computing framework to optimize the data structure. This data preprocessing optimization can effectively reduce the system's memory overhead when performing complex operations and improve the system's operating efficiency.

[0068] Data serialization and deserialization performance optimization: To address the performance issues of the PyArrow protocol when serializing and deserializing in a big data environment, the serialization and deserialization strategies and processes in the PyArrow protocol are adjusted according to the characteristics of the data set and the network bandwidth. This data serialization and deserialization performance optimization can make the system more efficient in serialization and deserialization when processing big data, thereby improving the system's operating efficiency.

[0069] In summary, this application improves the performance and efficiency of the OLAP system by implementing four optimization operations, and provides a more advanced and more effective OLAP real-time multidimensional analysis system.

[0070] Optionally, in one implementation of the present application, Figure 1 As shown in the dotted line part, the OLAP real-time multidimensional analysis system also includes a data service platform, which establishes a network connection with Pandas through AsyncIO, provides HTTP / gRPC service access to data, and directly obtains data of specific dimensions / indicators from the data service platform through data services.

[0071] The data service platform is an important component. It is mainly responsible for storing and processing large amounts of data analysis data, and provides users with various query and analysis tools for complex data analysis and mining. In the data service platform, specific dimension / indicator data can be directly obtained without performing data analysis, query and other operations, and the required data can be quickly obtained. AsyncIO is an asynchronous I / O framework that can improve the concurrent performance of the system. The data service platform establishes a network connection with Pandas through AsyncIO, which can achieve efficient data reading and writing operations. At the same time, AsyncIO also enables the data service platform to better support parallel computing, thereby improving the overall efficiency of the system.

[0072] In addition, the data service platform also provides HTTP / gRPC service, which is a high-performance inter-service communication protocol. You can access the data in the data service platform through HTTP / gRPC service without worrying about the underlying implementation details. For some specific dimension / indicator data, the data service platform can also obtain it directly from its own database. This design makes the data service platform very flexible and scalable, and can adapt to different usage scenarios and needs.

[0073] The real-time multidimensional analysis method based on massive data in the embodiment of the present application is applicable to Figure 1 In the OLAP real-time multidimensional analysis system shown in the figure, the OLAP real-time multidimensional analysis method is improved from the following four aspects: database design optimization, concurrency strategy optimization, data preprocessing optimization in a big data environment, and data serialization and deserialization performance optimization, as follows:

[0074] Embodiment 2

[0075] Figure 2 The present invention is a flowchart of an embodiment of an OLAP real-time multidimensional analysis method based on massive data in an embodiment of the present application.

[0076] Figure 2 The following is an OLAP real-time multidimensional analysis method based on database optimization, concurrency strategy optimization, data preprocessing optimization in a big data environment, and data serialization and deserialization performance optimization, including:

[0077] Step S201 ClickHouse design optimization: Use ClickHouse to store the source data that needs to be analyzed, and optimize the design of ClickHouse by adding data update and insertion functions and adding index strategies to optimize query functions.

[0078] ClickHouse has been designed and optimized to address performance issues when processing large data and complex tasks. By carefully designing and optimizing ClickHouse with new data update and insert functions and adding index strategies to optimize query functions, its performance in processing large-scale data and complex queries has been improved. Among them, the new data update and insert functions can be implemented in real-time synchronization, and the addition of index strategies to optimize query functions can be implemented by adopting more sophisticated and flexible index strategies, applying memory computing methods to cache common query calculation results, and using more efficient distributed computing frameworks. Specifically, using more sophisticated and flexible indexes can help reduce data scanning time and increase query speed. We need to choose appropriate indexes based on actual business needs and usage habits.

[0079] Step S202 concurrent strategy adjustment and optimization: by using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO.

[0080] Using Python's multi-process module, an independent Python process is started for each concurrent I / O operation in AsyncIO. This can avoid the degradation of the overall system performance due to the blocking of a single process, maximize the use of the system's multi-core resources, and thus improve computing efficiency. When AsyncIO is used for CPU-intensive tasks, it can overcome the limitations of the global interpreter lock (GIL), and computing-intensive tasks can also be truly processed in parallel.

[0081] Step S203: Data preprocessing optimization: Use a columnar data storage method and combine Pandas with a distributed computing framework to optimize the data structure.

[0082] In order to solve the problem of high memory requirements and weak distributed functions of Pandas in the big data environment, the distributed functions of Pandas are enhanced while maintaining its efficient memory usage. This is achieved by combining the columnar data storage method with the distributed computing framework. By using more efficient data structures, such as the columnar data storage method, and combining it with distributed computing frameworks such as Spark and Dask, the data preprocessing capabilities in the big data environment are effectively improved.

[0083] Step S204: Data serialization and deserialization performance optimization: adjust the serialization and deserialization strategies and processes in the PyArrow protocol according to the characteristics of the data set and the network bandwidth.

[0084] Considering the performance issues of the PyArrow protocol when serializing and deserializing in a big data environment, the serialization and deserialization strategies and methods are adjusted accordingly to adapt to specific data sets and network bandwidth. Through the adjusted serialization and deserialization strategies and processes, the performance of the PyArrow protocol in a big data environment has been significantly improved.

[0085] In summary, the protection points of the present invention mainly revolve around database design, concurrency strategy, data preprocessing in a big data environment, and performance improvement of PyArrow, which effectively solves the problems existing in the background technology and achieves the objectives of this application.

[0086] Specifically, the above-mentioned real-time multidimensional analysis method based on massive data is designed and optimized from the following four aspects: ClickHouse design optimization, concurrency strategy optimization, data preprocessing optimization in a big data environment, and data serialization and deserialization performance optimization, respectively, targeting the performance defects of ClickHouse when processing large-scale data and complex tasks; by using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO; in view of the high memory requirement and weak distributed function of Pandas in a big data environment, a columnar data storage method is used to combine Pandas with a distributed computing framework to optimize the data structure; in view of the performance problems of the PyArrow protocol during serialization and deserialization in a big data environment, the serialization and deserialization strategies and processes in the PyArrow protocol are adjusted according to the characteristics of the data set and the network bandwidth. One or more of these technical means overcome the corresponding system performance defects to improve the data processing speed and data analysis efficiency in OLAP analysis, and can quickly process massive data, thereby effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0087] Optionally, in one implementation of the embodiment of the present application, the data update and insertion functions in the above step 201 are specifically implemented in the following ways: 1. After light pre-aggregation of the experimental dimension of the data on the offline side, synchronize it to ClickHouse in real time; 2. For special data that requires detailed data, synchronize it to ClickHouse in real time. Among them, the time interval of real-time synchronization can be determined according to the specific data update cycle, such as synchronization at the hour level, once a week or several weeks, etc.

[0088] By saving the original data that needs to be analyzed, that is, the source data, in ClickHouse in some way, performing some light pre-aggregation in an offline environment, and then synchronizing these pre-aggregation results to ClickHouse in real time, special data that requires more detailed information should also be synchronized to ClickHouse in real time to optimize the design of the database, so that ClickHouse can bring convenience to large-scale data analysis while having data update and insertion performance, so as to overcome the performance defects of ClickHouse in processing large-scale data and complex tasks, and improve the data processing capabilities of ClickHouse.

[0089] Optionally, in one implementation of the present application, in the above step 201, adding an index strategy to optimize the query function specifically includes: optimizing the query function by adding an index strategy during data query and applying a memory calculation method to cache common query calculation results in memory.

[0090] The use of index strategies not only retains the advantage of ClickHouse's fast analysis, but also suppresses the performance loss of data updates and insertions. The use of memory computing methods to cache common query calculation results in memory can improve query performance, thereby further improving ClickHouse's data processing capabilities.

[0091] Optionally, in one implementation of the present application, the cache in the above optimization method is to cache data segments at different levels, and cache them locally in parquet format through PyArrow. Using PyArrow to cache data segments at different levels locally can reduce network latency and improve the system's concurrency while ensuring query speed.

[0092] Optionally, in one implementation of the present application, the memory computing method in the above optimization method is a method of accelerating the analysis results by memory computing based on the principle of minimizing DB query and network IO operations; the common query calculation results are presented in the form of data cubes in memory, which are used to generate data report results of multiple analysis dimensions. Following the principle of minimizing DB query and network IO, the calculation is performed in memory as much as possible, thereby accelerating the analysis results and improving query performance.

[0093] Optionally, in an implementation of the embodiment of the present application, the data structure of the data cube in the memory is aggregated by the following data aggregation operations:

[0094] The original data is grouped according to business rules, and the original data is aggregated using an aggregation function, and a unique label is assigned to each group;

[0095] Re-execute the aggregation operation on each group and merge the aggregation results into a data structure;

[0096] Obtain the aggregation type, set the corresponding mapping relationship according to the value of the aggregation result, and apply the aggregation result to the corresponding label;

[0097] Use the combination function pd.concat to concatenate multiple data structures together, sort them from high to low according to the charge field, and obtain the data structure of the final result.

[0098] By preprocessing data through data aggregation and charge fields, the data structure is optimized, thereby optimizing the data preprocessing process. This not only reduces the memory requirements when processing big data, but also enables more efficient distributed processing, thereby saving a lot of computing and storage resources.

[0099] Optionally, in one implementation of the present application, the OLAP real-time multidimensional analysis system also includes: a data service platform, which establishes a network connection with Pandas through AsyncIO, provides HTTP / gRPC service to access data, and directly obtains data of specific dimensions / indicators from the data service platform through data services.

[0100] By adding a new data service platform to the OLAP real-time multidimensional analysis system and directly obtaining specific dimension / indicator data from the data platform through data services, the database design optimization can be further improved, the data query speed of the OLAP real-time multidimensional analysis system can be improved, and the real-time analysis performance of the OLAP real-time multidimensional analysis system can be effectively improved.

[0101] Embodiment 3

[0102] The present application provides an OLAP real-time multidimensional analysis method based on database optimization, including:

[0103] Step 201 ClickHouse design optimization: Use ClickHouse to store the source data that needs to be analyzed, and optimize the design of ClickHouse by adding data update and insertion functions and adding index strategies to optimize query functions.

[0104] This OLAP real-time multidimensional analysis method improves the OLAP real-time multidimensional analysis method only through the single aspect of ClickHouse design optimization, so as to overcome the system performance defects of ClickHouse when processing large-scale data and complex tasks, such as the inability to update and insert data and low query efficiency, so as to achieve the technical purpose of effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0105] Embodiment 4

[0106] The embodiment of the present application provides an OLAP real-time multidimensional analysis method based on concurrent strategy optimization, including:

[0107] Step S202 concurrent strategy adjustment and optimization: by using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO.

[0108] The OLAP real-time multidimensional analysis method improves the OLAP real-time multidimensional analysis method only by optimizing the concurrent strategy, so as to overcome the system performance defect that the traditional multithreading in AsyncIO cannot effectively utilize the multi-core processor resources due to the limitation of the global interpreter lock (GIL), thereby achieving the technical purpose of effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0109] Embodiment 5

[0110] The present application provides an OLAP real-time multidimensional analysis method based on data preprocessing optimization in a big data environment, including:

[0111] Step S203: Data preprocessing optimization: Use a columnar data storage method and combine Pandas with a distributed computing framework to optimize the data structure.

[0112] The OLAP real-time multidimensional analysis method improves the OLAP real-time multidimensional analysis method only by optimizing data preprocessing in a big data environment, so as to overcome the problems of high memory requirements and weak distributed functions of Pandas in a big data environment, thereby achieving the technical purpose of effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0113] Embodiment 6

[0114] The present application provides an OLAP real-time multidimensional analysis method based on data serialization and deserialization performance optimization in a big data environment, including:

[0115] Step S204: Data serialization and deserialization performance optimization: adjust the serialization and deserialization strategies and processes in the PyArrow protocol according to the characteristics of the data set and the network bandwidth.

[0116] This OLAP real-time multidimensional analysis method improves the OLAP real-time multidimensional analysis method by only optimizing the data serialization and deserialization performance in a big data environment, so as to overcome the performance inefficiency of PyArrow during serialization and deserialization in a big data environment, thereby achieving the technical purpose of effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0117] Embodiment 7

[0118] The embodiment of the present application provides an OLAP real-time multidimensional analysis method based on concurrent strategy optimization, data preprocessing optimization in a big data environment, and data serialization and deserialization performance optimization, including:

[0119] Step S202 concurrent strategy adjustment and optimization: by using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO;

[0120] Step S203: Data preprocessing optimization: using a columnar data storage method and combining Pandas with a distributed computing framework to optimize the data structure;

[0121] Step S204: Data serialization and deserialization performance optimization: adjust the serialization and deserialization strategies and processes in the PyArrow protocol according to the characteristics of the data set and the network bandwidth.

[0122] The OLAP real-time multidimensional analysis method is improved by optimizing the concurrency strategy, optimizing the data preprocessing in the big data environment, and optimizing the data serialization and deserialization performance. The method overcomes the limitation of the global interpreter lock (GIL), in which the traditional multithreading in AsyncIO cannot effectively utilize the multi-core processor resources. The method also addresses the problems of high memory requirements and weak distributed functions of Pandas in the big data environment, as well as the inefficient performance of PyArrow during serialization and deserialization in the big data environment, thereby achieving the technical purpose of effectively improving the real-time analysis performance and efficiency of the OLAP real-time multidimensional analysis system.

[0123] Embodiment 8

[0124] Figure 3The schematic diagram of the hardware architecture of the computer device 10000 for implementing the OLAP real-time multi-dimensional analysis method based on massive data according to the embodiment of the present application is shown schematically. In some embodiments, the computer device 10000 may be a terminal device such as a smart phone, a wearable device, a tablet computer, a personal computer, a vehicle terminal, a game console, a virtual device, a workbench, a digital assistant, a set-top box, a robot, etc. In other embodiments, the computer device 10000 may be a rack server, a blade server, a tower server, or a cabinet server (including an independent server, or a server cluster composed of multiple servers), etc. Figure 3 As shown, the computer device 10000 includes but is not limited to: a memory 10010, a processor 10020, and a network interface 10030 that can communicate with each other through a system bus. Among them:

[0125] The memory 10010 includes at least one type of computer-readable storage medium, and the readable storage medium includes a flash memory, a hard disk, a multimedia card, a card-type memory (such as an SD or DX memory), a random access memory (RAM), a static random access memory (SRAM), a read-only memory (ROM), an electrically erasable programmable read-only memory (EEPROM), a programmable read-only memory (PROM), a magnetic memory, a magnetic disk, an optical disk, etc. In some embodiments, the memory 10010 may be an internal storage module of the computer device 10000, such as a hard disk or a memory of the computer device 10000. In other embodiments, the memory 10010 may also be an external storage device of the computer device 10000, such as a plug-in hard disk equipped on the computer device 10000, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. Of course, the memory 10010 may also include both the internal storage module of the computer device 10000 and its external storage device. In this embodiment, the memory 10010 is generally used to store the operating system and various application software installed in the computer device 10000, such as the program code of the OLAP real-time multidimensional analysis method, etc. In addition, the memory 10010 can also be used to temporarily store various data that have been output or will be output.

[0126] In some embodiments, the processor 10020 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other chips. The processor 10020 is generally used to control the overall operation of the computer device 10000, such as performing control and processing related to data interaction or communication with the computer device 10000. In this embodiment, the processor 10020 is used to run the program code stored in the memory 10010 or process data.

[0127] The network interface 10030 may include a wireless network interface or a wired network interface, and the network interface 10030 is generally used to establish a communication link between the computer device 10000 and other computer devices. For example, the network interface 10030 is used to connect the computer device 10000 to an external terminal through a network, and to establish a data transmission channel and a communication link between the computer device 10000 and the external terminal. The network may be a wireless or wired network such as an intranet, the Internet, the Global System of Mobile communication (GSM), Wideband Code Division Multiple Access (WCDMA), 4G network, 5G network, Bluetooth, Wi-Fi, etc.

[0128] It should be pointed out that Figure 3 Only a computer device having components 10010 - 10030 is shown, but it should be understood that implementation of all of the components shown is not a requirement, and more or fewer components may alternatively be implemented.

[0129] In this embodiment, the OLAP real-time multidimensional analysis method stored in the memory 10010 can also be divided into one or more program modules and executed by one or more processors (such as processor 10020) to complete the technical solution described in the embodiment of the present application.

[0130] Embodiment 9

[0131] The embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored, wherein when the computer program is executed by a processor, the steps of the OLAP real-time multidimensional analysis method in the embodiment are implemented.

[0132] In this embodiment, the computer-readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (for example, SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEP ROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the computer-readable storage medium can be an internal storage unit of a computer device, such as a hard disk or memory of the computer device. In other embodiments, the computer-readable storage medium can also be an external storage device of a computer device, such as a plug-in hard disk equipped on the computer device, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. Of course, the computer-readable storage medium can also include both the internal storage unit of the computer device and its external storage device. In this embodiment, the computer-readable storage medium is generally used to store the operating system and various application software installed on the computer device, such as the program code of the OLAP real-time multidimensional analysis method in the embodiment, etc. In addition, the computer-readable storage medium can also be used to temporarily store various types of data that have been output or are to be output.

[0133] Embodiment 10

[0134] A computer program product is also provided in an embodiment of the present application, including a computer program, which implements the method in the above embodiment when executed by a processor.

[0135] Obviously, those skilled in the art should understand that the modules or steps of the above-mentioned embodiments of the present application can be implemented by general-purpose computer devices, they can be concentrated on a single computer device, or distributed on a network composed of multiple computer devices, optionally, they can be implemented by executable program codes of computer devices, so that they can be stored in a storage device and executed by the computer device, and in some cases, the steps shown or described can be executed in a different order from that herein, or they can be made into individual integrated circuit modules, or multiple modules or steps therein can be made into a single integrated circuit module for implementation. In this way, the embodiments of the present application are not limited to any specific combination of hardware and software.

[0136] It should be noted that the above are only preferred embodiments of the present application, and the patent protection scope of the present application is not limited thereto. Any equivalent structure or equivalent process transformation made using the contents of the specification and drawings of the present application, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A real-time multidimensional analysis method of OLAP based on massive data, characterized in that: The real-time multidimensional analysis method is used in an OLAP real-time multidimensional analysis system, which is a big data analysis system that uses ClickHouse as a database management system, Pandas as a data processing and analysis library, an asynchronous non-blocking IOAsyncIO network mode, and interacts with data through the PyArrow protocol; The OLAP real-time multidimensional analysis method includes at least one of the following four optimization operations: ClickHouse design optimization: Use ClickHouse to store the source data that needs to be analyzed, and optimize queries by adding data update and insertion functions and adding index strategies; Concurrency strategy adjustment and optimization: By using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO; Data preprocessing optimization: Use columnar data storage methods and combine Pandas with distributed computing frameworks to optimize data structures; Data serialization and deserialization performance optimization: adjust the serialization and deserialization in the PyAr row protocol according to the characteristics of the data set and the network bandwidth.

2. The OLAP real-time multidimensional analysis method according to claim 1, characterized in that: The data update and insertion functions are implemented in the following ways: After lightly pre-aggregating the data in the experimental dimension on the offline side, synchronize it to ClickHouse in real time; For special data that requires detailed data, synchronize it to ClickHouse in real time.

3. The OLAP real-time multidimensional analysis method according to claim 1 or 2, characterized in that: The added index strategy to optimize the query function includes: When querying data, we add index strategies and apply memory computing methods to cache common query calculation results in memory to optimize queries.

4. The OLAP real-time multidimensional analysis method according to claim 3, characterized in that: The cache is a segmented cache of data at different levels, which is cached locally in parquet format through PyArrow.

5. The OLAP real-time multidimensional analysis method according to claim 3 or 4, characterized in that: The memory computing method includes: minimizing DB query and network IO operations and accelerating analysis results by memory computing; The common query calculation results are presented in the form of data cubes in the memory, and are used to generate data report results in multiple analysis dimensions.

6. The OLAP real-time multidimensional analysis method according to claim 5, characterized in that: The data structure of the data cube in memory is obtained by aggregation through the following data aggregation operations: The original data is grouped according to business rules, and the original data is aggregated using an aggregation function, and a unique label is assigned to each group; Re-execute the aggregation operation on each group and merge the aggregation results into a data structure; Obtain the aggregation type, set the corresponding mapping relationship according to the value of the aggregation result, and apply the aggregation result to the corresponding label; Use the combination function pd.concat to concatenate multiple data structures together, sort them from high to low according to the charge field, and obtain the data structure of the final result.

7. The OLAP real-time multidimensional analysis method according to claim 1, characterized in that: The OLAP real-time multidimensional analysis system also includes: a data service platform; The data service platform establishes a network connection with Pandas through AsyncIO, provides HTTP / gRPC service to access data, and directly obtains data of specific dimensions / indicators from the data service platform through data services.

8. An OLAP real-time multidimensional analysis system based on massive data, characterized in that: The OLAP real-time multidimensional analysis system is a big data analysis system that uses ClickHouse as a database management system, Pandas as a data processing and analysis library, an asynchronous non-blocking IOAsyncIO network mode, and interacts with data through the PyArrow protocol; The OLAP real-time multidimensional analysis system is optimized by at least one of the following four optimization operations: ClickHouse design optimization: Use ClickHouse to store the source data that needs to be analyzed, and optimize the design of ClickHouse by adding data update and insertion functions and adding index strategies to optimize query functions; Concurrency strategy adjustment and optimization: By using Python's multi-process module, a separate Python process is used for each concurrent I / O operation of AsyncIO; Data preprocessing optimization: Use columnar data storage methods and combine Pandas with distributed computing frameworks to optimize data structures; Data serialization and deserialization performance optimization: Adjust the serialization and deserialization strategies and processes in the PyAr row protocol according to the characteristics of the dataset and network bandwidth.

9. The OLAP real-time multidimensional analysis system according to claim 8, characterized in that: The OLAP real-time multidimensional analysis system also includes: a data service platform; The data service platform establishes a network connection with Pandas through AsyncIO, provides HTTP / gR PC service to access data, and directly obtains data of specific dimensions / indicators from the data service platform through data services.

10. A computer device, characterized in that: include: at least one processor; and, a memory communicatively coupled to the at least one processor; The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the real-time multidimensional analysis method according to any one of claims 1 to 7.

11. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and when the computer instructions are executed by a processor, the real-time multidimensional analysis method according to any one of claims 1 to 7 is implemented.

12. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the real-time multidimensional analysis method according to claims 1 to 7 are implemented.

Images