Self-adaptive data merging and optimizing method and system applied to Clickhome MergeTree engine

Abstract

The invention provides a self-adaptive data merging and optimizing method and system applied to a Clickhome MergeTree engine, and the method comprises the steps: generating a data merging and optimizing task based on data in the Clickhome MergeTree engine by adopting a self-adaptive data merging triggering mechanism; an intelligent data folding strategy is adopted, and combined and optimized data in the data combined and optimized task are combined, folded and archived; a consistency guarantee algorithm is adopted, data consistency verification is carried out on the merged and optimized data after merging, folding and archiving processing, and a data merging and optimizing task is completed; according to the method, data merging and folding operations can be intelligently and autonomously completed by adopting a dynamic analysis decision mechanism and an intelligent data folding strategy for the data in the MergeTree engine of the ClickHouse database, the data consistency during the data merging and folding operations is ensured through a consistency guarantee algorithm, and the performance of the MergeTree engine of the ClickHouse database is improved.

Description

Technical Field

[0001] The patent application of this invention belongs to the field of computer technology, and specifically relates to an adaptive data merging and optimization method and system applied to the ClickhouseMergeTree engine. Background Art

[0002] With the rapid development of the big data era, the storage and efficient query of massive data have become an important research direction in the field of information technology. As a column-based database, ClickHouse has been widely used in large-scale data analysis scenarios with its high-performance query capabilities and flexible data storage methods. In the ClickHouse system, the MergeTree storage engine has become one of its core components due to its efficient data storage, index construction, and query optimization functions. However, there are still many problems with the current MergeTree engine's merging and optimization mechanism, which limits its performance and resource utilization efficiency in dynamic load and high concurrency scenarios.

[0003] In the MergeTree (MergeTree storage) engine of ClickHouse (column-based database), data is stored in columnar format, which optimizes the reading performance of large-scale data. However, after the data update operation, data merging and folding operations need to be manually triggered to improve query performance and free up storage space; this process not only increases the user's operational burden, but may also lead to performance degradation and data inconsistency due to improper operation timing. Summary of the invention

[0004] In order to overcome the above-mentioned deficiencies of the prior art, the present invention patent application proposes an adaptive data merging and optimization method applied to the ClickhouseMergeTree engine, including: Based on the data in the Clickhouse MergeTree engine, an adaptive data merge trigger mechanism is used to generate data merge optimization tasks; Adopting an intelligent data folding strategy to merge, fold and archive the merged and optimized data in the data merging and optimization task; A consistency assurance algorithm is used to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing, so as to complete the data merging and optimization task.

[0005] Preferably, the data in the Clickhouse MergeTree engine is used to generate a data merging optimization task by using an adaptive data merging trigger mechanism, including: Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time; Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request; When the dynamic analysis result meets the merge optimization trigger condition, the data merge optimization task is generated based on the data of the Clickhouse MergeTree engine; Among them, the data update and query request include one or more of the following: the amount of updated data and the complexity of the query; the complex event machine processing technology includes complex event processing technology and machine learning algorithm.

[0006] Preferably, after the data in the Clickhouse MergeTree engine is used to generate the data merging optimization task by using an adaptive data merging trigger mechanism, the method further includes: Real-time monitoring of resource usage in Clickhouse MergeTree engine; According to the resource usage, combined with the task type and task data volume of the data merging and optimizing task, dynamically adjust the execution state of the data merging and optimizing task through the task scheduler; Among them, the resource usage includes one or more of the following: CPU usage, memory occupancy and I / O throughput; the execution status includes one or more of the following: execution start, suspended execution, execution termination, execution pause and execution termination.

[0007] Preferably, the merged optimized data includes one or more of the following: merged data, folded data and archived data; the intelligent data folding strategy is adopted to merge, fold and archive the merged optimized data in the data merging optimization task, including: Based on the data merging optimization task combined with the data update and query request, data mining and pattern recognition technology are used to identify the access pattern and access popularity of the data segment in the Clickhouse MergeTree engine; According to the access mode and access popularity of the data segment, by means of pre-set data classification and data priority, determining the merged data, folded data and archived data in the data segment; respectively merging the merged data to generate secondary merged data, folding the folded data to generate secondary folded data, and archiving the archived data to generate secondary archived data; According to data-related factors, the secondary merged data, the secondary folded data and the secondary archived data are stored in corresponding storage media; The data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

[0008] Preferably, the access heat mathematical expression of the data segment is as follows: in, For data segment The visit popularity, is the jth data segment, are weight coefficients used to balance access frequency and temporal freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0009] Preferably, the consistency guarantee algorithm includes: an optimistic locking mechanism, a data version control mechanism and a Merkle hash tree; the consistency guarantee algorithm is used to perform data consistency verification on the merged optimization data after the merge, fold and archive processing to complete the data merge optimization task, including: Adopting optimistic locking mechanism and data version control mechanism to check the version number of the merged optimized data; After completing the verification of the version number, using a Merkle hash tree, respectively calculate the hash values ​​of the merged data, the folded data, the archived data, the secondary merged data, the secondary folded data, and the secondary archived data; When the hash values ​​of the merged data and the secondary merged data, the hash values ​​of the folded data and the secondary folded data, and the hash values ​​of the archived data and the secondary archived data are consistent, the data merging optimization task is completed; When there is an inconsistency in the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data, a data rollback merge operation is performed to perform data verification until the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0010] Based on the same inventive concept, the patent application of the present invention also provides an adaptive data merging and optimization system applied to the Clickhouse MergeTree engine, including: a data merging optimization task generation module, a data optimization module and a data verification module; The data merging optimization task generation module is used to generate data merging optimization tasks based on the data in the Clickhouse MergeTree engine using an adaptive data merging trigger mechanism; The data optimization module is used to adopt an intelligent data folding strategy to merge, fold and archive the merged and optimized data in the data merging and optimization task; The data verification module is used to use a consistency assurance algorithm to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing, so as to complete the data merging and optimization task.

[0011] Preferably, the data merging optimization task generation module is specifically used to: Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time; Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request; When the dynamic analysis result meets the merge optimization trigger condition, the data merge optimization task is generated based on the data of the Clickhouse MergeTree engine; Among them, the data update and query request include one or more of the following: the amount of updated data and the complexity of the query; the complex event machine processing technology includes complex event processing technology and machine learning algorithm.

[0012] Preferably, the system further comprises: a task dynamic adjustment module, which is used to: Real-time monitoring of resource usage in Clickhouse MergeTree engine; According to the resource usage, combined with the task type and task data volume of the data merging and optimizing task, dynamically adjust the execution state of the data merging and optimizing task through the task scheduler; Among them, the resource usage includes one or more of the following: CPU usage, memory occupancy and I / O throughput; the execution status includes one or more of the following: execution start, suspended execution, execution termination, execution pause and execution termination.

[0013] Preferably, the data optimization module is specifically used for: Based on the data merging optimization task combined with the data update and query request, data mining and pattern recognition technology are used to identify the access pattern and access popularity of the data segment in the Clickhouse MergeTree engine; According to the access mode and access popularity of the data segment, by means of pre-set data classification and data priority, determining the merged data, folded data and archived data in the data segment; respectively merging the merged data to generate secondary merged data, folding the folded data to generate secondary folded data, and archiving the archived data to generate secondary archived data; According to data-related factors, the secondary merged data, the secondary folded data and the secondary archived data are stored in corresponding storage media; The data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

[0014] Preferably, the access heat mathematical expression of the data segment is as follows: in, For data segment The visit popularity, is the jth data segment, are weight coefficients used to balance access frequency and temporal freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0015] Preferably, the consistency guarantee algorithm includes: an optimistic locking mechanism, a data version control mechanism and a Merkle hash tree; the data verification module includes: Adopting optimistic locking mechanism and data version control mechanism to check the version number of the merged optimized data; After completing the verification of the version number, using a Merkle hash tree, respectively calculate the hash values ​​of the merged data, the folded data, the archived data, the secondary merged data, the secondary folded data, and the secondary archived data; When the hash values ​​of the merged data and the secondary merged data, the hash values ​​of the folded data and the secondary folded data, and the hash values ​​of the archived data and the secondary archived data are consistent, the data merging optimization task is completed; When there is an inconsistency in the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data, a data rollback merge operation is performed to perform data verification until the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0016] Based on the same inventive concept, the patent application of the present invention also provides an electronic device, comprising: at least one processor and a memory; the memory and the processor are connected via a bus; The memory is used to store one or more programs; When the one or more programs are executed by the at least one processor, an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine as described above is implemented.

[0017] Based on the same inventive concept, the patent application of the present invention also provides a readable storage medium on which a computer program and an execution program are stored. When the execution program is executed, an adaptive data merging and optimization method applied to the ClickhouseMergeTree engine as described above is implemented.

[0018] Compared with the closest prior art, the patent application of this invention has the following beneficial effects: The patent application of the present invention provides an adaptive data merging and optimization method and system applied to the Clickhouse MergeTree engine, including: based on the data in the Clickhouse MergeTree engine, an adaptive data merging trigger mechanism is used to generate a data merging optimization task; an intelligent data folding strategy is used to merge, fold and archive the merged optimization data in the data merging optimization task; a consistency guarantee algorithm is used to perform data consistency verification on the merged optimization data after the merge, fold and archive processing to complete the data merging optimization task; the present invention generates a data merging optimization task through data updates and query requests in the MergeTree engine of the ClickHouse database, and can intelligently and autonomously complete data merging and folding operations through a dynamic analysis decision mechanism and an intelligent data folding strategy in turn, and through a consistency guarantee algorithm, data consistency during data merging and folding operations is ensured, thereby improving the performance of the MergeTree engine of the ClickHouse database. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 A schematic flow chart of an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine provided in the patent application of the present invention; Figure 2 A schematic diagram of the adaptive data merging process provided for the patent application of the present invention; Figure 3 A schematic diagram of an adaptive data merging and optimization system applied to the Clickhouse MergeTree engine provided for the patent application of the present invention; Figure 4 A schematic diagram of the structure of an electronic device provided for the patent application of the present invention. DETAILED DESCRIPTION

[0020] The specific implementation methods of the patent application of the present invention are further described in detail below in conjunction with the accompanying drawings.

[0021] Embodiment 1: The present invention patent application provides an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine. Figure 1 As shown, including: Step 1: Based on the data in the Clickhouse MergeTree engine, an adaptive data merge trigger mechanism is used to generate data merge optimization tasks; Step 2: Adopting an intelligent data folding strategy, merging and folding the merged and optimized data in the data merging and optimization task to archive the merged and optimized data; Step 3: Use a consistency assurance algorithm to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing to complete the data merging and optimization task.

[0022] In one implementation, the above step 1 uses an adaptive data merge trigger mechanism based on the data in the Clickhouse MergeTree engine to generate a data merge optimization task, including: Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time; For example, a listener is designed to monitor data update operations and query requests in real time, and based on this, the trigger threshold of the merge operation is dynamically adjusted. Specifically, lightweight monitoring technology is used to capture data updates and query requests in real time, including key indicators such as the amount of updated data and the complexity of the query, and these indicators are fed back to the dynamic analysis and decision-making mechanism in real time.

[0023] Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request; For example, for the dynamic analysis and decision-making mechanism, we first define the data update frequency within the time window. The mathematical expression of the data update frequency is as follows:

[0024] in, is the time window at time t The data update frequency is t, t is the time instant, is the time window, For the time window The time point of the update operation that occurs within for The size of the point-in-time data update operation; For example, the dynamic analysis and decision-making mechanism is to analyze the collected data through complex event processing (CEP) technology or machine learning algorithm, identify the changing trends of data update frequency and query patterns, and dynamically adjust the trigger thresholds and strategies of data merging based on these trends to achieve optimal data processing efficiency.

[0025] For example, the mathematical expression for dynamically adjusting the trigger threshold for data merging is as follows: in, is the trigger threshold for data merging after adjustment at time t, is the basic threshold, is the current system load at time t, is the maximum load threshold of the system.

[0026] When the dynamic analysis result meets the merge optimization trigger condition, the data merge optimization task is generated based on the data of the Clickhouse MergeTree engine; For example, based on the dynamic analysis results, when the system detects that specific conditions are met, such as the data update frequency reaches a certain threshold, it automatically generates a data merging optimization task and uses advanced task scheduling algorithms to ensure efficient execution of the data merging operation.

[0027] For example, an adaptive algorithm is used to analyze the data blocks stored in the MergeTree engine, and the data merging strategy is dynamically adjusted based on the size, distribution and access frequency of the data to avoid unnecessary full merge operations. According to the designed intelligent merging mechanism, the appropriate merging time is automatically selected by monitoring the system load and query requests to reduce the impact of the merge on system performance.

[0028] For example, the task scheduling involved in the automatic merge trigger can be dynamically managed and scheduled using container orchestration tools such as Kubernetes; when the decision mechanism determines that a data merge operation needs to be performed, the system submits a merge task request to the Kubernetes API, and Kubernetes decides whether to start the task immediately or execute it later based on the current cluster resource usage and priority policy. In this way, even in the case of tight resources, the system can ensure that normal query operations will not be affected by executing data merge tasks.

[0029] Among them, the data update and query request include one or more of the following: the amount of updated data and the complexity of the query; the complex event machine processing technology includes complex event processing technology and machine learning algorithm.

[0030] The adaptive data merging trigger mechanism of the present invention can trigger data merging by combining real-time monitoring with adaptive algorithms to adapt to the dynamic changes of data updates and queries. The real-time monitoring module is responsible for capturing data update operations and query requests within the system. By analyzing the frequency and pattern of these activities, the adaptive algorithm dynamically adjusts the triggering conditions of the merging operation according to the analysis results to optimize system performance and response speed.

[0031] In one implementation, after the data in the Clickhouse MergeTree engine is used to generate the data merging optimization task by using an adaptive data merging trigger mechanism, the method further includes: Real-time monitoring of resource usage in Clickhouse MergeTree engine; For example, a scheduling algorithm based on priority and resource utilization is designed to dynamically manage data merging and optimization tasks, specifically: real-time monitoring of system resource usage to provide data support for scheduling decisions.

[0032] According to the resource usage, combined with the task type and task data volume of the data merging and optimizing task, dynamically adjust the execution state of the data merging and optimizing task through the task scheduler; For example, an intelligent resource-aware task scheduler is to develop an efficient task scheduler that intelligently manages and schedules data to merge and optimize tasks, and dynamically adjusts the priority and execution frequency of tasks based on system resource usage and system load.

[0033] For example, based on the feedback from resource usage monitoring, the task scheduler needs to be able to dynamically adjust the priority and execution strategy of the background data merging task. The task scheduler must not only understand the importance and urgency of each data merging optimization task, but also be able to predict its impact on system resources. The task scheduler can dynamically adjust the execution plan of the data merging optimization task based on the task type, data size, and current resource usage of the system.

[0034] For example, the task scheduler automatically adjusts the priority of background tasks according to the system load to balance query performance and data merging efficiency; for data merging optimization tasks, if the current I / O (Input / Output) throughput of the system is already very high, the task scheduler can decide to delay the execution of some non-urgent merging tasks, or schedule them to a period when the system load is lower. In addition, the task scheduler can also adjust the resource allocation of tasks, such as limiting the number of CPU (Central Processing Unit) cores or disk I / O bandwidth used, to reduce the overall impact on the system.

[0035] Among them, the resource usage includes one or more of the following: CPU usage, memory occupancy and I / O throughput; the execution status includes one or more of the following: execution start, suspended execution, execution termination, execution pause and execution termination.

[0036] For example, another key function of the task scheduler is to manage task execution, which involves operations such as starting, pausing, continuing, and canceling data merging and optimization tasks. The task scheduler should be able to intelligently manage these operations based on the priority of the task and the resource status of the system. This requires the task scheduler to be tightly integrated with the underlying operating system or container management platform (such as the Kubernetes container orchestration tool mentioned above) so that the life cycle of the task can be directly controlled.

[0037] For example, when a data merging optimization task is suspended due to excessive system load, the task scheduler needs to be able to monitor changes in system resources and automatically restart the task once it detects that there is enough free system resources. Similarly, if a running data merging optimization task suddenly becomes less urgent, the task scheduler also needs to be able to lower its priority in real time or temporarily suspend it to free up resources for more urgent tasks. For example, Figure 2 The figure shows the adaptive data merging process. The present invention can also use a conflict detection algorithm to perform conflict detection, specifically as follows: using an automatic folding trigger to trigger and execute parallel folding processing on the data involved in the data merging optimization task to complete the adaptive data merging; For example, it also supports parallel data merging operations, effectively utilizing multi-core processor resources and accelerating the merging and optimization process of large-scale data sets.

[0038] For example, a real-time monitoring and feedback mechanism is also provided. Administrators can view the progress of merging and optimization and system performance in real time through the monitoring interface and make necessary adjustments.

[0039] Through such an intelligent perception task scheduler, the ClickHouse MergeTree engine can manage data merging and optimization tasks more intelligently and efficiently, which can not only ensure the efficiency and responsiveness of data processing, but also ensure that system resources are reasonably utilized to avoid affecting the performance of foreground services due to the execution of background tasks; this highly dynamic and adaptive scheduling strategy is the key to achieving efficient data processing and query response. The present invention dynamically starts, pauses or adjusts the execution strategy according to the importance and urgency of the task, reduces the impact on the foreground operation, and improves the overall performance of the system; the present invention designs a background task scheduler to intelligently schedule data merging tasks according to the current resource usage of the system (CPU, memory, I / O) to avoid affecting the query performance of the foreground; the adaptive data merging and optimization algorithm is introduced through the adaptive data merging trigger mechanism, which can significantly improve the performance and data consistency of the ClickHouse MergeTree engine when processing update operations. This technical innovation not only reduces the need for manual intervention, but also provides an efficient and automated solution for large-scale data processing. The application of this algorithm will greatly enhance the competitiveness and reliability of the ClickHouse database in enterprise-level applications.

[0040] In one implementation, the merged optimized data includes one or more of the following: merged data, folded data, and archived data; the intelligent data folding strategy described in step 2 is adopted to merge, fold, and archive the merged optimized data in the data merge optimization task, including: Based on the data merging optimization task combined with the data update and query request, data mining and pattern recognition technology are used to identify the access pattern and access popularity of the data segment in the Clickhouse MergeTree engine; For example, data pattern analysis is used to analyze data update operations and query requests in the system using data mining and pattern recognition techniques to identify data access patterns and hot spots; the analysis can be based on historical log records. For example, by analyzing query logs over a period of time, frequently accessed data tables or columns can be identified.

[0041] According to the access mode and access popularity of the data segment, by means of pre-set data classification and data priority, determining the merged data, folded data and archived data in the data segment; respectively merging the merged data to generate secondary merged data, folding the folded data to generate secondary folded data, and archiving the archived data to generate secondary archived data; According to data-related factors, the secondary merged data, the secondary folded data and the secondary archived data are stored in corresponding storage media; For example, it provides data compression optimization function, selects the optimal compression algorithm according to different data types and block sizes, and reduces storage space occupancy; the system has a self-adjustment function, which can dynamically adjust the MergeTree index and data block storage structure according to historical query data and performance analysis results to improve query efficiency.

[0042] The data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

[0043] The example also includes dynamic adjustment and feedback loops, specifically: the system needs to regularly re-evaluate the popularity and access patterns of the data, and dynamically adjust the intelligent data folding strategy and storage strategy; in addition, establish a feedback mechanism to adjust the prediction model and decision logic according to changes in actual query performance and storage efficiency to achieve continuous optimization.

[0044] For example, the design of the intelligent data folding strategy focuses on an in-depth understanding of the data life cycle, update pattern and query frequency. The strategy determines which data segments should be merged, folded or archived through intelligent classification and priority sorting to optimize the physical storage structure of the data. The main considerations include the frequency of data access (heat), the timeliness of the data, and the relevance of the data. For data columns that are frequently queried together, their storage and access speed can be optimized through column storage; for "cold data" that has not been accessed for a long time, it can be considered to be folded or archived to a lower-cost storage medium; the intelligent data folding strategy of the present invention, by analyzing the data update pattern and query pattern, intelligently determines which data segments to merge and fold, so as to optimize the data storage structure, reduce storage occupancy and increase query speed.

[0045] In one implementation, the access popularity mathematical expression of the data segment is as follows: in, For data segment The visit popularity, is the jth data segment, are weight coefficients used to balance access frequency and temporal freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0046] In one implementation, the consistency guarantee algorithm includes: an optimistic locking mechanism, a data version control mechanism and a Merkle hash tree; the consistency guarantee algorithm described in step 3 above is used to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing, and complete the data merge optimization task, including: Adopting optimistic locking mechanism and data version control mechanism to check the version number of the merged optimized data; For example, when implementing the data version control mechanism and the optimistic locking mechanism, each data shard in the merged optimized data will be assigned a unique version number; when an update operation request arrives, the system first checks whether the version number carried in the request is consistent with the currently stored version number; if they are consistent, the system will execute the update operation and increment the version number of the shard to reflect the latest status of the data; this mechanism can effectively avoid data inconsistency problems caused by concurrent updates.

[0047] After completing the verification of the version number, using a Merkle hash tree, respectively calculate the hash values ​​of the merged data, the folded data, the archived data, the secondary merged data, the secondary folded data, and the secondary archived data; For example, using Merkle hash tree for data integrity verification is another key implementation method; Merkle hash tree is a hash tree in which each leaf node contains the hash value of the data block, and non-leaf nodes contain the hash of the hash values ​​of their child nodes. By comparing the Merkle tree root hash values ​​of the data shards before and after the merge, it is possible to efficiently verify whether the data remains consistent.

[0048] For example, before the data merge operation begins, the system will build a Merkle tree for all data shards to be merged and calculate a root hash value; after the merge operation is completed, the Merkle tree is built again for the merged data and a new root hash value is calculated.

[0049] When the hash values ​​of the merged data and the secondary merged data, the hash values ​​of the folded data and the secondary folded data, and the hash values ​​of the archived data and the secondary archived data are consistent, the data merging optimization task is completed; When there is an inconsistency in the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data, a data rollback merge operation is performed to perform data verification until the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0050] For example, if the root hash values ​​before and after the merge are consistent, it means that the merge operation does not introduce any data inconsistency; if they are inconsistent, the system needs to roll back the merge operation or take other measures to restore data consistency; the consistency guarantee algorithm of the present invention introduces data version control and locking mechanisms while merging and folding data to ensure data consistency and the security of concurrent operations.

[0051] For example, consider a simple data merge scenario, which contains two data blocks A and B. Before the merge, the hash value HA and the hash value HB are calculated for A and B respectively, and then the parent hash HAB of these two hash values ​​is calculated as the root hash. After the two blocks are merged, if the merge operation is correct, recalculating the hash value for the merged data block should obtain a result consistent with HAB. This method is particularly suitable for scenarios where absolute data consistency needs to be ensured, such as financial data processing, medical record management, etc.; the consistency guarantee algorithm of the present invention is to use data version control, optimistic locking mechanism and data consistency verification technology to ensure data consistency and operation security during the data merging and folding process; optimistic locking mechanism and data version control mechanism to ensure data consistency of concurrent update operations; before and after the merge operation, use data snapshots and consistent hashing technology to perform data integrity and consistency verification to ensure the accuracy and integrity of data during the merge process; The present invention generates data merging optimization tasks through data update and query requests in the MergeTree engine of the ClickHouse database, and can intelligently and autonomously complete data merging and folding operations through dynamic analysis and decision-making mechanisms and intelligent data folding strategies. The consistency guarantee algorithm ensures data consistency during data merging and folding operations, thereby improving the performance of the MergeTree engine of the ClickHouse database. The present invention proposes an adaptive data merging and optimization method for the Clickhouse MergeTree engine, which can dynamically adjust the merging strategy according to data characteristics to improve query performance and storage efficiency; the present invention improves data merging efficiency and optimizes storage performance by dynamically analyzing and adjusting the data blocks of the MergeTree storage engine. The method is based on an adaptive algorithm, combines data characteristics, access frequency and system load conditions, intelligently selects merging strategies, avoids unnecessary full-volume merging operations, and reduces the impact of the merging process on system performance. The system supports selecting the optimal compression algorithm according to different data types, reduces storage space occupancy, and adjusts indexes and data storage structures through historical query data to improve query efficiency. The present invention is applicable to large-scale data sets, supports parallel merging operations, makes full use of multi-core processor resources, accelerates the merging process, and is applicable to scenarios requiring efficient data storage and query.

[0052] Embodiment 2: Based on the same inventive concept, the present invention patent application also provides an adaptive data merging and optimization system applied to the ClickhouseMergeTree engine. Figure 3 As shown, it includes: a data merging optimization task generation module, a data optimization module and a data verification module; The data merging optimization task generation module is used to generate data merging optimization tasks based on the data in the Clickhouse MergeTree engine using an adaptive data merging trigger mechanism; The data optimization module is used to adopt an intelligent data folding strategy to merge, fold and archive the merged and optimized data in the data merging and optimization task; The data verification module is used to use a consistency assurance algorithm to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing, so as to complete the data merging and optimization task.

[0053] Preferably, the data merging optimization task generation module is specifically used to: Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time; Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request; When the dynamic analysis result meets the merge optimization trigger condition, the data merge optimization task is generated based on the data of the Clickhouse MergeTree engine; Among them, the data update and query request include one or more of the following: the amount of updated data and the complexity of the query; the complex event machine processing technology includes complex event processing technology and machine learning algorithm.

[0054] Preferably, the system further comprises: a task dynamic adjustment module, which is used to: Real-time monitoring of resource usage in Clickhouse MergeTree engine; According to the resource usage, combined with the task type and task data volume of the data merging and optimizing task, dynamically adjust the execution state of the data merging and optimizing task through the task scheduler; Among them, the resource usage includes one or more of the following: CPU usage, memory occupancy and I / O throughput; the execution status includes one or more of the following: execution start, suspended execution, execution termination, execution pause and execution termination.

[0055] Preferably, the data optimization module is specifically used for: Based on the data merging optimization task combined with the data update and query request, data mining and pattern recognition technology are used to identify the access pattern and access popularity of the data segment in the Clickhouse MergeTree engine; According to the access mode and access popularity of the data segment, by means of pre-set data classification and data priority, determining the merged data, folded data and archived data in the data segment; respectively merging the merged data to generate secondary merged data, folding the folded data to generate secondary folded data, and archiving the archived data to generate secondary archived data; According to data-related factors, the secondary merged data, the secondary folded data and the secondary archived data are stored in corresponding storage media; The data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

[0056] Preferably, the access heat mathematical expression of the data segment is as follows: in, For data segment The visit popularity, is the jth data segment, are weight coefficients used to balance access frequency and temporal freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0057] Preferably, the consistency guarantee algorithm includes: an optimistic locking mechanism, a data version control mechanism and a Merkle hash tree; the data verification module includes: Adopting optimistic locking mechanism and data version control mechanism to check the version number of the merged optimized data; After completing the verification of the version number, using a Merkle hash tree, respectively calculate the hash values ​​of the merged data, the folded data, the archived data, the secondary merged data, the secondary folded data, and the secondary archived data; When the hash values ​​of the merged data and the secondary merged data, the hash values ​​of the folded data and the secondary folded data, and the hash values ​​of the archived data and the secondary archived data are consistent, the data merging optimization task is completed; When there is an inconsistency in the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data, a data rollback merge operation is performed to perform data verification until the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0058] Example 3 like Figure 4 As shown, the present invention also provides an electronic device, which may be a computer device, a single-chip device, an intelligent mobile device, etc. The electronic device in this embodiment may include a processor, a memory, a transceiver component, etc. The memory, the processor, and the transceiver component are connected via a bus; the memory may be used to store an execution program, and an exemplary execution program may include instructions; the processor is used to execute the instructions stored in the memory. The memory may also be used to store data, which may be called and / or modified when the instructions are executed.

[0059] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components, etc. It is the computing core and control core of the terminal, which is suitable for implementing one or more instructions, specifically suitable for loading and executing one or more instructions in a storage medium to implement corresponding method flows or corresponding functions, so as to implement the steps of an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine in the above-mentioned embodiment.

[0060] Example 4 Based on the same inventive concept, the present invention also provides a readable storage medium, specifically an electronic device readable storage medium (Memory), which is a memory device in an electronic device for storing programs and data. It can be understood that the storage medium here can include both built-in storage media in electronic devices and, of course, extended storage media supported by electronic devices. The storage medium provides a storage space, which stores the operating system of the terminal. In addition, one or more instructions suitable for being loaded and executed by a processor are also stored in the storage space, and these instructions can be one or more execution programs (including program codes). It should be noted that the storage medium here can be a high-speed RAM memory or a non-volatile memory, such as at least one disk memory. The processor loads and executes one or more instructions stored in the storage medium, which can implement the steps of an adaptive data merging and optimization method applied to the ClickhouseMergeTree engine in the above embodiment.

[0061] It should be understood by those skilled in the art that the embodiments of the present invention patent application can be provided as methods, systems, or computer program products. Therefore, the present invention patent application can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention patent application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0062] The present invention patent application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present invention patent application. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of the processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0063] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0064] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0065] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the patent application of the present invention rather than to limit its protection scope. Although the patent application of the present invention is described in detail with reference to the above embodiments, ordinary technicians in the relevant field should understand that after reading the patent application of the present invention, those skilled in the art can still make various changes, modifications or equivalent substitutions to the specific implementation methods of the application, but these changes, modifications or equivalent substitutions are all within the protection scope of the claims to be approved.

Claims

1. An adaptive data merging and optimization method applied to the Clickhouse MergeTree engine, characterized in that: include: Based on the data in the Clickhouse MergeTree engine, an adaptive data merge trigger mechanism is used to generate data merge optimization tasks; Adopting an intelligent data folding strategy to merge, fold and archive the merged and optimized data in the data merging and optimization task; A consistency assurance algorithm is used to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing, so as to complete the data merging and optimization task.

2. The method according to claim 1, characterized in that Based on the data in the Clickhouse MergeTree engine, an adaptive data merging trigger mechanism is used to generate data merging optimization tasks, including: Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time; Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request; When the dynamic analysis result meets the merge optimization trigger condition, the data merge optimization task is generated based on the data of the Clickhouse MergeTree engine; Among them, the data update and query request include one or more of the following: the amount of updated data and the complexity of the query; the complex event machine processing technology includes complex event processing technology and machine learning algorithm.

3. The method according to claim 1 or 2, characterized in that The method further includes: generating data merging optimization tasks by using an adaptive data merging trigger mechanism based on the data in the Clickhouse MergeTree engine; Real-time monitoring of resource usage in Clickhouse MergeTree engine; According to the resource usage, combined with the task type and task data volume of the data merging and optimizing task, dynamically adjust the execution state of the data merging and optimizing task through the task scheduler; Among them, the resource usage includes one or more of the following: CPU usage, memory occupancy and I / O throughput; the execution status includes one or more of the following: execution start, suspended execution, execution termination, execution pause and execution termination.

4. The method according to claim 2, characterized in that The merged optimized data includes one or more of the following: merged data, folded data and archived data; The intelligent data folding strategy is adopted to merge, fold and archive the merged and optimized data in the data merging and optimization task, including: Based on the data merging optimization task combined with the data update and query request, data mining and pattern recognition technology are used to identify the access pattern and access popularity of the data segment in the Clickhouse MergeTree engine; According to the access mode and access popularity of the data segment, by means of pre-set data classification and data priority, determining the merged data, folded data and archived data in the data segment; respectively merging the merged data to generate secondary merged data, folding the folded data to generate secondary folded data, and archiving the archived data to generate secondary archived data; According to data-related factors, the secondary merged data, the secondary folded data and the secondary archived data are stored in corresponding storage media; The data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

5. The method according to claim 4, characterized in that The mathematical expression of the access heat of the data segment is as follows: in, For data segment The visit popularity, is the jth data segment, are weight coefficients used to balance access frequency and temporal freshness. for The frequency of visits, is the current time, for The time of the last visit.

6. The method according to claim 4, characterized in that The consistency guarantee algorithm includes: an optimistic locking mechanism, a data version control mechanism and a Merkle hash tree; the consistency guarantee algorithm is used to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing to complete the data merge optimization task, including: Adopting optimistic locking mechanism and data version control mechanism to check the version number of the merged optimized data; After completing the verification of the version number, using a Merkle hash tree, respectively calculate the hash values ​​of the merged data, the folded data, the archived data, the secondary merged data, the secondary folded data, and the secondary archived data; When the hash values ​​of the merged data and the secondary merged data, the hash values ​​of the folded data and the secondary folded data, and the hash values ​​of the archived data and the secondary archived data are consistent, the data merging optimization task is completed; When there is an inconsistency in the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data, a data rollback merge operation is performed to perform data verification until the hash values ​​between the merged data and the secondary merged data, the hash values ​​between the folded data and the secondary folded data, and the hash values ​​between the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

7. An adaptive data merging and optimization system applied to the Clickhouse MergeTree engine, characterized in that: include: Data merging and optimization task generation module, data optimization module and data verification module; The data merging optimization task generation module is used to generate data merging optimization tasks based on the data in the Clickhouse MergeTree engine using an adaptive data merging trigger mechanism; The data optimization module is used to adopt an intelligent data folding strategy to merge, fold and archive the merged and optimized data in the data merging and optimization task; The data verification module is used to use a consistency assurance algorithm to perform data consistency verification on the merged and optimized data after the merge, fold and archive processing, so as to complete the data merging and optimization task.

8. The system according to claim 7, characterized in that The data merging optimization task generation module is specifically used for: Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time; Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request; When the dynamic analysis result meets the merge optimization trigger condition, the data merge optimization task is generated based on the data of the Clickhouse MergeTree engine; Among them, the data update and query request include one or more of the following: the amount of updated data and the complexity of the query; the complex event machine processing technology includes complex event processing technology and machine learning algorithm.

9. The system according to claim 7 or 8, characterized in that The system further comprises: a task dynamic adjustment module, which is used to: Real-time monitoring of resource usage in Clickhouse MergeTree engine; According to the resource usage, combined with the task type and task data volume of the data merging and optimizing task, dynamically adjust the execution state of the data merging and optimizing task through the task scheduler; Among them, the resource usage includes one or more of the following: CPU usage, memory occupancy and I / O throughput; the execution status includes one or more of the following: execution start, suspended execution, execution termination, execution pause and execution termination.

10. The system according to claim 8, characterized in that The data optimization module is specifically used for: Based on the data merging optimization task combined with the data update and query request, data mining and pattern recognition technology are used to identify the access pattern and access popularity of the data segment in the Clickhouse MergeTree engine; According to the access mode and access popularity of the data segment, by means of pre-set data classification and data priority, determining the merged data, folded data and archived data in the data segment; respectively merging the merged data to generate secondary merged data, folding the folded data to generate secondary folded data, and archiving the archived data to generate secondary archived data; According to data-related factors, the secondary merged data, the secondary folded data and the secondary archived data are stored in corresponding storage media; The data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

Images