An adaptive data merging and optimization method and system applied to the Clickhouse MergeTree engine

Abstract

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 adopted to generate a data merging optimization task; an intelligent data folding strategy is adopted to merge, fold and archive the merged optimization data in the data merging optimization task; a consistency guarantee algorithm is adopted to perform data consistency verification on the merged optimization data after the merge, fold and archive processing, and complete the data merging optimization task; the present invention adopts a dynamic analysis decision mechanism and an intelligent data folding strategy for the data in the MergeTree engine of the ClickHouse database, and can intelligently and autonomously complete data merging and folding operations, and through 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.

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 querying of massive amounts of data have become important research areas in the information technology field. ClickHouse, a column-based database, has been widely used in large-scale data analysis scenarios due to its high-performance query capabilities and flexible data storage. The MergeTree storage engine is a core component of the ClickHouse system due to its efficient data storage, index building, and query optimization capabilities. However, the current MergeTree engine's merging and optimization mechanisms still have many issues, limiting its performance and resource utilization efficiency in dynamic load and high-concurrency scenarios.

[0003] In the MergeTree engine of ClickHouse (a column-based database), data is stored in a columnar format, optimizing the read performance of large-scale data. However, after data updates, manual data merging and collapsing operations must be triggered to improve query performance and free up storage space. This process not only increases the user's operational burden but can also lead to performance degradation and data inconsistency due to improper timing. Summary of the Invention

[0004] To overcome the above-mentioned deficiencies in the prior art, the present invention patent application proposes an adaptive data merging and optimization method applied to the ClickhouseMergeTree engine, comprising:

[0005] Based on the data in the Clickhouse MergeTree engine, an adaptive data merge trigger mechanism is used to generate data merge optimization tasks;

[0006] Adopting an intelligent data folding strategy to merge, fold and archive the merged and optimized data in the data merging and optimization task;

[0007] A consistency assurance 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 merging and optimization task.

[0008] Preferably, the data in the Clickhouse MergeTree engine is used to generate a data merging optimization task by adopting an adaptive data merging trigger mechanism, including:

[0009] Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time;

[0010] Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request;

[0011] 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;

[0012] The data update and query request includes 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.

[0013] Preferably, after generating the data merging optimization task based on the data in the Clickhouse MergeTree engine and using the adaptive data merging trigger mechanism, the method further includes:

[0014] Real-time monitoring of resource usage in ClickHouse MergeTree engine;

[0015] According to the resource usage, combined with the task type and task data volume of the data merging and optimization task, the execution state of the data merging and optimization task is dynamically adjusted through the task scheduler;

[0016] 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 suspension.

[0017] Preferably, the merged and optimized data includes one or more of the following: merged data, folded data, and archived data; the intelligent data folding strategy is used to merge, fold, and archive the merged and optimized data in the data merging and optimization task, including:

[0018] 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;

[0019] According to the access pattern and access popularity of the data segment, by using pre-set data classification and data priority, determining the merged data, collapsed data and archived data in the data segment;

[0020] 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;

[0021] storing the secondary merged data, the secondary folded data, and the secondary archived data in corresponding storage media according to data-related factors;

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

[0023] Preferably, the access popularity mathematical expression of the data segment is as follows:

[0024]

[0025] in, For the data segment The visit popularity, is the jth data segment, Both are weight coefficients used to balance access frequency and time freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0026] 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 and optimized data after the merge, fold, and archive processing to complete the data merging and optimization task, including:

[0027] Adopting optimistic locking mechanism and data version control mechanism to verify the version number of the merged optimized data;

[0028] 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;

[0029] 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;

[0030] When there is a hash value inconsistency between the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data, a data rollback merge operation is taken to perform data verification until the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0031] Based on the same inventive concept, the present patent application also provides an adaptive data merging and optimization system applied to the Clickhouse MergeTree engine, including: a data merging and optimization task generation module, a data optimization module, and a data verification module;

[0032] 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;

[0033] 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;

[0034] 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.

[0035] Preferably, the data merging optimization task generation module is specifically used to:

[0036] Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time;

[0037] Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request;

[0038] 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;

[0039] The data update and query request includes 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.

[0040] Preferably, the system further comprises: a task dynamic adjustment module, configured to:

[0041] Real-time monitoring of resource usage in ClickHouse MergeTree engine;

[0042] According to the resource usage, combined with the task type and task data volume of the data merging and optimization task, the execution state of the data merging and optimization task is dynamically adjusted through the task scheduler;

[0043] 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 suspension.

[0044] Preferably, the data optimization module is specifically used to:

[0045] 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;

[0046] According to the access pattern and access popularity of the data segment, by using pre-set data classification and data priority, determining the merged data, collapsed data and archived data in the data segment;

[0047] 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;

[0048] storing the secondary merged data, the secondary folded data, and the secondary archived data in corresponding storage media according to data-related factors;

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

[0050] Preferably, the access popularity mathematical expression of the data segment is as follows:

[0051]

[0052] in, For the data segment The visit popularity, is the jth data segment, Both are weight coefficients used to balance access frequency and time freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0053] 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:

[0054] Adopting optimistic locking mechanism and data version control mechanism to verify the version number of the merged optimized data;

[0055] 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;

[0056] 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;

[0057] When there is a hash value inconsistency between the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data, a data rollback merge operation is taken to perform data verification until the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0058] Based on the same inventive concept, the present invention patent application also provides an electronic device, comprising: at least one processor and a memory; the memory and the processor are connected via a bus;

[0059] The memory is used to store one or more programs;

[0060] When the one or more programs are executed by the at least one processor, the adaptive data merging and optimization method applied to the Clickhouse MergeTree engine as described above is implemented.

[0061] 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.

[0062] Compared with the closest prior art, the patent application of this invention has the following beneficial effects:

[0063] 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 adopted to generate a data merging optimization task; an intelligent data folding strategy is adopted to merge, fold and archive the merged optimization data in the data merging optimization task; a consistency guarantee algorithm is adopted to perform data consistency verification on the merged optimization data after the merge, fold and archive processing, and complete the data merging optimization task; the present invention generates a data merging optimization task by updating and querying the data in the MergeTree engine of the ClickHouse database, and in turn, through a dynamic analysis decision-making mechanism and an intelligent data folding strategy, it can intelligently and autonomously complete the data merging and folding operations, and through 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. BRIEF DESCRIPTION OF THE DRAWINGS

[0064] Figure 1 A flowchart of an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine provided in the patent application of this invention;

[0065] Figure 2 A schematic diagram of the adaptive data merging process provided for the patent application of this invention;

[0066] Figure 3 A schematic diagram of an adaptive data merging and optimization system applied to the Clickhouse MergeTree engine provided for the present patent application;

[0067] Figure 4 A schematic structural diagram of the electronic device provided for the patent application of this invention. DETAILED DESCRIPTION

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

[0069] Example 1:

[0070] The present invention patent application provides an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine. Figure 1 Shown, including:

[0071] 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;

[0072] Step 2: Adopting an intelligent data folding strategy to merge, fold, and archive the merged and optimized data in the data merging and optimization task;

[0073] Step 3: Using a consistency assurance algorithm, 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.

[0074] 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:

[0075] Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time;

[0076] For example, a listener is designed to monitor data update operations and query requests in real time, and dynamically adjust the trigger threshold of the merge operation based on this. Specifically, lightweight listening 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.

[0077] Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request;

[0078] 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:

[0079]

[0080] in, is the time window at time t The data update frequency is t, t is the time, is the time window, For the time window The time point of the update operation that occurs within for The size of the data update operation at the time point;

[0081] For example, the dynamic analysis and decision-making mechanism is to analyze the collected data through complex event processing (CEP) technology or machine learning algorithms, identify the changing trends of data update frequency and query patterns, and dynamically adjust the trigger thresholds and strategies for data merging based on these trends to achieve optimal data processing efficiency.

[0082] For example, the mathematical expression for dynamically adjusting the trigger threshold for data merging is as follows:

[0083]

[0084] 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, The maximum load threshold of the system.

[0085] 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;

[0086] 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 the efficient execution of the data merging operation.

[0087] For example, an adaptive algorithm is used to analyze 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, by monitoring the system load and query requests, the appropriate merging time is automatically selected to reduce the impact of the merge on system performance.

[0088] For example, the task scheduling involved in triggering automated merges can be dynamically managed and scheduled using container orchestration tools like Kubernetes. When the decision mechanism determines that a data merge operation is necessary, the system submits a merge task request to the Kubernetes API. Kubernetes then decides whether to start the task immediately or at a later time based on the current cluster resource usage and priority policies. This ensures that even when resources are limited, the system ensures that normal query operations are not affected by the data merge task.

[0089] The data update and query request includes 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.

[0090] The adaptive data merge trigger mechanism of this invention combines real-time monitoring with an adaptive algorithm to trigger data merges, adapting to the dynamic changes in data updates and queries. The real-time monitoring module captures data update operations and query requests within the system. By analyzing the frequency and patterns of these activities, the adaptive algorithm dynamically adjusts the trigger conditions for the merge operation based on the analysis results to optimize system performance and response speed.

[0091] In one implementation, after generating a data merging optimization task based on data in the ClickHouse MergeTree engine using an adaptive data merging trigger mechanism, the process further includes:

[0092] Real-time monitoring of resource usage in ClickHouse MergeTree engine;

[0093] For example, a scheduling algorithm based on priority and resource utilization is designed to dynamically manage data merging and optimization tasks. Specifically, it monitors system resource usage in real time and provides data support for scheduling decisions.

[0094] According to the resource usage, combined with the task type and task data volume of the data merging and optimization task, the execution state of the data merging and optimization task is dynamically adjusted through the task scheduler;

[0095] For example, an intelligent resource-aware task scheduler is developed 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.

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

[0097] For example, the task scheduler automatically adjusts the priority of background tasks based on system load to balance query performance and data merge efficiency. For data merge optimization tasks, if the system's current I / O (Input / Output) throughput is already high, the task scheduler may decide to delay the execution of some non-urgent merge tasks or schedule them for periods of lower system load. Furthermore, the task scheduler can 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.

[0098] 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 suspension.

[0099] For example, another key function of the task scheduler is to manage task execution, which involves operations such as starting, pausing, resuming, and canceling data merging and optimization tasks. The task scheduler should be able to intelligently manage these operations based on the task priority and system resource status. 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 task lifecycle can be directly controlled.

[0100] For example, when a data merging and 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 sufficient free resources. Similarly, if a running data merging and 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.

[0101] 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;

[0102] 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.

[0103] 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.

[0104] Through this intelligent, perceptual task scheduler, the ClickHouse MergeTree engine can more intelligently and efficiently manage data merge and optimization tasks. This not only ensures data processing efficiency and responsiveness, but also ensures the proper utilization of system resources, preventing background tasks from impacting foreground service performance. This highly dynamic and adaptive scheduling strategy is key to achieving efficient data processing and query response. The present invention dynamically starts, pauses, or adjusts execution strategies based on task importance and urgency, mitigating the impact on foreground operations and improving overall system performance. The present invention designs a background task scheduler that intelligently schedules data merge tasks based on the system's current resource usage (CPU, memory, and I / O) to avoid impacting foreground query performance. By introducing an adaptive data merge and optimization algorithm through an adaptive data merge trigger mechanism, the ClickHouse MergeTree engine significantly improves update performance and data consistency. This technological innovation not only reduces the need for manual intervention but also provides an efficient, automated solution for large-scale data processing. The application of this algorithm will significantly enhance the competitiveness and reliability of the ClickHouse database in enterprise-level applications.

[0105] In one implementation, the merged and optimized data includes one or more of the following: merged data, folded data, and archived data; the intelligent data folding strategy used in step 2 to merge, fold, and archive the merged and optimized data in the data merging and optimization task includes:

[0106] 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;

[0107] For example, data pattern analysis can be 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 hotspots. 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.

[0108] According to the access pattern and access popularity of the data segment, by using pre-set data classification and data priority, determining the merged data, collapsed data and archived data in the data segment;

[0109] 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;

[0110] storing the secondary merged data, the secondary folded data, and the secondary archived data in corresponding storage media according to data-related factors;

[0111] 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.

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

[0113] Examples also include 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, a feedback mechanism is established to adjust the prediction model and decision logic based on changes in actual query performance and storage efficiency to achieve continuous optimization.

[0114] 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 uses intelligent classification and priority sorting to determine which data segments should be merged, folded or archived, thereby optimizing the physical storage structure of the data. The main considerations include the frequency (heat) of data access, 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 analyzes the data update pattern and query pattern to intelligently determine which data segments to merge and fold, so as to optimize the data storage structure, reduce storage occupancy and increase query speed.

[0115] In one implementation, the mathematical expression of the access popularity of the data segment is as follows:

[0116]

[0117] in, For the data segment The visit popularity, is the jth data segment, Both are weight coefficients used to balance access frequency and time freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0118] In one implementation, the consistency assurance algorithm includes: an optimistic locking mechanism, a data version control mechanism, and a Merkle hash tree; the consistency assurance 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 to complete the data merging and optimization task, including:

[0119] Adopting optimistic locking mechanism and data version control mechanism to verify the version number of the merged optimized data;

[0120] For example, when implementing the data version control mechanism and 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 by 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.

[0121] 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;

[0122] For example, using a Merkle hash tree for data integrity verification is another key implementation method. A Merkle hash tree is a hash tree where each leaf node contains the hash value of a data block, and non-leaf nodes contain the hashes of their child nodes. By comparing the Merkle tree root hash values ​​of the data shards before and after the merge, data consistency can be efficiently verified.

[0123] 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.

[0124] 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;

[0125] When there is a hash value inconsistency between the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data, a data rollback merge operation is taken to perform data verification until the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0126] 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 assurance 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.

[0127] For example, consider a simple data merging scenario, which contains two data blocks A and B. Before the merge, hash values ​​HA and hash values ​​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 of 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, data snapshots and consistent hashing technology are used to perform data integrity and consistency verification to ensure the accuracy and integrity of data during the merge process;

[0128] The present invention generates data merging optimization tasks through data update and query requests in the MergeTree engine of the ClickHouse database, and sequentially completes data merging and folding operations intelligently and autonomously through dynamic analysis and decision-making mechanisms and intelligent data folding strategies. In addition, the present invention ensures data consistency during data merging and folding operations through consistency assurance algorithms, thereby improving the performance of the MergeTree engine of the ClickHouse database.

[0129] 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. Based on an adaptive algorithm, the method combines data characteristics, access frequency and system load conditions to intelligently select a merging strategy, avoid unnecessary full-volume merging operations, and reduce the impact of the merging process on system performance. The system supports the selection of 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, fully utilizes multi-core processor resources, accelerates the merging process, and is applicable to scenarios requiring efficient data storage and query.

[0130] Example 2:

[0131] Based on the same inventive concept, the present invention patent application also provides an adaptive data merging and optimization system applied to ClickhouseMergeTree engine. Figure 3 As shown, it includes: data merging optimization task generation module, data optimization module and data verification module;

[0132] 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;

[0133] 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;

[0134] 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.

[0135] Preferably, the data merging optimization task generation module is specifically used to:

[0136] Use lightweight monitoring technology to monitor data updates and query requests in the Clickhouse MergeTree engine in real time;

[0137] Adopting a dynamic analysis and decision-making mechanism to obtain dynamic analysis results of the data update and query request;

[0138] 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;

[0139] The data update and query request includes 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.

[0140] Preferably, the system further comprises: a task dynamic adjustment module, configured to:

[0141] Real-time monitoring of resource usage in ClickHouse MergeTree engine;

[0142] According to the resource usage, combined with the task type and task data volume of the data merging and optimization task, the execution state of the data merging and optimization task is dynamically adjusted through the task scheduler;

[0143] 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 suspension.

[0144] Preferably, the data optimization module is specifically used to:

[0145] 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;

[0146] According to the access pattern and access popularity of the data segment, by using pre-set data classification and data priority, determining the merged data, collapsed data and archived data in the data segment;

[0147] 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;

[0148] storing the secondary merged data, the secondary folded data, and the secondary archived data in corresponding storage media according to data-related factors;

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

[0150] Preferably, the access popularity mathematical expression of the data segment is as follows:

[0151]

[0152] in, For the data segment The visit popularity, is the jth data segment, Both are weight coefficients used to balance access frequency and time freshness. for The frequency of visits, is the current time, for The time of the last visit.

[0153] 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:

[0154] Adopting optimistic locking mechanism and data version control mechanism to verify the version number of the merged optimized data;

[0155] 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;

[0156] 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;

[0157] When there is a hash value inconsistency between the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data, a data rollback merge operation is taken to perform data verification until the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

[0158] Example 3

[0159] like Figure 4As shown, the present invention also provides an electronic device, which may be a computer, a single-chip microcomputer, a smart mobile device, or the like. The electronic device in this embodiment may include a processor, a memory, a transceiver component, and the like. The memory, processor, and transceiver component are connected via a bus; the memory may be used to store an execution program, which may include instructions; and the processor may be used to execute the instructions stored in the memory. The memory may also be used to store data, which may be accessed and / or modified during the execution of the instructions.

[0160] 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 gate 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 the storage medium to implement the corresponding method flow or corresponding function, so as to realize the steps of an adaptive data merging and optimization method applied to the Clickhouse MergeTree engine in the above embodiment.

[0161] Example 4

[0162] 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 the electronic device and, of course, extended storage media supported by the electronic device. 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 the processor are also stored in the storage space. 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.

[0163] Those skilled in the art will appreciate that embodiments of the present invention patent application may be provided as methods, systems, or computer program products. Thus, the present invention patent application may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention patent application may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0164] 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 produce 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.

[0165] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work 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 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

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

[0167] 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 and are not intended to limit its scope of protection. Although the patent application of the present invention has been 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 scope of protection 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; Based on the data merging optimization task combined with data update and query requests, data mining and pattern recognition technology are used to identify the access pattern and access popularity of data segments in the Clickhouse MergeTree engine; According to the access pattern and access popularity of the data segment, by using pre-set data classification and data priority, determining the merged data, collapsed data and archived data in the data segment; 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 as merged optimized data after the merge, fold, and archive processing; Using a consistency assurance algorithm, the merged and optimized data after the merge, fold and archive processing is subjected to data consistency verification to complete the data merging and optimization task; Among them, when the number of queries for a data column in the data segment exceeds a preset query number threshold, the storage method and access speed of the data column are optimized; when the number of queries for a data column within a preset time period does not reach a preset minimum query number, the data column is folded and archived to a storage medium with lower cost; the data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

2. The method according to claim 1, wherein 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; The data update and query request includes one or more of the following: the amount of updated data and the complexity of the query; complex event machine processing technology includes complex event processing technology and machine learning algorithm.

3. The method according to claim 1 or 2, wherein: The method uses the adaptive data merging trigger mechanism based on the data in the ClickHouse MergeTree engine to generate the data merging optimization task, and 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 optimization task, the execution state of the data merging and optimization task is dynamically adjusted through the task scheduler; 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 suspension.

4. The method according to claim 1, wherein The mathematical expression of the access popularity of the data segment is as follows: in, is the access popularity of the j-th data segment, 、 Both are weight coefficients used to balance access frequency and time freshness. is the access frequency of the jth data segment, is the current time, is the time when the jth data segment was last accessed.

5. The method according to claim 1, wherein 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 merging and optimization task, including: Adopting optimistic locking mechanism and data version control mechanism to verify 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 a hash value inconsistency between the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data, a data rollback merge operation is taken to perform data verification until the hash value of the merged data and the secondary merged data, the hash value of the folded data and the secondary folded data, and the hash value of the archived data and the secondary archived data are consistent, thereby completing the data merging optimization task.

6. An adaptive data merging and optimization system applied to the Clickhouse MergeTree engine, characterized in that include: Data merging and optimization task generation module, digital segment access identification module, merge and fold back data determination module, data merging and folding back module, data storage 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 digital segment access identification module is used to identify the access pattern and access popularity of data segments in the Clickhouse MergeTree engine based on the data merging optimization task combined with data update and query requests, using data mining and pattern recognition technology; The merge, fold, and archive data determination module is configured to determine the merged data, folded data, and archived data in the data segment based on the access pattern and access popularity of the data segment and through pre-set data classification and data priority; The data merging, folding and archiving module is used to merge the merged data to generate secondary merged data, fold the folded data to generate secondary folded data, and archive the archived data to generate secondary archived data; A data storage module is used to store the secondary merged data, the secondary folded data and the secondary archived data as the merged optimized data after the merge, fold and archive processing in a corresponding storage medium according to data-related factors; 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; Among them, when the number of queries for a data column in the data segment exceeds a preset query number threshold, the storage method and access speed of the data column are optimized; when the number of queries for a data column within a preset time period does not reach a preset minimum query number, the data column is folded and archived to a storage medium with lower cost; the data-related factors include one or more of the following: data access frequency, data timeliness and data relevance.

7. The system according to claim 6, wherein: 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; The data update and query request includes one or more of the following: the amount of updated data and the complexity of the query; complex event machine processing technology includes complex event processing technology and machine learning algorithm.

8. The system according to claim 6 or 7, characterized in that The system further includes a task dynamic adjustment module, configured 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 optimization task, the execution state of the data merging and optimization task is dynamically adjusted through the task scheduler; 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 suspension.

Images