Computing engine selection method, device, storage medium and electronic device

Abstract

The embodiments of the present invention propose a computing engine selection method, device, storage medium and electronic device. The method includes: obtaining SQL features of the SQL query statement to be executed; obtaining data features of the table to be queried by the SQL query statement; obtaining state features of each computing engine; generating a feature vector for each computing engine; inputting each feature vector into a resource consumption prediction model and a time consumption prediction model for calculation, respectively obtaining the predicted resource consumption and predicted time consumption of each computing engine executing the SQL query statement; calculating the predicted cost of each computing engine executing the SQL query statement based on the predicted resource consumption and predicted time consumption of each computing engine executing the SQL query statement; selecting the computing engine that executes the SQL query statement based on the predicted cost of each computing engine executing the SQL query statement. The embodiments of the present invention can select the computing engine with the best performance in executing SQL query statements.

Description

Technical Field

[0001] The present invention relates to the field of big data technology, and in particular to a computing engine selection method, device, readable storage medium, and electronic device. Background Art

[0002] SQL (Structured Query Language) is a high-level non-procedural programming language. It is a database query and programming language used to access data and query, update and manage relational database systems.

[0003] Query scheduling refers to the process of scheduling query statements written in SQL to the appropriate computing engine in the big data platform for execution. The scheduling process is performed based on the specific query characteristics and engine status information.

[0004] The patent application with publication number CN110807145A discloses a query engine acquisition method, which first obtains the characteristics of the SQL statement, then obtains the weight of the time consumed by each engine in multiple engines to execute the query statement containing the feature based on the characteristics, and finally selects the engine to execute the SQL query based on the weight.

[0005] This method simply extracts SQL features and then selects a computing engine based on the inherent performance of various computing engines for queries of SQL statements containing each feature. Since only SQL features are considered, the computing engine with the best performance cannot be selected. Summary of the invention

[0006] The embodiments of the present invention provide a computing engine selection method, device, readable storage medium and electronic device to select a computing engine with the best performance for executing SQL query statements.

[0007] The technical solution of the embodiment of the present invention is achieved as follows:

[0008] A computing engine selection method, the method comprising:

[0009] Obtaining SQL features of a structured query language SQL query statement to be executed;

[0010] Obtaining data features of the table to be queried by the SQL query statement;

[0011] Obtain status characteristics of each computing engine;

[0012] For each computing engine, a feature vector is generated respectively, each feature vector including: the SQL feature of the SQL query statement, the data feature of the table to be queried by the SQL query statement, and the state feature of the corresponding computing engine;

[0013] Input each feature vector into the resource consumption prediction model for calculation, and obtain the predicted resource consumption of each computing engine executing the SQL query statement;

[0014] Input each feature vector into the time consumption prediction model for calculation, and obtain the predicted time consumption of each computing engine to execute the SQL query statement;

[0015] Calculate the predicted cost of each computing engine executing the SQL query statement according to the predicted resource consumption of each computing engine executing the SQL query statement and the predicted time consumed by each computing engine executing the SQL query statement;

[0016] A computing engine for executing the SQL query statement is selected according to the predicted cost of each computing engine executing the SQL query statement.

[0017] The step of obtaining the SQL features of the SQL query statement to be executed includes:

[0018] Extract the table name to be queried by the SQL query statement;

[0019] The grammatical features included in the SQL query statement are obtained, and the feature values ​​involved in each grammatical feature are extracted and put into the SQL feature.

[0020] The step of obtaining the grammatical features contained in the SQL query statement and extracting the feature values ​​involved in each grammatical feature and putting them into the SQL feature includes:

[0021] Determine whether the SQL query statement contains the SELECT* syntax feature, and if so, add the SELECT syntax feature flag to the SQL feature; or / and,

[0022] Extract the number and type of columns in the project column of the query, and put the number and type of columns in the project column into the SQL feature; or / and,

[0023] Determine whether the SQL query statement contains the limit syntax feature, if so, extract the limit value, and put the limit syntax feature flag and the limit value into the SQL feature; or / and,

[0024] Determine whether the SQL query statement contains a filter condition, if so, extract the filter expression, and put the filter condition flag and the filter expression into the SQL feature; or / and,

[0025] Determine whether the SQL query statement contains a union syntax feature, and if so, put the union syntax feature flag into the SQL feature; or / and,

[0026] Determine whether the SQL query statement has an aggregate function, if so, extract the aggregate function type and aggregate column, and put the aggregate function flag, the aggregate function type and the aggregate column into the SQL feature; or / and,

[0027] Determine whether the SQL query statement contains a groupby syntax feature, if so, extract the groupby grouping column, and put the groupby syntax feature flag and the groupby grouping column into the SQL feature; or / and,

[0028] Determine whether the SQL query statement contains the orderby syntax feature, if so, extract the sorting column, and put the orderby syntax feature flag and the sorting column into the SQL feature; or / and,

[0029] Determine whether the SQL query statement contains a join syntax feature, if so, extract the join type, and put the join syntax feature flag and the join type into the SQL feature; or / and,

[0030] It is determined whether the SQL query statement contains a subquery. If so, a subquery flag is placed in the SQL feature.

[0031] The step of obtaining data features of the table to be queried by the SQL query statement includes:

[0032] Get one or any combination of the following information about a table:

[0033] The number of data rows, the size of each column of data, the proportion of null values, the number of distinct values, the minimum value of each column, and the maximum value of each column.

[0034] The obtaining of the status characteristics of each computing engine includes:

[0035] Get the total number of CPUs, available CPUs, total memory, and available memory of each computing engine;

[0036] Alternatively, the total number of CPUs, the number of available CPUs, the total number of memories, the number of available memories, and at least one of the following parameters of each computing engine are obtained: online status, disk IO value, and network IO value.

[0037] The obtaining of the status characteristics of each computing engine further includes:

[0038] The state characteristics of each computing engine at a plurality of preset time points after the same time point as the submission time point of the SQL query statement on the most recent consecutive preset days are obtained.

[0039] The resource consumption prediction model and the time consumption prediction model are obtained through the following training process:

[0040] Collect multiple SQL query statements that have been executed;

[0041] For each SQL query statement, a feature vector is constructed respectively, each feature vector including: the SQL feature of the corresponding SQL query statement, the data feature of the table queried by the corresponding SQL query statement, and the state feature of the computing engine executing the corresponding SQL query statement;

[0042] Get the actual resource consumption and actual time consumed by the computing engine when executing each SQL query statement;

[0043] Input each feature vector into the resource consumption prediction model to be trained in turn, and compare the predicted resource consumption output by the resource consumption prediction model with the actual resource consumption corresponding to the input feature vector, and update the resource consumption prediction model parameters according to the comparison result until the resource consumption prediction model converges;

[0044] Each feature vector is input into the time consumption prediction model to be trained in turn, and the predicted time consumption output by the time consumption prediction model is compared with the actual time consumption corresponding to the input feature vector, and the time consumption prediction model parameters are updated according to the comparison result until the time consumption prediction model converges.

[0045] A computing engine selection device, the device comprising:

[0046] A feature acquisition module is used to obtain the SQL features of the structured query language SQL query statement to be executed; obtain the data features of the table to be queried by the SQL query statement; obtain the state features of each computing engine; and generate a feature vector for each computing engine, each feature vector containing: the SQL features of the SQL query statement, the data features of the table to be queried by the SQL query statement, and the state features of the corresponding computing engine;

[0047] A prediction module is used to input each feature vector into a resource consumption prediction model for calculation, and obtain the predicted resource consumption of each computing engine executing the SQL query statement; input each feature vector into a time consumption prediction model for calculation, and obtain the predicted time consumption of each computing engine executing the SQL query statement;

[0048] A selection module is used to calculate the predicted cost of each computing engine executing the SQL query statement based on the predicted resource consumption of each computing engine executing the SQL query statement and the predicted time consumed by each computing engine to execute the SQL query statement; and select a computing engine to execute the SQL query statement based on the predicted cost of each computing engine executing the SQL query statement.

[0049] A non-transitory computer-readable storage medium stores instructions, which, when executed by a processor, cause the processor to perform the steps of any one of the above computing engine selection methods.

[0050] An electronic device includes the non-transitory computer-readable storage medium as described above, and the processor capable of accessing the non-transitory computer-readable storage medium.

[0051] In an embodiment of the present invention, by inputting the SQL features of the SQL query statement, the data features of the table to be queried by the SQL query statement, and the state features of each computing engine into a resource consumption prediction model and a time consumption prediction model respectively, the predicted resource consumption and predicted time consumption of each computing engine executing the SQL query statement are obtained, so that the computing engine with the lowest cost is selected to execute the SQL query statement, thereby selecting the computing engine with the best performance in executing the SQL query statement. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 A flow chart of a computing engine selection method provided by an embodiment of the present invention;

[0053] Figure 2 A flow chart of a method for training a resource consumption prediction model and a time consumption prediction model provided in one embodiment of the present invention;

[0054] Figure 3 A schematic diagram of the structure of a computing engine selection device provided by an embodiment of the present invention;

[0055] Figure 4 A schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0056] The present invention will be further described in detail below in conjunction with the accompanying drawings and specific embodiments.

[0057] Figure 1 A flowchart of a computing engine selection method provided in an embodiment of the present invention, wherein the specific steps are as follows:

[0058] Step 101: Obtain SQL features of the SQL query statement to be executed.

[0059] In an optional embodiment, step 101 specifically includes: extracting the table name to be queried by the SQL query statement; obtaining the grammatical features contained in the SQL query statement, and extracting the feature values ​​involved in each grammatical feature and putting them into the SQL feature.

[0060] In an optional embodiment, the grammatical features included in the SQL query statement are obtained, and the feature values ​​involved in each grammatical feature are extracted and put into the SQL feature, including one or any combination of the following 1)-10):

[0061] 1) Determine whether the SQL query statement contains the SELECT* syntax feature. If so, add the SELECT syntax feature flag to the SQL feature;

[0062] 2) Extract the number of columns and column types of the project column of the query, and put the number of columns and column types of the project column into the SQL feature;

[0063] 3) Determine whether the SQL query statement contains the limit syntax feature. If so, extract the limit value and put the limit syntax feature flag and the limit value into the SQL feature;

[0064] 4) Determine whether the SQL query statement contains a filter condition. If so, extract the filter expression and put the filter condition flag and the filter expression into the SQL feature;

[0065] 5) Determine whether the SQL query statement contains a union syntax feature. If so, put the union syntax feature flag into the SQL feature;

[0066] 6) Determine whether the SQL query statement has an aggregate function. If so, extract the aggregate function type and aggregate column, and put the aggregate function flag, aggregate function type and aggregate column into the SQL feature;

[0067] 7) Determine whether the SQL query statement contains the groupby syntax feature. If so, extract the groupby column and put the groupby syntax feature flag and the groupby column into the SQL feature;

[0068] 8) Determine whether the SQL query statement contains the orderby syntax feature. If so, extract the sorting column and put the orderby syntax feature flag and the sorting column into the SQL feature;

[0069] 9) Determine whether the SQL query statement contains a join syntax feature. If so, extract the join type and put the join syntax feature flag and the join type into the SQL feature;

[0070] 10) Determine whether the SQL query statement contains a subquery. If so, put the subquery flag into the SQL feature.

[0071] Step 102: Obtain data features of the table to be queried by the SQL query statement.

[0072] In an optional embodiment, step 102 specifically includes: obtaining one or any combination of the following information of the table: the number of data rows, the size of each column of data, the proportion of null values, the number of distinct values, the minimum value of each column, and the maximum value of each column.

[0073] Obtaining the data features of the table to be queried by the SQL query statement is essentially obtaining the amount of data to be queried by the SQL query statement. Generally, various information of the table will be statistically calculated regularly and then summarized in the metadata center.

[0074] Step 103: Obtain the status characteristics of each computing engine.

[0075] In an optional embodiment, step 103 specifically includes: obtaining the total number of CPUs, the number of available CPUs, the total number of memories, and the number of available memories of each computing engine; or obtaining the total number of CPUs, the number of available CPUs, the total number of memories, the number of available memories, and at least one of the following parameters: online status, disk IO (input and output) value, network IO value. The online status includes: whether it is online, whether it can provide services normally, etc.

[0076] In an optional embodiment, step 103 further includes: obtaining state characteristics of each computing engine at a plurality of preset time points after the same time point as the submission time point of the SQL query statement on the most recent consecutive preset days.

[0077] For example, obtain the status features of each computing engine at 1 second, 5 seconds, 10 seconds, 30 seconds, and 1 minute after the submission time of the SQL query statement in the last three days. For example, if the submission time of the SQL query statement is 9:15:20 on October 30, 2021, then obtain the status features of each computing engine at 9:15:21, 9:15:25, 9:15:30, 9:15:50, and 9:16:20 on October 29, October 28, and October 27.

[0078] Step 104: Generate a feature vector for each computing engine. Each feature vector includes: SQL features of the SQL query statement, data features of the table to be queried by the SQL query statement, and state features of the corresponding computing engine.

[0079] For example, if there are currently m computing engines that can execute the SQL query statement, then a total of m feature vectors are generated.

[0080] In an optional embodiment, in this step 104, before generating a feature vector for each computing engine respectively, it further includes: performing further feature engineering processing on the SQL features of the SQL query statement, the data features of the table to be queried by the SQL query statement, and the state features of the corresponding computing engine, including: null value processing (i.e. filling null values), feature derivation (such as: deriving features such as whether the submission time is daytime or nighttime, whether it is a weekend, or / and whether it is a holiday based on the submission time of the SQL query statement).

[0081] Step 105: Input each feature vector into the resource consumption prediction model for calculation, and obtain the predicted resource consumption of each computing engine executing the SQL query statement.

[0082] Step 106: Input each feature vector into the time consumption prediction model for calculation, and obtain the predicted time consumption of each computing engine to execute the SQL query statement.

[0083] Step 107: Calculate the predicted cost of each computing engine executing the SQL query statement based on the predicted resource consumption of each computing engine executing the SQL query statement and the predicted time consumed by each computing engine executing the SQL query statement.

[0084] In an optional embodiment, for each computing engine, the predicted resource consumption and predicted time consumption of the computing engine executing the SQL query statement are weightedly calculated based on pre-configured resource consumption weights and time consumption weights to obtain the predicted cost of the computing engine executing the SQL query statement.

[0085] Step 108: Select a computing engine to execute the SQL query statement based on the predicted cost of each computing engine executing the SQL query statement.

[0086] In an optional embodiment, step 108 specifically includes: sorting the predicted cost of each computing engine executing the SQL query statement in ascending order, and then starting from the smallest predicted cost, sequentially performing the following judgments to determine whether the computing engine corresponding to the current predicted cost satisfies: being online and able to provide services normally; if so, selecting the computing engine corresponding to the current predicted cost to execute the SQL query statement; otherwise, switching to the next predicted cost, and returning to the action of determining whether the computing engine corresponding to the current predicted cost satisfies: being online and able to provide services normally.

[0087] In the above embodiment, by inputting the SQL characteristics of the SQL query statement, the data characteristics of the table to be queried by the SQL query statement, and the state characteristics of each computing engine into the resource consumption prediction model and the time consumption prediction model respectively, the predicted resource consumption and predicted time consumption of each computing engine executing the SQL query statement are obtained, so as to select the computing engine with the lowest cost to execute the SQL query statement, thereby selecting the computing engine with the best performance in executing the SQL query statement.

[0088] Figure 2 A flow chart of a method for training a resource consumption prediction model and a time consumption prediction model provided in an embodiment of the present invention, wherein the specific steps are as follows:

[0089] Step 201: Collect multiple SQL query statements that have been executed.

[0090] The SQL query statements here mainly refer to DQL (Data Query Language) query statements.

[0091] Step 202: construct a feature vector for each SQL query statement, each feature vector including: SQL features of the corresponding SQL query statement, data features of the table queried by the corresponding SQL query statement, and state features of the computing engine executing the corresponding SQL query statement.

[0092] In an optional embodiment, the SQL features of the SQL query statement are obtained in the following manner: extracting the table name to be queried by the SQL query statement; obtaining the grammatical features contained in the SQL query statement, and extracting the feature values ​​involved in each grammatical feature and putting them into the SQL features.

[0093] In an optional embodiment, the grammatical features included in the SQL query statement are obtained, and the feature values ​​involved in each grammatical feature are extracted and put into the SQL feature, including one or any combination of the following 1)-10):

[0094] 1) Determine whether the SQL query statement contains the SELECT* syntax feature. If so, put the SELECT syntax feature flag into the SQL feature;

[0095] 2) Extract the number of columns and column types of the project column being queried, and put the number of columns and column types of the project column into the SQL feature;

[0096] 3) Determine whether the SQL query statement contains the limit syntax feature. If so, extract the limit value and put the limit syntax feature flag and the limit value into the SQL feature;

[0097] 4) Determine whether the SQL query statement contains a filter condition. If so, extract the filter expression and put the filter condition flag and the filter expression into the SQL feature;

[0098] 5) Determine whether the SQL query statement contains a union syntax feature. If so, put the union syntax feature flag into the SQL feature;

[0099] 6) Determine whether the SQL query statement has an aggregate function. If so, extract the aggregate function type and aggregate column, and put the aggregate function flag, aggregate function type and aggregate column into the SQL feature;

[0100] 7) Determine whether the SQL query statement contains the groupby syntax feature. If so, extract the groupby grouping column and put the groupby syntax feature flag and the groupby grouping column into the SQL feature;

[0101] 8) Determine whether the SQL query statement contains the orderby syntax feature. If so, extract the sorting column and put the orderby syntax feature flag and the sorting column into the SQL feature;

[0102] 9) Determine whether the SQL query statement contains a join syntax feature. If so, extract the join type and put the join syntax feature flag and the join type into the SQL feature;

[0103] 10) Determine whether the SQL query statement contains a subquery. If so, put the subquery flag into the SQL feature.

[0104] In an optional embodiment, the data characteristics of the table to be queried by the corresponding SQL query statement are obtained in the following manner: obtaining one or any combination of the following information of the table: the number of data rows, the size of each column of data, the proportion of null values, the number of distinct values, the minimum value of each column, and the maximum value of each column.

[0105] In an optional embodiment, the status characteristics of the computing engines that execute the corresponding SQL query statements are obtained by: obtaining the total number of CPUs, the number of available CPUs, the total number of memories, and the number of available memories of the computing engines that execute the SQL query statements at the time when the SQL query statements are submitted; or, obtaining the total number of CPUs, the number of available CPUs, the total number of memories, the number of available memories, and at least one of the following parameters of the computing engines that execute the SQL query statements at the time when the SQL query statements are submitted: online status, disk IO value, network IO value.

[0106] In an optional embodiment, further based on the submission time of the SQL query statement, the state characteristics of the computing engine executing the SQL query statement at multiple preset time points after the same time point as the submission time of the SQL query statement on the most recent consecutive preset days are obtained.

[0107] In an optional embodiment, in this step 202, before constructing a feature vector for each SQL query statement, the step further includes: for each SQL query statement, further feature engineering processing is performed on the SQL features of the SQL query statement, the data features of the table queried by the SQL query statement, and the state features of the computing engine that executes the SQL query statement, including: null value processing (i.e., filling null values), feature derivation (such as: deriving features such as whether the submission time is daytime or nighttime, whether it is a weekend, and / or whether it is a holiday based on the submission time of the SQL query statement).

[0108] Step 203: Obtain the actual resource consumption and actual time consumption of the computing engine when executing each SQL query statement.

[0109] Step 204: Input each feature vector into the resource consumption prediction model to be trained in turn, and compare the predicted resource consumption output by the resource consumption prediction model with the actual resource consumption corresponding to the input feature vector, and update the resource consumption prediction model parameters according to the comparison result until the resource consumption prediction model converges.

[0110] Step 205: Input each feature vector into the time consumption prediction model to be trained in turn, and compare the predicted time consumption output by the time consumption prediction model with the actual time consumption corresponding to the input feature vector, and update the time consumption prediction model parameters according to the comparison result until the time consumption prediction model converges.

[0111] Step 204 and step 205 are executed in any order and can be executed in parallel.

[0112] In practical applications, the resource consumption prediction model and the time consumption prediction model may be trained using different machine learning algorithms or parameter configurations, which is not limited in the present invention.

[0113] In addition, different parameter configurations and / or convergence conditions can be used to train multiple resource consumption prediction models and time consumption prediction models, and verification samples can be collected in advance. The prediction effects of multiple resource consumption prediction models and multiple time consumption prediction models can be verified using the verification samples, and the resource consumption prediction model and time consumption prediction model with the best prediction effect can be selected as the resource consumption prediction model and time consumption prediction model finally adopted.

[0114] Figure 3This is a schematic diagram of the structure of a computing engine selection device provided by an embodiment of the present invention. The device mainly includes: a feature acquisition module 31, a prediction module 32 and a selection module 33, wherein:

[0115] The feature collection module 31 is used to obtain the SQL features of the SQL query statement to be executed; obtain the data features of the table to be queried by the SQL query statement; obtain the status features of each computing engine; and generate a feature vector for each computing engine, each feature vector containing: the SQL features of the SQL query statement, the data features of the table to be queried by the SQL query statement, and the status features of the corresponding computing engine.

[0116] The prediction module 32 is used to input each feature vector into the resource consumption prediction model for calculation, and obtain the predicted resource consumption of each computing engine executing the SQL query statement; and input each feature vector into the time consumption prediction model for calculation, and obtain the predicted time consumption of each computing engine executing the SQL query statement.

[0117] The selection module 33 is used to calculate the predicted cost of each computing engine executing the SQL query statement based on the predicted resource consumption of each computing engine executing the SQL query statement and the predicted time taken by each computing engine to execute the SQL query statement; and select the computing engine that executes the SQL query statement based on the predicted cost of each computing engine executing the SQL query statement.

[0118] In an optional embodiment, the feature acquisition module 31 obtains the SQL features of the SQL query statement to be executed, including: extracting the table name to be queried by the SQL query statement; obtaining the grammatical features contained in the SQL query statement, and extracting the feature values ​​involved in each grammatical feature and putting them into the SQL features.

[0119] In an optional embodiment, the feature collection module 31 obtains the grammatical features contained in the SQL query statement, and extracts the feature values ​​involved in each grammatical feature and puts them into the SQL feature, including:

[0120] Determine whether the SQL query statement contains the SELECT* syntax feature. If so, put the SELECT syntax feature flag into the SQL feature; or / and, extract the number and column type of the project column of the query, and put the number and column type of the project column into the SQL feature; or / and, determine whether the SQL query statement contains the limit syntax feature. If so, extract the limit value and put the limit syntax feature flag and the limit value into the SQL feature; or / and, determine whether the SQL query statement contains a filter condition. If so, extract the filter expression and put the filter condition flag and the filter expression into the SQL feature; or / and, determine whether the SQL query statement contains the union syntax feature. If so, put the union syntax feature flag into the SQL feature; or / and, determine whether the SQL query statement has an aggregate function , if yes, extract the aggregate function type and aggregate column, and put the aggregate function flag, aggregate function type and aggregate column into the SQL feature; or / and, determine whether the SQL query statement contains the groupby syntax feature, if yes, extract the groupby grouping column, and put the groupby syntax feature flag and the groupby grouping column into the SQL feature; or / and, determine whether the SQL query statement contains the orderby syntax feature, if yes, extract the sorting column, and put the orderby syntax feature flag and the sorting column into the SQL feature; or / and, determine whether the SQL query statement contains the join syntax feature, if yes, extract the join type, and put the join syntax feature flag and the join type into the SQL feature; or / and, determine whether the SQL query statement contains a subquery, if yes, put the subquery flag into the SQL feature.

[0121] In an optional embodiment, the feature acquisition module 31 obtains data features of the table to be queried by the SQL query statement, including: obtaining one or any combination of the following information of the table: the number of data rows, the size of each column of data, the proportion of null values, the number of distinct values, the minimum value of each column, and the maximum value of each column.

[0122] In an optional embodiment, the feature collection module 31 obtains the status characteristics of each computing engine, including: obtaining the total number of CPUs, the number of available CPUs, the total number of memories, and the number of available memories of each computing engine; or, obtaining the total number of CPUs, the number of available CPUs, the total number of memories, the number of available memories, and at least one of the following parameters: online status, disk IO value, network IO value.

[0123] In an optional embodiment, the feature collection module 31 obtains the status features of each computing engine, further comprising: obtaining the status features of each computing engine at a plurality of preset time points after the same time point as the submission time point of the SQL query statement on the most recent consecutive preset days.

[0124] In an optional embodiment, the above-mentioned device further includes: a training module, which is used to collect multiple SQL query statements that have been executed; for each SQL query statement, a feature vector is constructed respectively, and each feature vector includes: the SQL feature of the corresponding SQL query statement, the data feature of the table queried by the corresponding SQL query statement, and the state feature of the computing engine that executes the corresponding SQL query statement; the actual resource consumption and actual time consumption of the computing engine when executing each SQL query statement are obtained; each feature vector is input into the resource consumption prediction model to be trained in turn, and the predicted resource consumption output by the resource consumption prediction model is compared with the actual resource consumption corresponding to the input feature vector, and the resource consumption prediction model parameters are updated according to the comparison result until the resource consumption prediction model converges; each feature vector is input into the time consumption prediction model to be trained in turn, and the predicted time consumption output by the time consumption prediction model is compared with the actual time consumption corresponding to the input feature vector, and the time consumption prediction model parameters are updated according to the comparison result until the time consumption prediction model converges.

[0125] An embodiment of the present invention further provides a non-transitory computer-readable storage medium, which stores instructions. When the instructions are executed by a processor, the processor executes the steps of the computing engine selection method described in any of the above embodiments.

[0126] Figure 4 This is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. The electronic device includes the above-mentioned non-transitory computer-readable storage medium 41 and a processor 42 capable of accessing the non-transitory computer-readable storage medium 41.

[0127] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A computing engine selection method, characterized in that: The method includes: Obtaining SQL features of a structured query language SQL query statement to be executed, wherein the SQL features include: a table name to be queried by the SQL query statement, grammatical features contained therein, and feature values ​​involved in each grammatical feature; Obtain data features of the table to be queried by the SQL query statement, wherein the data features of the table include one or any combination of the following information: number of data rows, size of each column of data, ratio of null values, number of distinct values, minimum value of each column, and maximum value of each column; Obtaining status characteristics of each computing engine, wherein the status characteristics of each computing engine include: the total number of CPUs, the number of available CPUs, the total number of memories, and the number of available memories of each computing engine, or include: the total number of CPUs, the number of available CPUs, the total number of memories, the number of available memories of each computing engine, and at least one of the following parameters: online status, disk IO value, and network IO value; For each computing engine, a feature vector is generated respectively, each feature vector including: the SQL feature of the SQL query statement, the data feature of the table to be queried by the SQL query statement, and the state feature of the corresponding computing engine; Input each feature vector into the resource consumption prediction model for calculation, and obtain the predicted resource consumption of each computing engine executing the SQL query statement; Input each feature vector into the time consumption prediction model for calculation, and obtain the predicted time consumption of each computing engine to execute the SQL query statement; Calculate the predicted cost of each computing engine executing the SQL query statement according to the predicted resource consumption of each computing engine executing the SQL query statement and the predicted time consumed by each computing engine executing the SQL query statement; A computing engine for executing the SQL query statement is selected according to the predicted cost of each computing engine executing the SQL query statement.

2. The method according to claim 1, characterized in that The step of obtaining the grammatical features contained in the SQL query statement and extracting the feature values ​​involved in each grammatical feature and putting them into the SQL feature includes: Determine whether the SQL query statement contains the SELECT * syntax feature, and if so, add the SELECT syntax feature flag to the SQL feature; or / and, Extract the number and type of columns in the project column of the query, and put the number and type of columns in the project column into the SQL feature; or / and, Determine whether the SQL query statement contains the limit syntax feature, if so, extract the limit value, and put the limit syntax feature flag and the limit value into the SQL feature; or / and, Determine whether the SQL query statement contains a filter condition, if so, extract the filter expression, and put the filter condition flag and the filter expression into the SQL feature; or / and, Determine whether the SQL query statement contains a union syntax feature, and if so, put the union syntax feature flag into the SQL feature; or / and, Determine whether the SQL query statement has an aggregate function, and if so, extract the aggregate function type and aggregate column, and put the aggregate function flag, the aggregate function type and the aggregate column into the SQL feature; or / and, Determine whether the SQL query statement contains a groupby syntax feature. If so, extract the groupby grouping column and put the groupby syntax feature flag and the groupby grouping column into the SQL feature; or / and, Determine whether the SQL query statement contains the orderby syntax feature, if so, extract the sorting column, and put the orderby syntax feature flag and the sorting column into the SQL feature; or / and, Determine whether the SQL query statement contains a join syntax feature, if so, extract the join type, and put the join syntax feature flag and the join type into the SQL feature; or / and, It is determined whether the SQL query statement contains a subquery. If so, a subquery flag is placed in the SQL feature.

3. The method according to claim 1, characterized in that The obtaining of the status characteristics of each computing engine further includes: The state characteristics of each computing engine at a plurality of preset time points after the same time point as the submission time point of the SQL query statement on the most recent consecutive preset days are obtained.

4. The method according to claim 1, characterized in that: The resource consumption prediction model and the time consumption prediction model are obtained through the following training process: Collect multiple SQL query statements that have been executed; For each SQL query statement, a feature vector is constructed respectively, each feature vector including: the SQL feature of the corresponding SQL query statement, the data feature of the table queried by the corresponding SQL query statement, and the state feature of the computing engine executing the corresponding SQL query statement; Get the actual resource consumption and actual time consumed by the computing engine when executing each SQL query statement; Input each feature vector into the resource consumption prediction model to be trained in turn, and compare the predicted resource consumption output by the resource consumption prediction model with the actual resource consumption corresponding to the input feature vector, and update the resource consumption prediction model parameters according to the comparison result until the resource consumption prediction model converges; Each feature vector is input into the time consumption prediction model to be trained in turn, and the predicted time consumption output by the time consumption prediction model is compared with the actual time consumption corresponding to the input feature vector, and the time consumption prediction model parameters are updated according to the comparison result until the time consumption prediction model converges.

5. A computing engine selection device, characterized in that: The device includes: A feature collection module is used to obtain SQL features of a structured query language SQL query statement to be executed; obtain data features of a table to be queried by the SQL query statement; obtain state features of each computing engine; generate a feature vector for each computing engine, each feature vector containing: SQL features of the SQL query statement, data features of the table to be queried by the SQL query statement, and state features of the corresponding computing engine; the SQL features include: the name of the table to be queried by the SQL query statement, the included grammatical features, and the feature values ​​involved in each grammatical feature; the data features of the table include one or any combination of the following information: the number of data rows, the size of each column of data, the proportion of null values, the number of distinct values, the minimum value of each column, and the maximum value of each column; the state features of each computing engine include: the total number of CPUs, the number of available CPUs, the total number of memories, and the number of available memories of each computing engine, or include: the total number of CPUs, the number of available CPUs, the total number of memories, the number of available memories, and at least one of the following parameters: online status, disk IO value, network IO value; A prediction module is used to input each feature vector into a resource consumption prediction model for calculation, and obtain the predicted resource consumption of each computing engine executing the SQL query statement; input each feature vector into a time consumption prediction model for calculation, and obtain the predicted time consumption of each computing engine executing the SQL query statement; A selection module is used to calculate the predicted cost of each computing engine executing the SQL query statement based on the predicted resource consumption of each computing engine executing the SQL query statement and the predicted time consumed by each computing engine to execute the SQL query statement; and select a computing engine to execute the SQL query statement based on the predicted cost of each computing engine executing the SQL query statement.

6. A non-transitory computer-readable storage medium storing instructions, characterized in that: When the instructions are executed by a processor, the processor is caused to perform the steps of the computing engine selection method according to any one of claims 1 to 4.

7. An electronic device, characterized in that: The method comprises the non-transitory computer-readable storage medium of claim 6, and the processor having access to the non-transitory computer-readable storage medium.

Images