Materialized view refresh method, apparatus, computer equipment and storage medium

By parsing materialized view refresh requests and obtaining pre-stored metadata for verification, incremental refresh query statements are generated, which solves the problems of data corruption and execution instability in materialized view refresh and achieves data consistency and stability in high-concurrency scenarios.

CN123068437APending Publication Date: 2026-11-10JINZHUAN INFORMATION TECHNOLOGY CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202611246616.6
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2026-08-17
Publication Date
2026-11-10

AI Technical Summary

Technical Problem

Existing materialized view refresh technologies suffer from insufficient reliability in determining refresh scenarios, poor adaptability to different view types, and lax snapshot boundary control, leading to data corruption and unstable execution, making it difficult to meet the data correctness and stability requirements of high-concurrency business scenarios.

Method used

By parsing the materialized view refresh request, pre-stored metadata is obtained and the refresh scenario is verified to determine whether it meets the incremental refresh mode. Independent read-write transactions are started to obtain database consistency snapshot information, incremental refresh query statements are generated, and target incremental data is filtered and refreshed based on the view definition information.

Benefits of technology

It improves the data accuracy and execution stability of materialized view refresh, avoids data corruption and duplicate processing issues, and ensures data consistency in high-concurrency scenarios.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN123068437A_ABST
    Figure CN123068437A_ABST
Patent Text Reader

Abstract

This invention relates to the field of database technology and discloses a materialized view refresh method, apparatus, computer device, and storage medium, comprising: acquiring and parsing a materialized view refresh request to extract the target materialized view to be refreshed and its associated refresh mode; when the target materialized view is not in a refresh state, acquiring pre-stored metadata of the target materialized view and verifying the refresh scenario of the target materialized view; when the refresh scenario meets the execution conditions of the incremental refresh mode, initiating an independent read-write transaction, acquiring the database consistency snapshot information of the independent read-write transaction, rewriting the query statement based on the view definition information in the pre-stored metadata, and generating an incremental refresh query statement; based on the incremental refresh query statement and the database consistency snapshot information, selecting target incremental data from the materialized view log of the target database to refresh the target materialized view. This invention can improve the data correctness and execution stability of materialized view refresh.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] This invention relates to the field of database technology, and specifically to a materialized view refresh method, apparatus, computer device, and computer-readable storage medium. Background Technology

[0002] Materialized views are database objects used to store pre-computed query results. They persistently store query results from the base table, significantly reducing real-time computation overhead in complex query scenarios. They are widely used in data analysis, business reporting, and real-time metric statistics. When base table data changes, a refresh operation needs to be performed on the materialized view to ensure consistency between the data stored within the materialized view and the base table data. Currently, materialized view refreshes are mainly divided into full refresh and incremental refresh modes. Full refresh mode re-executes the entire query and overwrites all data in the materialized view, consuming significant CPU and I / O resources and resulting in long refresh times in large-scale data scenarios. Incremental refresh mode relies on the materialized view log to capture base table change records and updates the materialized view data only based on these change records, resulting in lower resource overhead and better suitability for high-frequency refresh requirements.

[0003] Currently, existing incremental refresh solutions based on materialized view logs typically read the materialized view logs directly to perform incremental refresh after receiving a refresh request from the client. However, in practical applications, these existing technologies have several shortcomings: First, they lack systematic verification of refresh requests and preconditions. They fail to fully integrate the pre-stored metadata corresponding to the materialized view to comprehensively verify whether the current refresh scenario meets the execution conditions for incremental refresh mode, easily leading to incorrect execution of the incremental refresh process in scenarios where the conditions for incremental refresh are not met, causing data corruption. Second, the refresh logic for different view types differs fundamentally. Most existing solutions use a unified query rewriting and data update logic, failing to differentiate view types based on view definition information to rewrite query statements, easily causing errors in materialized view updates. Third, the incremental refresh process suffers from insufficient transaction boundary control, failing to define the visibility range of the incremental log based on transaction snapshots, easily leading to misprocessing of change logs generated after the snapshot time point, resulting in duplicate refreshes or missed incremental data processing.

[0004] In summary, existing materialized view incremental refresh techniques suffer from several technical issues, including insufficient reliability in determining refresh scenarios, poor adaptability to different view types, lax snapshot boundary control, and the potential for data inconsistency caused by abnormal logs. These limitations make it difficult to simultaneously meet the requirements of data accuracy and execution stability for materialized view refresh in high-concurrency business scenarios. Therefore, a new materialized view refresh method is urgently needed to address these technical shortcomings. Summary of the Invention

[0005] In view of the shortcomings of the prior art, the purpose of this invention is to provide a materialized view refresh method, apparatus, computer device and computer-readable storage medium, aiming to solve the problem of how to improve the data correctness and execution stability of materialized view refresh.

[0006] To achieve the above objectives, the present invention adopts the following technical solution: In a first aspect, the present invention provides a materialized view refresh method, comprising: Obtain a materialized view refresh request sent by the client for the target database, parse the materialized view refresh request, and extract the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein, the refresh mode includes at least an incremental refresh mode. When the target materialized view is not in a refresh state, the pre-stored metadata of the target materialized view is obtained, and the refresh scenario of the target materialized view is verified based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is started, and the database consistency snapshot information of the independent read-write transaction is obtained. Based on the view definition information in the pre-stored metadata, the query statement is rewritten to generate an incremental refresh query statement. Based on the incremental refresh query statement and the database consistency snapshot information, target incremental data is filtered out from the materialized view log of the target database, and the target materialized view is refreshed based on the target incremental data.

[0007] In a second aspect, the present invention provides a materialized view refresh device, comprising: The parsing module is used to obtain the materialized view refresh request sent by the client for the target database, parse the materialized view refresh request, and extract the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein, the refresh mode includes at least the incremental refresh mode. The judgment module is used to obtain the pre-stored metadata of the target materialized view when the target materialized view is not in a refreshing state, and to verify the refresh scenario of the target materialized view based on the pre-stored metadata, and to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. The rewrite module is used to start an independent read-write transaction when the refresh scenario meets the execution conditions of the incremental refresh mode, obtain the database consistency snapshot information of the independent read-write transaction, and perform query statement rewriting based on the view definition information in the pre-stored metadata to generate an incremental refresh query statement. The filtering module is used to filter out target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refresh the target materialized view based on the target incremental data.

[0008] Thirdly, the present invention provides a computer device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the materialized view refresh method as described above.

[0009] Fourthly, the present invention provides a computer-readable storage medium storing a computer program, wherein the computer program, when executed by a processor, implements the materialized view refresh method as described above.

[0010] Compared to existing technologies, this invention provides a materialized view refresh method, apparatus, computer device, and computer-readable storage medium, comprising: acquiring a materialized view refresh request for a target database sent by a client; parsing the materialized view refresh request; extracting the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein the refresh mode includes at least an incremental refresh mode; when the target materialized view is not in a refresh state, acquiring pre-stored metadata of the target materialized view, and verifying the refresh scenario of the target materialized view based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode; when the refresh scenario meets the execution conditions of the incremental refresh mode, initiating an independent read-write transaction, acquiring the database consistency snapshot information of the independent read-write transaction, rewriting the query statement based on the view definition information in the pre-stored metadata to generate an incremental refresh query statement; and filtering target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refreshing the target materialized view based on the target incremental data. Thus, the present invention can improve the data accuracy and execution stability of materialized view refresh. Attached Figure Description

[0011] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0012] Figure 1 This is a schematic diagram illustrating the application environment of a materialized view refresh method provided in an embodiment of the present invention.

[0013] Figure 2 This is a flowchart illustrating a materialized view refresh method according to an embodiment of the present invention.

[0014] Figure 3 This is a schematic diagram of the program module of a materialized view refresh device provided in an embodiment of the present invention.

[0015] Figure 4 This is a schematic diagram of the structure of a computer device provided in an embodiment of the present invention.

[0016] Figure 5 This is another structural schematic diagram of a computer device provided in an embodiment of the present invention. Detailed Implementation

[0017] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0018] It should be understood that, when used in this specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or collections thereof.

[0019] It should also be understood that the term “and / or” as used in this specification and the appended claims refers to any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0020] As used in this specification and the appended claims, the term "if" may be interpreted, depending on the context, as "when," "once," "in response to determination," or "in response to detection." Similarly, the phrase "if determined" or "if [the described condition or event] is detected" may be interpreted, depending on the context, as meaning "once determined," "in response to determination," "once [the described condition or event] is detected," or "in response to detection of [the described condition or event]."

[0021] Furthermore, in the description of this invention and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0022] References to "one embodiment" or "some embodiments" as described in this specification mean that one or more embodiments of the invention include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized.

[0023] It should be understood that the sequence number of each step in the following embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0024] To illustrate the technical solution of the present invention, specific embodiments are described below.

[0025] An embodiment of the present invention provides a materialized view refresh method, which can be applied to, for example... Figure 1In the application environment shown, the client and server communicate via a network. The client includes, but is not limited to, handheld computers, desktop computers, laptops, ultra-mobile personal computers (UMPCs), netbooks, cloud computing devices, and personal digital assistants (PDAs). The server can be a standalone server or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms.

[0026] Please see Figure 2 An embodiment of the present invention provides a materialized view refresh method, wherein the method includes the following steps: S100. Obtain a materialized view refresh request for the target database sent by the client, parse the materialized view refresh request, and extract the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein, the refresh mode includes at least an incremental refresh mode. S200. When the target materialized view is not in a refreshing state, obtain the pre-stored metadata of the target materialized view, and verify the refresh scenario of the target materialized view based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. S300. When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is started, and the database consistency snapshot information of the independent read-write transaction is obtained. Based on the view definition information in the pre-stored metadata, the query statement is rewritten to generate an incremental refresh query statement. S400. Based on the incremental refresh query statement and the database consistency snapshot information, target incremental data is filtered out from the materialized view log of the target database, and the target materialized view is refreshed based on the target incremental data.

[0027] In specific implementation, the materialized view refresh method of this embodiment first receives and parses the materialized view refresh request sent by the client, extracts the target materialized view to be refreshed and the corresponding refresh mode, and reads the pre-stored metadata of the target materialized view if there is no ongoing refresh task. Based on the pre-stored metadata, the refresh scenario is verified before determining whether the execution conditions for incremental refresh mode are met, thus avoiding incorrect initiation of the incremental refresh process in scenarios where incremental refresh conditions are not met. When the refresh scenario meets the execution conditions for incremental refresh mode, an independent read / write transaction is initiated and the corresponding database consistency snapshot information is obtained. The query statement is completed based on the view definition information in the pre-stored metadata. The incremental refresh query statement is rewritten, and the time boundary for data reading is defined by independent read-write transactions and database consistency snapshot information. Then, the incremental refresh query statement and database consistency snapshot information are combined to filter the target incremental data from the materialized view log for refresh processing. This can avoid the risk of data tampering caused by multiple tasks concurrently refreshing the same target materialized view, and can also rely on the database consistency snapshot information to constrain the log reading range, preventing the problems of missed or duplicate incremental data processing. At the same time, the query statement rewriting is based on the view definition information, ensuring that the incremental refresh query statement is compatible with the actual definition of the target materialized view, thereby improving the overall data correctness and execution stability of materialized view refresh.

[0028] Further, in one embodiment, the materialized view refresh method, wherein obtaining the materialized view refresh request sent by the client for the target database, parsing the materialized view refresh request, and extracting the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result, specifically includes: Receive a materialized view refresh request for the target database sent by the client, perform compliance verification on the materialized view refresh request, and parse the materialized view refresh request that passes the verification according to the preset parsing rules to obtain the request parsing result; From the request parsing result, extract the materialized view identifier corresponding to the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view; wherein, the refresh mode also includes the full refresh mode.

[0029] Further, in the materialized view refresh method, when the target materialized view is not in a refresh state, obtaining the pre-stored metadata of the target materialized view, and verifying the refresh scenario of the target materialized view based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode, specifically includes: Based on the materialized view identifier, verify whether the target materialized view exists in the target database. If the target materialized view exists in the target database, verify whether the target materialized view is in a refreshing state. When the target materialized view is not in a refresh state, the pre-stored metadata corresponding to the target materialized view is obtained, and the refresh scenario of the target materialized view is verified based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. When the refresh scenario does not meet the execution conditions of the incremental refresh mode, the full refresh mode is used to refresh the target materialized view.

[0030] In specific implementation, this embodiment first performs compliance verification upon receiving a materialized view refresh request, and then uses preset parsing rules to complete request parsing, extracting the materialized view identifier and refresh mode. Simultaneously, it incorporates the full refresh mode into the refresh mode options, filtering out illegal and incomplete refresh requests and preventing invalid requests from entering the subsequent refresh process. It verifies whether the target materialized view actually exists by checking the materialized view identifier, and then further determines whether the target materialized view is in a refresh state. This avoids abnormal scenarios such as refreshing non-existent objects and multiple tasks concurrently refreshing the same target materialized view. After reading pre-stored metadata to verify the refresh scenario, if the refresh scenario does not meet the execution conditions of the incremental refresh mode, it automatically switches to the full refresh mode to perform the refresh processing, avoiding data anomalies caused by forcibly executing incremental refresh, and achieving adaptive degradation of the refresh mode. This further enhances the fault tolerance and execution reliability of the materialized view refresh process.

[0031] The specific implementation process of this embodiment is roughly as follows: 1. Receive materialized view refresh requests for the target database sent by the client and perform a complete compliance check on the received raw refresh requests. Compliance checks include request message integrity checks, request field parameter validity checks, and request message format standardization checks, identifying various abnormal requests such as missing messages, empty key fields, out-of-bounds parameter values, message formats that do not conform to protocol specifications, and messages that have been tampered with. For refresh requests that fail the check, the request is discarded directly, an error message is returned, and it is not further processed; only materialized view refresh requests that pass the compliance check are allowed to enter the subsequent parsing stage, blocking the impact of abnormal requests on the target database at the request entry point and preventing invalid tasks from consuming the target database's computing resources.

[0032] 2. For materialized view refresh requests that pass compliance verification, pre-configured parsing rules are invoked to perform structured parsing on the request message, removing irrelevant extended and redundant fields, and outputting a structured request parsing result. Two types of key parameters are extracted from the request parsing result: the first is the materialized view identifier corresponding to the target materialized view to be refreshed, which is an identification field used within the target database to uniquely locate the materialized view object; the second is the refresh mode specified by the client, which supports both incremental and full refresh modes. The extracted materialized view identifier and refresh mode are passed down as global input parameters for this refresh task.

[0033] 3. Using the extracted materialized view identifier, initiate an object query to the target database to verify whether the target materialized view corresponding to the materialized view identifier has already been created in the target database. If the corresponding target materialized view cannot be found in the target database, it means that the object to be refreshed does not exist, and the current refresh task is terminated directly, returning an error message indicating that the object does not exist to the client. If the query confirms that the target materialized view does exist, then further read the refresh status flag field maintained internally by the target materialized view, and determine whether the target materialized view is currently in a refresh state based on the content of the flag field. If the target materialized view is in a refresh state, it means that a refresh task is already being executed. To prevent data conflicts caused by multiple tasks concurrently modifying the same materialized view, the current refresh request is directly rejected. If the target materialized view is not in a refresh state, then continue to execute the subsequent processing steps.

[0034] 4. After confirming that the target materialized view object is valid and that there are no currently running refresh tasks, read the complete pre-stored metadata of the target materialized view from the metadata storage area of ​​the target database. The pre-stored metadata includes the view definition information of the target materialized view, the availability status of the materialized view log, the snapshot point record of the last refresh completion, the base table structure change record, and other status information. Based on all the read pre-stored metadata, perform refresh scenario verification, checking each of the preconditions required by the incremental refresh mode, such as whether the materialized view log is complete and available, whether the historical snapshot point is valid, and whether the base table has undergone structural changes that do not support incremental refresh, to comprehensively determine whether the current refresh scenario meets the execution conditions of the incremental refresh mode.

[0035] 5. Execute branch processing logic based on the refresh scenario verification result. When the current refresh scenario meets the execution conditions for incremental refresh mode, the process flows to subsequent independent read / write transaction initiation, query statement rewriting, and other incremental refresh business processes, using a lower-overhead incremental method to update the materialized view data. When the current refresh scenario does not meet the execution conditions for incremental refresh mode, the incremental refresh process is no longer attempted. Instead, the refresh mode is automatically downgraded, switching to full refresh mode to refresh the target materialized view. Full recalculation ensures that the data stored in the materialized view is consistent with the actual data in the base table, avoiding data omissions and data corruption caused by forcibly running incremental refresh in scenarios where conditions are not met.

[0036] Further, in one embodiment, the materialized view refresh method, wherein when the refresh scenario meets the execution conditions of the incremental refresh mode, initiates an independent read-write transaction, obtains the database consistency snapshot information of the independent read-write transaction, and performs query statement rewriting based on the view definition information in the pre-stored metadata to generate an incremental refresh query statement, specifically includes: When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is started after configuring the transaction isolation level, and the database consistency snapshot information corresponding to the independent read-write transaction is obtained. The view definition information in the pre-stored metadata is parsed to identify the view type corresponding to the target materialized view. The query statement is rewritten according to the view type to generate an incremental refresh query statement that can be executed by the target database. The view type includes aggregated views and non-aggregated views.

[0037] Furthermore, the materialized view refresh method, wherein the step of filtering target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refreshing the target materialized view based on the target incremental data, specifically includes: The incremental refresh query statement is invoked, and the materialized view log of the target database is preprocessed with snapshot filtering based on the database consistency snapshot information to filter out log records that exceed the visibility range corresponding to the database consistency snapshot information, thereby obtaining candidate incremental log data. The candidate incremental log data is subjected to data compliance verification and data conflict detection. Invalid, corrupt, and conflicting log records are removed to obtain the target incremental data. Using the target incremental data, the target materialized view is refreshed to generate a materialized view refresh result, which is then sent to the client.

[0038] Furthermore, the materialized view refresh method, wherein the step of refreshing the target materialized view using the target incremental data to generate a materialized view refresh result and sending the materialized view refresh result to the client, specifically includes: Obtain the view type corresponding to the identified target materialized view, and use the target incremental data to update the target materialized view according to the data change logic matching the view type to obtain the original refresh execution result; The original refresh execution result is standardized and encapsulated to generate a materialized view refresh result, which is then sent to the client.

[0039] Furthermore, the materialized view refresh method, after obtaining the identified view type corresponding to the target materialized view, and using the target incremental data to perform data update processing on the target materialized view according to the data change logic matching the view type to obtain the original refresh execution result, further includes: Using the obtained original refresh execution result, the pre-stored metadata corresponding to the target materialized view is updated.

[0040] In specific implementation, when the incremental refresh mode execution conditions are met in the refresh scenario, this embodiment enables independent read / write transactions by configuring the transaction isolation level and obtaining the corresponding database consistency snapshot information. It parses the view definition information in the pre-stored metadata to identify the view type (aggregated or non-aggregated view). Based on different view types, it rewrites the query statements to generate incremental refresh query statements adapted to the target database. This allows for the generation of appropriate query logic for aggregated and non-aggregated views, avoiding calculation errors caused by unified rewriting logic. Subsequently, it combines the incremental refresh query statements and database consistency snapshot information to perform snapshot filtering preprocessing on the materialized view logs, filtering out log records outside the snapshot visibility range to obtain candidate incremental log data. Then, it performs data compliance verification and data conflict detection on the candidate incremental log data, eliminating invalid and corrupted data. 1. Conflicting log records are used to obtain target incremental data. Snapshot boundary control is used to avoid issues such as missed or duplicate processing of incremental logs, while filtering abnormal logs to reduce the risk of dirty data entering the refresh process. During the refresh execution phase, the identified view type is reused, and data change logic matching the view type is used to complete the data update of the target materialized view to obtain the original refresh execution result. The original refresh execution result is standardized and encapsulated before being returned to the client as the materialized view refresh result. Furthermore, the pre-stored metadata corresponding to the target materialized view is updated based on the original refresh execution result, so that the metadata record is synchronized with the latest refresh status. Thus, from multiple levels such as transaction snapshot control, view type differentiation processing, log filtering and verification, and metadata status synchronization, the data correctness of incremental refresh of materialized views is further improved, and the execution stability and traceability of the entire refresh process are enhanced.

[0041] The specific implementation process of this embodiment is roughly as follows: 1. After determining that the refresh scenario meets the execution conditions for incremental refresh mode, the system first configures a transaction isolation level adapted to the incremental refresh business, starts an independent read-write transaction, and obtains the corresponding database consistency snapshot information (such as ReadView snapshot information) through this independent read-write transaction. This database consistency snapshot information fixes the data read view of this incremental refresh task, limiting all data that the transaction can see to the data committed at the time of snapshot generation and before, and shielding the base table changes that occur after snapshot generation. Next, the system reads and parses the view definition information stored internally in the pre-stored metadata, parses the aggregate functions, grouping fields, and query statement structure in the view definition information, identifies the view type of the target materialized view, and distinguishes between aggregate views and non-aggregate views; according to the identified view type, it performs differentiated query statement rewriting processing, generates incremental refresh query statements adapted to the grouping aggregation recalculation logic for aggregate views, and generates incremental refresh query statements adapted to the row-level change capture logic for non-aggregate views, and finally outputs incremental refresh query statements that can be directly executed in the target database, avoiding aggregation calculation errors caused by using a unified query logic.

[0042] 2. The generated incremental refresh query statement is invoked to read the materialized view logs of the target database. Combined with the database consistency snapshot information, snapshot filtering preprocessing is performed to filter the transaction commit times of the materialized view log records. Log records with commit times later than the corresponding time points in the database consistency snapshot information are all removed, retaining only the change logs within the snapshot's visibility range, thus obtaining candidate incremental log data. Subsequently, data compliance verification and data conflict detection are performed on each candidate incremental log record. Data compliance verification checks whether log records are complete, whether key fields are missing, and whether field values ​​are valid, removing damaged, incomplete, or invalid log records. Data conflict detection identifies concurrent scenarios such as multiple sequentially modified records for the same data row and primary key conflicts, marking and removing conflicting log records that would cause update anomalies. After two rounds of filtering, reliable target incremental data is obtained, preventing dirty data from flowing into the materialized view update process from the log layer.

[0043] 3. Reuse the view types identified in the previous parsing of view definition information, and select the data change logic that matches the view type to process the target incremental data. When the target materialized view is an aggregated view, re-aggregate and calculate the corresponding groups based on the target incremental data, and update the aggregation results of the corresponding groups; when the target materialized view is a non-aggregated view, directly perform row-level insert, row-level update, and row-level delete operations based on the target incremental data to complete the modification of the data inside the target materialized view. After the materialized view update operation is completed, the original refresh execution result is generated. The original refresh execution result includes the total number of log entries processed in this refresh, the number of successfully processed records, the number of failed records, the execution time, exception error information, the snapshot position corresponding to this transaction, and other underlying original execution information.

[0044] 4. The original refresh execution results are standardized and encapsulated according to a preset output protocol, forming a structured materialized view refresh result. This materialized view refresh result provides refresh success or failure indicators, statistical indicators, and anomaly descriptions. The encapsulated materialized view refresh result is pushed to the client, allowing the client to obtain the execution status of this incremental refresh. After completing the materialized view data update and obtaining the original refresh execution result, the snapshot position, refresh status, and processing statistics carried in the original refresh execution result are read. The pre-stored metadata corresponding to the target materialized view is updated, and the snapshot position, task execution status, and number of processed records for this refresh are persistently written into the pre-stored metadata, completing metadata status synchronization. This provides reliable metadata input for the next materialized view refresh task and also achieves persistent retention of refresh task execution information, facilitating fault diagnosis and task traceability.

[0045] As can be seen from the above method embodiments, the materialized view refresh method provided by the present invention includes: obtaining a materialized view refresh request for a target database sent by a client; parsing the materialized view refresh request; extracting the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein the refresh mode includes at least an incremental refresh mode; when the target materialized view is not in a refresh state, obtaining the pre-stored metadata of the target materialized view, and verifying the refresh scenario of the target materialized view based on the pre-stored metadata, determining whether the refresh scenario meets the execution conditions of the incremental refresh mode; when the refresh scenario meets the execution conditions of the incremental refresh mode, starting an independent read-write transaction, obtaining the database consistency snapshot information of the independent read-write transaction, performing query statement rewriting based on the view definition information in the pre-stored metadata, and generating an incremental refresh query statement; based on the incremental refresh query statement and the database consistency snapshot information, filtering target incremental data from the materialized view log of the target database, and refreshing the target materialized view based on the target incremental data. Therefore, the method of the present invention can improve the data accuracy and execution stability of materialized view refresh.

[0046] It should be understood that although this application provides the method operation steps as described in the embodiments or flowcharts, conventional or non-inventive labor may include more or fewer operation steps, and these operation steps are not necessarily executed sequentially according to the order of the embodiments or flowcharts. The order of steps listed in the embodiments or flowcharts is merely one way of executing many steps and does not represent the only execution order. It should be noted that there is no necessary sequential order between the above steps. Those skilled in the art can understand from the description of the embodiments of the present invention that the above steps may have different execution orders in different embodiments, that is, they may be executed in parallel or in exchange, etc. Moreover, at least some steps in the embodiments or flowcharts may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but may be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but may be executed in turn, alternately, or synchronously with other steps or at least a part of the sub-steps or stages of other steps.

[0047] Based on the above method embodiments, please refer to Figure 3 Another embodiment of the present invention also provides a materialized view refresh device, wherein the device includes: The parsing module 11 is used to obtain a materialized view refresh request for a target database sent by the client, parse the materialized view refresh request, and extract the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein, the refresh mode includes at least an incremental refresh mode. The judgment module 12 is used to obtain the pre-stored metadata of the target materialized view when the target materialized view is not in a refreshing state, and to verify the refresh scenario of the target materialized view based on the pre-stored metadata, and to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. The rewrite module 13 is used to start an independent read-write transaction when the refresh scenario meets the execution conditions of the incremental refresh mode, obtain the database consistency snapshot information of the independent read-write transaction, and perform query statement rewriting based on the view definition information in the pre-stored metadata to generate an incremental refresh query statement. The filtering module 14 is used to filter out target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refresh the target materialized view based on the target incremental data.

[0048] Further, in one embodiment, the materialized view refresh device, wherein obtaining the materialized view refresh request sent by the client for the target database, parsing the materialized view refresh request, and extracting the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result, specifically includes: Receive a materialized view refresh request for the target database sent by the client, perform compliance verification on the materialized view refresh request, and parse the materialized view refresh request that passes the verification according to the preset parsing rules to obtain the request parsing result; From the request parsing result, extract the materialized view identifier corresponding to the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view; wherein, the refresh mode also includes the full refresh mode.

[0049] Further, in the materialized view refresh device, when the target materialized view is not in a refresh state, acquiring pre-stored metadata of the target materialized view and verifying the refresh scenario of the target materialized view based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode specifically includes: Based on the materialized view identifier, verify whether the target materialized view exists in the target database. If the target materialized view exists in the target database, verify whether the target materialized view is in a refreshing state. When the target materialized view is not in a refresh state, the pre-stored metadata corresponding to the target materialized view is obtained, and the refresh scenario of the target materialized view is verified based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. When the refresh scenario does not meet the execution conditions of the incremental refresh mode, the full refresh mode is used to refresh the target materialized view.

[0050] Further, in one embodiment, the materialized view refresh device, wherein when the refresh scenario meets the execution conditions of the incremental refresh mode, initiates an independent read-write transaction, obtains the database consistency snapshot information of the independent read-write transaction, and performs query statement rewriting based on the view definition information in the pre-stored metadata to generate an incremental refresh query statement, specifically includes: When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is started after configuring the transaction isolation level, and the database consistency snapshot information corresponding to the independent read-write transaction is obtained. The view definition information in the pre-stored metadata is parsed to identify the view type corresponding to the target materialized view. The query statement is rewritten according to the view type to generate an incremental refresh query statement that can be executed by the target database. The view type includes aggregated views and non-aggregated views.

[0051] Further, in the materialized view refresh device, the step of filtering target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refreshing the target materialized view based on the target incremental data, specifically includes: The incremental refresh query statement is invoked, and the materialized view log of the target database is preprocessed with snapshot filtering based on the database consistency snapshot information to filter out log records that exceed the visibility range corresponding to the database consistency snapshot information, thereby obtaining candidate incremental log data. The candidate incremental log data is subjected to data compliance verification and data conflict detection. Invalid, corrupt, and conflicting log records are removed to obtain the target incremental data. Using the target incremental data, the target materialized view is refreshed to generate a materialized view refresh result, which is then sent to the client.

[0052] Furthermore, the materialized view refresh device, wherein the step of refreshing the target materialized view using the target incremental data, generating a materialized view refresh result, and sending the materialized view refresh result to the client, specifically includes: Obtain the view type corresponding to the identified target materialized view, and use the target incremental data to update the target materialized view according to the data change logic matching the view type to obtain the original refresh execution result; The original refresh execution result is standardized and encapsulated to generate a materialized view refresh result, which is then sent to the client.

[0053] Furthermore, the materialized view refresh device, after acquiring the identified view type corresponding to the target materialized view, and using the target incremental data to perform data update processing on the target materialized view according to data change logic matching the view type to obtain the original refresh execution result, further includes: Using the obtained original refresh execution result, the pre-stored metadata corresponding to the target materialized view is updated.

[0054] It should be noted that, in the device embodiments of the present invention, the information interaction and execution process between the above modules are based on the same concept as in the method embodiments of the present invention. For details on their specific functions and the resulting technical effects, please refer to the aforementioned method embodiments section, which will not be repeated here.

[0055] Based on the above method embodiments, another embodiment of the present invention also provides a computer device, which can be a server, and its internal structure diagram can be as follows. Figure 4 As shown. The computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computing and control capabilities. The memory includes a non-volatile storage medium and internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The network interface is used to communicate with external terminals via a network connection. When the computer program is executed by the processor, it implements the functions or steps of the materialized view refresh method server-side as described in any of the above method embodiments.

[0056] Based on the above method embodiments, another embodiment of the present invention also provides a computer device, which can be a client, and its internal structure diagram can be as follows. Figure 5As shown. The computer device includes a processor, memory, network interface, display screen, and input device connected via a system bus. The processor provides computing and control capabilities. The memory includes a non-volatile storage medium and internal memory. The non-volatile storage medium stores an operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The network interface is used to communicate with external terminals via a network connection. When executed by the processor, the computer program implements the functions or steps of the materialized view refresh method on the client side as described in any of the above method embodiments.

[0057] Those skilled in the art will understand that Figure 4 and Figure 5 The structural schematic diagram shown is only a schematic diagram of a part of the structure related to the present invention and does not constitute a limitation on the computer device on which the present invention is applied. The specific computer device may include more components than shown in the figure, or combine certain components, or have different component arrangements.

[0058] The processor referred to here can be a CPU, but it can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (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.

[0059] The memory includes readable storage media, internal memory, etc., where internal memory can be the RAM of a computer device. Internal memory provides an environment for the operation of the operating system and computer-readable instructions stored in the readable storage media. The readable storage media can be the hard drive of the computer device, or in other embodiments, it can be an external storage device of the computer device, such as a plug-in hard drive, Smart Media Card (SMC), Secure Digital (SD) card, or Flash Card. Furthermore, the memory can include both internal storage units and external storage devices of the computer device. The memory is used to store the operating system, applications, boot loader, data, and other programs, such as program code for computer programs. The memory can also be used to temporarily store data that has been output or will be output.

[0060] Based on the above method embodiments, another embodiment of the present invention provides a computer-readable storage medium storing a computer program, wherein the computer program, when executed by a processor, implements the materialized view refresh method as described in any of the above method embodiments. The computer-readable storage medium may be non-volatile or volatile.

[0061] It should be noted that the functions or steps that can be achieved by the computer-readable storage medium or computer device, and the technical effects brought about by the functions / steps, can be referred to the relevant descriptions in the foregoing method embodiments. To avoid repetition, they will not be described one by one here.

[0062] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc. The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0063] Those skilled in the art will understand that, for the sake of convenience and brevity, the embodiments of the device of the present invention are only illustrated by the division of the above-mentioned functional units and modules. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of the present invention. The specific working process of the units and modules in the above device can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here. If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium.

[0064] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.

[0065] In the embodiments provided by this invention, it should be understood that the disclosed apparatus / computer devices and methods can be implemented in other ways. For example, the apparatus / computer device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between apparatuses or units may be electrical, mechanical, or other forms.

[0066] The units described as separate components may or may not be physically separate. 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 units can be selected to achieve the purpose of this embodiment according to actual needs.

[0067] It should be noted that any AI models, software tools, or components not belonging to this company appearing in the embodiments of this application are merely illustrative examples and do not represent actual use. All user personal information involved in the embodiments of this application has been authorized (with the knowledge and consent) by the relevant parties or has been fully authorized by all parties, and the executing entity may obtain it through various legal and compliant means. The collection, storage, use, processing, transmission, provision, and disclosure of the information, data, and signals involved all comply with relevant laws and regulations and do not violate public order and good morals.

[0068] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A materialized view refresh method, characterized in that, include: Obtain a materialized view refresh request sent by the client for the target database, parse the materialized view refresh request, and extract the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein, the refresh mode includes at least an incremental refresh mode. When the target materialized view is not in a refresh state, the pre-stored metadata of the target materialized view is obtained, and the refresh scenario of the target materialized view is verified based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is started, and the database consistency snapshot information of the independent read-write transaction is obtained. Based on the view definition information in the pre-stored metadata, the query statement is rewritten to generate an incremental refresh query statement. Based on the incremental refresh query statement and the database consistency snapshot information, target incremental data is filtered out from the materialized view log of the target database, and the target materialized view is refreshed based on the target incremental data.

2. The materialized view refresh method according to claim 1, characterized in that, The process of obtaining the materialized view refresh request sent by the client for the target database, parsing the materialized view refresh request, and extracting the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result includes: Receive a materialized view refresh request for the target database sent by the client, perform compliance verification on the materialized view refresh request, and parse the materialized view refresh request that passes the verification according to the preset parsing rules to obtain the request parsing result; From the request parsing result, extract the materialized view identifier corresponding to the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view; wherein, the refresh mode also includes the full refresh mode.

3. The materialized view refresh method according to claim 2, characterized in that, When the target materialized view is not in a refresh state, the pre-stored metadata of the target materialized view is obtained, and the refresh scenario of the target materialized view is verified based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode, including: Based on the materialized view identifier, verify whether the target materialized view exists in the target database. If the target materialized view exists in the target database, verify whether the target materialized view is in a refreshing state. When the target materialized view is not in a refresh state, the pre-stored metadata corresponding to the target materialized view is obtained, and the refresh scenario of the target materialized view is verified based on the pre-stored metadata to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. When the refresh scenario does not meet the execution conditions of the incremental refresh mode, the full refresh mode is used to refresh the target materialized view.

4. The materialized view refresh method according to claim 1, characterized in that, When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is initiated, and the database consistency snapshot information of the independent read-write transaction is obtained. Based on the view definition information in the pre-stored metadata, the query statement is rewritten to generate an incremental refresh query statement, including: When the refresh scenario meets the execution conditions of the incremental refresh mode, an independent read-write transaction is started after configuring the transaction isolation level, and the database consistency snapshot information corresponding to the independent read-write transaction is obtained. The view definition information in the pre-stored metadata is parsed to identify the view type corresponding to the target materialized view. The query statement is rewritten according to the view type to generate an incremental refresh query statement that can be executed by the target database. The view type includes aggregated views and non-aggregated views.

5. The materialized view refresh method according to claim 4, characterized in that, The step of filtering target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refreshing the target materialized view based on the target incremental data, includes: The incremental refresh query statement is invoked, and the materialized view logs of the target database are preprocessed with snapshot filtering based on the database consistency snapshot information to filter out log records that exceed the visibility range corresponding to the database consistency snapshot information, thereby obtaining candidate incremental log data. The candidate incremental log data is subjected to data compliance verification and data conflict detection. Invalid, corrupt, and conflicting log records are removed to obtain the target incremental data. Using the target incremental data, the target materialized view is refreshed to generate a materialized view refresh result, which is then sent to the client.

6. The materialized view refresh method according to claim 5, characterized in that, The step of refreshing the target materialized view using the target incremental data, generating a materialized view refresh result, and sending the materialized view refresh result to the client includes: Determine the view type corresponding to the identified target materialized view, and use the target incremental data to update the target materialized view according to the data change logic matching the view type to obtain the original refresh execution result; The original refresh execution result is standardized and encapsulated to generate a materialized view refresh result, which is then sent to the client.

7. The materialized view refresh method according to claim 6, characterized in that, After determining the view type corresponding to the identified target materialized view, and using the target incremental data to update the target materialized view according to the data change logic matching the view type, to obtain the original refresh execution result, the process further includes: Using the original refresh execution result, the pre-stored metadata corresponding to the target materialized view is updated.

8. A materialized view refresh device, characterized in that, include: The parsing module is used to obtain the materialized view refresh request sent by the client for the target database, parse the materialized view refresh request, and extract the target materialized view to be refreshed in the target database and the refresh mode associated with the target materialized view from the request parsing result; wherein, the refresh mode includes at least the incremental refresh mode. The judgment module is used to obtain the pre-stored metadata of the target materialized view when the target materialized view is not in a refreshing state, and to verify the refresh scenario of the target materialized view based on the pre-stored metadata, and to determine whether the refresh scenario meets the execution conditions of the incremental refresh mode. The rewrite module is used to start an independent read-write transaction when the refresh scenario meets the execution conditions of the incremental refresh mode, obtain the database consistency snapshot information of the independent read-write transaction, and perform query statement rewriting based on the view definition information in the pre-stored metadata to generate an incremental refresh query statement. The filtering module is used to filter out target incremental data from the materialized view log of the target database based on the incremental refresh query statement and the database consistency snapshot information, and refresh the target materialized view based on the target incremental data.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the materialized view refresh method as described in any one of claims 1-7.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the materialized view refresh method as described in any one of claims 1-7.