Spark SQL multi-table connection optimization method, device, computer equipment and storage medium

Abstract

The present application relates to a Spark SQL multi-table connection optimization method, device, computer equipment and storage medium. The method comprises: obtaining a set of SQL multi-table connection query statements designed according to a target data warehouse; dividing the set of SQL multi-table connection query statements into a training set and a test set; training a pre-built Markov decision model according to the training set and the test set to obtain a multi-table connection query plan selection strategy for the target data warehouse; and connecting data tables of the target data warehouse according to the multi-table connection query plan selection strategy. The method solves the problem that the existing query optimization of Spark SQL is inefficient and relies on the experience of developers when selecting a multi-table connection query plan, and can quickly and effectively build a multi-table connection query execution plan, thereby improving the execution efficiency of Spark SQL.

Description

Technical Field

[0001] The present application relates to the field of big data technology, and in particular to a Spark SQL multi-table join optimization method, device, computer equipment and storage medium. Background Art

[0002] Spark SQL is a component used by Spark to process structured data and is one of the most commonly used components in Spark. The origin of Spark SQL is to provide a quick-start tool for technicians who are familiar with traditional relational databases but do not understand the MapReduce method. They can use the Spark cluster to process large-scale data in the form of structured data query language. It is implemented by using the catalyst query optimizer to analyze and optimize the input SQL-like statements, and finally form the Spark underlying program execution. However, Spark SQL query optimization, like traditional databases, has the problem of low execution performance of multi-table join queries. For multi-table join queries, different join orders between multiple tables and the physical implementation of the join are important factors affecting the execution time, and the optimizer needs to quickly and effectively select the optimal execution plan.

[0003] At present, the method used by Spark SQL to solve the problem of selecting a query plan for multi-table joins is based on cost optimization. The cost model is set to pre-evaluate the cost of joining two tables, and a dynamic programming algorithm is used to select the join order with the lowest estimated cost to build a logical plan. The physical implementation is selected based on the set threshold to build a physical plan. However, the search space for multi-table join execution plans grows explosively with the number of joined tables. When there are many joined tables in a query, it takes too long to select the optimal plan. The threshold for selecting the physical implementation method of the join also depends on the experience of the developer to manually adjust. With the development of some complex analysis methods in big data, the problem of Spark SQL in multi-table join queries has become increasingly prominent. In recent years, some methods have emerged in the field of traditional databases that use deep reinforcement learning to optimize multi-table join queries. However, these methods only optimize the order of multi-table joins. In Spark SQL, queries are converted to Spark RDD underlying programs for execution. Different physical implementation methods of join operations during execution are also important factors affecting the performance of multi-table join queries.

[0004] Therefore, the existing Spark SQL query optimizer has the problems of low efficiency in selecting the optimal join execution plan and the selection of the optimal physical plan depends on the experience of developers. Summary of the invention

[0005] Based on this, it is necessary to provide a Spark SQL multi-table join optimization method, device, computer equipment and storage medium that can improve the efficiency of the optimal join execution plan in response to the above technical problems.

[0006] A Spark SQL multi-table join optimization method, the method comprising:

[0007] Obtain a set of SQL multi-table join query statements designed according to the target data warehouse;

[0008] Dividing the SQL multi-table join query statement set into a training set and a test set;

[0009] According to the training set and the test set, a pre-built Markov decision model is trained to obtain a multi-table join query plan selection strategy for the target data warehouse;

[0010] According to the multi-table join query plan selection strategy, the data tables of the target data warehouse are joined.

[0011] In one embodiment, the Markov decision model is constructed as follows:

[0012] Combine the SQL multi-table join query statements input by the Spark program with the data warehouse information and abstract them into the state characteristics of the Markov decision model in the form of a join tree;

[0013] The original logical plan selection and physical plan selection stages of Spark SQL are combined to redesign the plan selection method and abstract it into the action features of the Markov decision model.

[0014] According to the physical implementation of Spark SQL connection, a time cost model is designed to evaluate the time cost of executing actions as the reward of Markov decision model;

[0015] The Markov decision model is constructed according to the state characteristics, the action characteristics and the reward.

[0016] In one embodiment, the step of combining the SQL multi-table join query statement input by the Spark program with the data warehouse information and abstracting it into the state characteristics of the Markov decision model in the form of a join tree includes:

[0017] The SQL multi-table join query statement input by the Spark program is combined with the data warehouse information, and the corresponding data tables are represented in the form of each connection tree. The characteristics of the connection tree are represented in the form of a vector:

[0018]

[0019] The first m bits are the join keys contained in the join tree in the form of one-hot encoding, and the last bit Ca is the estimated cardinality of the join tree execution result, which is obtained by the cardinality estimator provided by the data warehouse;

[0020] The forest composed of the connected trees is represented as the state feature of the Markov decision model.

[0021] In one embodiment, the plan selection method is redesigned by combining the original logical plan selection and physical plan selection stages of Spark SQL, and is abstracted into the steps of action features of the Markov decision model, including:

[0022] Select two connection trees L and R from the forest of state features for connection, and specify a connection physical implementation method pa, expressed in the form of a vector [L, R, pa], as an action feature of the Markov decision model;

[0023] Among them, L represents the connection left table, R represents the connection right table, and pa represents the connection physical implementation method.

[0024] In one embodiment, the physical connection implementation method includes:

[0025] Any one or more of performing hash join after broadcasting the small table, performing hash join after data shuffling, and performing sort merge join after data shuffling.

[0026] In one embodiment, the step of designing a time cost model for evaluating the time cost of executing an action according to the physical implementation of the Spark SQL connection as a reward of the Markov decision model includes:

[0027] For the action features [L, R, pa], the estimated cardinality of the connection trees L and R is used as input, and the cost model is selected according to the selected connection physical implementation method pa. The parameters of the cost model are set according to the performance of the Spark cluster computing nodes to obtain the estimated action time cost, which is normalized and used as the reward of the Markov decision model.

[0028] In one embodiment, the step of training the pre-built Markov decision model based on the training set and the test set to obtain the multi-table join query plan selection strategy of the target data warehouse includes:

[0029] According to the training set and the test set, a pre-built Markov decision model is trained in a cross-validation training manner to obtain a multi-table join query plan selection strategy for the target data warehouse.

[0030] A Spark SQL multi-table connection optimization device, the device comprising:

[0031] A statement set acquisition module is used to acquire a SQL multi-table connection query statement set designed according to the target data warehouse;

[0032] A partitioning module, used to divide the SQL multi-table join query statement set into a training set and a test set;

[0033] A training module, used to train the pre-built Markov decision model according to the training set and the test set, and obtain a multi-table join query plan selection strategy for the target data warehouse;

[0034] A connection module is used to connect the data tables of the target data warehouse according to the multi-table connection query plan selection strategy.

[0035] A computer device comprises a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the method when executing the computer program.

[0036] A computer-readable storage medium stores a computer program, which implements the steps of the method when executed by a processor.

[0037] The above Spark SQL multi-table connection optimization method, device, computer equipment and storage medium obtain a set of SQL multi-table connection query statements designed according to the target data warehouse; divide the set of SQL multi-table connection query statements into a training set and a test set; train a pre-built Markov decision model based on the training set and the test set to obtain a multi-table connection query plan selection strategy for the target data warehouse; the Markov decision model is solved using a deep reinforcement learning algorithm to obtain a multi-table connection query plan selection strategy for the target data warehouse, and connects the data tables of the target data warehouse based on the multi-table connection query plan selection strategy. The problem of low efficiency and reliance on developer experience in the existing query optimization of Spark SQL in multi-table connection query plan selection is solved, and a multi-table connection query execution plan can be quickly and effectively constructed, thereby improving the execution efficiency of Spark SQL. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 A schematic diagram of a flow chart of a Spark SQL multi-table join optimization method in an embodiment;

[0039] Figure 2 An example diagram of a connection tree construction for a multi-table connection SQL query in one embodiment;

[0040] Figure 3 A schematic diagram of the relationship between modules of a training method in an embodiment;

[0041] Figure 4 Constructing a time result graph for an experimental test plan in one embodiment;

[0042] Figure 5It is a graph showing the execution time results of an experimental test plan in one embodiment;

[0043] Figure 6 The figure is a structural block diagram of a Spark SQL multi-table join optimization device in one embodiment. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0045] In one embodiment, Figure 1 As shown, a Spark SQL multi-table join optimization method is provided, including the following steps:

[0046] Step S220, obtaining a set of SQL multi-table connection query statements designed according to the target data warehouse.

[0047] The target data warehouse refers to the database that needs to be connected to the data table.

[0048] Step S240, dividing the SQL multi-table join query statement set into a training set and a test set.

[0049] Step S260: training the pre-built Markov decision model based on the training set and the test set to obtain a multi-table join query plan selection strategy for the target data warehouse.

[0050] Among them, the Markov decision model is used for Spark SQL multi-table join query plan selection. The Markov decision model can be used to convert the Spark SQL multi-table join query execution plan selection problem into a Markov decision process. The deep reinforcement learning algorithm can be used to train to obtain a strategy to guide the construction of the optimal plan, that is, the multi-table join query plan selection strategy for the target data warehouse.

[0051] Specifically: When Spark SQL query is applied to a data warehouse, the SQL multi-table connection query statement of the data warehouse is abstracted as an initial state according to the information of the data table in the data warehouse and the cardinality information of the data table obtained by Spark SQL through the histogram, and represented in the form of a matrix. For the data warehouse, training is performed according to the pre-built Markov decision model, and the action to be executed in the current state is selected to obtain a new state. If the new state is a terminal state, it means that the optimal execution plan has been constructed. Otherwise, the action to be executed next is selected according to the action selection strategy until the new state is a terminal state, which means that the optimal execution plan has been constructed. The optimal execution plan is the multi-table connection query plan selection strategy for the data warehouse.

[0052] Step S280, connecting the data tables of the target data warehouse according to the multi-table connection query plan selection strategy.

[0053] The above Spark SQL multi-table connection optimization method obtains a set of SQL multi-table connection query statements designed according to the target data warehouse; divides the set of SQL multi-table connection query statements into a training set and a test set; trains a pre-built Markov decision model based on the training set and the test set to obtain a multi-table connection query plan selection strategy for the target data warehouse; the Markov decision model is solved using a deep reinforcement learning algorithm to obtain a multi-table connection query plan selection strategy for the target data warehouse, and connects the data tables of the target data warehouse based on the multi-table connection query plan selection strategy. The method solves the problem that the existing query optimization of Spark SQL is inefficient and relies on the experience of developers when selecting multi-table connection query plans, and can quickly and effectively build a multi-table connection query execution plan, thereby improving the execution efficiency of Spark SQL.

[0054] In one embodiment, the method of constructing a Markov decision model is as follows: combining the SQL multi-table connection query statement input by the Spark program with the data warehouse information, and abstracting it as the state characteristics of the Markov decision model in the form of a connection tree; combining the original logical plan selection and physical plan selection stages of Spark SQL, redesigning the plan selection method, and abstracting it as the action characteristics of the Markov decision model; according to the physical implementation of the Spark SQL connection, designing a time cost model for evaluating the time cost of executing an action as a reward of the Markov decision model; and constructing a Markov decision model based on the state characteristics, action characteristics and rewards.

[0055] Among them, in order to convert the execution plan selection process of multi-table connection into a Markov decision process and solve it using the reinforcement learning method, it is first necessary to abstract the input multi-table connection query SQL statement.

[0056] In one embodiment, the steps of combining the SQL multi-table join query statement input by the Spark program with the data warehouse information and abstracting the state characteristics of the Markov decision model in the form of a join tree include:

[0057] The SQL multi-table join query statement input by the Spark program is combined with the data warehouse information, and the corresponding data tables are represented in the form of each join tree. The characteristics of the join tree are represented in the form of a vector:

[0058]

[0059] The first m bits are the join keys contained in the join tree in the form of one-hot encoding, and the last bit Ca is the estimated cardinality of the join tree execution result, which is obtained by the cardinality estimator provided by the data warehouse;

[0060] The forest of connected trees represents the state characteristics of the Markov decision model.

[0061] Among them, the execution plan of multi-table join can usually be abstracted into the form of a join tree. The join tree is a binary tree, in which non-leaf nodes represent the intermediate results of the join and have two child nodes. The leaf nodes represent the basic data tables involved in the join, and the root node represents the final result of the query. A new join tree construction method is designed based on the join tree structure. First, the initial state is a set of basic data tables contained in the multi-table join query. Each basic table can be abstracted as a join tree with only a root node. The initial state can be regarded as a forest composed of join trees representing basic tables. Secondly, each step operation is to select two join trees from the forest for join operation. The intermediate results obtained form a new join binary tree, which forms a new forest with the remaining join trees and transfers to a new state. This operation is performed continuously until only one join tree is left, that is, a join plan for the join query is constructed. In this join tree construction method, the forest in each step can be regarded as the current state, and the selection of two join trees for join operation is regarded as an action. The probability of selecting an action under each state and the change of the state after selecting an action are only related to the current state. It can be proved that the join tree construction method has Markov property.

[0062] For the state in the connection tree construction method designed above, the connection tree T i Represented in the form of a vector:

[0063] T i =[… 0 1 0 … Ca]

[0064] Among them, the first m bits are the connection keys contained in the connection tree represented in the form of one-hot encoding, and the last bit Ca is the estimated cardinality of the connection tree execution result. Then the state is regarded as a set of connection trees, which is represented in the form of a matrix. Using an n×(m+1) matrix to encode the state, it has the following form:

[0065]

[0066] Where S i is the state at time i, n is the number of all relational tables in the application data warehouse, and is also the maximum number of possible join trees, m is the number of attributes in the data warehouse that can be used as join keys, Ca iis the estimated cardinality of join tree i. Each row represents a join tree in the current state, where a bit set to 1 means that the join key represented by the column has been merged into the join tree represented by the row, and a row with all zeros means that there is no join tree at that position in the current state. When only one row in the matrix is ​​not all zeros, it means that the terminal state has been reached and a join plan tree has been obtained.

[0067] In one embodiment, the original logical plan selection and physical plan selection stages of Spark SQL are combined, and the plan selection method is redesigned and abstracted into the steps of the action features of the Markov decision model, including: selecting two connection trees L and R from the forest of state features for connection, and specifying the connection physical implementation method pa, which is expressed in the form of a vector [L, R, pa] as the action feature of the Markov decision model; wherein L represents the connection left table, R represents the connection right table, and pa represents the connection physical implementation method.

[0068] Among them, the Markov decision model also needs to abstractly represent the connection tree merging operation that causes the state change as an action vector. The Markov decision model designs the action feature vector in the form of a triple, as shown in the formula:

[0069] A(S i , j) = [L, R, pa]

[0070] Among them, A(S i , j) indicates that in state S i The action j is selected below. L and R represent the number of rows in the state matrix of the left and right subtrees participating in the connection, i.e. the number of the connection tree. The range of L and R is [0, n). pa is the selected underlying implementation of the connection operation. In Spark SQL, there are three ways to implement the physical implementation of equivalued connection, which are represented by 0, 1, and 2 in the action. For a data warehouse with n data tables, the possible actions are According to the current state, determine which actions are available. Figure 2As shown in the figure, an example is used to show the connection tree construction process of a multi-table connection SQL query, and its state characteristics and action characteristics are represented in the form of matrices and vector sets in the above manner. In the example, there are three tables in the data warehouse and each table has two attributes that can be used as connection keys. Tables A, B, and C are connected with multi-tables using attribute k as the connection key. Therefore, there are three connection trees in the initial state. The columns set to 1 in each row represent the positions of the attributes of the connection keys Ak, Bk, and Ck in the data warehouse. The cardinality of the three basic tables is obtained from the data warehouse through the cardinality estimation module and put into the last column to form a matrix representing the initial state. For this state, the optional actions in the action list are judged and selected. In the example, the first action selected [0, 1, pa], that is, connecting the connection tree 0 and the connection tree 1 using the connection underlying implementation method pa. Then there are two connection trees in the second state. The newly obtained connection tree cardinality attribute is obtained by the cardinality estimation module in the data warehouse, and the action selection continues. There is only one connection tree in the third state, that is, the terminal state is reached. This representation method abstracts the characteristics of the table and connection key in the database, effectively distinguishes different connection intermediate states, and facilitates the subsequent deep reinforcement learning module training.

[0071] In one embodiment, the physical connection implementation method includes: performing any one or more of a hash connection after broadcasting a small table, performing a hash connection after data shuffling, and performing a sort-merge connection after data shuffling.

[0072] Spark SQL provides three physical implementations for equivalent joins, including Broadcast Join (broadcasting small tables and then performing hash joins), Shuffle Hash Join (shuffling data and then performing hash joins), and Sort Merge Join (shuffling data and then performing sort merge joins). Take the range of pa values ​​as {0, 1, 2}, which represent the three physical implementations, as an example, pa 0 represents hash joins after broadcasting small tables, pa 1 represents hash joins after shuffling data, and pa 2 represents sort merge joins after shuffling data.

[0073] In one embodiment, according to the physical implementation of Spark SQL connection, a time cost model for evaluating the time cost of executing an action is designed as a reward of the Markov decision model, including: for the action feature [L, R, pa], the estimated cardinality of the connection tree L and R is used as input, the cost model is selected according to the selected connection physical implementation method pa, the parameters of the cost model are set according to the performance of the Spark cluster computing node, and the estimated action time cost is obtained, which is normalized as the reward of the Markov decision model.

[0074] In one embodiment, a pre-constructed Markov decision model is trained according to a training set and a test set to obtain a multi-table join query plan selection strategy for a target data warehouse, comprising: training the pre-constructed Markov decision model by cross-validation training according to the training set and the test set to obtain a multi-table join query plan selection strategy for the target data warehouse.

[0075] like Figure 3 As shown, in one embodiment, the method for training the pre-built Markov decision model according to the training set and the test set specifically includes the following contents:

[0076] The training method includes multi-table join query statement abstraction module (such as Figure 3 Abstract model shown), cardinality estimation and connection cost design module (such as Figure 3 Cost model shown in Figure 2) and deep reinforcement learning training module (such as Figure 3 The training model shown in the figure), the multi-table join query statement abstraction module is responsible for converting the optimal plan selection problem of multi-table join into three parts: state, action and state transition in the Markov decision process. It abstracts the state features that can represent the current connection tree situation, the action features that can represent the connection order selection and the underlying execution method selection, and the state transition equations of the state change after the action is executed from the SQL multi-table join query statement input by the Spark SQL program and the data warehouse of the application. The cardinality estimation and connection cost design module designs a cost model for judging the resource consumption of executing a certain action in a certain state by analyzing the underlying execution process of the Spark SQL connection operation, and uses the cardinality of the data table and the intermediate result as input for calculation, and obtains the estimated cost as a reward in reinforcement learning. For the three physical implementations of Spark SQL equivalent joins, BroadcastJoin, Shuffle Hash Join and Sort Merge Join, the cardinality estimation and connection cost design modules are set as follows:

[0077]

[0078]

[0079] Cost SMJ (A,B)=(Ca(A)*size(A)+Ca(B)*size(B))*T(SS)+(Ca(i)+Ca(j))*T(c)

[0080] Among them, Cost BJ (A, B) is the time cost of Spark SQL executing BroadcastJoin on data table A and data table B in the computing cluster; Cost SHJ(A, B) is the time cost of Spark SQL to perform Shuffle Hash Join on data table A and data table B in the computing cluster; Cost SMJ (A, B) is the time cost of Spark SQL to perform Sort Merge Join on data table A and data table B in the computing cluster; Ca(i) is the cardinality of table i, i∈A or B, A and B are data table numbers; Ca(j) is the cardinality of table j, i≠j∈A or B; size(i) is the average size of each data entry in table i, i∈A or B, A and B are data table numbers; T(BC) is the time constant for Spark cluster to broadcast unit data, In the Spark computing cluster subnode table And the time cost of executing Hash Join in Table B, It is the part of table A that is assigned to the child nodes of the Spark computing cluster. In the Spark computing cluster subnode table and table The time cost of executing Hash Join in It is the part of table B assigned to the child nodes of the Spark computing cluster; T(HS) is the time constant of cluster unit data execution Hash Shuffle, T(SS) is the time constant of cluster unit data execution Sort Shuffle, and T(c) is the time constant of cluster computing node comparison unit data. Cost HJ The time cost model of (i, j) is set as:

[0081]

[0082] Among them, T(hash) is the time constant for cluster computing nodes to create hash tables for unit data, M is the memory size provided by cluster child nodes, and T(w) is the time constant for cluster child nodes to write unit data. When the memory Ca(j)*size(j) occupied by table j is less than or equal to the memory size M provided by the child node, the time cost of Hash Join is the time to create a hash table for table i plus the time to scan table j, otherwise the time cost is added with the time of hash table overflow to disk. The above time constants should be set according to the configuration environment of the Spark cluster. After executing an action in a certain state, the cost value obtained according to the time cost model is normalized and used as the reward value of reinforcement learning.

[0083] The above multi-table join query statement abstraction module and cardinality estimation and join cost design module transform the multi-table join plan construction problem into a Markov decision process and train it through a deep reinforcement learning algorithm. The deep reinforcement learning DQN algorithm is used for training in implementation. After the training converges, the strategy matrix is ​​obtained, which can guide Spark SQL to more efficiently select the optimal execution plan when optimizing multi-table join queries.

[0084] In order to verify the improvement of Spark SQL multi-table join query optimization performance by this application, the JOB SQL multi-table join query set based on the Imdb online movie database was used for experimental verification. The experiment adopted cross-validation training, with 80 SQL multi-table join query statements as the training set and 33 SQL multi-table join query statements as the test set. Four sets of training sets and test sets were constructed with different divisions to ensure that each data table appears in the training set to avoid insufficient training. In this experiment, the number of MLP network layers of DQN was set to 3 to achieve good results. This application was compared with the existing CBO module for optimizing multi-table joins in Spark SQL:

[0085] like Figure 4 As shown, a comparison of the time taken to select the optimal plan by the existing dynamic programming method (CBO-dp) of Spark SQL and the Spark SQL multi-table connection optimization method (CBO-drl) based on deep reinforcement learning designed in this application under different numbers of connections is shown. The horizontal axis is the number of connections, and the vertical axis is the plan construction time (ms). The vertical axis is measured on a logarithmic scale. As can be seen from the figure, the method based on dynamic programming increases exponentially with the increase in the number of connections, while the Spark SQL multi-table connection optimization method based on deep reinforcement learning has a time complexity of approximately linear because it is greedily constructed using the trained Markov decision model. In terms of the efficiency of building the plan, when the number of connections is less than 5, the method based on dynamic programming is better than the Spark SQL multi-table connection optimization method based on deep reinforcement learning, because it takes a certain amount of time to load and calculate the policy neural network in the model. However, when the number of connections is greater than 5, the SparkSQL multi-table connection optimization method based on deep reinforcement learning is significantly better than the traditional method. Figure 5 As shown in the figure, the time comparison of CBO-dp and CBO-drl in constructing plans and executing them under different numbers of connections is shown. It can be seen that the Spark SQL multi-table join optimization method based on deep reinforcement learning can accurately select the optimal plan for execution in most cases, and the overall performance is significantly better than the traditional method when the number of connections is 3, 6, 8, 9, and 11. On average, CBO-drl is 15.2% faster than CBO-dp.

[0086] It should be understood that although Figure 1 The steps in the flowchart are shown in sequence as indicated by the arrows, but these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified in this document, there is no strict order restriction for the execution of these steps, and these steps can be executed in other orders. Moreover, Figure 1 At least part of the steps may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be executed in turn or alternately with other steps or at least part of the sub-steps or stages of other steps.

[0087] In one embodiment, Figure 6 As shown, a Spark SQL multi-table connection optimization device is provided, including: a statement set acquisition module 310, a partitioning module 320, a training module 330 and a connection module 340.

[0088] The statement set acquisition module 310 is used to acquire a SQL multi-table connection query statement set designed according to the target data warehouse.

[0089] The division module 320 is used to divide the SQL multi-table join query statement set into a training set and a test set.

[0090] The training module 330 is used to train the pre-built Markov decision model according to the training set and the test set to obtain the multi-table join query plan selection strategy of the target data warehouse.

[0091] The connection module 340 is used to connect the data tables of the target data warehouse according to the multi-table connection query plan selection strategy.

[0092] For the specific definition of the Spark SQL multi-table join optimization device, please refer to the definition of the Spark SQL multi-table join optimization method above, which will not be repeated here. Each module in the above-mentioned Spark SQL multi-table join optimization device can be implemented in whole or in part by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules.

[0093] In one embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the above-mentioned Spark SQL multi-table join optimization method when executing the computer program.

[0094] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of the above-mentioned Spark SQL multi-table join optimization method are implemented.

[0095] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0096] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0097] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. A Spark SQL multi-table join optimization method, characterized in that: The method comprises: Obtain a set of SQL multi-table join query statements designed according to the target data warehouse; Dividing the SQL multi-table join query statement set into a training set and a test set; According to the training set and the test set, a pre-built Markov decision model is trained to obtain a multi-table join query plan selection strategy for the target data warehouse; Connecting the data tables of the target data warehouse according to the multi-table connection query plan selection strategy; The Markov decision model is constructed as follows: Combine the SQL multi-table join query statements input by the Spark program with the data warehouse information and abstract them into the state characteristics of the Markov decision model in the form of a join tree; The original logical plan selection and physical plan selection stages of Spark SQL are combined to redesign the plan selection method and abstract it into the action features of the Markov decision model. According to the physical implementation of Spark SQL connection, a time cost model is designed to evaluate the time cost of executing actions as the reward of Markov decision model; Constructing the Markov decision model according to the state characteristics, the action characteristics and the reward; The step of combining the SQL multi-table connection query statement input by the Spark program with the data warehouse information and abstracting it into the state characteristics of the Markov decision model in the form of a connection tree includes: The SQL multi-table join query statement input by the Spark program is combined with the data warehouse information, and the corresponding data tables are represented in the form of each connection tree. The characteristics of the connection tree are represented in the form of a vector: The first m bits are the join keys contained in the join tree in the form of one-hot encoding, and the last bit Ca is the estimated cardinality of the join tree execution result, which is obtained by the cardinality estimator provided by the data warehouse; A forest consisting of the connected trees is represented as a state feature of a Markov decision model; The method combines the original logical plan selection and physical plan selection stages of Spark SQL, redesigns the plan selection method, and abstracts it into the steps of the action features of the Markov decision model, including: Select two connection trees L and R from the forest of state features for connection, and specify a connection physical implementation method pa, expressed in the form of a vector [L, R, pa], as an action feature of the Markov decision model; Among them, L represents the connection left table, R represents the connection right table, and pa represents the connection physical implementation method.

2. The method according to claim 1, characterized in that The physical connection implementation method comprises: Any one or more of performing hash join after broadcasting the small table, performing hash join after data shuffling, and performing sort merge join after data shuffling.

3. The method according to claim 1, characterized in that The step of designing a time cost model for evaluating the time cost of executing an action according to the physical implementation of the Spark SQL connection as a reward of the Markov decision model includes: For the action features [L, R, pa], the estimated cardinality of the connection trees L and R is used as input, and the cost model is selected according to the selected connection physical implementation method pa. The parameters of the cost model are set according to the performance of the Spark cluster computing nodes to obtain the estimated action time cost, which is normalized and used as the reward of the Markov decision model.

4. The method according to claim 1, characterized in that: The step of training the pre-built Markov decision model according to the training set and the test set to obtain the multi-table join query plan selection strategy of the target data warehouse includes: According to the training set and the test set, a pre-built Markov decision model is trained in a cross-validation training manner to obtain a multi-table join query plan selection strategy for the target data warehouse.

5. A Spark SQL multi-table connection optimization device, characterized in that: The device comprises: A statement set acquisition module is used to acquire a SQL multi-table connection query statement set designed according to the target data warehouse; A partitioning module, used to divide the SQL multi-table join query statement set into a training set and a test set; A training module, used to train the pre-built Markov decision model according to the training set and the test set, and obtain a multi-table join query plan selection strategy for the target data warehouse; A connection module, used to connect the data tables of the target data warehouse according to the multi-table connection query plan selection strategy; The Markov decision model is constructed as follows: Combine the SQL multi-table join query statements input by the Spark program with the data warehouse information and abstract them into the state characteristics of the Markov decision model in the form of a join tree; The original logical plan selection and physical plan selection stages of Spark SQL are combined to redesign the plan selection method and abstract it into the action features of the Markov decision model. According to the physical implementation of Spark SQL connection, a time cost model is designed to evaluate the time cost of executing actions as the reward of Markov decision model; Constructing the Markov decision model according to the state characteristics, the action characteristics and the reward; The step of combining the SQL multi-table connection query statement input by the Spark program with the data warehouse information and abstracting it into the state characteristics of the Markov decision model in the form of a connection tree includes: The SQL multi-table join query statement input by the Spark program is combined with the data warehouse information, and the corresponding data tables are represented in the form of each connection tree. The characteristics of the connection tree are represented in the form of a vector: The first m bits are the join keys contained in the join tree in the form of one-hot encoding, and the last bit Ca is the estimated cardinality of the join tree execution result, which is obtained by the cardinality estimator provided by the data warehouse; A forest consisting of the connected trees is represented as a state feature of a Markov decision model; The method combines the original logical plan selection and physical plan selection stages of Spark SQL, redesigns the plan selection method, and abstracts it into the steps of the action features of the Markov decision model, including: Select two connection trees L and R from the forest of state features for connection, and specify a connection physical implementation method pa, expressed in the form of a vector [L, R, pa], as an action feature of the Markov decision model; Among them, L represents the connection left table, R represents the connection right table, and pa represents the connection physical implementation method.

6. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 4 are implemented.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 4 are implemented.

Images