Incremental calculation method and device based on full-quantity SQL and electronic equipment

Abstract

The application provides a full-amount SQL-based incremental calculation method and device and electronic equipment. The method comprises the following steps: receiving a full-amount business SQL of a target table, parsing the full-amount business SQL into an abstract syntax tree, and generating a full-amount execution plan composed of multiple logical operators according to the abstract syntax tree; traversing the logical operators in the full-amount execution plan to form an incremental execution plan; maintaining an intermediate state and an index for the logical operators in the incremental execution plan; receiving a source table change event; determining the affected logical operators based on the primary keys and associated keys involved in the deletion sub-events and / or insertion sub-events, processing the deletion sub-events and / or insertion sub-events in the affected logical operators according to the incremental propagation rules, and updating the intermediate state through the index; passing the local changes to the successor operators of the affected logical operators, generating new local changes by the successor operators according to the incremental propagation rules, repeating the passing until the result change event of the target table is generated at the output operator.

Description

Technical Field

[0001] This application mainly relates to the field of big data technology, and in particular to an incremental calculation method, device and electronic device based on full SQL. Background Technology

[0002] In data processing platforms such as Data Factory, developers typically write full-scale business SQL to express business logic such as data cleaning, filtering, joining, and aggregation, enabling the system to perform full computations according to a scheduled cycle. Full computation requires performing full scans, full joins, and full aggregations on the source tables. As data volume increases, computational resource consumption and execution latency continuously increase, making it difficult to meet the timeliness requirements of high-frequency updates. To improve data processing efficiency, existing technologies generally use manually written incremental SQL to maintain a separate set of incremental logic. That is, the same business logic requires maintaining two sets of logic simultaneously: full-scale SQL and incremental SQL. However, this approach not only has high development and maintenance costs but is also prone to semantic drift with business iterations, leading to inconsistencies between full and incremental results. Summary of the Invention

[0003] The purpose of this application is to provide an incremental calculation method, device, and electronic device based on full SQL, so as to overcome the problems of high development threshold and maintenance cost, and inconsistent results caused by semantic drift due to manual writing of incremental SQL in the prior art.

[0004] In a first aspect, this application provides an incremental computation method based on full SQL, comprising the following steps: S1, receiving full business SQL of a target table, parsing the full business SQL into an abstract syntax tree, and generating a full execution plan composed of multiple logical operators based on the abstract syntax tree; S2, traversing the logical operators in the full execution plan, matching the corresponding incremental propagation rule for each logical operator according to its type, and applying the incremental propagation rule to the logical operator to rewrite the processing logic of the logical operator from full computation to incremental computation, forming an incremental execution plan; S3, maintaining intermediate states and indexes for the logical operators in the incremental execution plan; S4, receiving source table change events, converting the source table change events into standard change events, and identifying the type of the standard change events, the types including insert events, delete events, and update events. The update event corresponds to a delete sub-event and an insert sub-event, the insert event corresponds to an insert sub-event, the delete event corresponds to a delete sub-event, the delete sub-event represents the withdrawal of the old record's contribution to the result, and the insert sub-event represents the contribution of writing a new record to the result; S5, based on the primary key and association key involved in the delete sub-event and / or insert sub-event, determine the affected logical operator, process the delete sub-event and / or insert sub-event according to the incremental propagation rule within the affected logical operator, and update the intermediate state through the index to generate a local change of the affected logical operator; S6, pass the local change to the successor operator of the affected logical operator, and the successor operator generates a new local change according to the incremental propagation rule, repeating the transmission until the result change event of the target table is generated at the output operator.

[0005] In a second aspect, this application provides an incremental computing device based on full SQL, comprising: an SQL parsing module configured to receive full business SQL of a target table, parse the full business SQL into an abstract syntax tree, and generate a full execution plan composed of multiple logical operators based on the abstract syntax tree; an incremental rewriting module configured to traverse the logical operators in the full execution plan, match corresponding incremental propagation rules for each logical operator according to its type, and apply the incremental propagation rules to the logical operators to rewrite the processing logic of the logical operators from full computation to incremental computation, forming an incremental execution plan; a state maintenance module configured to maintain intermediate states and indexes for the logical operators in the incremental execution plan; and an event access module configured to receive source table change events, convert the source table change events into standard change events, and identify the type of the standard change events, the types including insertion events and deletion events. The system includes an update event, which corresponds to a delete sub-event and an insert sub-event, where the insert event corresponds to an insert sub-event and the delete event corresponds to a delete sub-event. The delete sub-event represents the withdrawal of the old record's contribution to the result, and the insert sub-event represents the contribution of writing a new record to the result. An incremental execution module is configured to determine the affected logical operator based on the primary key and association key involved in the delete sub-event and / or insert sub-event, process the delete sub-event and / or insert sub-event within the affected logical operator according to the incremental propagation rule, and update the intermediate state through the index to generate a local change for the affected logical operator. A result output module is configured to pass the local change to the successor operator of the affected logical operator, whereby the successor operator generates a new local change according to the incremental propagation rule and continues to propagate until a result change event for the target table is generated at the output operator.

[0006] Thirdly, this application provides an electronic device comprising: one or more processors; and one or more memories coupled to the one or more processors and storing instructions thereon; wherein the electronic device performs the method of the first aspect described above when the instructions are executed individually or jointly by the one or more processors.

[0007] Fourthly, this application provides a non-transitory computer-readable storage medium storing machine-executable instructions that, when executed by one or more processors of a machine, cause the machine to perform the method described in the first aspect.

[0008] Compared with the prior art, the present technical solution has the following beneficial effects: This technical solution automatically generates executable incremental execution plans based on the full set of business SQL, eliminating the need to maintain a separate set of incremental SQL for the same business logic. This avoids the semantic drift problem caused by two sets of logic leading to inconsistent results. By matching the corresponding incremental propagation rules according to the type of logical operator, the automatically generated incremental execution plan can be reused in the long term, reducing the development threshold and maintenance costs. By constructing intermediate states and indexes to determine the affected logical operators, irrelevant data is prevented from participating in calculations such as association and aggregation, improving the efficiency of incremental calculation. By unifying insert, delete, and update events into two basic operations, delete sub-events and insert sub-events, all subsequent logical operators only need to handle these two types of sub-events, and each operator does not need to handle update logic separately, thereby avoiding rule inconsistencies and result omissions. Attached Figure Description

[0009] The above and other objects, features, and advantages of this disclosure will become more apparent from the more detailed description of some embodiments thereof in the accompanying drawings, in which: Figure 1 A schematic diagram of an incremental calculation method based on full SQL provided in this application is shown; Figure 2 A schematic diagram of an incremental computing device based on full SQL provided in this application is shown; Figure 3 A schematic diagram of an electronic device provided in this application is shown. Detailed Implementation

[0010] The principles of this disclosure will now be described with reference to some embodiments. It should be understood that these embodiments are described for illustrative purposes only and to assist those skilled in the art in understanding and implementing this disclosure, and do not impose any limitation on the scope of this disclosure. The disclosure described herein may be implemented in ways other than those described below.

[0011] In the following description and claims, unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this disclosure pertains.

[0012] References to "an embodiment," "embodiment," "exemplary embodiment," etc., in this disclosure indicate that the described embodiments may include specific features, structures, or characteristics, but not every embodiment needs to include specific features, structures, or characteristics. Furthermore, such phrases do not necessarily refer to the same embodiment. Moreover, when a specific feature, structure, or characteristic is described in connection with an exemplary embodiment, whether explicitly described or not, those skilled in the art will recognize that such a feature, structure, or characteristic affects its connection to other embodiments.

[0013] In the description of this technical solution, "system" refers to the data processing platform. Specifically, the data processing platform is a computer system capable of SQL parsing and execution, incremental propagation rule matching, intermediate state management, source table change event reception and processing, and incremental calculation execution.

[0014] The basic concepts involved in this technical solution will be briefly introduced below.

[0015] Full business SQL: User-written SQL statements that describe the final result of the target table. Full business SQL expresses "what the target table should look like".

[0016] Full calculation: This refers to a method of re-executing the calculation logic defined in the full business SQL on all data in the source table without utilizing historical calculation results to obtain the result for the target table. Each execution scans the full data in the source table and performs all joins, filters, aggregations, and other operations.

[0017] Incremental calculation: This refers to a calculation method that performs calculations only on the data affected by the changes when the source table data changes. It utilizes the maintained intermediate state to avoid scanning back through the full data, thereby obtaining the change results of the target table.

[0018] Full execution plan: After parsing the full business SQL, a directed acyclic graph (DAG) is generated based on the abstract syntax tree, consisting of multiple logical operators (each logical operator represents a node). The logical operators in the full execution plan correspond to an operation in the full business SQL (such as filtering, projection, join, etc.), and the edges between nodes represent the data flow. The full execution plan describes the complete logical path of "how to calculate the result of the target table from the source table data," and serves as the basis for generating subsequent incremental execution plans.

[0019] Incremental execution plan: Based on the full execution plan, the execution plan is formed by applying the corresponding incremental propagation rules to the logical operators. The topology of the incremental execution plan is the same as that of the full execution plan (the nodes and their connections remain unchanged), the difference is that each node is attached with the incremental propagation rules - that is, how the operator processes and generates incremental changes when it receives a change sub-event.

[0020] Intermediate State: This is a temporary result set maintained by the logical operators in the incremental execution plan. It is continuously updated and maintained during incremental calculation to prevent the full data from being scanned back during incremental calculation. Different types of logical operators maintain different intermediate states. For example: Join operators maintain valid records from another related table; Aggregation operators maintain the current aggregation values ​​of each group (such as the accumulated value of the SUM operator, the count value of the COUNT operator, and the maximum / minimum values ​​of MAX / MIN); Window operators maintain the current data state within the window; Deduplication operators maintain the records currently retained for each deduplication key.

[0021] Indexes: Intermediate query interfaces. Different types of logical operators maintain indexes built on different keys. For example, the Join operator maintains an index built on the join key; the aggregation operator maintains an index built on the aggregation dimension; and the deduplication operator maintains an index built on the deduplication key.

[0022] The following is combined with Figure 1 This application provides a detailed description of the specific steps involved in incremental computation based on a full set of SQL statements. For example... Figure 1 As shown, the method specifically includes the following steps: Step S1: Receive the full business SQL of the target table, parse the full business SQL into an abstract syntax tree, and generate a full execution plan consisting of multiple logical operators based on the abstract syntax tree.

[0023] For example, in a medical data factory scenario, a user can submit the following full-scale business SQL: SELECT o.patient_id, d.dept_name, SUM(o.amount) AS total_amount FROM ods_order o JOIN dim_dept d ON o.dept_id = d.dept_id WHERE o.is_valid = 1 GROUP BY o.patient_id, d.dept_name; The above SQL only defines the result and does not specify how to partially update the target table when ods_order or dim_dept changes. After receiving the full business SQL, the system first parses it into an abstract syntax tree (AST), extracts semantic fragments such as Select, Filter, Join, and Group By, and then generates a full execution plan consisting of multiple logical operator nodes based on the AST.

[0024] Step S2: Traverse the logical operators in the full execution plan, match the corresponding incremental propagation rule for each logical operator according to its type, and apply the incremental propagation rule to the logical operator to rewrite the processing logic of the logical operator from full calculation to incremental calculation, thus forming an incremental execution plan.

[0025] In some embodiments, the incremental propagation rule in step S2 includes at least one of the following: (1) Incremental propagation rules for filtering operators: The filter is propagated to subsequent operators when the deletion sub-event and / or insertion sub-event meet the filtering conditions, and not when they do not meet the conditions. For example, when the full business SQL contains WHERE o.is_valid = 1, if the is_valid field of the changed record is 1, the changed record is propagated to the next operator; if it is 0, it is not propagated.

[0026] (2) Incremental propagation rules for projection operators: retain the fields required by subsequent operators and keep the primary key or change identifier traceable. The projection operator additionally retains the primary key field to maintain the traceability of change records in subsequent propagation.

[0027] (3) Incremental propagation rules of Join operator: Use intermediate states and indexes to perform local joins on the changed table and the other table, and generate incremental join results only within the range of affected key values. Specifically, when a change sub-event of one source table arrives, query the record on the other side stored in the intermediate state of the Join operator with the join key value in the sub-event, and only perform a local join with the sub-event on the records hit by the query, instead of re-executing the full join on both sides.

[0028] (4) Incremental propagation rules for aggregation operators: The current aggregation value of each group is maintained according to the type of aggregation function. If the aggregation function is SUM or COUNT, addition or subtraction is performed based on the current aggregation value. If the aggregation function is MAX or MIN, a candidate set or auxiliary index is further maintained. For insertion sub-events, the value of the new record is added to the candidate set or auxiliary index and compared with the current maximum or minimum value and updated. For deletion sub-events, the maximum or minimum value is re-determined from the candidate set or auxiliary index. If the aggregation function is AVG, it is decomposed into SUM and COUNT. When the AVG value is needed, it is calculated based on the SUM aggregation value and the COUNT aggregation value. Aggregation operators avoid backscanning the entire group of data by maintaining the current aggregation value (and candidate set or auxiliary index) according to the aggregation function type.

[0029] (5) Incremental propagation rules for deduplication operators: Maintain deduplication key index and priority retention rules. When the deduplication key of an insertion sub-event conflicts with the deduplication key of a retained record, the deletion sub-event and insertion sub-event are propagated to subsequent operators. The priority retention rule is used to determine which record to retain when multiple records have the same deduplication key.

[0030] (6) Incremental propagation rule of window operators: Update the state of affected windows according to event time, water level and window boundary. That is, only the state of windows involved in the change sub-event is updated, and other windows are not affected.

[0031] (7) Incremental propagation rule of Union operator: The source identifier is retained in each change stream and multiple change streams are merged. The source identifier is used to distinguish which input the change record comes from so that subsequent operators can process it correctly.

[0032] In step S2, the system can automatically supplement the incremental execution plan with the key metadata required for incremental calculation based on the parsing results of the full business SQL and the metadata of the source tables. Alternatively, users can configure and supplement the key metadata. Specifically, the key metadata includes: the primary key or unique identifier of the source table, used to locate records by primary key in the source table's status index; the type of source table change event, including insert, delete, and update events, used to determine the event processing method; the Join key, used to locate the record on the other side in the Join operator's status index; the aggregation dimension, used to locate the aggregate value by grouping key in the aggregation operator's status index; and the event time field, used by the window operator to determine the affected window by combining event time, watermark, and window boundary. For example, in a medical data factory scenario, the system obtains the Join key as dept_id, the aggregation dimensions as patient_id and dept_name, and the aggregation function type as SUM from the SQL parsing results; it obtains the primary key of the source table ods_order as order_id and the primary key of the source table dim_dept as dept_id from the database metadata; and the event time field is specified through configuration.

[0033] In some embodiments, in step S2, when there is a syntax structure in the full execution plan that is not covered by the incremental propagation rule, the sub-execution plan corresponding to the syntax structure is determined, a partial batch recalculation is performed on the sub-execution plan, and the incremental propagation rule is applied to the remaining sub-execution plans in the full execution plan for rewriting.

[0034] For example, a full business SQL statement contains nodes with four logical operators: filtering, join, complex nested subqueries, and aggregation. When the system traverses the logical operators during the rule matching phase, it matches the corresponding incremental propagation rules for the filtering, join, and aggregation operators. However, the node for the complex nested subquery does not have a corresponding incremental propagation rule. In this case, the system only performs a partial batch recalculation on the sub-execution plan corresponding to that node, while the remaining nodes continue to apply the incremental propagation rules for incremental rewriting. This ensures that when a source table change event occurs, the filtering, join, and aggregation parts still follow the incremental path, while only the subquery part undergoes batch recalculation, rather than the entire SQL statement being rolled back to full recalculation. The advantage of this approach is that it allows the system to process complex SQL statements in a hybrid execution manner, maximizing the performance gains of incremental calculation while ensuring correctness, and avoiding a complete rollback to full recalculation due to a few uncovered special cases.

[0035] Step S3: Maintain intermediate states and indexes for the logical operators in the incremental execution plan.

[0036] Specifically, the Join operator maintains an index built on the join key, storing the valid records of the other joined table; the Aggregation operator maintains an index built on the aggregation dimension, storing the current aggregation value and candidate set or auxiliary index of each group; the Window operator maintains the current data state within the window; and the Deduplication operator maintains an index built on the deduplication key, storing the records currently retained for each deduplication key. For example, for the full business SQL in the above medical data factory scenario, the Join operator maintains an index in the dim_dept table with dept_id as the index key, so that when a change record in ods_order arrives, the corresponding department record in dim_dept can be quickly located by the dept_id value; the Aggregation operator maintains an index with patient_id and dept_name as index keys, storing the SUM cumulative value of each group, so that when a change arrives, the current cumulative value can be directly read to perform addition or subtraction operations.

[0037] In some embodiments, step S3 further maintains the result key, source key, and lineage mapping relationship. The result key identifies a record in the target table or intermediate result, the source key identifies a record in the source table or upstream logical operator output, and the lineage mapping relationship records the correspondence between the source key and the result key. For example, in a medical data factory scenario, using "order_id=O001" as the source key and "Internal Medicine, total_amount=3700" as the result key, the system maintains the lineage mapping relationship to record the correspondence between the source key and the result key. When "order_id=O001" is deleted, the system directly locates the target table record corresponding to the result key through this lineage mapping relationship to perform precise retraction. The advantage of this approach is that when record retraction, dimension table changes, and cross-layer propagation occur, the system can accurately locate the specific affected record and perform precise retraction and rewriting, rather than coarsely recalculating the entire group or the entire target table, thereby reducing unnecessary computational overhead while ensuring the correctness of the results.

[0038] Step S4: Receive source table change events, convert source table change events into standard change events, and identify the type of standard change events.

[0039] The standard change events include insertion, deletion, and update events. Update events correspond to deletion and insertion sub-events, and insertion events correspond to insertion sub-events. Deletion sub-events represent the withdrawal of the old record's contribution to the result, while insertion sub-events represent the contribution of writing a new record to the result. The advantage of this approach is that by unifying insertion, deletion, and update events into two basic operations—deletion and insertion sub-events—all subsequent logical operators only need to handle these two types of sub-events. Each operator does not need to handle update logic separately, thus avoiding inconsistencies in rules and omissions in results.

[0040] In some embodiments, in step S4, the standard change event includes the source table identifier, event type, primary key, transaction position or version number, pre-change image, post-change image, and event time. The advantage of this is that it converts source table change events into a unified standard event format, avoiding the need for different access sources to maintain their own set of processing logic, thus reducing maintenance costs.

[0041] In some embodiments, in step S4, source table change events are received from any one or more data sources, including database Binlog, log subscription, message queue, tabular storage change log, and application-side event stream.

[0042] Step S5: Determine the affected logical operators based on the primary key and association key involved in the deletion sub-event and / or insertion sub-event. Process the deletion sub-event and / or insertion sub-event within the affected logical operators according to the incremental propagation rule, and update the intermediate state through the index to generate local changes of the affected logical operators.

[0043] Step S6: The local changes are passed to the successor operators of the affected logic operators. The successor operators generate new local changes according to the incremental propagation rules. This process is repeated until a result change event of the target table is generated at the output operator.

[0044] Specifically, result change events are target table change operations formed by the summation of incremental changes generated layer by layer by each logical operator at the output operator after propagation. The output format can include: inserting target rows, deleting target rows, updating target rows, rollback events for existing results, and correction events for aggregate values, window values, or associated results. In different implementations, the result writing format can be either "rollback stream + append stream" or directly outputting Upsert, but the core logic is to generate target result corrections that are consistent with the full semantics based on the impact of the change.

[0045] In some embodiments, after step S6, the method further includes: writing the result change event to the target storage in the form of Upsert, reversal stream, change stream, snapshot table, or subscription event, thereby persisting the incremental calculation result to the target storage in multiple forms. In some embodiments, version number, batch boundary, and state snapshot can also be synchronously advanced. To ensure project availability, idempotent write control, retry compensation, checkpoint persistence, state and result consistency verification, and breakpoint continuation calculation after fault recovery can also be performed, thereby making the entire incremental execution chain a deployable production-grade execution solution, rather than just remaining at the internal operator level of state maintenance.

[0046] Figure 2 This application illustrates an incremental calculation device based on full SQL statements. The device specifically includes an SQL parsing module 201, an incremental rewriting module 202, a state maintenance module 203, an event access module 204, an incremental execution module 205, and a result output module 206, wherein: The SQL parsing module 201 is configured to receive the full business SQL of the target table, parse the full business SQL into an abstract syntax tree, and generate a full execution plan consisting of multiple logical operators based on the abstract syntax tree.

[0047] The incremental rewriting module 202 is configured to traverse the logical operators in the full execution plan, match the corresponding incremental propagation rules for the logical operators according to their types, and apply the incremental propagation rules to the logical operators to rewrite the processing logic of the logical operators from full calculation to incremental calculation, thus forming an incremental execution plan.

[0048] The state maintenance module 203 is configured to maintain intermediate states and indexes as a logical operator in the incremental execution plan.

[0049] The event access module 204 is configured to receive source table change events, convert source table change events into standard change events, and identify the type of standard change events. The types include insert events, delete events, and update events. Update events correspond to delete sub-events and insert sub-events, insert events correspond to insert sub-events, and delete events correspond to delete sub-events. Delete sub-events indicate the withdrawal of the old record's contribution to the result, and insert sub-events indicate the contribution of writing a new record to the result.

[0050] The incremental execution module 205 is configured to determine the affected logical operators based on the primary key and associated key involved in the deletion sub-event and / or insertion sub-event, process the deletion sub-event and / or insertion sub-event within the affected logical operators according to the incremental propagation rules, and update the intermediate state through the index to generate local changes of the affected logical operators.

[0051] The result output module 206 is configured to pass local changes to the successor operators of the affected logic operators, and the successor operators generate new local changes according to the incremental propagation rules and continue to propagate until the result change event of the target table is generated at the output operator.

[0052] The aforementioned incremental computing device based on full SQL can be implemented as a data processing platform for implementation. Figure 1 The illustrated method is an incremental calculation method based on full SQL. For the device embodiment, since it basically corresponds to the method embodiment, relevant details can be found in the description of the method embodiment. The device embodiments described above are merely illustrative, and some or all of the modules can be selected to achieve the purpose of the present invention according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0053] Figure 3 This refers to an electronic device applicable to embodiments of this application. For example... Figure 3 The electronic device includes one or more memories 301 and one or more processors 302, wherein the one or more memories 301 are coupled to and store instructions thereon, the instructions being executed individually or jointly by the one or more processors 302, causing the electronic device to perform the method as described in any of the first aspects.

[0054] It should be understood that the processor mentioned in the embodiments of this application can be a CPU, or other general-purpose processors, DSPs, ASICs, FPGAs, or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor can be a microprocessor or any conventional processor.

[0055] It should also be understood that the memory mentioned in the embodiments of this application can be volatile memory or non-volatile memory, or may include both volatile and non-volatile memory. Non-volatile memory can be read-only memory (ROM), programmable read-only memory, erasable programmable read-only memory, electrically erasable programmable read-only memory, or flash memory. Volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of RAM are available, such as static random access memory, dynamic random access memory, synchronous dynamic random access memory, double data rate synchronous dynamic random access memory, enhanced synchronous dynamic random access memory, synchronous linked dynamic random access memory, and direct memory bus random access memory.

[0056] The basic concepts have been described above. Obviously, for those skilled in the art, the above disclosure is merely illustrative and does not constitute a limitation of this application. Although not explicitly stated herein, those skilled in the art may make various modifications, improvements, and corrections to this application. Such modifications, improvements, and corrections are covered within this application and therefore remain within the spirit and scope of the exemplary embodiments of this application.

[0057] Furthermore, this application uses specific terms to describe embodiments of the application. For example, "an embodiment," "one embodiment," and / or "some embodiments" refer to a particular feature, structure, or characteristic related to at least one embodiment of the application. Therefore, it should be emphasized and noted that "an embodiment," "one embodiment," or "an alternative embodiment" mentioned twice or more in different locations in this specification do not necessarily refer to the same embodiment. In addition, certain features, structures, or characteristics in one or more embodiments of the application can be appropriately combined.

[0058] Some aspects of this application can be executed entirely by hardware, entirely by software (including firmware, resident software, microcode, etc.), or by a combination of hardware and software. The aforementioned hardware or software may be referred to as a "data block," "module," "engine," "unit," "component," or "system." The processor may be one or more application-specific integrated circuits (ASICs), digital signal processors (DSPs), digital signal processing devices (DAPDs), programmable logic devices (PLDs), field-programmable gate arrays (FPGAs), processors, controllers, microcontrollers, microprocessors, or combinations thereof. Furthermore, aspects of this application may manifest as computer products residing in one or more computer-readable media, including computer-readable program code. For example, computer-readable media may include, but are not limited to, magnetic storage devices (e.g., hard disks, floppy disks, magnetic tapes, etc.), optical discs (e.g., compressed CDs, digital multifunction DVDs, etc.), smart cards, and flash memory devices (e.g., cards, sticks, key drives, etc.).

[0059] A computer-readable medium may contain a propagated data signal containing computer program code, for example, on baseband or as part of a carrier wave. This propagated signal may take various forms, including electromagnetic, optical, and so on, or suitable combinations thereof. A computer-readable medium can be any computer-readable medium other than a computer-readable storage medium, which can be connected to an instruction execution system, apparatus, or device to enable communication, propagation, or transmission of a program for use. The program code located on the computer-readable medium can be propagated through any suitable medium, including radio, cable, fiber optic cable, radio frequency signals, or similar media, or any combination of the above media.

[0060] Similarly, it should be noted that, in order to simplify the description of the present application and thus aid in the understanding of one or more embodiments of the invention, the foregoing description of the embodiments of the present application sometimes combines multiple features into a single embodiment, drawing, or description thereof. However, this disclosure method does not imply that the subject matter of the application requires more features than those mentioned in the claims. In fact, the embodiments contain fewer features than all the features of the single embodiments disclosed above.

[0061] In some embodiments, numbers describing the quantity of components and attributes are used. It should be understood that such numbers used in the description of embodiments are modified in some examples with the terms "approximately," "approximately," or "generally." Unless otherwise stated, "approximately," "approximately," or "generally" indicates that the numbers are allowed to vary by ±20%. Accordingly, in some embodiments, the numerical parameters used in the specification and claims are approximate values, which may be changed depending on the characteristics required by individual embodiments. In some embodiments, numerical parameters should take into account specified significant digits and employ a general method of digit reservation. Although the numerical ranges and parameters used to confirm their breadth of scope in some embodiments of this application are approximate values, in specific embodiments, such values ​​are set as precisely as feasible.

Claims

1. An incremental calculation method based on full SQL statements, characterized in that, Includes the following steps: S1, Receive the full business SQL of the target table, parse the full business SQL into an abstract syntax tree, and generate a full execution plan consisting of multiple logical operators based on the abstract syntax tree; S2, traverse the logical operators in the full execution plan, match the corresponding incremental propagation rule for the logical operator according to the type of the logical operator, apply the incremental propagation rule to the logical operator to rewrite the processing logic of the logical operator from full calculation to incremental calculation, and form an incremental execution plan; S3, maintain intermediate states and indexes for the logical operators in the incremental execution plan; S4, receive source table change events, convert the source table change events into standard change events, and identify the type of the standard change events. The types include insert events, delete events, and update events. The update event corresponds to delete sub-events and insert sub-events. The insert event corresponds to insert sub-events. The delete event corresponds to delete sub-events. The delete sub-event indicates the withdrawal of the old record's contribution to the result. The insert sub-event indicates the contribution of writing a new record to the result. S5, determine the affected logical operator based on the primary key and association key involved in the deletion sub-event and / or insertion sub-event, process the deletion sub-event and / or insertion sub-event within the affected logical operator according to the incremental propagation rule, and update the intermediate state through the index to generate local changes of the affected logical operator; S6, the local change is passed to the successor operator of the affected logical operator, and the successor operator generates a new local change according to the incremental propagation rule. The propagation is repeated until the result change event of the target table is generated at the output operator.

2. The method as described in claim 1, characterized in that, In step S2, the incremental propagation rule includes at least one of the following: Incremental propagation rule for filtering operators: propagate to subsequent operators when the deletion and / or insertion of sub-events meet the filtering conditions, and do not propagate when they do not meet the conditions; Incremental propagation rules for projection operators: retain the fields required by subsequent operators and keep the primary key or change identifier traceable; Incremental propagation rule of the Join operator: Use the intermediate state and the index to perform a local join between the changed table and another table associated with the Join, and generate incremental Join results only within the range of affected key values; Incremental propagation rules for aggregation operators: Maintain the current aggregation value of each group according to the type of aggregation function. If the aggregation function is SUM or COUNT, perform addition or subtraction operations based on the current aggregation value. If the aggregation function is MAX or MIN, further maintain a candidate set or auxiliary index. For insertion sub-events, add the value of the new record to the candidate set or auxiliary index and compare it with the current maximum or minimum value and update it. For deletion sub-events, redetermine the maximum or minimum value from the candidate set or auxiliary index. If the aggregation function is AVG, decompose it into SUM and COUNT. When AVG value is needed, it is calculated based on the SUM aggregation value and the COUNT aggregation value. Incremental propagation rules for deduplication operators: Maintain deduplication key index and priority retention rules. When the deduplication key of an insertion sub-event conflicts with the deduplication key of a retained record, the deletion sub-event and insertion sub-event are passed to subsequent operators. Incremental propagation rule for window operators: Update the state of the affected window based on the event time, water level, and window boundaries; The incremental propagation rule of the Union operator is to retain the source identifier in each changed flow and merge multiple changed flows.

3. The method as described in claim 1, characterized in that, In step S4, the standard change event includes source table identifier, event type, primary key, transaction position or version number, pre-change mirror, post-change mirror, and event time.

4. The method as described in claim 1, characterized in that, In step S4, the source table change event is received from any one or more data sources, including the database Binlog, log subscription, message queue, tabular storage change log, and application-side event stream.

5. The method as described in claim 1, characterized in that, In step S3, the result key, source key, and lineage mapping relationship are further maintained, wherein the result key identifies a record in the target table or intermediate result, the source key identifies a record in the source table or upstream logical operator output, and the lineage mapping relationship records the correspondence between the source key and the result key.

6. The method as described in claim 1, characterized in that, In step S2, when there is a syntax structure in the full execution plan that is not covered by the incremental propagation rule, the sub-execution plan corresponding to the syntax structure is determined, a partial batch recalculation is performed on the sub-execution plan, and the incremental propagation rule is applied to the remaining sub-execution plans in the full execution plan for rewriting.

7. The method as described in claim 1, characterized in that, After step S6, the method further includes writing the result change event to the target storage in the form of Upsert, Recall Stream, Change Stream, Snapshot Table, or Subscription Event.

8. An incremental computing device based on full SQL, characterized in that, include: The SQL parsing module is configured to receive the full business SQL of the target table, parse the full business SQL into an abstract syntax tree, and generate a full execution plan consisting of multiple logical operators based on the abstract syntax tree. The incremental rewriting module is configured to traverse the logical operators in the full execution plan, match the corresponding incremental propagation rule for the logical operator according to the type of the logical operator, and apply the incremental propagation rule to the logical operator to rewrite the processing logic of the logical operator from full calculation to incremental calculation, thereby forming an incremental execution plan; The state maintenance module is configured to maintain intermediate states and indexes for logical operators in the incremental execution plan; The event access module is configured to receive source table change events, convert the source table change events into standard change events, and identify the type of the standard change events. The types include insert events, delete events, and update events. The update event corresponds to the delete sub-event and the insert sub-event. The insert event corresponds to the insert sub-event. The delete event corresponds to the delete sub-event. The delete sub-event indicates the withdrawal of the old record's contribution to the result. The insert sub-event indicates the contribution of writing a new record to the result. An incremental execution module is configured to determine the affected logical operators based on the primary key and association key involved in the deletion sub-event and / or insertion sub-event, process the deletion sub-event and / or insertion sub-event within the affected logical operators according to the incremental propagation rules, and update the intermediate state through the index to generate local changes to the affected logical operators; and, The result output module is configured to pass the local changes to the successor operators of the affected logical operators, and the successor operators generate new local changes according to the incremental propagation rules and continue to propagate until the result change event of the target table is generated at the output operator.

9. An electronic device, characterized in that, include: One or more processors; And one or more memories coupled to one or more processors and storing instructions thereon; When the instructions are executed individually or jointly by one or more processors, the electronic device performs the method as described in any one of claims 1-7.

10. A non-transitory computer-readable storage medium storing machine-executable instructions, characterized in that, When executed by one or more processors of the machine, the machine-executable instructions cause the machine to perform the method as described in any one of claims 1-7.

Images