A multi-table joint query method and device, electronic equipment and storage medium

Abstract

One or more embodiments of the present specification provide a multi-table joint query method and device, electronic equipment and storage medium. The method is applied to a data warehouse; the data warehouse stores a joint relation tree organized in the form of a multi-way tree; the joint relation tree includes leaf nodes for representing each physical table in a plurality of physical tables participating in multi-party computing, and non-leaf nodes for representing joint operations performed on the physical tables represented by the plurality of leaf nodes connected thereto; the method includes: receiving a query requirement for at least part of the plurality of physical tables, the query requirement indicating at least part of the physical tables and a specified query order; in response to the query requirement, traversing the leaf nodes corresponding to the at least part of the physical tables in the joint relation tree in the specified query order, and performing the joint operation corresponding to the non-leaf node connected to the physical table corresponding to each traversed leaf node on the physical table to obtain a multi-table joint query result.

Description

Technical Field

[0001] This specification relates to one or more embodiments in the field of multi-party computation technology, and in particular to a method, apparatus, electronic device and storage medium for multi-table joint query. Background Technology

[0002] To break down data silos between different organizations and leverage the greater value of data, cross-domain and cross-organizational data fusion can be implemented. In data fusion scenarios based on multi-party computation, users may not be able to know the original data content of the multiple physical tables involved in the multi-party computation, but only the multi-party computation results obtained by performing multi-party computation on the multiple physical tables, that is, the default multi-table joint query results among the multiple physical tables.

[0003] Furthermore, in multi-table join queries, different orderings of the operands and operators in the join operation can lead to different join query results. The default multi-table join result is the result obtained by performing join operations on the multiple physical tables according to the default query order among them.

[0004] In practical applications, users may not expect to obtain the default multi-table join query results among the multiple physical tables, but rather the multi-table join query results among a subset of the physical tables.

[0005] However, in related technologies, since the positions of each physical table in the multi-way tree are fixed, and the order of traversing the nodes in the multi-way tree is determined by the traversal method, it is difficult to flexibly traverse the multi-way tree according to different specified query orders if preorder traversal, inorder traversal, postorder traversal, etc. are used to traverse the multi-way tree. Therefore, it is impossible to obtain multi-table joint query results that can meet different query requirements. Summary of the Invention

[0006] This application provides a multi-table join query method applied to a data warehouse; the data warehouse stores a join tree representing the join relationships between multiple physical tables participating in multi-party computation; the join tree is organized in the form of a multi-way tree; wherein, the join tree includes: leaf nodes representing each physical table in the multiple physical tables, and non-leaf nodes representing the join operations performed on the physical tables represented by the multiple leaf nodes connected to it; the method includes:

[0007] Receive query requests for at least a portion of the plurality of physical tables; wherein the query requests are used to indicate the at least a portion of the physical tables, and to indicate a specified query order among the at least a portion of the physical tables;

[0008] In response to the query request, according to the specified query order, each leaf node in the stored union relation tree corresponding to each physical table in the at least part of the physical tables is traversed, and for each physical table corresponding to the traversed leaf node, a union operation corresponding to the non-leaf node connected to each traversed leaf node is performed to obtain a multi-table union query result that satisfies the query request.

[0009] This application also provides a multi-table join query apparatus applied to a data warehouse; the data warehouse stores a join tree for representing the join relationships between multiple physical tables participating in multi-party computation; the join tree is organized in the form of a multi-way tree; wherein, the join tree includes: leaf nodes for representing each physical table in the multiple physical tables, and non-leaf nodes for representing the join operations performed on the physical tables represented by the multiple leaf nodes connected to it; the apparatus includes:

[0010] A receiving unit is configured to receive query requests for at least a portion of the plurality of physical tables; wherein the query requests are used to indicate the at least a portion of the physical tables and to indicate a specified query order among the at least a portion of the physical tables;

[0011] The query unit is configured to respond to the query request by traversing each leaf node in the stored union relation tree corresponding to each physical table in the at least some physical tables according to the specified query order, and performing a union operation corresponding to the non-leaf nodes connected to each leaf node for each physical table corresponding to the traversed leaf node, so as to obtain a multi-table union query result that satisfies the query request.

[0012] This application also provides an electronic device, including a communication interface, a processor, a memory, and a bus, wherein the communication interface, the processor, and the memory are interconnected via the bus;

[0013] The memory stores machine-readable instructions, and the processor executes the above method by invoking the machine-readable instructions.

[0014] This application also provides a machine-readable storage medium storing machine-readable instructions, which, when called and executed by a processor, implement the above-described method.

[0015] Through the above embodiments, in response to a query request for at least a portion of the physical tables involved in multi-party computation, the leaf nodes corresponding to the at least a portion of the physical tables indicated by the query request can be traversed in the union tree according to the specified query order. For each leaf node, a union operation corresponding to its connected non-leaf nodes is performed to obtain a multi-table union query result that satisfies the query request. Therefore, in data fusion scenarios based on multi-party computation, users can accurately obtain multi-table union query results for a portion of the physical tables as needed, and these results can be output according to a specified query order, thereby improving the user experience of multi-table union queries. Attached Figure Description

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

[0017] Figure 1 This is a schematic diagram illustrating a virtual view as an exemplary embodiment;

[0018] Figure 2 This is a schematic diagram of a union relationship tree shown in an exemplary embodiment;

[0019] Figure 3 This is a flowchart illustrating a multi-table join query method as an exemplary embodiment;

[0020] Figure 4 This is a schematic diagram of the structure of an electronic device containing a multi-table join query device, as shown in an exemplary embodiment.

[0021] Figure 5 This is a block diagram illustrating a multi-table join query apparatus as an exemplary embodiment. Detailed Implementation

[0022] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0023] It should be noted that the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification in other embodiments. In some other embodiments, the methods may include more or fewer steps than described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments.

[0024] To break down data silos between different organizations and leverage the greater value of data, cross-domain and cross-organizational data fusion can be implemented. In data fusion scenarios based on multi-party computation, users may not be able to know the original data content of the multiple physical tables involved in the multi-party computation, but only the multi-party computation results obtained by performing multi-party computation on the multiple physical tables, that is, the default multi-table joint query results among the multiple physical tables.

[0025] In one possible embodiment, a virtual view can be used to display the default multi-table join query results between the multiple physical tables to the user. This virtual view can be understood as a special table that only stores the metadata of the multiple physical tables participating in the multi-party computation, without storing the original data content of each physical table. The virtual view can be used to represent horizontal and vertical join relationships between the multiple physical tables; wherein the horizontal and vertical join relationships can correspond to the union and join operations in SQL (Structured Query Language), respectively.

[0026] For example, see Figure 1 , Figure 1 This is a schematic diagram illustrating a virtual view as an exemplary embodiment. (As shown) Figure 1 As shown, the eight physical tables involved in the multi-party computation can be represented as t1, t2, t3, t4, t5, t6, t7, and t8, respectively. If the operator “∪” represents the union operation and the operator “×” represents the join operation, then as follows: Figure 1 The virtual view shown can be used to represent the default multi-table join result among the eight physical tables as "t1∪t2∪t3×t4×t5∪t6(t7×t8)".

[0027] It should be noted that in multi-table join queries, different order of the operands and operators in the join operation will lead to different results.

[0028] For example, the results of multi-table join queries represented by "t4×t5" and "t5×t4" are different; for common fields between physical tables t4 and t5, the fields in physical table t4 can be retained in the multi-table join query result "t4×t5", while the fields in physical table t5 can be retained in the multi-table join query result "t5×t4".

[0029] Since the virtual view can only be used to represent the default multi-table join query results between multiple physical tables—that is, the virtual view can only be used to represent the default multi-table join query results obtained by performing a join operation on the multiple physical tables according to the default query order (e.g., the default query order could be "t1→t2→t3→t4→t5→t6→t7→t8")—in a possible application scenario, if the user expects not the default multi-table join query results between the multiple physical tables, but needs to obtain the multi-table join query results between a subset of the multiple physical tables, then the user needs to specify the subset of physical tables that actually participate in the multi-table join query, as well as the order in which the subset of physical tables are placed in the join operation, in order to accurately obtain the multi-table join query results for the subset of physical tables.

[0030] In view of this, this specification aims to propose a technical solution for traversing the union relation tree according to a specified query order to obtain the results of a multi-table joint query for a portion of the physical tables in multiple physical tables involved in multi-party computation.

[0031] A data warehouse can store a union tree representing the joint relationships between multiple physical tables participating in multi-party computation; the union tree can be organized as a multi-way tree. The union tree may include leaf nodes representing the individual physical tables, and non-leaf nodes representing the joint operations performed on the physical tables represented by the connected leaf nodes.

[0032] For example, see Figure 2 , Figure 2 This is a schematic diagram illustrating a union relationship tree, as shown in an exemplary embodiment. For example... Figure 1 The horizontal and vertical associations between the eight physical tables represented in the virtual view shown can be represented as follows: Figure 2 The union tree shown. Figure 2As shown, the leaf nodes “t1”, “t2”, “t3”, “t4”, “t5”, “t6”, “t7”, and “t8” in the union relationship tree can be used to represent each of the eight physical tables participating in the multi-party computation; the non-leaf nodes “∪” and “×” in the union relationship tree can be used to represent the union operation or join operation performed on each physical table represented by each leaf node connected to them.

[0033] Specifically, the union operation performed on the physical tables represented by leaf nodes "t1", "t2", and "t3" can be a union operation represented by the non-leaf node "∪". The union operation performed on the physical tables represented by leaf nodes "t1", "t2", "t3", "t4", and "t5" can be performed by first executing a union operation represented by the non-leaf node "∪" on the physical tables represented by leaf nodes "t1", "t2", and "t3", and then executing a join operation represented by the non-leaf node "×" on the result of the union operation and the physical tables represented by leaf nodes "t4" and "t5". Further details will not be elaborated here.

[0034] In implementation, the data warehouse can receive query requests for at least some of the multiple physical tables; wherein the query request is used to indicate the at least some physical tables and to indicate a specified query order among the at least some physical tables; further, in response to the query request, the data warehouse can traverse each leaf node in the stored union relationship tree corresponding to each physical table in the at least some physical tables according to the specified query order, and perform a union operation corresponding to the non-leaf nodes connected to each traversed leaf node for each physical table corresponding to the traversed leaf node, so as to obtain a multi-table union query result that satisfies the query request.

[0035] For example, the eight physical tables involved in multi-party computation can be represented as t1, t2, t3, t4, t5, t6, t7, and t8, respectively. The data warehouse can receive query requests for at least a portion of these eight physical tables. These query requests can indicate that the at least a portion of the physical tables includes t1, t4, t6, and t8, and can also indicate a specified query order among the at least a portion of the physical tables as "t1→t4→t6→t8". Further, in response to the query request, the data warehouse can traverse the tables according to the specified query order "t1→t4→t6→t8". Figure 2The leaf nodes “t1”, “t4”, “t6”, and “t8” in the union relationship tree are shown. For each leaf node that is traversed, the corresponding non-leaf nodes are joined to perform the union operation, so as to obtain the multi-table union query result “t1×t4∪t6∪t8” that satisfies the query requirements.

[0036] Therefore, in the technical solution of this specification, in response to a query request for at least a portion of the physical tables involved in multi-party computation, the leaf nodes corresponding to the at least a portion of the physical tables indicated by the query request can be traversed in the union relationship tree according to the specified query order. Furthermore, a union operation corresponding to the non-leaf nodes connected to each traversed leaf node is performed on the physical table, thereby obtaining a multi-table union query result that satisfies the query request. Thus, in data fusion scenarios based on multi-party computation, users can accurately obtain multi-table union query results for a portion of the physical tables as needed, and the multi-table union query results can be output according to a specified query order, thereby improving the user experience of multi-table union queries.

[0037] It should be noted that the technical solution in this specification proposes a new method for traversing and outputting multi-way trees, supporting the output of nodes in the multi-way tree according to a specified query order, so as to obtain multi-table joint query results that can meet different query requirements. In related technologies, preorder traversal, inorder traversal, and postorder traversal are usually used to traverse and output multi-way trees.

[0038] The specific implementation methods for traversing the union tree using preorder, inorder, and postorder traversal are not detailed here; please refer to the relevant technologies for preorder, inorder, and postorder traversal. To enable those skilled in the art to better understand the technical solutions in the embodiments of this specification, only the default query order of preorder, inorder, and postorder traversal is briefly explained below.

[0039] For example, a binary tree can include three parts: the root node, the left subtree, and the right subtree. If D, L, and R are specified to represent traversing the root node, traversing the left subtree, and traversing the right subtree, respectively, then the default query order for preorder traversal is "D→L→R", the default query order for inorder traversal is "L→D→R", and the default query order for postorder traversal is "L→R→D".

[0040] Therefore, in related technologies, since the positions of each physical table in the multi-way tree are fixed, and the order of traversing the nodes in the multi-way tree is determined by the traversal method, it is difficult to flexibly traverse the joint relationship tree according to different specified query orders if preorder traversal, inorder traversal, postorder traversal, etc. are used to traverse the joint relationship tree. Thus, it is impossible to obtain multi-table joint query results that can meet different query requirements.

[0041] The present application will now be described through specific embodiments and in conjunction with specific application scenarios.

[0042] Please see Figure 3 , Figure 3 This is a flowchart illustrating a multi-table join query method as an exemplary embodiment.

[0043] In this specification, the method can be applied to a data warehouse; the data warehouse can store a union tree representing the union relationships between multiple physical tables participating in multi-party computation; the union tree can be organized in the form of a multi-way tree. The union tree can include: leaf nodes representing each physical table in the multiple physical tables, and non-leaf nodes representing the union operations performed on the physical tables represented by the multiple leaf nodes connected to it.

[0044] Specifically, the union operation may include union operations and / or join operations.

[0045] For example, the data warehouse can be a data warehouse built on a big data storage and computing system; the data warehouse can store, for example... Figure 2 The union tree shown. Figure 2 As shown, the leaf nodes “t1”, “t2”, “t3”, “t4”, “t5”, “t6”, “t7”, and “t8” in the union relationship tree can be used to represent each of the eight physical tables participating in the multi-party computation; the non-leaf nodes “∪” and “×” in the union relationship tree can be used to represent the union operation or join operation performed on each physical table represented by each leaf node connected to them.

[0046] It should be noted that, in the embodiments shown above, as Figure 2 As shown, the leaf nodes in the union tree are denoted as the table identifiers of the corresponding physical tables, and the non-leaf nodes in the union tree are denoted as the operators of the corresponding union operations. This is merely an exemplary description and does not impose any special limitations on this specification.

[0047] In one embodiment shown, the data warehouse may maintain a first data structure for storing the root subtree in the union relationship tree, a second data structure for storing the first mapping relationship, and a third data structure for storing the second mapping relationship; wherein, the first mapping relationship is the mapping relationship between a non-root node in the union relationship tree and its subtree; and the second mapping relationship is the mapping relationship between a subtree in the union relationship tree and the union operation corresponding to its root node.

[0048] For example, regarding such Figure 2 The union tree shown can be formed by combining each non-leaf node and its connected child nodes into a subtree; that is, leaf node "t1", leaf node "t2", leaf node "t3" and non-leaf node "∪" can be combined into subtree "sub1", subtree "sub1", leaf node "t4", leaf node "t5" and non-leaf node "×" can be combined into subtree "sub2", leaf node "t7", leaf node "t8" and non-leaf node "×" can be combined into subtree "sub3", and subtree "sub2", leaf node "t6", subtree "sub3" and non-leaf node "∪" can be combined into subtree "sub4".

[0049] Based on this, the data warehouse can maintain a first data structure rootSubtree, a second data structure nodeToSubtreeMap, and a third data structure subtreeToOperatorMap.

[0050] The first data structure, rootSubtree, can be used to store the root subtree in the union relationship tree, and can be denoted as: <sub4>.

[0051] The second data structure, nodeToSubtreeMap, can be used to store the first mapping relationship between non-root nodes and their subtrees in the union tree, which can be denoted as <non-root node, subtree containing the non-root node>, and can specifically include:<t1,sub1> ,<t2,sub1> ,<t3,sub1> ,<sub1,sub2> ,<t4,sub2> ,<t5,sub2> ,<t7,sub3> ,<t8,sub3> ,<t6,sub4> ,<sub2,sub4> ,<sub3,sub4> .

[0052] The third data structure, subtreeToOperatorMap, can be used to store the second mapping relationship between the subtrees in the union tree and the union operations corresponding to their root nodes. It can be denoted as <subtree, root node of the subtree>, and specifically can include:<sub1,∪> ,<sub2,×> ,<sub3,×> ,<sub4,∪> .

[0053] The method can perform the following steps:

[0054] Step 302: Receive query requests for at least a portion of the physical tables among a plurality of physical tables participating in multi-party computation; wherein the query requests are used to indicate the at least a portion of the physical tables and to indicate a specified query order among the at least a portion of the physical tables.

[0055] For example, the eight physical tables involved in multi-party computation can be represented as t1, t2, t3, t4, t5, t6, t7, and t8, respectively. The data warehouse can receive query requests for at least some of the eight physical tables. The query requests can be used to indicate that the at least some physical tables include t1, t4, t6, and t8, and can be used to indicate that the specified query order among the at least some physical tables is "t1→t4→t6→t8".

[0056] It should be noted that in the embodiments shown above, the at least some physical tables refer to four physical tables out of the plurality of physical tables. This is merely an exemplary description and does not represent a particular limitation on this specification. In practical applications, the at least some physical tables can be any number of physical tables out of the plurality of physical tables. Furthermore, in the embodiments shown above, the specified query order is "t1→t4→t6→t8," which is also merely an exemplary description and does not represent a particular limitation on this specification. In practical applications, for different query needs, the at least some physical tables can be instructed to be queried in any order.

[0057] In one embodiment shown, in step 302, the query requirement may be a sequence of table identifiers; the sequence of table identifiers may include table identifiers of at least a portion of the physical tables arranged in the specified query order.

[0058] For example, the data warehouse may receive a table identifier sequence [t1, t4, t6, t8] representing a query request for at least some of the physical tables. The table identifier sequence [t1, t4, t6, t8] may be used to indicate that the at least some physical tables include t1, t4, t6, and t8, and may be used to indicate that the specified query order among the at least some physical tables is "t1→t4→t6→t8".

[0059] Step 304: In response to the query request, according to the specified query order, traverse each leaf node in the stored union relation tree corresponding to each physical table in the at least some physical tables, and for each physical table corresponding to each traversed leaf node, perform a union operation corresponding to the non-leaf node connected to each traversed leaf node to obtain a multi-table union query result that satisfies the query request.

[0060] For example, upon receiving a query request for physical tables t1, t4, t6, and t8, in response to the query request, the system can traverse the tables in the specified query order "t1→t4→t6→t8" as indicated by the query request. Figure 2 The leaf nodes "t1", "t4", "t6", and "t8" in the union relationship tree are shown. For each leaf node that is traversed, the corresponding non-leaf nodes are joined to perform the union operation, so as to obtain the multi-table union query result "t1×t4∪t6∪t8" that satisfies the query requirements.

[0061] In one embodiment shown, if the union relationship tree has not yet been constructed before traversing and outputting it according to the specified query order, the union relationship tree can be generated based on the first data structure, the second data structure, and the third data structure maintained by the data warehouse.

[0062] In implementation, in step 304, before traversing each leaf node in the stored union tree corresponding to each physical table in the at least some physical tables according to the specified query order, the method may further include: reading the root subtree in the union tree stored in the first data structure; reading the first mapping relationship between the non-root node and its subtree in the union tree stored in the second data structure; reading the second mapping relationship between the subtree and the union operation corresponding to the root node in the union tree stored in the third data structure; and generating the union tree based on the read root subtree, the first mapping relationship, and the second mapping relationship.

[0063] For example, the data warehouse maintains a first data structure `rootSubtree`, a second data structure `nodeToSubtreeMap`, and a third data structure `subtreeToOperatorMap`. The first data structure `rootSubtree` stores the root subtree of the union relationship tree; the second data structure `nodeToSubtreeMap` stores the first mapping relationship between non-root nodes and their respective subtrees in the union relationship tree; and the third data structure `subtreeToOperatorMap` stores the second mapping relationship between subtrees and their corresponding root nodes and their associated union operations. In response to the query request, the first data structure `rootSubtree`, the second data structure `nodeToSubtreeMap`, and the third data structure `subtreeToOperatorMap` can be read first, and based on the read root subtree, the first mapping relationship, and the second mapping relationship, a query can be generated as follows: Figure 2 The union relationship tree shown.

[0064] In one embodiment, in step 304, the step of traversing each leaf node in the stored union relation tree corresponding to each physical table in the at least some physical tables according to the specified query order, and performing a union operation corresponding to the non-leaf nodes connected to each leaf node for each traversed leaf node to obtain a multi-table union query result that satisfies the query requirements, may specifically include: generating a query queue corresponding to the query requirements; wherein, the queue elements in the query queue include each leaf node corresponding to each physical table in the at least some physical tables, arranged according to the position of the corresponding physical table in the specified query order; dequeuing the queue element at the head of the query queue as the initial first node, and traversing the remaining queue elements in the query queue sequentially according to the specified query order, iteratively performing a queue element merging operation on the traversed queue elements until the query queue is empty, and determining the queue element merging result corresponding to the query queue as the multi-table union query result that satisfies the query requirements.

[0065] For example, in response to the query request for physical tables t1, t4, t6, and t8, where the query request indicates a specified query order among physical tables t1, t4, t6, and t8 as "t1→t4→t6→t8", a query queue [leaf node "t1", leaf node "t4", leaf node "t6", leaf node "t8"] (simply referred to as query queue [t1, t4, t6, t8]) corresponding to the query request can be generated; furthermore, the nodes located in the query queue can be... If the queue element "t1" at the head of the queue is dequeued as the initial first node, then the remaining queue elements in the query queue can be [t4, t6, t8]. Further, the remaining queue elements in the query queue can be traversed sequentially, and the queue element merging operation can be performed iteratively on the traversed queue elements until the query queue is empty. The traversal ends when the query queue is empty, and the merged queue element result "t1×t4∪t6∪t8" corresponding to the query queue can be determined as the multi-table joint query result that satisfies the query requirements.

[0066] In the embodiments shown above, the step of performing a queue element merging operation on the traversed queue elements may specifically include: dequeuing the traversed queue element as a second node, and determining whether the first node and the second node meet the merging condition; if the first node and the second node meet the merging condition, then performing a union operation corresponding to the root node of the subtree where the second node is located on the first node and the second node to obtain the queue element merging result corresponding to the query queue, and updating the first node to the queue element merging result; if the first node and the second node do not meet the merging condition, then inserting the first node into the tail of the query queue, and updating the first node to the second node.

[0067] Specifically, the merging conditions may include: the subtree containing the first node and the subtree containing the second node are the same subtree; or, the subtree containing the first node is a descendant tree of the subtree containing the second node.

[0068] Specifically, the step of performing a joint operation on the first node and the second node corresponding to the root node of the subtree where the second node is located, to obtain a queue element merging result corresponding to the query queue, may include: based on the operator of the joint operation corresponding to the root node of the subtree where the second node is located, taking the first node as the operation object on the left side of the operator and the second node as the operation object on the right side of the operator, and performing a joint operation on the first node and the second node; and determining the joint operation result as the queue element merging result corresponding to the query queue.

[0069] For example, after dequeuing the queue element "t1" at the head of the query queue [t1, t4, t6, t8] as the initial first node, the queue element "t4" that has been traversed can be dequeued as the second node. Since the subtree "sub1" where the first node "t1" is located is a descendant of the subtree "sub2" where the second node "t4" is located, it can be determined that the first node "t1" and the second node "t4" satisfy the merging condition. Then, the join operation corresponding to the root node "×" of the subtree "sub2" can be performed on the first node "t1" and the second node "t4" to obtain the queue element merging result "t1×t4". In addition, the first node can be updated from "t1" to "t1×t4".

[0070] Continuing with the example described above, the queue element "t6" can be dequeued as the second node. Since the subtree "sub4" containing the first node "t1×t4" and the subtree "sub4" containing the second node "t6" are the same subtree, it can be determined that the first node "t1×t4" and the second node "t6" satisfy the merging condition. Therefore, the union operation corresponding to the root node "∪" of the subtree "sub4" can be performed on the first node "t1×t4" and the second node "t6" to obtain the queue element merging result "t1×t4∪t6". In addition, the first node can be updated from "t1×t4" to "t1t4∪t6".

[0071] In another embodiment shown, to avoid an infinite loop during the traversal process, a preset symbol can be added to the query queue; however, since it is not possible to perform a queue element merging operation on the preset symbol, it is necessary to first determine whether the second node currently dequeued is the preset symbol during the iteration process. If the second node is not the preset symbol, then a queue element merging operation is further performed on the second node.

[0072] In implementation, the queue elements in the query queue may further include a preset symbol located at the tail of the query queue. In this case, before determining whether the first node and the second node meet the merging condition, the method may further include: determining whether the second node is the preset symbol; if the second node is the preset symbol, inserting the first node into the tail of the query queue and updating the first node to the second node, ending the current iteration; if the second node is not the preset symbol, determining whether the first node and the second node meet the merging condition.

[0073] For example, the preset symbol can be EOF; in response to the query request for physical tables t1, t4, t6, and t8, where the query request indicates that the specified query order among physical tables t1, t4, t6, and t8 is "t1→t4→t6→t8", a query queue [t1, t4, t6, t8, EOF] corresponding to the query request can be generated. After dequeuing the traversed queue element as the second node, it can be determined whether the second node is EOF; if the second node is EOF, the first node (i.e., the queue element traversed in the previous iteration) can be inserted into the tail of the query queue, and the first node can be updated to EOF, ending the current iteration and continuing to traverse the next queue element located at the head of the query queue; if the second node is not EOF, it can be further determined whether the first node and the second node meet the merging condition.

[0074] In another embodiment shown, when the preset symbol is added to the query queue, since the queue element merging operation cannot be performed on the preset symbol, it may be necessary to determine whether the first node (i.e., the queue element traversed in the previous iteration) is the preset symbol during the iteration process. If the first node is not the preset symbol, the queue element merging operation is further performed on the second node.

[0075] In implementation, before determining whether the first node and the second node meet the merging condition, the method may further include: determining whether the first node is the preset symbol; if the first node is the preset symbol, inserting the first node into the tail of the query queue and updating the first node to the second node, ending the current iteration; if the first node is not the preset symbol, determining whether the first node and the second node meet the merging condition.

[0076] For example, after dequeuing the traversed queue element as the second node, if it is determined that the second node is not EOF, it can be further determined whether the first node is EOF; if the first node is EOF, it can be inserted into the tail of the query queue, and the first node can be updated to the currently traversed queue element, ending the current iteration and continuing to traverse the next queue element located at the head of the query queue; if the first node is not EOF, it can be further determined whether the first node and the second node meet the merging condition.

[0077] In one possible embodiment, since the process of merging queue elements in the query queue is similar to the process of forming a new subtree from the first node, the second node, and the root node of the subtree where the second node is located, if the first node is a new subtree obtained through the queue element merging operation when the second node is the preset symbol, the first node can be directly inserted into the tail of the query queue; if the first node is a node that has not undergone the queue element merging operation, the first node can be "upgraded" to the subtree where the first node is located, and then the "upgraded" first node can be inserted into the tail of the query queue.

[0078] In implementation, inserting the first node into the tail of the query queue may specifically include: determining whether the first node is the result of merging queue elements; if the first node is the result of merging queue elements, then inserting the first node into the tail of the query queue; if the first node is not the result of merging queue elements, then updating the first node to the subtree where the first node is located, and inserting the first node into the tail of the query queue.

[0079] In another possible embodiment, when the first node is the root subtree in the union tree, since the root subtree cannot be "upgraded" and the queue elements cannot be merged with the traversed queue elements, the root tree can be directly inserted into the tail of the query queue.

[0080] In implementation, before updating the first node to the subtree where the first node is located, the method may further include: determining whether the first node is the root subtree in the union relationship tree; if the first node is the root subtree in the union relationship tree, inserting the first node into the tail of the query queue and ending the current iteration; if the first node is not the root subtree in the union relationship tree, updating the first node to the subtree where the first node is located.

[0081] As can be seen from the above technical solution, in response to a query request for at least a portion of the physical tables involved in multi-party computation, the leaf nodes corresponding to the at least a portion of the physical tables indicated by the query request can be traversed in the union relationship tree according to the specified query order. For each leaf node, a union operation corresponding to its connected non-leaf nodes is performed to obtain a multi-table union query result that satisfies the query request. Therefore, in data fusion scenarios based on multi-party computation, users can accurately obtain multi-table union query results for a portion of the physical tables as needed, and these results can be output according to a specified query order, thereby improving the user experience of multi-table union queries.

[0082] To enable those skilled in the art to better understand the technical solutions in the embodiments of this specification, the following description, in conjunction with... Figure 2 The union relationship tree shown, taking the specified query order of the query request as "t8→t6→t4→t1" as an example, will be used to describe the embodiments in this specification in detail.

[0083] For example, such as Figure 2 As shown, the eight physical tables participating in the multi-party computation can be represented as t1, t2, t3, t4, t5, t6, t7, and t8, respectively. The data warehouse can receive query requests for at least some of the eight physical tables. These query requests can indicate that the at least some physical tables include t1, t4, t6, and t8, and can also indicate a specified query order among the at least some physical tables as "t8→t6→t4→t1". Further, in response to the query requests shown, a query queue [t8, t6, t4, t1, EOF] can be generated, and a traversal process as shown below can be executed according to the specified query order:

[0084]

[0085]

[0086]

[0087] Through the above traversal process, the merged queue element result "t6∪t8∪(t4×t1)" can be determined as the result of a multi-table joint query that satisfies the query requirements.

[0088] Corresponding to the embodiments of the multi-table join query method described above, this specification also provides an embodiment of a multi-table join query device.

[0089] Please see Figure 4 , Figure 4 This is an exemplary embodiment illustrating the hardware structure of an electronic device housing a multi-table join query device. At the hardware level, the device includes a processor 402, an internal bus 404, a network interface 406, memory 408, and non-volatile memory 410, and may also include other hardware required for business operations. One or more embodiments of this specification can be implemented in software, for example, the processor 402 reads the corresponding computer program from the non-volatile memory 410 into memory 408 and then runs it. Of course, besides software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution entity of the following processing flow is not limited to individual logic units, but can also be hardware or logic devices.

[0090] Please see Figure 5 , Figure 5 This is a block diagram illustrating a multi-table join query apparatus as an exemplary embodiment. This multi-table join query apparatus can be applied to, for example... Figure 4 The illustrated electronic device is used to implement the technical solution of this specification. The multi-table join query device can be applied to a data warehouse; the data warehouse stores a join tree representing the join relationships between multiple physical tables participating in multi-party computation; the join tree is organized in the form of a multi-branch tree; wherein the join tree includes: leaf nodes representing each physical table in the multiple physical tables, and non-leaf nodes representing the join operations performed on the physical tables represented by the multiple leaf nodes connected to it; the device may include:

[0091] The receiving unit 502 is configured to receive query requests for at least a portion of the plurality of physical tables; wherein the query requests are used to indicate the at least a portion of the physical tables and to indicate a specified query order among the at least a portion of the physical tables;

[0092] The query unit 504 is configured to respond to the query request, traverse each leaf node in the stored union relation tree corresponding to each physical table in the at least part of the physical tables according to the specified query order, and perform a union operation corresponding to the non-leaf nodes connected to each leaf node for each physical table corresponding to the traversed leaf node, so as to obtain a multi-table union query result that satisfies the query request.

[0093] In this embodiment, the data warehouse maintains a first data structure for storing the root subtree in the union relationship tree, a second data structure for storing the first mapping relationship, and a third data structure for storing the second mapping relationship; wherein, the first mapping relationship is the mapping relationship between a non-root node in the union relationship tree and its subtree; the second mapping relationship is the mapping relationship between a subtree in the union relationship tree and the union operation corresponding to its root node;

[0094] The device further includes:

[0095] The reading unit is configured to read the root subtree in the union tree stored in the first data structure; and to read the first mapping relationship between the non-root node and its subtree in the union tree stored in the second data structure; and to read the second mapping relationship between the subtree and the union operation corresponding to the root node in the union tree stored in the third data structure.

[0096] The generation unit is used to generate the joint relationship tree based on the read root subtree, the first mapping relationship, and the second mapping relationship.

[0097] In this embodiment, the union operation includes union operations and / or join operations.

[0098] In this embodiment, the query requirement is a sequence of table identifiers; the sequence of table identifiers includes the table identifiers of at least a portion of the physical tables arranged according to the specified query order.

[0099] In this embodiment, the query unit 504 is specifically used for:

[0100] Generate a query queue corresponding to the query request; wherein, the queue elements in the query queue include leaf nodes corresponding to each physical table in the at least some physical tables, arranged according to the position of the corresponding physical table in the specified query order.

[0101] The queue element at the head of the query queue is dequeued as the initial first node, and the remaining queue elements in the query queue are traversed sequentially according to the specified query order. The queue element merging operation is performed iteratively on the traversed queue elements until the query queue is empty, and the traversal ends. The result of merging the queue elements corresponding to the query queue is determined as the multi-table joint query result that satisfies the query requirements.

[0102] In this embodiment, the query unit 504 is specifically used for:

[0103] The queue element that has been traversed is dequeued as the second node, and it is determined whether the first node and the second node meet the merging condition.

[0104] If the first node and the second node satisfy the merging condition, then perform a joint operation corresponding to the root node of the subtree where the second node is located on the first node and the second node to obtain the queue element merging result corresponding to the query queue, and update the first node to the queue element merging result;

[0105] If the first node and the second node do not meet the merging condition, then the first node is inserted into the tail of the query queue, and the first node is updated to the second node.

[0106] In this embodiment, the merging conditions include:

[0107] The subtree containing the first node and the subtree containing the second node are the same subtree; or,

[0108] The subtree containing the first node is a descendant tree of the subtree containing the second node.

[0109] In this embodiment, the queue elements in the query queue also include a preset symbol located at the tail of the query queue;

[0110] The query unit 504 is also used for:

[0111] Determine whether the second node is the preset symbol;

[0112] If the second node is the preset symbol, then the first node is inserted into the tail of the query queue, and the first node is updated to the second node, ending this round of iteration;

[0113] If the second node is not the preset symbol, then determine whether the first node and the second node meet the merging conditions.

[0114] In this embodiment, the query unit 504 is further configured to:

[0115] Determine whether the first node is the preset symbol;

[0116] If the first node is the preset symbol, then insert the first node into the tail of the query queue, update the first node to the second node, and end the current iteration;

[0117] If the first node is not the preset symbol, determine whether the first node and the second node meet the merging conditions.

[0118] In this embodiment, the query unit 504 is specifically used for:

[0119] Determine whether the first node is the result of merging the queue elements;

[0120] If the first node is the result of merging the queue elements, then the first node is inserted at the tail of the query queue;

[0121] If the first node is not the result of merging the queue elements, then the first node is updated to the subtree where the first node is located, and the first node is inserted into the tail of the query queue.

[0122] In this embodiment, the query unit 504 is further configured to:

[0123] Determine whether the first node is the root subtree in the union relationship tree;

[0124] If the first node is the root subtree in the union relationship tree, then insert the first node into the tail of the query queue and end the current iteration;

[0125] If the first node is not the root subtree in the union relationship tree, then the first node is updated to the subtree containing the first node.

[0126] The specific implementation process of the functions and roles of each unit in the above device can be found in the implementation process of the corresponding steps in the above method, and will not be repeated here.

[0127] For the device embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to in the description of the method embodiments. The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of the solution in this specification according to actual needs. Those skilled in the art can understand and implement this without creative effort.

[0128] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which can take the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.

[0129] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0130] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0131] Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0132] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation entry points are provided for users to choose to authorize or refuse.

[0133] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0134] 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.

[0135] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this specification. The singular forms "a," "described," and "the" as used in one or more embodiments of this specification and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term "and / or" as used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0136] It should be understood that although the terms first, second, third, etc., may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first information may also be referred to as second information without departing from the scope of one or more embodiments of this specification, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "in response to a determination," or "when," or "in the event of a determination."

[0137] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification should be included within the protection scope of one or more embodiments of this specification.

Claims

1. A multi-table join query method, applied to a data warehouse; wherein the data warehouse stores a join tree representing the join relationships between multiple physical tables participating in multi-party computation; The union tree is organized as a multi-branch tree; wherein... The union tree includes: leaf nodes representing each physical table in the plurality of physical tables, and non-leaf nodes representing union operations performed on the physical tables represented by the plurality of leaf nodes connected to it; the method includes: Receive query requests for at least a portion of the plurality of physical tables; wherein the query requests are used to indicate the at least a portion of the physical tables specified by the user, and the specified query order among the at least a portion of the physical tables specified by the user; In response to the query request, according to the specified query order, each leaf node in the stored union relation tree corresponding to each physical table in the at least part of the physical tables is traversed, and for each physical table corresponding to the traversed leaf node, a union operation corresponding to the non-leaf node connected to each traversed leaf node is performed to obtain a multi-table union query result that satisfies the query request.

2. The method according to claim 1, wherein the data warehouse maintains a first data structure for storing the root subtree in the union relationship tree, a second data structure for storing the first mapping relationship, and a third data structure for storing the second mapping relationship; wherein, The first mapping relationship is the mapping relationship between the non-root node in the union relationship tree and its subtree; The second mapping relationship is the mapping relationship between the subtrees in the union relationship tree and the union operation corresponding to their root nodes; Before traversing the leaf nodes of the stored union tree corresponding to the physical tables in the at least some physical tables according to the specified query order, the method further includes: Read the root subtree in the union relationship tree stored in the first data structure; and read the first mapping relationship between the non-root node and its subtree in the union relationship tree stored in the second data structure; and read the second mapping relationship between the subtree and the union operation corresponding to the root node in the union relationship tree stored in the third data structure. Based on the read root subtree, the first mapping relationship, and the second mapping relationship, the joint relationship tree is generated.

3. The method according to claim 1, wherein the union operation includes a union operation and / or a join operation.

4. The method according to claim 1, wherein the query requirement is a table identifier sequence; the table identifier sequence includes table identifiers of at least a portion of the physical tables arranged according to the specified query order.

5. The method according to claim 1, wherein traversing each leaf node in the stored union relation tree corresponding to each physical table in the at least some physical tables according to the specified query order, and performing a union operation corresponding to the non-leaf nodes connected to each traversed leaf node for each physical table corresponding to the traversed leaf node, to obtain a multi-table union query result that satisfies the query requirements, includes: Generate a query queue corresponding to the query request; wherein, the queue elements in the query queue include leaf nodes corresponding to each physical table in the at least some physical tables, arranged according to the position of the corresponding physical table in the specified query order. The queue element at the head of the query queue is dequeued as the initial first node, and the remaining queue elements in the query queue are traversed sequentially according to the specified query order. The queue element merging operation is performed iteratively on the traversed queue elements until the query queue is empty, and the traversal ends. The result of merging the queue elements corresponding to the query queue is determined as the multi-table joint query result that satisfies the query requirements.

6. The method according to claim 5, wherein performing a queue element merging operation on the traversed queue elements includes: The queue element that has been traversed is dequeued as the second node, and it is determined whether the first node and the second node meet the merging condition. If the first node and the second node satisfy the merging condition, then perform a joint operation corresponding to the root node of the subtree where the second node is located on the first node and the second node to obtain the queue element merging result corresponding to the query queue, and update the first node to the queue element merging result; If the first node and the second node do not meet the merging condition, then the first node is inserted into the tail of the query queue, and the first node is updated to the second node.

7. The method according to claim 6, wherein the merging conditions include: The subtree containing the first node and the subtree containing the second node are the same subtree; or, The subtree containing the first node is a descendant tree of the subtree containing the second node.

8. The method according to claim 6, wherein the queue element in the query queue further includes a preset symbol located at the tail of the query queue; Before determining whether the first node and the second node meet the merge conditions, the method further includes: Determine whether the second node is the preset symbol; If the second node is the preset symbol, then the first node is inserted into the tail of the query queue, and the first node is updated to the second node, ending this round of iteration; If the second node is not the preset symbol, then determine whether the first node and the second node meet the merging conditions.

9. The method according to claim 8, wherein before determining whether the first node and the second node satisfy the merging condition, the method further comprises: Determine whether the first node is the preset symbol; If the first node is the preset symbol, then insert the first node into the tail of the query queue, update the first node to the second node, and end the current iteration; If the first node is not the preset symbol, determine whether the first node and the second node meet the merging conditions.

10. The method according to claim 8, wherein inserting the first node into the tail of the query queue comprises: Determine whether the first node is the result of merging the queue elements; If the first node is the result of merging the queue elements, then the first node is inserted at the tail of the query queue; If the first node is not the result of merging the queue elements, then the first node is updated to the subtree where the first node is located, and the first node is inserted into the tail of the query queue.

11. The method according to claim 10, wherein before updating the first node to the subtree containing the first node, the method further comprises: Determine whether the first node is the root subtree in the union relationship tree; If the first node is the root subtree in the union relationship tree, then insert the first node into the tail of the query queue and end the current iteration; If the first node is not the root subtree in the union relationship tree, then the first node is updated to the subtree containing the first node.

12. A multi-table join query device, applied to a data warehouse; the data warehouse stores a join tree for representing the join relationships between multiple physical tables participating in multi-party computation; The union tree is organized as a multi-branch tree; wherein... The union tree includes: leaf nodes representing each physical table in the plurality of physical tables, and non-leaf nodes representing union operations performed on the physical tables represented by the plurality of leaf nodes connected to it; the apparatus includes: A receiving unit is configured to receive query requests for at least a portion of the plurality of physical tables; wherein the query requests are configured to indicate the at least a portion of the physical tables specified by the user, and a specified query order among the at least a portion of the physical tables specified by the user; The query unit is configured to respond to the query request by traversing each leaf node in the stored union relation tree corresponding to each physical table in the at least some physical tables according to the specified query order, and performing a union operation corresponding to the non-leaf nodes connected to each leaf node for each physical table corresponding to the traversed leaf node, so as to obtain a multi-table union query result that satisfies the query request.

13. An electronic device, comprising a communication interface, a processor, a memory, and a bus, wherein the communication interface, the processor, and the memory are interconnected via the bus; The memory stores machine-readable instructions, and the processor executes the method according to any one of claims 1 to 11 by invoking the machine-readable instructions.

14. A machine-readable storage medium storing machine-readable instructions that, when invoked and executed by a processor, implement the method of any one of claims 1 to 11.

Images