Reverse semi-connection query method and device, equipment, storage medium and program product

Abstract

The invention discloses a reverse semi-connection query method and device, equipment, a storage medium and a program product, and relates to the technical field of distributed databases. The method comprises the steps of obtaining an SQL query statement; analyzing the SQL query statement to obtain a connection column of a left side table and a connection column of a right side table corresponding to the reverse semi-connection operation; generating a left broadcast execution plan according to the connection column of the left table and the connection column of the right table; and executing the SQL query statement according to the left broadcast execution plan to obtain a reverse semi-connection query result of the SQL query statement. Compared with resource and performance waste caused by the fact that data on the two sides need to be distributed or summarized in a traditional method, the left side data broadcast distribution based on the reverse semi-connection is achieved, only the left side data need to be distributed, the performance overhead of right side data distribution is reduced, and the query performance of a distributed database is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of distributed databases, and in particular to an anti-semi-join query method, apparatus, device, storage medium and program product. Background Art

[0002] In distributed databases, efficient data distribution and processing are crucial for improving query performance. For some common left join operations, left-side broadcasting can be implemented. By broadcasting the data on one side of the smaller table to all nodes, redistribution of the larger table's data is avoided, significantly improving query efficiency.

[0003] However, the anti-semi-join operation is a special join operation based on the left table, used to query data in the left table that has no matching records in the right table. Due to the special nature of the anti-semi-join operation, current conventional methods cannot effectively support the left-side broadcast distribution method. Typically, a hash table or index is constructed for the right-side table data, and then each record in the left table is quickly searched to determine whether the left-side table data exists in the right table. These methods incur significant data transmission overhead. Summary of the Invention

[0004] The present invention provides an anti-semi-join query method, apparatus, device, storage medium and program product to solve the problem that the current anti-semi-join operation cannot effectively support the distribution mode of left broadcast.

[0005] In a first aspect, an embodiment of the present invention provides an anti-semi-join query method, comprising:

[0006] Get the SQL query statement;

[0007] Parse the SQL query statement to obtain the join columns of the left table and the right table corresponding to the anti-semi-join operation;

[0008] Generate a left-side broadcast execution plan based on the join columns of the left table and the join columns of the right table; the left-side broadcast execution plan includes: an anti-semi-join result correction operator, a data sending operator as the left child node of the anti-semi-join result correction operator, an anti-semi-join operator as the left child node of the data sending operator, a left-side data broadcast operator as the left child node of the anti-semi-join operator, a projection operator as the left child node of the left data broadcast operator, a left-side table operator as the left child node of the projection operator, and a right-side table operator as the right child node of the anti-semi-join operator;

[0009] The SQL query statement is executed according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement.

[0010] In a second aspect, an embodiment of the present invention provides an anti-semi-join query device, comprising:

[0011] Statement acquisition module, used to obtain SQL query statements;

[0012] A statement parsing module parses the SQL query statement to obtain the join columns of the left table and the right table corresponding to the anti-semi-join operation;

[0013] an execution plan generation module, configured to generate a left-side broadcast execution plan for an anti-semi-join operation based on a join column of the left table and a join column of the right table; the left-side broadcast execution plan comprising: an anti-semi-join result modification operator, a data sending operator as a left child node of the anti-semi-join result modification operator, an anti-semi-join operator as a left child node of the data sending operator, a left-side data broadcast operator as a left child node of the anti-semi-join operator, a projection operator as a left child node of the left data broadcast operator, a left-side table operator as a left child node of the projection operator, and a right-side table operator as a right child node of the anti-semi-join operator;

[0014] A query module is used to execute the SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement.

[0015] In a third aspect, an embodiment of the present invention provides an electronic device, comprising:

[0016] at least one processor;

[0017] and a memory communicatively coupled to the at least one processor;

[0018] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the anti-semi-join query method described in any embodiment of the present invention.

[0019] In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the anti-semi-join query method described in any embodiment of the present invention when executed.

[0020] In a fifth aspect, an embodiment of the present invention provides a computer program product including a computer program, which, when executed by a processor, implements the anti-semi-join query method described in any embodiment of the present invention.

[0021] The technical solution of an embodiment of the present invention obtains an SQL query statement; parses the SQL query statement to obtain the join columns of the left table and the join columns of the right table corresponding to the anti-semi-join operation; generates a left-side broadcast execution plan based on the join columns of the left table and the right table; and executes the SQL query statement according to the left-side broadcast execution plan to obtain the anti-semi-join query result of the SQL query statement. This solution implements the distribution of left-side data broadcast based on the anti-semi-join, requiring only the left-side data to be distributed. This solves the resource and performance waste caused by traditional methods requiring the distribution or aggregation of data on both sides, thereby reducing the performance overhead of distributing data on the right side and improving the query performance of distributed databases.

[0022] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0024] Figure 1 A flowchart of an anti-semi-join query method provided in Example 1 of the present invention;

[0025] Figure 2 A schematic diagram of a left-side broadcast execution plan provided for implementation of the present invention;

[0026] Figure 3 A flowchart of an anti-semi-join query method provided in the second embodiment of the present invention;

[0027] Figure 4 A schematic diagram of the structure of an anti-semi-join query device provided in the third embodiment of the present invention;

[0028] Figure 5 A schematic diagram of the structure of an electronic device for implementing the anti-semi-join query method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0029] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0030] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0031] Example 1

[0032] Figure 1 This is a flowchart of a reverse semi-join query method provided in the first embodiment of the present invention. This embodiment is applicable to the case of left-side broadcast distribution for reverse semi-join. The method can be executed by a reverse semi-join query device. The reverse semi-join query device can be implemented in the form of hardware and / or software. The reverse semi-join query device can be configured in an electronic device. Figure 1 As shown, the method includes:

[0033] S110: Obtain SQL query statement.

[0034] The SQL query statement can be understood as a Structured Query Language (SQL) statement that performs an anti-semi-join query in a distributed database. For example, the SQL query statement might be: select * from T1 where c2 not in (select d2 from T2), which indicates that the data in table T1 where the value of column c2 is not in table T2's column d2 is selected. For example, "not in" or "NOT EXISTS" can be implemented using an anti-semi-join.

[0035] S120 , parsing the SQL query statement to obtain the join columns of the left table and the right table corresponding to the anti-semi-join operation.

[0036] Specifically, the SQL query statement is parsed, and the join columns of the left table and the right table corresponding to the anti-semi-join operation are determined according to the parsing result.

[0037] Optionally, the anti-semi-join operation may include a hash-based anti-semi-join operation, an index-based anti-semi-join operation, a merge-based anti-semi-join operation, and a nested-based anti-semi-join operation.

[0038] S130 : Generate a left broadcast execution plan according to the join columns of the left table and the join columns of the right table.

[0039] The left-side broadcast execution plan can be understood as an execution plan based on broadcasting the left-side table. The execution plan is used to describe the various steps, operation sequence, indexes used, and data access methods used by the database management system to execute a query statement.

[0040] Among them, the left broadcast execution plan includes: an anti-semi-join result correction operator, a data sending operator as the left child node of the anti-semi-join result correction operator, an anti-semi-join operator as the left child node of the data sending operator, a left data broadcast operator as the left child node of the anti-semi-join operator, a projection operator as the left child node of the left data broadcast operator, a left table operator as the left child node of the projection operator, and a right table operator as the right child node of the anti-semi-join operator.

[0041] Specifically, the projection operator is used to generate a data identification number for the broadcasted left-side table data; the left-side data broadcast operator is used to broadcast the join column data of the left-side table; the anti-semi-join operator is used to return data in the join column of the left-side table that has no matching records with the join column of the right-side table; the data sending operator is used to send the anti-semi-join operation result to the corresponding target node according to the data identification number; the anti-semi-join result correction operator is used to correct the received anti-semi-join result; the left-side table operator is used to scan the left-side table data; and the right-side table operator is used to scan the right-side table data.

[0042] For example, Figure 2 Schematic diagram of a left-side broadcast execution plan provided for implementation of the present invention. Figure 2As shown, the left broadcast execution plan includes: the anti-semi-join result correction operator DIST(OPT_ANTISEMI), the data sending operator SEND(AUTOID) as the left child node of the anti-semi-join result correction operator, the anti-semi-join operator HLS(ANTI) as the left child node of the data sending operator, the left data broadcast operator L_BRO as the left child node of the anti-semi-join operator, the projection operator PRJT(AUTOID) as the left child node of the left data broadcast operator, the left table scan operator Table Scan(T1) as the left child node of the projection operator, and the right table scan operator Table Scan(T2) as the right child node of the anti-semi-join operator.

[0043] S140: Execute the SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement.

[0044] Specifically, each node in the distributed database executes the SQL query statement according to the generated left-side broadcast execution plan, obtains the anti-semi-join operation result of each node, and summarizes the anti-semi-join operation results of each node to obtain the anti-semi-join query result of the SQL query statement.

[0045] The technical solution of an embodiment of the present invention obtains an SQL query statement; parses the SQL query statement to obtain the join columns of the left table and the join columns of the right table corresponding to the anti-semi-join operation; generates a left-side broadcast execution plan based on the join columns of the left table and the right table; and executes the SQL query statement according to the left-side broadcast execution plan to obtain the anti-semi-join query result of the SQL query statement. Compared with traditional methods that require distributing or aggregating data on both sides, resulting in wasted resources and performance, this method implements the distribution of left-side data broadcast based on anti-semi-join, requiring only the left-side data to be distributed, reducing the performance overhead of distributing right-side data and improving the query performance of distributed databases.

[0046] Example 2

[0047] Figure 3 This is a flowchart of an anti-semi-join query method provided in Example 2 of the present invention. Based on the above embodiment, this embodiment further refines the steps of executing the SQL query statement according to the left broadcast execution plan to obtain the anti-semi-join query result of the SQL query statement.

[0048] like Figure 3 As shown, the method includes:

[0049] S210: Obtain SQL query statement.

[0050] S220 , parsing the SQL query statement to obtain the join columns of the left table and the right table corresponding to the anti-semi-join operation.

[0051] S230 : Generate a left broadcast execution plan according to the join columns of the left table and the join columns of the right table.

[0052] Specifically, the left broadcast execution plan includes: the anti-semi-join result correction operator DIST (OPT_ANTISEMI), the data sending operator SEND (AUTOID) as the left child node of the anti-semi-join result correction operator, the anti-semi-join operator HLS (ANTI) as the left child node of the data sending operator, the left data broadcast operator L_BRO as the left child node of the anti-semi-join operator, the projection operator PRJT (AUTOID) as the left child node of the left data broadcast operator, the left table scan operator Table Scan (T1) as the left child node of the projection operator, and the right table scan operator Table Scan (T2) as the right child node of the anti-semi-join operator.

[0053] S240. For each current node in the distributed database, execute the SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join result of the current node, and count the anti-semi-join result of the current node and the anti-semi-join results returned by other nodes in the distributed database to obtain a corrected anti-semi-join result of the current node.

[0054] The anti-semijoin result of the current node can be understood as the result obtained by performing an anti-semijoin query on the current node. The modified anti-semijoin result of the current node can be understood as the modified anti-semijoin result of the current node, which is modified by combining it with the anti-semijoin results of other nodes.

[0055] Specifically, for each current node in the distributed database, the SQL query statement is executed according to the generated left-side broadcast execution plan, the connection columns on both sides of the anti-semi-join of the SQL query statement are queried, and the anti-semi-join result of the current node is obtained. However, the anti-semi-join result obtained at this time is only a query result for the connection column data of the right table stored in the current node. The connection column data of the right table may also exist in other nodes in the distributed database, so it is not necessarily a correct anti-semi-join result. It is also necessary to receive the anti-semi-join results returned by other nodes in the distributed database, and by merging and counting the anti-semi-join results of the current node and the anti-semi-join results of other nodes, the anti-semi-join result of the current node is corrected to obtain the corrected anti-semi-join result of the current node. Thus, the corrected anti-semi-join result obtained by executing the SQL query statement on each node in the distributed database is obtained.

[0056] As an optional implementation of this embodiment, S240, executing the left broadcast execution plan to obtain an anti-semi-join result of the current node, and counting the anti-semi-join result of the current node and anti-semi-join results returned by other nodes in the distributed database to obtain a modified anti-semi-join result of the current node, includes:

[0057] S241 , executing the left table operator to obtain the join column data of the left table of the anti-semi-join operator in the current node; executing the right table operator in the current node to obtain the join column data of the right table of the anti-semi-join operator.

[0058] Specifically, the current node executes the left table operator Table Scan(T1) to scan the left table of the anti-semi-join operator and obtain the join column data of the left table T1 in the current node. The right table operator Table Scan(T2) is executed to scan the right table of the anti-semi-join operator and obtain the join column data of the right table T2 in the current node.

[0059] It is understandable that the connection column data of the left table T1 may include data stored in the current node and / or other nodes; similarly, the connection column data of the right table T2 may also include data stored in the current node and / or other nodes.

[0060] S242: Execute the projection operator to generate a data identification number for the connection column data of the left table.

[0061] The data identification number AUTOID can be understood as a unique identifier of the data, which is used to distinguish different data.

[0062] Specifically, the current node executes the projection operator PRJT(AUTOID) which is the parent node of the left table operator Table Scan(T1) to determine the data identification number of the join column data of the left table.

[0063] Optionally, the data identification number includes the node sequence number and line program number of the node where the connection column data of the left table is located.

[0064] The thread program number can be understood as a serial number assigned to a thread when a task is assigned, which is used to identify the order of tasks and can generally be arranged as a natural number or a positive integer.

[0065] Exemplarily, the data identification number AUTOID can be of int64 type, and counted from 1 in each thread. At the same time, the AUTOID contains at least the node sequence number and thread program number of the current node, such as adding the node sequence number and thread program number of the current node to the high byte. This ensures that the AUTOID values recorded in each thread will not be repeated. For example, for thread 0 of node A, the thread program number is 1, and the data identification number can be A01; for thread 0 of node A, the thread program number is 2, and the data identification number can be A02. For thread 2 of node B, the thread program number is 1, and the data identification number can be B21.

[0066] S243: Execute the left data broadcast operator to broadcast the connection column data of the left table carrying the data identification number.

[0067] Specifically, the current node executes the left data broadcast operator L_BRO which is the parent node of the projection operator PRJT(AUTOID), and broadcasts the connection column data of the left table carrying the data identification number so that each node can receive the connection column data of the left table.

[0068] S244. Execute the anti-semi-join operator to obtain an anti-semi-join result of the current node, where the anti-semi-join result includes data selected from the join column of the left table that has no matching record with the join column of the right table and corresponding data identification numbers.

[0069] Specifically, the current node executes the anti-semi-join operator HLS(ANTI), which is the parent node of the left-side data broadcast operator L_BRO and the right-side table operator TableScan(T2). For each data item in the left-side table's join column, the current node checks the right-side table's join column within the current node to see if there is any data matching the left-side table's join column. If not, the current node outputs the data item in the left-side table's join column and the corresponding data identification number. This selects all data items in the left-side table's join column that do not have a matching record in the right-side table's join column, along with their corresponding data identification numbers. The selected data items and corresponding data identification numbers are then determined as the anti-semi-join result for the current node.

[0070] S245. Execute the data sending operator to send the anti-semi-join result of the current node to other nodes in the corresponding distributed database according to the data identification number, so that each of the other nodes counts its own anti-semi-join result and the received anti-semi-join result to obtain its own modified anti-semi-join result.

[0071] Among them, the other nodes to which the anti-semi-join result of the current node needs to be sent can be determined based on the data identification number in the anti-semi-join result. Optionally, for a hash-based anti-semi-join operation, the node sequence numbers of the other nodes can be the result of taking the hash value of the data identification number modulo the number of threads. For example, if the number of threads in the distributed database is N=10, there are 4 threads on node 1 and 6 threads on node 2, (hash value of the data identification number)%(number of threads in the distributed database N)=n, n={0,1,2,…,9 (i.e., N-1)}, which means that the anti-semi-join result of the current node is sent to the node corresponding to the nth thread, that is, when n=0,1,2,3, the anti-semi-join result of the current node is sent to node 1; when n=4,5,…,9, the anti-semi-join result of the current node is sent to node 2.

[0072] Specifically, the current node executes the data sending operator SEND(AUTOID) which is the parent node of the anti-semi-join operator HLS(ANTI), and sends the anti-semi-join result of the current node to other nodes in the corresponding distributed database according to the data identification number. This enables different nodes to send the anti-semi-join results with the same data identification number to the same node, thereby ensuring that the modified anti-semi-join result of the node is correct.

[0073] It is understandable that there may be some other nodes that send the anti-semi-join results of their own nodes to the current node according to the data identification number, so the current node can receive the anti-semi-join results sent by other nodes.

[0074] S246 , executing the anti-semi-join result correction operator, counting the anti-semi-join result of the current node and the received anti-semi-join results returned by the other nodes, to obtain a corrected anti-semi-join result of the current node.

[0075] Specifically, the current node executes the anti-semi-join result correction operator DIST(OPT_ANTISEMI) of the parent node of the data sending operator SEND(AUTOID), merges and counts its own anti-semi-join result and the anti-semi-join results received from other nodes, and corrects its own anti-semi-join result to obtain a corrected anti-semi-join result.

[0076] It's important to note that in the left-side broadcast execution plan, there can be one or more anti-semi-join result correction operators (DIST(OPT_ANTISEMI)). However, records with the same data identification number (AUTOID) are processed in the same node's DIST(OPT_ANTISEMI). This is guaranteed by the data send operator (SEND(AUTOID)). The data send operator (SEND(AUTOID)) sends the anti-semi-join result of the current node to other nodes in the corresponding distributed database according to the data identification number. This ensures that different nodes send anti-semi-join results with the same data identification number to the same node, thereby ensuring that the corrected anti-semi-join results are generated correctly.

[0077] Optionally, executing the anti-semi-join result correction operator, counting the anti-semi-join result of the current node and the anti-semi-join results received and returned from the other nodes, and obtaining the corrected anti-semi-join result of the current node, includes: executing the anti-semi-join result correction operator, counting the data identification numbers that are identical in the anti-semi-join result of the current node and the anti-semi-join results of other nodes; and determining the data identification number whose count result is equal to the sum of the parallelism of the current node and the other nodes and the corresponding data as the corrected anti-semi-join result of the current node.

[0078] The parallelism of each node can be configured according to actual needs, and the embodiment of the present invention does not impose any limitation on this.

[0079] Specifically, after receiving the anti-semi-join results returned by the other nodes, the current node executes the anti-semi-join result modification operator DIST(OPT_ANTISEMI). This counts the records with the same data identification number in the anti-semi-join results of the current node and the anti-semi-join results of the other nodes, incrementing the corresponding count by one for each identical AUTOID value. If the count result for a record equals the sum of the parallelism of the current node and the other nodes, it is considered that no matching data is found for that record in any node. Therefore, this record and the corresponding data identification number are determined as one of the modified anti-semi-join results of the current node, indicating that data from the left table has been selected that has no matching records in the join column of the right table in any node.

[0080] S250: Determine the data set corresponding to the modified anti-semi-join result of each node in the distributed database as the anti-semi-join query result of the SQL query statement.

[0081] Specifically, the data in the modified anti-semi-join result of each node obtained by executing the SQL query statement according to the left broadcast execution plan on each node in the distributed database is summarized to obtain a data set of the modified anti-semi-join results of all nodes, that is, the anti-semi-join query result of the SQL query statement.

[0082] Next, we will use a specific anti-semi-join query example to explain in detail the steps of the anti-semi-join query method provided by the embodiment of the present invention. Assume that the following operations are performed in a distributed database:

[0083] create table T1(c1 int,c2 int)partition by hash(c1)partitions 3;

[0084] create table T2(d1 int,d2 int)partition by hash(d1)partitions 5;

[0085] insert into T1 select level,level from dual connect by level<6;

[0086] insert into T2 select level+3,level+3from dual connect by level<7;

[0087] commit;

[0088] SQL query statement Q1: select * from T1 where c2 not in (select d2 from T2)

[0089] The left table corresponding to the anti-semi-join operation is T1, and the join column of the left table is c2. The right table corresponding to the anti-semi-join operation is T2, and the join column of the right table is d2.

[0090] The above operation shows that the left table T1 has 5 records, and the data in the c2 column of the left table T1 is ((1,3,5),(2),(4)); the right table T2 has 6 records, and the data in the d2 column of the table T2 is ((8),(5,9),(6),(7),(4)).

[0091] It should be noted that in a distributed system, the data in column c2 of table T1 on the left can be distributed across different partitions; the data in column d2 of table T2 on the right can also be distributed across different partitions, and partitions may distribute data across multiple nodes. For example, the data in column c2 (1, 3, 5) of table T1 on the left is in the first partition of table T1 on the left, the data in column c2 (2) is in the second partition of table T1 on the left, and the data in column c2 (4) is in the third partition of table T1 on the left. The data in column d2 (8) of table T2 is in the first partition of table T2 on the right, the data in column c2 (5, 9) is in the second partition of table T2 on the right, the data in column c2 (6) is in the third partition of table T2 on the right, the data in column c2 (7) is in the fourth partition of table T2 on the right, and the data in column c2 (4) is in the fifth partition of table T2 on the right. According to the node division, the data in the c2 column of the table T1 on the right side stores two nodes, A and B. The data in the c2 column of the node A is (1, 3, 5), and the data in the c2 column of the node B is (2), (4); the data in the d2 column of the table T2 on the right side also stores two nodes, A and B. The data in the d2 column of the node A is (8), (5, 9); the data in the d2 column of the node B is (6), (7), (4).

[0092] Directly broadcasting the left-hand table data may result in an incorrect anti-semi-join query result. For example, assume the anti-semi-join and right-hand table T2 have five threads, each scanning a partitioned sub-table. If the data from left-hand table T1 is directly broadcast to right-hand table T2, for the data 5 in column c2 of left-hand table T1, only one of the five threads does not meet the anti-semi-join conditions and will not be returned. However, none of the other four threads on the right side match the data 5 from the left table, so the record with c2 = 5 will be incorrectly returned multiple times. Using traditional processing methods may result in an incorrect anti-semi-join query result. Furthermore, if the anti-semi-join's parallelism is ≥ 2, the record with c2 = 5 will be returned multiple times on node B because it does not match any data in column d2 of the right-hand table, which will also result in an incorrect anti-semi-join query result.

[0093] In this embodiment, to address the above problem, the left broadcast execution plan is executed for node A to obtain the modified anti-semi-join result of node A. The specific steps are:

[0094] A1 executes the Table Scan(T1) operator on the left table to obtain the data in column c2 of table T1 on the left side of node A: (1, 3, 5).

[0095] A2 executes the right-side table operator Table Scan(T2) to obtain the data in column d2 of the right-side table T2 in node A: (8), (5, 9).

[0096] A3. Execute the projection operator PRJT(AUTOID) to determine the data identification number for the data in column c2 (1, 3, 5) of table T1 on the left. The data identification number includes the node number and line program number of the node where the data in the join column of the left table resides.

[0097] A4. Execute the left data broadcast operator L_BRO to broadcast the data (1, 3, 5) in the join column c2 of the left table T1 on node A, which carries the data identification number. Node B receives the data in the join column c2 of the left table T1 on node A. Node B also receives the data (2, 4) in the join column c2 of the left table T1 on node B, which carries the data identification number.

[0098] A5. Execute the anti-semi-join operator HLS(ANTI) to obtain the anti-semi-join result for node A, as shown in Table 1. The data (1, 3, 5) in the left table T1 is the data in node A, and the data (2, 4) in the left table T1 is the data broadcast from node B to node A.

[0099] Table 1

[0100]

[0101] Therefore, the data identifiers in the anti-semi-join result output by node A include: A01, A02, B11, B21.

[0102] Similarly, for node B, execute the left broadcast execution plan to obtain the modified anti-semi-join result of node B. The specific steps are:

[0103] B1 executes the Table Scan(T1) operator on the left table to obtain the data in column c2 of table T1 on the left side of node B: (2, 4).

[0104] B2. Execute the right table operator Table Scan(T2) to obtain the d2 column data of the right table T2 in node B: (6), (7), (4).

[0105] B3. Execute the projection operator PRJT(AUTOID) to determine the data identification number of the data (2,4) in column c2 of table T1 on the left. The data identification number includes the node sequence number and line program number of node B.

[0106] B4. Execute the left data broadcast operator L_BRO to broadcast the data (2, 4) in the join column c2 of the left table T1 with the data identification number in node B. Node A receives the data in the join column c2 of the left table T1 in node B. Node A also receives the data (1, 3, 5) in the join column c2 of the left table T1 with the data identification number in node A.

[0107] B5. Execute the anti-semi-join operator HLS(ANTI) to obtain the anti-semi-join result for node A, as shown in Table 2. The data (1, 3, 5) in the left table T1 is the data broadcast from node A to node B, and the data (2, 4) in the left table T1 is the data within node B.

[0108]

[0109] Therefore, the data identifiers in the anti-semi-join result output by node B include: A01, A02, A03, B11.

[0110] After node A and node B each output the anti-semijoin result, node A continues to execute step A6 and executes the data sending operator SEND(AUTOID). The output anti-semijoin result is sent to the corresponding node according to the data identification number AUTOID. That is, A01 and A02 are sent to node A, and B11 and B21 are sent to node B. Node B continues to execute step B6 and executes the data sending operator SEND(AUTOID). The output anti-semijoin result is sent to the corresponding node according to the data identification number AUTOID. That is, A01, A02, and A03 are sent to node A, and B11 is sent to node B. Thus, the data received by node A is identified as: A01 and A02 of node A and A01, A02, and A03 of node B. The data received by node B is identified as: B11 and B21 of node A and B11 of node B.

[0111] After node A and node B each receive the anti-semi-join results sent by the other node, node A continues to execute step A7 and executes the anti-semi-join result correction operator DIST(OPT_ANTISEMI) to count the data with the same data identifier, obtaining (A01, 2), (A02, 2), and (A03, 1). If the parallelism of node A and node B is both 1, the data identifiers whose count is equal to the sum of the parallelism of node A and node B, 2, are A01 and A02. Therefore, node A outputs the corrected anti-semi-join results of data 1 corresponding to data identifier A01 and data 3 corresponding to data identifier A02.

[0112] For node B, continue with step B7 and execute the anti-semi-join result correction operator DIST(OPT_ANTISEMI). Count the data with the same data identifier to obtain (B11, 2) and (B21, 1). If the parallelism of nodes A and B is both 1, the data identifier whose count is equal to the sum of the parallelism of nodes A and B, 2, is B11. Therefore, node B outputs the corrected anti-semi-join result as data 2 corresponding to data identifier B11.

[0113] Finally, the set {1, 3, 2} of data 1 and 3 corresponding to the modified anti-semi-join result of node A and data 2 corresponding to the modified anti-semi-join result of node B in the distributed database is determined as the anti-semi-join query result of the SQL query statement.

[0114] The anti-semi-join query provided in this embodiment implements the distribution of left-side data broadcast based on the anti-semi-join, ensuring the correctness of the results while fully utilizing thread resources. Only the left-side data needs to be distributed, reducing the performance overhead of distributing the right-side data. This avoids the resource and performance waste caused by the traditional method of distributing or aggregating data on both sides.

[0115] Example 3

[0116] Figure 4 This is a schematic diagram of the structure of an anti-semi-join query device provided in the third embodiment of the present invention. Figure 4 As shown, the device includes: a statement acquisition module 310, a statement parsing module 320, an execution plan generation module 330 and a query module 340; wherein,

[0117] A statement acquisition module 310 is used to acquire SQL query statements;

[0118] A statement parsing module 320 parses the SQL query statement to obtain a join column of a left table and a join column of a right table corresponding to an anti-semi-join operation;

[0119] An execution plan generation module 330 generates a left-side broadcast execution plan for an anti-semi-join operation based on the join columns of the left table and the join columns of the right table; the left-side broadcast execution plan includes: an anti-semi-join result modification operator, a data sending operator as the left child node of the anti-semi-join result modification operator, an anti-semi-join operator as the left child node of the data sending operator, a left-side data broadcast operator as the left child node of the anti-semi-join operator, a projection operator as the left child node of the left data broadcast operator, a left-side table operator as the left child node of the projection operator, and a right-side table operator as the right child node of the anti-semi-join operator.

[0120] The query module 340 is configured to execute the SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement.

[0121] An embodiment of the present invention provides an anti-semi-join query device, which obtains an SQL query statement; parses the SQL query statement to obtain the join columns of the left table and the join columns of the right table corresponding to the anti-semi-join operation; generates a left-side broadcast execution plan based on the join columns of the left table and the join columns of the right table; executes the SQL query statement according to the left-side broadcast execution plan to obtain the anti-semi-join query result of the SQL query statement; and can obtain the correct anti-semi-join operation result by broadcasting the left table, thereby reducing the data transmission overhead of the anti-semi-join operation and improving the query performance of the distributed database. Furthermore, the device can support multiple anti-semi-join operations, such as hash-based anti-semi-join operations, index-based anti-semi-join operations, merge-based anti-semi-join operations, and nested anti-semi-join operations.

[0122] Optionally, the query module 340 includes:

[0123] an anti-semi-join query unit, configured to execute an SQL query statement according to the left broadcast execution plan for each current node in the distributed database, obtain an anti-semi-join result of the current node, and count the anti-semi-join result of the current node and anti-semi-join results returned by other nodes in the distributed database to obtain a modified anti-semi-join result of the current node;

[0124] The query result determining unit is used to determine the data set corresponding to the modified anti-semi-join result of each node in the distributed database as the anti-semi-join query result of the SQL query statement.

[0125] Optionally, the anti-semijoin query unit includes:

[0126] a left table operation subunit, configured to execute the left table operator and obtain the join column data of the left table of the anti-semi-join operator in the current node;

[0127] a right-side table operation subunit, configured to execute the right-side table operator and obtain the join column data of the right-side table of the anti-semi-join operator in the current node;

[0128] A data identification generating subunit, configured to execute the projection operator to generate a data identification number for the connection column data of the left table;

[0129] a left broadcast subunit, configured to execute the left data broadcast operator to broadcast the connection column data of the left table carrying the data identification number;

[0130] an anti-semi-join operation subunit, configured to execute the anti-semi-join operator to obtain an anti-semi-join result for the current node, the anti-semi-join result comprising data selected from the join column of the left table that has no matching record with the join column of the right table and corresponding data identification numbers;

[0131] a data sending subunit, configured to execute the data sending operator and send the anti-semi-join result of the current node to other nodes in the corresponding distributed database according to the data identification number, so that each of the other nodes counts its own anti-semi-join result and the received anti-semi-join result to obtain its own modified anti-semi-join result;

[0132] The result correction subunit is configured to execute the anti-semi-join result correction operator, count the anti-semi-join result of the current node and the anti-semi-join results received from other nodes, and obtain a corrected anti-semi-join result of the current node.

[0133] Optionally, the result correction subunit includes:

[0134] Executing the anti-semi-join result correction operator to count the data identification numbers that are identical between the anti-semi-join result of the current node and the anti-semi-join results of other nodes;

[0135] The data identification number whose counting result is equal to the sum of the parallelism of the current node and the other nodes and the corresponding data are determined as the modified anti-semi-join result of the current node.

[0136] Optionally, the data identification number includes the node sequence number and line program number of the node where the connection column data of the left table is located.

[0137] Optionally, the anti-semi-join operation includes: a hash-based anti-semi-join operation, an index-based anti-semi-join operation, a merge-based anti-semi-join operation, and a nested-based anti-semi-join operation.

[0138] The anti-semi-join query device provided by the embodiment of the present invention can execute the anti-semi-join query method provided by any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0139] Example 4

[0140] Figure 5A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0141] like Figure 5 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., which is communicatively connected to the at least one processor 11. The memory stores a computer program that can be executed by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 into the random access memory (RAM) 13. Various programs and data required for the operation of the electronic device 10 can also be stored in the RAM 13. The processor 11, ROM 12, and RAM 13 are connected to each other via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0142] Multiple components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0143] The processor 11 may be any general-purpose and / or specialized processing component with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as the anti-semi-join query method.

[0144] In some embodiments, the anti-semi-join query method can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the anti-semi-join query method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to execute the anti-semi-join query method in any other appropriate manner (e.g., via firmware).

[0145] Various embodiments of the systems and techniques described herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0146] In some embodiments, the anti-semi-join query method can be implemented as a computer program, which is invisibly included in a computer program product. When executed by a processor, the computer program implements the anti-semi-join query method of the present invention. The computer program product can be understood as a software product that primarily implements its solution through the computer program. The computer program used to implement the method of the present invention can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when executed by the processor, the computer program implements the functions / operations specified in the flowcharts and / or block diagrams. The computer program can be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0147] In the context of the present invention, computer-readable storage media can be tangible media that can contain or store a computer program for use with an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. Computer-readable storage media can include but are not limited to electronic, magnetic, optical, electromagnetic, infrared or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media can include electrical connections based on one or more lines, portable computer disks, hard disks, random access memories (RAM), read-only memories (ROM), erasable programmable read-only memories (EPROM or flash memory), optical fibers, portable compact disk read-only memories (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0148] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0149] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0150] A computing system may include clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within the cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0151] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed in parallel, sequentially, or in a different order, as long as the desired results of the technical solution of the present invention can be achieved. This is not limited herein.

[0152] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. An anti-semi-join query method, characterized in that: Applied to a distributed database, the method includes: Get the SQL query statement; Parse the SQL query statement to obtain the join columns of the left table and the right table corresponding to the anti-semi-join operation; Generate a left-side broadcast execution plan based on the join columns of the left table and the join columns of the right table; the left-side broadcast execution plan includes: an anti-semi-join result correction operator, a data sending operator as the left child node of the anti-semi-join result correction operator, an anti-semi-join operator as the left child node of the data sending operator, a left-side data broadcast operator as the left child node of the anti-semi-join operator, a projection operator as the left child node of the left data broadcast operator, a left-side table operator as the left child node of the projection operator, and a right-side table operator as the right child node of the anti-semi-join operator; The SQL query statement is executed according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement.

2. The method according to claim 1, characterized in that The executing the SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement includes: For each current node in the distributed database, execute an SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join result of the current node, and count the anti-semi-join result of the current node and anti-semi-join results returned by other nodes in the distributed database to obtain a modified anti-semi-join result of the current node; The data set corresponding to the modified anti-semi-join result of each node in the distributed database is determined as the anti-semi-join query result of the SQL query statement.

3. The method according to claim 2, characterized in that The executing the left broadcast execution plan to obtain the anti-semi-join result of the current node, and counting the anti-semi-join result of the current node and the anti-semi-join results returned by other nodes in the distributed database to obtain a modified anti-semi-join result of the current node, including: Executing the left table operator to obtain the join column data of the left table of the anti-semi-join operator in the current node; Executing the right table operator to obtain the join column data of the right table of the anti-semi-join operator in the current node; Executing the projection operator to generate data identification numbers for the connection column data of the left table; Executing the left data broadcast operator to broadcast the connection column data of the left table carrying the data identification number; Executing the anti-semi-join operator to obtain an anti-semi-join result of the current node, the anti-semi-join result including data selected from the join column of the left table that has no matching record with the join column of the right table and corresponding data identification numbers; executing the data sending operator to send the anti-semi-join result of the current node to other nodes in the corresponding distributed database according to the data identification number, so that each of the other nodes counts its own anti-semi-join result and the received anti-semi-join result to obtain its own modified anti-semi-join result; The anti-semi-join result correction operator is executed, the anti-semi-join result of the current node and the received anti-semi-join results returned by the other nodes are counted, and a corrected anti-semi-join result of the current node is obtained.

4. The method according to claim 3, characterized in that The executing the anti-semi-join result correction operator, counting the anti-semi-join result of the current node and the anti-semi-join results returned by the other nodes, and obtaining a corrected anti-semi-join result of the current node, includes: Executing the anti-semi-join result correction operator to count the data identification numbers that are identical between the anti-semi-join result of the current node and the anti-semi-join results of other nodes; The data identification number whose counting result is equal to the sum of the parallelism of the current node and the other nodes and the corresponding data are determined as the modified anti-semi-join result of the current node.

5. The method according to any one of claims 3 to 4, characterized in that The data identification number includes the node sequence number and line program number of the node where the connection column data of the left table is located.

6. The method according to claim 1, characterized in that The anti-semi-join operation includes: a hash-based anti-semi-join operation, an index-based anti-semi-join operation, a merge-based anti-semi-join operation and a nested-based anti-semi-join operation.

7. An anti-semi-join query device, characterized in that: include: Statement acquisition module, used to obtain SQL query statements; A statement parsing module parses the SQL query statement to obtain the join columns of the left table and the right table corresponding to the anti-semi-join operation; An execution plan generation module generates a left broadcast execution plan for an anti-semi-join operation according to the join columns of the left table and the join columns of the right table; The left broadcast execution plan includes: an anti-semi-join result correction operator, a data sending operator as a left child node of the anti-semi-join result correction operator, an anti-semi-join operator as a left child node of the data sending operator, a left data broadcast operator as a left child node of the anti-semi-join operator, a projection operator as a left child node of the left data broadcast operator, a left table operator as a left child node of the projection operator, and a right table operator as a right child node of the anti-semi-join operator; A query module is used to execute the SQL query statement according to the left broadcast execution plan to obtain an anti-semi-join query result of the SQL query statement.

8. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively coupled to the at least one processor; The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the anti-semi-join query method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the anti-semi-join query method according to any one of claims 1 to 6 when executed.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the anti-semi-join query method according to any one of claims 1 to 6 when executed by a processor.

Images