Table connection method and device

Abstract

The embodiment of the invention provides a table connection method and device.The method comprises the steps that if a first table to be connected and a second table to be connected currently are connected based on a non-broadcast hash connection mode, the capacity of a memory occupied by the fact that data fragments of the first table are distributed to all computing nodes is determined; wherein the data volume of the first table is smaller than that of the second table; the data of the first table is subjected to fragmentation operation; and if the occupied memory capacity is not greater than the preset memory capacity of the computing node, converting the non-broadcast hash connection mode into a broadcast hash connection mode to execute connection of the first table and the second table.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of computing engine technology, and more particularly, to a table connection method and device. Background Art

[0002] In common computing engines, there are often multiple types of operations for connection, such as broadcast hash join (BHJ) operation, shuffled hash join (SHJ) connection and sort merge join (SMJ) operation. Among them, since the BHJ operation usually only distributes the data in the table with a large amount of data and the table with a small amount of data to multiple computing nodes, and then performs a local connection operation with the data in the table with a large amount of data on each computing node, and does not perform data distribution (Shuffled) on the data in the table with a large amount of data. Other connection types usually also perform data distribution on the data in the table with a large amount of data. Therefore, the network resources, computing resources and computing time consumed by broadcast hash connection are usually better than other connection types. However, in the existing scheme for determining the execution plan (Plan) for table connection in the computing engine, the confirmation method for the connection type makes the probability of performing table connection through broadcast hash connection low, that is, the coverage rate of broadcast hash connection in the execution plan is low. Furthermore, this type of execution plan consumes high network resources, computing resources, and computing time, and has poor computing performance. Therefore, a new table connection method is needed. Summary of the invention

[0003] The embodiments in this specification aim to provide a method and device for table connection, which can effectively improve the coverage of broadcast hash connections in execution plans for connecting tables, thereby reducing the network resources, computing resources and computing time consumed by the execution plans, improving the computing performance of the execution plans, and solving the shortcomings of the prior art.

[0004] According to a first aspect, a method for improving the computing performance of a table join execution plan in a computing engine is provided, comprising:

[0005] If the first table and the second table to be connected are connected based on a non-broadcast hash connection, the data shards of the first table are distributed to the memory capacity occupied by each computing node, wherein the data volume of the first table is smaller than the data volume of the second table; and the data of the first table is subjected to sharding operation;

[0006] If the occupied memory capacity is not greater than the preset memory capacity of the computing node, the non-broadcast hash join method is converted into a broadcast hash join method to execute the connection between the first table and the second table.

[0007] In a possible implementation, the first table and the second table are connected based on a non-broadcast hash connection, including:

[0008] By running an execution plan in a target computing engine, the first table and the second table are connected in a manner based on a non-broadcast hash connection, wherein the execution plan includes a first branch and a second branch of parallel computing, the first branch is used to read data of the first table and perform a sharding operation on the data of the first table, and the second branch is used to read data of the second table and perform a sharding operation on the data of the second table;

[0009] Determining the capacity of the memory occupied by the data slices of the first table distributed to each computing node includes: blocking the second branch, executing the first branch, and determining the capacity of the memory occupied by the data slices of the first table distributed to each computing node.

[0010] In a possible implementation, reading the first data of the first table includes: if the occupied memory capacity is not greater than the preset memory capacity of the computing node, converting the non-broadcast hash connection mode into a broadcast hash connection mode to execute the connection between the first table and the second table, including:

[0011] If the occupied memory capacity is not greater than the preset memory capacity of the computing node, the second branch is modified to remove the portion that performs the sharding operation on the data of the second table, and the connection method of the first table and the second table is converted into a broadcast hash connection method; the modified second branch is unblocked, and the connection result of the first table and the second table is determined based on the execution result of the modified second branch.

[0012] In a possible implementation, the method further includes: if the occupied memory capacity is greater than the preset memory capacity of the computing node, unblocking the second branch, and determining a connection result of the first table and the second table based on the execution result of the unblocked second branch.

[0013] In a possible implementation, the execution plan includes a first instruction instructing to execute the second branch;

[0014] Blocking the execution of the second branch includes:

[0015] The first instruction is removed from the execution plan, and a preset instruction indicating blocking of the second branch is inserted at the original position of the first instruction.

[0016] In a possible implementation, the second branch includes a second instruction for performing a slicing operation on data in the second table;

[0017] Modifying the second branch to remove a portion that performs a slicing operation on the data in the second table includes: removing the second instruction from the second branch.

[0018] In a possible implementation, unblocking the modified second branch and determining a connection result of the first table and the second table based on an execution result of the modified second branch includes: unblocking the modified second branch and determining a connection result of the first table and the second table based on an execution result of the executed first branch and an execution result of the modified second branch.

[0019] In a possible implementation, converting the connection mode of the first table and the second table into a broadcast hash connection mode includes: adding a third branch to the execution plan, the third branch including the first branch, the third branch further used to determine complete data of the first table according to data shards of the first table on each computing node, and sending the complete data of the first table to the multiple computing nodes:

[0020] Determining the execution result of the broadcast hash join operation based on the execution result of the executed first branch and the modified execution result of the second branch includes:

[0021] Based on the execution result of the executed first branch, the execution result of the third branch is determined, and based on the execution result of the third branch and the modified execution result of the second branch, the connection result of the first table and the second table is determined.

[0022] In a possible implementation, before the sharding operation is performed on the data in the first table, a data filtering operation and / or a data expansion operation is also performed.

[0023] In a possible implementation manner, the data of the first table includes data of the first table read from a permanent storage medium.

[0024] According to a second aspect, a table connection device is provided, which is deployed in an image acquisition terminal, and the device includes:

[0025] The determining unit is configured to, if the first table and the second table to be connected are connected based on a non-broadcast hash connection, determine the capacity of the memory occupied by the data shards of the first table distributed to each computing node; wherein the amount of data in the first table is less than the amount of data in the second table; and the data of the first table is subjected to a sharding operation;

[0026] The conversion unit is configured to convert the non-broadcast hash connection method into a broadcast hash connection method to execute the connection between the first table and the second table if the occupied memory capacity is not greater than the preset memory capacity of the computing node.

[0027] According to a third aspect, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed in a computer, the computer is caused to execute the method described in the first aspect.

[0028] According to a fourth aspect, a computing device is provided, comprising a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, the method described in the first aspect is implemented.

[0029] By utilizing one or more of the methods, apparatuses, computing devices, and storage media in the above aspects, the coverage of broadcast hash connections in the execution plan for connecting tables can be effectively improved, thereby reducing the computing resources, network resources, and computing time consumed by the execution plan, and improving the computing performance of the execution plan. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0031] Figure 1 A schematic diagram showing a scheme for determining whether to connect a table by a broadcast hash join operation;

[0032] Figure 2 A schematic diagram showing an execution plan in a computing engine according to an embodiment of the present specification;

[0033] Figure 3 A schematic diagram showing a method for improving the computing performance of a table join execution plan in a computing engine according to an embodiment of the present specification;

[0034] Figure 4 A flow chart showing a method for improving the computing performance of a table join execution plan in a computing engine according to an embodiment of the present specification;

[0035] Figure 5 A schematic diagram showing an execution plan according to another embodiment of the present specification;

[0036] Figure 6 A schematic diagram showing conversion to a broadcast hash connection according to an embodiment of this specification;

[0037] Figure 7A schematic diagram showing a modified execution plan according to another embodiment of the present specification;

[0038] Figure 8 A schematic diagram showing a hash connection that is not converted to a broadcast connection according to an embodiment of the present specification;

[0039] Fig. 9 A structural diagram of a device for improving the computing performance of a table join execution plan in a computing engine according to an embodiment of the present specification is shown. DETAILED DESCRIPTION

[0040] The solution provided in this invention book will be described below in conjunction with the accompanying drawings.

[0041] As mentioned above, in common computing engines, there are often multiple types of operations for connection, such as Broadcast Hash Join (BHJ) operation, Shuffled Hash Join (SHJ) connection and Sort Merge Join (SMJ) operation. Among them, since the BHJ operation usually only distributes the data in the table with large data volume and the table with small data volume to multiple computing nodes, and then performs a local connection operation with the data in the table with large data volume on each computing node, and does not distribute the data in the table with large data volume. For other connection operations, such as SHJ or SMJ, the data in the table with large data volume and the table with small data volume are distributed to multiple computing nodes for local connection operations. Therefore, the computing performance of the broadcast hash connection is usually higher than that of other connection types, and the computing resources and network resources consumed are also less.

[0042] However, in the existing solution for determining the execution plan (Plan) for connecting tables in the computing engine, the confirmation method for whether to perform the broadcast hash connection operation makes the probability of connecting tables through the broadcast hash connection low. For example, Figure 1 A schematic diagram showing a scheme for determining whether to connect tables by broadcast hash join operation. The amount of data in table A is less than the amount of data in table B. Figure 1As shown, the scheme is mainly determined based on the predicted value of the memory capacity occupied by the data of Table A after the data is loaded into the memory (or simply the predicted capacity) and the preset threshold. Specifically, for example, when the predicted capacity is not greater than the preset threshold, Table A and Table B are connected by a broadcast hash join operation. Otherwise, Table A and Table B are connected by other connection operations. However, since the prediction of the memory capacity occupied by the data of Table A after the data is loaded into the memory is essentially inaccurate, and since the capacity of the data of Table A sent to each computing node by the broadcast hash join operation exceeds the actual available memory capacity on the computing node, the broadcast hash join operation will fail, resulting in the execution failure of the execution plan for table connection. In order to ensure the security of the execution plan, the value of the preset threshold is usually only set to be relatively conservative, that is, it is set to a value lower than the memory capacity (referred to as available memory capacity or available capacity) that can actually be used for table connection in the computing node. In one example, for example, it is only set to 10MB. The lower preset threshold results in a lower probability of connecting the table through a broadcast hash join operation in the execution plan for connecting the table, which in turn makes the calculation performance of the execution plan for connecting the table lower, and consumes more network resources, computing resources and computing time.

[0043] In order to solve the above technical problems, an embodiment of this specification provides a method for improving the computing performance of a table join execution plan in a computing engine. Figure 2 A schematic diagram showing an execution plan in a computing engine according to an embodiment of the present specification is shown. First, for example, Figure 2 The execution plan shown is executed, for example, by a connection operation other than BHJ (for example, SHJ, indicated by an SHJ instruction). The execution result of the SHJ operation is determined according to the execution results of the execution branches (branch 1 and branch 2) of the parallel computing, wherein branch 1 is used to read the data in table A with a relatively small amount of data (for example, by reading instructions), and slice the data in table A read and distribute different slices to multiple computing nodes (for example, by distributing Shuffle instructions), and branch 2 is used to read the data in table B with a relatively large amount of data (for example, by reading instructions), and slice the data in table B read and distribute different slices to multiple computing nodes (for example, by distributing Shuffle instructions). The execution plan can be predetermined based on the predicted capacity of the data in table A loaded in the memory being greater than a preset threshold. Figure 3 A schematic diagram showing a method for improving the computing performance of a table join execution plan in a computing engine according to an embodiment of the present specification. Figure 3 As shown, in obtaining Figure 2After the execution plan shown, branch 2 can be modified to remove the part in which the data in table B to be read is sliced ​​and the different slices are distributed to multiple computing nodes. In one example, for example, the distribution instruction can be removed. After modifying branch 2, the execution of the modified branch 2 can be blocked. In one example, for example, the instruction indicating the execution of branch 2 can be removed and replaced with an instruction that blocks the execution), and the execution of branch 1 is started. Since the distribution instruction included in branch 1 can be used to count the capacity of the table data that has been actually loaded into the memory and distributed to each computing node. Therefore, the actual capacity of the data of table A loaded in the memory can be determined by executing branch 1. Furthermore, it can be determined whether the initial SHJ connection can be converted to a BHJ connection based on the determined actual capacity. Specifically, if the actual capacity is greater than a preset threshold and less than the preset memory amount of the computing node, the SHJ connection can be converted to a BHJ connection, the modified branch 2 is executed, and the execution result of the BHJ connection is determined based on the execution result of the modified branch 2 and the execution result of the executed branch 1.

[0044] The advantage of this method is that: by executing the branch of distributing small table (table with relatively small data volume) data of other connection operations in the obtained execution plan, the branch of distributing large table (table with relatively large data volume) data is modified and blocked. The actual capacity of small table data loaded in memory can be determined by executing the former, and whether the initial other connection operation can be converted into BHJ connection operation can be determined according to whether the actual capacity is greater than the preset memory amount indicating the available capacity in the computing node. Since whether to convert to BHJ connection is determined according to the actual capacity of memory loading of small table data itself, the problem of execution plan failure caused by inaccurate prediction of memory loading capacity of small table data after conversion to BHJ connection operation is avoided. In addition, since the preset memory capacity is usually much higher than the preset threshold, a large number of execution plans that do not adopt BHJ according to the preset threshold can be converted to BHJ, thereby greatly improving the coverage rate of BHJ in the execution plan and reducing the network resources, computing resources and time resources consumed by the execution plan for connecting tables. In summary, this method can improve the computing performance of the execution plan for connecting tables under the premise of ensuring the security of the execution plan.

[0045] Figure 4 A flowchart of a method for improving the computing performance of a table join execution plan in a computing engine according to an embodiment of the present specification is shown. Figure 4 As shown, the method comprises at least the following steps:

[0046] Step S401, if the first table and the second table to be connected are connected based on a non-broadcast hash connection, determine the capacity of the memory occupied by each computing node for distributing the data shards of the first table; wherein the amount of data in the first table is less than the amount of data in the second table; and the data in the first table is subjected to a sharding operation;

[0047] Step S403: if the occupied memory capacity is not greater than the preset memory capacity of the computing node, convert the non-broadcast hash join method into a broadcast hash join method to execute the connection between the first table and the second table.

[0048] First, in step S401, if the first table and the second table to be connected are connected based on a non-broadcast hash connection, the capacity of the memory occupied by the data shards of the first table distributed to each computing node is determined. The amount of data in the first table is smaller than the amount of data in the second table, and the data in the first table is subjected to a sharding operation. Specifically, an execution plan for connecting the first table and the second table in the target computing engine can be obtained, and the amount of data in the first table is smaller than the amount of data in the second table. The execution plan may include a first branch and a second branch of parallel computing, wherein the first branch is used to read the first data of the first table, slice the first data, and distribute different shards of the first data to multiple computing nodes, and the second branch is used to read the second data of the second table, slice the second data, and distribute different shards of the second data to multiple computing nodes.

[0049] A computing engine is a software or system used for processing and computing large-scale data. It can usually process data in memory, thereby improving the speed and efficiency of data processing. An execution plan is a specific execution step generated in a computing engine for a computing task. In different embodiments, the target computing engine can be a computing engine of different specific types, and the execution plan obtained in this step can also be an execution plan generated for different specific computing tasks, which is not limited in this specification. In different embodiments, the execution plan can be obtained in different ways, for example, it can be generated based on a SQL (Structured Query Language) statement.

[0050] The execution plan obtained in this step can be used to connect the execution plan of the first table and the second table. Among them, the amount of data in the first table is less than the amount of data in the second table. The execution plan can be predetermined based on the predicted capacity of the data of the first table (i.e., the first data) loaded in the memory being greater than a preset threshold. In different embodiments, the specific method of determining the predicted capacity. In one embodiment, the first data can be read from a permanent storage medium. Then the predicted capacity can be determined by the first format in which the first data is saved in the permanent storage medium, the second format in which the first data is loaded in the memory, and the preset capacity comparison coefficient for the first format and the second format. In different specific embodiments, the permanent storage medium can be different, for example, it can be a mechanical hard disk (HDD, Hard Disk Drive), a solid state drive (SSD, Solid State Drive), a flash memory (Flash Memory), etc. In different embodiments, the value of the preset threshold value can also be different. For example, in one embodiment, the preset threshold value can be set to 10M.

[0051] The execution plan may include a first branch and a second branch of parallel computing, wherein the first branch is used to read data of a first table (or first data for short), slice the first data, and distribute different slices of the first data to multiple computing nodes. The second branch is used to read data of a second table (or second data for short), slice the second data, and distribute different slices of the second data to multiple computing nodes. In this step, the first branch may read the data of the first table in the form of a stream, for example, by reading instructions. The data of the first table read in a stream may be sliced ​​by a shuffle instruction and different slices of the data of the first table may be distributed to multiple computing nodes. Similarly, the first branch may also read the data of the first table in the form of a stream, for example, by reading instructions. The data of the second table read in a stream may be sliced ​​by a shuffle instruction and different slices of the data of the second table may be distributed to multiple computing nodes.

[0052] Data slicing (also known as data sharding) is to divide a large data set into multiple smaller data subsets (i.e. shards), and then store and manage these shards on different storage or computing nodes. Its significance lies in dispersing data to multiple physical storage locations to achieve distributed storage of data for large-scale data storage and processing.

[0053] After reading the first data in the first branch or the second data in the second branch, a data filtering operation and / or a data expansion operation may be applied to the read first data or the second data. Therefore, in one embodiment, the first data of the first table is read and a preset data filtering operation and / or a data expansion operation is applied to the read first data. Furthermore, the predicted capacity of the first data loaded in the memory may be a predicted value of the capacity of the first data in the memory after the data filtering operation and / or the data expansion operation is applied. In different specific embodiments, the specific type of the data filtering operation may be different. In a specific embodiment, for example, a row filtering (Filter) operation and / or a column filtering operation (Project operation, or projection operation) may be included. In different specific embodiments, the specific type of the data filtering operation may also be different. In a specific embodiment, for example, an operation of expanding the first data through data from other tables may be included. Figure 5 FIG. 2 is a schematic diagram showing an execution plan according to another embodiment of the present specification. Figure 5 As shown, for example, after the instruction to read table A in branch 1, a row filter (Filter) instruction and a project (Project) instruction are also included, wherein the Filter instruction can be used to filter the received data (such as data read by the read instruction) according to the data row, and the Project instruction can be used to filter the received data (such as data read by the read instruction) according to the data column, so the Project instruction is also called a column filter instruction.

[0054] Next, the second branch may be modified to remove a portion for slicing the second data and distributing different shards of the second data across multiple computing nodes. In one embodiment, the second branch may include a second instruction for slicing the second data and distributing different shards of the second data across multiple computing nodes. Furthermore, the second branch may be modified by removing the second instruction from the second branch. For example Figure 3 In the example shown, the dispatch instruction may be removed from the second branch.

[0055] Furthermore, the execution of the modified second branch can be blocked, and the actual capacity of the first data loaded in the memory can be determined by executing the first branch. In different embodiments, the specific method of blocking the execution of the modified second branch can be different. In one embodiment, the execution plan includes a first instruction indicating the execution of the second branch. Furthermore, the execution of the second branch can be blocked in the following manner: remove the first instruction from the execution plan, and insert a preset instruction indicating the blocking of the second branch at the original position of the first instruction. For example Figure 3 In the example shown, the instruction instructing to execute branch 2 can be removed from the execution plan, and a preset instruction (blocking instruction) instructing to block the second branch can be inserted at the original position of the instruction.

[0056] Thereafter, in step S403, if the occupied memory capacity is not greater than the preset memory capacity of the computing node, the non-broadcast hash connection method can be converted to a broadcast hash connection method to execute the connection between the first table and the second table. In one embodiment, if the actual capacity is greater than a preset threshold and less than the preset memory capacity of the computing node, the modified second branch can be unblocked, and the connection result of the first table and the second table is determined based on the execution result of the modified second branch.

[0057] In this step, the actual capacity of the first data loaded into the memory can be determined by executing the first branch. In the above embodiment of applying a data filtering operation and / or a data expansion operation to the first data, the actual capacity of the first data after being loaded into the memory can be the actual value of the capacity of the first data in the memory after the data filtering operation and / or the data expansion operation is applied.

[0058] After determining the actual capacity, it can be determined whether to unblock the modified second branch and determine the connection result of the first table and the second table based on the execution result of the modified second branch, based on whether the actual capacity is greater than the preset threshold and less than the preset memory capacity of the computing node. Specifically, if the actual capacity is greater than the preset threshold and less than the preset memory capacity of the computing node, the blockage of the modified second branch is unblocked, and the connection result of the first table and the second table is determined based on the execution result of the modified second branch. The preset memory capacity of the computing node can be a preset value indicating the available capacity in the computing node, and the value of the preset memory capacity is greater than the preset threshold. In different embodiments, the value of the preset memory capacity may be different. In one example, if the available capacity in multiple computing nodes is different, the preset memory capacity may not be greater than the smallest available capacity therein and be greater than the preset threshold.

[0059] In different embodiments, the execution plan obtained in step S401 (also referred to as the initial plan for ease of description) may connect tables through other connection operations other than BHJ. In one embodiment, if the execution plan obtained in step S401 is an execution plan for connecting the first table and the second table through a sort merge join operation or a distributed hash join operation, the execution result of the sort merge join operation or the distributed hash join operation is determined according to the execution result of the first branch and the execution result of the second branch. Then, the sort merge join operation or the distributed hash join operation may be changed to a broadcast hash join operation to unblock the modified second branch, and the execution result of the broadcast hash join operation is determined based on the execution result of the executed first branch and the execution result of the modified second branch, and the execution result of the broadcast hash join operation is used to determine the connection result of the first table and the second table. Figure 6A schematic diagram showing conversion to a broadcast hash connection according to an embodiment of this specification is shown. Figure 6 As shown, the SHJ instruction indicating the SHJ connection in the initial plan can be changed to a BHJ instruction indicating the BHJ connection, the blocking instruction of branch 2 after the blocking modification (removing the distribution instruction) is removed, and the remaining instructions in the modified branch 2 are executed. Based on the execution result of the executed branch 1 and the execution result of the modified branch 2, the execution result of the SHJ instruction is determined, that is, the connection result of Table A and Table B is obtained.

[0060] In a specific embodiment, the broadcast hash join operation further includes a third branch, the third branch includes the first branch, and is further used to determine the complete first data according to different shards of the first data on multiple computing nodes, and send the complete first data to the multiple computing nodes. Furthermore, the execution result of the third branch can be determined based on the execution result of the executed first branch, and the execution result of the broadcast hash join operation can be determined based on the execution result of the third branch and the modified execution result of the second branch. Figure 7 A schematic diagram showing a modified execution plan according to another embodiment of the present specification is shown. Figure 7 As shown, the BHJ instruction indicating the BHJ connection also includes branch 3, which may include branch 1, a summary instruction, and a whole table distribution instruction, wherein the summary instruction may determine the data of the complete first table according to different slices of the data of the first table (i.e., the first data) on multiple computing nodes after the first branch is executed. The whole table distribution instruction may send the complete data of the first table to multiple computing nodes.

[0061] In some scenarios, it is determined that the actual capacity is greater than the preset memory capacity, in order to roll back the modification to the second branch, the table connection is completed by executing the rollback modified second branch. Therefore, in one embodiment, if the actual capacity determined in step 403 is not less than the preset memory capacity, the modification to the second branch can also be rolled back, and the second branch after the rollback modification is unblocked, and the connection result of the first table and the second table is determined based on the execution result of the rollback modified second branch.

[0062] In a specific embodiment, if the initial plan is an execution plan for connecting the first table and the second table through a sort merge join operation or a distributed hash join operation, the execution result of the sort merge join operation or the first distributed hash join operation is determined according to the execution result of the first branch and the execution result of the second branch. Then, the sort merge join operation or the first distributed hash join operation can be changed to a second distributed hash join operation, and the modification to the second branch is rolled back, and the modified second branch is unblocked. Based on the execution result of the executed first branch and the execution result of the modified second branch after the rollback, the execution result of the second distributed hash join operation is determined, and the execution result of the fourth join operation is used to determine the connection result of the first table and the second table. In an example, for example, for Figure 3 In the plan shown in FIG. 1 , after determining the actual capacity of table A loaded in the memory by executing branch 1 and determining not to convert to BHJ connection according to the actual capacity, branch 2 can be restored as follows: Figure 2 The initial form shown in the figure and execute branch 2.

[0063] In some scenarios, the computing performance of the SHJ join operation is higher than that of the SMJ operation, and both the SMJ join operation and the SHJ join operation can determine the join result based on the data distribution of the large table data and the small table data. Therefore, in another example, if the join operation in the initial plan is an SMJ join operation, after it is determined that it cannot be converted to a BHJ join operation, the SMJ join operation can be changed to an SHJ join operation, and after rolling back the modification and executing the second branch that distributes the large table data, the execution result of the SHJ join operation is determined based on the execution result of the executed first branch and the execution result of the second branch after the rollback modification.

[0064] The above describes a method for improving the computing performance of a table join execution plan in a computing engine provided by an embodiment of the present specification. Another embodiment of the present specification also provides a device for improving the computing performance of a table join execution plan in a computing engine. Fig. 9 FIG. 2 shows a structural diagram of a device for improving the computing performance of a table join execution plan in a computing engine according to an embodiment of the present specification. Fig. 9 As shown, the apparatus 900 may include:

[0065] The determining unit 91 is configured to determine the capacity of the memory occupied by each computing node for distributing the data shards of the first table to each computing node if the first table and the second table to be connected are connected based on a non-broadcast hash connection; wherein the amount of data in the first table is less than the amount of data in the second table; and the data in the first table is subjected to a sharding operation;

[0066] The conversion unit 92 is configured to convert the non-broadcast hash connection method into a broadcast hash connection method to execute the connection between the first table and the second table if the occupied memory capacity is not greater than the preset memory capacity of the computing node.

[0067] On another aspect, the present specification provides a computer-readable storage medium having a computer program stored thereon, which, when executed in a computer, causes the computer to execute any one of the above methods.

[0068] On another aspect, the present specification provides a computing device, including a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, any one of the above methods is implemented.

[0069] It should be understood that the descriptions such as “first” and “second” in this article are only used to distinguish similar concepts for the sake of simplicity of description and do not have any other limiting effect.

[0070] Those skilled in the art should be aware that in one or more of the above examples, the functions described in the present invention can be implemented by hardware, software, firmware or any combination thereof. When implemented by software, these functions can be stored in a computer-readable medium or transmitted as one or more instructions or codes on a computer-readable medium.

[0071] The specific implementation methods described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation method of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made on the basis of the technical solution of the present invention should be included in the scope of protection of the present invention.

Claims

1. A table connection method, comprising: If the first table and the second table to be connected are connected based on a non-broadcast hash connection, the data shards of the first table are distributed to the memory capacity occupied by each computing node, wherein the data volume of the first table is smaller than the data volume of the second table; and the data of the first table is subjected to sharding operation; If the occupied memory capacity is not greater than the preset memory capacity of the computing node, the non-broadcast hash join method is converted into a broadcast hash join method to execute the connection between the first table and the second table.

2. The method according to claim 1, wherein: The first table and the second table are connected based on a non-broadcast hash connection, including: By running an execution plan in a target computing engine, the first table and the second table are connected in a manner based on a non-broadcast hash connection, wherein the execution plan includes a first branch and a second branch of parallel computing, the first branch is used to read data of the first table and perform a sharding operation on the data of the first table, and the second branch is used to read data of the second table and perform a sharding operation on the data of the second table; Determining the capacity of the memory occupied by the data slices of the first table distributed to each computing node includes: blocking the second branch, executing the first branch, and determining the capacity of the memory occupied by the data slices of the first table distributed to each computing node.

3. The method according to claim 2, wherein: If the occupied memory capacity is not greater than the preset memory capacity of the computing node, converting the non-broadcast hash join method into a broadcast hash join method to execute the connection between the first table and the second table, including: If the occupied memory capacity is not greater than the preset memory capacity of the computing node, the second branch is modified to remove the portion that performs the sharding operation on the data of the second table, and the connection method of the first table and the second table is converted into a broadcast hash connection method; the modified second branch is unblocked, and the connection result of the first table and the second table is determined based on the execution result of the modified second branch.

4. The method according to claim 2, further comprising: If the occupied memory capacity is greater than the preset memory capacity of the computing node, the second branch is unblocked, and the connection result of the first table and the second table is determined based on the execution result of the unblocked second branch.

5. The method according to claim 2, wherein: Including in the execution plan a first instruction instructing to execute a second branch; Blocking the execution of the second branch includes: The first instruction is removed from the execution plan, and a preset instruction indicating blocking of the second branch is inserted at the original position of the first instruction.

6. The method according to claim 3, wherein: The second branch includes a second instruction for performing a slicing operation on data of the second table; Modifying the second branch to remove a portion that performs a slicing operation on the data in the second table includes: removing the second instruction from the second branch.

7. The method according to claim 3, wherein: Unblocking the modified second branch and determining the connection result of the first table and the second table based on the execution result of the modified second branch, including: unblocking the modified second branch and determining the connection result of the first table and the second table based on the execution result of the executed first branch and the execution result of the modified second branch.

8. The method according to claim 7, wherein: Converting the connection mode of the first table and the second table into a broadcast hash connection mode includes: adding a third branch to the execution plan, the third branch includes the first branch, and the third branch is further used to determine the complete data of the first table according to the data shards of the first table on each computing node, and sending the complete data of the first table to the multiple computing nodes: Determining the execution result of the broadcast hash join operation based on the execution result of the executed first branch and the modified execution result of the second branch includes: Based on the execution result of the executed first branch, the execution result of the third branch is determined, and based on the execution result of the third branch and the modified execution result of the second branch, the connection result of the first table and the second table is determined.

9. The method according to claim 1, wherein: Before the sharding operation is performed on the data in the first table, a data filtering operation and / or a data expansion operation is performed on the data in the first table.

10. The method according to claim 1, wherein: The data of the first table includes the data of the first table read from a permanent storage medium.

11. A device for meter connection, the device comprising: The determining unit is configured to, if the first table and the second table to be connected are connected based on a non-broadcast hash connection, determine the capacity of the memory occupied by the data shards of the first table distributed to each computing node; wherein the amount of data in the first table is less than the amount of data in the second table; and the data of the first table is subjected to a sharding operation; The conversion unit is configured to convert the non-broadcast hash connection method into a broadcast hash connection method to execute the connection between the first table and the second table if the occupied memory capacity is not greater than the preset memory capacity of the computing node.

12. A computer-readable storage medium having a computer program stored thereon, which, when executed in a computer, causes the computer to execute the method according to any one of claims 1 to 10.

13. A computing device, comprising a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, the method according to any one of claims 1 to 10 is implemented.

Images