An optimization method for SortMergeJoin join computation based on Spark SQL

Abstract

This invention discloses a method for optimizing SortMergeJoin join computation based on Spark SQL, relating to the fields of big data and databases. This invention re-implements a custom execution plan orchestration by matching the native Spark SQL logical execution plan. By reducing the shuffle operations of the job itself, it improves the execution efficiency of the job and also enhances the overall IO efficiency of the Spark SQL cluster. Furthermore, by rearranging the Spark SQL physical execution plan, this invention avoids unnecessary data shuffle operations, accelerating the execution efficiency of SQL jobs. Additionally, by reducing the number of data shuffles, unnecessary IO resources are released, thus also improving the overall IO efficiency of the Spark SQL cluster.

Description

Technical Field

[0001] This invention belongs to the field of big data and database technology, and in particular relates to an optimization method for SortMergeJoin join computation based on Spark SQL. Background Technology

[0002] Spark SQL provides a high-performance computing framework that offers general-purpose join computation capabilities for massive datasets. SortMergeJoinExec is a commonly used Spark join computation execution plan. The SortMergeJoinExec execution plan is a binary execution plan, consisting of a left table execution plan and a right table execution plan. The left and right table execution plans describe the computational logic on both sides of the join computation.

[0003] In a common business scenario, the left table is used for a grouping and aggregation calculation task, and the columns involved in the join calculation are also part of the grouping conditions (i.e., the Group By condition in the SQL statement) for the left table's grouping and aggregation calculation. To complete the final calculation logic, the Spark SQL framework performs two data shuffle operations when the left table has grouping and aggregation calculations, and one shuffle operation when the right table is being calculated. The data from the left and right tables, after being shuffled, are used to complete the final join calculation according to the same partition number.

[0004] In scenarios involving large tables (where both the left and right tables in a JOIN are large), if the left table's calculation logic involves grouping and aggregation, and the Join column is also part of the left table's grouping column, then two data shuffles of the left table will increase Shuffle I / O, impacting overall computational efficiency. Summary of the Invention

[0005] The technical problem to be solved by this invention is to provide an optimization method based on the Spark SQL engine to address the shortcomings of the background technology. In large table join calculation scenarios, if the grouping column of the left table contains a JOIN column, the overall calculation efficiency can be improved by reducing the number of shuffles in the left table, avoiding shuffle IO overhead.

[0006] To solve the above-mentioned technical problems, the present invention adopts the following technical solution:

[0007] A Spark SQL-based SortMergeJoin join computation optimization method re-implements custom execution plan orchestration by matching the native Spark SQL logical execution plan;

[0008] SortMergeJoin is a join strategy in Spark SQL for performing join calculations. Its corresponding Spark SQL physical execution plan is SortMergeJoinExec. The SortMergeJoinExec execution plan is a binary execution plan, which includes the execution plan for the left table and the execution plan for the right table. Spark SQL is a SQL module of the Apache Spark software used to process structured data. Join is a commonly used SQL join operation.

[0009] Specifically, it includes the following steps:

[0010] Step 1: Perform SQL rule matching. If the current execution plan is of type SortMergeJoin and the JOIN type is type LeftJoin, then continue to determine whether there is aggregation grouping calculation in the execution plan of its left table.

[0011] SortMergeJoin is a sort-based association operation.

[0012] Step 2: Determine whether the grouping and aggregation column information of the execution plan in the left table contains the JOIN column set information. If the condition is met, execute the subsequent optimization logic; otherwise, end the optimization process.

[0013] Step 3: Extract the execution plan of the left table in the JOIN. At this time, the execution plan of the left table is an execution plan of the Hash grouping aggregation calculation type. The Hash grouping aggregation execution plan is executed in two stages. The first stage is the Partial stage, which performs aggregation calculation and data shuffle. The second stage is the Final stage, which reads the Shuffled data from the previous stage to complete the final aggregation calculation.

[0014] Hash grouping refers to calculating a new value for the current data by using a hash algorithm based on a specified column in the table data.

[0015] The partial stage is the stage for local data computation.

[0016] Shuffle operation refers to the process of redistributing and reorganizing data in distributed computing;

[0017] The Final stage is the stage for global data computation.

[0018] Step 4: Extract the data sorting operation from the JOIN left table execution plan, use the Shuffle execution plan generated in Step 3 as a sub-execution plan of the Sort execution plan, and modify the sorting rules of the Sort execution plan to sort and construct the plan based on the grouping column order condition of the Fina Hash aggregation.

[0019] Step 5: Extract the final stage hash aggregation execution plan of the left table of JOIN, take the Sort execution plan generated in step 4 as its child execution plan, and use the current execution plan as the complete calculation logic of the left side of JOIN.

[0020] As a further preferred embodiment of the SortMergeJoin join computation optimization method based on Spark SQL of the present invention, the SortMergeJoin execution plan execution flow is as follows:

[0021] Step A: Read the datasets from the left and right tables respectively and perform calculations;

[0022] Step B: The number of partitions in the left table is kept consistent with the number of partitions in the right table. The conditions in the sorting stage of the left table are modified to the column conditions in the final grouping and aggregation stage, and the calculation in the final aggregation stage is delayed until after the Sort stage.

[0023] Step C: In the Final stage, the final calculation logic of the left table is completed, and it is used as a flow table to perform connection calculations with the table on the right.

[0024] As a further preferred embodiment of the Spark SQL-based SortMergeJoin join calculation optimization method of the present invention, the calculation conditions for the left table data are optimized as follows:

[0025] The Shuffle condition uses the Join column to construct the join column data and calculates the value of the Hash function;

[0026] The conditions for grouping and aggregation calculations in the Partial phase remain unchanged.

[0027] As a further preferred embodiment of the Spark SQL-based SortMergeJoin join computation optimization method of the present invention, in step 3, optimizing the left-side computation logic to reduce the number of shuffles requires modifying the shuffle conditions in the Partial grouping aggregation stage, using JOIN column conditions for data shuffle partitioning.

[0028] As a further preferred embodiment of the SortMergeJoin join computation optimization method based on Spark SQL of the present invention, in step 5, the Final stage Hash is the use of the Hash operator to calculate the data in the global computation stage of aggregation computation.

[0029] Compared with the prior art, the present invention, employing the above technical solution, has the following technical effects:

[0030] 1. This invention provides an optimization method for SortMergeJoin connection computation based on Spark SQL. By reducing the Shuffle operation of the job itself, it improves the execution efficiency of the job and also improves the overall IO efficiency of the Spark SQL cluster.

[0031] 2. This invention avoids unnecessary data shuffle operations by rearranging the physical execution plan of Spark SQL, thereby speeding up the execution efficiency of SQL jobs. In addition, by reducing the number of data shuffles, unnecessary IO resources are released, thus improving the overall IO efficiency of the Spark SQL cluster. Attached Figure Description

[0032] Figure 1 This is the execution planning flowchart of the SortMergeJoin of this invention;

[0033] Figure 2 This is a schematic diagram of the SortMergeJoin execution plan of the present invention;

[0034] Figure 3 This is a schematic diagram of the native Spark SortMergeJoin execution plan of this invention;

[0035] Figure 4 This is a schematic diagram of the optimized Spark SortMergeJoin execution plan of this invention. Detailed Implementation

[0036] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings:

[0037] The invention will be further described in detail with reference to the accompanying drawings, and at least one preferred embodiment will be specifically described. The description should be so specific that it enables a person skilled in the art to reproduce the invention without having to expend creative effort, such as exploration, research and experimentation.

[0038] A Spark SQL-based SortMergeJoin join computation optimization method re-implements custom execution plan orchestration by matching the native Spark SQL logical execution plan;

[0039] SortMergeJoin is a join strategy in Spark SQL for performing join calculations. Its corresponding Spark SQL physical execution plan is SortMergeJoinExec. The SortMergeJoinExec execution plan is a binary execution plan, which includes the execution plan for the left table and the execution plan for the right table. Spark SQL is a SQL module of the Apache Spark software used to process structured data. Join is a commonly used SQL join operation. This type of operation mainly matches and merges data from two or more tables according to a common column.

[0040] Management node (Spark Driver)

[0041] Spark Driver refers to the management node of Spark SQL programs.

[0042] SortMergeJoin is a common join strategy in Spark SQL for performing join calculations. Its corresponding Spark SQL physical execution plan is SortMergeJoinExec. The SortMergeJoinExec execution plan is a binary execution plan, which includes the execution plan for the left table and the execution plan for the right table.

[0043] The process is as follows Figure 1 As shown:

[0044] 1. Perform SQL rule matching. If the current execution plan is of type SortMergeJoin and the JOIN type is LeftJoin, then continue to check if there is aggregation grouping calculation in its left table. If none of the above conditions are met, the optimization process ends. SortMergeJoin is a sort-based join operation;

[0045] 2. If the above conditions are met, determine whether the grouping and aggregation column information of the left table contains the JOIN column set information. If the conditions are met, execute the subsequent optimization logic; otherwise, end the optimization process.

[0046] 3. Extract the execution plan for the left table in the JOIN operation. At this point, the execution plan for the left table is a Hash-based grouping aggregation calculation. The Hash-based grouping aggregation execution plan is executed in two phases: the first phase is the Partial phase, which performs aggregation calculations and data shuffling; the second phase is the Final phase, which reads the shuffled data from the previous phase to complete the final aggregation calculation. To optimize the left-side calculation logic and reduce the number of shuffles, the shuffle conditions in the Partial grouping aggregation phase need to be modified to use the JOIN column conditions for data shuffling partitioning.

[0047] Hash grouping refers to calculating a new value for the current data by using a hash algorithm based on a specified column in the table data.

[0048] The partial stage is the stage for local data computation.

[0049] Shuffle operation refers to the process of redistributing and reorganizing data in distributed computing;

[0050] The Final stage is the stage for global data computation.

[0051] 4. Extract the Sort execution plan from the left table of the JOIN operation. Use the Shuffle execution plan generated in step 3 as a sub-execution plan of the Sort execution plan. Simultaneously, modify the sorting rules of the Sort execution plan to use the grouping column order condition of the Final Hash aggregation for sorting and construction. This should be Final Hash, where the aggregation calculation uses a hash operator to calculate the data during the global computation phase.

[0052] 5. Extract the final stage hash aggregation execution plan of the left table of JOIN, take the Sort execution plan generated in the above 4 steps as its child execution plan, and use the current execution plan as the complete calculation logic of the left side of JOIN.

[0053] The optimized SortMergeJoin execution plan execution flow is as follows: Figure 2 As shown:

[0054] The datasets from the left and right tables are read separately for calculation. The calculation conditions for the left table data are optimized as follows:

[0055] (1) Shuffle conditions are constructed using the Join column (Hash(JoinKeys)). The Shuffle conditions are constructed using the Join column to calculate the value of the Hash function on the join column data.

[0056] (2) The grouping and aggregation calculation conditions in the Partial stage remain unchanged.

[0057] The number of partitions in the left table is kept consistent with the number of partitions in the right table. The conditions in the sorting phase of the left table are modified to the column conditions in the final grouping and aggregation phase, and the calculation in the final aggregation phase is deferred to after the Sort phase.

[0058] The final stage completes the final calculation logic of the left table and uses it as a flow table to perform connection calculations with the right table.

[0059] like Figure 3 and Figure 4 As shown in the comparison before and after optimization, it can be seen that the Shuffle stage of the left table in SortMergeJoin is reduced from three times to two times. From the experimental data of large table association calculation, it can be seen that reducing the total number of Shuffles can significantly reduce the time overhead of join calculation.

[0060] In the SortMergeJoin scenario, if the left table contains grouping and aggregation calculation logic, and the grouping columns include aggregation column information, the number of shuffle operations on the left table can be reduced, thus improving the overall execution efficiency of the job.

[0061] In the SortMergeJoin scenario, if the left table contains grouping and aggregation calculation logic, and the grouping columns include aggregation column information, native Apache Spark does not optimize shuffle for this specific scenario. Therefore, if the left table is a large table, it requires at least two shuffle operations. Shuffling large amounts of data reduces the execution efficiency of the job itself, and the large number of shuffle operations leads to significant disk I / O consumption, causing other jobs requiring disk read / write operations to queue. Therefore, this invention improves the execution efficiency of the job itself and the overall I / O efficiency of the Spark SQL cluster by reducing the shuffle operations within the job.

[0062] In specific scenarios involving joins between two tables, reducing the number of shuffles improves job efficiency. By rearranging the Spark SQL physical execution plan, unnecessary data shuffle operations are avoided, accelerating the execution of SQL jobs. Furthermore, by reducing the number of data shuffles, unnecessary I / O resources are released, thus improving the overall I / O efficiency of the Spark SQL cluster.

[0063] It will be understood by those skilled in the art that, unless otherwise defined, all terms used herein (including technical and scientific terms) have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. It should also be understood that terms such as those defined in general dictionaries should be understood to have the same meaning as in the context of the prior art, and should not be interpreted in an idealized or overly formal sense unless defined as herein.

[0064] The above embodiments are merely illustrative of the technical concept of the present invention and should not be construed as limiting the scope of protection of the present invention. Any modifications made to the technical solution based on the technical concept proposed in this invention shall fall within the scope of protection of this invention. The embodiments of the present invention have been described in detail above, but the present invention is not limited to the above embodiments. Within the scope of knowledge possessed by those skilled in the art, various changes can be made without departing from the spirit of the present invention.

Claims

1. An optimization method for SortMergeJoin join computation based on Spark SQL, characterized in that: By matching the native Spark SQL logic execution plan, a custom execution plan orchestration can be reimplemented; SortMergeJoin is a join strategy in Spark SQL for performing join calculations. Its corresponding Spark SQL physical execution plan is SortMergeJoinExec. The SortMergeJoinExec execution plan is a binary execution plan, which includes the execution plan for the left table and the execution plan for the right table. Spark SQL is a SQL module of the Apache Spark software used to process structured data. Join is a commonly used SQL join operation. Specifically, it includes the following steps: Step 1: Perform SQL rule matching. If the current execution plan is of type SortMergeJoin and the JOIN type is type LeftJoin, then continue to determine whether there is aggregation grouping calculation in the execution plan of its left table. SortMergeJoin is a sort-based association operation. Step 2: Determine whether the grouping and aggregation column information of the execution plan in the left table contains the JOIN column set information. If the condition is met, execute the subsequent optimization logic; otherwise, end the optimization process. Step 3: Extract the execution plan of the left table in the JOIN. At this time, the execution plan of the left table is an execution plan of the Hash grouping aggregation calculation type. The Hash grouping aggregation execution plan is executed in two stages. The first stage is the Partial stage, which performs aggregation calculation and data shuffle. The second stage is the Final stage, which reads the Shuffled data from the previous stage to complete the final aggregation calculation. Hash grouping refers to calculating a new value for the current data by using a hash algorithm based on a specified column of the table data. The partial stage is the stage for local data computation. Shuffle operation refers to the process of redistributing and reorganizing data in distributed computing; The Final stage is the stage for global data computation. Step 4: Extract the data sorting operation from the JOIN left table execution plan, use the Shuffle execution plan generated in Step 3 as a sub-execution plan of the Sort execution plan, and modify the sorting rules of the Sort execution plan to sort and construct the plan based on the grouping column order condition of the Fina Hash aggregation. Step 5: Extract the final stage hash aggregation execution plan of the left table of JOIN, take the Sort execution plan generated in step 4 as its child execution plan, and use the current execution plan as the complete calculation logic of the left side of JOIN.

2. The SortMergeJoin join computation optimization method based on Spark SQL according to claim 1, characterized in that: The execution flow of the SortMergeJoin execution plan is as follows: Step A: Read the datasets from the left and right tables respectively and perform calculations; Step B: The number of partitions in the left table is kept consistent with the number of partitions in the right table. The conditions in the sorting stage of the left table are modified to the column conditions in the final grouping and aggregation stage, and the calculation in the final aggregation stage is delayed until after the Sort stage. Step C: In the Final stage, the final calculation logic of the left table is completed, and it is used as a flow table to perform connection calculations with the table on the right.

3. The SortMergeJoin join computation optimization method based on Spark SQL according to claim 2, characterized in that: The calculation conditions for the data in the left table are optimized as follows: The Shuffle condition uses the Join column to construct the join column data and calculates the value of the Hash function; The conditions for grouping and aggregation calculations in the Partial phase remain unchanged.

4. The SortMergeJoin join computation optimization method based on Spark SQL according to claim 1, characterized in that: In step 3, optimizing the left-hand calculation logic to reduce the number of shuffles requires modifying the shuffle conditions in the Partial grouping and aggregation stage, using the JOIN column conditions for data shuffle partitioning.

5. The SortMergeJoin join computation optimization method based on Spark SQL according to claim 1, characterized in that: In step 5, the Final stage Hash is the aggregation calculation global calculation stage that uses the Hash operator to calculate the data.

Images