Method and apparatus for optimizing data load plans for distributed data processing systems

Abstract

Embodiments of the present specification provide a method and device for optimizing a data loading plan of a distributed data processing system. In the method for optimizing a data loading plan of a distributed data processing system, the method comprises: extracting a plurality of tables and a connection condition to which a connection operation is directed from a data parallel loading plan to be optimized, which involves a plurality of table connections; selecting, based on a dynamic programming method, a candidate execution plan with the minimum execution cost from a plurality of candidate execution plans equivalent to the data parallel loading plan and generated according to the plurality of tables and the connection condition as an optimal execution plan, wherein the equivalence refers to that the execution order of operators in the candidate execution plan is different but the execution result and the execution position of the root operator in the execution plan are the same, and the candidate execution plan comprises a push-down execution plan for indicating pushing down at least part of the operators involving the connection operation to distributed database execution.

Description

Technical Field

[0001] The embodiments in this specification generally relate to the field of computer technology, and more particularly to methods and apparatus for optimizing data loading schedules in distributed data processing systems. Background Technology

[0002] As data volumes increase, single computers can no longer handle the storage and computation needs of large-scale data. Therefore, many distributed storage and computing systems have emerged, such as OceanBase, HDFS, TiDB, Spark, and Hive. In distributed data processing systems composed of distributed databases and distributed computing systems, generating efficient data loading plans becomes a crucial research issue when data needs to be loaded from the distributed database to the distributed computing system for computation. Existing technologies for distributed data processing systems involving multi-table joins include two methods: one is to load each single table into the distributed computing system in parallel, and then use parallel join operators to compute the results on the distributed computing system side; the other is to perform parallel joins on the distributed database side, and then read the join results at a single point on the distributed computing system side. However, because the execution plan optimizers of the above methods do not consider the characteristics of multi-table parallel loading mechanisms, and the single-point loading method is slow and prone to memory shortages, the generated execution plans are inefficient. Summary of the Invention

[0003] In view of the above, embodiments of this specification provide a method and apparatus for optimizing a data loading plan in a distributed data processing system. Using this method and apparatus, dynamic programming can be introduced to address the problem of optimizing a data loading plan involving at least some operators of multi-table joins to be pushed down to a distributed database for execution, thereby obtaining an optimal execution plan and achieving efficient optimization of the data loading plan.

[0004] According to one aspect of an embodiment of this specification, a method is provided for optimizing a data loading plan of a distributed data processing system, the distributed data processing system including a distributed database and a distributed computing system. The method includes: extracting multiple tables and join conditions for join operations from a data parallel loading plan involving multiple table joins to be optimized, wherein the data parallel loading plan includes an execution plan for instructing the parallel loading of join results of multiple tables satisfying the join conditions into the distributed computing system, the multiple tables being stored in the distributed database; and selecting the candidate execution plan with the lowest execution cost from multiple candidate execution plans generated based on the multiple tables and join conditions that are equivalent to the data parallel loading plan as the optimal execution plan, based on a dynamic programming method, wherein equivalence means that the execution order of operators in the candidate execution plans is different but the execution result is the same as the execution position of the root operator in the execution plan, and the candidate execution plan includes a pushdown execution plan for instructing at least some operators involving join operations to be pushed down to the distributed database for execution.

[0005] According to another aspect of the embodiments of this specification, an apparatus is provided for optimizing a data loading plan of a distributed data processing system, the distributed data processing system including a distributed database and a distributed computing system, the apparatus comprising: an extraction unit configured to extract multiple tables and join conditions for join operations from a data parallel loading plan involving multiple table joins to be optimized, wherein the data parallel loading plan includes an execution plan for instructing the parallel loading of join results of multiple tables satisfying the join conditions into the distributed computing system, the multiple tables being stored in the distributed database; and an optimization unit configured to select the candidate execution plan with the lowest execution cost from multiple candidate execution plans generated based on the multiple tables and join conditions that are equivalent to the data parallel loading plan as the optimal execution plan, wherein equivalence means that the execution order of operators in the candidate execution plans is different but the execution result is the same as the execution position of the root operator in the execution plan, the candidate execution plans including a pushdown execution plan for instructing at least some operators involving join operations to be pushed down to the distributed database for execution.

[0006] According to another aspect of the embodiments of this specification, an apparatus for optimizing a data loading schedule of a distributed data processing system is provided, comprising: at least one processor, a memory coupled to the at least one processor, and a computer program stored on the memory, wherein the at least one processor executes the computer program to implement the method for optimizing a data loading schedule of a distributed data processing system as described above.

[0007] According to another aspect of the embodiments of this specification, a computer-readable storage medium is provided that stores a computer program, which, when executed by a processor, implements the method described above for optimizing a data loading schedule for a distributed data processing system.

[0008] According to another aspect of the embodiments of this specification, a computer program product is provided, including a computer program that is executed by a processor to implement the method described above for optimizing a data loading schedule for a distributed data processing system. Attached Figure Description

[0009] A further understanding of the nature and advantages of this specification can be achieved by referring to the following figures. In the figures, similar components or features may have the same reference numerals.

[0010] Figure 1 An exemplary architecture of a method and apparatus for optimizing a data loading schedule in a distributed data processing system, according to embodiments of this specification, is shown.

[0011] Figure 2 A flowchart illustrating an example of a method for optimizing a data loading schedule in a distributed data processing system according to an embodiment of this specification is shown.

[0012] Figure 3 A schematic diagram illustrating an example of a process for selecting the optimal execution plan based on dynamic programming according to an embodiment of this specification is shown.

[0013] Figure 4 A flowchart illustrating an example of the process for selecting the optimal execution plan according to an embodiment of this specification is shown.

[0014] Figure 5 A flowchart illustrating an example of the process for determining the optimal execution plan according to an embodiment of this specification is shown.

[0015] Figure 6 A flowchart illustrating an example of the process for determining the optimal execution plan according to an embodiment of this specification is shown.

[0016] Figure 7 A block diagram illustrating an example of an apparatus for optimizing a data loading schedule in a distributed data processing system according to embodiments of this specification is shown.

[0017] Figure 8 A block diagram of an example optimization unit in an apparatus for optimizing a data loading schedule in a distributed data processing system, according to an embodiment of this specification, is shown.

[0018] Figure 9A block diagram of an example of a planning optimization module in an apparatus for optimizing a data loading schedule of a distributed data processing system, according to an embodiment of this specification, is shown.

[0019] Figure 10 A block diagram illustrating an example of an apparatus for optimizing a data loading schedule in a distributed data processing system according to embodiments of this specification is shown. Detailed Implementation

[0020] The subject matter described herein will be discussed below with reference to exemplary embodiments. It should be understood that these embodiments are discussed merely to enable those skilled in the art to better understand and implement the subject matter described herein, and are not intended to limit the scope, applicability, or examples set forth in the claims. The function and arrangement of the elements discussed may be changed without departing from the scope of the embodiments described herein. Various processes or components may be omitted, substituted, or added as needed in the various examples. Furthermore, features described in some examples may be combined in other examples.

[0021] As used herein, the term "comprising" and its variations are open terms meaning "including but not limited to". The term "based on" means "at least partially based on". The terms "one embodiment" and "an embodiment" mean "at least one embodiment". The term "another embodiment" means "at least one other embodiment". The terms "first", "second", etc., may refer to different or the same objects. Other definitions, whether explicit or implicit, may be included below. Unless explicitly indicated by the context, the definition of a term shall remain consistent throughout the specification.

[0022] In this specification, the term "execution plan" can be used to describe the execution process of a database performing queries (such as SQL statements) on a distributed database involving multiple tables or partitions, or the loading of data by a computational system. An execution plan can consist of multiple operators representing data processing operations (such as selection, projection, join, etc.).

[0023] The method and apparatus for optimizing a data loading schedule in a distributed data processing system according to embodiments of this specification will now be described in detail with reference to the accompanying drawings.

[0024] Figure 1 An exemplary architecture 100 of a method and apparatus for optimizing a data loading schedule in a distributed data processing system according to embodiments of this specification is shown.

[0025] exist Figure 1 In this system, network 110 is used to interconnect terminal device 120 and distributed data processing system 130.

[0026] Network 110 can be any type of network capable of interconnecting network entities. Network 110 can be a single network or a combination of various networks. In terms of coverage, network 110 can be a local area network (LAN), a wide area network (WAN), etc. In terms of the carrying medium, network 110 can be a wired network, a wireless network, etc. In terms of data switching technology, network 110 can be a circuit-switched network, a packet-switched network, etc.

[0027] Terminal device 120 can be any type of electronic computing device capable of connecting to network 110, accessing servers or websites on network 110, processing data or signals, etc. For example, terminal device 120 can be a desktop computer, laptop computer, tablet computer, smartphone, etc. Although in Figure 1 Only one terminal device is shown in the diagram, but it should be understood that a different number of terminal devices may be connected to network 110.

[0028] In one implementation, terminal device 120 can be used by a user. Terminal device 120 may include application clients (e.g., application client 121) that can provide various services to the user. In one example, the application client may be an application involving database operations, such as a shopping application, a financial application, or a news application. In some cases, application client 121 can interact with distributed data processing system 130. For example, application client 121 can transmit user-input messages to distributed data processing system 130 and receive responses associated with those messages from distributed data processing system 130. In this document, "message" can refer to any input information, such as information from user input that represents a data query.

[0029] The distributed data processing system 130 may include a distributed database 131 and a distributed computing system 132 connected by communication. The distributed computing system is Spark. The distributed database 131 may be various distributed databases, such as a distributed HTAP (Hybrid Transactional / Analytical Processing) database management system. As an example, the distributed database 131 may be OceanBase with a cloud-native distributed architecture, which supports enterprise-level features such as financial-grade high availability, transparent horizontal scaling, distributed transactions, multi-tenancy, and syntax compatibility, but currently does not support complex computing functions such as machine learning (ML). The distributed computing system 132 may be various distributed big data processing and analysis engines. As an example, the distributed computing system 132 may be Spark, which may include modules such as SQL (Structured Query Language), machine learning, stream computing, and graph processing. When users need to perform complex analysis workloads such as machine learning or other UDFs (User Defined Functions) on data stored in the distributed database 131, they can combine loading the data from the distributed database 131 to the distributed computing system 132 for computation to jointly handle the aforementioned workloads.

[0030] It should be understood that Figure 1 All network entities shown are exemplary, and any other network entities may be involved in Architecture 100 depending on the specific application requirements.

[0031] Figure 2 A flowchart is shown of a method 200 for optimizing a data loading schedule in a distributed data processing system according to an embodiment of this specification.

[0032] like Figure 2 As shown in 210, the multiple tables and join conditions targeted by the join operations are extracted from the data parallel loading plan involving multiple table joins to be optimized.

[0033] In this embodiment, multiple tables and join conditions for the join operations can be extracted from the data parallel loading plan involving multi-table joins to be optimized. The data parallel loading plan may include an execution plan instructing the parallel loading of join results from multiple tables that meet the join conditions into the distributed computing system. These multiple tables may be stored in the distributed database.

[0034] In one example, for the SQL statement `select * from a,b,c,d where…(condition)`, the optimized data parallel loading plan involving multiple table joins could be the execution plan generated from the above SQL statement, for example, an execution plan that instructs the joining of tables a,b,c,d under the above conditions. In one example, the optimized data parallel loading plan involving multiple table joins could be represented by `(((a,b),c),d)`. `(((a,b),c),d)` can be used to characterize joining the result of the join between tables a and b with table c, and then joining the result of the join with table d.

[0035] In one example, the execution plan described above may include a directed acyclic graph (DAG) composed of operators. These operators can be the basic building blocks of the execution plan, used to describe the fundamental operations corresponding to specific SQL semantics, such as the TABLE SCAN, TABLE INSERT, TABLE UPDATE, TABLE DELETE, JOIN, GROUP BY, ORDER BY, and EXCHANGE operators. Alternatively, the execution plan may also be a directed tree composed of operators.

[0036] In 220, the optimal execution plan is selected from multiple candidate execution plans that are equivalent to the data parallel loading plan generated based on multiple tables and join conditions, using dynamic programming.

[0037] In this embodiment, dynamic programming can be used to generate multiple candidate execution plans equivalent to the data parallel loading plan based on the extracted tables and join conditions. Then, the candidate execution plan with the lowest execution cost is selected as the optimal execution plan from these equivalent candidate execution plans. Here, equivalence means that the execution order of operators in the candidate execution plans may differ, but the execution result is the same as the execution position of the root operator in the execution plan. The execution position can include a distributed database and a distributed computing system. The candidate execution plans can include pushdown execution plans that indicate at least some operators involving join operations should be pushed down to the distributed database for execution. In one example, the pushdown execution plan can include an execution plan that first performs scan, projection, selection, and join calculations on the distributed database and then transmits the calculation results to the distributed computing system.

[0038] In one example, Oceanbase and Spark can be used as a distributed database and a distributed computing system, respectively, in a Join operation. OB (a, Join) OB (b, c) and JoinOB (Join OB (a, b), c) are equivalent programs, while Join OB (a, Join) OB (b, c) and Join Spark (a, Join) Spark (b, c) is not an equivalent program. Join OB (a, Join) OB (b, c) is used to characterize a situation in a distributed database system where a join operation is performed on tables b and c first, followed by a join operation on table a with the result of the join operation. OB (Join OB (a, b), c) are used to characterize a situation in a distributed database system where the join operation between tables a and b is performed first, followed by the join operation between the result of the join operation and table c. Spark (a, Join) Spark (b, c) is used to characterize the situation where, in a distributed computing system, the join operation between tables b and c is performed first, and then the join operation between table a and the above join result is performed.

[0039] In one example, using Oceanbase and Spark as a distributed database and distributed computing system respectively, transfer(Join) OB (a, b, c, d)), Join Spark (transfer(Join OB (a, b)), transfer(Join) OB (c, d)) and Join Spark (transfer(a), transfer(b), transfer(c), transfer(d)) all belong to equivalent projects. Among them, transfer(Join) OB (a, b, c, d) is used to represent the process of first pushing the execution plan of joining tables a, b, c, d down to the distributed database system for execution, and then transmitting the join result to the distributed computing system. Spark (transfer(Join OB (a, b)), transfer(Join) OB (c, d) represents the process of first pushing the execution plan of joining tables a, b and c, d down to the distributed database system for execution, then transmitting the join results of tables a, b and c, d to the distributed computing system, and finally executing the join between the join results of tables a, b and c, d in the distributed computing system. Spark(transfer(a), transfer(b), transfer(c), transfer(d)) is used to represent transferring tables a, b, c, and d to a distributed computing system, and then performing a join on tables a, b, c, and d in the distributed computing system.

[0040] Optionally, refer to Figure 3 , Figure 3 A schematic diagram illustrating an example of a process 300 for selecting the optimal execution plan based on dynamic programming according to an embodiment of this specification is shown.

[0041] like Figure 3 As shown, for loading the join results of tables a, b, c, d into a Spark data loading plan, taking Oceanbase (OB) and Spark as a distributed database and distributed computing system respectively, the solid arrows in the diagram indicate the extraction of sub-execution plans, and the dashed arrows indicate dependencies. In one example, the execution plan on Spark may include: transferring the join results obtained from performing the join of tables a, b, c, d on OB (as shown by the root node (a, b, c, d) in the execution plan on OB) to Spark; and performing the join of the join results of tables a, b, c with table d on Spark (as shown by the node in the execution plan on Spark). As shown in the figure, the execution plan on Spark will join the results of the join between tables a and c with the results of the join between tables b and d (as shown in the node in the Spark execution plan). (As shown). Similarly, for an execution plan that joins tables a, b, and c on Spark (as shown by node [a, b, c] in the Spark execution plan), it can include: transferring the join result obtained from joining tables a, b, and c on OB (as shown by node (a, b, c) in the OB execution plan) to Spark, and then performing a join on Spark that combines the join result of tables a and b with table c (as shown by node [a, b, c] in the Spark execution plan). As shown in the figure, the execution plan on Spark will join the result of the join between tables a and c with table b (as shown in the node in the Spark execution plan). As shown in the figure). An execution plan that joins tables a, b, and c on OB (as shown by node (a, b, c) in the execution plan on OB) can include: joining the result of the join between tables a and b with table c on OB (as shown by node (a, b, c) in the execution plan on OB). As shown), the join result of tables a and c is executed on OB to join table b (as shown in the node in the execution plan on OB in the figure). (As shown).

[0042] It is understandable that for nodes in the graph (e.g.) There are other equivalent execution plans, such as... and Etc., not shown in the diagram, and will not be described further here.

[0043] It is understandable that, for an input query, compared to the brute-force search method that enumerates all equivalent plans, the dynamic programming method can generate a data loading plan more efficiently because it can prune unnecessary search branches and reduce the size of the search space by enumerating only a portion of the equivalent plans. For example, when joining tables a, b, c, d, a brute-force search will enumerate all possible orders; while dynamic programming, if it is known that plan (a, (b, c)) is better than plan ((a, b), c), will only enumerate ((a, (b, c), d), and not (((a, b), c), d). Here, (a, (b, c)) represents joining tables b and c first, then joining table a and the result of the above join. ((a, b), c) represents joining tables a and b first, then joining the result of the above join and table c. ((a, (b, c), d) represents joining the result of ((a, b), c) and table d. (((a, b), c), d) represents joining the result of ((a, b), c) and table d.

[0044] Optionally, refer to Figure 4 , Figure 4 A flowchart illustrating an example of an optimal execution plan selection process 400 according to an embodiment of this specification is shown.

[0045] like Figure 4 As shown, in 410, the initial state is determined based on the state space.

[0046] In this embodiment, the dimensions of the state space can include the set of tables formed by the multiple tables and the execution location. The execution location can be used to indicate whether the root operator of the candidate execution plan is executed on a distributed database or a distributed computing system. In one example, the initial state can be a state for a single table where the join order does not need to be determined.

[0047] In one example, the state space can be represented by a two-dimensional array L(s)(t). Here, s can represent the set of tables formed by the aforementioned tables. t can represent the execution location, and its value can be a distributed database or a distributed computing system.

[0048] Optionally, the initial state may include a first initial state corresponding to the execution location in the distributed computing system. In one example, for a table `table`, taking Spark as the distributed computing system, the first initial state can be represented as `L({table})(Spark) = Mintransfer(table)`. Here, `transfer()` can be used to represent transferring data from the distributed database to the distributed computing system. `Min` can be used to represent the process of selecting the execution plan with the lowest execution cost among multiple execution plans. In one example, the above process can use a cost model to calculate the execution cost of each execution plan and then select the execution plan with the lowest execution cost. It should be noted that the above cost model can refer to a tool used by a distributed data processing system to optimize execution plans; its input is an execution plan or sub-execution plan, and its output is the execution cost. It can be understood that the lower the execution cost, the better the corresponding execution plan or sub-execution plan is generally.

[0049] Optionally, the initial state may also include a second initial state corresponding to the execution location in the distributed database. In one example, for the table `table`, using Oceanbase as the distributed database, the second initial state can be represented as L({table})(OB) = table.

[0050] In step 420, the optimal execution plan is obtained based on the initial state and the state transition equation.

[0051] In this embodiment, the state transition equation can be used to indicate the selection of the sub-execution plan with the lowest execution cost from multiple equivalent sub-execution plans among the candidate execution plans.

[0052] It is understandable that for a set of tables formed by multiple tables, each partitioning method will determine a join order. The execution plans used to characterize these different join orders have the same computational results but usually different execution times; therefore, they are equivalent execution plans.

[0053] In one example, a candidate execution plan may include at least one of the following: a first pushdown execution plan indicating that all operators involving join operations are pushed down to the distributed database for execution; a second pushdown execution plan indicating that some operators involving join operations are pushed down to the distributed database for execution; and a non-pushdown execution plan indicating that only the original data is transferred. In one example, taking Oceanbase and Spark as the distributed database and distributed computing system respectively, for joining tables a, b, c, and d, the aforementioned first pushdown execution plan may include transfer(Join... OB (a, b, c, d)), the second pushdown execution plan mentioned above may include Join. Spark(transfer(Join OB (a, b)), transfer(Join) OB (c, d)) and Join Spark (transfer(Join OB (a, b, c)), transfer(d)), etc., the above non-pushdown execution plans can include Join. Spark (transfer(a), transfer(b), transfer(c), transfer(d)). Among them, Join OB (a, b, c, d), Join OB (a, b), Join OB (c, d), Join OB (a, b, c) belongs to the sub-execution plan whose execution location is in the distributed database. Join Spark () belongs to the sub-execution plan whose execution location is in the distributed computing system.

[0054] Optionally, the state transition equation may include a first state transition equation corresponding to the execution location in the distributed computing system. In one example, for a set of tables s, taking Spark as the distributed computing system, the first state transition equation can be expressed as L(s)(Spark) = MinJoin Spark (L(s1)(Spark), L(s2)(Spark)). Wherein, s1∪s2=s.

[0055] Optionally, the state transition equation may also include a second state transition equation corresponding to the execution location in the distributed database. In one example, for table set s, taking Oceanbase as the distributed database, the second state transition equation can be expressed as L(s)(OB)=MinJoin OB (L(s1)(OB), L(s2)(OB)). Wherein... s1∪s2=s.

[0056] Based on this, the proposed scheme can recursively solve for the optimal execution plan using the initial state and state transition equations. Since this scheme adds the dimension of execution position to the traditional state space, it effectively solves the problem of not being able to distinguish the execution positions of operators in the execution plan when dealing with pushdown execution plans, thus facilitating the successful solution of the optimal execution plan.

[0057] Optionally, refer to the following: Figure 5 , Figure 5 A flowchart illustrating an example of a process 500 for determining an optimal execution plan according to an embodiment of this specification is shown.

[0058] In step 510, based on the first initial state and the first state transition equation, the optimal local candidate sub-execution plan corresponding to the execution location in the distributed computing system is obtained.

[0059] In this embodiment, based on the first initial state and the first state transition equation, the optimal local candidate sub-execution plan corresponding to the execution location in the distributed computing system can be recursively obtained. In one example, the table set s may include tables a, b, and c. For the sub-execution plan in the distributed computing system formed by tables [a, b, c], it can be formed by connecting tables [a, b] and [c], or by connecting tables [a, c] and [b], or by connecting tables [a] and [b, c], and so on. The sub-execution plan with the lowest execution cost among the above equivalent sub-execution plans can be determined as the optimal local candidate sub-execution plan.

[0060] In step 520, the optimal execution plan is determined based on the optimal local candidate sub-execution plan.

[0061] In one example, the optimal local candidate sub-execution plan described above can be determined as the optimal execution plan.

[0062] Based on this, this scheme can determine the optimal execution plan from multiple equivalent execution plans in the distributed computing system according to the execution position of the root operator, so that the distributed computing system can perform data loading operations or computation operations according to the above optimal execution plan.

[0063] Optionally, refer to the following: Figure 6 , Figure 6 A flowchart illustrating an example of a process 600 for determining an optimal execution plan according to an embodiment of this specification is shown.

[0064] In step 610, based on the second initial state and the second state transition equation, the optimal sub-execution plan to be transmitted corresponding to the execution location in the distributed database is obtained.

[0065] In this embodiment, based on the second initial state and the second state transition equation, the optimal sub-execution plan to be transmitted corresponding to the execution location in the distributed database can be recursively obtained. In one example, the table set s may include tables a, b, and c. For the sub-execution plan in the distributed database consisting of tables [a, b, c], it can be formed by connecting tables [a, b] and [c], or by connecting tables [a, c] and [b], or by connecting tables [a] and [b, c], and so on. The sub-execution plan with the lowest execution cost among the above equivalent sub-execution plans can be determined as the optimal sub-execution plan to be transmitted.

[0066] In step 620, the optimal sub-execution plan to be transferred is converted into a candidate sub-execution plan after transfer.

[0067] In this embodiment, the optimal sub-execution plan to be transferred can be converted into a candidate sub-execution plan after transfer in various ways, for example, it can be represented by transfer(L(s)(OB)). Here, L(s)(OB) can be used to represent the optimal sub-execution plan to be transferred. In one example, transfer(Join... OB (a,b,c) can be used to represent the execution plan that will transfer the join result of tables a,b,c executed in Oceanbase to Spark.

[0068] In step 630, the optimal execution plan is determined based on the execution costs of the optimal local candidate sub-execution plan and the candidate sub-execution plan after transmission.

[0069] In this embodiment, the execution plan corresponding to the sub-execution plan with the minimum execution cost among the optimal local candidate sub-execution plans and the candidate sub-execution plans after transmission can be determined as the optimal execution plan. In one example, for table set s, taking Spark and Oceanbase as the distributed computing system and distributed database respectively, the first state transition equation can be expressed as:

[0070] L(s)(Spark)=Min(MinJoin spark (L(s1)(Spark),L(s2)(Spark)),Mintransfer(L(s)(OB)))

[0071] The second state transition equation can be expressed as: L(s)(OB)=MinJoin OB (L(s1)(OB),L(s2)(OB)), where, s1∪s2=s.

[0072] Based on this, for execution plans executed in a distributed computing system, this scheme can consider not only the equivalent plan formed by connecting two sub-execution plans executed in a distributed computing system, but also the equivalent plan formed by transferring an execution plan executed in a distributed database. This allows for a more comprehensive selection of the optimal execution plan and improves the optimization effect of the generated optimal execution plan.

[0073] use Figures 1-6The method disclosed herein for optimizing data loading plans in distributed data processing systems addresses the problem of optimizing data loading plans involving at least some operators of multi-table joins to be pushed down to the distributed database for execution, thereby obtaining the optimal execution plan. Compared to the greedy algorithm's tendency to get trapped in local optima and the brute-force search's large search space, this scheme models the process of finding the optimal execution plan as an optimization problem that satisfies the output of a globally optimal solution. Furthermore, it prunes unnecessary search branches during the optimization process, thus ensuring that the globally optimal solution is not overlooked while simultaneously improving the optimization speed of the data loading plan.

[0074] Continue to refer to Figure 7 , Figure 7 A block diagram illustrating an example of an apparatus 700 for optimizing a data loading schedule in a distributed data processing system according to an embodiment of this specification is shown. This apparatus embodiment can be used with... Figures 2-6 Corresponding to the method embodiments shown, this device can be specifically applied to various electronic devices.

[0075] like Figure 7 As shown, the apparatus 700 for optimizing the data loading plan of a distributed data processing system may include an extraction unit 710 and an optimization unit 720. The aforementioned distributed data processing system may include a distributed database and a distributed computing system.

[0076] Extraction unit 710 is configured to extract multiple tables and join conditions targeted by join operations from a data parallel loading plan involving multiple table joins to be optimized. The data parallel loading plan includes an execution plan instructing the parallel loading of join results from multiple tables satisfying the join conditions into the distributed computing system, wherein the multiple tables are stored in the distributed database. The operation of extraction unit 710 can be referred to above. Figure 2 The operation described in section 210.

[0077] In one example, the execution plan comprises a directed acyclic graph consisting of operators.

[0078] Optimization unit 720 is configured to select the optimal execution plan from multiple candidate execution plans generated based on the multiple tables and join conditions, which are equivalent to the data parallel loading plan, using dynamic programming. The equivalence refers to the fact that the execution order of operators in the candidate execution plans may differ, but the execution result is the same as the execution position of the root operator in the execution plan. The candidate execution plans include pushdown execution plans that indicate at least some operators involving join operations should be pushed down to the distributed database for execution. The operation of optimization unit 720 can be referred to above. Figure 2 The operation described in section 220.

[0079] Further reference Figure 8 , Figure 8 A block diagram of an example of an optimization unit 800 in an apparatus for optimizing a data loading schedule in a distributed data processing system, according to an embodiment of this specification, is shown.

[0080] like Figure 8 As shown, the optimization unit 800 may include an initial state determination module 810 and a planning optimization module 820.

[0081] The initial state determination module 810 is configured to determine the initial state based on a state space. The dimensions of the state space include the table set formed by the multiple tables and the execution position, where the execution position indicates that the root operator of the candidate execution plan will be executed on the distributed database or the distributed computing system. The operation of the initial state determination module 810 can be referred to above. Figure 4 The operation described in section 410.

[0082] The plan optimization module 820 is configured to obtain the optimal execution plan based on the initial state and the state transition equation. The state transition equation indicates the selection of the sub-execution plan with the lowest execution cost from multiple equivalent sub-execution plans among the candidate execution plans. The operation of the plan optimization module 820 can be referred to above. Figure 4 The operation described in section 420.

[0083] Continue to refer to Figure 9 , Figure 9 A block diagram of an example of a planning optimization module 900 in an apparatus for optimizing a data loading schedule of a distributed data processing system according to an embodiment of this specification is shown.

[0084] like Figure 8 As shown, the planning optimization module 900 may include an initial optimization submodule 910 and an optimal plan determination submodule 920. The initial state includes a first initial state corresponding to the execution location in the distributed computing system. The state transition equation includes a first state transition equation corresponding to the execution location in the distributed computing system.

[0085] The initial optimization submodule 910 is configured to obtain the optimal local candidate sub-execution plan corresponding to the execution location in the distributed computing system based on the first initial state and the first state transition equation. The operation of the initial optimization submodule 910 can be referred to above. Figure 5 The operation of 510 is described.

[0086] The optimal plan determination submodule 920 is configured to determine the optimal execution plan based on the optimal local candidate sub-execution plan. The operation of the optimal plan determination submodule 920 can be referred to above. Figure 5 The operation described in section 520.

[0087] In one example, the initial state further includes a second initial state corresponding to the execution location in the distributed database. The state transition equation further includes a second state transition equation corresponding to the execution location in the distributed database. The optimal plan determination submodule 920 can be further configured to: obtain the optimal sub-execution plan to be transmitted corresponding to the execution location in the distributed database based on the second initial state and the second state transition equation; convert the optimal sub-execution plan to be transmitted into a candidate sub-execution plan after transmission; and determine the optimal execution plan based on the execution costs of the optimal local candidate sub-execution plan and the candidate sub-execution plan after transmission. The operation of the optimal plan determination submodule 920 can be referred to above. Figure 6 The operation described in section 610-630.

[0088] Reference above Figures 1 to 9 Embodiments of an apparatus for optimizing a data loading schedule in a distributed data processing system, according to embodiments of this specification, have been described.

[0089] The apparatus for optimizing the data loading plan of a distributed data processing system according to the embodiments of this specification can be implemented in hardware, software, or a combination of hardware and software. Taking software implementation as an example, as a logical device, it is formed by the processor of its host device reading the corresponding computer program instructions from memory into memory and executing them. In the embodiments of this specification, the apparatus for optimizing the data loading plan of a distributed data processing system can, for example, be implemented using an electronic device.

[0090] Figure 10 A schematic diagram of an apparatus 1000 for optimizing a data loading schedule in a distributed data processing system, according to an embodiment of this specification, is shown.

[0091] like Figure 10 As shown, the apparatus 1000 for optimizing the data loading schedule of a distributed data processing system may include at least one processor 1010, a memory (e.g., non-volatile memory) 1020, a main memory 1030, and a communication interface 1040, and the at least one processor 1010, memory 1020, main memory 1030, and communication interface 1040 are connected together via a bus 1050. The at least one processor 1010 executes at least one computer-readable instruction (i.e., the elements implemented in software described above) stored or encoded in the memory.

[0092] In one embodiment, computer-executable instructions are stored in memory that, when executed, cause at least one processor 1010 to: extract multiple tables and join conditions for join operations from a data-parallel loading plan involving multiple table joins to be optimized, wherein the data-parallel loading plan includes an execution plan for instructing the parallel loading of join results of multiple tables satisfying the join conditions into the distributed computing system, the multiple tables being stored in the distributed database; and select the candidate execution plan with the lowest execution cost from multiple candidate execution plans generated based on the multiple tables and join conditions that are equivalent to the data-parallel loading plan as the optimal execution plan, wherein equivalence means that the execution order of operators in the candidate execution plans is different but the execution result is the same as the execution position of the root operator in the execution plan, the candidate execution plans including a pushdown execution plan for instructing at least some operators involving join operations to be pushed down to the distributed database for execution.

[0093] It should be understood that the computer-executable instructions stored in memory, when executed, cause at least one processor 1010 to perform the above-described combinations in the various embodiments of this specification. Figure 1-9 The description includes various operations and functions.

[0094] According to one embodiment, a program product, such as a computer-readable medium, is provided. The computer-readable medium may have instructions (i.e., the elements implemented in software as described above), which, when executed by a computer, cause the computer to perform the above-described combinations of the various embodiments of this specification. Figure 1-9 The description includes various operations and functions.

[0095] Specifically, a system or apparatus equipped with a readable storage medium may be provided, on which software program code implementing the functions of any of the embodiments described above is stored, and the computer or processor of the system or apparatus can read and execute the instructions stored in the readable storage medium.

[0096] In this case, the program code itself, which can be read from the readable medium, can perform the functions of any of the above embodiments. Therefore, the machine-readable code and the readable storage medium storing the machine-readable code constitute part of the present invention.

[0097] The computer program code required for the operation of each part of this manual can be written in any one or more programming languages, including object-oriented programming languages ​​such as Java, Scala, Smalltalk, Eiffel, JADE, Emerald, C++, C#, VB, .NET, and Python; conventional procedural programming languages ​​such as C, Visual Basic 2003, Perl, COBOL 2002, PHP, and ABAP; dynamic programming languages ​​such as Python, Ruby, and Groovy; or other programming languages. This program code can run on the user's computer, or as a standalone software package on the user's computer, or partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer can be connected to the user's computer via any network, such as a local area network (LAN) or wide area network (WAN), or connected to an external computer (e.g., via the Internet), or in a cloud computing environment, or used as a service, such as Software as a Service (SaaS).

[0098] Examples of readable storage media include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD-RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, program code can be downloaded from a server computer or the cloud via a communication network.

[0099] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0100] Not all steps and units in the above process and system structure diagrams are mandatory; some steps or units can be omitted as needed. The execution order of each step is not fixed and can be determined as required. The device structure described in the above embodiments can be a physical structure or a logical structure. That is, some units may be implemented by the same physical entity, or some units may be implemented by multiple physical entities, or they may be jointly implemented by certain components in multiple independent devices.

[0101] The term "exemplary" as used throughout this specification means "serving as an example, instance, or illustration" and does not imply that it is "preferred" or "advantageous" over other embodiments. Detailed descriptions are included for the purpose of providing an understanding of the described techniques. However, these techniques may be practiced without these detailed descriptions. In some instances, well-known structures and apparatuses are shown in block diagram form to avoid obscuring the concepts of the described embodiments.

[0102] The optional embodiments of the present specification have been described in detail above with reference to the accompanying drawings. However, the embodiments of the present specification are not limited to the specific details in the above embodiments. Within the scope of the technical concept of the embodiments of the present specification, various simple modifications can be made to the technical solutions of the embodiments of the present specification, and these simple modifications all fall within the protection scope of the embodiments of the present specification.

[0103] The foregoing description of this specification is provided to enable any person skilled in the art to implement or use the content of this specification. Various modifications to the content of this specification will be apparent to those skilled in the art, and the general principles defined herein can be applied to other variations without departing from the scope of protection of this specification. Therefore, this specification is not limited to the examples and designs described herein, but is consistent with the widest scope of the principles and novel features disclosed herein.

Claims

1. A method for optimizing a data loading plan in a distributed data processing system, the distributed data processing system including a distributed database and a distributed computing system, the method comprising: Extract the multiple tables and join conditions targeted by the join operations from the data parallel loading plan involving multiple table joins to be optimized, wherein the data parallel loading plan includes an execution plan for instructing the parallel loading of join results of multiple tables that meet the join conditions into the distributed computing system, and the multiple tables are stored in the distributed database; and The optimal execution plan is selected from multiple candidate execution plans that are equivalent to the data parallel loading plan generated based on the multiple tables and join conditions using dynamic programming. The equivalence means that the execution order of the operators in the candidate execution plans is different, but the execution result is the same as the execution position of the root operator in the execution plan. The execution position of the root operator is the distributed database or the distributed computing system. The candidate execution plan includes a pushdown execution plan for indicating that at least some operators involving join operations are pushed down to the distributed database for execution.

2. The method of claim 1, wherein, The selection of the candidate execution plan with the lowest execution cost from multiple candidate execution plans generated based on the multiple tables and join conditions, which are equivalent to the data parallel loading plan, as the optimal execution plan, based on dynamic programming includes: The initial state is determined based on a state space, wherein the dimensions of the state space include a set of tables formed by the plurality of tables and an execution position, the execution position being used to indicate the execution of the root operator of the candidate execution plan on the distributed database or the distributed computing system; and The optimal execution plan is obtained based on the initial state and the state transition equation, wherein the state transition equation is used to indicate the selection of the sub-execution plan with the minimum execution cost from multiple equivalent sub-execution plans among the candidate execution plans.

3. The method of claim 2, wherein, The initial state includes a first initial state corresponding to the execution location in the distributed computing system, and the state transition equation includes a first state transition equation corresponding to the execution location in the distributed computing system. The step of obtaining the optimal execution plan based on the initial state and the state transition equation includes: Based on the first initial state and the first state transition equation, the optimal local candidate sub-execution plan corresponding to the execution location in the distributed computing system is obtained; and The optimal execution plan is determined based on the optimal local candidate sub-execution plan.

4. The method of claim 3, wherein, The initial state also includes a second initial state corresponding to the execution location in the distributed database, and the state transition equation also includes a second state transition equation corresponding to the execution location in the distributed database. The step of determining the optimal execution plan based on the optimal local candidate sub-execution plan includes: Based on the second initial state and the second state transition equation, the optimal sub-execution plan to be transmitted corresponding to the execution position in the distributed database is obtained; Convert the optimal sub-execution plan to be transferred into a candidate sub-execution plan after transfer; and The optimal execution plan is determined based on the execution costs of the optimal local candidate sub-execution plan and the candidate sub-execution plan after transmission.

5. The method of any one of claims 1 to 4, wherein, The execution plan comprises a directed acyclic graph consisting of operators.

6. An apparatus for optimizing a data loading schedule in a distributed data processing system, the distributed data processing system comprising a distributed database and a distributed computing system, the apparatus comprising: An extraction unit is configured to extract multiple tables and join conditions for join operations from a data parallel loading plan involving multiple table joins to be optimized. The data parallel loading plan includes an execution plan instructing the parallel loading of join results from multiple tables satisfying the join conditions into the distributed computing system, wherein the multiple tables are stored in the distributed database. The optimization unit is configured to select the candidate execution plan with the lowest execution cost from multiple candidate execution plans generated based on the multiple tables and join conditions that are equivalent to the data parallel loading plan, as the optimal execution plan, based on dynamic programming. The equivalence means that the execution order of the operators in the candidate execution plans is different, but the execution result is the same as the execution position of the root operator in the execution plan. The execution position of the root operator is the distributed database or the distributed computing system. The candidate execution plan includes a pushdown execution plan for indicating that at least some operators involving join operations are pushed down to the distributed database for execution.

7. The apparatus of claim 6, wherein, The optimization unit includes: An initial state determination module is configured to determine the initial state based on a state space, wherein the dimensions of the state space include a set of tables formed by the plurality of tables and an execution position, the execution position being used to indicate the execution of the root operator of the candidate execution plan on the distributed database or the distributed computing system; and The plan optimization module is configured to obtain the optimal execution plan based on the initial state and the state transition equation, wherein the state transition equation is used to indicate the selection of the sub-execution plan with the minimum execution cost from multiple equivalent sub-execution plans among the candidate execution plans.

8. The apparatus of claim 7, wherein, The initial state includes a first initial state corresponding to the execution location in the distributed computing system, and the state transition equation includes a first state transition equation corresponding to the execution location in the distributed computing system. The plan optimization module includes: The initial optimization submodule is configured to obtain the optimal local candidate sub-execution plan corresponding to the execution location in the distributed computing system based on the first initial state and the first state transition equation; and The optimal plan determination submodule is configured to determine the optimal execution plan based on the optimal local candidate sub-execution plan.

9. The apparatus of claim 8, wherein, The initial state also includes a second initial state corresponding to the execution location in the distributed database, and the state transition equation also includes a second state transition equation corresponding to the execution location in the distributed database. The optimal plan determination submodule is further configured as follows: Based on the second initial state and the second state transition equation, the optimal sub-execution plan to be transmitted corresponding to the execution position in the distributed database is obtained; Convert the optimal sub-execution plan to be transferred into a candidate sub-execution plan after transfer; and The optimal execution plan is determined based on the execution costs of the optimal local candidate sub-execution plan and the post-transfer candidate sub-execution plan.

10. An apparatus for optimizing a data load plan for a distributed data processing system, comprising: At least one processor, a memory coupled to the at least one processor, and a computer program stored on the memory, wherein the at least one processor executes the computer program to implement the method for optimizing a data loading schedule for a distributed data processing system as described in any one of claims 1 to 5.

11. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the method for optimizing a data loading schedule for a distributed data processing system as described in any one of claims 1 to 5.

12. A computer program product comprising a computer program executed by a processor to implement the method for optimizing a data loading schedule for a distributed data processing system as described in any one of claims 1 to 5.

Images