Method and apparatus for creating materialized view of database

Abstract

This specification provides a method and apparatus for creating a materialized view of a database. The method involves obtaining the relational trees corresponding to each SQL statement in a set of SQL statements for a target database. Nodes in these relational trees represent operators required to complete the corresponding SQL statement. An initial relational forest is constructed based on the relational trees corresponding to each SQL statement. The initial relational forest is traversed, and several node pairs are formed based on the current node and its sibling nodes, as well as the child nodes of the current node and its sibling nodes. The structure of the initial relational forest is modified based on the description information of the operators represented by the two nodes in each node pair to obtain a target relational forest. A target relational path is determined from the target relational forest, and a materialized view of the target database is created based on the target SQL statement corresponding to the target relational path. This target database may store private data.

Description

Technical Field

[0001] This specification relates to one or more embodiments in the field of databases, and more particularly to a method and apparatus for creating a materialized view of a database. Background Technology

[0002] In database query optimization practices, creating materialized views is a common technique. The data in a materialized view is obtained by pre-computing fragments or entire blocks of user queries. After creating a materialized view, user queries targeting the original table can be redirected to the pre-computed data (materialized view), thus accelerating queries. The data in the materialized view can, for example, be privacy-sensitive data.

[0003] When creating materialized views, it is usually necessary to analyze and explore the SQL statements entered by users in order to extract the most suitable parts for pre-computation, thereby maximizing the return on investment in pre-computation and storage and query acceleration.

[0004] Therefore, there is a need to provide a more efficient method for creating materialized views of a database. Summary of the Invention

[0005] This specification describes one or more embodiments of a method for creating materialized views of a database, which can create more and better materialized views.

[0006] Firstly, a method for creating materialized views of a database is provided, including:

[0007] Obtain the relational trees corresponding to each SQL statement in a set of SQL statements for the target database. The nodes in the relational trees represent the operators that need to be executed to complete the corresponding SQL statement.

[0008] Based on the relation trees corresponding to each of the SQL statements, an initial relation forest is constructed.

[0009] Traverse the initial relation forest and form several node pairs based on the current node and its sibling nodes, as well as the child nodes of the current node and its sibling nodes;

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

[0011] The target relationship path is determined from the target relationship forest, and a materialized view of the target database is created based on the target SQL statement corresponding to the target relationship path.

[0012] Secondly, an apparatus for creating a materialized view of a database is provided, comprising:

[0013] The acquisition unit is used to acquire the relational trees corresponding to each SQL statement in a set of SQL statements for the target database, wherein the nodes in the relational trees represent the operators that need to be executed to complete the corresponding SQL statement;

[0014] The construction unit is used to construct an initial relation forest based on the relation trees corresponding to each of the SQL statements;

[0015] A traversal unit is used to traverse the initial relation forest and form several node pairs based on the current node and its sibling nodes, as well as the child nodes of the current node and its sibling nodes.

[0016] The modification unit is used to modify the structure of the initial relation forest based on the description information of the operators represented by the two nodes in each node pair, so as to obtain the target relation forest;

[0017] The determining unit is used to determine the target relationship path from the target relationship forest and create a materialized view of the target database based on the target SQL statement corresponding to the target relationship path.

[0018] Thirdly, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed in a computer, causes the computer to perform the method of the first aspect.

[0019] Fourthly, a computing device is provided, including a memory and a processor, wherein the memory stores executable code, and the processor executes the executable code to implement the method of the first aspect.

[0020] The method for creating materialized views of a database provided in one or more embodiments of this specification can obtain more and better relational paths for creating materialized views by modifying the structure of the relation forest formed by relational trees based on a set of SQL statements. Thus, this solution can greatly improve the diversity and accuracy of the created materialized views. Attached Figure Description

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

[0022] Figure 1 This is a schematic diagram illustrating an implementation scenario of one embodiment disclosed in this specification;

[0023] Figure 2A flowchart illustrating a method for creating a materialized view of a database according to one embodiment is shown;

[0024] Figure 3 This shows a relationship tree in one example;

[0025] Figure 4a The initial relation forest is shown in one example;

[0026] Figure 4b This illustrates a target relationship forest in one example;

[0027] Figure 4c This is illustrated in another example of a target relationship forest;

[0028] Figure 4d The target relationship forest is shown in another example;

[0029] Figure 5 A schematic diagram of an apparatus for creating a materialized view of a database according to one embodiment is shown. Detailed Implementation

[0030] The solution provided in this specification will now be described with reference to the accompanying drawings.

[0031] As mentioned earlier, materialized views can be created to optimize data queries against a database. Traditionally, materialized views are created using two main methods:

[0032] The first method creates a materialized view based on a single SQL statement. This method has the limitation of only being able to perform single-point optimization.

[0033] The second method involves human experts evaluating and creating the materialized view. However, this method is limited by human experience and time.

[0034] Given the drawbacks of both of the aforementioned approaches, the inventors of this application propose creating materialized views based on a set of SQL statements. Specifically, by modifying the structure of the relation forest formed by the relation trees based on a set of SQL statements, a better solution in batch space is obtained, which has a greater return on investment compared to methods based on single SQL statements. Furthermore, this approach can utilize computing power, thereby significantly improving the efficiency of materialized view creation.

[0035] Figure 1 This is a schematic diagram illustrating an implementation scenario of one of the embodiments disclosed in this specification. Figure 1 In this context, 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 that includes a query result; it can be a local copy of remote data used to optimize data queries against that database.

[0036] Figure 1 In this context, after the database engine receives an SQL statement from the client, it can analyze the query data of the SQL statement (such as data tables and query columns) and redirect the SQL statement to a materialized view to improve query efficiency.

[0037] Figure 2 A flowchart illustrating a method for creating a materialized view of a database according to one embodiment is shown. This method can be executed by any device, apparatus, platform, or cluster of devices with computing and processing capabilities. Figure 2 As shown, the method may include the following steps.

[0038] Step S202: Obtain the relational tree corresponding to each SQL statement in a set of SQL statements for the target database. The nodes in the relational tree represent the operators that need to be executed to complete the corresponding SQL statement.

[0039] Among these SQL statements, the individual SQL statements in the set of SQL statements above can be completely unrelated.

[0040] In one embodiment, each SQL statement in the aforementioned set of SQL statements can be input into a syntax parsing tool, which will then generate the corresponding relational trees. In a more specific embodiment, the syntax parsing tool can be Calcite (an open-source SQL parsing tool).

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

[0042] After obtaining the abstract syntax tree, the syntax parsing tool can convert it into a corresponding relational tree (also known as a relational algebra RelNode) based on the database's metadata. This metadata 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.

[0043] The relationship tree described above describes the flow path of data after it is retrieved from the data table. This flow path can cover operations such as filtering, aggregation, and joining.

[0044] Specifically, each node in the aforementioned relation tree represents an operator (i.e., an operation in the flow path, also known as atomic relational algebra), and the connecting edges between operators have a pointing relationship. These operators can include, but are not limited to, scan operators, filter operators, project operators, join operators, and aggregate operators.

[0045] It should be understood that each of the above operators corresponds one-to-one with a clause in an SQL statement. For example, the scan operator corresponds to the FROM clause, the filter operator corresponds to the WHERE clause, the mapping operator corresponds to the SELECT clause, the join operator corresponds to the JOIN clause, and the grouping operator corresponds to the GROUP BY clause.

[0046] It should be noted that since the above operators correspond to the clauses in the SQL statement, the corresponding SQL statement can be executed by iteratively executing each operator in the relation tree based on the pointing relationship between the nodes.

[0047] Finally, each operator in the above relation tree has corresponding descriptive information, which includes at least one of the following: operator type, input / output, and operator attributes.

[0048] Regarding the operator types mentioned above, each operator can represent an operator type, which may include scanning operators, filtering operators, and mapping operators, etc.

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

[0050] Finally, regarding the operator attributes mentioned above, for the scan operator, its operator attribute can be a data table; for the filter operator, its operator attribute can be a filter condition (expressed through 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; and for the grouping operator, its operator attribute can be a data column (also called a grouping column).

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

[0052] For example, suppose the SQL statement is as follows: `select a,b from tbl1 where c>10`, then the corresponding relation tree can be as follows: Figure 3As shown, the relational tree includes three operators: scan operator, filter operator, and project operator. 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).

[0053] It should be understood that, according to Figure 3 By defining the connection relationships between the three operators and iteratively executing the scan, filter, and project operators, the SQL statement can be executed.

[0054] Step S204: Construct an initial relation forest based on the relation trees corresponding to each SQL statement.

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

[0056] In one embodiment, a Directed Acyclic Graph (DAG) can be constructed as an initial relation forest based on the pointing relationships between nodes in the relation trees corresponding to each SQL statement and the operator attributes of the operators represented by the nodes. This initial relation forest may include multiple root nodes.

[0057] It should be understood that constructing a directed acyclic graph as described above is the process of adding nodes and their connecting edges. Adding a node means adding the nodes stored in each of the aforementioned relation trees, and adding connecting edges means adding the connecting edges stored in each relation tree. It should be noted that during the process of adding nodes, the input and operator attributes of the node to be added can be compared with those of the nodes already added. If they match, the node to be added is considered to have been added, and this process continues until all nodes and connecting edges stored in all relation trees have been added.

[0058] For example, suppose a set of SQL statements targeting a target database contains the following SQL statements:

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

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

[0061] 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)

[0062] Therefore, based on the relation trees corresponding to the four SQL statements mentioned above, the initial relation forest constructed can be as follows: Figure 4a As shown.

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

[0064] Step S206: Traverse the initial relation forest and form several node pairs based on the current node and its sibling nodes, as well as the child nodes of the current node and its sibling nodes.

[0065] In one embodiment, a depth-first traversal method can be used to traverse the initial relation forest. The depth-first traversal method starts from a node v in the graph (i.e., the initial relation forest) and visits node v; it then proceeds to visit the unvisited neighbors of node v in turn, and so on, until all nodes in the graph that are connected to node v by a path have been visited; if there are still unvisited nodes in the graph, the depth-first traversal is restarted from that unvisited node, until all nodes in the graph have been visited.

[0066] by Figure 4a For example, assuming the current node is filter1 in the second branch from the leftmost position, by traversing its sibling nodes and their child nodes, the following node pairs can be formed: [filter1, filter2], [filter1, join3], [filter1, object3], etc.

[0067] It should be noted that, according to the definition of the depth-first traversal method above, when the initial relation forest includes multiple root nodes, during the traversal of the initial relation forest, the nodes that are connected to the root node by a path will be traversed from each of the multiple root nodes until all nodes have been visited.

[0068] Step S208: Based on the description information of the operators represented by the two nodes in each node pair, modify the structure of the initial relation forest to obtain the target relation forest.

[0069] The aforementioned node pairs include the first node pair, and the modification of the initial relation forest structure includes performing one or more of the following:

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

[0071] If two nodes in the first node are sibling nodes (i.e., the two nodes have the same parent node), and the operator types represented by the two nodes are both mapping operators / grouping operators / connection operators, then create a new node and connection edge based on the two nodes.

[0072] If the two nodes in the first node pair are sibling nodes and the operators represented by the two nodes are both filtering operators, then create a connecting edge between the two nodes.

[0073] The description information of the aforementioned operators may include operator type, input / output, and operator attributes.

[0074] Regarding the first point above, after removing one of the two nodes, if the removed node is at the bottom level (i.e., a leaf node), then the following first operation can be performed: remove the connection edge between the removed node and its parent node. If the removed node is at the top level (i.e., the root node), then the following second operation can be performed: move the connection edge between the removed node and its child node to the other node and the child node of the removed node. If the removed node is at an intermediate level, then both the first and second operations can be performed simultaneously.

[0075] It should be understood that by performing the first step above, the structure of the initial relation forest can be simplified.

[0076] Regarding the second item above, creating new nodes and connecting edges specifically includes creating a new node with the same operator type as the two nodes, using the merged result of the operator attributes represented by the two nodes as the operator attributes of the new node, and creating connecting edges from the parent node of the two nodes to the new node, and connecting edges from the new node to the two nodes.

[0077] For example Figure 4a Taking the initial relation forest shown as an example, suppose the current node pair is: [project3, project5], that is, the two nodes in the node pair are sibling nodes, the operator type represented by the two nodes is mapping operator, and the operator attributes of node project3 are [a, b], and the operator attributes of node project5 are [b, c]. Then the target relation forest can be obtained as follows: Figure 4b As shown. Figure 4b In the process, a new node project35 was created, along with connecting edges from node join3 (the parent node of nodes project3 and project5) to the new node project35, and connecting edges from the new node project35 to nodes project3 and project5 respectively. Figure 4b In the example, the operator attributes of the new node project35 are [a,b,c], which is the result of merging the operator attributes of nodes project3 and project4.

[0078] Since the aforementioned mapping operators correspond to the select clauses, after modifying the initial forest structure (i.e., creating new nodes and connecting edges), based on the new node project35 and its operator attributes, the following select clause can be generated: select a,b,c. Based on node project3 (or node project5), the generated select clause is: select a,b (or select b,c). This demonstrates that the modified initial relation forest allows for the selection of more data for storage, thereby enhancing data storage value.

[0079] Also as Figure 4aTaking the initial relation forest shown as an example, suppose the current node pair is: [join2, join3], that is, the two nodes in this node pair are sibling nodes, the operator type represented by these two nodes is the join operator, and the operator attribute of node join2 is: [tabl1, tabl2], and the operator attribute of node join3 is: [tbl3]. Then the target relation forest can be obtained as follows: Figure 4c As shown. Figure 4c In the process, a new node join23 was created, along with connecting edges originating from node filter1 (the parent node of node join2), node filter2 (the common parent node of nodes join2 and join3), and node scan3 (the parent node of node join3) pointing to the new node join23, and connecting edges originating from the new node join23 pointing to nodes join2 and join3, respectively. Figure 4c In the example, the operator attributes of the new node join23 are: [tabl1,tabl2,tabl3], which is the result of merging the operator attributes of nodes join2 and join3.

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

[0081] Similarly, the structure of the initial relation forest can be modified based on the grouping operator. The modification method is similar to that based on the mapping operator and the join operator. Furthermore, based on the modified initial relation forest, SQL statements for creating lighter aggregate table views can be generated, which can greatly optimize data querying. This will not be elaborated further in this manual.

[0082] Regarding the third item above, if the first node pair includes the first node and the 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, then a connection edge from the second node to the first node is created.

[0083] For example Figure 4aTaking the initial relation forest shown as an example, suppose the current node pair is: [filter1, filter2], that is, the two nodes are sibling nodes, the operator type represented by these two nodes is the filter operator, and the operator attributes of node filter1 are [filter>5] and the operator attributes of node filter2 are [filter>10]. Then the target relation forest can be obtained as follows: Figure 4d As shown. Figure 4d Since [filter>10] implies [filter>5], a connection edge is created from node filter1 to node filter2.

[0084] It should be understood that the above explanation uses any first node pair as an example to illustrate the method of modifying the structure of the initial relation forest. Similarly, if other node pairs exist, the structure of the initial relation forest can also be modified based on other node pairs, which will not be elaborated on here.

[0085] Furthermore, it should be understood that when modifying the initial relation forest by performing the above-mentioned multiple operations, the resulting target relation forest includes more branches than the initial relation forest. Thus, this scheme can expand the candidate path set used to create materialized views. In other words, more and better materialized views can be created based on this scheme.

[0086] Step S210: Determine the target relationship path from the target relationship forest, and create a materialized view of the target database based on the target SQL statement corresponding to the target relationship path.

[0087] In one embodiment, nodes in the target relationship forest can be scored according to predetermined rules, with each score indicating the value of the corresponding node in improving data retrieval. Target nodes are selected based on their scores, and the path from the target node to the root node is determined as the target relationship path.

[0088] In a more specific embodiment, the aforementioned predetermined rules can be understood as conditions that node features must satisfy. Specifically, it can be determined whether each node feature conforms to the corresponding predetermined rule, resulting in multiple judgment results. For example, if the out-degree of a node is greater than a predetermined value, the judgment result that the predetermined rule is satisfied can be obtained, which can be represented by "1". Otherwise, if the out-degree of a node is not greater than the predetermined value, the judgment result that the predetermined rule is not satisfied can be obtained, which can be represented by "0". Of course, in practical applications, other values ​​can also be used to represent the judgment result that the predetermined rule is satisfied or not satisfied, and this description does not limit this.

[0089] Generally speaking, the number of the above-mentioned judgment results matches the number of predetermined rules.

[0090] In one embodiment, the weighted sum of each determination result is used as the comprehensive result. Then, 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.

[0091] It should be understood that after selecting the target nodes mentioned above, the path from the root node to the target node can be determined as the target relationship path. There can be multiple such target relationship paths.

[0092] Then, based on the pointing relationships of the connecting edges between each node in each target relationship path, and the operator attributes of the operators represented by the nodes, a target SQL statement can be uniquely determined.

[0093] by Figure 4b Taking the target relation forest shown as an example, assuming the selected target node is node join3, the determined target SQL statement can be:

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

[0095] Therefore, materialized views can be created based on the following SQL statements:

[0096] create table view_123as select*from(select*from tbl1 where x>10)jointbl2 on xxx

[0097] In the SQL statement for creating the materialized view above, the statement after `as` is the target SQL statement, and "view_123" is the name of the final materialized view. In one example, the name of the materialized view can be obtained by performing MD5 hashing on the logical plan of the above SQL statement.

[0098] Furthermore, because the SQL statement above contains "create table…", the data in the materialized view will eventually be stored in the data table.

[0099] It should be understood that in scenarios where data is updated periodically, the statements for creating materialized views described above can be executed periodically to update the stored data periodically.

[0100] In summary, the method for creating materialized views of a database provided in this specification can construct a batch space based on the relational tree of multiple SQL statements, and then search for optimal solutions within this batch space, thereby obtaining more and better relational paths for creating materialized views. In other words, this solution can transform the problem of constructing relational paths for creating materialized views into a problem of exploring the structure of a relational forest, thus providing a completely new approach to the creation of materialized views.

[0101] Corresponding to the above-described method for creating materialized views of a database, one embodiment of this specification also provides an apparatus for creating materialized views of a database, which are used to optimize data queries against the database. For example... Figure 5 As shown, the device may include:

[0102] The acquisition unit 502 is used to acquire the relational trees corresponding to each SQL statement in a set of SQL statements for the target database. The nodes in the relational trees represent the operators that need to be executed to complete the corresponding SQL statement.

[0103] Construction unit 504 is used to construct an initial relation forest based on the relation trees corresponding to each SQL statement.

[0104] Traversal unit 506 is used to traverse the initial relation forest and form several node pairs based on the current node and its sibling nodes, as well as the child nodes of the current node and its sibling nodes.

[0105] Modification unit 508 is used to modify the structure of the initial relation forest based on the description information of the operators represented by the two nodes in each node pair, so as to obtain the target relation forest.

[0106] The determination unit 510 is used to determine the target relationship path from the target relationship forest and create a materialized view of the target database based on the target SQL statement corresponding to the target relationship path.

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

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

[0109] The removal submodule 5082 is used to remove one of the two nodes in the first node pair when the description information of the operators represented by the two nodes is consistent.

[0110] Create submodule 5084 to create a new node and connection edge based on the two nodes in the first node pair, where the two nodes are sibling nodes and the operator types represented by the two nodes are both mapping operators / grouping operators / connection operators.

[0111] Submodule 5084 is also used to create a connection edge between two nodes when the two nodes in the first node pair are sibling nodes and the operators represented by the two nodes are both filtering operators.

[0112] In one embodiment, creating submodule 5084 is specifically used for:

[0113] 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 attributes of the new node. Also, create connecting edges from the parent nodes of the two nodes to the new node and connecting edges from the new node to the two nodes.

[0114] In this context, 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.

[0115] In one embodiment, the operator attribute of the above-mentioned filtering operator is the filtering condition, and the above-mentioned first node pair includes a first node and a second node; the submodule 5084 is further specifically used for:

[0116] 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, then create a connection edge from the second node to the first node.

[0117] In one embodiment, the building unit 504 is specifically used for:

[0118] Based on the pointing relationships between nodes in the relation trees 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 relation forest.

[0119] In one embodiment, the determining unit 510 is specifically used for:

[0120] According to predetermined rules, nodes in the target relation forest are scored, and the score indicates the value of the corresponding node in improving data query.

[0121] The target node is selected based on the score, and the path from the target node to the root node is determined as the target relationship path.

[0122] The functions of each functional unit of the apparatus in the above embodiments of this specification can be implemented through the steps of the above method embodiments. Therefore, the specific working process of the apparatus provided in one embodiment of this specification will not be repeated here.

[0123] The apparatus for creating materialized views of a database provided in one embodiment of this specification can greatly improve the diversity and accuracy of the created materialized views.

[0124] According to another embodiment, a computer-readable storage medium is also provided, on which a computer program is stored, which, when executed in a computer, causes the computer to perform a combination Figure 2 The method described.

[0125] According to another embodiment, a computing device is also provided, including a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, it implements a combination... Figure 2 The method described.

[0126] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the medium or device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0127] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0128] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of this specification. It should be understood that the above description is only a specific embodiment of this specification and is not intended to limit the scope of protection of this specification. Any modifications, equivalent substitutions, improvements, etc., made on the basis of the technical solution of this specification should be included within the scope of protection of this specification.

Claims

1. A method for creating a materialized view of a database, comprising: Obtain the relational trees corresponding to each SQL statement in a set of SQL statements for the target database. The nodes in the relational trees represent the operators that need to be executed to complete the corresponding SQL statement. Based on the relation trees corresponding to each of the SQL statements, an initial relation forest is constructed. Traverse the initial relation forest and form several node pairs based on the current node and its sibling nodes, as well as 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 relation forest is modified to obtain the target relation forest; The target relationship path is determined from the target relationship forest, and a materialized view of the target database is created based on the target SQL statement corresponding to the target relationship path. Each node pair includes the first node pair; Modifying the structure of the initial relation forest includes: If the description information of the operators represented by the two nodes in the first node pair is consistent, remove one of the two nodes; And / or, If 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 / connection operators, then create a new node and connection edge based on the two nodes.

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

3. The method according to claim 1, wherein, The process of creating new nodes and connecting edges based on these 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 attributes of the new node. Also, create connecting edges from the parent nodes of the two nodes to the new node and connecting edges from the new node to the two nodes.

4. The method according to claim 3, wherein, 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.

5. The method according to claim 1, wherein, The operator attribute of the filtering operator is the filtering condition; the first node pair includes the first node and the second node; Create a connecting edge between the two nodes, including: If the filtering condition of the filtering operator represented by the first node contains the filtering condition of the filtering operator represented by the second node, then a connection edge is created from the second node to the first node.

6. The method according to claim 1, wherein, The construction of the initial relation forest includes: Based on the pointing relationships between nodes in the relation trees 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 relation forest.

7. The method according to claim 1, wherein, Determining the target relationship path from the target relationship forest includes: According to predetermined rules, the nodes in the target relationship forest are scored, and the scores indicate the value of the corresponding nodes in improving data query. The target node is selected based on the scoring, and the path from the target node to the root node is determined as the target relationship path.

8. An apparatus for creating a materialized view of a database, comprising: The acquisition unit is used to acquire the relational trees corresponding to each SQL statement in a set of SQL statements for the target database, wherein the nodes in the relational trees represent the operators that need to be executed to complete the corresponding SQL statement; The construction unit is used to construct an initial relation forest based on the relation trees corresponding to each of the SQL statements; A traversal unit is used to traverse the initial relation forest and form several node pairs based on the current node and its sibling nodes, as well as the child nodes of the current node and its sibling nodes. The modification unit is used to modify the structure of the initial relation forest based on the description information of the operators represented by the two nodes in each node pair, so as to obtain the target relation forest; The determining unit is used to determine the target relationship path from the target relationship forest and create a materialized view of the target database according to the target SQL statement corresponding to the target relationship path; Each node pair includes the first node pair; The modification unit includes: The removal submodule is used to remove one of the two nodes in the first node pair if the description information of the operators represented by the two nodes is consistent. And / or, A submodule is created to create new nodes and connection edges based on the two nodes in the first node pair, where the two nodes are sibling nodes and the operators represented by the two nodes are both mapping operators / grouping operators / connection operators.

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

10. The apparatus according to claim 8, wherein, The creation of the 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 attributes of the new node. Also, create connecting edges from the parent nodes of the two nodes to the new node and connecting edges from the new node to the two nodes.

11. The apparatus according to claim 10, wherein, 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.

12. The apparatus according to claim 8, wherein, The operator attribute of the filtering operator is the filtering condition; the first node pair includes a first node and a second node; the creation submodule is further specifically used for: If the filtering condition of the filtering operator represented by the first node contains the filtering condition of the filtering operator represented by the second node, then a connection edge is created from the second node to the first node.

13. The apparatus according to claim 8, wherein, The building unit is specifically used for: Based on the pointing relationships between nodes in the relation trees 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 relation forest.

14. The apparatus according to claim 8, wherein, The determining unit is specifically used for: According to predetermined rules, the nodes in the target relationship forest are scored, and the scores indicate the value of the corresponding nodes in improving data query. The target node is selected based on the scoring, and the path from the target node to the root node is determined as the target relationship path.

15. A computer-readable storage medium having a computer program stored thereon, wherein, When the computer program is executed in the computer, it causes the computer to perform the method of any one of claims 1-7.

16. A computing device comprising a memory and a processor, wherein, The memory stores executable code, and when the processor executes the executable code, it implements the method of any one of claims 1-7.

Images