A general incremental calculation method based on intermediate states

Abstract

The present invention discloses a general incremental calculation method based on intermediate state, which rewrites a logical execution plan to obtain its corresponding incremental execution plan, including the following steps: S1: obtaining user SQL and parsing it to generate a logical execution plan; S2: screening and judging operators to select target operators for persistent processing; S3: persistently storing the target operators for persistent processing; S4: using an algorithm to match the state of the logical execution plan in the intermediate state with the current execution plan, so that all operators find corresponding equivalent operators; S5: reading the previous aggregation result of the incremental execution plan, and merging it with the incremental aggregation result of the current incremental data calculation to obtain the final execution plan; S6: real-time monitoring, updating and maintenance of the final execution plan.

Description

Technical Field

[0001] The present invention relates to the field of big data technology, and in particular to a general incremental calculation method based on intermediate states. Background Art

[0002] General Incremental Computing: In big data systems, incremental computing technology is an efficient method for processing data changes. It only computes new or modified data, rather than processing the entire data set every time. This significantly improves processing efficiency and reduces resource consumption. For example, in e-commerce systems, a large amount of new order data is generated daily. Using incremental computing, only the newly generated orders are processed, eliminating the need to recalculate all past order data. General incremental computing technology is designed for general scenarios and unifies the current stream, batch, and interactive modes through a set of incremental computing logic. This differs from the incremental computing of stream computing.

[0003] SQL queries: SQL queries are a crucial way for users to interact with big data systems. They allow users to extract the required data from one or more tables based on specific conditions and requirements. By writing SQL queries, users can specify the columns to be retrieved, filter conditions, sorting rules, and data grouping methods.

[0004] SQL query optimizer: The optimizer is a crucial component in big data systems. It is responsible for generating efficient execution plans for queries, thereby significantly improving query performance.

[0005] Execution plan: An execution plan describes the series of steps and sequence taken by the data system to complete a specific SQL query. It is the optimal execution plan generated by the optimizer based on the query statement, metadata (such as table structure and index information), and statistical information (such as data distribution and number of rows). It will be assigned to a real physical machine for execution and ultimately obtain the calculation result.

[0006] Intermediate state: The intermediate state is a temporary result set in the incremental calculation process. It needs to be persisted on the storage medium. Each incremental task can rely on the intermediate state of the previous incremental refresh to continue the incremental update, thereby accelerating the execution efficiency of some tasks.

[0007] Scan operator: The Scan operator is a basic operator in the query execution plan. It is mainly used to read data from physical storage (such as disk and memory) and provide raw data input for subsequent data processing steps.

[0008] Aggregate operator: An aggregate operator is a key component in query processing and big data processing frameworks for summarizing and performing statistical operations on data. Its main function is to aggregate a set of data according to specific rules, converting multiple input rows into one or more output rows.

[0009] Window operator: The window operator aggregates and sorts data by partition in the big data framework, and then performs corresponding calculations on key components.

[0010] Join operator: Join operator is an important operation in query and data processing framework for merging data from two or more data sources.

[0011] Sink operator: The sink operator is a key component in the data processing process. It plays an important role in both stream processing and batch processing systems. It is mainly responsible for outputting processed data to external storage systems or other target locations. It is the "exit" of the data processing pipeline.

[0012] In the prior art, generating a general incremental calculation execution plan can be divided into the following steps: Figure 2 As shown:

[0013] 1. Query parsing

[0014] The big data system first performs lexical analysis on the input SQL query statement, breaking it down into individual lexical units (tokens), such as keywords (such as SELECT, FROM, WHERE), identifiers (table names, column names), operators (such as +, -, *, / ), and constants (numbers, strings). Based on the lexical units obtained from the lexical analysis, a syntax tree (SyntaxTree) is constructed according to the grammatical rules of the SQL language. The syntax tree is a tree-like structure that reflects the grammatical structure and logical relationships of the query statement. Semantic checks are performed on the syntax tree to ensure that the query statement is semantically correct. This includes checking the existence of table and column names, whether data types match, and whether permissions are sufficient.

[0015] 2. Generate a logical execution plan

[0016] Convert the validated AST into a logical query plan. A logical query plan is a representation based on relational algebra that describes the logical steps of a query without involving specific physical implementation details. Common logical operations include selection, projection, join, and aggregation.

[0017] 3. Query Optimization

[0018] The query optimizer performs a series of equivalent transformations on the logical execution plan, converting it into a form that is easier to optimize and execute. For example, it can convert subqueries into joins or simplify complex expressions. The system uses two types of optimizers to optimize execution plans: the Rule-based Optimizer (RBO) and the Cost-based Optimizer (CBO).

[0019] RBO first rewrites the execution plan. This process is based on a series of optimization rules that perform equivalent transformations on the logical plan, resulting in a more optimal one. Common logical optimizations include predicate pushdown, projection pruning, and join order adjustment. For example, predicate pushdown rules execute filtering conditions as early as possible at the data source, reducing the amount of data required for subsequent operations.

[0020] After completing basic optimization operations, the CBO begins converting the optimized logical plan into a physical query plan. The physical query plan describes the specific execution method of the query, including the algorithm used, data access method, and parallel execution strategy. During this phase, the optimizer considers factors such as data source characteristics and system resources to select appropriate physical operators to implement the logical operations. For example, for join operations, it selects an appropriate join algorithm (such as nested loop join or hash join).

[0021] At the same time, the CBO stage will also attempt to incrementally rewrite the execution plan. For example, it will replace the data source (Scan) with the incremental reading mode, and gradually replace each operator with the incremental calculation method (different operators have different incremental algorithms), finally completing the generation of the incremental execution plan. Figure 3 The figure shows a specific example of incremental execution plan rewriting. The Scan operator only needs to be simply replaced with a mode for reading incremental data. The subsequent Filter operator naturally has the ability to consume any streaming data without any rewriting. The final Sink operator needs to be converted from a mode for fully covering data to a mode for appending incremental data. This completes a simple incremental task rewriting.

[0022] 4. Execution Plan

[0023] The final physical query plan is handed over to the specific execution engine for execution. According to the description of the physical plan, the execution engine reads data from the data source, processes it according to the specified operation steps, and returns the query results.

[0024] As can be seen, the generation of an incremental execution plan is completed within the CBO engine. During this complex process, different operators, based on their unique algorithmic logic, each generate a corresponding sub-execution plan, aiming to accurately output the incremental data corresponding to that operator. This process appears orderly, but in reality, it harbors hidden efficiency risks. A significant shortcoming of conventional incremental computing engines is their lack of the key concept of state storage. State storage acts as a data "memory," recording intermediate results and status information during the computation process, providing a fast way to retrieve historical results for subsequent computations.

[0025] refer to Figure 4 The figure shows the incremental rewrite algorithm for the Scan and Aggregate operators: Scan simply needs to change to a mode that reads incremental data; however, Aggregate needs to scan historical data, recalculate the previous results, read the incremental data and merge it with the historical data, calculate the current full result, and then offset the two to obtain the current incremental result. This algorithm is very inefficient, equivalent to calculating a large amount of data twice, which not only wastes computing resources but also increases computing time. The core goal of incremental computing is to consume only incremental data and obtain the latest calculation results at the lowest cost. However, existing algorithms clearly do not achieve this goal, which prevents the full advantages of incremental computing from being fully utilized.

[0026] Given this, a new computing framework is urgently needed to improve the overall performance of incremental computing. This new framework should fully consider the importance of state storage and avoid unnecessary repeated calculations by properly recording and utilizing intermediate states. Summary of the Invention

[0027] The purpose of the present invention is to provide a general incremental calculation method based on intermediate states to overcome the deficiencies in the prior art.

[0028] To achieve the above object, the present invention provides the following technical solutions:

[0029] This application discloses a general incremental calculation method based on intermediate states, which rewrites a logical execution plan to obtain its corresponding incremental execution plan, including the following steps:

[0030] S1: Obtain user SQL and parse it to generate a logical execution plan;

[0031] S2: Filter and judge the operators and select the target operator for persistence processing;

[0032] S3: Persistently stores the target operator for persistent processing;

[0033] S4: The algorithm matches the intermediate logical execution plan with the current execution plan, so that all operators can find their corresponding equivalent operators.

[0034] S5: Read the previous aggregation result of the incremental execution plan and merge it with the incremental aggregation result calculated for the current incremental data to obtain the final execution plan;

[0035] S6: Monitor, update, and maintain the final execution plan in real time.

[0036] The S2 includes the following sub-steps:

[0037] S21: Determine whether the operator is stateful. Aggregation operators, window operators, and join operators are stateful operators.

[0038] S22: Determine whether the state of the operator is itself, and if not, rewrite it;

[0039] S23: If the state required by the current operator is its input node, select the position most worthy of being saved.

[0040] The S22 includes the following content: the aggregation operator is judged according to the type of the aggregation function: if it is a SUM or COUNT type, it itself is the state; if it is an AVG, it is rewritten as a SUM or COUNT type; if it is a MIN or MAX type, then when its input data is append-only, its output result is regarded as the state, otherwise its input data is the state node.

[0041] The S22 includes the following contents:

[0042] S221: Determine the type of the operator's input data, including append-only, delete-only, or both.

[0043] S222: Determine whether the intermediate state has explicitly appeared in the initial logical execution plan. If not, rewrite it to make it a state that is valuable to be saved.

[0044] The S3 includes the following contents: for the target operator of persistence processing, after selecting the creation location of the intermediate state, an additional sink is attached to the current execution plan, the content of the current node is persistently stored in the storage medium, and relevant information is recorded to track the several intermediate states created.

[0045] S4 includes the following: matching the two logical execution plans using an intermediate state matching algorithm, matching the intermediate state logical execution plan with the operators of the logical execution plan generated by the query sequentially from bottom to top until the root node, and replacing the logical execution plan of the entire view with a scan operation on the view after the matching is completed;

[0046] At this time, a tree consisting of several compensation operators followed by a scan operator is generated. This tree is equivalent to a node in the logical execution plan generated by the query.

[0047] The matching of the two logical execution plans in S4 includes the following: the matching results include: no match, complete match, and partial match; in the case of partial match, a compensation operator is calculated and generated, and the compensation operator is pulled up until it passes through a subsequent series of nodes that need to be matched; if the compensation operator cannot be calculated or cannot be pulled up, the view matching fails.

[0048] The S6 includes the following contents:

[0049] S61: Monitor the update of intermediate status in real time to ensure data consistency;

[0050] S62: Automatically reclaim the state of multiple failed matches to release the occupied storage space;

[0051] S63: When SQL changes, adaptively update the state storage.

[0052] The present application also discloses a general incremental computing device based on intermediate states, comprising a memory and one or more processors, wherein the memory stores executable code, and when the one or more processors execute the executable code, they are used to implement the above-mentioned general incremental computing method based on intermediate states.

[0053] The present application also discloses a computer-readable storage medium on which a program is stored. When the program is executed by a processor, the above-mentioned general incremental calculation method based on intermediate states is implemented.

[0054] Beneficial effects of the present invention:

[0055] (1) This solution introduces the concept of state storage to the general incremental computing framework, caching complex computation result sets in a persistent storage medium, avoiding repeated computation of the same historical data and significantly reducing the time and resources required for computation. This allows faster results when processing large-scale data, providing more timely and accurate support for the decision-making of enterprises and organizations. It demonstrates outstanding advantages in improving incremental computing execution performance and is expected to play an important role in the field of big data processing and promote the development and progress of the industry.

[0056] (2) This solution fully proposes the specific maintenance behaviors of each life cycle link from creation to deletion of the intermediate state, so that it can play a role adaptively in complex systems and massive query operations, significantly reducing the workload of data developers and data practitioners, allowing them to focus more on the development of business systems rather than tuning the massive details of existing links one by one, greatly liberating the productivity of enterprises and individuals.

[0057] The features and advantages of the present invention will be described in detail through embodiments with reference to the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] Figure 1 It is a flowchart of the steps of a general incremental calculation method based on intermediate states of the present invention;

[0059] Figure 2 It is a schematic diagram of the existing generation of a general incremental calculation execution plan of the present invention;

[0060] Figure 3 It is a schematic diagram of the rewriting of the existing incremental execution plan of the present invention;

[0061] Figure 4 It is the existing incremental rewriting algorithm for Scan operator and Aggregate operator of the present invention;

[0062] Figure 5 It is a schematic diagram of the rewriting of the logic execution plan of the present invention;

[0063] Figure 6 It is a schematic diagram of the present invention for matching the state of the logical execution plan in the intermediate state with the current execution plan;

[0064] Figure 7 It is a schematic diagram of the final execution plan of the present invention;

[0065] Figure 8 It is a schematic diagram of the device of the present invention;

[0066] Figure 9 It is a schematic diagram of the judgment process of the state operator of the present invention. DETAILED DESCRIPTION

[0067] To make the objectives, technical solutions, and advantages of the present invention more clearly understood, the present invention is further described in detail below with reference to the accompanying drawings and examples. However, it should be understood that the specific embodiments described herein are merely illustrative of the present invention and are not intended to limit the scope of the present invention. In addition, in the following description, descriptions of well-known structures and technologies are omitted to avoid unnecessary confusion of the present invention.

[0068] See Figure 1The embodiment of the present invention provides a general incremental computing framework based on intermediate states, which greatly improves the execution performance of incremental computing by caching a complex calculation result set on persistent storage. The main effect is reflected in: With this efficient caching mechanism, this framework can introduce many advanced and efficient incremental computing algorithms. It enables it to use many efficient incremental computing algorithms to complete the query calculation. By reloading the state of the previous calculation cache in each incremental computing task, the huge overhead caused by repeated calculations can be avoided, thereby greatly improving the execution efficiency of incremental computing.

[0069] At the same time, this framework needs to systematically solve various problems related to intermediate states: the first is the problem of selecting intermediate states. In the face of massive data and complex computing processes, how to accurately select those intermediate states that really have a key impact on the calculation results is an extremely challenging task.

[0070] Secondly, achieving complete matching of intermediate states is also a key step. During the computation process, intermediate states may change dynamically as data changes and computation progresses. It is necessary to ensure that the corresponding intermediate state can be accurately found for each query.

[0071] Furthermore, the updating of intermediate states cannot be ignored. As new data continues to flow in and calculations continue, intermediate states need to be updated in a timely manner to ensure that they are consistent with the latest data and calculation results.

[0072] Finally, maintaining the validity of intermediate states is the cornerstone of the stable operation of the entire framework. In practical applications, intermediate states may be affected by various factors, such as data errors and system failures, which may threaten their validity. A comprehensive monitoring and maintenance mechanism is necessary to monitor the validity of intermediate states in real time and enable timely repairs and adjustments if any issues are discovered.

[0073] At the same time, the intermediate state needs to be cleaned and optimized regularly to remove useless or expired information to improve the operating efficiency and performance of the entire framework.

[0074] Includes the following:

[0075] 1. Determine the location of the intermediate state

[0076] First of all, it is important to understand that not all operators require intermediate states, and having intermediate states does not necessarily make calculations faster. Therefore, it is necessary to determine which operator results are worth persisting and ensure that these results can be used by future calculations to achieve the purpose of acceleration. Figure 3 and Figure 4 The incremental execution plans of two different operators are given. Figure 3 The calculations shown are primarily the result of pass-through input data, so there is no need to look back at any historical data; however Figure 4 It is an aggregation operation that requires historical data to be calculated together with the newly added input data.

[0077] Figure 3 Some of the sample code shown includes:

[0078] INSERT OVERWRITE TABLE res

[0079] SELECT* FROM T WHERE date>'0101'

[0080] Therefore, we first need to distinguish between stateful and stateless operators. A stateful operator requires that any computation rely on historical results and cannot simply rely on incremental data to complete the current computation. Conversely, a stateless operator is self-sufficient and does not require any historical results. Stateful operators primarily fall into three categories: aggregate, window, and join. The intermediate state that needs to be generated also primarily relies on these three types of operators.

[0081] Secondly, whether an operator can use intermediate state is strongly dependent on the input data type (whether the input data is append-only, delete-only, or both). Different properties necessitate distinct incremental algorithms. For example, for the aggregate function MAX, if the input data contains a delete operation, and the result of the delete operation happens to be the value of MAX itself, then the new result cannot be calculated using incremental data.

[0082] Another factor that influences intermediate states is whether they are explicitly included in the original logical execution plan. Typically, some operators require a certain degree of equivalent rewriting before they become states worth preserving. For example, the common aggregate function AVG needs to be rewritten as SUM and COUNT. Saving AVG directly will not work, so an additional step is required to rewrite AVG to calculate SUM and COUNT, and then use a scalar SUM / COUNT calculation to obtain the original AVG result.

[0083] In summary, the following steps for screening intermediate states can be derived:

[0084] Whether the current operator is a stateful computation (Aggregate / Window / Join are considered stateful computations)

[0085] in the case of":

[0086] a. Whether the state of the current operator is itself (for example, Aggregate needs to be determined based on the type of aggregation function; SUM / COUNT itself is the state; AVG needs to be rewritten; MIN / MAX can only be considered as the state when the input data is append-only; otherwise, its input data is the state node)

[0087] b. If the state required by the current operator is its input node, it is necessary to find the location that is most worth saving. When determining whether a node is the most valuable location to save, you can refer to the following indicators:

[0088] 1. The number of data rows in the current node. The number of data rows is the most direct evaluation metric for the storage space occupied by the intermediate state. In big data systems, the number of rows in the input data can vary depending on the type of calculation. For example, a filtering operation typically results in fewer rows, while a multi-table join operation results in more data. Storing a node with a relatively small amount of data can generate benefits in state storage and reuse.

[0089] 2. The computational complexity of the current node. When storing the input node of the current operator, go down along the current logical execution plan. Each time you go down a layer of operators, it means that the operators you go through need to be recalculated once when the state is reused. For example, if the given execution plan is A<-B<-C<-..., if you directly save the B node for the A node, then the next time you calculate A, you can directly read the state of the B node to save repeated calculations. At this time, C can be regarded as a state that can be saved, but for A, each time after reading the state of C, it is necessary to re-execute the calculation of the B node. In summary, a balance can be found between the number of data rows and the computational complexity of each node. That is, when the cost of reading the low-level operator data plus the cost of recalculating the high-level nodes that have not been saved is less than the cost of directly reading the high-level node data, a more suitable storage location has been found;

[0090] 3. Does the current node contain columns that can be used for pruning operations? During the calculation of incremental tasks, a lot of data does not need to be read again. For example, for aggregation operations, all affected results in a single incremental calculation are selected by the incremental data's aggregation key (Group Key), and the remaining data is not involved in the calculation. Therefore, intermediate state data can be pruned based on the aggregation key. However, if the operator used as the state node does not have these columns, it is forced to scan the entire data, which greatly reduces the acceleration effect.

[0091] Figure 9 The sample code shown includes:

[0092] With cte AS (

[0093] SELECT * FROM(

[0094] SELECT * FROM A

[0095] WHERE date =0101'

[0096] )LATERAL VIEW EXPLODE(attributes)exploded attr

[0097] AS single_attr )

[0099] Select * FROM cte LEFT JOIN B on cte.id = B.id WHERE B.COI2 IS NOTNULL;

[0100] like Figure 9As shown in the following example, for a Join computation, both its left and right sides need to save state. For the left side, there are three options: 1. Save the Explode; 2. Save the Filter; 3. Save the Scan. These three options can be determined according to the previous rules. First, the state operator Explode is encountered. Since it expands a row of data into multiple rows, it will cause exponential data expansion in the state node, making it a less optimal option. Further down the Explode, the operator Filter is encountered. This is a filtering operator that filters the input data, retaining only a portion of the results. Therefore, if the filtering performance is excellent—that is, it can filter the input data to retain only a small portion of the results—even if the Explode is recalculated, there will still be positive returns. However, if the filtering performance is very poor, then further exploration is required to examine other operators. Finally, the operator Scan is encountered. This is a read operation, meaning that the data to be read has already been persisted, so there is no need to save it again. Therefore, the Scan is not considered a state node. In summary, saving the filter result may be the best option to some extent.

[0101] c. If neither of the above conditions is met, no state is saved. Most common computational logic will meet the judgment criteria of a or b, which means that the judgment rules cover the vast majority of practical scenarios.

[0102] If "No": the operator itself does not need to create additional state storage. In this case, it is necessary to determine whether the current node needs to be saved based on the subsequent nodes.

[0103] More detailed discrimination rules are recorded in Table 1.

[0104]

[0105] Table 1

[0106] Once the location for creating the intermediate state is selected, a sink is added to the current execution plan to persist the contents of the current node to the storage medium. The persisted data is also stored as a normal table (readable and writable, no different from a table created directly by the user), and the relevant information is recorded in the Meta metadata of the current refresh target table to track all intermediate states created by different tasks.

[0107] The information that needs to be recorded primarily includes: 1. The name of the table after the current intermediate state is persisted as a table; 2. The logical execution plan for the current intermediate state, used for node matching during reuse; and 3. The version numbers of each source table on which the current intermediate state depends, that is, what data it has consumed. Based on these three types of metadata, the intermediate state can be fully restored during the subsequent state reuse phase and embedded into the incremental execution plan.

[0108] Furthermore, for stateful operators (Join / Agg / Window), the state they rely on often has strong correlations with certain columns of incremental data (JoinKey / GroupKey / PartitionKey). Therefore, additional constraints can be automatically established on these correlated columns for intermediate state to accelerate reuse. These features significantly speed up state loading, directly filtering out most state files that don't need to be loaded. This reduces repetitive computations and further improves query performance. Specific details are listed in Table 2.

[0109]

[0110] Table 2

[0111] The specific generation process is as follows Figure 5 As shown in the figure, the SQL statement passed by the user is first compiled into a logical execution plan. Then, based on the above judgment rules, the logical execution plan splits the AVG in the Aggregate operator into COUNT and SUM. The two parts are then connected to a Sink operator for persistent storage. It is worth noting that Join is also a stateful operator. However, because the Scan operator on its left already expresses the persistence semantics, and the Aggregate on its right has already been saved, Join does not require any additional state storage.

[0112] Figure 5 Some of the sample code shown includes:

[0113] INSERT OVERWRITE TABLE res

[0114] SELECT * FROM

[0115] (SELECT *,AVG(c1) as _avg FROM T2) t2

[0116] INNER JOIN

[0117] (SELECT * FROM T1) t1

[0118] on t1.join_key = t2.join_key

[0119] 2. Matching intermediate states

[0120] After selecting the intermediate state and completing the persistent storage of the intermediate table, the next issue to consider is how to apply the existing intermediate state to the current query optimization. There are two possible solutions to achieve intermediate state reuse:

[0121] a. Split the original execution plan directly from the intermediate state into two execution sub-segments, one above the other. These sub-segments are stored in the metadata. The next time the plan is executed, they are retrieved and executed sequentially (first executing the data source-intermediate state portion, then the intermediate state-output portion). Once the intermediate state is created, all execution plans are executed sequentially, ensuring that the intermediate state is used.

[0122] b. The original execution plan remains unchanged. The intermediate state is treated as an equivalent replaceable node and selectively placed in the original execution plan. The optimizer freely chooses whether to use the last saved intermediate state based on the cost model.

[0123] After repeated evaluation, we decided on the second solution, which decouples the intermediate state from the user's original query. In other words, the state is simply a tool that can be used dynamically to accelerate queries, not a required part of the original query. Whenever a user decides to change the query SQL, these decoupled states can be independently and adaptively upgraded, retired, or regenerated, giving the system and users more freedom.

[0124] This led to the development of a new intermediate state matching algorithm (View-based Optimization, or Vbo). This algorithm treats the intermediate state matching problem as a subproblem of view rewriting (i.e., how to rewrite arbitrary user queries using a defined view) and solves it using optimizer-related technologies. From the optimizer's perspective, view matching can be abstracted into the problem of matching two logical execution plans. Specifically, the intermediate state has a logical execution plan P1, and the query to be accelerated generates a logical execution plan P2. Both are composed of several operators. When these two logical execution plans are input into Vbo, the framework matches operators one by one from the bottom up, starting from the Scan root node at the bottom and working upwards layer by layer.

[0125] Each level of matching has several possibilities: no match: the materialized view matching fails; full match: the matching continues upwards; partial match: the query requires fewer rows, columns, etc. than the view, and a compensating filter or project (or other operators) needs to be calculated. This compensation will be pulled up step by step, through the subsequent series of nodes to be matched. If the compensation cannot be calculated or pulled up, it also means that the view matching failed. If at the end, the view Figure 1 If the root node is matched directly, the logical execution plan of the entire view can be replaced with a scan operation on the view.

[0126] At this point, a logical execution plan is formed, consisting of several consecutive compensation operators, with an operator at the root node that reads data from the view. This means that, after a series of transformations, the previously saved state can be re-equalized to a certain position in the currently matched execution plan through a brief calculation. This allows for great flexibility in saving intermediate state. Even if the current execution plan differs slightly from the original plan used for state preservation after a period of system iteration, a series of computational transformations can still allow the old state node to be used in the current execution plan.

[0127] The specific process is as follows Figure 6 As shown, the previously saved intermediate state will put the logical execution plan that generated it into Vbo together with the current logical execution plan for state matching. The first comparison is the Scan operator. After the match is successful, the remaining operators will continue to match. Since Sink is a special persistent operator that only stores data in the storage medium and does not change any logical semantics, it will not participate in state matching. When it matches the Aggregate, the state matching has been completed and all operators have found the corresponding equivalent operators. So the subsequent steps are as follows Figure 7 As shown in the figure, a Scan (state) operator is placed directly in the incremental logical execution plan. It can directly read the previous aggregation result and merge it with the incremental aggregation result obtained by calculating the current incremental data to obtain the final incremental calculation expression.

[0128] 3. Update and maintain intermediate state

[0129] After state matching is complete, the intermediate state is placed in the execution plan in the form of Scan(state), and a corresponding Sink(state) is generated to write the updated state of this refresh task. Any state will have a Sink(state) to ensure data consistency.

[0130] In addition, if a state fails to match multiple refresh tasks consecutively (for example, due to SQL changes or major code changes that significantly alter the execution plan and are no longer compatible with the old version), it will be automatically recycled by the system to free up the occupied storage space. The corresponding metadata will also be automatically recycled.

[0131] In particular, when SQL changes, the state storage will also be updated adaptively.

[0132] For example, the original SQL is

[0133] "... SELECT COUNT(*) FROM SubQuery GROUP BY C1, C2;"

[0134] The state at this time is equivalent to SQL:

[0135] "... SELECT COUNT(*) FROM SubQuery GROUP BY C1, C2;"

[0136] The changed SQL is

[0137] "... SELECT COUNT(*) FROM SubQuery GROUP BY C1;"

[0138] The new state is equivalent to SQL:

[0139] "... SELECT COUNT(*) FROM SubQuery GROUP BY C1;"

[0140] It is easy to adaptively update from the first state to the second state, that is,

[0141] new_state = SELECT SUM(COUNT(*)) FROM old_state GROUP BY C1;

[0142] This allows for link upgrades while significantly reducing overhead. Notably, this equivalent upgrade is performed only once: after the new state is successfully created, the old state is automatically recycled to free up the space it occupied. This upgrade is also accomplished by the Vbo matching algorithm, which adds an additional aggregation operator to compensate for the old state's equivalent. The result of view-based reaggregation is considered equivalent to the current operator's result.

[0143] An embodiment of a general incremental computing device based on an intermediate state of the present invention can be applied to any device with data processing capabilities, and the device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented through software, or through hardware or a combination of software and hardware. Taking software implementation as an example, as a device in a logical sense, it is formed by the processor of any device with data processing capabilities in which it is located reading the corresponding computer program instructions in the non-volatile memory into the memory for execution. From the hardware level, if Figure 8 As shown, this is a hardware structure diagram of a general incremental calculation device based on an intermediate state of the present invention, in which any device with data processing capability is located. Figure 8 In addition to the processor, memory, network interface, and non-volatile memory shown, any device with data processing capabilities in which the apparatus in the embodiments is located may also include other hardware, typically based on the actual functions of the device with data processing capabilities, and this will not be described in detail here. The implementation process of the functions and effects of each unit in the above-mentioned apparatus is detailed in the implementation process of the corresponding steps in the above-mentioned method, and will not be described in detail here.

[0144] For the device embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to the partial description of the method embodiments. The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the present invention. A person of ordinary skill in the art can understand and implement the present invention without inventive work.

[0145] An embodiment of the present invention further provides a computer-readable storage medium having a program stored thereon. When the program is executed by a processor, a general incremental calculation device based on an intermediate state in the above embodiment is implemented.

[0146] The computer-readable storage medium may be an internal storage unit of any device with data processing capabilities described in any of the aforementioned embodiments, such as a hard disk or memory. The computer-readable storage medium may also be an external storage device of any device with data processing capabilities, such as a plug-in hard disk, a smart memory card (SmartMediaCard, SMC), an SD card, a flash memory card (FlashCard), etc. equipped on the device. Furthermore, the computer-readable storage medium may also include both an internal storage unit and an external storage device of any device with data processing capabilities. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capabilities, and may also be used to temporarily store data that has been output or is to be output.

[0147] 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 or improvements made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A general incremental calculation method based on intermediate states, characterized by: The steps include: S1: Obtain user SQL and parse it to generate a logical execution plan; S2: Filter and judge the operators and select the target operator for persistence processing; S3: Perform equivalent rewriting on the target operator for persistence processing, generate intermediate states, and store the intermediate states persistently in a storage medium. S4: The algorithm matches the intermediate logical execution plan with the current execution plan, so that all operators can find their corresponding equivalent operators. S5: Read the persisted intermediate state and incremental data, merge and calculate them, and output the final result. S6: Monitor, update, and maintain the final execution plan in real time; The S2 includes the following sub-steps: S21: Determine whether the operator is stateful. Aggregation operators, window operators, and join operators are stateful operators. S22: Determine whether the state of the operator is itself, and if not, rewrite it; S23: If the state required by the current operator is its input node, then the position most worth saving is selected; The S22 includes the following content: the aggregation operator is judged according to the type of the aggregation function: if it is a SUM or COUNT type, it itself is the state; if it is an AVG, it is rewritten as a SUM or COUNT type; if it is a MIN or MAX type, then when its input data is append-only, its output result is considered as the state, otherwise its input data is the state node; The S22 includes the following contents: S221: Determine the type of the operator's input data, including append-only, delete-only, or both. S222: Determine whether the intermediate state has explicitly appeared in the initial logical execution plan. If not, rewrite it to make it a state that is valuable to be saved. S4 includes the following: matching the two logical execution plans using an intermediate state matching algorithm, matching the intermediate state logical execution plan with the operators of the logical execution plan generated by the query sequentially from bottom to top until the root node, and replacing the logical execution plan of the entire view with a scan operation on the view after the matching is completed; At this point, a tree consisting of several compensation operators followed by a scan operator is generated. This tree is equivalent to a node in the logical execution plan generated by the query. The matching of the two logical execution plans in S4 includes the following: the matching results include: no match, complete match, and partial match; in the case of partial match, a compensation operator is calculated and generated, and the compensation operator is pulled up until it passes through a subsequent series of nodes that need to be matched; if the compensation operator cannot be calculated or cannot be pulled up, the view matching fails.

2. The general incremental calculation method based on intermediate states according to claim 1, characterized in that: The S3 includes the following contents: for the target operator of persistence processing, after selecting the creation location of the intermediate state, an additional sink is attached to the current execution plan, the content of the current node is persistently stored in the storage medium, and relevant information is recorded to track the several intermediate states created.

3. The general incremental calculation method based on intermediate states according to claim 1, characterized in that: The S6 includes the following contents: S61: Monitor the update of intermediate status in real time to ensure data consistency; S62: Automatically reclaim the state of multiple failed matches to release the occupied storage space; S63: When SQL changes, adaptively update the state storage.

4. A general incremental calculation device based on intermediate states, characterized in that: The method comprises a memory and one or more processors, wherein the memory stores executable code, and when the one or more processors execute the executable code, they are used to implement a general incremental calculation method based on an intermediate state as described in any one of claims 1 to 3.

5. A computer-readable storage medium, characterized in that: A program is stored thereon, and when the program is executed by a processor, a general incremental calculation method based on an intermediate state as described in any one of claims 1 to 3 is implemented.

Images