Materialized view processing method and device

Abstract

The invention discloses a materialized view processing method and device, and relates to the technical field of computers. A specific embodiment of the method comprises the following steps: counting a historical slow query log of a service system in a preset time period, and determining a query statement which does not hit a cache according to the historical slow query log; counting query information of each query statement in the preset time period to screen out query statements of which the query information meets a preset requirement to obtain a query statement set; and determining a public query statement of the query statement set, obtaining offline data corresponding to the public query statement from the data lake, and performing materialization processing on the offline data to obtain a materialized view. According to the embodiment, the materialized view can be automatically created, and the problem that the traditional materialized view needs to be created strongly depending on manual analysis is solved, so that access to a data lake is remarkably reduced, and the query performance and user experience of a service system are greatly improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular, to a method and apparatus for processing materialized views. Background Art

[0002] With the rise of the Internet industry and the advent of the DT (Data Technology) era, the value of data itself has been further amplified. Enterprises mine and analyze massive amounts of data to guide refined operations and decision-making. However, the data tables generated by traditional big data offline processing methods are difficult to meet the flexible ad-hoc analysis needs of users. Users often need to add complex functions and predicates to the offline tables for data cleaning, which leads to query performance bottlenecks.

[0003] To solve this problem, some databases in the industry (such as OLAP) have proposed the concept of materialized views (MaterializedView, abbreviated as MV). Different from traditional logical views, materialized views transparently rewrite the query plan during the query stage through pre-computation and early processing to achieve query acceleration for users. Although mainstream databases provide related capabilities for materialized views, this relies on experienced personnel to create and does not achieve the automatic creation of materialized views. Summary of the Invention

[0004] In view of this, embodiments of the present invention provide a method and apparatus for processing materialized views, which can at least solve the phenomenon that the creation of materialized views in the prior art depends on manual analysis.

[0005] To achieve the above object, according to one aspect of the embodiments of the present invention, a method for processing materialized views is provided, including:

[0006] Statistical business systems in a preset period of historical slow query logs, according to the historical slow query logs, determine the query statements that do not hit the cache;

[0007] Statistical information for each query statement in the preset period of query, to filter out the query information that meets the preset requirements of the query statement, to obtain a set of query statements;

[0008] Determine the common query statements of the query statement set, obtain the offline data corresponding to the common query statements from the data lake, and perform materialization processing on the offline data to obtain a materialized view.

[0009] Optionally, the statistical information for each query statement in the preset period of query, to filter out the query information that meets the preset requirements of the query statement, includes:

[0010] For each query statement, statistical query times in the preset period and the query time for each time, and then obtain the average query time;

[0011] Filter out the query statements whose query times are greater than or equal to a preset first value and whose average query time-consuming is greater than or equal to a preset second value.

[0012] Optionally, obtaining the set of query statements further includes:

[0013] For each query statement that meets the preset requirements, generate a logical execution plan tree, and based on the combination of text and structure rewriting, filter out the query statements that can be rewritten in materialized form.

[0014] Optionally, the method further includes:

[0015] Determine the query statements for creating materialized views, determine the sub-query statements of the query statements, and iterate through each sub-query statement to calculate the eigenvalue;

[0016] In response to the eigenvalues of the sub-query statements of multiple materialized views being consistent, determine the sub-query statement as the reusable statement for the multiple materialized views;

[0017] Obtain the offline data corresponding to the reusable statement from the data lake, and perform materialized processing on the offline data to obtain the materialized view.

[0018] Optionally, the method further includes:

[0019] Count the query times of each sub-query statement;

[0020] Filter out the reusable statements whose query times are greater than or equal to a preset third value.

[0021] Optionally, after obtaining the materialized view, the method further includes:

[0022] According to the database table name corresponding to the offline data, query in the message queue whether there is a corresponding data change message; wherein, the data lake receives the processed database table transmitted by the offline processing engine, and then sends a data change message about the database table to the message queue;

[0023] In response to the query result being that there is one, according to the data change message, obtain the offline data from the correspondingly changed database table in the data lake to update the metadata and data of the corresponding materialized view.

[0024] Optionally, the materialized view is stored in the query engine, and the method further includes:

[0025] According to the data change message, obtain the first metadata of the corresponding database table from the data lake;

[0026] Obtain the second metadata of the materialized view corresponding to the offline data;

[0027] Mark the materialized view as invalid in response to the first metadata being greater than the second metadata; mark the materialized view as valid after updating the materialized view;

[0028] During the period when the materialized view is marked as invalid, in response to receiving a query request, obtain the data that meets the query request from the data lake through the query engine and return it.

[0029] Optionally, the method further includes:

[0030] Count the number of accesses of each materialized view in a historical time period, mark the materialized view with the number of accesses less than or equal to a preset fourth value as invalid, and stop the refresh operation.

[0031] Optionally, the method further includes:

[0032] Receive a query request, perform lexical analysis and syntactic analysis on the query request to generate a logical query tree, and then match the materialized view according to the logical query tree;

[0033] In response to the matching result being non-existent, obtain the data that meets the query request from the data lake through the query engine and return it;

[0034] In response to the matching result being multiple materialized views, input the multiple materialized views into the cost evaluation model of the query engine to obtain the evaluation score of each materialized view;

[0035] Take the materialized view with the highest evaluation score as the target materialized view, and convert the logic of querying the data lake in the query request into the logic of querying the target materialized view;

[0036] Determine the data that meets the query request from the target materialized view according to the changed logic and return it.

[0037] Optionally, the matching of the materialized view according to the logical query tree includes:

[0038] Based on text-based matching rewriting, determine all query statements and sub-query statements that match the logical query tree;

[0039] Match all the matching query statements and sub-query statements with the creation statements of each materialized view;

[0040] In response to the matching result being existent, obtain all the matching materialized views;

[0041] In response to the matching result being non-existent, based on structure-based matching rewriting, compare the logical query tree and the logical execution plan tree of each materialized view to determine all the materialized views with consistent logic.

[0042] Optionally, the method further includes:

[0043] Based on text-based matching rewriting, replace all time-related functions in the query statements and sub-query statements with dynamic time functions.

[0044] Optionally, the method further includes:

[0045] Statistical matching rewrite times, and in response to the number of matching rewrite times reaching a preset number threshold, stop the matching rewrite operation, and obtain and return data that meets the query request from the data lake through the query engine.

[0046] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a materialized view processing device, including:

[0047] A statistical module for statistically analyzing the historical slow query logs of the business system in a preset time period, and determining query statements that miss the cache according to the historical slow query logs;

[0048] A screening module for statistically analyzing the query information of each query statement in the preset time period to screen out query statements whose query information meets the preset requirements, and obtaining a query statement set;

[0049] A materialization module for determining the common query statements of the query statement set, obtaining the offline data corresponding to the common query statements from the data lake, and performing materialization processing on the offline data to obtain a materialized view.

[0050] Optionally, the screening module is used for:

[0051] For each query statement, statistically analyze the number of query times and the query time for each time in the preset time period, and then obtain the average query time;

[0052] Screen out query statements whose number of query times is greater than or equal to a preset first value and whose average query time is greater than or equal to a preset second value.

[0053] Optionally, the screening module is further used for:

[0054] For each query statement that meets the preset requirements, generate a logical execution plan tree, and screen out query statements that can be materially rewritten based on a combination of text and structure rewriting.

[0055] Optionally, the device further includes a multi-level materialized view module for:

[0056] Determine the query statements for creating the materialized view, determine the sub-query statements of the query statements, and iterate each sub-query statement to calculate the eigenvalue;

[0057] In response to the eigenvalues of the sub-query statements of multiple materialized views being consistent, determine that the sub-query statement is a reusable statement for the multiple materialized views;

[0058] Obtain offline data corresponding to the reuse statement from the data lake, and perform materialization processing on the offline data to obtain a materialized view.

[0059] Optionally, the multi-level materialized view module is further configured to:

[0060] Count the query times of each sub-query statement;

[0061] Filter out the reuse statements whose query times are greater than or equal to a preset third value.

[0062] Optionally, the device further includes a refresh management module, configured to:

[0063] Query whether there is a corresponding data change message in the message queue according to the database table name corresponding to the offline data; wherein, the data lake receives the processed database table transmitted by the offline processing engine, and then sends a data change message about the database table to the message queue;

[0064] In response to the query result being that there is a data change message, obtain the offline data from the correspondingly changed database table in the data lake to update the metadata and data of the corresponding materialized view.

[0065] Optionally, the materialized view is stored in the query engine, and the refresh management module is further configured to:

[0066] Obtain the first metadata of the corresponding database table from the data lake according to the data change message;

[0067] Obtain the second metadata of the materialized view corresponding to the offline data;

[0068] In response to the first metadata being greater than the second metadata, mark the materialized view as invalid; after updating the materialized view, mark the materialized view as valid;

[0069] During the period when the materialized view is marked as invalid, in response to receiving a query request, obtain the data that meets the query request from the data lake through the query engine and return it.

[0070] Optionally, the device further includes a materialized view management module, configured to:

[0071] Count the access times of each materialized view in the historical time period, mark the materialized views with access times less than or equal to a preset fourth value as invalid, and stop the refresh operation.

[0072] Optionally, the device further includes a matching and rewriting module, configured to:

[0073] Receive a query request, perform lexical analysis and syntactic analysis on the query request to generate a logical query tree, and then match the materialized view according to the logical query tree;

[0074] In response to the matching result being non - existent, obtain data that meets the query request from the data lake through the query engine and return it;

[0075] In response to the matching result being multiple materialized views, input the multiple materialized views into the cost evaluation model of the query engine to obtain the evaluation score of each materialized view;

[0076] Take the materialized view with the highest evaluation score as the target materialized view, and convert the logic of querying the data lake in the query request into the logic of querying the target materialized view;

[0077] Determine the data that meets the query request from the target materialized view through the changed logic and return it.

[0078] Optionally, the matching and rewriting module is used for:

[0079] Based on text - based matching and rewriting, determine all query statements and sub - query statements that match the logical query tree;

[0080] Match all the query statements and sub - query statements that match with the creation statements of each materialized view;

[0081] In response to the matching result being existent, obtain all the matching materialized views;

[0082] In response to the matching result being non - existent, based on structure - based matching and rewriting, compare the logical query tree and the logical execution plan tree of each materialized view to determine all the materialized views with consistent logic.

[0083] Optionally, the matching and rewriting module is further used for:

[0084] Based on text - based matching and rewriting, replace the time - related functions in all query statements and sub - query statements with dynamic time functions.

[0085] Optionally, the matching and rewriting module is further used for:

[0086] Count the number of matching and rewriting times. In response to the number of matching and rewriting times reaching the preset number threshold, stop the matching and rewriting operation, obtain data that meets the query request from the data lake through the query engine and return it.

[0087] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided an electronic device for processing materialized views.

[0088] The electronic device according to the embodiments of the present invention includes: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement any of the above - described materialized view processing methods.

[0089] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a computer-readable medium having a computer program stored thereon, and when the program is executed by a processor, the materialized view processing method described above is implemented.

[0090] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a computing program product. A computing program product according to an embodiment of the present invention includes a computer program, and when the program is executed by a processor, the materialized view processing method provided by the embodiments of the present invention is implemented.

[0091] According to the solution provided by the present invention, one embodiment of the above invention has the following advantages or beneficial effects: It can solve the problem that traditional materialized views strongly rely on manual analysis and creation. By automatically identifying query statements that miss the cache and meet preset requirements, extracting the common logical parts for these query statements to create materialized views, significantly reducing the access to the data lake, improving the query response speed, and reducing the resource consumption of the overall system, thereby greatly improving the query performance and user experience of the business system.

[0092] The further effects of the above non-conventional optional ways will be described in conjunction with specific embodiments below. BRIEF DESCRIPTION OF THE DRAWINGS

[0093] The drawings are used to better understand the present invention and do not constitute an improper limitation to the present invention. Among them:

[0094] Figure 1 is a schematic diagram of the main process of a materialized view processing method according to an embodiment of the present invention;

[0095] Figure 2 is a schematic diagram of the process of an optional materialized view processing method according to an embodiment of the present invention;

[0096] Figure 3 is a schematic diagram of the process of another optional materialized view processing method according to an embodiment of the present invention;

[0097] Figure 4 is a schematic diagram of the process of another optional materialized view processing method according to an embodiment of the present invention;

[0098] Figure 5 is a schematic diagram of the process of another optional materialized view processing method according to an embodiment of the present invention;

[0099] Figure 6 is a schematic diagram of the process of a specific materialized view processing method according to an embodiment of the present invention;

[0100] Figure 7It is a schematic diagram of the main modules of a materialized view processing device according to an embodiment of the present invention;

[0101] Figure 8 It is an exemplary system architecture diagram to which an embodiment of the present invention can be applied;

[0102] Figure 9 It is a schematic diagram of the structure of a computer system of a mobile device or a server suitable for implementing an embodiment of the present invention. Detailed implementation manners

[0103] The following describes exemplary embodiments of the present invention with reference to the accompanying drawings. Various details of the embodiments of the present invention are included to facilitate understanding, and they should be considered merely exemplary. Therefore, those of ordinary skill in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for the sake of clarity and conciseness, the description below omits the description of well-known functions and structures.

[0104] It should be noted that in the technical solutions of the present disclosure, in terms of the collection, gathering, updating, analysis, processing, use, transmission, storage, etc. of user personal information, they all comply with the provisions of relevant laws and regulations, are used for legal purposes, and do not violate public order and good customs. Necessary measures are taken for user personal information to prevent illegal access to user personal information data, and to safeguard user personal information security, network security, and national security.

[0105] Regarding the terms involved in this solution, the following explanations are made:

[0106] OLAP: That is, an analytical database. Most OLAP databases use columnar storage and are oriented to analysis scenarios with more reads than writes.

[0107] Materialized view: Corresponding to the traditional logical view, that is, materializing the query result of the logical view to physical storage. Materialized views are often used in data warehouses and decision support systems to accelerate operations such as report generation and data analysis. Suppose there is an e-commerce system that contains two tables: orders (order table) and order_items (order details table). To improve query performance, especially when it is necessary to frequently count the total order amount of each customer, a materialized view can be created. The materialized view will pre-calculate and store the total order amount of each customer. Each time the total order amount of a customer is queried, the data is directly read from the materialized view instead of recalculated, thus greatly improving the query speed.

[0108] Metadata: That is, data that describes data. For a common data table, table name, column name, field type, etc., these can all be regarded as metadata.

[0109] Many mainstream OLAP databases in the industry, such as ClickHouse, Apache Doris, Apache Kylin, etc., provide materialized view related functions, but have not yet solved the problem of automatic creation of materialized views. As an advanced capability in database technology, materialized views usually require experienced DBAs (Database Administrators) or architects to extract query commonalities based on the query characteristics of the business itself, so as to establish a reasonable materialized view that covers as many queries as possible.

[0110] Currently, some OLAP engines are trying to explore the direction of automation. However, through communication with the open source community and follow-up of technical capabilities, it is found that in order to truly implement and produce results in business systems, automation capabilities require in-depth analysis of business query characteristics and the input of a large amount of statistical information. It is difficult to achieve significant benefits in real business by simply relying on rewriting based on traditional syntax or structure at the database level.

[0111] It should be noted that the benefits here are not just "monetization" in the traditional sense, but also include the enhancement of business value and optimization of user experience. In many scenarios (such as OKR (Objectives and Key Results) setting, performance reports and reviews), these "benefits" are crucial because they help measure the actual contribution of technical work from a business perspective and ensure that the value of technology is fully recognized and understood.

[0112] See also Figure 1 , which shows a main flow chart of a materialized view processing method provided by an embodiment of the present invention, including the following steps:

[0113] S101: Collecting statistics on historical slow query logs of the business system in a preset time period, and determining query statements that do not hit the cache according to the historical slow query logs;

[0114] S102: Counting query information of each query statement in the preset time period to filter out query statements whose query information meets preset requirements, and obtain a query statement set;

[0115] S103: Determine a common query statement in the query statement set, obtain offline data corresponding to the common query statement from the data lake, and materialize the offline data to obtain a materialized view.

[0116] This embodiment proposes an automatic creation mechanism based on materialized views, which belong to the database level. For steps S101 and S102, a scheduled task is set in the business system (such as the UData system). If the period is 7 days, the program will be executed regularly to automatically count the historical slow query logs of the last 7 days. By analyzing the historical slow query logs of these 7 days, those query SQL (Structured Query Language) statements that did not hit the cache are identified, especially those queries with long query times, many queries and involving offline tables. For example, offline queries with more than 100 queries and an average query time greater than or equal to 2 seconds will be analyzed in detail. The average query time here is calculated by accumulating the total query time and dividing it by the number of queries.

[0117] Taking a typical query scenario of a UData system as an example, users perform associated queries on offline tables and custom uploaded Excel local tables on the UData platform. Since offline data is updated T+1, users often need to query data for half a year or a year, which may involve hundreds of GB or even TB of data. The typical pain point of this type of query is that the query is often slow, because data transmission alone often takes several seconds or even tens of seconds. And because these queries are often part of a common logic (called a data set on the UData platform), they may be referenced and reused by multiple UData reports, so this slow query will be repeated many times, further increasing the query burden. In response to the above scenario problems, it is proposed to solve them through automated animalization.

[0118] Here we emphasize "cache misses". When a query hits the cache, it means that the required data already exists in the cache. In this case, there is no need to access the data lake through the query engine, so the advantages of materialization are difficult to reflect. However, in the case of cache misses, there is no corresponding data in the cache, and the data lake must be accessed through the query engine, which brings access pressure to the data lake. At this time, the role of materialized views is particularly prominent. It can significantly reduce direct access to the data lake and improve query efficiency. Therefore, this solution is only for the case of "cache misses".

[0119] Through the above automated analysis process, frequent and time-consuming cache miss queries can be accurately located and materialized views can be created for them, thereby effectively alleviating the pressure on the data lake and improving overall query performance.

[0120] For step S103, for the filtered query statements, extract the common query logic part of these query statements and determine the SQL statement of the common query logic part. Extract the common logic part of these queries for materialization. Thanks to the dataset concept proposed by the UData platform, abstractly extract the common query part and reuse it as part of the user's SQL query, thereby reducing redundant code and improving query efficiency.

[0121] Obtain the offline data corresponding to the SQL statement from the data lake and perform materialization processing on the offline data to create a materialized view and store it in the query engine. It should be noted that the data lake and the database are two different concepts: the database requires the stored data and tables to have a strict format, while the data lake allows storing various formats of data without strict format restrictions. In this way, query performance can be effectively improved and the management of query logic can be simplified.

[0122] Furthermore, the extracted common query logic part will generate a logical execution plan tree to determine whether it can be applied to rewriting. Given that the materialized view rewriting ability of the current mainstream query engines only supports rewriting for SPJG (select, project, join, group by), this solution innovatively introduces a rewriting method that combines text-based and structure-based approaches to verify whether the common query logic can achieve materialized rewriting. If transparent rewriting (same as materialized rewriting, meaning imperceptible to the user) cannot be achieved, then the creation of an automatic materialized view for it will be abandoned. For the part that can be successfully rewritten, the materialized view creation action will be executed.

[0123] Here, it is explained why a rewriting method that combines text-based and structure-based approaches is needed. If only relying on structural analysis, the subtle differences in the query may not be captured, while combining text analysis can identify specific patterns or keywords, thereby improving the accuracy and flexibility of rewriting. For example, although some queries have similar structures, their specific conditions are different, and text-based analysis can help distinguish these differences. In addition, by considering both text and structure information simultaneously, the query logic can be more comprehensively understood to avoid misjudgment, which helps ensure that the creation of the materialized view will not affect the correctness of the query result. For complex queries, especially those containing multiple subqueries, nested queries, or special functions, relying only on structural analysis may not be sufficient. Combining text analysis can better handle these complex situations and ensure the applicability and effectiveness of rewriting.

[0124] Suppose there is a query that extracts data from a customer table and an order table and filters and groups the data by country. Through structural analysis, the basic operations of the query (such as selecting fields, joining tables, filtering conditions, and grouping) can be identified. However, different queries may have different specific filtering conditions.

[0125] For example, both queries involve the same table joins and grouping operations, but one query filters customers in the United States and the other filters customers in Canada. If only relying on structural analysis, it might be wrongly assumed that these two queries can use the same materialized view. However, by combining text analysis, the differences in the filtering conditions can be accurately identified, thus avoiding the misuse of the same materialized view. Ultimately, a materialized view is created only when the query logic exactly matches, ensuring the transparency and accuracy of the rewrite. This approach not only improves the reliability of the rewrite but also guarantees that the user's query experience is not affected.

[0126] The method provided by the above embodiment can solve the problem that traditional materialized views strongly rely on manual analysis for creation. By automatically identifying query statements that miss the cache and meet the preset requirements, extracting the common logical parts for these query statements to create materialized views, it can significantly reduce the access to the data lake, improve the query response speed, and reduce the overall resource consumption of the system, thereby greatly enhancing the query performance and user experience of the business system.

[0127] See Figure 2 , which shows a schematic flowchart of an optional method for processing materialized views according to an embodiment of the present invention, including the following steps:

[0128] S201: Determine the query statement for creating a materialized view, determine the sub-query statements of the query statement, and iterate through each sub-query statement to calculate the feature values;

[0129] S202: In response to the feature values of the sub-query statements of multiple materialized views being consistent, determine that the sub-query statement is the reusable statement for the multiple materialized views;

[0130] S203: Obtain the offline data corresponding to the reusable statement from the data lake, and perform materialization processing on the offline data to obtain a materialized view.

[0131] This embodiment is applied after generating the materialized view to further optimize the query performance. In practical applications, there may still be commonalities among the multiple created materialized views. For example, the SQL statements of three materialized views have common parts, and these common parts themselves are also valid SQL statements. By extracting these common parts, a materialized view with a larger coverage can be created for a greater degree of query coverage, thereby improving the query efficiency.

[0132] This solution recursively analyzes the logical execution plan tree of the materialized view creation statement (i.e., the SQL statement) to identify and normalize all subquery statements. By calculating the eigenvalue of the subquery statement and counting its occurrence times, the subqueries with high reuse degree are determined. The eigenvalue is obtained by processing the subquery statement, and common algorithms include MD5 (Message-Digest Algorithm 5) or hash functions. For example, MD5 can generate a 32-bit hexadecimal string as the eigenvalue. Suppose there is a subquery statement "SELECT name, age FROM users WHERE country = 'USA'", after being processed by the MD5 algorithm, the eigenvalue "9e107d9d372bb6826bd81d3542a419d6" may be obtained. This eigenvalue is used to uniquely identify the subquery for subsequent reuse and comparison.

[0133] Basically, 99% of user queries contain complex subquery statements. Therefore, for each SQL statement with subqueries, the subquery statements are iteratively processed layer by layer, the eigenvalue is calculated, and the query times are counted. Taking MD5 as the eigenvalue example, MD5 can be used to identify whether two subquery statements are the same. If the MD5 values of multiple subquery statements are the same, it is considered that these subquery statements in this SQL are exactly the same, so as to find the commonality between multiple materialized views, and obtain the offline data corresponding to the reused statement from the data lake to build a higher-level materialized view on top of the materialized view. Finally, these optimized materialized views are stored in the query engine.

[0134] To evaluate the actual benefits of creating materialized views, filtering can be performed based on the query times. Specifically, the reused statements with larger query times are filtered out, such as greater than or equal to a preset threshold (e.g., 5 times), to ensure that the created materialized views have a significant performance improvement effect.

[0135] This solution realizes the identification of materialized views through various means, including an automated identification program based on heuristic rules and intelligent extraction of query commonality by a large model, mainly relying on the automated identification program based on heuristic rules. Currently, the heuristic rule identification method based on statistical information has significant benefits in the online system. This method can efficiently discover and utilize the commonality in queries, thereby optimizing the creation and use of materialized views.

[0136] The method provided in the above embodiments proposes a multi-level materialized view automatic creation mechanism, that is, automatically creating new materialized views on top of the existing materialized views, realizing the efficient reuse of subqueries, thereby reducing the maintenance cost of materialized views, reducing duplicate calculations and data redundancy, and significantly improving the query performance. Verified by the online system, this operation has brought a performance improvement of more than 5 times, significantly optimizing the use efficiency of cluster resources.

[0137] See Figure 3 , which shows a schematic flowchart of another optional physical materialized view processing method according to an embodiment of the present invention, including the following steps:

[0138] S301: Query whether there is a corresponding data change message in the message queue according to the database table name corresponding to the offline data; wherein, the data lake receives the processed database table transmitted by the offline processing engine, and then sends a data change message about the database table to the message queue;

[0139] S302: In response to the query result indicating existence, obtain the offline data from the correspondingly changed database table in the data lake according to the data change message, so as to update the metadata and data of the corresponding physical materialized view.

[0140] This embodiment is applied to the automatic management after the generation of the physical materialized view, especially the refresh management. In the data lake ecosystem, the processing of most offline data depends on the BDP platform (i.e., the offline processing engine) to achieve. When the upstream T+1 offline data is processed, the BDP platform will transmit the processed database table to the data lake for storage, and the data lake will send a data change message about the database table to the message queue (MessageQueue, abbreviated as MQ).

[0141] The refresh function is immediately enabled after the physical materialized view is created. This solution provides the ability to instantaneously refresh the physical materialized view through the query engine of the business system, enabling it to automatically sense changes in upstream data and ensure the instant update of the physical materialized view data. Specifically, the query engine listens for messages from the BDP platform, and checks whether there is a corresponding data change message in the message queue according to the database table name of the offline data corresponding to the physical materialized view. If there is a data change message, it means that the offline data has been updated and the physical materialized view needs to be refreshed, including updating its metadata and actual data, so as to ensure the freshness of the physical materialized view data. If there is no data change message, continue to listen without refreshing. This can ensure that the physical materialized view always reflects the latest data status.

[0142] This solution determines whether the offline data needs to be refreshed through metadata. The metadata usually includes the last update time of the partition where the offline data is located. A partition and bucket rule is set in the query engine to cache different offline data, ensuring reasonable distribution of data on multiple database servers and avoiding data skew. The partition and bucket are based on the output columns of the physical materialized view and the metadata statistical information of the columns to optimize data distribution and query performance.

[0143] For example, bucketing by gender will result in the data being divided into two parts (male and female), while bucketing by order number can divide the data into multiple parts and distribute them on multiple servers, thereby improving the speed of parallel queries. However, to determine which columns are suitable for bucketing, it is necessary to understand the data characteristics of these columns, and the data describing the data characteristics is called metadata. Partitioning usually uses a time field (such as by day or month) to perform the first-level division of the data, and then performs the second-level division based on the bucketing field.

[0144] Therefore, when receiving a data change message, this solution will obtain the first metadata of the corresponding database table from the data lake and obtain the second metadata of the materialized view corresponding to the offline data. Generally, the second metadata of the materialized view should be later than the first metadata. If it is found that the first metadata is later than the second metadata, it means that the materialized view in the query engine has not been refreshed in time, and at this time, the materialized view needs to be marked as invalid. Only after updating the materialized view will it be marked as valid again. During the period when the materialized view is invalid, that is, in a very short time interval when the local data has not been updated yet (it is possible that the change message has not been consumed), if a query request is received, the system will directly access the data lake to obtain the latest data and return the result. This can ensure that the query is always based on the latest data, guarantee the accuracy of the query data, and at the same time optimize the data management and query performance.

[0145] To ensure the effectiveness and performance benefits of the materialized view, this solution proposes a dynamic adjustment mechanism based on query habits. By counting the access times of each materialized view in the past week or two, the system can automatically evaluate its hit rate. For materialized views with access times lower than the preset threshold (the fourth value), the system will automatically mark them as invalid and stop the refresh operation, thereby realizing the automatic offline of the materialized view.

[0146] The method provided by the above embodiment monitors the data changes of the database table in real time through the message queue, and when detecting a change message, automatically obtains the updated offline data from the data lake to synchronously update the metadata and data of the materialized view. This mechanism ensures that the materialized view is always consistent with the latest state of the database table, significantly optimizes the refresh efficiency of the materialized view, greatly improves the timeliness and accuracy of the data. At the same time, it reduces the need for manual intervention, reduces the maintenance cost, and improves the automation degree and reliability of the system, further enhancing the query performance and user experience.

[0147] See Figure 4 , which shows a schematic flow diagram of another optional method for processing materialized views according to an embodiment of the present invention, including the following steps:

[0148] S401: Receive a query request, perform lexical analysis and syntactic analysis on the query request to generate a logical query tree, and then match the materialized view according to the logical query tree;

[0149] S402: In response to the matching result being non - existent, obtain data that meets the query request from the data lake through the query engine and return it;

[0150] S403: In response to the matching result being multiple materialized views, input the multiple materialized views into the cost evaluation model of the query engine to obtain the evaluation score of each materialized view;

[0151] S404: Use the materialized view with the highest evaluation score as the target materialized view, and convert the logic of querying the data lake in the query request into the logic of querying the target materialized view;

[0152] S405: Determine the data that meets the query request from the target materialized view through the changed logic and return it.

[0153] This embodiment is applied to the automatic rewriting link after the creation of the materialized view. For steps S401 and S402, after the query engine receives the query request initiated by the user business side, it will perform lexical analysis and syntax analysis in sequence, and finally generate a logical query tree. This process ensures the correctness and effectiveness of the query, laying a foundation for subsequent query execution. For example, when a user submits a complex SQL query, the query engine first performs lexical analysis on the query statement to identify basic elements such as keywords, identifiers, and operators. Then, through syntax analysis, it verifies the legality of the query structure and constructs a logical query tree to clearly represent the operation sequence and hierarchical relationship of the query.

[0154] This solution will match based on the generated logical query tree and the creation statement of the materialized view. If all materialized view queries cannot be hit, the original query will be returned, and the logic of the original query SQL statement will be executed, that is, obtain the data that meets the SQL statement from the data lake and return it.

[0155] For steps S403 - S405, when the query engine matches a materialized view, the matching result can be one or more. If only one materialized view is matched, the query engine will directly obtain the data that meets the query request from this materialized view and return it. However, when multiple materialized views are matched, the system will start a preset screening mechanism, input these candidate materialized views into the cost evaluation model of the query engine, and select the materialized view with the highest evaluation score for rewriting. Once a materialized view is hit, the query engine will automatically convert the logic of querying the remote offline table in the query request into the logic of querying the local materialized view, thus bringing a performance improvement of several times or even dozens of times. Especially in the case of large amounts of data and complex queries, this optimization effect is particularly significant.

[0156] Suppose a user initiates a query request involving the joining of multiple large tables and complex filtering conditions. The query engine first matches multiple relevant materialized views. Through a cost evaluation model, the system selects the optimal materialized view and uses it to rewrite the query logic. Eventually, the originally time-consuming remote table query is efficiently replaced with a fast query on the local materialized view, significantly shortening the response time and improving the overall performance.

[0157] The method provided in the above embodiment, through an intelligent matching and evaluation mechanism, ensures that the query request can efficiently utilize the materialized view, significantly improving the query performance. When the query request cannot directly hit the materialized view, the system will automatically obtain data from the data lake and return it, ensuring the integrity and accuracy of the query. In the case of multiple matching materialized views, the system selects the optimal materialized view for rewriting through a cost evaluation model. This mechanism not only improves the system's response speed but also reduces resource consumption, especially when dealing with complex queries and large data volumes.

[0158] See Figure 5 , which shows a schematic flowchart of another optional method for processing materialized views according to an embodiment of the present invention, including the following steps:

[0159] S501: Based on text-based matching and rewriting, determine all query statements and sub-query statements that match the logical query tree;

[0160] S502: Match all the query statements and sub-query statements that match with the creation statements of each materialized view;

[0161] S503: In response to the matching result being existent, obtain all the matching materialized views;

[0162] S504: In response to the matching result being non-existent, based on structure-based matching and rewriting, compare the logical query tree with the logical execution plan tree of each materialized view to determine all the materialized views with consistent logic.

[0163] This embodiment describes how to match materialized views according to the logical query tree in the query optimization phase. For steps S501 - S503, the system first performs text-based matching and rewriting. Text-based matching and rewriting will determine all query statements and sub-query statements that match the logical query tree, and compare these statements with the creation statements of each materialized view to find all the matching materialized views. After successful matching, the rewriting is automatically completed.

[0164] Assume the query condition is Select a from (select b from B ) A where a.id =1. The system first attempts to match the complete query statement Select a from (select b from B) A where a.id = 1. It checks whether there is a materialized view whose creation statement fully or partially matches this complete query. If a matching materialized view is found, the rewrite is directly completed. If the overall match fails, the system further decomposes the query and attempts to match the subquery part (select b from B). In this case, the system checks whether there is a materialized view whose creation statement matches the subquery. If a matching materialized view is found, the subquery part can be considered replaced with this materialized view, and the query logic is reconstructed.

[0165] However, in practical applications, the time functions of materialized views are usually dynamic time functions, while the time conditions in the user's query requests may be a specific day. This difference may lead to the failure of text-based matching. To solve this problem, this solution defaults to assisting the user in restoring dynamic time functions in the application layer background to ensure that text-based matching can successfully hit the materialized view. For example, if the user's query involves data on the current date or data two days before the current date (T-2), the system will automatically replace these query conditions with dynamic time functions on the application side background, so as to match the materialized view created with dynamic time, thus solving the problem that the query rewrite cannot be hit.

[0166] For step S504, for queries that cannot be hit, the system will further attempt to rewrite based on structural matching. At this time, the system compares each node and its information (such as fields, functions, grouping) in the logical query tree and the logical plan tree of the materialized view to determine whether the logical structures are consistent. If a materialized view corresponding to a logically consistent logical plan tree is found, the rewrite is completed. If neither of the above two rewrite methods can be completed, it falls back to the original query and continues to execute the subsequent query plan.

[0167] The structure-oriented rewrite is actually a rewrite based on the syntax tree. Assume that a piece of data is queried from the database, involving query, filtering, and scanning operations, which will form query nodes, filtering nodes, and scanning nodes in the syntax tree. If table A is queried, the operation of scanning A is necessarily involved. Suppose there is a faster materialized view B, the system can replace the operation of scanning A with the operation of scanning the materialized view B at the syntax tree level. In this way, the original query that needed to scan the entire table A is optimized to directly query the materialized view B, thus significantly improving the query performance.

[0168] In this way, the system can not only identify and utilize physically equivalent materialized views, but also significantly reduce the query execution time and resource consumption without affecting the query results. This rewrite mechanism based on the syntax tree ensures the flexibility and efficiency of query optimization, especially when dealing with complex queries, which can bring significant performance improvements.

[0169] Since the matching rewrite of materialized views is an NP (Nondeterministic Polynomial time) hard problem, the system will limit the number of attempts for materialized views to match. Because there are some real queries where the time consumed by the materialized view attempt to rewrite is much greater than the time of the real query, the system sets that if the number of attempts exceeds a certain limit, the matching attempt of the materialized view will be terminated.

[0170] The method provided in the above embodiments ensures the efficient matching of query statements and materialized views through text-based and structure-based matching rewrite mechanisms. First, it can comprehensively identify all relevant queries and subqueries in the logical query tree and precisely match them with the creation statements of materialized views, improving the hit rate. For complex queries that cannot be matched by text, the system further utilizes logical structure comparison to ensure that even in the case of complex query structures or the presence of dynamic time functions, the most suitable materialized view can be accurately found. This dual matching mechanism not only enhances the flexibility and accuracy of query optimization but also significantly reduces unnecessary calculations and resource consumption, thus greatly improving query performance and the overall efficiency of the system.

[0171] See Figure 6 As shown, it shows a schematic flow diagram of a specific materialized view processing method according to an embodiment of the present invention, which involves multiple links such as the automatic creation, automatic management, and automatic rewrite of materialized views. The overall process of the solution is as follows:

[0172] 1. Statistical information input: The system first statistically analyzes the historical slow query logs of the business system within a preset time period, determines the query statements that miss the cache, and uses them as the external information input for the automatic materialization function.

[0173] 2. Materialized view creation: Through heuristic rules, large models, and manual judgment in extreme scenarios, the system determines the query statements that meet the preset requirements, extracts the common logical parts of these query statements, and obtains the corresponding offline data from the data lake to generate materialized views, so as to complete the logical extraction and creation of materialized views.

[0174] 3. Offline data management: The query engine sets reasonable partition and bucketing rules, based on the output columns and metadata statistical information of the materialized view, to cache offline data and complete the refresh operation. The refreshed offline data will update the metadata and actual data of the materialized view.

[0175] 4. Materialized View Selection and Rewriting: The queries initiated by users may match multiple candidate materialized views. The system inputs these materialized views into the cost evaluation model of the query engine, selects the most suitable materialized view for rewriting to optimize the query performance.

[0176] 5. Dynamic Time Function Processing: For queries that cannot be hit due to dynamic time function predicates, the application layer background will default to assisting users in restoring the dynamic time function by default to ensure that the materialized views based on text matching can be successfully hit.

[0177] 6. Query Optimization and Performance Improvement: Once a materialized view is hit, the query engine will automatically convert the logic of querying remote offline tables into the logic of querying local materialized views. This operation can bring a performance improvement of several times or even dozens of times, especially in the case of large amounts of data and complex queries, the effect is significant.

[0178] 7. Matching Attempt Limit: Since the matching and rewriting of materialized views is an NP-hard problem, the system will limit the number of matching attempts. If the number of attempts exceeds a certain number and still fails, that is, the time consumed by the materialized view attempt to rewrite is much greater than the time of the actual query, the matching attempt will be terminated to avoid unnecessary resource consumption.

[0179] The actions such as the creation of the entire materialized view, query rewriting and replacement, and taking the materialized view offline are transparent to existing users, and users do not need to make any behavioral changes. Through the implementation of the automated materialization ability, the query performance of the cluster is optimized, bringing an extreme query experience to users. Compared with the existing technology, there are at least the following beneficial effects:

[0180] First, automatic materialization in the ad-hoc analysis scenario is realized, including the automation of the creation, management, and rewriting of materialized views. This is a reliable solution proposed in a scenario that cannot be supported by multiple mainstream OLAP engines in the industry after research, and has been verified by the online business scenario. After going online, it has brought a performance improvement of more than 5 times to the production system. In addition, the automatic creation of multi-level materialized views is realized, that is, materialized views are automatically created on top of materialized views, which can greatly reduce the refresh and maintenance costs of materialized views. Through online observation, the resource consumption of refreshing materialized views in the overall cluster has been significantly reduced.

[0181] See Figure 7 , which shows a schematic diagram of the main modules of a materialized view processing device 700 provided by an embodiment of the present invention, including:

[0182] A statistics module 701, configured to count the historical slow query logs of the business system in a preset time period, and determine the query statements that do not hit the cache according to the historical slow query logs;

[0183] A screening module 702, configured to count the query information of each query statement within the preset time period, so as to screen out the query statements whose query information meets the preset requirements, and obtain a query statement set;

[0184] A materialization module 703, configured to determine the common query statements in the query statement set, obtain the offline data corresponding to the common query statements from the data lake, and perform materialization processing on the offline data to obtain a materialized view.

[0185] In the implementation device of the present invention, the screening module 702 is configured to:

[0186] For each query statement, count the number of query times and the query time consumption each time within the preset time period, and further obtain the average query time consumption;

[0187] Screen out the query statements whose number of query times is greater than or equal to a preset first value and whose average query time consumption is greater than or equal to a preset second value.

[0188] In the implementation device of the present invention, the screening module 702 is further configured to:

[0189] For each query statement that meets the preset requirements, generate a logical execution plan tree, and based on the combination of text and structure rewriting, screen out the query statements that can be materially rewritten.

[0190] The implementation device of the present invention further includes a multi-level materialized view module, configured to:

[0191] Determine the query statements for creating the materialized view, determine the sub-query statements of the query statements, and iterate each sub-query statement to calculate the characteristic values;

[0192] In response to the characteristic values of the sub-query statements of multiple materialized views being consistent, determine that the sub-query statement is the reusable statement of the multiple materialized views;

[0193] Obtain the offline data corresponding to the reusable statement from the data lake, and perform materialization processing on the offline data to obtain a materialized view.

[0194] In the implementation device of the present invention, the multi-level materialized view module is further configured to:

[0195] Count the number of query times of each sub-query statement;

[0196] Screen out the reusable statements whose number of query times is greater than or equal to a preset third value.

[0197] The implementation device of the present invention further includes a refresh management module, configured to:

[0198] Query whether there is a corresponding data change message in the message queue according to the database table name corresponding to the offline data; wherein, the data lake receives the processed database table transmitted by the offline processing engine, and then sends a data change message about the database table to the message queue;

[0199] In response to the query result being that there is, obtain the offline data from the correspondingly changed database table in the data lake according to the data change message, so as to update the metadata and data of the corresponding materialized view.

[0200] In the implementation device of the present invention, the materialized view is stored in the query engine, and the refresh management module is further configured to:

[0201] Obtain the first metadata of the corresponding database table from the data lake according to the data change message;

[0202] Obtain the second metadata of the materialized view corresponding to the offline data;

[0203] In response to the first metadata being greater than the second metadata, mark the materialized view as invalid; after updating the materialized view, mark the materialized view as valid;

[0204] During the period when the materialized view is marked as invalid, in response to receiving a query request, obtain the data that meets the query request from the data lake through the query engine and return it.

[0205] The implementation device of the present invention further includes a materialized view management module, which is used for:

[0206] Statistically count the access times of each materialized view in the historical time period, mark the materialized views with access times less than or equal to a preset fourth value as invalid, and stop the refresh operation.

[0207] The implementation device of the present invention further includes a matching and rewriting module, which is used for:

[0208] Receive a query request, perform lexical analysis and syntactic analysis on the query request to generate a logical query tree, and then match the materialized view according to the logical query tree;

[0209] In response to the matching result being non-existent, obtain the data that meets the query request from the data lake through the query engine and return it;

[0210] In response to the matching result being multiple materialized views, input the multiple materialized views into the cost evaluation model of the query engine to obtain the evaluation score of each materialized view;

[0211] Take the materialized view with the highest evaluation score as the target materialized view, and convert the logic of querying the data lake in the query request into the logic of querying the target materialized view;

[0212] Determine and return the data that meets the query request from the target materialized view through the changed logic.

[0213] In the implementation device of the present invention, the matching and rewriting module is used for:

[0214] Based on text-based matching and rewriting, determine all query statements and sub-query statements that match the logical query tree;

[0215] Match all the query statements and sub-query statements that match with the creation statements of each materialized view;

[0216] In response to the matching result being existent, obtain all the materialized views that match;

[0217] In response to the matching result being non-existent, based on structure-based matching and rewriting, compare the logical query tree and the logical execution plan tree of each materialized view, and determine all the materialized views with consistent logic.

[0218] In the implementation device of the present invention, the matching and rewriting module is further used for:

[0219] Based on text-based matching and rewriting, replace the time-related functions in all query statements and sub-query statements with dynamic time functions.

[0220] In the implementation device of the present invention, the matching and rewriting module is further used for:

[0221] Statistically count the number of matching and rewriting times. In response to the number of matching and rewriting times reaching the preset number threshold, stop the matching and rewriting operation, and obtain and return the data that meets the query request from the data lake through the query engine.

[0222] In addition, the specific implementation content of the device in the embodiments of the present invention has been described in detail in the above method, so the repeated content will not be described here.

[0223] Figure 8 An exemplary system architecture 800 to which the embodiments of the present invention can be applied is shown, including terminal devices 801, 802, 803, a network 804, and a server 805 (merely examples).

[0224] The terminal devices 801, 802, 803 can be various electronic devices with a display screen and supporting web browsing, installed with various communication client applications. Users can use the terminal devices 801, 802, 803 to interact with the server 805 through the network 804 to receive or send messages, etc.

[0225] The network 804 is a medium for providing a communication link between the terminal devices 801, 802, 803 and the server 805. The network 804 can include various connection types, such as wired, wireless communication links, or fiber optic cables, etc.

[0226] The server 805 may be a server that provides various services. For example, it may be a background management server (merely an example) that supports shopping websites browsed by users using the terminal devices 801, 802, and 803. The background management server may analyze and process data such as product information query requests received, and feedback the processing results (such as target push information, product information - merely examples) to the terminal devices. It should be noted that the method provided by the embodiments of the present invention is generally executed by the server 805. Correspondingly, the device is generally disposed in the server 805.

[0227] It should be understood that Figure 8 the numbers of terminal devices, networks, and servers in

[0228] Next, refer to Figure 9 , which shows a schematic structural diagram of a computer system 900 of a terminal device suitable for implementing the embodiments of the present invention. Figure 9 The terminal device shown is merely an example and should not impose any limitations on the functions and usage scope of the embodiments of the present invention.

[0229] As Figure 9 shown, the computer system 900 includes a central processing unit (CPU) 901, which can perform various appropriate actions and processes according to programs stored in a read-only memory (ROM) 902 or programs loaded from a storage section 908 into a random access memory (RAM) 903. In the RAM 903, various programs and data required for the operation of the system 900 are also stored. The CPU 901, ROM 902, and RAM 903 are connected to each other via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0230] The following components are connected to the I / O interface 905: an input section 906 including a keyboard, a mouse, etc.; an output section 907 including, for example, a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 908 including a hard disk, etc.; and a communication section 909 including a network interface card such as a LAN card, a modem, etc. The communication section 909 performs communication processing via a network such as the Internet. A drive 910 is also connected to the I / O interface 905 as required. A removable medium 911, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 910 as required, so that a computer program read from it can be installed into the storage section 908 as required.

[0231] In particular, according to the embodiments disclosed in the present invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program contains program codes for executing the methods shown in the flowcharts. In such an embodiment, the computer program can be downloaded and installed from the network through the communication section 909, and / or installed from the removable medium 911. When the computer program is executed by the central processing unit (CPU) 901, the above functions defined in the system of the present invention are executed.

[0232] It should be noted that the computer-readable medium shown in the present invention can be a computer-readable signal medium, a computer-readable storage medium, or any combination of the two. The computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the above. More specific examples of the computer-readable storage medium can include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, the computer-readable storage medium can be any tangible medium that contains or stores a program, and the program can be used by or in combination with an instruction execution system, apparatus, or device. In the present invention, the computer-readable signal medium can include a data signal propagated in a baseband or as part of a carrier wave, which carries the computer-readable program code. Such a propagated data signal can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. The computer-readable signal medium can also be any computer-readable medium other than the computer-readable storage medium, and the computer-readable medium can send, propagate, or transmit a program for use by or in combination with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted by any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0233] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagram may represent a module, a segment of a program, or a part of code that contains one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions marked in the blocks may occur in a different order than that marked in the accompanying drawings. For example, two consecutive blocks shown may actually be executed substantially in parallel, and they may sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagram or flowchart, as well as combinations of blocks in the block diagram or flowchart, can be implemented by a dedicated hardware-based system that performs the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0234] The modules described in the embodiments of the present invention can be implemented in software or in hardware. The described modules can also be provided in a processor. For example, it can be described as: a processor includes a statistics module, a screening module, and a materialized view module. Among them, the names of these modules do not constitute a limitation on the module itself in some cases. For example, the materialized view module can also be described as the "materialized view module".

[0235] As another aspect, the present invention also provides a computer-readable medium, which can be included in the device described in the above embodiments; or can exist separately without being assembled into the device. The above computer-readable medium carries one or more programs, and when the one or more programs are executed by the device, the device is caused to execute any of the above-described materialized view processing methods.

[0236] The computer program product of the present invention includes a computer program, and the computer program implements the materialized view processing method in the embodiments of the present invention when executed by a processor.

[0237] The above specific implementation manners do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can occur depending on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A materialized view processing method, characterized in that: include: Collect historical slow query logs of the business system in a preset time period, and determine query statements that do not hit the cache based on the historical slow query logs; Counting the query information of each query statement in the preset time period to filter out the query statements whose query information meets the preset requirements, and obtaining a query statement set; Determine the common query statements in the query statement set, obtain offline data corresponding to the common query statements from the data lake, materialize the offline data, and obtain a materialized view.

2. The method according to claim 1, characterized in that The counting of query information of each query statement in the preset time period to filter out query statements whose query information meets preset requirements includes: For each query statement, count the number of queries and the query duration of each query in the preset time period, and then obtain the average query duration; Filter out query statements whose query times are greater than or equal to a preset first value and whose average query time is greater than or equal to a preset second value.

3. The method according to claim 1 or 2, characterized in that: The obtaining of the query statement set further includes: For each query statement that meets the preset requirements, a logical execution plan tree is generated, and query statements that can be materialized and rewritten are screened out through a combination of text-based and structure-based rewriting.

4. The method according to claim 1, characterized in that: The method further comprises: Determine a query statement for creating a materialized view, determine a subquery statement of the query statement, and iterate each subquery statement to calculate a characteristic value; In response to the characteristic values ​​of the sub-query statements of the multiple materialized views being consistent, determining that the sub-query statement is a reused statement of the multiple materialized views; Obtain offline data corresponding to the reused statements from the data lake, materialize the offline data, and obtain a materialized view.

5. The method according to claim 4, characterized in that The method further comprises: Count the number of queries for each subquery statement; The reused statements whose query times are greater than or equal to a preset third value are screened out.

6. The method according to claim 1 or 4, characterized in that: After obtaining the materialized view, the method further includes: According to the database table name corresponding to the offline data, query whether there is a corresponding data change message in the message queue; wherein the data lake receives the processed database table transmitted by the offline processing engine, and then sends the data change message about the database table to the message queue; In response to the query result being true, offline data is obtained from the corresponding changed database table in the data lake according to the data change message to update the metadata and data of the corresponding materialized view.

7. The method according to claim 6, characterized in that The materialized view is stored in the query engine, and the method further includes: According to the data change message, obtain the first metadata of the corresponding database table from the data lake; Obtain the second metadata of the materialized view corresponding to the offline data; In response to the first metadata being greater than the second metadata, marking the materialized view as invalid; after updating the materialized view, marking the materialized view as valid; During the period when the materialized view is marked as invalid, in response to receiving a query request, the query engine obtains and returns data that meets the query request from the data lake.

8. The method according to claim 1, characterized in that The method further comprises: The number of accesses to each materialized view in the historical time period is counted, the materialized view whose number of accesses is less than or equal to a preset fourth value is marked as invalid, and the refresh operation is stopped.

9. The method according to claim 1, characterized in that: The method further comprises: Receive query requests, perform lexical analysis and syntax analysis on the query requests to generate a logical query tree, and then match materialized views based on the logical query tree; In response to a matching result being non-existent, the query engine obtains data that matches the query request from the data lake and returns the data; In response to the matching result being a plurality of materialized views, the plurality of materialized views are input into a cost evaluation model of the query engine to obtain an evaluation score of each materialized view; The materialized view with the highest evaluation score is used as the target materialized view, and the logic of querying the data lake in the query request is converted into the logic of querying the target materialized view. Through the modified logic, the data that meets the query request is determined from the target materialized view and returned.

10. The method according to claim 9, characterized in that The matching of materialized views according to the logical query tree includes: Based on text matching rewriting, determine all query statements and sub-query statements that match the logical query tree; Match all matching query statements and subquery statements with the creation statement of each materialized view; In response to the matching result being present, all matching materialized views are obtained; In response to the matching result being non-existent, a structure-based matching rewrite is performed to compare the logical query tree and the logical execution plan tree of each materialized view to determine all logically consistent materialized views.

11. The method according to claim 10, characterized in that The method further comprises: Based on text matching rewriting, all time-related functions in query statements and subquery statements are replaced with dynamic time functions.

12. The method according to claim 9, characterized in that The method further comprises: The number of matching rewrites is counted, and in response to the matching rewrite number reaching a preset threshold, the matching rewrite operation is stopped, and the data that meets the query request is obtained from the data lake through the query engine and returned.

13. A materialized view processing device, characterized in that: include: A statistics module is used to collect statistics on the historical slow query logs of the business system in a preset time period, and determine the query statements that did not hit the cache based on the historical slow query logs; A screening module, used to count the query information of each query statement in the preset time period, so as to screen out the query statements whose query information meets the preset requirements, and obtain a query statement set; The materialization module is used to determine the common query statements in the query statement set, obtain the offline data corresponding to the common query statements from the data lake, and materialize the offline data to obtain the materialized view.

14. An electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 12.

15. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 12 is implemented.

16. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the method according to any one of claims 1 to 12 is implemented.

Images