A runtime-based connection optimization method

Abstract

The application discloses a kind of based on runtime's connection optimization method, comprising: runtime filter can inject and push down Filter in query plan according to need, so that filter data early, reduce the intermediate data size of calculation, Join is resource-consuming, time-consuming operation, a large amount of temporary data will be generated in Join, need a large amount of IO operation, on the one hand, it can improve query performance, on the other hand, it can also reduce the consumption of resources, BloomFilter filter input end and connection end in the left child node or right child node of Join, input end will generate BloomFilter filter according to input data in runtime, connection end will filter data according to the BlooFilter constructed by input end, in connection runtime, it can automatically construct filter according to filter expression and the real situation of data input, provide strong support for connection optimization, the execution of Join will be after filtering, data is filtered greatly, can effectively reduce the cost of calculation, improve efficiency, greatly reduce redundant calculation.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of IT application, in particular to a runtime-based connection optimization method. BACKGROUND

[0002] In the field of IT application, runtime connection includes the following disadvantages:

[0003] 1. Runtime filtering

[0004] Join is a CPU-intensive calculation, and the amount of input data directly affects the resources occupied by Join. Filtering data before Join execution can avoid excessive occupation and waste of resources.

[0005] 2. Execution stage cannot automatically filter

[0006] The execution stage will strictly follow the execution plan for execution, and once executed, it cannot be dynamically adjusted, which makes the selection of the execution plan particularly important. However, since the generation of the execution plan depends on the generation of statistical information, there is an error between the statistical information and the actual data, and the generated execution plan is often not optimal, which leads to connecting first and filtering later in the connection process.

[0007] 3. Redundant data transmission

[0008] Data cannot be pre-filtered because the distribution of runtime data and the actual situation of the data are unknown during the generation of the execution plan, and a filter cannot be constructed to filter the data, which will cause redundant data to be transmitted between various operators, affecting query performance and causing a large load on the network.

[0009] In view of this, a runtime-based connection optimization method is provided. SUMMARY

[0010] In order to overcome the shortcomings of the prior art, the present application provides a runtime-based connection optimization method to solve the problems raised in the background art.

[0011] To solve the above technical problems, the present application provides the following technical scheme: a runtime-based connection optimization method, comprising:

[0012] A runtime filter, which can inject and push down a filter in the query plan as needed to filter data early and reduce the size of intermediate data for calculation.

[0013] Join, the Join includes a StreamIter end and a Join Keys, while the Join is a resource-consuming and time-consuming operation, a large amount of temporary data is generated in the Join, a large amount of IO operations are required, and the data participating in the calculation is reduced near the source, which can improve the query performance and reduce the consumption of resources (network / IO / CPU, etc.) on the one hand;

[0014] A BloomFilter filter includes an input end and a connection end, the input end and the connection end are in the left child node or the right child node of the Join, the input end generates the BloomFilter filter according to the input data at runtime, and the connection end filters the data according to the BloomFilter constructed by the input end;

[0015] In some association scenarios, the dimension table after filtering can be fully utilized to greatly reduce the data scanning amount of the fact table, and the execution performance of the Join calculation is improved as a whole;

[0016] By filtering out the input data that will not hit the Join in the StreamIter end of the Join, the data transmission and calculation in the Join are greatly reduced, and the overall execution time is reduced; by using the BloomFilter filter and the value of the Join Keys on the other side of the Join to generate an IN predicate, the side of the Join is pre-filtered to improve the performance of the Join.

[0017] As a preferred technical solution of the present application, the filtering of the Join is called SortMergeJoin, and the filtering steps of the SortMergeJoin at runtime are as follows:

[0018] An injection optimization rule check is injected, and the injection optimization rule check is named Runt imeFilter; the Runt imeFilter is organized in the form of a Ru l e in the optimization stage, is injected into a physical plan by an optimizer, the Ru l e name is I njectRunt imeFilter, the logic of the I nject is run when the I njectRunt imeFilter rule is called, and it is judged whether the B l oomFi lter.Enab l ed is opened or not in the I nject logic; if none of them is opened, the optimization of the Runt imeFilter will not be performed;

[0019] Profitability check of BloomFilter, the BloomFilter filter injects at both ends of Join, if the input of BloomFilter input end comes from a single leaf node, the connection end of BloomFilter filter can bring profit;

[0020] Construct BloomFilter, the process of constructing BloomFilter filters the join keys required by Join in the execution plan, and then updates them to BloomFilter through aggregation during execution, loads BloomFilter filter when scanning data on the other side of the connection, and makes conditional judgment in BloomFilter filter according to Join Keys, outputs data that meets the conditions if it meets the conditions, and filters the data if it does not meet the conditions.

[0021] As a preferred technical solution of the present application, if BloomFilter.Enabled is in the open state, the following steps are performed:

[0022] Determine the number of BloomFilter, BloomFilter is used to filter data sets, the number of BloomFilter will affect the filtering effect, but too many BloomFilter will also occupy more memory, so the selection of BloomFilter will balance the filtering effect and memory occupation;

[0023] There is no existing BloomFilter on BloomFilter filter on the corresponding Join Keys, and there can be only one BloomFilter filter on the corresponding Join Keys;

[0024] Join Keys is a simple expression, and complex expression calculations such as UDF, JSON_TO_STRUCT, and REGEXP_REPLACE cannot support BloomFilter filter;

[0025] Join type judgment, only when the Join type is Inner, LeftSemi, or RightOuter, can BloomFilter injection be performed;

[0026] After the check is completed and the conditions are met, the InjectBloomFilter operation can be performed.

[0027] As a preferred technical solution of the present application, the filter expression of the input end of the BloomFilter filter has a selective predicate, and the following filter expressions can bring benefits: Not, And, Or, =, >=, <=, <, >, In, Contains, StartsWith, EndsWith, LikeAll, NotLikeAll, LikeAny, NotLikeAny, non-list expression, and BloomFilter filter cannot be constructed.

[0028] As a preferred technical solution of the present application, the current Join is SortMergeJoin or HashJoin, and the size of the filter application end is greater than bloomFilter.applicationSideScanSizeThreshol (default is 10GB), because the transmission of BloomFilter itself will bring certain cost, and if the data of the application end is too small, the cost of transmission will be greater than that without BloomFilter.

[0029] When the injection rule check passes and the application of BloomFilter is expected to bring benefits, injectFilter is executed to construct BloomFilter.

[0030] As a preferred technical solution of the present application, the process of constructing BloomFilter mainly has the following steps:

[0031] Selecting a Hash function;

[0032] Hashing the Join Keys of the input end of the BloomFilter filter, encapsulating the aggregation function of the BloomFilter filter, and encapsulating it as an aggregation expression, and setting the size of the filter to the number of rows of the table;

[0033] Directly applying the BloomFilter filter to the logic plan of the application end, while performing column pruning and constant merging;

[0034] Hashing the Join Keys of the other side (the large table side) of the Join, and performing BloomFilter filter query matching, if the matching data can be matched, the matching data is output, and if the matching data cannot be matched, the unmatched data is filtered.

[0035] Compared with the prior art, the present application can achieve the beneficial effects that:

[0036] In the execution engine runtime, the connection operator can automatically filter the child node data of the connection node according to the filtering condition, and reduce the size of the input data by filtering the input data, thereby improving the performance and reducing the waste of resources.

[0037] In the execution engine runtime, a BloomFilter filter is constructed for the continuously input data on one side of the connection, and the filtering operation is performed on the other side after the construction is completed, and the applicability check, profitability check, construction process and application process of the BloomFilter filter can guarantee the filtering effectiveness and high efficiency, thereby improving the performance of the connection. BRIEF DESCRIPTION OF DRAWINGS

[0038] Figure 1 Flowchart of the SortMergeJoin runtime filtering of the present application;

[0039] Figure 2 Flowchart of the HashJoin runtime filtering of the present application. DETAILED DESCRIPTION

[0040] In order to make the technical means, creative features, purposes and effects of the present application easy to understand, the following specific embodiments are further described, but the following embodiments are only preferred embodiments of the present application, not all. Based on the embodiments in the embodiments, other embodiments obtained by those skilled in the art without creative labor are within the protection scope of the present application. The experimental methods in the following embodiments are conventional methods, and the materials and reagents used in the following embodiments are commercially available unless otherwise specified.

[0041] Embodiment:

[0042] As shown in Figure 1 and Figure 2 The present application provides a connection optimization method based on runtime, comprising:

[0043] Runtime filter, the runtime filter can inject and push down the filter in the query plan as needed, so as to filter the data at an early stage and reduce the size of the intermediate data of the calculation;

[0044] Join, the Join includes StreamIter end and Join Keys, and the Join is a resource-consuming and time-consuming operation, a large amount of temporary data will be generated in the Join, a large amount of IO operation is required, and the data participating in the calculation is reduced near the source, which can improve the query performance on the one hand, and can reduce the consumption of resources (network / IO / CPU, etc.) on the other hand;

[0045] The BloomFilter filter includes an input end and a connection end, the input end and the connection end are left child nodes or right child nodes of the Join, the input end generates the BloomFilter filter according to input data at runtime, and the connection end filters data according to the BloomFilter constructed by the input end;

[0046] In some association scenarios, the dimension table after filtering can be fully utilized to greatly reduce the data scanning amount of the fact table, and the execution performance of the Join calculation is improved as a whole;

[0047] By filtering out the input data that cannot hit the Join in the StreamIter end of the Join in advance, the data transmission and calculation in the Join are greatly reduced, and the overall execution time is reduced; BloomFilter filters and the values of the Join Keys on the other side of the Join are used to generate IN predicates, and then the pre-filtering of one side of the Join is performed to improve the performance of the Join.

[0048] As shown in Figure 1 The filtering of the Join is called SortMergeJoin, and the filtering steps of the SortMergeJoin at runtime are as follows:

[0049] The injection optimization rule check is called Runt imeFilter, the Runt imeFilter is organized in the form of Ru l e in the optimization stage, and is injected into the physical plan by the optimizer, the Ru l e name is InjectRunt imeFilter, the logic of the injection is run when the InjectRunt imeFilter rule is called, and it is judged in the Inject logic whether the BloomFilter.Enab l ed is opened, if none of them is opened, the Runt imeFilter optimization will not be performed;

[0050] The profitability check of the BloomFilter, the BloomFilter filter is injected on both ends of the Join, if the input of the BloomFilter input end comes from a single leaf node, the connection end of the BloomFilter filter can bring benefits;

[0051] Construct BloomFilter, BloomFilter process, will filter the join keys required by the join in the execution plan, and then update it to the BloomFilter through aggregation during the execution. When the other side of the connection scans the data, load the BloomFilter filter, and make a conditional judgment according to the join keys in the BloomFilter filter. If it is satisfied, output the data that meets the condition. If it is not satisfied, filter the data.

[0052] As shown in Figure 1 and Figure 2 The embodiment discloses that if BloomFilter.Enabled is in an open state, the following steps are performed:

[0053] Determine the number of BloomFilter, BloomFilter is used to filter the data set, the number of BloomFilter will affect the filtering effect, but too many BloomFilter will also occupy more memory, so the selection of BloomFilter will balance the filtering effect and memory occupation;

[0054] There is no existing BloomFilter on the BloomFilter filter corresponding to the join keys. There can be only one BloomFilter filter on the BloomFilter corresponding to the join keys.

[0055] The join keys are simple expressions, and complex expression calculations such as UDF, JSON_TO_STRUCT, and REGEXP_REPLACE cannot support BloomFilter filters.

[0056] Joi n type judgment, only when the Joi n type is Inner, LeftSemi, and RightOuter, can BloomFilter injection be performed.

[0057] After the check is completed and the condition is met, the InjectBloomFilter operation can be performed.

[0058] As shown in Figure 1 and Figure 2As shown in the embodiments, the filter expression of the input end of the BloomFilter filter has a selective predicate, and the following filter expressions can bring benefits: Not, And, Or, =, >=, <=, <, >, In, Contains, StartsWith, EndsWith, LikeAll, NotLikeAll, LikeAny, NotLikeAny, non-list expressions, and BloomFilter filters cannot be constructed.

[0059] As shown in the embodiments, the filter expression of the input end of the BloomFilter filter has a selective predicate, and the following filter expressions can bring benefits: Not, And, Or, =, >=, <=, <, >, In, Contains, StartsWith, EndsWith, LikeAll, NotLikeAll, LikeAny, NotLikeAny, non-list expressions, and BloomFilter filters cannot be constructed. Figure 1 Figure 2 As shown in the embodiments, the filter expression of the input end of the BloomFilter filter has a selective predicate, and the following filter expressions can bring benefits: Not, And, Or, =, >=, <=, <, >, In, Contains, StartsWith, EndsWith, LikeAll, NotLikeAll, LikeAny, NotLikeAny, non-list expressions, and BloomFilter filters cannot be constructed.

[0060] When the injection rule check passes and the application of BloomFilter is expected to bring benefits, injectFilter is executed to construct the BloomFilter.

[0061] As shown in the embodiments, the filter expression of the input end of the BloomFilter filter has a selective predicate, and the following filter expressions can bring benefits: Not, And, Or, =, >=, <=, <, >, In, Contains, StartsWith, EndsWith, LikeAll, NotLikeAll, LikeAny, NotLikeAny, non-list expressions, and BloomFilter filters cannot be constructed. Figure 2 As shown in the embodiments, the filter expression of the input end of the BloomFilter filter has a selective predicate, and the following filter expressions can bring benefits: Not, And, Or, =, >=, <=, <, >, In, Contains, StartsWith, EndsWith, LikeAll, NotLikeAll, LikeAny, NotLikeAny, non-list expressions, and BloomFilter filters cannot be constructed.

[0062] A Hash function is selected.

[0063] The join keys of the input end of the BloomFilter filter are hashed, the aggregation function of the BloomFilter filter is encapsulated, and an aggregation expression is encapsulated, and the size of the filter is set to the number of rows of the table.

[0064] The logical plan of the application end is directly applied to the BloomFilter filter, and column pruning and constant merging are simultaneously performed.

[0065] The join keys of the other side (the large table side) of the join are hashed, and BloomFilter filter query matching is performed. If matching is found, the matching data is output, and if no matching is found, the non-matching data is filtered.

[0066] ​The beneficial effects of the method for optimizing connection based on runtime of the application are as follows:

[0067] 1. Dynamically filtering data

[0068] In the prior art, once the execution plan is determined, the filtering will occur after the connection operation, and the filtering cannot be dynamically performed according to the data condition; however, in the method of the application, during the connection runtime, the filter can be automatically constructed according to the filtering expression and the real condition of the data input, the filter balances the performance and the construction cost, and provides strong support for the connection optimization.

[0069] 2. Greatly improving the efficiency of JOIN

[0070] In the prior art, the execution sequence of JOIN is to first perform the connection and then perform the filtering, the input data is not filtered, the data amount is large, the calculation cost is increased, and the JOIN operation is inefficient; however, in the method of the application, the execution of JOIN will be performed after the filtering, the data is greatly filtered, the calculation cost can be effectively reduced, and the efficiency is improved.

[0071] 3. Small resource occupation

[0072] In the prior art, the execution of the static execution plan will cause a large amount of redundant calculation to occupy the CPU, network and memory resources, the resources cannot be effectively utilized, and the resources are wasted; however, in the method of the application, the redundant calculation can be greatly reduced, the resource occupation is more reasonable, and the CPU, network and memory resources can be more effectively utilized.

[0073] Although the embodiments of the application have been shown and described, it can be understood by those skilled in the art that various changes, modifications, replacements and variations can be made to the embodiments without departing from the principles and spirits of the application, the scope of the application is defined by the appended claims and their equivalents.

Claims

1. A runtime-based connection optimization method, characterized in that, include: Runtime filters, which can be injected and pushed down into the query plan as needed, can filter data early and reduce the size of intermediate data in the computation. Join includes the StreamIter end and Join Keys. Join is a resource-intensive and time-consuming operation. Join generates a lot of temporary data and requires a lot of IO operations. It reduces the amount of data involved in the calculation closer to the source, which can improve query performance and reduce resource consumption (network / IO / CPU, etc.). A Bloom filter includes an input end and a connection end, which are located in the left or right child node of a Join. During runtime, the input end generates a Bloom filter based on the input data, and the connection end filters the data based on the Bloom filter constructed by the input end. In some related scenarios, the filtered dimension tables can be fully utilized to significantly reduce the amount of data scanned from the fact table, thereby improving the overall performance of the Join calculation. By filtering out input data that will not be included in the Join operation in advance at the StreamIter end of the Join operation, the data transfer and computation in the Join operation are significantly reduced, thus reducing the overall execution time. The performance of the Join operation is improved by generating the IN predicate using a Bloom filter and the Join Keys values ​​on the other side of the Join operation, and then pre-filtering one side of the Join operation.

2. The runtime-based connection optimization method according to claim 1, characterized in that, The filtering process described in this Join is called SortMerjoin, and the SortMerjoin runtime filtering steps are as follows: The injection optimization rule check is named RuntimeFilter. During the optimization phase, RuntimeFilter is organized into a Rule and injected into the physical plan by the optimizer. The Rule is named InjectRuntimeFilter. When the InjectRuntimeFilter rule is called, its Inject logic will be executed. In the Inject logic, it will check whether BloomFilter.Enabled is enabled. If neither is enabled, the RuntimeFilter optimization will not be performed. The profitability check of the Bloom Filter involves injecting the Bloom Filter at both ends of the Join. If the input of the Bloom Filter comes from a single leaf node, then the join of the Bloom Filter can generate a profit. The process of constructing a Bloom filter involves filtering the join keys required for the join in the execution plan, updating them in the Bloom filter through aggregation during execution, loading the Bloom filter when the join on the other side is scanning data, and performing condition checks on the Bloom filter based on the join keys. If the conditions are met, the data that meets the conditions is output; otherwise, the data is filtered out.

3. The runtime-based connection optimization method according to claim 2, characterized in that, If BloomFilter.Enabled is enabled, follow these steps: Determine the number of Bloom filters. Bloom filters are used to filter datasets. The number of Bloom filters will affect the filtering effect. However, too many Bloom filters will consume more memory. Therefore, the selection of Bloom filters will be a trade-off between filtering effect and memory consumption. If there is no existing BloomFilter on the corresponding Join Key, then only one BloomFilter can exist on the corresponding Join Key. Join Keys are simple expressions; complex expression calculations cannot support Bloom Filters. The Join type must be checked; Bloom Filter injection is only allowed when the Join type is Inner, LeftSemi, or RightOuter. Once the checks are complete and the conditions are met, the InjectBloomFilter operation can be performed.

4. The runtime-based connection optimization method according to claim 2, characterized in that, The filter expression at the input of the BloomFilter has a selective predicate, is not a list expression, and cannot construct a BloomFilter.

5. The runtime-based connection optimization method according to claim 4, characterized in that, The current Join is either SortMereJoin or HashJoin. The size of the filter application is larger than bloomFilter.applicationSideScanSizeThreshold because passing the BloomFilter itself incurs a certain cost. If the data on the application side is too small, the cost of transmission will be greater than not using the BloomFilter. If the injection rule check passes and the application of Bloom Filter is expected to generate a benefit, then injectFilter will be executed to construct Bloom Filter.

6. The runtime-based connection optimization method according to claim 2, characterized in that, The process of constructing a Bloom Filter mainly involves the following steps: Choose a hash function; Hash the Join Keys at the input of the BloomFilter, encapsulate the aggregation function of the BloomFilter, and encapsulate it as an aggregation expression. The size of the filter is set to the number of rows in the table. The application's logical plan is directly filtered by a Bloom filter, while column pruning and constant merging are performed simultaneously. Hash the Join Keys on the other side of the Join operation and query for a match using a Bloom Filter. If a match is found, the matching data is output; otherwise, the unmatched data is filtered out.

Images