Cartesian product Join optimization method based on Spark SQL

Abstract

The invention discloses a Cartesian product Join optimization method based on a Spark SQL (Structured Query Language), which belongs to the field of big data and databases, and comprises the following steps of: performing execution plan rewriting on a Join SQL task of a non-equivalence association condition, namely, a multi-equivalence condition OR, folding the non-equivalence association condition, namely, the multi-equivalence condition OR into an equivalence virtual association column equivalence association condition; according to the method, the association strategy of the SQL is optimized from the Cartesian ProductJoin with the worst performance to the SortMergeJoin, the shuffle data volume and resource consumption of SQL execution calculation are reduced, and the SQL execution time consumption is reduced, so that the calculation performance of the non-equivalent Join SQL is improved, and meanwhile, the overall calculation efficiency of the Spark SQL cluster is also improved.

Description

Technical Field

[0001] The present invention belongs to the field of big data and database, and in particular relates to a Cartesian product Join optimization method based on Spark SQL. Background Art

[0002] In Spark SQL, there are five Join strategies, from high to low performance: BroadcastHashJoin, BroadcastNestedLoopJoin, ShuffleHashJoin, SortMergeJoin, and CartesianProductJoin. In the equal-join scenario, the join keys of two tables are connected by equal-value conditions. Spark SQL attempts to select join strategies in the order of BroadcastHashJoin > SortMergeJoin > ShuffleHashJoin. In the non-equal-join scenario, the join conditions are connected by inequalities. Non-equal-join can only be calculated using the NestedLoopJoin method. Therefore, the only optional strategies are BroadcastNestedLoopJoin and CartesianProductJoin. The join strategy is selected in the order of CartesianProductJoin > BroadcastNestedLoopJoin. CartesianProductJoin only supports inner join types. For calculation types other than InnerJoin, only BroadcastNestedLoopJoin can be selected for calculation.

[0003] Based on the rules for selecting the Join strategy in Spark SQL, for join calculations using OR to connect multiple columns, such as join conditions like tab1.col1 = tab2.col or tab1.col2 = tab2.col, BroadcastHashJoin cannot be used if the table data volume is large. Furthermore, because the join condition is of the OR type, it is a non-equality join and hash shuffle cannot be performed on the data. Therefore, the efficient ShuffleHashJoin and SortMergeJoin operators cannot be used for join calculations. Ultimately, the join calculation will degenerate into using the CartesianProductJoin operator.

[0004] For joins involving multiple columns, such as tab1.col1 = tab2.col or tab1.col2 = tab2.col, open-source Spark SQL uses the CartesianProductJoin strategy when the table data volume is large. CartesianProductJoin has the worst execution efficiency of the five join strategies, requiring a large amount of data shuffle distribution. Assuming the two tables involved in the join have M and N partitions, respectively, the join calculation requires M*N tasks. This results in significant network and computing resource overhead, leading to very low SQL execution performance, especially in scenarios with massive data volumes. Summary of the Invention

[0005] The technical problem to be solved by the present invention is to provide a Cartesian product Join optimization method based on Spark SQL to address the shortcomings of the background technology. By folding non-equivalent association conditions such as multiple equivalent conditions OR into equivalent association conditions of virtual associated columns, the SQL association strategy is optimized from the worst-performing CartesianProductJoin to SortMergeJoin, reducing the resource consumption of SQL execution calculations and the SQL execution time, thereby improving the calculation performance of this non-equivalent Join SQL.

[0006] The present invention adopts the following technical solutions to solve the above technical problems:

[0007] A Cartesian product Join optimization method based on Spark SQL specifically includes the following steps:

[0008] Step 1: Expand the optimization rules in the logical plan stage to identify logical plans that can be optimized for collapsed joins. Add an expanded FlatMapTransForm node above the Relation node or Filter node of the left table. FlatMapTransForm represents a logical plan that adds virtual join columns and expands the data. It also converts the join condition so that the virtual join columns of tab1 are equal to the actual join columns of the right table.

[0009] Step 2: Expand the optimization strategy for converting the logical plan to the physical plan. Identify the FlatMapTransForm node in step 1 and convert the logical plan of the FlatMapTransForm type into the FlatMapTransFormExec operator. The FlatMapTransFormExec operator represents the physical operator that adds virtual associated columns and expands the data.

[0010] Step 3: Implement the FlatMapTransFormExec operator, which is responsible for expanding each row in the input RDD[InternalRow]. When the values ​​of the left table's associated fields are equal, the original data is retained and a virtual associated column VIRTUAL_JOIN_COL is added. The value of VIRTUAL_JOIN_COL is the value of the left table's associated fields. When the values ​​of the left table's associated fields are not equal, the original row data is converted into two rows, and virtual associated columns VIRTUAL_JOIN_COL are added to each row. The values ​​of VIRTUAL_JOIN_COL are equal to the values ​​of the two associated fields respectively.

[0011] Step 4: Perform association calculations using the native Spark SQL SortMergeJoin strategy.

[0012] As a further preferred solution of the Cartesian product Join optimization method based on Spark SQL of the present invention, in step 1, before the folding optimization, the join condition of the two tables is a non-equivalence condition, and the join calculation is performed using the CartesianProductJoin strategy.

[0013] As a further preferred solution of the Cartesian product Join optimization method based on Spark SQL of the present invention, in step 3, after folding optimization, a virtual association column VIRTUAL_JOIN_COL is added to the left table, and the left table data is expanded according to whether the values ​​of the two association fields of the left table are equal, and then associated with the right table through the equal association condition tab1.VIRTUAL_JOIN_COL=tab2.MOBILE, thereby using the SortMergeJoin method to perform association calculation.

[0014] As a further preferred solution of the Cartesian product Join optimization method based on Spark SQL of the present invention, SortMergeJoin is used to perform association calculation, and the association conditions are folded in a conversion method, so that the calculation results before and after the conversion are equivalent.

[0015] As a further preferred solution of the Cartesian product Join optimization method based on Spark SQL of the present invention, before the folding Join optimization, each data partition of the left table is shuffled to each partition of the right table for association calculation. After the folding optimization, the association condition becomes an equal-value association condition. The data is repartitioned and then shuffled using a hash method, and then the association calculation is performed using the SortMergeJoin strategy, thereby reducing the amount of data involved in the shuffle and the number of tasks in the association calculation stage.

[0016] Compared with the prior art, the present invention adopts the above technical solution and has the following technical effects:

[0017] 1. This paper proposes a non-equivalence join optimization strategy. For join calculation SQL statements that use OR to connect multiple columns, such as join conditions like tab1.col1=tab2.col or tab1.col2=tab2.col, this strategy folds these non-equivalence join conditions into equivalent virtual join column equality conditions. This optimizes the SQL join strategy from the worst-performing CartesianProductJoin to SortMergeJoin, reducing the amount of shuffle data and resource consumption during SQL execution and shortening SQL execution time, thereby improving the computational performance of these non-equivalence join SQL statements.

[0018] 2. By rewriting the execution plan for the SQL task of non-equal join, adjusting the non-equal join conditions to equivalent equal join conditions, and optimizing the SQL join strategy from the worst-performing CartesianProductJoin to SortMergeJoin, the amount of shuffle data and resource consumption during SQL execution calculations are reduced, and the SQL execution time is shortened, thereby improving the computing performance of this non-equal join SQL and the overall computing efficiency of the Spark SQL cluster. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 This is a schematic diagram of the folded Join optimization of the present invention;

[0020] Figure 2 This is a schematic diagram of the shuffle process before and after the folded Join optimization of the present invention;

[0021] Figure 3 Collapse Join optimization implementation process. DETAILED DESCRIPTION

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

[0023] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention. The present invention is described in detail below based on the drawings and preferred embodiments. The purpose and effect of the present invention will become more clear. It should be understood that the specific embodiments described here are only used to explain the present invention and are not used to limit the present invention.

[0024] The relevant concepts of the present invention are as follows:

[0025] BroadcastHashJoin: Broadcast hash join, which broadcasts the smaller data table to all computing nodes, and then uses the hash table on each node to perform a hash join with the local table partition.

[0026] BroadcastNestedLoopJoin: Broadcast nested loop join, broadcasts the small table to all nodes where the large table's partitions are located, and then performs a nested loop join on the small table and the large table partitions on each node.

[0027] ShuffleHashJoin: Shuffle hash join: Both large tables need to be repartitioned through shuffle (Shuffle) to ensure that data with the same join key is divided into the same node, and then a hash join is performed on each node.

[0028] SortMergeJoin: Sort-merge join, sorts the two tables by the join key, and then compares the join keys row by row in a manner similar to merge sort to complete the join operation.

[0029] CartesianProductJoin: Cartesian product join, which performs a nested loop join on each partition of the left table with each partition of the right table.

[0030] Join Key: In SQL JOIN operations, a column used to associate two tables.

[0031] Shuffle: The process of repartitioning and redistributing data during data processing. This process typically occurs when operations such as cross-node aggregation, sorting, and joining require data to be redistributed to different nodes or tasks based on a key for subsequent processing.

[0032] Shuffle map: During the shuffle process, it is responsible for processing data and partitioning the data by key and sending it to the shuffle service that manages the shuffle data to prepare data for the subsequent reduce phase.

[0033] Shuffle reduce: This stage is responsible for pulling the output data of the shuffle map stage from the shuffle service, merging and sorting, and performing final calculations (such as aggregation or join).

[0034] Range Bounds:

[0035] The optimization solution of the present invention is applicable to the following SQL format:

[0036] left_relation join_type right_relation join_criteria[condition];

[0037] Parameter explanation:

[0038] left_relation: the left table involved in the association calculation;

[0039] right_relation: the right table involved in the relation calculation;

[0040] join_type: Join type. Supported optimized join types include INNER JOIN and RIGHT JOIN.

[0041] join_criteria: join calculation conditions. To support optimized join calculation conditions, the conditions must satisfy the form of tab1.col1 = tab2.col or tab1.col2 = tab2.col. That is, different columns in the left table must be the same columns in the right table.

[0042] condition: predicate condition, optional;

[0043] Example of optimizing SQL:

[0044] SELECT t1.*,t2.*from tab1 t1 join tab2 t2 on tab1.col1=tab2.col ortab1.col2=tab2.col;

[0045] SELECT t1.*,t2.*from tab1 t1 inner join tab2 t2 on tab1.col1=tab2.col or tab1.col2=tab2.col where t1.col3='xxx';

[0046] SELECT t1.*,t2.*from tab1 t1 right join tab2 t2 on tab1.col1=tab2.col or tab1.col2=tab2.col where t1.col3='xxx'limit 10;

[0047] create table test as SELECT t1.*,t2.*from tab1 t1 join tab2 t2 ontab1.col1=tab2.col or tab1.col2=tab2.col;

[0048] Note: For join conditions such as tab1.col = tab2.col1 or tab1.col = tab2.col2, that is, joining the same column in the left table with different columns in the right table, the supported optimized join types include INNER JOIN and LEFT JOIN, which are not detailed here.

[0049] Although the join condition for the optimized SQL statement is an OR condition consisting of multiple equal-value conditions, it has a characteristic: different columns in the left table are joined with the same column in the right table. This SQL statement corresponds to a very common practical application scenario, where the two fields in the left table likely have the same business meaning, such as a person's login account and mobile phone number. Therefore, their values ​​are likely to be equal, although inconsistencies may exist.

[0050] Therefore, for this type of SQL, we can retain one record where the two fields col1 and col2 in the left table are equal, copy another record where they are unequal, and add a virtual join column to each record. When the two field values ​​are equal, the field value is used as the value of the virtual join column. When the values ​​are unequal, the record is split into two records, each with the values ​​of the two join fields as the values ​​of the virtual join column. The join condition is then changed to require that the virtual join column of the left table is equal to the join column of the right table. Since the join condition has been changed from a non-equality condition to an equality condition, the SortMergeJoin method can be used for join calculations. This conversion method, which collapses the join conditions, results in equivalent calculations before and after the conversion.

[0051] The following example uses the join condition tab1.USER_ID = tab2.MOBILE OR tab1.PHONE_NUMBER = tab2.MOBILE to illustrate the equivalence of the join calculation process and results before and after folding optimization. Figure 1 shown.

[0052] Note: The table data shown is artificially constructed simulation data and does not involve privacy-sensitive information.

[0053] like Figure 1As shown in the figure, before the folding optimization, the join conditions between the two tables were non-equivalence conditions, requiring the CartesianProductJoin strategy for join calculation. After the folding optimization, we added a virtual join column, VIRTUAL_JOIN_COL, to the left table. Based on whether the values ​​of the two join fields in the left table are equal, the left table data is expanded and then joined with the right table using the equality join condition, tab1.VIRTUAL_JOIN_COL = tab2.MOBILE. This allows the SortMergeJoin method to perform join calculations. The join calculation results before and after the folding join optimization are equivalent.

[0054] The schematic diagram of the shuffle process before and after the folded Join optimization is as follows Figure 2 As shown, Figure 2 This example demonstrates the changes in the shuffle process before and after Collapse Join optimization. Before Collapse Join optimization, each data partition in the left table must be shuffled to each partition in the right table for join calculations. This shuffle requires a very large amount of data, resulting in high computational resource overhead and poor performance in the case of massive data volumes. After Collapse optimization, join conditions become equality join conditions. Data can be repartitioned using a hashing method before shuffling, and then join calculations can be performed using the SortMergeJoin strategy. This significantly reduces the amount of data involved in the shuffle and the number of tasks required for join calculations, significantly reducing resource consumption and improving Join calculation performance.

[0055] The following uses a specific scenario as an example to illustrate the optimization effect of the folded join optimization in reducing resource consumption and shuffle data volume:

[0056] Example scenario description:

[0057] The data slice size is 512MB (spark.sql.files.maxPartitionBytes=536870912);

[0058] The number of partitions in the shuffle phase is 500 (spark.sql.shuffle.partitions=500);

[0059] The left table size is 100G, and the right table size is 200G;

[0060] In the worst case, the values ​​of different associated columns in each row of data in the left table are different. After adding virtual columns to the left table data, the data size is doubled, that is, 200GB.

[0061] Based on the slice size configuration and table size, we can calculate that the number of tasks in the shuffle map phase for the left table is 100G / 512M = 200, and the number of tasks in the shuffle map phase for the right table is 200G / 512M = 400. Therefore, for CartesianProductJoin, the number of tasks in the shuffle reduce phase is 400*200 = 80,000, and the shuffle data volume is 100G*400+200G*200 = 80TB. For SortMergeJoin, since data can be shuffled using hash shuffle, the number of tasks in the shuffle reduce phase is the default number of shuffle partitions, 500, and the shuffle data volume is 200G+200G = 400GB. Table 1 shows a performance comparison before and after the Collapse Join optimization.

[0062] Table 1

[0063]

[0064] As can be seen from Table 1, after the fold join optimization, the amount of data involved in the shuffle and the number of join calculation tasks are reduced by more than 99%, which can greatly improve the execution efficiency of this type of non-equal join SQL. Combined with the above solution, in Spark SQL, the fold join optimization implementation process is as follows: Figure 3 As shown:

[0065] The optimization rules in the logical planning stage are expanded to identify logical plans that can be optimized for collapsed joins. An expanded FlatMapTransForm node is added above the Relation node or Filter node of the left table. FlatMapTransForm represents a logical plan that adds virtual join columns and expands the data. The join condition is converted so that the virtual join columns of tab1 are equal to the actual join columns of the right table.

[0066] Expand the optimization strategy for converting the logical plan to the physical plan, identify the FlatMapTransForm node in 1), and convert the logical plan of the FlatMapTransForm type into the FlatMapTransFormExec operator. The FlatMapTransFormExec operator represents the physical operator that adds virtual associated columns and expands the data.

[0067] Implement the FlatMapTransFormExec operator, which is responsible for expanding each row in the input RDD[InternalRow]. When the values ​​of the left table's associated fields are equal, the original data is retained and a virtual associated column VIRTUAL_JOIN_COL is added. The value of VIRTUAL_JOIN_COL is the value of the left table's associated fields. When the values ​​of the left table's associated fields are not equal, the original row data is converted into two rows, and virtual associated columns VIRTUAL_JOIN_COL are added to each row. The values ​​of VIRTUAL_JOIN_COL are equal to the values ​​of the two associated fields respectively.

[0068] Use the native Spark SQL SortMergeJoin strategy to perform association calculations.

[0069] This paper proposes a non-equivalent Join optimization strategy. For join calculation SQL statements that use OR to connect multiple columns, such as join conditions like tab1.col1=tab2.color tab1.col2=tab2.col, this strategy folds these non-equivalent OR conditions into equivalent virtual join column equality conditions. This optimizes the SQL join strategy from the worst-performing CartesianProductJoin to SortMergeJoin, reducing the amount of shuffle data and resource consumption during SQL execution and shortening SQL execution time, thereby improving the computational performance of these non-equivalent Join SQL statements.

[0070] By rewriting the execution plan for the SQL task of non-equal join, adjusting the non-equal join conditions to equivalent equal join conditions, and optimizing the SQL join strategy from the worst-performing CartesianProductJoin to SortMergeJoin, the amount of shuffle data and resource consumption required for SQL execution calculations are reduced, and the SQL execution time is shortened, thereby improving the computing performance of this non-equal join SQL and the overall computing efficiency of the Spark SQL cluster.

[0071] Those skilled in the art will understand that the above descriptions are merely preferred embodiments of the invention and are not intended to limit the invention. Although the invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art will still be able to modify the technical solutions described in the aforementioned embodiments or substitute equivalents for some of the technical features. Any modifications, equivalent substitutions, etc. made within the spirit and principles of the invention shall be included within the scope of protection of the invention. All technical features in this embodiment may be freely combined according to actual needs.

[0072] Finally, it should be noted that the above is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art can still modify the technical solutions described in the aforementioned embodiments or make equivalent substitutions for some of the technical features therein. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A Cartesian product join optimization method based on Spark SQL, characterized by: The specific steps include: Step 1: Expand the optimization rules in the logical plan stage to identify logical plans that can be optimized for collapsed joins. Add an expanded FlatMapTransForm node above the Relation node or Filter node of the left table. FlatMapTransForm represents a logical plan that adds virtual join columns and expands the data. It also converts the join condition so that the virtual join columns of tab1 are equal to the actual join columns of the right table. Step 2: Expand the optimization strategy for converting the logical plan to the physical plan. Identify the FlatMapTransForm node in step 1 and convert the logical plan of the FlatMapTransForm type into the FlatMapTransFormExec operator. The FlatMapTransFormExec operator represents the physical operator that adds virtual associated columns and expands the data. Step 3: Implement the FlatMapTransFormExec operator, which is responsible for expanding each row in the input RDD[InternalRow]. When the values ​​of the left table's associated fields are equal, the original data is retained and a virtual associated column VIRTUAL_JOIN_COL is added. The value of VIRTUAL_JOIN_COL is the value of the left table's associated fields. When the values ​​of the left table's associated fields are not equal, the original row data is converted into two rows, and virtual associated columns VIRTUAL_JOIN_COL are added to each row. The values ​​of VIRTUAL_JOIN_COL are equal to the values ​​of the two associated fields respectively. Step 4: Perform association calculations using the native Spark SQL SortMergeJoin strategy.

2. The Cartesian product Join optimization method based on Spark SQL according to claim 1, characterized in that: In step 1, before folding optimization, the join condition between the two tables is a non-equivalence condition, and the join calculation is performed using the CartesianProductJoin strategy.

3. The Cartesian product Join optimization method based on Spark SQL according to claim 1, characterized in that: In step 3, after folding optimization, a virtual join column VIRTUAL_JOIN_COL is added to the left table. The left table data is expanded based on whether the values ​​of the two join fields in the left table are equal, and then joined with the right table through the equal join condition tab1.VIRTUAL_JOIN_COL = tab2.MOBILE, thereby using the SortMergeJoin method for join calculation.

4. The method for optimizing Cartesian product joins based on Spark SQL according to claim 3, characterized in that: Use SortMergeJoin to perform join calculations, collapsing join conditions. The calculation results before and after the conversion are equivalent.

5. The method for optimizing Cartesian product joins based on Spark SQL according to claim 1, characterized in that: Before the Collapse Join optimization, each data partition of the left table is shuffled to each partition of the right table for join calculation. After the Collapse optimization, the join condition becomes an equality join condition. The data is repartitioned using the hash method and then shuffled. Finally, the SortMergeJoin strategy is used for join calculation, reducing the amount of data involved in the shuffle and the number of tasks in the join calculation stage.

Images