A time aggregation method for distributed NoSQL database

Abstract

The present invention proposes a distributed NoSQL database time aggregation method, which comprises the following steps: setting a materialized view, and creating a time aggregation table after creating a base table, and making the last clustering key OBSERVED_TIME in the base table STATIONS in the time aggregation table must be of timestamp type, and using it as a time dimension; automatically maintaining the time aggregation table, and in the warehousing process, traversing cache lines after creating a partition object, and after traversing, if it is a partition object, warehousing the partition object and ending the warehousing; if it is not a partition object, determining whether the insertion row time is within the cache line range, and if so, merging the cache line into the partition object, and then re-traversing the cache line; if it is not within the cache line range, directly re-traversing the cache line; after completing the warehousing, when updating or deleting data, traversing the cache line for the insertion row data, and after traversing, if it is data that needs to be updated or deleted, aggregating the data to be updated and deleted into the cache line after querying the base table.

Description

Technical Field

[0001] The present invention belongs to the field of time aggregation, and in particular relates to a distributed NoSQL database time aggregation method. Background Art

[0002] With the advent of the big data era, distributed NoSQL databases have shown great advantages in processing massive data, among which the storage and analysis of time series data is particularly important. However, existing distributed NoSQL databases still face many challenges when processing time aggregation queries. First, traditional time aggregation methods usually require scanning a large amount of data, resulting in poor query performance, especially when processing long-range aggregation queries. Second, existing aggregation methods often find it difficult to effectively utilize the parallel computing capabilities of distributed systems and fail to fully utilize the performance advantages of clusters. Furthermore, when faced with highly concurrent real-time aggregation requests, the system's response time often cannot meet user needs, affecting the real-time nature of data analysis. In addition, existing aggregation methods lack flexibility in processing aggregations of different time granularities, making it difficult to meet users' diverse query needs. At the same time, when processing large-scale data sets, existing methods often have the problem of excessive memory consumption, which limits the scalability of the system.

[0003] Another common problem is that existing aggregation methods perform poorly when dealing with data skew, causing some nodes to be overloaded and affecting overall performance. In addition, existing methods are inefficient when dealing with incremental aggregation and have difficulty responding to data updates quickly. In terms of data consistency, time aggregation in a distributed environment also faces challenges. How to improve aggregation efficiency while ensuring data consistency becomes a difficult problem. Finally, existing aggregation methods often lack effective processing mechanisms for outliers and missing data, affecting the accuracy and reliability of aggregation results. These problems have seriously restricted the application and development of distributed NoSQL databases in the field of time series data analysis. A new time aggregation method is urgently needed to address these challenges, improve query performance, and enhance the scalability and flexibility of the system, so as to better meet the needs of users for real-time data analysis in the big data era. Therefore, a distributed NoSQL database time aggregation method is urgently needed. Summary of the invention

[0004] The present invention proposes a distributed NoSQL database time aggregation method, which solves the efficiency and flexibility problems of distributed NoSQL database in time aggregation. By setting materialized views, automatically maintaining time aggregation tables, optimizing data update and deletion processes, and building time index trees, the performance of time aggregation queries is improved, efficient data management and fast query are achieved, and the system's adaptability to queries at different time scales is enhanced.

[0005] The technical solution of the present invention is implemented as follows: a distributed NoSQL database time aggregation method, the method comprising the following steps:

[0006] Set up a materialized view and create a time aggregation table after creating the base table. The last clustering key OBSERVED_TIME in the base table STATIONS in the time aggregation table must be of timestamp type and used as the time dimension.

[0007] Automatically maintain the time aggregation table. When performing the warehousing process, traverse the cache line after creating the partition object. If it is a partition object after the traversal, the partition object is stored in the warehouse and the warehousing is completed; if it is not a partition object, determine whether the row insertion time is within the cache line range. If so, merge the cache line into the partition object and then traverse the cache line again; if it is not within the cache line range, directly traverse the cache line again;

[0008] After the data is entered into the database, when updating or deleting data, the inserted row data will be traversed through the cache row. After the traversal, if the data needs to be updated or deleted, the data to be updated and deleted will be aggregated into the cache row after querying the base table, and the modified row will be aggregated into the cache row at the same time, and then the time scale will be traversed from bottom to top. After the traversal is completed, if the update or deletion conditions are met, the corresponding operation will be performed. If the conditions are not met, the aggregate table will be queried and re-aggregated into the cache row, and the time scale will continue to be traversed from bottom to top; if the data does not need to be updated or deleted, it will be partially updated or partially deleted when the minimum time scale cache valid number is 0, and then the column will be added to the query column and the column will be traversed again;

[0009] The non-bottom-level data are sorted by day, ten-day period, month, and year to form a time index tree, and the root node is set to the entire time range. The subordinate nodes are year, month, ten-day period, and day, respectively. Time queries are performed according to the time index tree.

[0010] Compared with the existing technology, this solution has significant differences and improvements in many aspects: Traditional NoSQL databases usually do not support or rarely support materialized views, while this solution introduces the concept of materialized views. This allows common aggregation results to be pre-calculated and stored, greatly improving query efficiency. Unlike materialized views in relational databases, the implementation here may be more suitable for the distributed characteristics of NoSQL and can be efficiently maintained and updated in a distributed environment. Existing NoSQL solutions usually require manual maintenance of aggregate data or aggregation calculations at the application level. This new approach sinks the aggregation logic to the database level by automatically maintaining the time aggregation table, reducing the complexity and burden of the application layer. This not only improves efficiency, but also ensures data consistency and real-time performance. Traditional NoSQL databases are often inefficient when handling frequent update and delete operations, especially those involving the time dimension. This new approach specifically optimizes these processes, and may use technologies such as incremental updates and batch processing, so that the real-time and consistency of data are better guaranteed, while also improving the overall performance of the system. Although many NoSQL databases support indexes, index structures specifically for the time dimension are not common. This solution introduces the concept of a time index tree, which may be a special tree structure optimized for time queries. This index structure can significantly improve the query efficiency of various time scales, and can quickly respond to queries from seconds to years. Existing NoSQL solutions often lack flexibility in handling queries at different time scales, and may require different data models or query methods to be designed for different time scales. This new approach enhances the system's adaptability to queries at different time scales through a combination of the above technologies, without the need to design storage or query strategies separately for each time scale. By combining the above technologies, this solution significantly improves the performance of time aggregation queries overall. Compared with traditional methods, it may have significant improvements in multiple indicators such as query latency, throughput, and resource utilization efficiency. This solution simplifies the data management process through automated maintenance and optimization. Compared with the manual optimization and maintenance that may be required in traditional methods, this approach reduces the complexity of operation and maintenance and improves the reliability and stability of the system.

[0011] As a preferred implementation, after the table is created, the average value is not saved in the time aggregation table, and the query is performed using: sum / valid number; non-numeric columns are not supported, and a warning prompt is given when uploading non-numeric column data.

[0012] As a preferred implementation, the stations_ta partition key in the time aggregation table is set to be consistent with the base table STATIONS: the first column of the clustering key in the time aggregation table stations_ta is time_scale, which is used to distinguish different time scales; the other columns are consistent with the base table STATIONS.

[0013] As a preferred implementation, the STATIONS numeric column type is added to the aggregate table stations_ta in the base table, where the tuple members are the valid number, sum, minimum value, minimum value occurrence time, maximum value, and maximum value occurrence time.

[0014] After adopting the above technical solution, the beneficial effect of the present invention is that the beneficial effect brought by this solution is to significantly improve the performance and flexibility of the distributed NoSQL database when processing time aggregation queries. By setting materialized views, the system can pre-calculate and store commonly used aggregation results, greatly reducing the computational overhead during querying, thereby significantly reducing the query response time. The mechanism of automatically maintaining the time aggregation table ensures the real-time and accuracy of the aggregated data, while reducing the burden on the application layer, so that developers can focus on business logic rather than underlying data processing. The optimized data update and deletion process improves the efficiency of the system when processing frequently changing data and ensures data consistency. The construction of the time index tree greatly accelerates query operations at various time scales, and queries at both the second level and the grade level can respond quickly. These improvements work together to not only improve query performance, but also enhance the scalability and adaptability of the system, so that it can better cope with the analysis needs of time series data of different scales and types. In addition, this solution simplifies the data management process, reduces the complexity of operation and maintenance, and improves the reliability and stability of the system. In general, this innovative method provides enterprises and organizations with an efficient, flexible and easy-to-manage time series data processing solution that can support more complex data analysis and decision support applications, creating greater data value for users. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0016] Figure 1 is a flow chart of the method of the present invention;

[0017] Figure 2 It is the warehousing flow chart of the present invention;

[0018] Figure 3 Insert a flow chart for the present invention;

[0019] Figure 4 Update or delete flowcharts for the present invention;

[0020] Figure 5 This is the time index tree flow chart of the present invention. DETAILED DESCRIPTION

[0021] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0022] Example:

[0023] like Figures 1 to 5 As shown, a distributed NoSQL database time aggregation method, the method comprises the following steps:

[0024] Set up a materialized view and create a time aggregation table after creating the base table. The last clustering key OBSERVED_TIME in the base table STATIONS in the time aggregation table must be of timestamp type and used as the time dimension.

[0025] Automatically maintain the time aggregation table. When performing the warehousing process, traverse the cache line after creating the partition object. If it is a partition object after the traversal, the partition object is stored in the warehouse and the warehousing is completed; if it is not a partition object, determine whether the row insertion time is within the cache line range. If so, merge the cache line into the partition object and then traverse the cache line again; if it is not within the cache line range, directly traverse the cache line again;

[0026] After the data is entered into the database, when updating or deleting data, the inserted row data will be traversed through the cache row. After the traversal, if the data needs to be updated or deleted, the data to be updated and deleted will be aggregated into the cache row after querying the base table, and the modified row will be aggregated into the cache row at the same time, and then the time scale will be traversed from bottom to top. After the traversal is completed, if the update or deletion conditions are met, the corresponding operation will be performed. If the conditions are not met, the aggregate table will be queried and re-aggregated into the cache row, and the time scale will continue to be traversed from bottom to top; if the data does not need to be updated or deleted, it will be partially updated or partially deleted when the minimum time scale cache valid number is 0, and then the column will be added to the query column and the column will be traversed again;

[0027] The non-bottom-level data are sorted by day, ten-day period, month, and year to form a time index tree, and the root node is set to the entire time range. The subordinate nodes are year, month, ten-day period, and day, respectively. Time queries are performed according to the time index tree.

[0028] The working principle and process of this distributed NoSQL database time aggregation method are as follows: First, by setting up materialized views and creating time aggregation tables, the foundation for time aggregation queries is laid. After the base table is created, the system will create a time aggregation table, in which the last clustering key OBSERVED_TIME of the STATIONS table is set to the timestamp type as the time dimension. This design allows the system to efficiently perform time-related aggregation operations. Next, the system implements a mechanism for automatically maintaining the time aggregation table. During the data warehousing process, the system will create partition objects and traverse the cache rows. If it is a partition object after the traversal, it will be directly stored in the warehouse; if it is not a partition object, the system will determine whether the time of the inserted row is within the cache row range. If it is within the range, the cache row will be merged into the partition object and the cache row will be traversed again; if it is not within the range, the cache row will be traversed again directly. This process ensures that the newly inserted data can be correctly integrated into the existing aggregation structure.

[0029] When updating or deleting data, the system uses a complex processing flow. First, the cache line is traversed. If the data needs to be updated or deleted, the system will query the base table, aggregate the data to be updated and deleted to the cache line, and modify the row aggregation. Then, the time scale is traversed from bottom to top, and the update or delete operation is performed according to the conditions. If the conditions are not met, the aggregate table is queried and re-aggregated to the cache line, and the time scale is continued to be traversed. For data that does not need to be updated or deleted, the system will add the column to the query column and re-traverse when the minimum time scale cache valid number is 0 or partially updated / deleted. This method ensures the consistency and accuracy of the aggregated data and remains efficient even in frequent update and delete operations. Finally, the system builds a time index tree that arranges non-bottom-level data in the order of day, ten-day, month, and year. The root node represents the entire time range, and the subordinate nodes are year, month, ten-day, and day in order. This structure greatly optimizes the efficiency of time queries and allows the system to quickly locate and retrieve data within a specific time range. Overall, this method achieves efficient time aggregation query and data management through carefully designed data structures and processing flows. It significantly improves query performance and system flexibility while ensuring data consistency and real-time performance. Compared with traditional methods, this solution shows higher efficiency and adaptability when processing large-scale time series data, and is particularly suitable for application scenarios that require frequent time dimension analysis.

[0030] After the table is created, the average value is not saved in the time aggregation table, and the query is performed using the sum / valid number. Non-numeric columns are not supported, and a warning prompt is given when uploading non-numeric column data. Traditional time aggregation methods usually pre-calculate and store the average value in the aggregation table, while this new method chooses not to save the average value in the time aggregation table, but to dynamically calculate it using the "sum / valid number" method when querying. The main differences and effects of this design are as follows: First, not pre-storing the average value can significantly reduce the use of storage space, especially when processing large-scale time series data, this savings can bring considerable cost benefits. Second, dynamically calculating the average value improves the flexibility and real-time nature of the data. In traditional methods, the pre-calculated average value may become outdated due to data updates, and the aggregation table needs to be updated frequently. Dynamic calculation ensures that each query is calculated based on the latest data, providing more accurate results.

[0031] In addition, this approach can better handle outliers or missing values ​​in the data through the "and / valid number" calculation method. For example, when calculating the average temperature, if the temperature data at some time points is missing, dynamic calculation can easily exclude these data points, while the pre-calculated average may require complex update logic to handle this situation. Not supporting non-numeric columns is another important design decision. This restriction simplifies aggregation logic and improves query efficiency. Numerical data is easier to perform mathematical operations and statistical analysis, while aggregation of non-numeric data (such as text or category data) usually requires more complex processing logic. By focusing on numerical data, the system can optimize performance and simplify implementation. The warning prompt when uploading non-numeric column data helps maintain data quality and consistency. It can promptly remind users or system administrators of potential data type errors and prevent data that is not suitable for aggregation from entering the system, thereby avoiding possible errors in subsequent queries and analysis. This design also increases the system's fault tolerance and user-friendliness. In general, this approach has significant advantages in improving system efficiency, flexibility, and data quality through dynamic calculation, focusing on numerical data, and timely error prompts. It is particularly suitable for large-scale time series data application scenarios that require frequent updates and real-time analysis, such as meteorological data analysis, financial market monitoring, or industrial sensor data processing.

[0032] Set the partition key of stations_ta in the time aggregation table to be consistent with the base table STATIONS: the first column of the clustering key in the time aggregation table stations_ta is time_scale, which is used to distinguish different time scales; the other columns are consistent with the base table STATIONS. . Traditional time aggregation table design usually creates independent table structures for different time scales, or uses complex column combinations in the same table to represent data of different time scales. In contrast, this new approach achieves more efficient and flexible time aggregation management by introducing time_scale as the first clustering key in the time aggregation table stations_ta while keeping other columns consistent with the base table STATIONS. The main advantages of this design are: First, it greatly simplifies the data model and reduces data redundancy. By using time_scale in the same table to distinguish different time scales, the need to create a separate table for each time scale is avoided, thereby reducing storage overhead and management complexity. Second, this design improves query efficiency. Since the aggregated data of all time scales are stored in the same table and are consistent with the base table structure, the system can more easily perform cross-time scale queries and analysis without complex table join operations.

[0033] In addition, maintaining a consistent structure with the base table STATIONS (except for the time_scale column) makes the mapping of data between the base table and the aggregate table intuitive and simple, which is conducive to data maintenance and update. This consistency also simplifies application development because developers can use similar query patterns to access data in the base table and the aggregate table. Another important advantage is the enhanced scalability of the system. As new time scale requirements emerge, only new values ​​need to be added to the time_scale column without modifying the table structure or creating a new table, which greatly improves the flexibility and adaptability of the system. This design is also conducive to data consistency maintenance. When the base table data changes, it becomes relatively simple to update the aggregate table because the structures of the two tables are basically the same and similar update logic can be used. In general, this design approach improves query efficiency, system flexibility and maintainability while keeping the data model concise through clever table structure design. It is particularly suitable for application scenarios that require frequent multi-scale time aggregation analysis, such as meteorological data analysis, financial market monitoring, or IoT data processing. This approach not only simplifies the system architecture, but also provides a solid foundation for efficient data analysis and decision support.

[0034] In the base table, add the STATIONS numeric column type to the aggregate table stations_ta, where the tuple members are valid number, sum, minimum value, minimum value occurrence time, maximum value, and maximum value occurrence time. This design integrates multiple statistical indicators of the STATIONS numeric column into one tuple, including valid number, sum, minimum value, minimum value occurrence time, maximum value, and maximum value occurrence time. This method is more compact and efficient than the traditional method of storing each statistical indicator separately. Secondly, these statistical indicators are added to the aggregate table stations_ta as a whole, which is easy to manage and query. This design can greatly reduce the number of tables and fields in the database and simplify the database structure. Thirdly, the occurrence time of the minimum and maximum values ​​is included, which is information ignored by many traditional statistical methods, but is very valuable for data analysis and anomaly detection. Finally, this design makes data aggregation and query more flexible and efficient. Users can quickly obtain the required statistical information as needed without complex calculations or multi-table joint queries.

[0035] The main functions of this design include: improving data storage and query efficiency, reducing storage space usage; simplifying the database structure for easy management and maintenance; providing more comprehensive statistical information, including the time of extreme value occurrence, which is conducive to in-depth data analysis; supporting more flexible data aggregation and query operations to meet different analysis needs; facilitating real-time statistics and updates, and can quickly reflect the changing trends of data. In general, this design method has significant advantages in data storage efficiency, query performance, statistical comprehensiveness, and analysis flexibility, and is a more advanced and practical data management solution.

[0036] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A distributed NoSQL database time aggregation method, characterized in that: The method comprises the following steps: Set up a materialized view and create a time aggregation table after creating the base table. The last clustering key OBSERVED_TIME in the base table STATIONS in the time aggregation table must be of timestamp type and used as the time dimension. Automatically maintain the time aggregation table. When the warehousing process is in progress, traverse the cache line after creating the partition object. If it is a partition object after the traversal, store the partition object in the warehouse and then end the warehousing. If it is not a partition object, determine whether the row insertion time is within the cache line range. If so, merge the cache line into the partition object and then traverse the cache line again; If it is not in the cache line range, directly re-traverse the cache line; After the data is stored in the database, when updating or deleting data, the inserted row data will be traversed through the cache row. After the traversal, if the data needs to be updated or deleted, the updated and deleted data will be aggregated into the cache row after querying the base table, and the modified row will be aggregated into the cache row at the same time. Then the time scale will be traversed from bottom to top. After the traversal is completed, if the update or deletion conditions are met, the corresponding operation will be performed. If the conditions are not met, the aggregate table will be queried and re-aggregated into the cache row, and the time scale will continue to be traversed from bottom to top. If the data does not need to be updated or deleted, it is partially updated or partially deleted when the minimum time scale cache valid number is 0, and then the column is added to the query column and the column is traversed again; The non-bottom-level data are sorted by day, ten-day period, month, and year to form a time index tree, and the root node is set to the entire time range. The subordinate nodes are year, month, ten-day period, and day, respectively. Time queries are performed according to the time index tree.

2. A distributed NoSQL database time aggregation method as claimed in claim 1, characterized in that: After the table is created, the average value is not saved in the time aggregation table, and the query uses: sum / valid number. Non-numeric columns are not supported, and a warning prompt is given when uploading non-numeric column data.

3. A distributed NoSQL database time aggregation method as claimed in claim 1, characterized in that: Set the partition key of stations_ta in the time aggregation table to be consistent with the base table STATIONS: the first column of the clustering key of the time aggregation table stations_ta is time_scale, which is used to distinguish different time scales; the other columns are consistent with the base table STATIONS.

4. A distributed NoSQL database time aggregation method as claimed in claim 1, characterized in that: In the base table, add the STATIONS numeric column type to the aggregate table stations_ta, where the tuple members are the valid number, sum, minimum value, minimum value occurrence time, maximum value, and maximum value occurrence time.

Images