A method, apparatus, device, and medium for left outer join query in a distributed environment

Abstract

The application discloses a left outer join query method and device in a distributed environment, equipment and a medium. The method comprises the following steps: obtaining a target SQL statement; determining the connection column of the left table and the connection column of the right table corresponding to the outer join operation according to the target SQL statement; if the connection column of the left table and / or the connection column of the right table is not a distributed column, generating an execution plan; executing the target SQL statement according to the execution plan to obtain a target connection result set corresponding to the target SQL statement. Through the technical scheme of the application, the left outer join result can be obtained by broadcasting the left table data, the target connection result set is obtained by correcting the left outer join result, and when the data amount of the left table is small and the data amount of the right table is large, the network communication overhead can be effectively reduced, and the left outer join query performance is improved.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of vehicle technology, and in particular to a method, apparatus, device and storage medium for left outer join query in a distributed environment. Background Technology

[0002] When performing a left outer join operation in a distributed environment, the data on the right side is typically broadcast, or the data on both sides is redistributed. When the join columns are not all distributed columns, and the amount of data on the left side is smaller than that on the right side, both of these methods will cause significant network communication overhead, resulting in poor performance. Summary of the Invention

[0003] This invention provides a method, apparatus, device, and storage medium for left outer join queries in a distributed environment. This enables the acquisition of left outer join results by broadcasting data from the left table, and the acquisition of a target join result set by modifying the left outer join results. This effectively reduces network communication overhead and improves left outer join query performance when the amount of data in the left table is small and the amount of data in the right table is large.

[0004] According to one aspect of the present invention, a left outer join query method in a distributed environment is provided, comprising:

[0005] Obtain the target SQL statement;

[0006] Determine the join columns of the left table and the right table corresponding to the outer join operation based on the target SQL statement;

[0007] If the join columns of the left table and / or the join columns of the right table are non-distributed columns, an execution plan is generated, wherein the execution plan includes: a join result filtering operator, a first data receiving operator for the left child node of the join result filtering operator, a first data sending operator for the left child node of the first data receiving operator, a left outer join operator for the left child node of the first data sending operator, a second data receiving operator for the left child node of the left outer join operator and a first table related operator for the right child node, a second data sending operator for the left child node of the second data receiving operator, and a second table related operator for the left child node of the second data sending operator.

[0008] The target SQL statement is executed according to the execution plan to obtain the target join result set corresponding to the target SQL statement.

[0009] According to another aspect of the present invention, a left outer join query apparatus in a distributed environment is provided, the left outer join query apparatus in a distributed environment comprising:

[0010] The target SQL statement acquisition module is used to acquire the target SQL statement;

[0011] The join column determination module is used to determine the join columns of the left table and the right table corresponding to the outer join operation based on the target SQL statement.

[0012] An execution plan generation module is used to generate an execution plan if the join columns of the left table and / or the join columns of the right table are non-distributed columns. The execution plan includes: a join result filtering operator, a first data receiving operator for the left child node of the join result filtering operator, a first data sending operator for the left child node of the first data receiving operator, a left outer join operator for the left child node of the first data sending operator, a second data receiving operator for the left child node of the left outer join operator and a first table related operator for the right child node, a second data sending operator for the left child node of the second data receiving operator, and a second table related operator for the left child node of the second data sending operator.

[0013] The execution module is used to execute the target SQL statement according to the execution plan and obtain the target join result set corresponding to the target SQL statement.

[0014] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0015] At least one processor; and

[0016] A memory communicatively connected to the at least one processor; wherein,

[0017] The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the left outer join query method in a distributed environment as described in any embodiment of the present invention.

[0018] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions, the computer instructions being configured to cause a processor to execute and implement the left outer join query method in a distributed environment as described in any embodiment of the present invention.

[0019] This invention, in its embodiments, obtains a target SQL statement; determines the join columns of the left and right tables corresponding to the outer join operation based on the target SQL statement; if the join columns of the left and / or right tables are non-distributed columns, an execution plan is generated; and the target SQL statement is executed according to the execution plan to obtain the target join result set corresponding to the target SQL statement. This allows for obtaining the left outer join result by broadcasting data from the left table, and by correcting the left outer join result to obtain the target join result set. Therefore, when the left table has a small amount of data and the right table has a large amount of data, it can effectively reduce network communication overhead and improve the query performance of the left outer join.

[0020] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0021] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 This is a flowchart of a left outer join query method in a distributed environment according to an embodiment of the present invention;

[0023] Figure 2 This is a schematic diagram of the execution plan in an embodiment of the present invention;

[0024] Figure 3 This is a schematic diagram of a left outer join query device in a distributed environment according to an embodiment of the present invention;

[0025] Figure 4 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. Detailed Implementation

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

[0027] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0028] It is understood that before using the technical solutions disclosed in the various embodiments of this disclosure, users should be informed of the types, scope of use, and usage scenarios of the personal information involved in this disclosure in an appropriate manner in accordance with relevant laws and regulations, and user authorization should be obtained.

[0029] Example 1

[0030] Figure 1 This is a flowchart illustrating a left outer join query method in a distributed environment, provided by an embodiment of the present invention. This embodiment is applicable to left outer join queries in a distributed environment. The method can be executed by a left outer join query device in a distributed environment as described in this embodiment. This device can be implemented in software and / or hardware, such as... Figure 1 As shown, the method specifically includes the following steps:

[0031] S110, retrieve the target SQL statement.

[0032] The target SQL statement is the SQL statement used to perform a left outer join. For example, the target SQL statement could be SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.D1.

[0033] S120, determine the join columns of the left table and the right table corresponding to the outer join operation based on the target SQL statement.

[0034] Specifically, the join columns of the left and right tables corresponding to the outer join operation are determined based on the target SQL statement. For example, this could involve creating tables T1 and T2:

[0035] CREATE TABLE T1(C1 INT,C2 INT,ID INT)PARTITION BY HASH(ID)PARTITIONS2STORAGE ON(TS1,TS2);

[0036] CREATE TABLE T2(D1 INT,D2 INT,ID INT)PARTITION BY HASH(ID)PARTITIONS2STORAGE ON(TS1,TS2);

[0037] Table T1 uses the ID column as the distribution column, dividing the table into two HASH sub-tables, which are stored on two storage nodes, TS1 and TS2, respectively.

[0038] Table T2 uses the ID column as the distribution column, dividing the table into two HASH sub-tables, which are stored on two storage nodes, TS1 and TS2, respectively.

[0039] The target SQL statement is: SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.D1;

[0040] The above statement indicates a left outer join between tables T1 and T2, with C1 as the join column in table T1 and D1 as the join column in table T2, neither of which are distributed columns. Since the data in tables T1 and T2 are stored across multiple nodes, matching data may not reside on the same node. Therefore, simply summarizing the calculation results from each node may result in an incorrect join set.

[0041] It's important to note that directly broadcasting data from the left-hand table can lead to an incorrect final join result set. For example, the current node broadcasts a data entry (1,2,3) from table T1 to other nodes. Upon receiving this data, each node searches for a matching entry from table T2. If node 1 finds a matching entry (1,4,5), the join result is (1,2,3,1,4,5). If node 2 does not find a matching entry, the join result is (1,2,3,NULL,NULL,NULL). During the final aggregation, the result set may contain both (1,2,3,1,4,5) and (1,2,3,NULL,NULL,NULL), resulting in an incorrect result set.

[0042] S130, if the join columns of the left table and / or the join columns of the right table are not distributed columns, then generate an execution plan.

[0043] The execution plan includes: a join result filtering operator, a first data receiving operator for the left child node of the join result filtering operator, a first data sending operator for the left child node of the first data receiving operator, a left outer join operator for the left child node of the first data sending operator, a second data receiving operator for the left child node of the left outer join operator and a first table related operator for the right child node, a second data sending operator for the left child node of the second data receiving operator, and a second table related operator for the left child node of the second data sending operator.

[0044] The first table-related operator can be a first table data scan operator, used to scan the data in the right-hand table. The second table-related operator can be a second table data scan operator, used to scan the data in the left-hand table. The second data sending operator is used to broadcast the data of the left-hand table in the current node to the second data receiving operator on each node. The second data receiving operator is used to receive the data sent by the second data sending operator. The left outer join operator can be a hash left outer join operator or an index left outer join operator, used to implement the left outer join calculation function. The first data sending operator is used to send the join result corresponding to the current node to the corresponding first data receiving operator in a data redistribution manner. The first data receiving operator is used to receive the data sent by the first data sending operator.

[0045] Furthermore, the operator types on both sides of the left outer join operator can be arbitrary before inserting the send and receive operators.

[0046] It should be noted that in a distributed environment, each node executes statements according to the generated execution plan.

[0047] In a specific example, the generated execution plan is as follows: Figure 2 As shown, the execution plan includes: a join result filtering operator (NULL_DIST), a first data receive operator (Recv) for the left child node of the join result filtering operator, a first data send operator (Send(by T1.ROWID)) for the left child node of the first data receive operator, a hash left outer join operator (Hash Left Join) for the left child node of the first data send operator, a second data receive operator (Recv) for the left child node of the hash left outer join operator and a first table data scan operator (Table Scan(T2)) for the right child node, a second data send operator (Send) for the left child node of the second data receive operator, and a second table data scan operator (Table Scan(T1)) for the left child node of the second data send operator.

[0048] S140, Execute the target SQL statement according to the execution plan to obtain the target join result set corresponding to the target SQL statement.

[0049] Specifically, the method for obtaining the target join result set corresponding to the target SQL statement by executing the target SQL statement according to the execution plan can be as follows: Execute the target SQL statement according to the execution plan to obtain an initial join result set; filter the initial join result set to obtain the target join result set corresponding to the target SQL statement. Alternatively, the method can be as follows: Scan the left and right table data within the current node; broadcast the left table data within the current node; receive the target data from the left table, where the target data from the left table includes the left table data from all nodes storing left table data in the distributed environment; perform a left outer join query based on the target data from the left table and the right table data in the current node to obtain the join result corresponding to the current node; send the join result corresponding to the current node to other nodes in the distributed environment; receive the join results sent by other nodes in the distributed environment; filter the join results received by the current node to obtain the filtered join result set corresponding to the current node; merge the filtered join result sets corresponding to each node in the distributed environment to obtain the target join result set.

[0050] Optionally, the target SQL statement is executed according to the execution plan to obtain the target join result set corresponding to the target SQL statement, including:

[0051] Obtain the data of the two child nodes of the left outer join operator, wherein the data of the two child nodes includes: the left table data and the right table data of the current node;

[0052] Broadcast the data from the left table within the current node;

[0053] Receive the target data from the left table, where the target data from the left table includes: the left table data from all nodes storing the left table data in the distributed environment;

[0054] Perform a left outer join query based on the target data in the left table and the data in the right table in the current node to obtain the join result corresponding to the current node;

[0055] Send the connection results corresponding to the current node to other nodes in the distributed environment;

[0056] Receive connection results sent by other nodes in a distributed environment;

[0057] Filter the connection results received by the current node to obtain the filtered connection result set corresponding to the current node;

[0058] The filtered connection result sets corresponding to each node in the distributed environment are merged to obtain the target connection result set.

[0059] Specifically, the method to obtain the data of the two child nodes of the left outer join operator can be as follows: scan the left table data in the current node using the second table data scan operator, and scan the right table data in the current node using the first table data scan operator.

[0060] Specifically, the way to broadcast the left table data in the current node can be: send the left table data in the current node to the second data receiving operator on each node in the distributed environment through the second data sending operator.

[0061] Specifically, the method for receiving the target data of the left table can be as follows: receive the left table data from all nodes storing left table data in the distributed environment through the second data receiving operator, and generate the target data based on the left table data from all nodes storing left table data in the distributed environment.

[0062] Specifically, the method for sending the connection results corresponding to the current node to other nodes in a distributed environment can be as follows: The connection results corresponding to the current node are sent to the corresponding first data receiving operator via data redistribution using the first data sending operator. It should be noted that the first data sending operator can send the connection results corresponding to the current node to the first data receiving operator corresponding to a set rule, which can be: by T1.ROWID, meaning that based on the left-hand row identifier, the first data sending operator sends the connection results with the same left-hand row identifier in the connection results corresponding to the current node to the first data receiving operator of the same node.

[0063] Specifically, the method for receiving connection results sent by other nodes in a distributed environment can be as follows: The first data receive operator is used to receive connection results sent by the first data send operator of other nodes. It should be noted that the connection results received by the first data receive operator are the same as the connection results sent by the first data send operator.

[0064] Specifically, the method for filtering the connection results received by the current node to obtain the filtered connection result set corresponding to the current node can be as follows: determine the initial connection result set based on the connection results received by the current node, and filter the connection results in the initial connection result set based on the right-hand table data of each connection result in the initial connection result set to obtain the filtered connection result set corresponding to the current node.

[0065] Optionally, the first data sending operator carries the target sending rule;

[0066] Accordingly, the connection results corresponding to the current node are sent to other nodes in the distributed environment, including:

[0067] Based on the left-hand row identifier corresponding to the connection result, the connection results corresponding to different left-hand row identifiers are sent to different nodes in the distributed environment.

[0068] The target sending rule can be Send(by T1.ROWID), that is, the SEND identifier sends the connection result to the RECV of different nodes according to the ROWID in the T1 table.

[0069] Optionally, the connection results received by the current node are filtered to obtain a filtered set of connection results corresponding to the current node, including:

[0070] Obtain a hash table, wherein the hash table includes: left row identifiers and matching statuses corresponding to the left row identifiers;

[0071] Add the join results of the current node with non-NULL values ​​from the right-hand table to the filtered join result set of the current node;

[0072] Add the connection results of the current node whose right table data is NULL and whose left table row identifier does not exist in the hash table to the cache;

[0073] Add the connection results whose matching status is "failed" corresponding to the left row of the cache to the filtered connection result set of the current node.

[0074] Furthermore, connection results for the current node where the right-hand table data is NULL and the corresponding left-hand table row identifier does not exist in the hash table are added to the cache. In other words, connection results where the right-hand table data is NULL for the first time are added to the cache. It's important to note that adding connection results to the cache is a secondary filtering process after the initial filtering of all connection results for the current node. This prevents the loss of accurate connection results due to errors in the initial filtering, allowing for more precise filtering of all connection results for the current node.

[0075] Specifically, after adding the connection results where the right table data of all connection results of the current node is NULL and the left table row identifier corresponding to the connection result does not exist in the hash table to the cache, the matching status corresponding to the left table row identifier in the hash table is updated to match failure.

[0076] Specifically, the method to add the connection results with a failed match status corresponding to the left-hand table row in the cache to the filtered connection result set of the current node can be as follows: After traversing all connection results of the current node, traverse the connection results in the cache and perform the following operations for each connection result in the cache:

[0077] Retrieve the left-hand row identifier corresponding to the current connection result, and determine the matching result in the hash table corresponding to that left-hand row identifier. If the match is successful, ignore the current connection result and continue iterating to the next connection result; if the match fails, add the current connection result to the filtered connection result set of the current node, and continue iterating to the next connection result. After iterating through all connection results in the cache, the filtered connection result set corresponding to each node can be determined. Summarize the filtered connection result sets corresponding to all nodes to obtain the final result set.

[0078] Optional, also includes:

[0079] For all connection results of the current node, add the left table row identifier of the connection result whose right table data is not NULL and whose corresponding left table row identifier does not exist in the hash table to the hash table, and update the matching status corresponding to the left table row identifier to match successfully;

[0080] For all connection results of the current node, if the data in the right table is not NULL, and there is a corresponding left table row identifier in the hash table, and the matching status of the left table row identifier corresponding to the connection result whose matching status is failed is updated to successful;

[0081] For all connection results of the current node, if the data in the right table is not NULL, there is a corresponding left table row identifier in the hash table, and the matching status of the left table row identifier corresponding to the successful connection result remains unchanged.

[0082] For all connection results of the current node, if the data in the right table is NULL and there is no corresponding left table row identifier in the hash table, add the left table row identifier of the connection result to the hash table, and update the matching status corresponding to the left table row identifier to match failure.

[0083] Optional, also includes:

[0084] Discard all connection results of the current node where the data in the right table is NULL and the row identifier in the left table indicates a successful match.

[0085] Discard all connection results of the current node where the data in the right table is NULL and the matching status of the row in the left table is "match failed".

[0086] In a specific example, the NULL_DIST operator executes as follows: a hash table is used to store the matching status corresponding to the row identifiers on the left, and a specific cache space is used to store the empty join results. Assume the hash table is named HT, the cache space is named unmatch-cache, and both HT and unmatch-cache are initially empty.

[0087] First, iterate through all the connection results of the current node, and perform the following operations for each connection result:

[0088] Step 1. Determine if the data in the right table of the current join result is NULL. If it is not NULL, proceed to Step 2; if it is NULL, proceed to Step 3.

[0089] Step 2. Obtain the left table row identifier corresponding to the current connection result, search for the left table row identifier in HT. If it is found and the matching status is "match successful", do not modify the matching result; if it is found and the matching status is "match failed", change the matching status corresponding to the left table row identifier to "match successful".

[0090] If not found, add the identifier of the left-hand table row to HT and update its corresponding matching status to "match successful".

[0091] It should be noted that regardless of the search result in the hash table, the current join result is added to the target result set, and the process continues to traverse the next join result.

[0092] 3. Obtain the left-hand row identifier corresponding to the current connection result, search for the left-hand row identifier in HT. If found, ignore the current connection result and continue to traverse the next connection result; if not found, add the left-hand row identifier to HT, set its corresponding matching status to match failure, add the current connection result to the unmatch-cache cache, and continue to traverse the next connection result.

[0093] In addition, if the data in the right-hand table of the current connection result is null, and there is already a successfully matched record in HT, the current connection result is discarded; if the data in the right-hand table of the current connection result is null, and there is already a failed match record in HT, the current connection result is also discarded.

[0094] After iterating through all connection results on the current node, iterate through all connection results in the unmatch-cache cache and perform the following operations for each connection result:

[0095] Retrieve the left-hand row identifier corresponding to the current connection result, and determine the matching status of that left-hand row identifier in HT. If the match is successful, ignore the current connection result and continue traversing the next connection result; if the match fails, add the current connection result to the filtered connection result set of the current node, and continue traversing the next connection result. After traversing all connection results in the cache, the filtered connection result set of the current node can be determined.

[0096] By summing up the filtered connection result sets corresponding to all nodes, we can obtain the final result set.

[0097] Optionally, if the join columns of the left-hand table and / or the join columns of the right-hand table are not distributed columns, an execution plan is generated, including:

[0098] If the join columns of the left table and / or the join columns of the right table are non-distributed columns, the cost of broadcasting data on the left is less than the cost of broadcasting data on the right, and the cost of broadcasting data on the left is less than the cost of redistributing data on both sides, then an execution plan is generated.

[0099] It should be noted that if the cost of broadcasting data on the left side is less than the cost of broadcasting data on the right side, and the cost of broadcasting data on the left side is less than the cost of redistributing data on both sides, then the cost of broadcasting data on the left side is minimized. Using the left-side data broadcasting method when its cost is minimized can effectively reduce network communication overhead and improve the performance of left outer join queries.

[0100] Specifically, the determination that the cost of broadcasting data on the left side is less than the cost of broadcasting data on the right side, and that the cost of broadcasting data on the left side is less than the cost of redistributing data on both sides, can be made as follows: First, obtain the number of data items in the left and right tables. If the number of data items in the left table is less than the number of data items in the right table, then the cost of broadcasting data on the left side is less than the cost of broadcasting data on the right side, and that the cost of broadcasting data on the left side is less than the cost of redistributing data on both sides. In other words, the cost of broadcasting data on the left side is the lowest. Alternatively, the determination can be made as follows: If the number of rows in the left table is less than the number of rows in the right table, then the cost of broadcasting data on the left side is less than the cost of broadcasting data on the right side, and that the cost of broadcasting data on the left side is less than the cost of redistributing data on both sides.

[0101] Optionally, if the join columns of the left-hand table and / or the join columns of the right-hand table are not distributed columns, an execution plan is generated, including:

[0102] If the join column of the left table and / or the join column of the right table are not distributed columns, then get the number of rows in the left table;

[0103] If the number of rows in the left table is less than the number of rows in the right table, an execution plan is generated.

[0104] In a specific example, the embodiment of the present invention mainly adjusts the generation methods of the Send and Recv operators in the planning generation stage, and introduces a new NULL_DIST operator to achieve the purpose of directly broadcasting the left-hand data and correcting the final result set.

[0105] The plan generation phase includes the following process:

[0106] Step 1: Determine whether the current left outer join operation (including hash left outer join and index left outer join) satisfies the following two conditions simultaneously. If it does, execute Step 2 after generating the left outer join operator; otherwise, generate an execution plan according to the right data broadcast or the data redistribution on both sides.

[0107] a. The join column connecting one side or both sides is a non-distributed column (if both sides are distributed columns, the intelligent partition join method can be used without optimization, that is, after scanning the data of the left and right tables at each node, the left outer join calculation is performed directly).

[0108] b. The system, through cost model evaluation, believes that using the left-side data broadcasting method has the lowest cost;

[0109] Step 2: Insert a pair of SEND and RECV operators to the left of the current left outer join operator. The SEND operator is a broadcast type SEND operator, used to send the data from the left to each node. Proceed to Step 3;

[0110] Step 3: Insert a pair of SEND and RECV operators above the current left outer join operator, and then insert a NULL_DIST operator above the RECV operator. SEND distributes the data from the left outer join operator to each node according to the unique identifier of each row in the left table; data with the same unique identifier will be distributed to the same node. The unique identifier can be ROWID, which is the row number corresponding to each row in the table.

[0111] Suppose table T1 contains the data (1,2,3), with a corresponding ROWID of 1. The join result calculated on node 1 for this data is (1,2,3,1,4,5), and the join result calculated on node 2 is (1,2,3,NULL,NULL,NULL). Since both results correspond to row number 1 in table T1, they will be assigned to the same node for further processing. Let's assume they are assigned to node 1 for processing.

[0112] The NULL_DIST operator removes duplicate NULL values ​​from multiple join results corresponding to the same unique identifier, including:

[0113] a. If the join result contains both non-empty join results (e.g., (1,2,3,1,4,5)) and empty join results (e.g., (1,2,3,NULL,NULL,NULL)), then eliminate all empty join results and keep only the non-empty join results;

[0114] b. If the join result contains only one or more non-empty join results, then retain all non-empty join results;

[0115] c. If the join result contains only one or more empty join results, then only one empty join result is retained.

[0116] (Other operators, such as the Table Scan operator, will still be generated as usual.)

[0117] After the above operations, the execution plan of the SQL statement is as follows: Figure 2 As shown, according to Figure 2 The execution plan states that when node 1 executes the NULL_DIST operator, it will target the two join results (1,2,3,1,4,5) and (1,2,3,NULL,NULL,NULL) with ROWID 1 on this node.

[0118] Eliminate (1,2,3,NULL,NULL,NULL), and keep only (1,2,3,1,4,5).

[0119] Finally, the connection results of each node are summarized to obtain the final connection result set.

[0120] The technical solution of this embodiment obtains the target SQL statement; determines the join columns of the left and right tables corresponding to the outer join operation based on the target SQL statement; if the join columns of the left and / or right tables are non-distributed columns, an execution plan is generated; the target SQL statement is executed according to the execution plan to obtain the target join result set corresponding to the target SQL statement. This method can obtain the left outer join result by broadcasting data from the left table, and obtain the target join result set by correcting the left outer join result. Therefore, when the amount of data in the left table is small and the amount of data in the right table is large, it can effectively reduce network communication overhead and improve the query performance of the left outer join.

[0121] Example 2

[0122] Figure 3 This is a schematic diagram of a left outer join query device in a distributed environment, provided by an embodiment of the present invention. This embodiment is applicable to left outer join queries in a distributed environment. The device can be implemented using software and / or hardware, and can be integrated into any device that provides left outer join query functionality in a distributed environment, such as… Figure 3As shown, the left outer join query device in the distributed environment specifically includes: a target SQL statement acquisition module 310, a join column determination module 320, an execution plan generation module 330, and an execution module 340.

[0123] The target SQL statement acquisition module is used to acquire the target SQL statement.

[0124] The join column determination module is used to determine the join columns of the left table and the right table corresponding to the outer join operation based on the target SQL statement.

[0125] An execution plan generation module is used to generate an execution plan if the join columns of the left table and / or the join columns of the right table are non-distributed columns. The execution plan includes: a join result filtering operator, a first data receiving operator for the left child node of the join result filtering operator, a first data sending operator for the left child node of the first data receiving operator, a left outer join operator for the left child node of the first data sending operator, a second data receiving operator for the left child node of the left outer join operator and a first table related operator for the right child node, a second data sending operator for the left child node of the second data receiving operator, and a second table related operator for the left child node of the second data sending operator.

[0126] The execution module is used to execute the target SQL statement according to the execution plan and obtain the target join result set corresponding to the target SQL statement.

[0127] The above-described products can perform the methods provided in any embodiment of the present invention, and have the corresponding functional modules and beneficial effects for performing the methods.

[0128] Example 3

[0129] Figure 4 A schematic diagram of an electronic device 10 that can be used to implement embodiments 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 processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

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

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

[0132] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the left outer join query method in a distributed environment.

[0133] In some embodiments, the distributed left outer 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 distributed left outer join query method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the distributed left outer join query method by any other suitable means (e.g., by means of firmware).

[0134] Various embodiments of the systems and techniques described above 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), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0135] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

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

[0137] 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 provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, 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 sound input, voice input, or tactile input).

[0138] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0139] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through a communication network. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0140] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0141] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for left outer join query in a distributed environment, characterized in that, The method comprises the following steps: acquiring a target SQL statement; determining, according to the target SQL statement, a connection column of a left table and a connection column of a right table corresponding to an outer join operation; generating an execution plan if the connection column of the left table and / or the connection column of the right table is not a distributed column, wherein the execution plan comprises a connection result filtering operator, a first data receiving operator of a left child node of the connection result filtering operator, a first data sending operator of a left child node of the first data receiving operator, a left outer join operator of a left child node of the first data sending operator, a second data receiving operator and a first table correlation operator of a right child node of the left outer join operator, a second data sending operator of a left child node of the second data receiving operator, and a second table correlation operator of a left child node of the second data sending operator; executing the target SQL statement according to the execution plan to obtain a target connection result set corresponding to the target SQL statement.

2. The method of claim 1, wherein, The method for executing the target SQL statement according to the execution plan to obtain a target connection result set corresponding to the target SQL statement comprises the following steps: acquiring data of two child nodes of a scan left outer join operator, wherein the data of the two child nodes comprises left table data and right table data in a current node; broadcasting the left table data in the current node; receiving target data of the left table, wherein the target data of the left table comprises left table data in all nodes storing the left table data in a distributed environment; performing a left outer join query according to the target data of the left table and the right table data in the current node to obtain a connection result corresponding to the current node; sending the connection result corresponding to the current node to other nodes in the distributed environment; receiving the connection result sent by the other nodes in the distributed environment; filtering the connection result received by the current node to obtain a filtered connection result set corresponding to the current node; merging the filtered connection result sets corresponding to all nodes in the distributed environment to obtain the target connection result set.

3. The method of claim 2, wherein, The first data sending operator carries a target sending rule. Correspondingly, the method for sending the connection result corresponding to the current node to other nodes in the distributed environment comprises the following steps: sending the connection result corresponding to different left table row identifiers to different nodes in the distributed environment according to the left table row identifiers.

4. The method of claim 2, wherein, The method for filtering the connection result received by the current node to obtain a filtered connection result set corresponding to the current node comprises the following steps: acquiring a hash table, wherein the hash table comprises left table row identifiers and matching states corresponding to the left table row identifiers; adding, to the filtered connection result set of the current node, connection results in which right table data is not NULL in all the connection results of the current node; adding, to a cache, connection results in which right table data is NULL and the left table row identifiers corresponding to the connection results do not exist in the hash table in all the connection results of the current node; adding, to the filtered connection result set of the current node, connection results in which the matching states corresponding to the left table row identifiers in the cache are matching failures.

5. The method of claim 4, wherein, The method further comprises the following steps: adding the left table row identifier of the join result in which the right table data is a non-NULL value and the corresponding left table row identifier does not exist in the hash table to the hash table, and updating the matching state corresponding to the left table row identifier to matching success; updating the matching state corresponding to the left table row identifier of the join result in which the right table data is a non-NULL value, the corresponding left table row identifier exists in the hash table, and the matching state corresponding to the left table row identifier in the hash table is matching failure to matching success; keeping the matching state corresponding to the left table row identifier of the join result in which the right table data is a non-NULL value, the corresponding left table row identifier exists in the hash table, and the matching state corresponding to the left table row identifier in the hash table is matching success unchanged; adding the left table row identifier of the join result in which the right table data is a NULL value and the corresponding left table row identifier does not exist in the hash table to the hash table, and updating the matching state corresponding to the left table row identifier to matching failure.

6. The method of claim 4, wherein, Further comprising: discarding the join result in which the right table data is a NULL value and the matching state corresponding to the left table row identifier is matching success; discarding the join result in which the right table data is a NULL value and the matching state corresponding to the left table row identifier is matching failure.

7. The method of claim 1, wherein, If the join column of the left table and / or the join column of the right table is not a distributed column, generating an execution plan, comprising: If the join column of the left table and / or the join column of the right table is not a distributed column, the left data broadcast cost is less than the right data broadcast cost, and the left data broadcast cost is less than the two-side data redistribution cost, generating an execution plan.

8. The method of claim 1, wherein, If the join column of the left table and / or the join column of the right table is not a distributed column, generating an execution plan, comprising: If the join column of the left table and / or the join column of the right table is not a distributed column, obtaining the number of rows of the left table; If the number of rows of the left table is less than the number of rows of the right table, generating an execution plan.

9. A device for left outer join query in a distributed environment, characterized by, Comprising: a target SQL statement obtaining module, configured to obtain a target SQL statement; a join column determining module, configured to determine the join column of a left table and the join column of a right table corresponding to an outer join operation according to the target SQL statement; an execution plan generating module, configured to generate an execution plan if the join column of the left table and / or the join column of the right table is not a distributed column, wherein the execution plan comprises: a join result filtering operator, a left child node first data receiving operator of the join result filtering operator, a left child node first data sending operator of the first data receiving operator, a left child node left outer join operator of the first data sending operator, a left child node second data receiving operator and a right child node first table correlation operator of the left outer join operator, a left child node second data sending operator of the second data receiving operator, and a second table correlation operator of the second data sending operator; An execution module is configured to execute the target SQL statement according to the execution plan, and obtain a target connection result set corresponding to the target SQL statement.

10. An electronic device, comprising: The electronic device comprises: at least one processor; and a memory connected with the at least one processor in communication; wherein 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 to enable the at least one processor to execute the left outer join query method in a distributed environment according to any one of claims 1-8.

11. A computer readable storage medium, characterized in that, The computer readable storage medium stores computer instructions, and the computer instructions are used to enable the processor to implement the left outer join query method in a distributed environment according to any one of claims 1-8 when executed.

Images