Materialized view construction method, data processing system, medium and program product

Abstract

The invention discloses a materialized view construction method, a data processing system, a medium and a program product, and relates to the field of electrical digital data processing.The method comprises the steps that text query information is received, the execution sequence and the execution type of query operation in the text query information are determined, and a logic plan is generated; extracting a scanning operation, a projection operation, a connection operation and an aggregation operation from the logic plan to obtain a query sub-plan representing query semantics; according to the operation type of the query sub-plan, converting the query sub-plan into an initial plan slice; normalizing the initial plan film to generate a standard plan film in a preset format; according to a preset aggregation strategy, performing deduplication merging processing on the standard plan slices to obtain a target plan slice; and generating a table establishment statement of the materialized view based on the target plan film to construct the materialized view. By implementing the method, generation of the materialized view can be optimized, and the redundancy of the materialized view is reduced.

Description

Technical Field

[0001] This application relates to the field of electronic digital data processing, and in particular, to a materialized view construction method, a data processing system, a medium, and a program product. Background Art

[0002] In an OLAP database system, multi-table association aggregation is a typical query operation. To improve the performance of such queries, the database system usually adopts the materialized view technology to pre-compute the association operations and aggregation operations in the query and store them in the form of a table. When a user initiates a query, the query engine can replace the part of the query that matches the materialized view with an access to the materialized view, thereby reducing the amount of computation and improving the query performance.

[0003] In the related art, the materialized view construction technology mainly uses a logical plan composed of relational algebra operators to represent the query structure. This solution analyzes the query history, converts the query into a logical plan, extracts the query structure therein, and then generates a table creation statement for the materialized view based on these query structures.

[0004] However, with the increasing complexity of query patterns and the more diverse ways users write queries, the number of logical plans has increased sharply; and since each logical plan triggers the generation of a materialized view, the system needs to maintain a large number of materialized views, consuming a large amount of storage space and computing resources, resulting in a high cost. Summary of the Invention

[0005] This application provides a materialized view construction method, a data processing system, a medium, and a program product for optimizing the generation of materialized views and reducing the redundancy of materialized views.

[0006] In a first aspect, this application provides a materialized view construction method applied to a data processing system. The method includes: receiving text query information, determining the execution order and execution type of query operations in the text query information, and generating a logical plan; extracting scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing query semantics; converting the query sub-plan into an initial plan slice according to the operation type of the query sub-plan; performing a normalization process on the initial plan slice to generate a canonical plan slice in a preset format; performing a redundancy elimination and merging process on the canonical plan slice according to a preset aggregation strategy to obtain a target plan slice; and generating a table creation statement for the materialized view based on the target plan slice to construct the materialized view.

[0007] In the above embodiments, the data processing system generates a logical plan by receiving text query information, extracts the query sub-plan in SPJG mode and converts it into a plan fragment, and then through normalization processing and aggregation strategy processing, finally generates a materialized view table creation statement; realizes the standardized representation and equivalence determination of the query structure, can effectively identify equivalent query structures, eliminate redundant materialized view recommendations, and at the same time maintain the query acceleration ability of the materialized view; through the intermediate representation form of the plan fragment, it not only ensures the standardization of query processing, but also provides sufficient flexibility to handle various complex scenarios.

[0008] Combined with some embodiments of the first aspect, in some embodiments, the initial plan fragment includes a table plan fragment, a star join fragment, and an aggregation plan fragment; the step of converting the query sub-plan into an initial plan fragment according to the operation type of the query sub-plan specifically includes: when the query sub-plan is a single-table operation, converting the query sub-plan into a table plan fragment; when the query sub-plan is a multi-table join operation, converting the query sub-plan into a star join fragment; when the query sub-plan includes an aggregation operation, generating an aggregation plan fragment corresponding to the query sub-plan.

[0009] In the above embodiments, the data processing system divides the initial plan fragment into three types: table plan fragment, star join fragment, and aggregation plan fragment, and adopts corresponding conversion strategies for different query operation types, enabling the system to accurately process single-table operations, multi-table join operations, and aggregation operations; not only improves the accuracy of query structure conversion, but also facilitates subsequent normalization processing and redundancy elimination and merging, and at the same time enhances the system's ability to process complex queries.

[0010] Combined with some embodiments of the first aspect, in some embodiments, the step of normalizing the initial plan fragment to generate a normalized plan fragment in a preset format specifically includes: extracting the sub-fragment corresponding to the wide table in the initial plan fragment to obtain the plan fragment to be processed; traversing the plan fragment to be processed from bottom to top, converting the field data in the plan fragment to be processed into a string in a preset fixed order to obtain a normalized value; generating a normalized plan fragment based on the plan fragment to be processed and the normalized value.

[0011] In the above embodiments, through the normalization processing method of traversing the plan fragment from bottom to top and converting it into a string in a fixed order, the system can convert query structures with different forms but the same semantics into the same normalized value, can effectively eliminate the influence brought by differences in query writing methods, enable different expression forms with the same query semantics to be recognized as equivalent structures, and thus avoid generating redundant materialized views.

[0012] In some embodiments in combination with some embodiments of the first aspect, the step of performing redundancy elimination and merging processing on the specification plan slices according to a preset aggregation strategy to obtain the target plan slices specifically includes: grouping the specification plan slices with the same specification value into a group, and performing merging processing on the index column and dimension column of each group of slices to obtain merged plan slices; obtaining a preset aggregation logic expression to determine the preset aggregation strategy; and performing roll-up transformation processing on the merged plan slices according to the preset aggregation strategy to obtain the target plan slices.

[0013] In the above embodiments, the data processing system groups the plan slices with the same specification value, merges their index columns and dimension columns, and then performs transformation processing according to the preset aggregation strategy, so that the system realizes the effective identification and merging of equivalent query structures; not only reduces the number of redundant materialized views, but also ensures the query performance of the materialized views through reasonable strategy selection.

[0014] In some embodiments in combination with some embodiments of the first aspect, the step of performing roll-up transformation processing on the merged plan slices according to a preset aggregation strategy to obtain the target plan slices specifically includes: extracting the sub-slices corresponding to the wide table in the merged plan slices to obtain merged sub-slices; identifying the predicate types including atomic predicates, constant predicates, and aggregation predicates in the merged sub-slices; determining the corresponding preset aggregation strategy according to the predicate types, and performing roll-up transformation processing on the merged plan slices to obtain the target plan slices.

[0015] In the above embodiments, the data processing system can adopt corresponding processing methods according to the characteristics of different types of predicates by identifying the predicate types in the merged sub-slices and selecting appropriate aggregation strategies accordingly. This classification processing method enables the system to process various query conditions more precisely, ensuring both the accuracy of the query results and providing optimization space.

[0016] In some embodiments in combination with some embodiments of the first aspect, the step of determining the corresponding preset aggregation strategy according to the predicate types, performing roll-up transformation processing on the merged plan slices to obtain the target plan slices specifically includes: detecting the aggregation operation types in the merged sub-slices to determine the set of aggregation operations to be processed; obtaining the corresponding basic transformation strategies from the preset strategy library according to the set of aggregation operations to obtain a set of basic strategies; determining the strategy combination rules of the set of basic strategies based on user settings to generate a composite transformation strategy; and performing roll-up transformation processing on the merged plan slices according to the composite transformation strategy to obtain the target plan slices.

[0017] In the above embodiments, the data processing system realizes flexible processing of aggregation operations by detecting the aggregation operation types, selecting the corresponding transformation strategies from the strategy library, and generating composite transformation strategies according to user settings; it can not only process various complex aggregation scenarios, but also dynamically adjust the processing strategies according to actual needs.

[0018] In some embodiments in combination with some embodiments of the first aspect, after the step of generating a table creation statement for a materialized view based on a target plan slice to construct the materialized view, the method further includes: establishing a mapping relationship between a canonical value and the materialized view to obtain a view mapping table; receiving a user query request, extracting a query structure from the user query request to obtain a plan slice to be matched; performing a normalization process on the plan slice to be matched to obtain a canonical value to be matched; and determining a target materialized view according to the canonical value to be matched and the view mapping table.

[0019] In the above embodiments, the data processing system realizes efficient matching and use of materialized views by establishing a mapping relationship between canonical values and materialized views and using this mapping relationship to process user query requests; this processing method not only speeds up the query processing speed but also improves the utilization rate of materialized views.

[0020] In a second aspect, an embodiment of the present application provides a data processing system, which includes: one or more processors and a memory; the memory is coupled to the one or more processors, and the memory is used to store computer program code, the computer program code includes computer instructions, and the one or more processors call the computer instructions to enable the data processing system to execute the method described in the first aspect and any possible implementation manner in the first aspect.

[0021] In a third aspect, an embodiment of the present application provides a computer program product containing instructions, which, when the computer program product runs on a data processing system, enables the data processing system to execute the method described in the first aspect and any possible implementation manner in the first aspect.

[0022] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium including instructions, which, when the instructions run on a data processing system, enables the data processing system to execute the method described in the first aspect and any possible implementation manner in the first aspect.

[0023] It can be understood that the data processing system provided in the second aspect, the computer program product provided in the third aspect, and the computer storage medium provided in the fourth aspect are all used to execute the method provided in the embodiments of the present application. Therefore, the beneficial effects that can be achieved can refer to the beneficial effects in the corresponding method, which will not be elaborated here.

[0024] One or more technical solutions provided in the embodiments of the present application have at least the following technical effects or advantages: 1. Since a complete processing flow from text query to materialized view table creation statement is adopted, including steps such as logical plan generation, query sub-plan extraction, plan fragment conversion, normalization processing, and aggregation strategy processing, it is possible to convert query structures with different forms but the same semantics into a standardized representation form, effectively solving the problem of materialized view redundancy caused by diverse query expression methods in related technologies, and then achieving the accuracy of materialized view recommendation and the optimized utilization of system resources. Through the intermediate representation form of plan fragments, the system can accurately identify equivalent query structures and avoid creating redundant materialized views for queries with the same semantics.

[0025] 2. Since three different types of plan fragment representation forms, namely table plan fragments, star join fragments, and aggregation plan fragments, are adopted and corresponding conversion processing is carried out according to the operation types of query sub-plans, it is possible to accurately process various types of query operations, effectively solving the problem of inflexible processing of complex query structures in related technologies, and then achieving the accuracy of query structure conversion and the improvement of processing efficiency; through targeted conversion strategies, the system can accurately capture the characteristics of different types of query operations. Especially when dealing with multi-table join scenarios in the star model, it can better maintain query semantics and provide optimization space.

[0026] 3. Since a mapping mechanism between canonical values and materialized views is adopted and appropriate materialized views are determined through normalization processing and mapping matching during query processing, it is possible to quickly and accurately find suitable materialized views to accelerate queries, effectively solving the problem of low efficiency in materialized view selection in related technologies, and then achieving a significant improvement in query processing performance; by establishing a mapping relationship between canonical values and materialized views, the system can quickly locate suitable materialized views and avoid repeated matching calculations. Brief Description of the Drawings

[0027] Figure 1 is a schematic diagram of an application scenario of the materialized view construction method in an embodiment of the present application; Figure 2 is a schematic flowchart of the materialized view construction method in an embodiment of the present application; Figure 3 is another schematic flowchart of the materialized view construction method in an embodiment of the present application; Figure 4 is a schematic diagram of the structural organization of a plan fragment in an embodiment of the present application; Figure 5 is another schematic diagram of the structural organization of a plan fragment in an embodiment of the present application; Figure 6 is another schematic diagram of an application scenario of the materialized view construction method in an embodiment of the present application; Figure 7It is a schematic structural diagram of an entity device in an embodiment of the present application's data processing system. Detailed implementation manners

[0028] The terms used in the following embodiments of the present application are only for the purpose of describing specific embodiments and are not intended to limit the present application. As used in the specification of the present application, the singular forms "a", "an", "the above", "the", and "this" are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term "and / or" used in the present application refers to any or all possible combinations including one or more of the listed items.

[0029] Hereinafter, the terms "first" and "second" are only for descriptive purposes and cannot be construed as implying or suggesting relative importance or implicitly indicating the quantity of the indicated technical features. Thus, features defined with "first" and "second" may explicitly or implicitly include one or more of such features. In the description of the embodiments of the present application, unless otherwise specified, the meaning of "a plurality" is two or more.

[0030] For ease of understanding, the scenarios in the embodiments of the present application are introduced below. First, it is necessary to understand the characteristics of the OLAP (Online Analytical Processing) system. OLAP is a data processing method oriented to analysis and is mainly used to process complex multi-dimensional data analysis queries. In an OLAP system, a materialized view (MV) is an important means for query acceleration. Essentially, a materialized view is a pre-computed and stored database object (snapshot), which saves the query results in the form of a physical table. When a user queries, data can be directly obtained from these pre-computed results, thus significantly improving the query performance. During the query processing, the query optimizer of the database generates a logical plan, which is a query execution plan composed of relational algebra operators and describes the specific execution steps and order of the query. In these queries, the SPJG pattern is a typical query pattern, which includes four basic operations: table scan (Scan), projection (Project), join (Join), and aggregation (Aggregate). Among them, the aggregation operation can be divided into two categories: roll-upable aggregation functions (such as sum, count, min, max, etc.) and non-roll-upable aggregation functions (such as count distinct, avg distinct, stddev, etc.). The results of roll-upable aggregation functions can be further aggregated, while non-roll-upable aggregation functions cannot be directly aggregated twice.

[0031] To better represent and process query structures, the present invention proposes a PlanPiece system. A PlanPiece is an abstract base class that contains basic information such as a set of output columns and a set of filtering predicates. Based on PlanPiece, this application designs three specific subclasses: TablePiece is used to represent single-table operations; StarJoinPiece is used to describe multi-table join relationships, especially suitable for handling the join scenario between a fact table and multiple dimension tables in a star model; AggregatePiece is used to describe aggregation operations. It not only contains information such as regular metrics, distinct metrics, dimension columns, and roll-up dimension columns, but also introduces the concept of a FlatTable. The FlatTable is an important data structure used to describe the data source of aggregation operations. It classifies predicates into two categories: a set of stiff conjuncts with a fixed form and a set of flexible conjuncts. This classification helps the system better handle different types of filtering conditions.

[0032] To achieve flexible processing of aggregation operations, the present invention also proposes an AggregatePolicy. This is a policy system that adopts the idea of logic programming and contains multiple basic policies, such as IdentityPolicy (keeping the original value), SumPolicy (summation), AvgPolicy (average value), AndPolicy (condition combination); such as AndPolicy, OrPolicy, NotPolicy, and SeqPolicy. Through the combination of these policies, the system can flexibly handle various complex aggregation scenarios. In practical applications, the present invention provides multiple matching modes: PerfectMatch retains all predicate conditions and is suitable for scenarios that require exact matching; RollupMatch provides stronger generalization ability by removing flexible predicates and adding roll-up dimensions; PartiallyRollupMatch achieves a balance between accuracy and generalization ability by performing partial dimension roll-up while retaining time-related predicates.

[0033] The organic combination of these concepts and mechanisms enables the present invention to effectively identify equivalent query structures, eliminate redundancy, and flexibly generate appropriate materialized view creation statements according to actual needs. Through the predicate promotion technology of moving filtering conditions to higher-level predicates and the normalization process of converting query structures into standard forms, the system can better discover and process equivalent query structures, thereby improving the accuracy and efficiency of materialized view recommendation.

[0034] The following introduces the application scenarios of the embodiments of this application.

[0035] In the related art, multi-table association aggregation in an OLAP database system is a typical query operation. To improve the performance of such queries, the database system usually adopts the materialized view technology to pre-compute the association operation and aggregation operation in the query and store them in the form of a table. That is, the materialized view accesses the base tables associated with the query and pre-computes the results of the JOIN operator and AGGREGATE operator in the query. When the user initiates a query, the query engine can replace the part of the query that matches the materialized view with an access to the materialized view, thereby reducing or even eliminating the computational amount of obtaining data from the base tables for association and aggregation calculations and improving the query performance.

[0036] In the related art, the optimization of query performance can be achieved by adopting a materialized view recommendation method based on query frequency. The following introduces the scenario of using the materialized view construction method in the related art.

[0037] When users use the materialized view acceleration technology, the following needs to be considered when designing the table creation statement of the materialized view: 1. Extract the query substructure that can be accelerated by the MV. The query substructure refers to the partial plan in the query plan that satisfies a certain pattern (such as the SPJG pattern, which includes the query part with Scan, Project, Join, and Aggregate operators).

[0038] 2. Preprocess the query substructure to eliminate redundant query substructures and avoid recommending redundant MVs. Redundant query substructures refer to a group of query substructures that can be accelerated by the same MV. Affected by differences in text writing methods, filtering conditions, and expression calculations, after being processed by the optimizer of the query engine, non-equivalent logical plans are generated.

[0039] 3. Select the correct MV table creation statement. For the same query substructure, when designing the MV, there are multiple available MV table creation statements to choose from. Especially when the query substructure contains different predicates and the aggregate operator contains a non-roll-up aggregate function (such as count distinct), multiple factors such as the acceleration ability of the MV, the generalization ability of the MV, and the MV maintenance cost need to be considered.

[0040] However, by adopting the materialized view construction method in the embodiments of the present application, by converting the query into a standard plan slice structure, normalizing it, and performing intelligent merging, the efficient reuse of the materialized view is achieved, which not only improves the query performance but also reduces the system maintenance cost. The following introduces the scenario of using the materialized view construction method in the present application.

[0041] When using the materialized view construction method in the embodiments of the present application, the following aspects need to be comprehensively considered when designing the table creation statement of the materialized view: First, it is necessary to extract the query substructure that can be accelerated by the MV. The query substructure here refers to the part of the query plan that satisfies a certain pattern (such as the SPJG pattern, that is, the part of the plan containing Scan, Project, Join, and Aggregate operators). Second, it is necessary to preprocess the query substructure to eliminate redundancy. Due to the diverse ways users write queries, the same query may have differences in text writing methods, filtering conditions, and expression calculations. After being processed by the optimizer of the query engine, logically equivalent plans may be generated. These query substructures can actually be accelerated by the same MV. Finally, it is necessary to select the correct MV table creation statement. For the same query substructure, there may be multiple choices of table creation statements when designing the MV. Especially when the query substructure contains different predicates, or the aggregate operator contains a non-roll-up aggregate function (such as count distinct), it is necessary to comprehensively consider multiple dimensions such as the acceleration ability, generalization ability, and maintenance cost of the MV.

[0042] Based on this, please refer to Figure 1 , Figure 1 which is a schematic diagram of an application scenario of the materialized view construction method in the embodiments of the present application. The entire process starts with the SQL query information in text form (textual SQL). These queries are first processed and optimized by the optimizer (RboOptimizer) of the query engine to generate the initial logical plan. The generated logical plan then enters the plan piece processing stage. In this stage, the system first uses the plan piece builder (PlanPieceBuilder) to convert the logical plan into the initial plan piece format, and then standardizes these plan pieces through the plan piece normalizer (PlanPieceNormalizer). The standardized plan pieces can be merged to eliminate redundant query structures. Next, these processed plan pieces will be further transformed and optimized through the aggregate policy (AggregatePolicy) to adjust them into a form more suitable for generating materialized views. Finally, in the generation stage, the system generates the query part of the materialized view through the query generator (QueryGenerator), and the final materialized view table creation statement is constructed by the aggregate materialized view generator (AggregateMVGenerator). The entire process forms a complete closed loop, ensuring the efficient conversion from query to materialized view.

[0043] It can be seen that when using the materialized view construction method in the embodiments of the present application, while achieving query optimization, it can also effectively solve the problem that materialized views are difficult to reuse in traditional methods, thereby achieving the optimal balance between resource utilization and query efficiency.

[0044] For ease of understanding, the method provided in this embodiment will be described in terms of its process in combination with the above scenario. Please refer to Figure 2 , which is a schematic flowchart of the method for building a materialized view in an embodiment of the present application.

[0045] S201. Receive text query information, determine the execution order and execution type of the query operations in the text query information, and generate a logical plan.

[0046] Among them, the text query information represents the SQL statement written by the user, including information such as query targets, data sources, filtering conditions, and calculation logics; the query operations refer to various operation operators in the SQL statement, such as statement blocks like SELECT, FROM, WHERE, GROUPBY, etc.; the execution order is used to represent the dependency relationship and the sequence between each query operation; the execution type refers to the specific processing method of the query operation, including types such as table scan, data filtering, join calculation, aggregation statistics, etc.; the logical plan represents the tree-shaped execution structure after converting the text query, and is used to describe the complete processing flow of the query.

[0047] After receiving the user's query request, the data processing system needs to convert the SQL query in text form into an executable processing plan. Specifically, the data processing system first parses the SQL text to identify various query operations included therein; then analyzes the dependency relationship between these operations to determine their execution order; then, based on the semantic characteristics of each operation, determines its execution type; finally, organizes this information into a tree-shaped logical plan structure, where each node corresponds to a query operation, and the connections between the nodes represent the data flow relationship.

[0048] In some embodiments, the conversion from SQL query to logical plan can be achieved in multiple ways: Optionally, the SQL text can be first converted into an abstract syntax tree (AST), the query structure can be identified through lexical analysis and syntactic analysis, then the AST can be converted into a logical operator tree, and finally the operator order can be optimized and adjusted to obtain the final logical plan; Optionally, the keywords and expressions in the SQL can be directly identified using the rule matching method, the corresponding logical operators can be constructed, and then organized into a logical plan according to the dependency relationship between the operators. It can be understood that other parsing and conversion methods can also be used to process SQL queries, which are not limited herein.

[0049] In practical applications, SQL queries may contain complex subqueries or non-standard syntax structures, which increases the difficulty of generating logical plans. The data processing system can adopt query rewrite technology to convert complex queries into equivalent standard forms. The specific approach is as follows: First, establish a query rewrite rule library, which includes transformation rules such as subquery expansion, predicate pushdown, and expression simplification; then perform pattern matching on the input query to find applicable rewrite rules; finally, repeatedly apply these rules until no further optimization is possible. This can ensure that the generated logical plan has good structure and optimizability.

[0050] S202. Extract scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan that characterizes the query semantics.

[0051] Among them, the scan operation represents the process of reading raw data from the data source, including full table scan and index scan, etc.; the projection operation refers to the process of selecting required column fields; the join operation is used to represent the association relationship between multiple tables, including various types such as inner join and outer join; the aggregation operation represents the calculation of grouping and statistics on data, such as summation, counting, average value, etc.; the query sub-plan refers to the partial execution plan that meets a specific pattern extracted from the complete logical plan.

[0052] After obtaining the logical plan, the data processing system needs to identify the query parts in it that can be optimized using materialized views. Specifically, the data processing system first locates the sub-tree structure containing the SPJG pattern in the logical plan, that is, the execution path that sequentially includes the four operations of scan, projection, join, and aggregation; then analyzes the specific attributes of these operations, including the table object being scanned, the set of columns being projected, the join conditions, and the grouping and calculation logic of the aggregation; finally, reorganize this information into an independent query sub-plan while retaining the complete query semantics.

[0053] In some embodiments, the extraction of the query sub-plan can be achieved in multiple ways: Optionally, a bottom-up traversal method can be adopted, starting from the leaf nodes to check each execution path, and when a complete path that meets the SPJG pattern is found, extract it as the query sub-plan; Optionally, a pattern matching method can be adopted, pre-define the feature description of the SPJG pattern, and then search for the matching sub-tree structure in the logical plan and extract the matching part. It can be understood that other traversal and matching strategies can also be adopted to extract the query sub-plan, which is not limited here.

[0054] In some cases, there may be multiple execution paths that satisfy the SPJG pattern in the same logical plan, and there may be overlapping or inclusion relationships between these paths. In response to this situation, the data processing system needs to perform path selection and merging processing. The specific approach is as follows: First, calculate the cost and benefit of each SPJG path, including factors such as the amount of data involved and the computational complexity; then check the relationships between the paths to identify the set of paths that can be merged; finally, select the optimal path combination to generate the corresponding query sub-plan. This can avoid extracting redundant query structures and improve the efficiency of subsequent processing.

[0055] S203. Convert the query sub-plan into an initial plan fragment according to the operation type of the query sub-plan.

[0056] Among them, the query sub-plan represents the execution path of the SPJG pattern extracted from the logical plan; the operation type refers to the specific operation types included in the sub-plan, including single-table operations, multi-table join operations, and aggregation operations; the initial plan fragment represents the standardized data structure obtained after the preliminary conversion of the query sub-plan, including three types: table plan fragment, star join fragment, and aggregation plan fragment; the plan fragment conversion represents the process of mapping query operations to the corresponding plan fragment structure.

[0057] The data processing system needs to convert the query sub-plan into a plan fragment form that is convenient for subsequent processing. Specifically, the data processing system first analyzes the structural characteristics of the query sub-plan to determine its operation type; for single-table operations, generate a table plan fragment that contains the metadata information of the table and the filtering conditions; for multi-table join operations, generate a star join fragment that records the connection relationship between the central table and the dimension tables; for operations with aggregation, generate an aggregation plan fragment that saves the information of the metric columns, dimension columns, and aggregation functions. Each plan fragment contains two basic fields: columns (the set of output columns) and conjuncts (the set of filtering predicates).

[0058] In some embodiments, the conversion of the query sub-plan to the plan fragment can be implemented in multiple ways: Optionally, a recursive conversion method can be adopted. Starting from the root node of the sub-plan, create the corresponding plan fragment structure according to the node type, then recursively process the sub-nodes, and finally connect all the plan fragments into a complete structure; Optionally, a template matching method can be adopted. Pre-define the plan fragment templates corresponding to different operation types, and then fill the specific parameters in the sub-plan into the templates to generate the final plan fragment. It can be understood that other conversion strategies can also be adopted to generate the plan fragment, which is not limited here.

[0059] During the conversion process, it may be the case that complex connection relationships are difficult to directly map to a standard star structure. The data processing system can adopt connection relationship reorganization techniques. The specific approach is as follows: First, analyze the connection conditions between tables and construct a connection relationship graph; then, identify possible star patterns in the graph, including the central table and related dimension tables; for parts that do not conform to the star pattern, try to reorganize them into a star structure by adjusting the connection order or splitting the connections; finally, generate the corresponding star connection pieces. For cases where it is truly impossible to convert to a star structure, the original connection relationship is maintained.

[0060] S204. Normalize the initial plan piece to generate a standardized plan piece in a preset format.

[0061] Among them, the initial plan piece represents the original data structure converted from the query sub-plan; the normalization process refers to the process of converting the plan piece into a unified standard format; the preset format represents the standardized representation form predefined by the system; the standardized plan piece represents the plan piece structure after being processed by standardization, which is convenient for equivalence judgment and subsequent processing.

[0062] The data processing system needs to perform unified normalization processing on plan pieces from different sources and in different forms. Specifically, the data processing system first extracts the wide table information in the plan piece, including the table structure, field information, and filtering conditions; then traverses the entire plan piece structure from bottom to top, converts the information of each node into a string form in a preset fixed order; then processes various special structures, such as unifying the different orders of multi-table connections and standardizing equivalent filtering conditions; finally, generates a standardized plan piece based on the converted string and the original plan piece to ensure that queries with the same semantics can obtain the same standardized form.

[0063] In some embodiments, the normalization of the plan piece can be achieved in multiple ways: Optionally, the lexicographical normalization method can be adopted to sort and organize all the information in the plan piece (including table names, column names, filtering conditions, etc.) according to the predefined lexicographical rules to generate a unique standardized form; Optionally, the semantic normalization method can be adopted. First, analyze the query semantics of the plan piece, and then convert structures with different expressions but the same semantics into a unified standard form. It can be understood that other normalization strategies can also be used to process the plan piece, which is not limited here.

[0064] During the normalization process, a key issue is how to handle the predicate conditions in the plan slices, because the same filtering semantics may have multiple different expressions. The data processing system can adopt predicate normalization techniques. The specific approach is as follows: First, decompose the complex predicate expressions into atomic predicates; then simplify and standardize each atomic predicate, such as unifying the direction of comparison operators and merging overlapping value range conditions; next, recombine the standardized atomic predicates according to predefined rules; finally, generate predicate expressions in a unified format. This can ensure that predicate conditions with equivalent semantics can obtain the same canonical form.

[0065] S205. Perform redundancy elimination and merging processing on the canonical plan slices according to the preset aggregation strategy to obtain the target plan slices.

[0066] Among them, the preset aggregation strategy represents a set of transformation rules predefined by the system, used to guide the optimization and merging of plan slices; redundancy elimination and merging represent the processing process of eliminating redundancy and merging equivalent structures; the canonical plan slices refer to the plan slices in the standard format after normalization processing; the target plan slices represent the optimized plan slice structure finally used to generate the materialized view.

[0067] The data processing system needs to perform further optimization processing on the normalized plan slices. Specifically, the data processing system first groups the plan slices with the same standard form according to the canonical values; then merges the index columns and dimension columns of the plan slices within each group, including merging the roll-upable aggregation functions and associated dimension fields; next, obtains the preset aggregation logic expression to determine the applicable aggregation strategy; finally, performs roll-up transformation processing on the merged plan slices according to the selected strategy, adjusts the aggregation level and calculation logic to obtain the final target plan slices. This process takes into account multiple factors such as query performance, storage cost, and usage frequency.

[0068] In some embodiments, the redundancy elimination and merging processing of the plan slices can be implemented in multiple ways: Optionally, a rule-based merging strategy can be adopted, predefined a set of transformation rules (such as IdentityPolicy, AvgPolicy, AndPolicy, etc.), and select and apply appropriate rules for transformation according to the characteristics of the plan slices; Optionally, a cost-based merging strategy can be adopted, calculate the comprehensive cost (including computational complexity, storage overhead, etc.) for each possible merging scheme, and select the scheme with the optimal cost for transformation. It can be understood that other strategies can also be adopted to achieve the optimization and merging of plan slices, which are not limited here.

[0069] During the merge process, a typical problem is how to handle plan fragments containing non-roll-up aggregate functions (such as count distinct). The data processing system can adopt special aggregate function processing techniques. The specific approach is as follows: First, identify the non-roll-up aggregate functions in the plan fragment; then analyze the calculation characteristics and usage scenarios of these functions; for count distinct, consider retaining the underlying deduplicated data or using approximate calculation methods; for avg distinct, convert it into a combination of sum distinct and count distinct; finally, select an appropriate processing strategy according to actual requirements to balance accuracy and performance.

[0070] S206. Generate a table creation statement for the materialized view based on the target plan fragment to construct the materialized view.

[0071] Among them, the target plan fragment represents the final plan fragment structure after optimization processing; the table creation statement refers to the SQL statement used to create the materialized view; the materialized view represents a database object that pre-computes the query results and stores them in the form of a table; the construction process includes steps such as view creation, data calculation, and storage.

[0072] The data processing system needs to convert the optimized plan fragment into an actual executable materialized view definition. Specifically, the data processing system first analyzes the structural characteristics of the target plan fragment, including data sources, calculation logic, and output specifications; then generates a standardized SELECT statement based on this information, including the SELECT clause, FROM clause, WHERE clause, and GROUP BY clause; then adds necessary view management options, such as refresh strategies, storage parameters, etc.; finally, assembles them into a complete CREATE MATERIALIZED VIEW statement. The entire process needs to ensure that the generated table creation statement can accurately express the query semantics of the plan fragment and is convenient for the database system to execute and maintain.

[0073] In some embodiments, the generation of the materialized view table creation statement can be achieved in multiple ways: Optionally, a templated generation method can be adopted, where SQL templates corresponding to different types of plan fragments are predefined, and then the specific information in the plan fragment is filled into the template to generate the final table creation statement; Optionally, a progressive generation method can be adopted, where first a basic query structure is generated, and then filtering conditions, aggregate calculations, optimization hints, etc. are gradually added, and finally assembled into a complete table creation statement. It can be understood that other methods can also be used to generate the definition statement of the materialized view, which is not limited here.

[0074] During the creation of materialized views, it becomes a challenge to select appropriate refresh strategies and storage parameters. The data processing system can adopt an adaptive configuration technique. The specific approach is as follows: First, analyze the usage characteristics of the materialized view, including query frequency, data update mode, and storage capacity requirements; then, based on these characteristics, dynamically select the most suitable refresh method (such as full refresh or incremental refresh) and storage parameters (such as partitioning strategy, compression method, etc.) for each materialized view; finally, add this configuration information to the table creation statement. This can ensure that the materialized view can achieve the best performance in actual operation.

[0075] In the above embodiments, although the query substructure in the query engine adopts the same representation form as the query (i.e., the logical plan composed of relational algebra operators), this representation form can effectively solve the query optimization problem. However, in the MV recommendation scenario, the intermediate representation form of the query substructure also needs to focus on the following two aspects: On the one hand, it is the equivalence determination of the query substructure. By eliminating the leaf parts in the query substructure and retaining the stem parts, the query substructure is transformed into a standard form, so that query structures with the same standard form are equivalent, thereby avoiding creating redundant MVs for equivalent query substructures. On the other hand, it is the flexibility of the form. When there are multiple candidate MVs for the query substructure, the intermediate representation form needs to be able to flexibly transform into all possible MV table creation statements.

[0076] The following provides a further and more specific process description of the method provided in this embodiment. Please refer to Figure 3 , which is another process schematic diagram of the materialized view construction method in the embodiments of the present application.

[0077] S301. Receive text query information, determine the execution order and execution type of the query operation in the text query information, and generate a logical plan.

[0078] Referring to step S201, the data processing system will generate a logical plan.

[0079] S302. Extract scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing the query semantics.

[0080] Referring to step S202, the data processing system will determine the query sub-plan.

[0081] S303. According to the operation type of the query sub-plan, convert the query sub-plan into an initial plan slice.

[0082] Referring to step S203, the data processing system will generate an initial plan slice.

[0083] In some embodiments, the initial plan piece includes a table plan piece, a star join piece, and an aggregation plan piece; the data processing system performs classification processing, that is, when the query sub-plan is a single-table operation, the data processing system converts the query sub-plan into a table plan piece; when the query sub-plan is a multi-table join operation, the data processing system converts the query sub-plan into a star join piece; when the query sub-plan includes an aggregation operation, the data processing system generates an aggregation plan piece corresponding to the query sub-plan.

[0084] Please refer to Figure 4 , Figure 4 which is a schematic structural organization diagram of the plan piece in the embodiment of the present application; Figure 4 It details the structural design and relationships of three core plan piece types in the present invention. First is the basic plan piece (PlanPiece), which, as an abstract base class, contains two basic fields: a column set (columns) for storing output column information and a predicate set (conjuncts) for storing filtering conditions. On this basis, the table plan piece (TablePiece) extends the functions of the basic plan piece through inheritance. It additionally contains table fields for storing the complete metadata information of the database table, directly corresponding to the physical table in the database. The star join piece (StarJoinPiece), on the other hand, provides a more complex structure specifically for describing the connection relationships between multiple tables. In the star join piece, the centre table (centre) field points to the fact table, and the corners array describes the connection information with each dimension table. Each corner contains information such as the join type (joinType), equal join conditions (equalConjuncts), and non-equal join conditions (otherConjuncts). The bottom of the star join piece diagram shows the connection relationship between the partsupp, part, and supplier tables in TPC-H through a specific example, intuitively illustrating the actual application scenario of the star join piece.

[0085] In addition, please refer to Figure 5 , Figure 5 which is another schematic structural organization diagram of the plan piece in the embodiment of the present application; Figure 5Deeply demonstrates the complete structural design of the AggregatePiece. As a structure specifically for handling aggregation operations, the AggregatePiece contains four core field sets: The metrics set is used to store aggregation functions that can perform roll-up operations, such as sum, count, etc.; the distinctMetrics set is specifically used to store those aggregation functions that cannot directly perform roll-up operations, typically count distinct; the dimensions set stores the dimension columns of the aggregation operation; and the rollupDimensions set stores those dimension columns that can perform roll-up operations. In addition, the AggregatePiece also contains a key flat table structure, which is further divided into three parts: the stiffConjuncts set is used to store those fixed-form filtering conditions, the flexibleConjuncts set is used to store dynamic filtering conditions that may change over time, and a plan piece object pointing to the underlying data structure, which may be a TablePiece or a StarJoinPiece. This design not only ensures the flexibility of data processing but also maintains the clarity of the structure.

[0086] That is, the data processing system sets up an abstract class PlanPiece and its three subclasses: TablePiece, StarJoinPiece, and AggregatePiece. The query substructure that meets the SPJG pattern can be transformed into a tree structure composed of these three types. Among them, PlanPiece is the abstract base class, containing two main fields, columns and conjuncts, which represent all the columns output by this PlanPiece externally and the predicates for data filtering respectively. TablePiece corresponds to the Scan operator in the logical plan, and its table field stores the metadata information of the accessed table, such as table name, column information, partition information, etc. StarJoinPiece is used to represent the multi-table Join relationship, especially suitable for describing the association between a fact table and multiple dimension tables in the Star-Schema. Its centre field points to the fact table, and the corners field is an array of StarJoinCorner type. Each array element describes the Join information of a dimension table, including the type of Join, equality conditions, and non-equality conditions, etc. AggregatePiece corresponds to the aggregation operation and contains fields such as metrics (regular metrics), distinctMetrics (deduplicated metrics), dimensions (dimensions), and rollupDimensions (rollup dimensions). This design makes it more suitable for the materialized view recommendation scenario than the ordinary Aggregate operator.

[0087] Correspondingly, among them, single-table operation means query processing that only involves one data table; table plan piece means the standardized execution plan generated for single-table query; multi-table join operation refers to the association query that involves multiple data tables at the same time; star join piece is used to represent the standardized execution plan that takes one core table as the center and connects multiple dimension tables; aggregation operation means the processing logic that needs to perform grouped statistical calculations; aggregation plan piece refers to the standardized execution plan that contains aggregation calculations; query sub-plan means the local execution plan extracted from the original query.

[0088] The data processing system needs to convert the query sub-plan into a corresponding type of standard plan fragment structure according to its different characteristics. Specifically, the data processing system first analyzes the structural characteristics of the query sub-plan to determine whether it is a single-table operation, a multi-table join operation, or an operation involving aggregation; for a single-table operation, the data processing system extracts information such as the table access method, filter conditions, and output fields of the table, and constructs a corresponding table plan fragment; for a multi-table join operation, the data processing system analyzes the join relationship between the tables, identifies the core table and dimension tables, and constructs a star join fragment structure; for the case involving aggregation operations, the data processing system needs to extract information such as grouping fields, aggregation functions, and calculation expressions to generate an aggregation plan fragment. During the conversion process, the data processing system also needs to retain the filter conditions, field mappings, and calculation logic of the original query to ensure that the converted plan fragment can fully express the query semantics.

[0089] In some embodiments, the conversion of the query sub-plan to the plan fragment can be achieved in multiple ways: Optionally, a template matching conversion method can be adopted. First, define standard plan fragment templates for each type of query operation, then identify the operation type of the query sub-plan, then fill in the specific parameters in the query into the corresponding templates, and finally generate a complete plan fragment structure; Optionally, a recursive construction conversion method can be adopted. First, decompose the query sub-plan into basic operation units, then process each operation unit from bottom to top, then create corresponding plan fragment nodes according to the operation type, and finally connect these nodes into a complete plan fragment in the execution order of the query. It can be understood that other methods can also be used to implement the conversion operation of the query sub-plan to the plan fragment, which is not limited here.

[0090] S304. Extract the sub-fragment corresponding to the wide table in the initial plan fragment to obtain the plan fragment to be processed.

[0091] Among them, the initial plan fragment represents the original plan fragment structure converted from the query sub-plan; the wide table represents the logical data view formed after the table join operation, which contains the field information of all related tables; the sub-fragment refers to the local structural unit in the plan fragment, which contains specific operation logic; the plan fragment to be processed represents the sub-structure of the plan fragment that needs to be normalized.

[0092] Before the data processing system performs the normalization process, it needs to first locate and extract key data structures. Specifically, the data processing system first analyzes the overall structure of the initial plan fragment to identify the table join relationships contained therein; then locates the wide table structure formed by these join operations, which contains the complete path of data access; then extracts the operation logic related to this wide table, including field mappings, filter conditions, and calculation expressions, etc.; finally, organizes these extracted structures into the plan fragment to be processed to prepare for the subsequent normalization process.

[0093] In some embodiments, the extraction of wide-table related sub-pieces can be achieved in various ways: Optionally, a structure traversal method can be adopted. Starting from the root node of the initial planned piece, traverse downward along the data flow to identify and collect all operation nodes related to the wide table. Optionally, a marker propagation method can be used. First, mark the base table nodes involved in the wide table, then propagate the markers upward along the planned piece structure, and finally extract all the marked nodes and their associated information. It can be understood that other methods can also be used to achieve the extraction of related sub-pieces, which are not limited here.

[0094] During the extraction process, it is necessary to determine how to handle nested table join structures. The data processing system can adopt a hierarchical extraction strategy. The specific approach is as follows: First, construct a dependency graph of the table joins to identify direct and indirect connection relationships. Then, start from the innermost join and extract the relevant operation logic layer by layer outward. For each layer of join, it is necessary to retain its unique join conditions and filtering predicates. Finally, organize this hierarchical information into a structured planned piece to be processed. This can ensure that complex table join structures can be extracted completely and accurately, facilitating subsequent normalization processing. In addition, for join relationships with circular dependencies, appropriate cut-off points are also required to break the loop to ensure the terminability of the extraction process.

[0095] S305. Traverse the planned piece to be processed from bottom to top, and convert the field data in the planned piece to be processed into a string in a preset fixed order to obtain a canonical value.

[0096] Among them, traversing from bottom to top means starting from the leaf nodes of the planned piece and processing them level by level upward according to the hierarchical structure; field data refers to the structured information in the planned piece, including table names, field names, filtering conditions, calculation expressions, etc.; the preset fixed order refers to the standardized sorting rules predefined by the system to ensure that structures with the same semantics can obtain the same string representation; the canonical value refers to the string form after standardized conversion.

[0097] The data processing system needs to convert planned pieces in different forms but with the same semantics into a unified string representation. Specifically, the data processing system first traverses from the bottommost nodes of the planned piece to be processed, and these nodes are usually basic table access operations. Then, process the field data in each node according to the preset fixed order, including sorting the table names in lexicographical order, normalizing the column names, standardizing the filtering conditions, and unifying the representation of the calculation expressions. Then, splice the processed information into a normalized string form. Finally, combine these strings according to the dependency relationships between the nodes to generate the complete canonical value.

[0098] In some embodiments, the conversion of the planned slice to the specification value can be achieved in multiple ways: Optionally, a recursive conversion method can be adopted to normalize the information of each node, then recursively process its child nodes, and finally combine all the results in a fixed format; Optionally, a template filling method can be adopted, where string templates for different types of nodes are predefined, and then the normalized field information is filled into the corresponding templates. It can be understood that other conversion methods can also be used to generate the specification value, which is not limited here.

[0099] During the conversion process, a major problem is how to handle complex expressions and conditions. The data processing system can adopt expression normalization techniques. The specific approach is as follows: First, perform syntax parsing on the expression to obtain its internal operators and operands; then standardize the operands, such as unifying the numerical precision and normalizing the string format; then reorganize the expression structure according to the precedence and associativity of the operators; finally, generate a normalized string representation according to the predefined expression template. For complex predicates containing multiple conditions, they also need to be decomposed into atomic predicates and reorganized in the standard order. For example, for the condition "a>1 AND b<2 OR c=3", it is necessary to first decompose it into basic comparison expressions, and then reorganize it according to the precedence of the logical operators and the lexicographical order of the operands to ensure that conditions with equivalent semantics can obtain the same specification value.

[0100] S306. Generate a standardized planned slice based on the planned slice to be processed and the specification value.

[0101] Among them, the planned slice to be processed represents the original planned slice structure; the specification value represents the string form after standardization processing; the standardized planned slice represents a planned slice with a standard structure, which not only retains the original calculation semantics but also has a unified format representation, facilitating subsequent equivalence judgment and optimization processing.

[0102] The data processing system needs to construct a planned slice with a standard format based on the original structure and the normalized string. Specifically, the data processing system first parses the specification value string to extract the structured information contained therein, such as the access path of the table, the field mapping relationship, the filtering condition, and the calculation expression, etc.; then corresponds this information to the original semantics in the planned slice to be processed to ensure that the calculation logic of the query is not changed during the conversion process; then, according to the predefined planned slice template, reorganize the parsed information into a standard tree structure; finally, set necessary attribute tags for the newly generated planned slice, such as data type, calculation cost, etc. The whole process needs to ensure that the standardized planned slice not only maintains the complete semantics of the query but also has a unified structure representation.

[0103] In some embodiments, the generation of the standardized plan slices can be achieved in multiple ways: Optionally, the structural reconstruction method can be adopted. First, basic operation nodes are constructed according to the standardized values, and then these nodes are reconnected according to the dependency relationships of the original plan slices. Finally, the necessary attribute information is supplemented. Optionally, the in-situ conversion method can be adopted, directly performing structural adjustment and attribute update on the plan slices to be processed, and converting them into a form that meets the standardized requirements. It can be understood that other methods can also be adopted to generate the standardized plan slices, which are not limited herein.

[0104] During the process of generating the standardized plan slices, the reconstruction problem of complex expressions may be encountered. The data processing system can adopt expression reconstruction technology. The specific approach is as follows: First, the standardized expression string is parsed from the standardized values; then the operator precedence and associativity in the expression are analyzed; then the syntax tree structure of the expression is constructed based on this information; finally, the syntax tree is converted into an executable computing node. During this process, special attention needs to be paid to maintaining the calculation semantics of the expression unchanged, and at the same time ensuring that the generated structure meets the standardized requirements. For example, for a complex expression containing multiple calculation steps, the data type and precision requirements of the intermediate results need to be correctly processed to avoid calculation result deviations caused by the reconstruction process. For an expression containing custom functions or special operators, it is also necessary to ensure that these special semantics are correctly retained during the standardization process.

[0105] S307. Group the standardized plan slices with the same standardized values, and perform the merging process on the index columns and dimension columns of each group of slices to obtain the merged plan slices.

[0106] Among them, the same standardized value means that the standardized plan slices have exactly the same standardized string representation; the index column means the numerical fields that need to perform aggregation calculations; the dimension column means the descriptive fields used for grouping; the merging process refers to the process of integrating the calculation logics of multiple plan slices into a unified structure; the merged plan slices represent the optimized plan slice structure after merging.

[0107] The data processing system needs to merge and optimize the plan slices with the same semantics. Specifically, the data processing system first compares the standardized values of the standardized plan slices, groups the plan slices with the same standardized values into the same group; then analyzes the field attributes of the plan slices within each group, identifies the index columns and dimension columns; then performs the merging process on the index columns, including merging the same aggregation functions and processing duplicate calculation logics; at the same time, performs the merging process on the dimension columns to ensure that all necessary grouping dimensions are retained; finally, organizes the merged index columns and dimension columns into a new plan slice structure to form the optimized merged plan slices. This process needs to ensure that the merged plan slices can meet all the calculation requirements of the original plan slices at the same time.

[0108] In some embodiments, the merging process of the planned slices can be implemented in multiple ways: Optionally, a two-stage merging strategy can be adopted. In the first stage, the planned slices with exactly the same calculation logic are merged. In the second stage, the planned slices with partial overlap are processed, and the merging is achieved by appropriately expanding the calculation scope; Optionally, a progressive merging strategy can be adopted. Starting from the simplest metrics, the increasingly complex calculation logics are processed step by step, and it is ensured that the merged results meet the original requirements at each step. It can be understood that other strategies can also be adopted to implement the merging of the planned slices, which are not limited herein.

[0109] In the merging process, a key issue is how to handle different types of aggregation functions. The data processing system can adopt aggregation function merging techniques. The specific approach is as follows: First, identify various aggregation functions included in the planned slices, such as SUM, COUNT, AVG, etc.; then analyze the calculation characteristics of these functions to determine whether they can be directly merged or need to be merged after conversion; for simple aggregation functions (such as SUM, MAX), they can be directly merged; for composite aggregation functions (such as AVG), they need to be decomposed into basic aggregation functions (SUM / COUNT) before merging; for special aggregation functions (such as DISTINCTCOUNT), the original calculation logic may need to be retained. For example, when merging the planned slices containing AVG and SUM, it is necessary to ensure that sufficient intermediate calculation results are retained to support all required aggregation calculations. In addition, when merging dimension columns, the granularity level of the dimensions also needs to be considered to ensure that the merged planned slices can support data analysis requirements at different granularities.

[0110] S308. Obtain a preset aggregation logic expression and determine a preset aggregation strategy.

[0111] Among them, the aggregation logic expression represents a formal expression describing the aggregation calculation rules; the preset aggregation strategy represents a set of optimization rules predefined by the system for guiding the conversion of the planned slices; the roll-up transformation represents the process of converting low-granularity aggregation calculations into high-granularity ones; the target planned slice represents the final planned slice structure after aggregation optimization.

[0112] The data processing system needs to perform aggregation optimization on the planned slices according to predefined policies. Specifically, the data processing system first obtains the preset aggregation logic expressions from the configuration library, and these expressions define the conversion rules for different types of aggregation operations; then determines the applicable aggregation policies according to the semantics of the expressions, including IdentityPolicy (keeping the original value), SumPolicy (summation), AvgPolicy (average value), AndPolicy (conditional combination), etc.; then analyzes the aggregation operations in the merged planned slices to judge whether they meet the roll-up conditions; finally, converts the aggregation operations that meet the conditions according to the selected policy to generate the optimized target planned slices. The whole process needs to ensure that the calculation result after conversion is consistent with the original query semantics.

[0113] In some embodiments, the application of the aggregation policy can be implemented in multiple ways: Optionally, a rule matching method can be adopted to predefine a set of aggregation conversion rules and select and apply the corresponding rules according to the characteristics of the aggregation operations in the planned slices; Optionally, a cost evaluation method can be adopted to calculate the execution cost and benefits of each possible conversion scheme and select the optimal scheme for conversion. It can be understood that other ways can also be adopted to implement the application of the aggregation policy, which is not limited here.

[0114] During the aggregation optimization process, it is necessary to determine how to handle complex aggregation dependencies. The data processing system can adopt aggregation dependency analysis technology. The specific approach is: First, construct a dependency graph between aggregation operations to identify direct and indirect dependency relationships; then analyze the characteristics of each aggregation operation, including whether it is decomposable, whether it satisfies the associative law, etc.; then determine the execution order of the conversion according to the dependency relationship to ensure that the dependent aggregation operations are converted before the dependent parties; finally, gradually apply the conversion rules according to the determined order. For example, when processing a planned slice containing nested aggregations, it is necessary to start processing from the innermost aggregation and perform conversions layer by layer outward. For conversions that may cause changes in the calculation results, appropriate compensation calculations are also required to ensure the accuracy of the results. In addition, the system also needs to maintain the intermediate state during the conversion process to support possible rollback operations.

[0115] After that, the data processing system will perform roll-up conversion processing on the merged planned slices according to the preset aggregation policy to obtain the target planned slices. Correspondingly: S309. Extract the sub-slices corresponding to the wide table in the merged planned slices to obtain the merged sub-slices.

[0116] Among them, the merged planned slice represents the planned slice structure after the merging process; the sub-slice corresponding to the wide table represents the local calculation structure related to the wide table; the merged sub-slice represents the part that needs to be further processed extracted from the merged planned slice.

[0117] The data processing system needs to extract the key structures from the optimized plan slices. Specifically, the data processing system first analyzes the overall structure of the merged plan slices, identifies the calculation parts related to the wide table among them; then determines the boundary ranges of these parts, including the tables, fields, and calculation logics involved; then extracts these relevant structures completely to form independent merged sub-slices; finally, ensures that the extracted sub-slices retain the necessary context information for subsequent processing.

[0118] In some embodiments, the extraction of sub-slices can be achieved in multiple ways: Optionally, the marking propagation method can be adopted. First, mark the nodes related to the wide table, then propagate the marks along the data flow, and finally extract all the marked parts; Optionally, the structure matching method can be adopted. Pre-define the pattern features related to the wide table, and then search for the matching structures in the plan slices. It can be understood that other ways can also be adopted to achieve the extraction of sub-slices, which are not limited here.

[0119] During the extraction process, a key issue is how to ensure the integrity of the extraction. The data processing system can adopt dependency analysis technology. The specific approach is: First, construct a dependency relationship graph of each calculation node in the plan slice; then trace back its dependencies backward from the target node; finally, ensure that all necessary calculation logics are included in the extracted sub-slices.

[0120] S310. Identify the predicate types in the merged sub-slices that include atomic predicates, constant predicates, and aggregate predicates.

[0121] Among them, the atomic predicate represents the most basic comparison expression; the constant predicate represents the filtering condition containing a fixed value; the aggregate predicate represents the filtering condition involving aggregate calculations; the predicate type represents the classification of different types of filtering conditions.

[0122] The data processing system needs to classify and analyze the filtering conditions in the sub-slices. Specifically, the data processing system first scans all the filtering conditions in the merged sub-slices; then classifies them into three categories: atomic predicates (such as a>b), constant predicates (such as x=5), and aggregate predicates (such as SUM(y)>100) according to the characteristics of the conditions; then conducts further characteristic analysis on each type of predicate, including operand types, comparison operators, etc.; finally, annotates the type information for each predicate for subsequent optimization processing.

[0123] In some embodiments, the identification and classification of predicates can be achieved in multiple ways: Optionally, the syntax analysis method can be adopted to construct the syntax tree of the predicate and judge the predicate type according to the structural characteristics of the tree; Optionally, the pattern matching method can be adopted to pre-define the characteristic patterns of different types of predicates and determine the predicate type by matching. It can be understood that other ways can also be adopted to achieve the classification of predicates, which are not limited here.

[0124] During the recognition process, a common problem is how to handle compound predicates. The data processing system can adopt predicate decomposition techniques. The specific approach is as follows: First, decompose the compound predicate into basic logical expressions; then perform type judgment on each basic expression; finally, record the logical relationships between these basic expressions.

[0125] S311. Determine the corresponding preset aggregation strategy according to the predicate type, perform a roll-up transformation process on the merged plan slice to obtain the target plan slice.

[0126] Among them, the preset aggregation strategy represents the conversion rules predefined by the system for different types of predicates; the roll-up transformation process represents the process of converting low-level filtering conditions into high-level ones; the target plan slice represents the finally optimized plan slice structure.

[0127] The data processing system needs to select an appropriate optimization strategy according to the predicate type. Specifically, the data processing system first obtains the corresponding preset aggregation strategy from the configuration library according to the recognized predicate type; then analyzes the specific characteristics of each predicate to determine the applicable conversion rules; then performs conversion processing on the predicate according to the strategy requirements, which may include operations such as simplification, merging, and rewriting; finally, integrates the converted predicate into the plan slice to generate the optimized target structure.

[0128] In some embodiments, the conversion processing of predicates can be implemented in multiple ways: Optionally, a rule-based conversion method can be adopted, defining specific conversion rules for each predicate type and processing according to the rules; Optionally, a cost-based conversion method can be adopted, evaluating the execution efficiency of different conversion schemes and selecting the optimal scheme. It can be understood that other ways can also be used to implement the conversion of predicates, which are not limited here. During the conversion process, it is necessary to determine how to handle the dependency relationships between predicates. The data processing system can adopt dependency protection techniques. The specific approach is as follows: First, analyze the dependency relationships between predicates; then determine the execution order of predicate conversion; finally, maintain the necessary dependency information during the conversion process.

[0129] In some embodiments, the data processing system will perform compound strategy operations, that is, the data processing system will detect the aggregation operation types in the merged sub-slice to determine the set of aggregation operations to be processed; according to the set of aggregation operations, obtain the corresponding basic conversion strategies from the preset strategy library to obtain the basic strategy set; determine the strategy combination rules of the basic strategy set based on user settings to generate the compound conversion strategy; perform a roll-up transformation process on the merged plan slice according to the compound conversion strategy to obtain the target plan slice.

[0130] Among them, the aggregation operation type represents various types of aggregation functions included in the plan slice, such as SUM, COUNT, AVG, etc.; the aggregation operation set refers to the complete set of aggregation functions that need to be processed; the basic conversion strategy represents the optimization rules for a single aggregation function type; the basic strategy set is used to represent the set of all applicable basic optimization rules; the strategy combination rule refers to the combination method and priority between multiple basic strategies; the composite conversion strategy represents the complete optimization plan formed by combining multiple basic strategies; the roll-up conversion process refers to the process of converting low-level aggregation calculations into high-level ones.

[0131] After obtaining the merged sub-slice, the data processing system needs to analyze and optimize the aggregation operations therein. Specifically, the data processing system first scans all the calculation expressions in the merged sub-slice, identifies the types of aggregation functions included, such as SUM, COUNT, AVG, etc., and collects these functions into the set of aggregation operations to be processed; then, according to the function types in the aggregation operation set, it looks up the matching basic conversion strategies from the pre-configured policy library, and these strategies define the optimization rules for different aggregation functions; next, according to the parameters set by the user in the configuration file or interface, it determines how to combine these basic strategies, including the execution order, priority, and conflict handling method of the strategies; finally, the data processing system performs systematic conversion processing on the aggregation operations in the merged plan slice according to the generated composite conversion strategy, including operations such as decomposing complex aggregations, replacing aggregation functions, and adjusting the calculation order, and finally generates the optimized target plan slice. During the processing, the data processing system also needs to ensure that the calculation results after conversion are consistent with the original query.

[0132] In some embodiments, the optimization conversion of aggregation operations can be achieved in multiple ways: Optionally, a rule-based conversion method can be adopted. First, an equivalent conversion rule library for aggregation functions is constructed, then the characteristics and context environment of each aggregation operation are analyzed, then the applicable conversion rules are selected for processing, then the correctness of the conversion results is verified, and finally an optimized calculation structure is generated; Optionally, a cost-based conversion method can be adopted. First, a cost evaluation model for aggregation operations is established, then the execution cost of each possible conversion scheme is calculated, then the optimal conversion path is selected through a dynamic programming algorithm, then the conversion operation is performed according to the selected path, and finally a calculation scheme with the optimal cost is generated. It can be understood that other methods can also be used to achieve the optimization conversion of aggregation operations, which are not limited herein.

[0133] To handle aggregation operations more flexibly, the present application designs an AggregatePolicy mechanism. This mechanism adopts the form of logical programming and realizes customized modification of AggregatePiece by combining different Policies. AggregatePolicy includes three types of implementations: 1. Basic control classes: including the always - successful IdentityPolicy and the always - failing NonePolicy 2. Function implementation classes: such as AvgPolicy that converts avg to sum and count, etc., which can be extended according to requirements 3. Logical combination classes: including AndPolicy, OrPolicy, NotPolicy, and SeqPolicy, used to combine multiple basic Policies into composite Policies. This design enables the system to flexibly handle various aggregation scenarios, such as handling non - rollupable aggregation functions and optimizing query performance. At the same time, through the combination of Policies, more complex transformation logics can be achieved to meet different materialized view optimization requirements.

[0134] Please refer to Figure 6 , Figure 6 which is another schematic diagram of the application scenario of the materialized view construction method in the embodiments of this application; Figure 6 Through a specific query instance based on TPC - H, the actual processing process of the entire system is demonstrated. This query includes ordinary counting operations count(1) and distinct counting operations count(distinct l.l_partkey), and at the same time involves join operations of multiple tables such as lineitem, orders, customer, and nation. During the processing, the system identifies the time condition "l_shipdate>'1996 - 12 - 01'" as an elastic predicate for processing, while the geographical condition "n_name in ('UNITED STATES', 'CHINA')" is processed as a dimension column. The count(1) in the query is placed in the metrics set as a rollupable aggregation function, while the count(distinct l.l_partkey) is placed in the distinctMetrics set as a non - rollupable aggregation function. The lower part of the figure details the complete star - join relationship, clearly showing the hierarchical relationship and connection method between tables such as lineitem, orders, customer, and nation through multiple StarJoinPiece. This example fully demonstrates how the system processes a complex actual query and how various components work together.

[0135] S312. Generate a table - creating statement for the materialized view based on the target plan piece to construct the materialized view.

[0136] Referring to step S206, the data processing system constructs a materialized view.

[0137] In some embodiments, the data processing system performs a quick query based on a user request. That is, the data processing system establishes a mapping relationship between canonical values and materialized views to obtain a view mapping table; receives a user query request, extracts the query structure from the user query request to obtain a to-be-matched plan fragment; performs normalization processing on the to-be-matched plan fragment to obtain a to-be-matched canonical value; and determines a target materialized view according to the to-be-matched canonical value and the view mapping table.

[0138] Among them, the canonical value represents the standardized string representation of the plan fragment structure; the materialized view refers to the pre-computed and stored query result; the view mapping table is used to record the correspondence between the canonical value and the materialized view; the user query request represents the original query statement to be processed; the query structure refers to various operations in the query and the relationships between them; the to-be-matched plan fragment represents the part to be matched extracted from the user query; the to-be-matched canonical value represents the standardized representation of the to-be-matched plan fragment; and the target materialized view refers to the finally matched materialized view that can be used to optimize the query.

[0139] The data processing system needs to establish and maintain an index structure for the materialized views to quickly locate available materialized views. Specifically, the data processing system first calculates the canonical value for each materialized view and saves the correspondence between the canonical value and the view definition into the view mapping table; when receiving a user query request, the data processing system parses the query statement, extracts the operation structure therein, including information such as the access method of the table, field selection, filtering conditions, and calculation logic, to form a to-be-matched plan fragment; then performs the same normalization processing on this plan fragment as when creating the materialized view to obtain a standardized to-be-matched canonical value; finally, uses this canonical value to look up in the view mapping table to determine whether there is a materialized view that can be used to optimize the query. In this process, the data processing system also needs to consider factors such as the freshness and access rights of the materialized views.

[0140] In some embodiments, the matching search for materialized views can be implemented in various ways: Optionally, an exact matching method can be adopted. First, construct a hash index structure for the canonical value, then calculate the hash value of the to-be-matched canonical value, then look up the exactly matching item in the index, then verify the validity of the matching result, and finally return the found materialized view; Optionally, a pattern matching method can be adopted. First, convert the canonical value into a feature vector, then calculate the similarity between the to-be-matched canonical value and each item in the index, then select several candidate items with the highest similarity, then determine the best match through detailed semantic analysis, and finally return the selected materialized view. It can be understood that other ways can also be adopted to implement the matching search for materialized views, which are not limited herein.

[0141] In the embodiments of the present application, due to the innovative proposal of a query analysis framework based on planned slices and the combination of an intelligent aggregation strategy management mechanism, complex query requests can be converted into a standardized planned slice structure. Through normalization processing and optimized merging, accurate recognition of query semantics and efficient reuse of materialized views are achieved, effectively solving the problems existing in traditional materialized view methods, such as inaccurate query pattern recognition, low view reuse rate, and high maintenance costs. Furthermore, the overall performance of the data analysis system is optimized; not only is the query performance significantly improved, but the complexity of system maintenance is also reduced.

[0142] The data processing system in the embodiments of the present invention application will be described from the perspective of hardware processing. Please refer to Figure 7 , which is a schematic structural diagram of an entity device of the data processing system in the embodiments of the present application.

[0143] It should be noted that Figure 7 the structure of the data processing system shown is only an example and should not impose any limitations on the functions and usage scope of the embodiments of the present invention.

[0144] As Figure 7 shown, the data processing system includes a CPU 701, which can perform various appropriate actions and processes according to the program stored in the ROM 702 or the program loaded from the storage section 708 into the RAM 703, such as executing the method described in the above embodiments. In the RAM 703, various programs and data required for system operation are also stored. The CPU 701, ROM 702, and RAM 703 are connected to each other via a bus 704. The I / O interface 705 is also connected to the bus 704.

[0145] The following components are connected to the I / O interface 705: an input section 706 including an audio input device, a button switch, etc.; an output section 707 including a liquid crystal display (LCD), an audio output device, an indicator light, etc.; a storage section 708 including a hard disk, etc.; and a communication section 709 including a network interface card such as a LAN (Local Area Network) card, a modem, etc. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. A removable medium 711, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 710 as needed so that a computer program read from it can be installed into the storage section 708 as needed.

[0146] Specifically, according to an embodiment of the present invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, an embodiment of the present invention includes a computer program product that includes a computer program carried on a computer-readable medium, and the computer program includes a computer program for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through the communication section 709, and / or installed from the removable medium 711. When the computer program is executed by the CPU 701, various functions defined in the present invention are executed.

[0147] As described above, the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them; although the present application has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements for some of the technical features; and these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the various embodiments of the present application.

Claims

1. A method for constructing a materialized view, characterized in that Applied to a data processing system, the method includes: Receiving text query information, determining the execution order and execution type of the query operation in the text query information, and generating a logical plan; Extracting scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing query semantics; Converting the query sub-plan into an initial plan slice according to the operation type of the query sub-plan; Normalizing the initial plan slice to generate a standardized plan slice in a preset format; Performing redundancy elimination and merging processing on the standardized plan slice according to a preset aggregation strategy to obtain a target plan slice; Generating a table creation statement for a materialized view based on the target plan slice to construct the materialized view.

2. The method according to claim 1, wherein The initial plan slice includes a table plan slice, a star join slice, and an aggregation plan slice; The step of converting the query sub-plan into an initial plan slice according to the operation type of the query sub-plan specifically includes: When the query sub-plan is a single-table operation, converting the query sub-plan into a table plan slice; When the query sub-plan is a multi-table join operation, converting the query sub-plan into a star join slice; When the query sub-plan contains an aggregation operation, generating an aggregation plan slice corresponding to the query sub-plan.

3. The method according to claim 1, wherein The step of normalizing the initial plan slice to generate a standardized plan slice in a preset format specifically includes: Extracting the sub-slice corresponding to the wide table in the initial plan slice to obtain a plan slice to be processed; Traversing the plan slice to be processed from bottom to top, converting the field data in the plan slice to be processed into a string in a preset fixed order to obtain a standardized value; Generating a standardized plan slice based on the plan slice to be processed and the standardized value.

4. The method according to any one of claims 1 to 3, characterized in that, The step of performing redundancy elimination and merging processing on the standardized plan slice according to a preset aggregation strategy to obtain a target plan slice specifically includes: Grouping the standardized plan slices with the same standardized value, and performing merging processing on the index columns and dimension columns of each group of slices to obtain a merged plan slice; Obtaining a preset aggregation logical expression and determining a preset aggregation strategy; Performing roll-up conversion processing on the merged plan slice according to the preset aggregation strategy to obtain the target plan slice.

5. The method according to claim 4, wherein The step of performing roll-up conversion processing on the merged plan slice according to the preset aggregation strategy to obtain the target plan slice specifically includes: Extracting the sub-slice corresponding to the wide table in the merged plan slice to obtain a merged sub-slice; Identifying the predicate types including atomic predicates, constant predicates, and aggregation predicates in the merged sub-slice; Determining the corresponding preset aggregation strategy according to the predicate type, and performing roll-up conversion processing on the merged plan slice to obtain the target plan slice.

6. The method according to claim 5, characterized in that The step of determining the corresponding preset aggregation strategy according to the predicate type, and performing roll-up conversion processing on the merged plan slice to obtain the target plan slice specifically includes: Detecting the aggregation operation type in the merged sub-slice to determine the set of aggregation operations to be processed; Obtaining the corresponding basic conversion strategies from a preset strategy library according to the set of aggregation operations to obtain a set of basic strategies; Determining the strategy combination rule of the set of basic strategies based on user settings to generate a composite conversion strategy; Perform a roll-up transformation process on the merged plan slice according to the composite transformation strategy to obtain the target plan slice.

7. The method according to claim 1, characterized in that, After the step of generating a table creation statement for the materialized view based on the target plan slice to construct the materialized view, the method further includes: Establish a mapping relationship between the canonical values and the materialized view to obtain a view mapping table; Receive a user query request, extract the query structure in the user query request to obtain a plan slice to be matched; Perform a normalization process on the plan slice to be matched to obtain a canonical value to be matched; Determine a target materialized view according to the canonical value to be matched and the view mapping table.

8. A data processing system, characterized in that, The data processing system includes: one or more processors and a memory; the memory is coupled to the one or more processors, the memory is used to store computer program code, the computer program code includes computer instructions, and the one or more processors call the computer instructions to cause the data processing system to execute the method according to any one of claims 1-7.

9. A computer-readable storage medium, comprising instructions, characterized in that, When the instruction runs on the data processing system, it causes the data processing system to execute the method according to any one of claims 1-7.

10. A computer program product, characterized in that, When the computer program product runs on the data processing system, it causes the data processing system to execute the method according to any one of claims 1-7.

Images