Creation method and apparatus for materialized view of database

Abstract

Embodiments of the present specification provide a creation method and apparatus for a materialized view of a database. In the creation method, relationship trees corresponding to respective SQL statements in a group of SQL statements for a target database are acquired, nodes in each relationship tree representing operators needing to be executed to complete the corresponding SQL statement. An initial relationship forest is constructed on the basis of the relationship trees corresponding to the respective SQL statements. The initial relationship forest is traversed, and a plurality of node pairs are formed on the basis of a current node and brother nodes thereof and child nodes of the current node and the brother nodes thereof. The structure of the initial relationship forest is modified on the basis of description information of operators represented by the two nodes in each node pair, to obtain a target relationship forest. A target relationship path is determined from the target relationship forest, and a materialized view of the target database is created on the basis of a target SQL statement corresponding to the target relationship path, wherein private data can be stored in the target database.

Description

Method and device for creating materialized view of database

[0001] This application claims priority to the Chinese patent application filed with the State Intellectual Property Office of China on July 14, 2023, with application number 202310871204.1 and application name “Method and device for creating materialized views of a database”, the entire contents of which are incorporated by reference into this application. Technical Field

[0002] One or more embodiments of this specification relate to the field of databases, and more particularly, to a method and apparatus for creating a materialized view of a database. Background Art

[0003] Creating materialized views is a common practice in database query optimization. The data in a materialized view is obtained by pre-calculating fragments or entire blocks of user queries. After creating a materialized view, user queries targeting the original table can be redirected to the pre-calculated data (materialized view) to accelerate queries. The data in this materialized view can, for example, be private data.

[0004] When creating a materialized view, it is usually necessary to analyze and explore the SQL statements entered by the user to extract the parts that are most suitable for precalculation. This can maximize the output ratio of precalculation, storage investment, and query acceleration returns.

[0005] Therefore, it is necessary to provide a more efficient method for creating a materialized view of a database.

[0006] Summary of the Invention

[0007] One or more embodiments of this specification describe a method for creating a materialized view of a database, which can create more and better materialized views.

[0008] In a first aspect, a method for creating a materialized view of a database is provided, comprising:

[0009] Obtaining each relationship tree corresponding to each SQL statement in a set of SQL statements for a target database, where a node in the relationship tree represents an operator required to be executed to complete the corresponding SQL statement;

[0010] Constructing an initial relationship forest based on the relationship trees corresponding to the respective SQL statements;

[0011] Traversing the initial relationship forest, and forming a plurality of node pairs based on the current node and its sibling nodes, and the child nodes of the current node and its sibling nodes;

[0012] Based on the description information of the operators represented by the two nodes in each node pair, the structure of the initial relational forest is modified to obtain a target relational forest;

[0013] A target relationship path is determined from the target relationship forest, and a materialized view of the target database is created according to a target SQL statement corresponding to the target relationship path.

[0014] In a second aspect, a device for creating a materialized view of a database is provided, comprising:

[0015] An acquisition unit, configured to acquire each relationship tree corresponding to each SQL statement in a set of SQL statements for a target database, wherein a node in the relationship tree represents an operator required to be executed to complete the corresponding SQL statement;

[0016] A construction unit, configured to construct an initial relationship forest based on the relationship trees corresponding to the respective SQL statements;

[0017] A traversal unit, configured to traverse the initial relationship forest and form a plurality of node pairs based on a current node and its sibling nodes, and child nodes of the current node and its sibling nodes;

[0018] a modification unit, configured to modify the structure of the initial relational forest based on description information of the operators represented by the two nodes in each node pair, to obtain a target relational forest;

[0019] The determining unit is configured to determine a target relationship path from the target relationship forest and create a materialized view of the target database according to a target SQL statement corresponding to the target relationship path.

[0020] According to a third aspect, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed in a computer, the computer is caused to execute the method according to the first aspect.

[0021] In a fourth aspect, a computing device is provided, comprising a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, the method of the first aspect is implemented.

[0022] One or more embodiments of this specification provide a method for creating a materialized view of a database. By modifying the structure of the relationship forest formed by each relationship tree based on a set of SQL statements, more and better relationship paths for creating materialized views can be obtained. Therefore, this solution can greatly improve the diversity and accuracy of the created materialized views. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] In order to more clearly illustrate the technical solutions of the embodiments of this specification, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of this specification. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0024] FIG1 is a schematic diagram of an implementation scenario of an embodiment disclosed in this specification;

[0025] FIG2 shows a flow chart of a method for creating a materialized view of a database according to one embodiment;

[0026] FIG3 shows a relationship tree in one example;

[0027] Figure 4a shows the initial relationship forest in an example;

[0028] Figure 4b shows a target relationship forest in an example;

[0029] FIG4 c shows a target relationship forest in another example;

[0030] FIG4 d shows a target relationship forest in yet another example;

[0031] FIG5 shows a schematic diagram of a device for creating a materialized view of a database according to one embodiment. DETAILED DESCRIPTION

[0032] The solution provided in this specification is described below in conjunction with the accompanying drawings.

[0033] As mentioned above, materialized views can be created to optimize database queries. Traditionally, materialized views are created using the following two methods:

[0034] The first method is to create a materialized view based on a single SQL statement. This method has the limitation of only being able to optimize a single point.

[0035] The second method is to have human experts perform the evaluation and create materialized views. This method is limited by human experience and energy.

[0036] Given the shortcomings of both of the aforementioned solutions, the inventors of this application propose creating materialized views based on a set of SQL statements. Specifically, by modifying the structure of the relationship forest formed by the relationship trees based on a set of SQL statements, they achieve optimal solutions in batch space. This approach offers a greater input-output ratio than methods based on single SQL statements. Furthermore, this approach leverages computer computing power, significantly improving the efficiency of materialized view creation.

[0037] Figure 1 is a schematic diagram of an implementation scenario for an embodiment disclosed herein. In Figure 1 , multiple materialized views can be pre-created for a database: Materialized View A, Materialized View B, ..., Materialized View N. Each materialized view can be a database object containing a query result, or a local copy of remote data, used to optimize data queries against the database.

[0038] In Figure 1, after the database engine receives an SQL statement from the client, it analyzes the query data (e.g., data tables and query columns) of the SQL statement and can redirect the SQL statement to a materialized view to improve query efficiency.

[0039] Figure 2 shows a flow chart of a method for creating a materialized view of a database according to one embodiment. The method can be performed by any device, equipment, platform, or device cluster with computing and processing capabilities. As shown in Figure 2, the method may include the following steps.

[0040] Step S202 : obtaining each relationship tree corresponding to each SQL statement in a set of SQL statements for the target database, wherein the nodes in the relationship tree represent operators required to be executed to complete the corresponding SQL statement.

[0041] The SQL statements in the above set of SQL statements may be completely unrelated.

[0042] In one embodiment, each SQL statement in the above set of SQL statements can be input into a syntax parsing tool, and then the corresponding relationship trees can be obtained. In a more specific embodiment, the syntax parsing tool here can be Calcite (an open source SQL parsing tool).

[0043] Specifically, for any first SQL statement among the aforementioned SQL statements, the syntax parsing tool first parses it into an Abstract Syntax Tree (AST). The AST is a tree-like representation used to describe the grammatical structure of an SQL statement. Each node of the AST represents a grammatical structure or SQL element in the SQL statement. For example, a node of the AST can be a data table name, a table operation name, and a data column.

[0044] After obtaining the abstract syntax tree, the parsing tool can convert it into a corresponding relational tree (also known as a relational algebra RelNode) based on the database metadata. The metadata here may include, but is not limited to, the database name, table name, column name, the number and type of attribute columns in the table, and the database to which they belong.

[0045] The above relationship tree is used to describe the flow path of data after it is taken out of the data table. This flow path can cover operations such as filtering, aggregation, and connection.

[0046] Specifically, each node in the above relational tree represents an operator (i.e., an operation in the flow path, also known as atomic relational algebra), and the edges connecting operators have a pointing relationship. Operators here may include but are not limited to scan operators, filter operators, map operators, join operators, and aggregate operators.

[0047] It should be understood that each of the above operators corresponds one-to-one to each clause in the SQL statement. For example, the scan operator corresponds to the From clause, the filter operator corresponds to the Where clause, the map operator corresponds to the Select clause, the join operator corresponds to the Join clause, and the group operator corresponds to the Group by clause.

[0048] It should be noted that since the above operators correspond to the clauses in the SQL statement, based on the directional relationship between the connecting edges between the nodes, the execution of the corresponding SQL statement can be completed by iteratively executing the operators in the relationship tree in sequence.

[0049] Finally, for the operators in the above relationship tree, they have corresponding description information, where the description information includes at least one of the following: operator type, input and output, and operator attributes.

[0050] Regarding the above operator types, each operator mentioned above may represent an operator type, that is, the operator type may include a scanning operator, a filtering operator, a mapping operator, and the like.

[0051] Next, regarding the above input and output, input refers to the incoming edge information of the node corresponding to the operator, and output refers to the outgoing edge information of the node corresponding to the operator.

[0052] Finally, regarding the above operator attributes, for the scan operator, its operator attribute can be a data table; for the filter operator, its operator attribute can be a filter condition (represented by an expression); for the mapping operator, its operator attribute can be a data column (also called a query column); for the join operator, its operator attribute can be a data table; for the grouping operator, its operator attribute can be a data column (also called a grouping column).

[0053] It should be noted that, since in the embodiments of this specification, there is a one-to-one correspondence between nodes and operators, in the following description of this specification, the operator attributes of an operator are also referred to as the operator attributes of the node corresponding to the operator.

[0054] For example, assume the SQL statement is as follows: select a, b from tbl1 where c>10. The corresponding relationship tree is shown in Figure 3. The relationship tree includes three operators: scan, filter, and project. The operator attribute of the scan operator is tbl1 (i.e., the data table), the operator attribute of the filter operator is c>10 (i.e., the filtering condition), and the operator attributes of the project operator are a and b (i.e., the data columns).

[0055] It should be understood that according to the directional relationship between the connection edges of the three operators in Figure 3, the above-mentioned scan operator, filter operator and project operator are iteratively executed in sequence to complete the execution of the above-mentioned SQL statement.

[0056] Step S204: construct an initial relationship forest based on the relationship trees corresponding to the SQL statements.

[0057] It should be noted that the relationship tree described in the embodiments of this specification is a data structure that stores the directional relationships between the connecting edges between nodes and the operator attributes of the operators represented, etc. It is not a true graph structure. Therefore, the above step S204 can be understood as the process of constructing a graph structure based on the data stored in the relationship tree.

[0058] In one embodiment, a directed acyclic graph (DAG) can be constructed as an initial relationship forest based on the directional relationship between the connecting edges of the nodes in each relationship tree corresponding to each SQL statement and the operator attributes of the operators represented by the nodes. The initial relationship forest may include multiple root nodes.

[0059] It should be understood that the above-mentioned construction of a directed acyclic graph is the process of adding nodes and their connecting edges. Among them, adding nodes means adding nodes stored in the above-mentioned relationship trees, and adding connecting edges means adding connecting edges stored in each relationship tree. It should be noted that in the process of adding nodes, the input and operator attributes of the node to be added can be compared with the input and operator attributes of the already added nodes. If they are consistent, the node to be added is considered to have been added, and so on, until all nodes and connecting edges stored in all relationship trees are added.

[0060] For example, suppose that the SQL statements in a set of SQL statements for the target database are as follows:

[0061] SQL1: select*from tbl1 join(select a,b,id from(select*from tbl2 where x>5)t1 join(select*from tbl2 where x>10)t2 on t1.id=t2.id)t3 on tbl1.id=t3.id

[0062] SQL2: select func(a)as col1 from(select a,b,id from(select*from tbl2 where x>5)t1 join(select*from tbl2 where x>10)t2 on t1.id=t2.id)t3 on tbl1.id=t3.id

[0063] SQL3: select sum(a)from(select a,b,c from(select*from tbl2 where x>10)t1 join tbl3 on t1.id=tbl3.id)

[0064] SQL4: select func(b)from(select a,b,c,d from(select*from tbl2 where x>10)t1 join tbl3 on t1.id=tbl3.id)

[0065] Then, based on the relationship trees corresponding to the above four SQL statements, the constructed initial relationship forest can be shown in FIG4a.

[0066] It should be noted that in the initial relationship forest, each relationship path from the root node to the branch node or leaf node can constitute a candidate path set for creating a materialized view.

[0067] Step S206 , traverse the initial relationship forest, and form several node pairs based on the current node and its sibling nodes, and the child nodes of the current node and its sibling nodes.

[0068] In one embodiment, a depth-first traversal method can be used to traverse the initial relationship forest. The depth-first traversal method is to start from a node v in the graph (i.e., the initial relationship forest) and visit the node v; then, starting from the unvisited adjacent nodes of the node v, perform a depth-first traversal of the graph until all nodes in the graph that have paths connecting to the node v have been visited; if there are still unvisited nodes in the graph at this time, the depth-first traversal is restarted starting from the unvisited node until all nodes in the graph have been visited.

[0069] Taking Figure 4a as an example, assuming that the current node is the filter1 node in the second branch starting from the leftmost side, then by traversing its sibling nodes and their child nodes, the following node pairs can be formed: [filter1, filer2], [filter1, join3], [filter1, prject3], etc.

[0070] It should be noted that according to the definition of the above-mentioned depth-first traversal method, when the initial relationship forest includes multiple root nodes, in the process of traversing the initial relationship forest, the nodes that have paths connected to the root nodes will be traversed starting from multiple root nodes respectively until all nodes are visited.

[0071] Step S208 : Based on the description information of the operators represented by the two nodes in each node pair, the structure of the initial relational forest is modified to obtain a target relational forest.

[0072] The node pairs include the first node pair, and modifying the structure of the initial relationship forest includes performing one or more of the following:

[0073] When description information of operators represented by two nodes in the first node pair is consistent, remove one of the two nodes;

[0074] If two nodes in the first node are sibling nodes (i.e., they have the same parent node), and the operator types of the operators represented by the two nodes are both mapping operators, grouping operators, or join operators, a new node and connecting edge are created based on the two nodes.

[0075] When two nodes in the first node pair are sibling nodes and the operator types of the operators represented by the two nodes are both filter operators, a connecting edge is created between the two nodes.

[0076] The description information of the above operator may include operator type, input and output, and operator attributes.

[0077] Regarding the first item above, after removing one of the two nodes, if the removed node is at the bottom layer, that is, it is a leaf node, then the following first operation can also be performed: remove the edge connecting the node to the parent node; if the removed node is at the top layer, that is, it is a root node, then the following second operation can also be performed: migrate the edge connecting the node to the child node to the connection between the other of the two nodes and the child node of the node. If the removed node is at an intermediate layer, then the above first and second operations can also be performed simultaneously.

[0078] It should be understood that by performing the first item above, the structure of the initial relationship forest can be simplified.

[0079] Regarding the second item above, creating a new node and connecting edges specifically includes creating a new node with the same operator type as the two nodes, and taking the merging result of the operator attributes of the operators represented by the two nodes as the operator attributes of the new node, as well as creating a connecting edge from the parent node of the two nodes to the new node, and a connecting edge from the new node to the two nodes.

[0080] Taking the initial relational forest shown in Figure 4a as an example, assume the current node pair is [project3, project5], where the two nodes in the pair are siblings, the operators represented by both nodes are mapping operators, and the operator attributes of node project3 are [a, b] and those of node project5 are [b, c]. The resulting target relational forest can be shown in Figure 4b. In Figure 4b, a new node project35 is created, along with an edge from node join3 (the parent node of nodes project3 and project5) pointing to the new node project35 and edges from the new node project35 pointing to nodes project3 and project5, respectively. In Figure 4b, the operator attributes of the new node project35 are [a, b, c], representing the merged operator attributes of nodes project3 and project4.

[0081] Because the mapping operators described above correspond to select clauses, after modifying the initial forest structure (i.e., creating new nodes and connecting edges), the following select clause can be generated based on the new node project35 and its operator attributes: select a, b, c. Meanwhile, based on node project3 (or node project5), the generated select clause is: select a, b (or select b, c). This shows that the modified initial relational forest allows for more data to be selected for storage, thereby increasing the value of data storage.

[0082] Taking the initial relational forest shown in Figure 4a as an example, assume the current node pair is [join2, join3]. That is, the two nodes in this node pair are siblings, the operators represented by these two nodes are both join operators, and the operator attributes of node join2 are [tabl1, tabl2], and the operator attributes of node join3 are [tbl3]. The resulting target relational forest is shown in Figure 4c. In Figure 4c, a new node join23 is created, along with edges pointing from nodes filter1 (the parent of node join2), filter2 (the common parent of nodes join2 and join3), and scan3 (the parent of node join3). Edges are also created from the new node join23 to nodes join2 and join3, respectively. In Figure 4c, the operator attributes of the new node join23 are [tabl1, tabl2, tabl3], representing the merged operator attributes of nodes join2 and join3.

[0083] Because the join operators correspond to join clauses, after modifying the initial forest structure (i.e., creating new nodes and connecting edges), the following join clause is generated based on the new node join23 and its operator attributes: tabl1 join tabl2 join tabl3. Meanwhile, the join clause generated based on node join2 is: tabl1 join tabl2. This shows that a wider data table can be created based on the modified initial relational forest.

[0084] Similarly, the structure of the initial relational forest can be modified based on the grouping operator. The modification method is similar to the modification method based on the mapping operator and the connection operator. Based on the modified initial relational forest, an SQL statement can be generated to create a lighter aggregate table view, which can greatly optimize data queries. This manual will not go into details here.

[0085] Regarding the third item above, when the first node pair includes a first node and a second node, and if the filtering condition of the filtering operator represented by the first node implies the filtering condition of the filtering operator represented by the second node, a connection edge is created from the second node to the first node.

[0086] Taking the initial relational forest shown in Figure 4a as an example, assume the current node pair is [filter1, filter2], where the two nodes are siblings, the operators represented by these two nodes are both filter operators, and the operator attributes of node filter1 are [filter>5] and node filter2 are [filter>10]. The resulting target relational forest is shown in Figure 4d. In Figure 4d, because [filter>10] implies [filter>5], a connection edge is created from node filter1 to node filter2.

[0087] It should be understood that the above is an explanation of the method for modifying the structure of the initial relationship forest using any first node pair as an example. Similarly, if there are other node pairs, the structure of the initial relationship forest can also be modified based on the other node pairs, and this manual will not repeat them here.

[0088] In addition, it should be understood that when the initial relational forest is modified by executing the above multiple steps, the resulting target relational forest includes more branches than the initial relational forest, so that this solution can expand the set of candidate paths for creating materialized views. In other words, more and better materialized views can be created based on this solution.

[0089] Step S210 , determining a target relationship path from the target relationship forest, and creating a materialized view of the target database according to a target SQL statement corresponding to the target relationship path.

[0090] In one embodiment, nodes in the target relationship forest may be scored according to predetermined rules, the scores indicating the value of the corresponding nodes in improving data querying. A target node is selected based on the scores of each node, and the path from the target node to the root node is determined as the target relationship path.

[0091] In a more specific embodiment, the above-mentioned predetermined rules can be understood as the conditions that the node characteristics need to meet. Specifically, it can be judged whether each node characteristic meets the corresponding predetermined rule, and multiple judgment results can be obtained. For example, if the out-degree of the node is greater than the predetermined value, it can be obtained that the predetermined rule is met, which can be represented by "1". Otherwise, if the out-degree of the node is not greater than the predetermined value, it can be obtained that the predetermined rule is not met, which can be represented by "0". Of course, in actual applications, other values ​​can also be used to represent the judgment results of whether the predetermined rule is met or not met, and this description does not limit this.

[0092] Generally speaking, the number of the above determination results matches the number of predetermined rules.

[0093] In one embodiment, a weighted sum is obtained by performing a weighted summation on each determination result to obtain a weighted summation result as a comprehensive result. Subsequently, it is determined whether the comprehensive result is greater than a predetermined threshold. If the determination result indicates that the comprehensive result is greater than the predetermined threshold, the node can be selected as the target node.

[0094] It should be understood that after the target node is selected, the path from the root node to the target node can be determined as the target relationship path. The number of the target relationship paths can be multiple.

[0095] Afterwards, based on the directional relationship between the connecting edges of each node in each target relationship path and the operator attributes of the operator represented by the node, a target SQL statement can be uniquely determined.

[0096] Taking the target relation forest shown in FIG4b as an example, assuming that the selected target node is node join3, the determined target SQL statement can be:

[0097] select*from(select*from tbl1 where x>10)join tbl2 on xxx

[0098] Thus, a materialized view can be created based on the following SQL statement:

[0099] create table view_123 as select*from(select*from tbl1 where x>10)join tbl2 on xxx

[0100] In the SQL statement for creating a materialized view, the statement following "as" is the target SQL statement, and "view_123" is the name of the materialized view that is ultimately created. In one example, the name of the materialized view can be obtained by performing an MD5 encryption on the logical plan of the SQL statement.

[0101] In addition, since the above SQL statement contains "create table...", the data in the materialized view will eventually be stored in the data table.

[0102] It should be understood that in a scenario where data is updated periodically, the above statement for creating a materialized view may be executed periodically to periodically update the stored data.

[0103] In summary, the method for creating a materialized view in a database, as provided in the embodiments of this specification, can construct a batch space based on the relationship trees of multiple SQL statements, then search for optimal solutions within this batch space, thereby obtaining more and better relationship paths for creating materialized views. In other words, this solution transforms the problem of constructing the relationship paths used to create a materialized view into the problem of exploring the structure of the relationship forest, thereby providing a new approach to creating materialized views.

[0104] Corresponding to the above-mentioned method for creating a materialized view of a database, one embodiment of this specification also provides a device for creating a materialized view of a database, wherein the materialized view is used to optimize data queries against the database. As shown in FIG5 , the device may include:

[0105] The acquisition unit 502 is configured to acquire each relationship tree corresponding to each SQL statement in a set of SQL statements for the target database, where a node in the relationship tree represents an operator required to be executed to complete the corresponding SQL statement.

[0106] The construction unit 504 is configured to construct an initial relation forest based on the relation trees corresponding to the respective SQL statements.

[0107] The traversal unit 506 is configured to traverse the initial relationship forest and form a plurality of node pairs based on the current node and its sibling nodes, and the child nodes of the current node and its sibling nodes.

[0108] The modifying unit 508 is configured to modify the structure of the initial relational forest based on the description information of the operators represented by the two nodes in each node pair to obtain a target relational forest.

[0109] The determining unit 510 is configured to determine a target relationship path from the target relationship forest and create a materialized view of the target database according to a target SQL statement corresponding to the target relationship path.

[0110] In one embodiment, the description information of the operator includes at least one of the following: operator type, input and output, and operator attributes.

[0111] In one embodiment, each node pair includes a first node pair, and the modifying unit 508 includes:

[0112] A removal submodule 5082 is configured to remove one of the two nodes in the first node pair when description information of the operators represented by the two nodes are consistent;

[0113] A creation submodule 5084 is configured to create a new node and a connection edge based on the two nodes when the two nodes in the first node pair are sibling nodes and the operator types represented by the two nodes are both mapping operators, grouping operators, or joining operators.

[0114] The creation submodule 5084 is further configured to create a connection edge between the two nodes in the first node pair when the two nodes are sibling nodes and the operator types of the operators represented by the two nodes are both filter operators.

[0115] In one embodiment, the creation submodule 5084 is specifically used to:

[0116] Create a new node of the same operator type as the two nodes, and use the merged result of the operator attributes of the operators represented by the two nodes as the operator attribute of the new node, and create a connecting edge from the parent node of the two nodes to the new node and a connecting edge from the new node to the two nodes.

[0117] The operator attribute of the mapping operator is the query column, the operator attribute of the grouping operator is the grouping column, and the operator attribute of the join operator is the data table.

[0118] In one embodiment, the operator attribute of the filter operator is a filter condition, and the first node pair includes a first node and a second node; the creation submodule 5084 is further specifically configured to:

[0119] If the filtering condition of the filtering operator represented by the first node implies the filtering condition of the filtering operator represented by the second node, a connection edge is created from the second node to the first node.

[0120] In one embodiment, the construction unit 504 is specifically configured to:

[0121] According to the directional relationship between the connection edges of the nodes in each relationship tree corresponding to each SQL statement and the operator attributes of the operators represented by the nodes, a directed acyclic DAG graph is constructed as the initial relationship forest.

[0122] In one embodiment, the determining unit 510 is specifically configured to:

[0123] Scoring nodes in the target relational forest according to predetermined rules, where the scores indicate the value of the corresponding nodes for improving data querying;

[0124] The target node is selected according to the score, and the path from the target node to the root node is determined as the target relationship path.

[0125] The functions of the functional units of the device in the above embodiment of this specification can be implemented through the steps of the above method embodiment. Therefore, the specific working process of the device provided by one embodiment of this specification will not be repeated here.

[0126] An embodiment of this specification provides a device for creating a materialized view of a database, which can greatly improve the diversity and accuracy of the created materialized views.

[0127] According to another embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed in a computer, the computer is caused to execute the method described in conjunction with FIG. 2 .

[0128] According to yet another embodiment, a computing device is provided, including a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, the method described in conjunction with FIG. 2 is implemented.

[0129] The various embodiments in this specification are described in a progressive manner. Similar portions between the various embodiments can be referenced to each other. Each embodiment focuses on the differences between the other embodiments. In particular, the description of the medium or device embodiments is relatively simple because they are generally similar to the method embodiments. For relevant portions, refer to the description of the method embodiments.

[0130] The foregoing description of this specification describes specific embodiments. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims can be performed in an order different from that described in the embodiments and still achieve the desired results. Furthermore, the processes depicted in the accompanying drawings do not necessarily require the specific order shown or the sequential order to achieve the desired results. In certain embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0131] The specific implementation methods described above further illustrate the purpose, technical solutions and beneficial effects of this specification. It should be understood that the above description is only the specific implementation method of this specification and is not intended to limit the scope of protection of this specification. Any modifications, equivalent replacements, improvements, etc. made on the basis of the technical solutions of this specification should be included in the scope of protection of this specification.

Claims

1. A method for creating a materialized view of a database, comprising: Obtaining each relationship tree corresponding to each SQL statement in a set of SQL statements for a target database, wherein a node in the relationship tree represents an operator required to be executed to complete the corresponding SQL statement; Constructing an initial relationship forest based on the relationship trees corresponding to the SQL statements; Traversing the initial relationship forest, and forming a plurality of node pairs based on the current node and its sibling nodes, and the child nodes of the current node and its sibling nodes; Based on the description information of the operators represented by the two nodes in each node pair, the structure of the initial relationship forest is modified to obtain a target relationship forest; A target relationship path is determined from the target relationship forest, and a materialized view of the target database is created according to a target SQL statement corresponding to the target relationship path.

2. The method according to claim 1, wherein: The description information of the operator includes at least one of the following: operator type, input and output, and operator attributes.

3. The method according to claim 1, wherein: The node pairs include a first node pair; and the modifying the structure of the initial relationship forest includes performing one or more of the following: When description information of operators represented by two nodes in the first node pair is consistent, removing one of the two nodes; When two nodes in the first node pair are sibling nodes and the operator types of the operators represented by the two nodes are mapping operators / grouping operators / connection operators, a new node and a connection edge are created based on the two nodes; When two nodes in the first node pair are sibling nodes and the operator types of the operators represented by the two nodes are both filter operators, a connection edge is created between the two nodes.

4. The method according to claim 3, wherein: The creating a new node and a connecting edge based on the two nodes includes: Create a new node with the same operator type as the two nodes, and use the merged result of the operator attributes of the operators represented by the two nodes as the operator attribute of the new node, and create a connecting edge from the parent node of the two nodes to the new node and a connecting edge from the new node to the two nodes.

5. The method according to claim 4, wherein: The operator attribute of the mapping operator is a query column, the operator attribute of the grouping operator is a grouping column, and the operator attribute of the connection operator is a data table.

6. The method according to claim 3, wherein: The operator attribute of the filtering operator is a filtering condition; the first node pair includes a first node and a second node; The step of creating a connection edge between the two nodes includes: If the filtering condition of the filtering operator represented by the first node implies the filtering condition of the filtering operator represented by the second node, a connection edge is created from the second node to the first node.

7. The method according to claim 1, wherein: The constructing of the initial relationship forest includes: According to the directional relationship of the connection edges between the nodes in each relationship tree corresponding to each SQL statement and the operator attributes of the operators represented by the nodes, a directed acyclic DAG graph is constructed as the initial relationship forest.

8. The method according to claim 1, wherein: The determining of the target relationship path from the target relationship forest includes: Scoring the nodes in the target relationship forest according to a predetermined rule, wherein the score indicates the value of the corresponding node for improving data query; A target node is selected according to the scoring, and a path from the target node to the root node is determined as a target relationship path.

9. A device for creating a materialized view of a database, comprising: An acquisition unit, used to acquire each relationship tree corresponding to each SQL statement in a set of SQL statements for a target database, wherein the nodes in the relationship tree represent operators required to be executed to complete the corresponding SQL statement; A construction unit, configured to construct an initial relationship forest based on the relationship trees corresponding to the SQL statements; A traversal unit, used to traverse the initial relationship forest and form a plurality of node pairs based on a current node and its sibling nodes, and child nodes of the current node and its sibling nodes; A modification unit, configured to modify the structure of the initial relationship forest based on description information of operators represented by two nodes in each node pair, to obtain a target relationship forest; The determination unit is used to determine a target relationship path from the target relationship forest, and create a materialized view of the target database according to a target SQL statement corresponding to the target relationship path.

10. The device according to claim 9, wherein: The description information of the operator includes at least one of the following: operator type, input and output, and operator attributes.

11. The device according to claim 9, wherein: The node pairs include a first node pair; the modification unit includes: A removal submodule, used for removing one of the two nodes in the first node pair when description information of operators represented by the two nodes are consistent; A creation submodule is used to create a new node based on the two nodes when the two nodes in the first node pair are sibling nodes and the operator types of the operators represented by the two nodes are mapping operators / grouping operators / connection operators. Points and connecting edges; The creation submodule is further used to create a connection edge between the two nodes when the two nodes in the first node pair are brother nodes and the operator types of the operators represented by the two nodes are both filtering operators.

12. The device according to claim 11, wherein The creation submodule is specifically used for: Create a new node with the same operator type as the two nodes, and use the merged result of the operator attributes of the operators represented by the two nodes as the operator attribute of the new node, and create a connecting edge from the parent node of the two nodes to the new node and a connecting edge from the new node to the two nodes.

13. The device according to claim 12, wherein: The operator attribute of the mapping operator is a query column, the operator attribute of the grouping operator is a grouping column, and the operator attribute of the connection operator is a data table.

14. The device according to claim 11, wherein: The operator attribute of the filtering operator is a filtering condition; the first node pair includes a first node and a second node; and the creation module is further specifically used for: If the filtering condition of the filtering operator represented by the first node implies the filtering condition of the filtering operator represented by the second node, a connection edge is created from the second node to the first node.

15. The device according to claim 9, wherein: The building blocks are specifically used for: According to the directional relationship of the connection edges between the nodes in each relationship tree corresponding to each SQL statement and the operator attributes of the operators represented by the nodes, a directed acyclic DAG graph is constructed as the initial relationship forest.

16. The device according to claim 9, wherein: The determining unit is specifically used for: Scoring the nodes in the target relationship forest according to a predetermined rule, wherein the score indicates the value of the corresponding node for improving data query; A target node is selected according to the scoring, and a path from the target node to the root node is determined as a target relationship path.

17. A computer-readable storage medium having a computer program stored thereon, wherein: When the computer program is executed in a computer, the computer is caused to execute the method according to any one of claims 1 to 8.

18. A computing device comprising a memory and a processor, wherein: The memory stores executable codes, and when the processor executes the executable codes, the method according to any one of claims 1 to 8 is implemented.

Images