Intermediate result storage and transparent replacement method and device for asynchronous materialized view and electronic equipment

Abstract

The invention relates to an intermediate result storage and transparent replacement method and device for an asynchronous materialized view and electronic equipment, and the method comprises the steps: designing a storage structure of the materialized view, and obtaining an asynchronous incremental materialized view with a prepolymerization storage structure; when new data are inserted into the basic table, the asynchronous materialized incremental view is updated by using increment, and preset specific data are merged in the updating process to obtain a merged view; and rewriting query logic, and transparently rewriting the original query into a materialized view in combination with the asynchronous incremental materialized view. According to the method, through the design of a storage structure of the asynchronous incremental materialized view, a rewriting method for keeping query semantics and optimization of incremental data merging in the asynchronous incremental materialized view, the real-time query performance is effectively improved, and meanwhile the expandability and flexibility of the system are kept.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular, to a method, apparatus, and electronic device for storing intermediate results and transparently replacing an asynchronous materialized view. Background Art

[0002] A materialized view is a view that stores query results. Different from a normal view, a materialized view physically stores data in a database. When querying a materialized view, instead of recalculating data each time, the stored data is directly read, so the query speed is fast. The data of the materialized view needs to be updated regularly to keep it synchronized with the base table. An asynchronous materialized view is a special materialized view, and its update process is asynchronous and does not reflect the latest data of the base table in real time. The refresh operation of data is usually in the form of batch processing or scheduled tasks, rather than being triggered immediately when the base table is updated. When a user queries, the data returned is the data of the last refresh in the materialized view, rather than the latest state of the base table. An asynchronous materialized view can improve query performance and reduce update overhead. However, since the refresh of the asynchronous materialized view is asynchronous, the query result may not be the real-time of the base table, that is, there is a certain gap between the background updated data and the latest data, and the insufficient incremental data processing ability of the asynchronous materialized view will lead to insufficient real-time analysis ability. Summary of the Invention

[0003] In view of this, the present application proposes a method for storing intermediate results and transparently replacing an asynchronous materialized view to solve the problems reflected in the above background art.

[0004] According to an aspect of the present application, a method for storing intermediate results and transparently replacing an asynchronous materialized view is provided, including the following steps:

[0005] Design the storage structure of the materialized view to obtain an asynchronous incremental materialized view with a pre-aggregated storage structure;

[0006] When new data is inserted into the base table, the asynchronous materialized incremental view uses incremental updates and merges preset specific data during the update process to obtain a merged view;

[0007] Rewrite the query logic, and combine the asynchronous incremental materialized view to transparently rewrite the original query as a materialized view.

[0008] As an optional implementation of the present application, optionally, the designed storage structure of the materialized view includes a grouping key column, a data column, and an aggregation result column, and the grouping key column uniquely identifies each group of data, and the aggregation result column stores the calculation result in a partial aggregation manner.

[0009] As an alternative implementation of the present application, optionally, when new data is inserted into the base table, the asynchronous materialized incremental view uses incremental updates and merges preset specific data during the update process, including:

[0010] Based on the preset specific data, organize and merge data items to obtain an aggregated group;

[0011] Store the aggregated group as a data item into the view.

[0012] As an alternative implementation of the present application, optionally, the rewritten query logic includes:

[0013] Merge the incremental data with the data corresponding to the same grouping key in the base table according to the grouping key;

[0014] Obtain a new merged base table.

[0015] As an alternative implementation of the present application, optionally, the process of incremental update includes that when the data in the base table in the database changes, the changed new data is inserted into the asynchronous incremental materialized view as an incremental entry to obtain an updated asynchronous incremental materialized view.

[0016] As an alternative implementation of the present application, optionally, the merging of the incremental data with the data corresponding to the same grouping key in the base table according to the grouping key includes:

[0017] Merge the incremental data and the original data corresponding to each data column in the data structure with the same grouping key;

[0018] Update the aggregated result column by using a partial aggregation method according to the data in the merged data column.

[0019] As an alternative implementation of the present application, optionally, the asynchronous incremental materialized view supports an asynchronous refresh mechanism, including that the asynchronous incremental materialized view refreshes the view regularly according to a set time interval.

[0020] According to another aspect of the present application, there is also provided a device for storing intermediate results and transparently replacing an asynchronous materialized view, including the following modules:

[0021] A designed storage structure module for designing a pre-aggregated storage structure in the asynchronous materialized view;

[0022] An incremental data merging module for merging data entries with the same grouping key in the view;

[0023] A query rewriting and optimization module for rewriting the query logic in the asynchronous materialized view.

[0024] As an alternative implementation of the present application, optionally, the query rewriting optimization module includes:

[0025] A partial aggregation calculation module for merging the incremental partial results and the stored intermediate materialized results according to the query conditions to obtain the final and latest result set;

[0026] A result reading module for reading the results in the stored materialized results.

[0027] According to three aspects of the present application, an electronic device is proposed, including:

[0028] A processor;

[0029] A memory for storing the executable instructions of the processor;

[0030] Wherein, when the processor is configured to execute the executable instructions, it implements the method for storing and transparently replacing the intermediate results of an asynchronous materialized view described in any one of claims 1 to 8.

[0031] Advantages of the present application:

[0032] By introducing partial aggregation and query rewriting, the present invention solves the high overhead problem caused by full refresh and provides an efficient and flexible data warehouse optimization solution. Its core features include: Incremental update support, which can avoid full refresh and significantly reduce the update cost. An efficient storage method, the storage method of intermediate results saves storage resources and makes it possible to rewrite queries and merge incremental partial results. Optimization of dynamic queries, by means of rewriting strategies, merging materialized views and incremental data results to obtain the latest result set. The present invention can effectively improve the performance of real-time queries while maintaining the scalability and flexibility of the system. In a customer environment, using the asynchronous incremental materialized view of the present invention in combination with query rewriting can transparently rewrite the original query into a materialized view and significantly improve the query performance.

[0033] According to the following detailed description of exemplary embodiments with reference to the accompanying drawings, other features and aspects of the present application will become clear. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] The drawings included in the specification and constituting a part of the specification, together with the specification, illustrate the exemplary embodiments, features, and aspects of the present application and are used to explain the principles of the present application.

[0035] Figure 1 A flowchart showing the method for storing and transparently replacing the intermediate results of an asynchronous materialized view according to an embodiment of the present application;

[0036] Figure 2 A block diagram showing the device for storing and transparently replacing the intermediate results of an asynchronous materialized view according to an embodiment of the present application;

[0037] Figure 3 Execution strategy diagram showing the intermediate result storage and transparent replacement of the asynchronous materialized view in the embodiments of the present application; Detailed implementation manners

[0038] Various exemplary embodiments, features, and aspects of the present application will be described in detail below with reference to the accompanying drawings. The same reference numerals in the drawings denote elements having the same or similar functions. Although various aspects of the embodiments are shown in the drawings, the drawings do not have to be drawn to scale unless otherwise specified.

[0039] Among them, it should be understood that the terms "center", "longitudinal", "transverse", "length", "width", "upper", "lower", "front", "rear", "left", "right", "vertical", "horizontal", "top", "bottom", "inner", "outer", "clockwise", "counterclockwise", "axial", "radial", "circumferential", etc. indicate the orientation or positional relationship based on the orientation or positional relationship shown in the drawings, and are only for the convenience of describing the present application or simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and thus should not be construed as a limitation to the present application.

[0040] In addition, the terms "first" and "second" are only used for descriptive purposes and cannot be construed as indicating or implying relative importance or implicitly specifying the quantity of the indicated technical features. Thus, the features defined with "first" and "second" may explicitly or implicitly include one or more of such features. In the description of the present application, "a plurality" means two or more unless otherwise specifically defined.

[0041] The special term "exemplary" here means "serving as an example, embodiment, or illustration". Any embodiment described as "exemplary" here does not have to be construed as superior to or better than other embodiments.

[0042] In addition, for a better description of the present application, numerous specific details are given in the following detailed implementation manners. Those skilled in the art should understand that the present application can also be implemented without some specific details. In some instances, methods, means, elements, and circuits well-known to those skilled in the art are not described in detail so as to highlight the gist of the present application.

[0043] Embodiment 1

[0044] Figure 1 Flowchart showing the method for intermediate result storage and transparent replacement of the asynchronous materialized view according to Embodiment 1 of the present application. As Figure 1 shown, this flowchart includes:

[0045] S100. Design the storage structure of the materialized view to obtain an asynchronous incremental materialized view with a pre-aggregated storage structure.

[0046] The materialized view is a view that stores query results. Different from ordinary views, the materialized view physically stores data in the database. Since the stored data is directly read when querying the materialized view and there is no need to recalculate the data each time, the query speed is faster than that of ordinary views. The asynchronous materialized view is a special type of materialized view, and its update process is asynchronous and does not reflect the latest data of the base table in real time. In this embodiment, it is necessary to design a storage structure for the asynchronous incremental materialized view. The storage structure needs to add a grouping key, data columns, and column data of the aggregation result to form the pre-aggregated storage structure. The asynchronous incremental materialized view with the pre-aggregated storage structure can not only uniquely identify each group of data but also provide support for query rewriting.

[0047] S200. When new data is inserted into the base table, the asynchronous materialized incremental view uses incremental updates and merges preset specific data during the update process to obtain a merged view.

[0048] In this embodiment, when new data is inserted into the base table, the asynchronous materialized incremental view performs incremental updates in the background. The incremental update means that when performing an update operation, only the places that need to be changed are updated, and the places that do not need to be updated or have already been updated will not be updated repeatedly. The new data during the incremental update is inserted into the view as incremental entries. As the number of incremental updates increases, there may be multiple data entries with the same grouping key in the view. The preset specific data is these multiple data entries with the same grouping key. At this time, the preset specific data is merged through a merge operation and organized into one group, which can not only reduce the storage space but also further improve the query efficiency.

[0049] S300. Rewrite the query logic and transparently rewrite the original query as a materialized view in combination with the asynchronous incremental materialized view.

[0050] In this embodiment, query rewriting is one of the core technical advantages of this method. When querying, it is necessary to first capture the changed data of the base table, perform partial aggregation calculations on it, and update the materialized view. After the materialized view is updated, directly read the result after the materialized view is updated to respond to the query. Through the rewriting of the query logic, this invention merges the materialized view and the incremental data result, obtains the latest result set in which the incremental data has been updated to the materialized view. Therefore, the Delta Seq scan operator will not be executed during the query process, and the system can directly read the result from the materialized view without scanning the entire base table, thereby reducing the access to the base table and lowering the computational overhead.

[0051] As an alternative implementation of the present application, optionally, in step S100, the storage structure of the designed materialized view includes grouping key columns, data columns, and aggregate result columns, and the grouping key columns uniquely identify each group of data, and the aggregate result columns store the results of partial aggregation.

[0052] In this implementation, the storage structure of a group in the asynchronous incremental materialized view includes a grouping key and each piece of data, as well as the calculation results stored in a partial aggregation manner. The grouping key is used to uniquely identify the data group, and the aggregate result column is used to store the settlement of performing certain rule calculations on all or part of the data in each piece of data. And the calculation result is not the specific value of the final calculation result, but the intermediate aggregation result of all or part of the data corresponding to the calculation, that is, the aggregate result column stores the data of multiple data columns stored in a preset order, as well as the corresponding calculation formula. What is listed in the base data table is the data of multiple data columns stored in a preset order. The formula for displaying the aggregated data is stored in the background. For example, in the following table, for the grouping column i and the partial sum (SUM), count (COUNT), average value (AVG) (using the sum and count to support the AVG calculation) in the aggregate result column, note here: the average value (AVG) column does not directly store the value of AVG, but stores the intermediate aggregation result of AVG, the result of {Count, SUM}, that is, the original aggregated table, which also provides support for query rewrite.

[0053] i (grouping key) sum count avg 2 20 1 {1,20} 3 30 1 {1,30} 4 90 2 {2,90}

[0054] As an alternative implementation of the present application, optionally, in step S200, when new data is inserted into the base table, the asynchronous materialized incremental view uses incremental updates, and new data groups can be inserted during the update process, and the data groups also correspond to their own grouping keys.

[0055] In this implementation, the update process is the process of inserting new data as incremental entries into the asynchronous incremental materialized view when the base table changes. During the update process, after a large amount of new data is inserted, there will be multiple data entries with the same grouping key. The data entries with the same grouping key are the preset specific data in this implementation. These data entries are merged and sorted together through a merge operation. All the data with one grouping key in the base table are merged, and finally there is only one or one data group with one grouping key in the base table, and the view after the incremental data is merged is obtained. For example, based on the view in the above implementation, after merging two records with a grouping key of 2, the sum of the merged records is updated to 120, the count is updated to 2, and the intermediate result of AVG is also updated to {2, 120}, and the updated data is the entries with the background color in the table.

[0056] i sum count avg 2 120 2 {2,120} 3 30 1 {1,30} 4 90 2 {2,90}

[0057] As an alternative implementation of the present application, optionally, in step S300, the rewritten query logic includes: after merging the incremental data, obtaining a finally merged data group; and obtaining an updated asynchronous incremental materialized view, and the query request directly reads the query result from the updated asynchronous incremental materialized view.

[0058] In this implementation, by rewriting the query logic, the asynchronous incremental materialized view and the incremental data result are merged, enabling the query to obtain the latest result set. The system can significantly reduce the access to the base table, thereby reducing the computational overhead. Without using the query rewrite of the asynchronous incremental materialized view, the query needs to scan the entire base table and summarize all eligible data, resulting in a relatively high computational cost. In the rewritten query logic of this implementation, partial aggregation calculations need to be performed on the incremental data. The final aggregation result of the partial aggregation calculations is obtained by sequentially executing PartialHashAggregate and Finalize HashAggregate. After the final aggregation result is stored in the asynchronous incremental materialized view, the incremental data has been updated to the asynchronous incremental materialized view, that is, the data in the asynchronous incremental materialized view corresponds to the latest data in the database. Therefore, the Delta Seqscan operator in the query logic will not be executed, but the query request can directly read the query result from the asynchronous incremental materialized view, greatly reducing the query time.

[0059] As an alternative implementation of the present application, optionally, the process of incremental update includes that when the data in the base table in the database changes, the changed new data is inserted into the asynchronous incremental materialized view as an incremental entry to obtain an updated asynchronous incremental materialized view.

[0060] In this implementation, the asynchronous incremental materialized view uses incremental update instead of full update. That is, for a group of data corresponding to the same grouping key, the update is performed, and the corresponding calculation results in this group of data can also be updated synchronously in the way of intermediate aggregation. This can significantly reduce the cost of updating the view. The incremental update means that only the data that has changed since the last update is updated, rather than reloading all data irregularly, but specifically synchronizing the changed data. The process of incremental update is that when the data in the base table changes, the changed new data will be inserted into the storage as an incremental entry. For example, in the original view, the data with the grouping key of 2 is recorded, with a partial sum of 20 and a record count of 1. After inserting the new data (2, 100), the incremental entry records the partial sum of 100 and the record count of 1 for the grouping key 2. The newly inserted incremental entry is the last entry with a red background color in the table.

[0061] i sum count avg 2 20 1 {1,20} 3 30 1 {1,30} 4 90 2 {2,90} 2 100 1 {1,100}

[0062] As an alternative implementation of the present application, optionally, the partial aggregation calculation performed on the incremental data includes: combining the incremental partial result and the stored intermediate materialized result to obtain a preliminary combined result.

[0063] In this implementation, the incremental partial result is the new processed data aggregation result after the execution of Partial HashAggregate, and the stored intermediate materialized result is the data aggregation result processed before. The incremental partial result of the Partial HashAggregate and the stored intermediate materialized result are combined into a new data set through the Append operator to obtain the preliminary combined result, and then the Finalize HashAggregate final hash aggregation is uniformly performed after Motion to obtain the final aggregation result.

[0064] As an alternative implementation of the present application, optionally, the asynchronous incremental materialized view supports an asynchronous refresh mechanism, including that the asynchronous incremental materialized view refreshes the view regularly according to a set time interval.

[0065] In this implementation, the asynchronous incremental materialized view adopts an asynchronous refresh mechanism and can perform regular update operations on the view according to a preset time interval (such as updating once every 30 seconds). The asynchronous refresh mechanism is an efficient data processing method. After using the asynchronous refresh mechanism, the update task of the materialized view will not be executed immediately, but will be arranged in a time period with lower system load. This method can significantly reduce the impact on system resources and avoid resource competition during peak periods.

[0066] Embodiment 2

[0067] Based on the same principle as the foregoing method, a device for storing intermediate results and transparently replacing an asynchronous materialized view is also proposed. Refer to Figure 2 , a device 100 for storing intermediate results and transparently replacing an asynchronous materialized view according to an embodiment of the present disclosure includes:

[0068] A designed storage structure module 110, configured to design a pre-aggregation storage structure in the asynchronous materialized view;

[0069] An incremental data merging module 120, configured to merge data entries with the same grouping key in the view;

[0070] A query rewriting and optimization module 130, configured to rewrite the query logic in the asynchronous materialized view;

[0071] As an alternative implementation of the present application, optionally, the query rewrite optimization module includes:

[0072] A partial aggregation calculation module, configured to merge the incremental partial results and the stored intermediate materialized results according to the query conditions to obtain a final and up-to-date result set;

[0073] A result reading module, configured to read the results in the stored materialized results.

[0074] Obviously, those skilled in the art should understand that all or part of the processes of implementing the methods in the above embodiments can be completed by instructing relevant hardware through a computer program. The program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above control methods. Each module or step of the present invention described above can be implemented by a general-purpose computing device. They can be concentrated on a single computing device or distributed on a network composed of multiple computing devices. Optionally, they can be implemented by program codes executable by the computing device. Thus, they can be stored in a storage device and executed by the computing device, or they can be separately fabricated into individual integrated circuit modules, or multiple modules or steps among them can be fabricated into a single integrated circuit module to implement. In this way, the present invention is not limited to any specific combination of hardware and software.

[0075] Those skilled in the art can understand that all or part of the processes of implementing the methods in the above embodiments can be completed by instructing relevant hardware through a computer program. The program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above control methods. Among them, the storage medium can be a magnetic disk, an optical disc, a read-only memory (ROM), a random access memory (RAM), a flash memory, a hard disk drive (abbreviation: HDD), or a solid-state drive (SSD), etc.; the storage medium can also include a combination of the above types of memories.

[0076] Embodiment 3

[0077] Furthermore, an electronic device is proposed, including:

[0078] A processor;

[0079] A memory for storing instructions executable by the processor;

[0080] Wherein, when the processor is configured to execute the executable instructions, it implements the method for storing intermediate results and transparently replacing the asynchronous materialized view described in Embodiment 1.

[0081] The electronic device according to an embodiment of the present disclosure includes a processor and a memory for storing processor-executable instructions. The processor is configured to implement the method for storing intermediate results and transparently replacing an asynchronous materialized view described in any one of the foregoing when executing the executable instructions.

[0082] It should be noted that the number of processors can be one or more. At the same time, in the electronic device according to the embodiment of the present disclosure, an input device and an output device may also be included. Among them, the processor, the memory, the input device, and the output device can be connected through a bus or in other ways, which is not specifically limited herein.

[0083] As a computer-readable storage medium for storing intermediate results and transparently replacing an asynchronous materialized view, the memory can be used to store software programs, computer-executable programs, and various modules, such as: programs or modules corresponding to the method for storing intermediate results and transparently replacing an asynchronous materialized view according to the embodiment of the present disclosure. The processor executes various functional applications and data processing of the electronic device by running the software programs or modules stored in the memory.

[0084] The input device can be used to receive input numbers or signals. Among them, the signal can be a key signal related to user settings and function control of the device / terminal / server. The output device may include a display device such as a display screen.

[0085] The embodiments of the present application have been described above. The above description is exemplary and not exhaustive, and is not limited to the disclosed embodiments. Many modifications and variations are obvious to those of ordinary skill in the art in the technical field without departing from the scope and spirit of the described embodiments. The selection of the terms used herein is intended to best explain the principles of the embodiments, practical applications, or improvements to the technologies in the market, or to enable other ordinary skill in the art in the technical field to understand the embodiments disclosed herein.

Claims

1. A method for storing and transparently replacing intermediate results of asynchronous materialized views, characterized in that: Allowing a user to incrementally update data of a view, the method comprises the following steps: Design the storage structure of the materialized view to obtain an asynchronous incremental materialized view with a pre-aggregation storage structure; When new data is inserted into the basic table, the asynchronous materialized incremental view uses incremental update, and merges the preset specific data during the update process to obtain a merged view; The query logic is rewritten, and combined with the asynchronous incremental materialized view, the original query is transparently rewritten into the materialized view.

2. The method for storing and transparently replacing intermediate results of asynchronous materialized views according to claim 1, characterized in that: The storage structure of the designed materialized view includes a grouping key column, a data column, and an aggregate result column, and the grouping key column uniquely identifies each group of data, and the aggregate result column stores the calculation result in a partially aggregated manner.

3. The method for storing and transparently replacing intermediate results of asynchronous materialized views according to claim 2, characterized in that: When new data is inserted into the base table, the asynchronous materialized incremental view uses incremental updates, including: Set grouping keys for data; The new data is stored in the view as a data item according to the designed storage structure of the materialized view.

4. The method for storing and transparently replacing intermediate results of asynchronous materialized views according to claim 3, characterized in that: Rewritten query logic, including: Merge the data corresponding to the same grouping key in the basic table according to the grouping key for the incremental data; Get the new merged base table.

5. A method for storing intermediate results and transparently replacing asynchronous materialized views as described in claim 3, wherein the incremental update process includes inserting the changed new data as incremental entries into the asynchronous incremental materialized view when the basic table data in the database changes, thereby obtaining an updated asynchronous incremental materialized view.

6. The method for storing and transparently replacing intermediate results of asynchronous materialized views according to claim 4, characterized in that: The step of merging the incremental data corresponding to the same grouping key in the basic table according to the grouping key includes: Merge the incremental data and original data corresponding to each data column in the same grouping key data structure; The aggregated result column is updated by adopting a partial aggregation method according to the merged data column data.

7. The method for storing and transparently replacing intermediate results of asynchronous materialized views according to claim 4, characterized in that: The asynchronous incremental materialized view supports an asynchronous refresh mechanism, including that the asynchronous incremental materialized view periodically refreshes the view according to a set time interval.

8. A device for storing and transparently replacing intermediate results of asynchronous materialized views, comprising: Design storage structure module, used to design asynchronous materialized views with pre-aggregate storage structure; Incremental data merge module, used to merge multiple data entries with the same grouping key in the view; The query rewrite optimization module is used to rewrite the query logic in asynchronous materialized views.

9. The device for storing and transparently replacing intermediate results of asynchronous materialized views according to claim 8, characterized in that: Query rewriting optimization module, including: The partial aggregation calculation module is used to merge the incremental partial results and the stored intermediate materialized results according to the query conditions to obtain the final latest result set; The result reading module is used to read the results in the stored materialized results.

10. An electronic device, characterized in that: include: processor; a memory for storing processor-executable instructions; The processor is configured to implement the method for storing and transparently replacing intermediate results of an asynchronous materialized view as described in any one of claims 1 to 8 when executing the executable instructions.

Images