Method and device for grouping SQL tasks and storage medium

Abstract

The invention relates to the technical field of big data, and discloses a method and device for grouping SQL tasks and a storage medium, the method comprises the following steps: obtaining a plurality of SQL tasks based on operation log data of a database, each SQL task carrying resource consumption information and time window information, each SQL task is grouped based on the resource consumption information and the time window information to obtain a plurality of groups, each SQL task included in the same group is merged to obtain a plurality of merged SQL task bars, the number of the task bars is the same as that of the groups, processing items are established for each merged SQL task bar, and each processing item is used for processing each SQL task bar. According to the scheme, a large number of SQL tasks are intelligently grouped and merged, the resource consumption balance and the time window consistency are optimized, the number of the processed items is reduced, and the resource utilization efficiency is improved.

Description

Technical Field

[0001] This application relates to the field of big data technology, and provides a method, an apparatus, and a storage medium for grouping SQL tasks. Background Art

[0002] Currently, in the field of big data, the number of Structured Query Language (SQL) tasks involved in business has increased sharply. In some data processing operations, the number of SQL tasks may reach thousands. In the existing processing, each SQL task generates a separate Flink processing item, and then submits the Flink processing item to a server cluster for processing. The generation of the above-mentioned large number of Flink processing items increases the difficulty of operation and maintenance management. At the same time, a large number of Flink processing items also occupy more computing resources (for example, Central Processing Unit (CPU), memory, Input / Output (I / O)), resulting in a large waste of computing resources.

[0003] In addition, the window mechanisms of SQL tasks are complex and diverse (for example, sliding windows, rolling windows, etc.), so the grouping efficiency of SQL tasks is low, and it is difficult to balance different business requirements. Moreover, each time a Flink processing item is established, all SQL tasks need to be read in full. In the process of reading and processing SQL tasks multiple times as described above, not only does it increase the I / O overhead, but it also brings an additional burden to computing resources and storage resources, thus affecting the overall performance of the system. Summary of the Invention

[0004] Embodiments of this application provide a method, an apparatus, and a storage medium for grouping SQL tasks, which are used to intelligently group and merge SQL tasks, reduce the number of processing items, and improve the resource utilization efficiency.

[0005] The specific technical solutions provided by this application are as follows:

[0006] In a first aspect, an embodiment of this application provides a method for grouping SQL tasks, including:

[0007] Obtain multiple SQL tasks based on the operation log data of a database, where each SQL task carries resource consumption information and time window information;

[0008] Group each SQL task based on the resource consumption information and the time window information to obtain multiple groups, and merge each SQL task included in the same group to obtain multiple merged SQL task items, where the number of task items is the same as the number of groups;

[0009] Create processing items for each merged SQL task bar, and submit each processing item to the corresponding server cluster for processing.

[0010] Optionally, obtain multiple SQL tasks based on the running log data of the database, including:

[0011] Establish Flink cdc, and use Flink cdc to monitor in real time whether the running log data of the database has changed;

[0012] If it is monitored that the running log data has changed, extract multiple SQL tasks from the changed running log data;

[0013] Parse multiple SQL tasks respectively, and extract the resource consumption information and time window information carried by the SQL tasks from the parsing results.

[0014] Optionally, group each SQL task based on the resource consumption information and time window information to obtain multiple groups, including:

[0015] Randomly generate multiple initial solutions for each SQL task. Among them, each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number represents that the SQL tasks in the corresponding position belong to the same group, and different numbers represent that the SQL tasks in the corresponding position belong to different groups. The time window information of the SQL tasks in the same group represents the same window type, and i is a natural number;

[0016] Input the multiple initial solutions randomly generated based on each SQL task into the model corresponding to the NSGA-II algorithm, and enable the model to screen the multiple initial solutions in an iterative manner;

[0017] In each round of iteration, judge whether the initial solution screened out in this round meets the convergence condition. When the convergence condition is met, determine the multiple groups obtained after grouping each SQL task according to the screened initial solution; among them, the convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of screened initial solutions is one. The process of each round of iteration is as follows:

[0018] Based on the resource consumption information and time window information, determine the rank sorting result and crowding degree value of the multiple initial solutions before this round of iteration respectively;

[0019] Determine the parent solutions from each initial solution based on the rank sorting result and crowding degree value, and perform crossover and mutation on the parent solutions to obtain multiple offspring solutions;

[0020] Merge the parent solutions and offspring solutions to obtain multiple extended initial solutions, and screen out multiple initial solutions from the extended initial solutions based on the resource consumption information and time window information.

[0021] Optionally, based on the resource consumption information and the time window information, respectively determine the rank sorting result and the crowding degree value of multiple initial solutions before this iteration, including:

[0022] Divide each SQL task into k groups represented by the initial solution based on any one of the initial solutions before this iteration, where k is a positive integer;

[0023] Calculate the variance between the resource consumption information of each SQL task in each of the k groups and the resource consumption information of the remaining SQL tasks in each group, so as to obtain multiple variance values;

[0024] Calculate the absolute value of the difference between the time window information of each SQL task in each of the k groups and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values;

[0025] Determine the rank sorting result based on the variance value and the window value of each of the k groups, and respectively determine the crowding degree value of multiple groups of SQL tasks belonging to the same rank sorting result.

[0026] Optionally, determine the parent solutions from each initial solution based on the rank sorting result and the crowding degree value, including:

[0027] Calculate the comprehensive value of each of the k groups based on the preset sorting weight, the rank sorting result, the preset crowding degree weight, and the crowding degree value;

[0028] Select h groups with high comprehensive values from the k groups, and determine the initial solutions corresponding to the h groups as the parent solutions, where h is a positive integer less than k.

[0029] Optionally, perform crossover and mutation on the parent solutions to obtain multiple offspring solutions, including:

[0030] Perform crossover on the values at any position in the parent solutions through the simulated binary crossover algorithm to obtain multiple crossover parent solutions;

[0031] Randomly change the values at any position in the multiple crossover parent solutions to obtain multiple offspring solutions.

[0032] Optionally, merge the parent solutions and the offspring solutions to obtain multiple extended initial solutions, and screen out multiple initial solutions from the extended initial solutions based on the resource consumption information and the time window information, including:

[0033] Take the union of the parent solutions and the offspring solutions to obtain multiple extended initial solutions;

[0034] Divide each SQL task into q groups represented by the initial solution based on the multiple extended initial solutions, where q is a positive integer;

[0035] Calculate the variance between the resource consumption information of each SQL task in each of the q groups and the resource consumption information of the remaining SQL tasks in the same group, thereby obtaining multiple variance values;

[0036] Calculate the absolute value of the difference between the time window information of each SQL task in each of the q groups and the time window information of the remaining SQL tasks in the same group, thereby obtaining multiple window values;

[0037] Determine the rank sorting result based on the variance values and window values of each of the q groups, and respectively determine the congestion values of the q groups of SQL tasks belonging to the same rank sorting result;

[0038] Calculate the comprehensive value of each of the q groups based on the preset sorting weight, rank sorting result, preset congestion weight, and congestion value respectively;

[0039] Select f groups with high comprehensive values from the q groups, and determine the corresponding expanded initial solutions of the f groups as the selected multiple initial solutions, where f is a positive integer less than q.

[0040] In a second aspect, an embodiment of the present application further provides a device for grouping SQL tasks, including:

[0041] An acquisition unit, configured to acquire multiple SQL tasks based on the operation log data of the database, where each SQL task carries resource consumption information and time window information;

[0042] A grouping unit, configured to group each SQL task based on the resource consumption information and time window information to obtain multiple groups, and merge each SQL task included in the same group to obtain multiple merged SQL task items, where the number of task items is the same as the number of groups;

[0043] A submission unit, configured to establish processing matters for each of the merged SQL task items and submit each processing matter to the corresponding server cluster for processing.

[0044] Optionally, when acquiring multiple SQL tasks based on the operation log data of the database, the acquisition unit is configured to:

[0045] Establish Flink cdc, and use Flink cdc to monitor in real time whether the operation log data of the database has changed;

[0046] If it is monitored that the operation log data has changed, extract multiple SQL tasks from the changed operation log data;

[0047] Parse multiple SQL tasks respectively, and extract the resource consumption information and time window information carried by the SQL tasks from the parsing results.

[0048] Optionally, group each SQL task based on the resource consumption information and time window information to obtain multiple groups. The grouping unit is used for:

[0049] Randomly generate multiple initial solutions for each SQL task. Among them, each initial solution includes multiple digits at the i-th position. Each digit represents the i-th SQL task. The same digit represents that the SQL tasks at the corresponding position belong to the same group, and different digits represent that the SQL tasks at the corresponding position belong to different groups. The window types represented by the time window information of the SQL tasks in the same group are the same, and i is a natural number;

[0050] Input the multiple initial solutions randomly generated based on each SQL task into the model corresponding to the NSGA-II algorithm, and enable the model to screen the multiple initial solutions in an iterative manner;

[0051] During each round of iteration, determine whether the initial solutions screened out in this round meet the convergence condition. When the convergence condition is met, determine the multiple groups obtained after grouping each SQL task according to the screened initial solutions; among them, the convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of screened initial solutions is one. The process of each round of iteration is as follows:

[0052] Based on the resource consumption information and time window information, respectively determine the rank sorting results and crowding degree values of the multiple initial solutions before this round of iteration;

[0053] Determine the parent solutions from each initial solution based on the rank sorting results and crowding degree values, and perform crossover and mutation on the parent solutions to obtain multiple offspring solutions;

[0054] Combine the parent solutions and offspring solutions to obtain multiple expanded initial solutions, and screen out multiple initial solutions from the expanded initial solutions based on the resource consumption information and time window information.

[0055] Optionally, based on the resource consumption information and time window information, respectively determine the rank sorting results and crowding degree values of the multiple initial solutions before this round of iteration. The grouping unit is used for:

[0056] Divide each SQL task into k groups represented by the initial solution based on any one initial solution before this round of iteration, where k is a positive integer;

[0057] Calculate the variance of the resource consumption information of each SQL task in each of the k groups with the resource consumption information of the remaining SQL tasks in each of the k groups respectively, so as to obtain multiple variance values;

[0058] Calculate the absolute value of the difference between the time window information of each SQL task in each of the k groups and the time window information of the remaining SQL tasks in the same group, so as to obtain multiple window values;

[0059] Determine the rank sorting result based on the variance value and window value of each of the k groups, and respectively determine the congestion degree values of multiple groups of SQL tasks belonging to the same rank sorting result.

[0060] Optionally, determine the parent solutions from the respective initial solutions based on the rank sorting result and the congestion degree value. The grouping unit is used for:

[0061] Calculate the comprehensive value of each of the k groups respectively based on the preset sorting weight, the rank sorting result, the preset congestion degree weight, and the congestion degree value;

[0062] Select h groups with high comprehensive values from the k groups, and determine the initial solutions corresponding to the h groups as the parent solutions, where h is a positive integer less than k.

[0063] Optionally, perform crossover and mutation on the parent solutions to obtain multiple offspring solutions. The grouping unit is used for:

[0064] Perform crossover on the values at any position in the parent solutions through the simulated binary crossover algorithm to obtain multiple crossover parent solutions;

[0065] Randomly change the values at any position in the multiple crossover parent solutions to obtain multiple offspring solutions.

[0066] Optionally, merge the parent solutions and the offspring solutions to obtain multiple extended initial solutions, and screen out multiple initial solutions from the extended initial solutions based on the resource consumption information and the time window information. The grouping unit is used for:

[0067] Take the union of the parent solutions and the offspring solutions to obtain multiple extended initial solutions;

[0068] Divide each SQL task into q groups represented by the initial solutions based on the multiple extended initial solutions, where q is a positive integer;

[0069] Calculate the variance of the resource consumption information of each SQL task in each of the q groups and the resource consumption information of the remaining SQL tasks in the same group respectively, so as to obtain multiple variance values;

[0070] Calculate the absolute value of the difference between the time window information of each SQL task in each of the q groups and the time window information of the remaining SQL tasks in the same group respectively, so as to obtain multiple window values;

[0071] Determine the rank sorting result based on the variance value and window value of each of the q groups, and respectively determine the congestion degree values of the q groups of SQL tasks belonging to the same rank sorting result;

[0072] Calculate the comprehensive value of each group in q groups respectively based on the preset sorting weight, hierarchical sorting result, preset congestion weight, and congestion value;

[0073] Select f groups with high comprehensive values from the q groups, and determine the expanded initial solutions corresponding to the f groups as the selected multiple initial solutions, where f is a positive integer less than q.

[0074] In a third aspect, a server includes:

[0075] A memory for storing executable instructions;

[0076] A processor for reading and executing the executable instructions stored in the memory to implement the method according to any one of the first aspects.

[0077] In a fourth aspect, a computer-readable storage medium, when the instructions in the storage medium are executed by a processor, enable the processor to execute the method according to any one of the first aspects.

[0078] The beneficial effects of this application are as follows:

[0079] In summary, in the embodiments of this application, a method, device, and storage medium for grouping SQL tasks are provided. The method includes: obtaining multiple SQL tasks based on the operation log data of a database, where each SQL task carries resource consumption information and time window information, grouping each SQL task based on the resource consumption information and time window information to obtain multiple groups, and merging each SQL task included in the same group to obtain multiple merged SQL task items, where the number of task items is the same as the number of groups, creating processing matters for each merged SQL task item, and submitting each processing matter to the corresponding server cluster for processing. The above solution intelligently groups and merges a large number of SQL tasks, optimizes the resource consumption balance and time window consistency, reduces the number of processing matters, improves the resource utilization efficiency, and reduces the complexity of system operation and maintenance.

[0080] Other features and advantages of this application will be described in the subsequent description, and, in part, will become apparent from the description or be understood by implementing this application. The objectives and other advantages of this application can be realized and obtained by the structures specifically pointed out in the written description, claims, and drawings. Description of the Drawings

[0081] The drawings described herein are used to provide a further understanding of this application, and constitute a part of this application. The illustrative embodiments and descriptions of this application are used to explain this application and do not constitute an improper limitation of this application. In the drawings:

[0082] Figure 1 It is a schematic diagram of the system architecture for grouping SQL tasks in the embodiments of this application;

[0083] Figure 2 It is a schematic diagram of the process for grouping SQL tasks in the embodiments of this application;

[0084] Figure 3 It is a schematic diagram of the process for obtaining multiple SQL tasks based on the operation log data of the database in the embodiments of this application;

[0085] Figure 4 It is a schematic diagram of the process for grouping each SQL task based on resource consumption information and time window information to obtain multiple groups in the embodiments of this application;

[0086] Figure 5 It is a schematic diagram of the process for each iteration in the embodiments of this application;

[0087] Figure 6 It is a schematic diagram of the process for respectively determining the rank sorting result and crowding degree value of multiple initial solutions before this iteration based on resource consumption information and time window information in the embodiments of this application;

[0088] Figure 7 It is a schematic diagram of the process for determining the parental solutions from each initial solution based on the rank sorting result and crowding degree value in the embodiments of this application;

[0089] Figure 8 It is a schematic diagram of the process for performing crossover and mutation on the parental solutions to obtain multiple offspring solutions in the embodiments of this application;

[0090] Figure 9 It is a schematic diagram of the process for screening out multiple initial solutions according to the parental solutions and offspring solutions in the embodiments of this application;

[0091] Figure 10 It is a schematic diagram of the logical architecture of a device for grouping SQL tasks in the embodiments of this application;

[0092] Figure 11 It is a schematic diagram of the entity architecture of a server in the embodiments of this application. Detailed implementation manners

[0093] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the following will clearly and completely describe the technical solutions of this application in conjunction with the accompanying drawings in the embodiments of this application. Obviously, the described embodiments are part of the technical solutions of this application, rather than all of them. Based on the embodiments recorded in this application document, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope protected by the technical solutions of this application.

[0094] In the description and claims of the present invention and the above accompanying drawings, terms such as "first" and "second" are used to distinguish similar objects and do not necessarily need to describe a specific order or sequence. It should be understood that such data can be interchanged under appropriate circumstances so that the embodiments of the present invention described here can be implemented in an order other than those illustrated or described here.

[0095] First, the following noun explanations are provided:

[0096] (1) Multi-objective optimization algorithm (Non-dominated Sorting Genetic Algorithm II, NSGA-II): A classic multi-objective optimization algorithm used to solve optimization problems with two or more conflicting objectives. It is an improved version of the NSGA algorithm, with higher computational efficiency and the ability to solve multi-objective problems.

[0097] (2) Data change capture (Flink Change Data Capture, Flink cdc) is an open-source framework built on Apache Flink for capturing changed data in a database. It combines real-time data stream processing with the changed data in the database (such as insert, update, and delete operations), enabling efficient capture and processing of these data changes.

[0098] (3) Structured Query Language (SQL) is a standard programming language for managing and operating relational databases. It is used to perform various operations on a database, such as data query, insert, update, and delete. SQL is the main tool for defining, operating, and controlling data in a relational database management system (RDBMS).

[0099] The following will provide a detailed description of the preferred embodiments of this application in conjunction with the accompanying drawings.

[0100] Refer to Figure 1 As shown, in the embodiments of this application, the system includes multiple servers and at least one server cluster. In Figure 1Among them, servers 1, 2, and n are used to exemplarily represent the servers that run to group SQL tasks. Assume that processing service 1 runs on server 1. Correspondingly, server 1 groups the SQL tasks in processing service 1. Processing service 2 runs on server 2. Correspondingly, server 2 groups the SQL tasks in processing service 2. Processing service n runs on server n. Correspondingly, server n groups the SQL tasks in processing service n. Server cluster A is used to exemplarily represent the server cluster that interacts with the above-mentioned various servers, that is, server cluster A executes the processing matters established for the grouped and merged SQL tasks.

[0101] In the embodiments of the present application, the implementation of a method for grouping SQL tasks is mainly executed on the server side, and the following is a specific introduction.

[0102] Refer to Figure 2 As shown, in the embodiments of the present application, a specific process for grouping SQL tasks is as follows:

[0103] Step 201: Obtain multiple SQL tasks based on the operation log data of the database. Among them, each SQL task carries resource consumption information and time window information.

[0104] When the processing service on the server runs, the data in the database corresponding to the processing service will change in real time as the processing service runs. In order to comprehensively monitor the operation of the processing service, in the embodiments of the present application, the operation log data of the database will be obtained, and SQL tasks will be further obtained from the above operation log data.

[0105] The above-mentioned obtaining multiple SQL tasks based on the operation log data of the database, refer to Figure 3 As shown, specifically includes:

[0106] Step 2011: Establish Flink cdc, and use Flink cdc to listen in real time whether the operation log data of the database has changed.

[0107] In order to obtain the operation log data of the database of the processing service, during the implementation process, a Flink cdc is first established for the above-mentioned processing service. When the processing service starts to run, the above Flink cdc will first obtain all the data stored in the database, that is, the initial data in the operation log data. As the execution of the operation process of the processing service progresses, at least a part of the data in the above database will change. Based on this, the server will use Flink cdc to listen in real time whether the operation log data of the database has changed. Exemplarily, deploy Flink cdc11 to listen to the data of mysql binlog11.

[0108] It should be noted here that before the processing service starts running, multiple SQL tasks will be pre-configured for the processing service. Correspondingly, resource consumption information and time window information will also be configured for each of these SQL tasks, that is, the resource consumption and time window required for each SQL task during the processing will be estimated in advance, and the corresponding resource consumption information and time window information will be pre-configured for each SQL task to ensure the execution of each SQL task. The above-mentioned resource consumption information specifically includes CPU, memory, I / O, etc. required for the execution of the SQL task. The above time window information first includes which type of the sliding window, rolling window, cumulative window, and session window the SQL task corresponds to. In addition, the above time window information also includes the corresponding window length, etc.

[0109] Step 2012: If it is monitored that the running log data has changed, extract multiple SQL tasks from the changed running log data.

[0110] If the running log data has changed, it means that a certain SQL task in the processing service has been processed. If the running log data has not changed, it means that a certain SQL task has not been executed yet. Based on this, during the implementation process, if it is monitored through Flink cdc that the running log data has changed, the changed running log data will be captured through Flink cdc, and then multiple SQL tasks will be extracted from the changed running log data.

[0111] Step 2013: Parse each of the multiple SQL tasks respectively, and extract the resource consumption information and time window information carried by the SQL tasks from the parsing results.

[0112] Considering that the basis for grouping SQL tasks later is the resource consumption information and time window information, during the implementation process, after extracting multiple SQL tasks, the following operations will be performed for each SQL task: Parse the SQL task, determine the pre-configured resource consumption information and time window information in the SQL task through parsing, and extract the resource consumption information and time window information carried by the SQL task from the parsing results.

[0113] Example 1, assuming that the resource consumption information is full for CPU resources at 10 and the types of time window information are all sliding windows, 5 SQL tasks are obtained through Flink cdc and are marked in the form of Table 1 as follows:

[0114] Table 1

[0115] SQL_ID Resource consumption information (s) Time window information (s / per) SQL001 3 60 SQL002 8 150 SQL003 10 300 SQL004 6 90 SQL005 7 180

[0116] Step 202: Group each SQL task based on the resource consumption information and the time window information to obtain multiple groups, and merge each SQL task included in the same group to obtain multiple merged SQL task items, where the number of task items is the same as the number of groups.

[0117] In the embodiment of the present application, in order to improve the resource utilization efficiency of processing services, after extracting multiple SQL tasks, each SQL task is further grouped according to the resource consumption information and the time window information, so as to obtain multiple groups. Different from the related art where each SQL task is processed one by one, in the embodiment of the present application, multiple SQL tasks belonging to the same group will be merged into one SQL task item, so as to obtain multiple task items with the same number as the number of groups. The above one task item is the smallest processing unit in processing services.

[0118] The above-mentioned grouping of each SQL task based on the resource consumption information and the time window information to obtain multiple groups, refer to Figure 4 shown, including:

[0119] Step 2021: Randomly generate multiple initial solutions for each SQL task, where each initial solution includes multiple numbers at the i-th position. Each number represents the i-th SQL task. The same number represents that the SQL tasks corresponding to the positions belong to the same group, and different numbers represent that the SQL tasks corresponding to the positions belong to different groups. The window types represented by the time window information of the SQL tasks in the same group are the same, and i is a natural number.

[0120] In the embodiment of the present application, it is first assumed that each SQL task is randomly divided into any number of groups, that is, multiple initial solutions are randomly generated for each SQL task. The above one initial solution represents a situation of grouping the above SQL tasks.

[0121] It should be noted that each initial solution includes multiple numbers. The number at each position among the above multiple numbers represents the number at the i-th position. Each number represents the i-th SQL task.

[0122] Combined with the above Example 1 for illustration, the 1 at the first position in the initial solution 1[1,1,2,2,1] represents the first SQL task SQL001, the 1 at the second position represents the second SQL task SQL002, the 2 at the third position represents the third SQL task SQL003, the 2 at the fourth position represents the fourth SQL task SQL004, and the 1 at the fifth position represents the fifth SQL task SQL005.

[0123] In addition, the same number represents that the SQL tasks corresponding to the positions belong to the same group, and different numbers represent that the SQL tasks corresponding to the positions belong to different groups.

[0124] Combined with the above Example 1 for explanation, the initial solution 1 [1, 1, 2, 2, 1] means that the numbers corresponding to the above SQL tasks SQL001, SQL task SQL002, and SQL task SQL005 are all the number 1 in the initial solution 1, which means that SQL tasks SQL001, SQL002, and SQL005 all belong to group 1. The initial solution 1 [1, 1, 2, 2, 1] means that the numbers corresponding to the above SQL tasks SQL003 and SQL task SQL004 are all the number 2 in the initial solution 1, which means that SQL tasks SQL003 and SQL004 all belong to group 2.

[0125] It should also be noted that the number of initial solutions generated by each SQL task is randomly multiple. Continuing with the above Example 1 for explanation, when the initial solution 2 is [2, 2, 1, 1, 2], it means that the numbers corresponding to the above SQL tasks SQL001, SQL task SQL002, and SQL task SQL005 are all the number 2 in the initial solution 2, which means that SQL tasks SQL001, SQL002, and SQL005 all belong to group 2. The initial solution 2 [2, 2, 1, 1, 2] means that the numbers corresponding to the above SQL tasks SQL003 and SQL task SQL004 are all the number 1 in the initial solution 2, which means that SQL tasks SQL003 and SQL004 all belong to group 1.

[0126] Similarly, continuing with the above Example 1 for explanation, when the initial solution 3 is [1, 2, 1, 2, 1], it means that the numbers corresponding to the above SQL tasks SQL001, SQL task SQL003, and SQL task SQL005 are all the number 1 in the initial solution 3, which means that SQL tasks SQL001, SQL003, and SQL005 all belong to group 1. The initial solution 3 [2, 2, 1, 1, 2] means that the numbers corresponding to the above SQL tasks SQL002 and SQL task SQL004 are all the number 2 in the initial solution 3, which means that SQL tasks SQL002 and SQL004 all belong to group 2.

[0127] Similarly, continuing with the above Example 1 for explanation, when the initial solution 4 is [2, 1, 2, 1, 2], it means that the numbers corresponding to the above SQL tasks SQL001, SQL task SQL003, and SQL task SQL005 are all the number 2 in the initial solution 4, which means that SQL tasks SQL001, SQL003, and SQL005 all belong to group 2. The initial solution 4 [2, 2, 1, 1, 2] means that the numbers corresponding to the above SQL tasks SQL002 and SQL task SQL004 are all the number 1 in the initial solution 4, which means that SQL tasks SQL002 and SQL004 all belong to group 1.

[0128] In addition, it should be noted that the window types represented by the time window information of SQL tasks in the same group are the same. That is, when the window types represented by the time window information of two SQL tasks are different, the groups to which the SQL tasks are assigned will be different.

[0129] Example 2. Assume that the resource consumption information is 10 when the CPU resource is fully utilized, and the types of time window information include sliding window and rolling window. Five SQL tasks are obtained through Flink cdc and are labeled in the form of Table 2 as follows:

[0130] Table 2

[0131]

[0132]

[0133] Taking the above Example 2 as an illustration, the initial solution 5[1, 2, 3, 2, 1] means that the numbers corresponding to the above SQL task SQL001 and SQL task SQL005 are both the number 1 in the initial solution 5, which means that SQL task SQL001 and SQL005 both belong to Group 1. The initial solution 5[1, 2, 3, 2, 1] means that the numbers corresponding to the above SQL task SQL002 and SQL task SQL004 are both the number 2 in the initial solution 5, which means that SQL task SQL002 and SQL004 both belong to Group 2. The initial solution 5[1, 2, 3, 2, 1] means that the number corresponding to the above SQL task SQL003 is the number 3 in the initial solution 5, which means that SQL task SQL003 belongs to Group 3.

[0134] Step 2022: Input multiple initial solutions randomly generated based on each SQL task into the model corresponding to the NSGA-II algorithm, and enable the model to iteratively screen the multiple initial solutions.

[0135] During the implementation process, after randomly generating multiple initial solutions for each SQL task, input the above multiple initial solutions into the model corresponding to the NSGA-II algorithm, and enable the model to iteratively screen the multiple initial solutions in multiple rounds.

[0136] It should be noted that the model corresponding to the above NSGA-II algorithm is pre-trained according to historical solutions. Exemplarily, M historical solutions are used as input parameters and input into the initial model corresponding to the NSGA-II algorithm, and the above initial model is continuously trained until the output parameters of the above initial model are determined to be the maximum polling times (or, a historical solution). At this point, the configuration parameters of the above initial model are determined, and the initial model with determined configuration parameters is determined as the model corresponding to the NSGA-II algorithm, and this model corresponding to the NSGA-II algorithm is used to screen multiple initial solutions.

[0137] Step 2023: During each round of iteration, it is judged whether the initial solutions screened out in this round meet the convergence condition. When the convergence condition is met, multiple groups obtained by grouping each SQL task according to the screened-out initial solutions are determined. The convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of screened-out initial solutions is one. Refer to Figure 5 As shown, the process of each round of iteration is as follows:

[0138] Since the number of initial solutions is multiple, during the process of screening multiple initial solutions using the model corresponding to the NSGA-II algorithm, multiple rounds of iteration are required to make the screened-out initial solutions meet the convergence condition. Exemplarily, Condition 1: After multiple rounds of iteration, the number of iterations is equal to the preset maximum number of iterations, or, Condition 2: After multiple rounds of iteration, the number of screened-out initial solutions is one. That is, when any one of the above Condition 1 or Condition 2 is met, at least one initial solution is screened out from multiple initial solutions.

[0139] Obviously, the number of at least one initial solution here is less than the above multiple. After screening out at least one initial solution, multiple groups obtained by grouping each SQL task according to at least one initial solution are determined. The specific grouping situation here will not be elaborated one by one.

[0140] The execution steps in each round of iteration are specifically introduced below, that is, Step 20231 to Step 20233. The number of rounds of repeated execution of Step 20231 to Step 20233 here needs to be set according to the actual usage scenario.

[0141] Step 20231: Based on the resource consumption information and time window information, respectively determine the rank sorting result and crowding degree value of multiple initial solutions before this round of iteration.

[0142] During the implementation process, after inputting multiple initial solutions randomly generated by each SQL task into the model corresponding to the NSGA-II algorithm, first determine the rank sorting result of the above multiple initial solutions according to the resource consumption information of each SQL task, that is, determine the rank sorting result of multiple initial solutions before this round of iteration.

[0143] Refer to Figure 6 As shown, based on the resource consumption information and time window information, respectively determine the ranking result and crowding degree value of multiple initial solutions before this round of iteration, including:

[0144] Step 202311: Divide each SQL task into k groups represented by the initial solution based on any one of the initial solutions before this round of iteration, where k is a positive integer.

[0145] Since the number of initial solutions may change after each round of iteration, during the implementation process, first obtain multiple initial solutions before this round of iteration, that is, multiple initial solutions input into the model corresponding to the NSGA-II algorithm during this round of iteration. After obtaining the above multiple initial solutions, divide each SQL task included in each initial solution into k groups represented by the initial solution.

[0146] The process of dividing the SQL tasks into k groups is similar to the description above. Still taking the initial solution 1 [1, 1, 2, 2, 1] as an example, it means that the numbers corresponding to the above SQL tasks SQL001, SQL task SQL002, and SQL task SQL005 in the initial solution 1 are all the number 1 in the initial solution 1, that is, it means that SQL tasks SQL001, SQL002, and SQL005 all belong to group 1, and divide SQL001, SQL002, and SQL005 into group 1. The initial solution 1 [1, 1, 2, 2, 1] means that the numbers corresponding to the above SQL task SQL003 and SQL task SQL004 in the initial solution 1 are all the number 2 in the initial solution 1, that is, it means that SQL tasks SQL003 and SQL004 both belong to group 2, and divide SQL003 and SQL004 into group 2, that is, the value of the above k is 2.

[0147] Step 202312: Calculate the variance of the resource consumption information of each SQL task in each of the k groups with the resource consumption information of the remaining SQL tasks in each of the k groups, so as to obtain multiple variance values.

[0148] Taking the multiple randomly generated initial solutions including the initial solution 1 [1, 1, 2, 2, 1] as an example for illustration. By to calculate the variance of the resource consumption corresponding to this initial solution, where S in formula (1) i represents each SQL task included in the above initial solution, and R(S i ) represents the resource consumption information of the SQL task, and Var(R(S i )) represents the variance value of the resource consumption belonging to the same group in the initial solution.

[0149] In the specific implementation process, the initial solution 1 [1, 1, 2, 2, 1] is used for illustration. SQL tasks SQL001, SQL002, and SQL005 all belong to group 1. Formula (1) is used to calculate the variance of resource consumption of group 1 as f1 = Var(R(SQL001)) + Var(R(SQL002)) + Var(R(SQL005)), that is, the sum of the variance value of the resource consumption information of SQL001 and the resource consumption information of SQL002 and SQL005 in group 1 plus the variance value of the resource consumption information of SQL002 and the resource consumption information of SQL005 in group 1. Suppose the above sum value is a1. SQL tasks SQL003 and SQL004 both belong to group 2. Formula (1) is used to calculate the variance of resource consumption of group 2 as f1 = Var(R(SQL003)) + Var(R(SQL004)), that is, the variance value of the resource consumption information of SQL003 and the resource consumption information of SQL004 in group 2. Suppose the variance value is a2.

[0150] Step 202313: Calculate the absolute value of the difference between the time window information of each SQL task in each of the k groups and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values.

[0151] After calculating the variance values of the resource consumption information corresponding to each initial solution, further calculate the absolute value of the difference in the time window information of the initial solution according to where S in formula (2) i and S k represent each SQL task included in the above initial solution, and W(S i ) and W(S k ) represent the time window information of the SQL task.

[0152] Taking the initial solution 1 [1, 1, 2, 2, 1] for illustration, SQL tasks SQL001, SQL002, and SQL005 all belong to group 1. Using formula (2) to calculate the multiple window values of group 1 as f2 = |W(SQL001) - W(SQL002)| + |W(SQL002) - W(SQL005)|, that is, the sum of the absolute values of the differences between the time window information of SQL001 and the time window information of SQL002 and SQL005 in group 1. Suppose the above sum value is b1. SQL tasks SQL003 and SQL004 both belong to group 2. Using formula (2) to calculate the window value of group 2 as f2 = |W(SQL003) - W(SQL004)|, that is, the absolute value of the difference between the time window information of SQL003 and the time window information of SQL004 in group 2. Suppose the above absolute value is b2.

[0153] Step 202314: Determine the rank sorting result based on the variance value and window value of each group in the k groups, and respectively determine the congestion degree values of multiple groups of SQL tasks belonging to the same rank sorting result.

[0154] During the implementation process, after obtaining the variance value (or the sum value of multiple variance values) of each group in each initial solution respectively, further add up each variance value to obtain the balance value of the resource consumption information corresponding to the initial solution.

[0155] Still taking the above initial solution 1 [1, 1, 2, 2, 1] as an example, the balance value of the resource consumption information of the initial solution 1 is f1 = a1 + a2. Assume the value of f1 is 36. Similarly, assume that the value of f1 calculated for the initial solution 2 [2, 2, 1, 1, 2] using the above method is 32, the value of f1 for the initial solution 3 [1, 2, 1, 2, 1] is 34, and the value of f1 for the initial solution 4 [2, 2, 1, 1, 2] is 30.

[0156] During the implementation process, after obtaining multiple window values of each group in each initial solution respectively, further calculate the balance value of the time window information corresponding to the initial solution.

[0157] Still taking the above initial solution 1 [1, 1, 2, 2, 1] as an example, the balance value of the time window information of the initial solution 1 is f2 = b1 + b2. Assume the value of f2 is 50.56.

[0158] Similarly, assume that the value of f2 calculated for the initial solution 2 [2, 2, 1, 1, 2] using the above method is 58.33, the value of f2 for the initial solution 3 [1, 2, 1, 2, 1] is 45.25, and the value of f2 for the initial solution 4 [2, 2, 1, 1, 2] is 48.67.

[0159] In this way, the values of f1 and f2 of the above initial solution 1 [1, 1, 2, 2, 1], initial solution 2 [2, 2, 1, 1, 2], initial solution 3 [1, 2, 1, 2, 1], and initial solution 4 [2, 2, 1, 1, 2] are shown in Table 3 below.

[0160] Table 3

[0161] Label of the initial solution f1 f2 Initial solution 1 36 50.56 Initial solution 2 32 58.33 Initial solution 3 34 45.25 Initial solution 4 30 48.67

[0162] It can be concluded from Table 3 above that the value of f1 of the initial solution 2, which is 32, is less than the value of f1 of the initial solution 1, which is 36, and the value of f2 of the initial solution 2, which is 58.33, is greater than the value of f2 of the initial solution 1, which is 50.56. Therefore, the initial solution 2 and the initial solution 1 are non-dominant to each other. The value of f1 of the initial solution 3, which is 34, is greater than the value of f1 of the initial solution 4, which is 30, and the value of f2 of the initial solution 3, which is 45.25, is less than the value of f2 of the initial solution 4, which is 48.67. Therefore, the initial solution 3 and the initial solution 4 are non-dominant to each other. Moreover, the value of f1 of the initial solution 3, which is 34, is less than the value of f1 of the initial solution 1, which is 36, and the value of f2 of the initial solution 3, which is 45.25, is less than the value of f2 of the initial solution 1, which is 50.56. Therefore, the initial solution 3 and the initial solution 1 are mutually dominant. The value of f1 of the initial solution 4, which is 30, is less than the value of f1 of the initial solution 2, which is 32, and the value of f2 of the initial solution 4, which is 48.67, is less than the value of f2 of the initial solution 2, which is 58.33. Therefore, the initial solution 2 and the initial solution 4 are mutually dominant.

[0163] Moreover, in view of the fact that the resource consumption information and time window information corresponding to the above initial solution 3 and initial solution 4 are both small, therefore, it is determined that the rank sorting result is that the rank sorting result includes Sorting 1: the initial solution 3 and the initial solution 4, and the rank sorting result includes Sorting 2: the initial solution 1 and the initial solution 2.

[0164] After determining the rank sorting result of multiple initial solutions before this round of iteration, the formula (3) is used to calculate the crowding degree values of multiple initial solutions. Among them, f k (j + 1) represents the value of f1 or f2 of the initial solution in front of the current initial solution, and f k (j - 1) represents the value of f1 or f2 of the initial solution behind the current initial solution. represents the maximum value of f1 or f2 in this sorting, represents the minimum value of f1 or f2 in this sorting.

[0165] It should be noted here that the above formula (4) is only used to calculate the crowding degree value of the initial solution in the middle position. A rank sorting result uniquely corresponds to a crowding degree value. Moreover, when a rank sorting result includes two initial solutions, the above two initial solutions are both boundary initial solutions, and the crowding degree value of the boundary initial solution is infinite. The position of the initial solution in the rank sorting result is determined by the magnitude of the value of the above f1 or f2.

[0166] In the above example, the rank sorting results include Sorting 1: Initial Solution 3 and Initial Solution 4, and the rank sorting results include Sorting 2: Initial Solution 1 and Initial Solution 2 for illustration. Since the number of initial solutions in both Sorting 1 and Sorting 2 is two, that is, both of the two initial solutions are boundary initial solutions, therefore, the crowding degree values corresponding to the above Sorting 1 and Sorting 2 are both infinite.

[0167] Suppose Initial Solution 1, Initial Solution 3, and Initial Solution 4 in Table 3 are all in the above Sorting 1. Then, the initial solution located in the middle position is determined as Initial Solution 3 according to the magnitude of the value of f1, and formula (3) is used to calculate the crowding degree values of multiple initial solutions. When k equals 1, C1 is obtained according to the value of f1. When k equals 2, The crowding degree of Initial Solution 1 = C1 + C2 = 2, and moreover, the crowding degree value of this Sorting 1 is equal to the sum of the crowding degrees of Initial Solution 1, Initial Solution 3, and Initial Solution 4, that is, 2 plus infinity plus infinity. Then, the crowding degree value of this Sorting 1 is infinite.

[0168] Step 20232: Determine the parent solutions from each initial solution based on the rank sorting results and the crowding degree values, and perform crossover and mutation on the parent solutions to obtain multiple offspring solutions.

[0169] Considering that the above multiple initial solutions are randomly generated for each SQL task, that is, the above multiple initial solutions are not necessarily the optimal solutions. Based on this, in the embodiments of the present application, after determining the rank sorting results and the crowding degree values, the above each initial solution will be optimized and expanded by means of crossover and mutation.

[0170] Refer to Figure 7 As shown, determining the parent solutions from each initial solution specifically includes:

[0171] Step 202321: Calculate the comprehensive value of each group in k groups respectively based on the preset sorting weight, the rank sorting result, the preset crowding degree weight, and the crowding degree value.

[0172] Since the emphasis on the rank sorting results and the crowding degree values is different in different application scenarios, in the process of determining the parent solutions from the above randomly generated each initial solution, priority selection can be made according to the actual emphasis. During the implementation process, the specific proportion of the preset sorting weight and the crowding degree weight is used to determine whether to value more the rank sorting results or the crowding degree values in the above intermediate results.

[0173] For the convenience of calculation, during the implementation process, each SQL task is divided into groups with different initial solutions respectively, resulting in k groups. After calculating the rank sorting result and crowding degree value corresponding to each group, the sorting value is obtained by multiplying the rank sorting result with a preset sorting weight, and the crowding degree value is obtained by multiplying the crowding degree value with a preset crowding degree weight. Then, the sum value obtained by adding the sorting value and the crowding degree value is called the comprehensive value. The comprehensive value is calculated for each of the k groups represented by an initial solution.

[0174] Step 202322: Screen out h groups with high comprehensive values from the k groups, and determine the initial solutions corresponding to the h groups as the parental solutions, where h is a positive integer less than k.

[0175] After calculating the comprehensive value of each group, sort the comprehensive values in descending order, and screen out h groups with high comprehensive values from the k groups, that is, obtain h groups, and then determine the initial solutions corresponding to the above h groups as the parental solutions.

[0176] The above method of screening out h groups of initial solutions from the k groups as the parental solutions effectively screens the initial solutions randomly generated for the SQL tasks, thereby improving the effectiveness and excellence of the initial solutions of each group, and further making the finally determined initial solutions more convergent, which provides an effective guarantee for the effective establishment of the subsequent initial solutions.

[0177] Refer to Figure 8 As shown, perform crossover and mutation on the parental solutions to obtain multiple offspring solutions, including:

[0178] Step 202321’: Perform crossover on the values at any position in the parental solutions through the simulated binary crossover algorithm to obtain multiple crossover parental solutions.

[0179] In the embodiments of the present application, to ensure the comprehensiveness of the initial solutions, after determining the parental solutions from each initial solution, the above parental solutions are further expanded. During the implementation process, after determining the specific number of SQL tasks included in an initial solution, this number is used as the specific number of SQL tasks included in the expanded crossover parental solutions, thereby ensuring the consistency of each initial solution.

[0180] Generally, the simulated binary crossover algorithm randomly selects a crossover point, exchanges parts of two parental individuals at this point to generate two offspring individuals. If no crossover is performed, the parental individuals are directly returned as the offspring. During the implementation process, the above simulated binary crossover algorithm is used to expand the parental solutions, that is, perform crossover on the values at any position in the parental solutions. The specific crossover method is the method indicated by the above simulated binary crossover algorithm, and the multiple parental solutions obtained after crossover are determined as multiple crossover parental solutions.

[0181] Step 202322’: Randomly change the values at any position in multiple crossover parent solutions to obtain multiple offspring solutions.

[0182] During the implementation process, in order to make the offspring solutions more diverse, after obtaining multiple crossover parent solutions, the values of any one of the crossover parent solutions are changed, that is, the values at any position in the crossover parent solutions are randomly changed, so as to obtain multiple offspring solutions from one crossover parent solution. It should be noted that, in order to make the offspring solutions more convergent, the random change of the above values is mainly limited to the mutual change of the values already included in the initial solutions.

[0183] Exemplarily, taking the above initial solution 1 [1, 1, 2, 2, 1] as a crossover parent solution for illustration, when the values at any position in the initial solution 1 are randomly changed, the multiple offspring solutions obtained include but are not limited to [2, 1, 2, 2, 1], [1, 2, 2, 2, 1], [1, 1, 1, 2, 1], [1, 1, 1, 2, 1], and [2, 2, 1, 1, 2], etc.

[0184] Step 20233: Combine the parent solutions and the offspring solutions to obtain multiple expanded initial solutions, and screen out multiple initial solutions from the expanded initial solutions based on the resource consumption information and the time window information.

[0185] During the implementation process, after obtaining the parent solutions and the offspring solutions, the parent solutions and the offspring solutions are further combined to obtain multiple expanded initial solutions, that is, a set of optimal initial solutions is matched for each SQL task.

[0186] Refer to Figure 9 As shown, the steps of obtaining multiple initial solutions from the parent solutions and the offspring solutions specifically include:

[0187] Step 202331: Take the union of the parent solutions and the offspring solutions to obtain multiple expanded initial solutions.

[0188] During the implementation process, the number of the parent solutions and the offspring solutions obtained in the above steps is uncertain, but the functions and statuses of the parent solutions and the offspring solutions are the same. Based on this, the union of the above parent solutions and the offspring solutions is taken to obtain a set including all the parent solutions and the offspring solutions, and this set as a whole is used as the expanded initial solutions for multiple SQL tasks, that is, each parent solution and each offspring solution are respectively used as an expanded initial solution.

[0189] In the embodiments of the present application, in order to make the randomly obtained initial solutions more accurate, the initial solutions are optimized according to the resource consumption information and the time window information, that is, after steps such as determining the parent solutions, crossover and mutation to obtain the offspring solutions, etc., expanded initial solutions are obtained, thus providing an effective screening range for the determination of SQL task grouping.

[0190] After obtaining the effective screening range of the initial solution, further optimization is still carried out through the resource consumption information and time window information of the SQL tasks, that is, the parent solution is determined again from the expanded initial solution, and steps such as crossover and mutation of the child solutions are performed to obtain the final initial solution.

[0191] Step 202332: Divide each SQL task into q groups represented by the initial solution based on multiple expanded initial solutions, where q is a positive integer.

[0192] Since each expanded initial solution includes the group to which the SQL task at the corresponding position belongs, during the implementation process, for each expanded initial solution, the following operations are performed: that is, the specific numbers at each position in the expanded initial solution are determined respectively, and the same numbers are found among them. The same number indicates that the SQL tasks at the corresponding positions belong to the same group. Correspondingly, if the above numbers are different, it means that the SQL tasks at the corresponding positions belong to different groups. Further, the expanded initial solution is divided into multiple groups through the sameness or difference of the above numbers, that is, the above q groups.

[0193] Exemplarily, assume an expanded initial solution is [3, 1, 2, 3, 1]. Then, the number 3 in the first position and the number 3 in the fourth position of this expanded initial solution are the same, the number 1 in the second position and the number 1 in the fifth position are the same, and the number 2 in the third position is different from the numbers in other positions. In this way, the SQL tasks are divided into three groups, that is, the first group includes SQL2 and SQL5, the second group includes SQL3, and the third group includes SQL1 and SQL4.

[0194] Step 202333: Calculate the variance of the resource consumption information of each SQL task in each of the q groups with the resource consumption information of the remaining SQL tasks in the same group, thereby obtaining multiple variance values.

[0195] During the implementation process, after dividing the SQL tasks into q groups, use formula (1) j = 1, 2,..., m to calculate the variance of the resource consumption information of each SQL task in each of the q groups with the resource consumption information of the remaining SQL tasks in the same group, thereby obtaining multiple variance values. The specific calculation process here is similar to the above, and will not be elaborated one by one.

[0196] Still taking the expanded initial solution [3, 1, 2, 3, 1] in the above steps as an example for illustration, that is, calculate two variance values of the resource consumption information of the remaining SQL tasks in the first group and the second group and the third group respectively, and calculate one variance value of the resource consumption information of the remaining SQL tasks in the second group and the third group.

[0197] Step 202334: Calculate the absolute value of the difference between the time window information of each SQL task in each of the q groups and the time window information of the remaining SQL tasks in the same group, so as to obtain multiple window values.

[0198] During the implementation process, after dividing the SQL tasks into q groups, use formula (2) to calculate the absolute value of the difference between the time window information of each SQL task in each of the q groups and the time window information of the remaining SQL tasks in the same group, so as to obtain multiple window values. The specific calculation process here is similar to the above, and will not be elaborated one by one.

[0199] Still taking the expanded initial solution [3, 1, 2, 3, 1] in the above steps as an example, that is, calculate the absolute values of the two differences between the time window information of the remaining SQL tasks in the first group and the second group and the third group, namely two window values, and calculate the absolute value of one difference between the time window information of the remaining SQL tasks in the second group and the third group, namely one window value.

[0200] During the implementation process, after obtaining the variance value (or the sum value of multiple variance values) of each group in each expanded initial solution, further add up the variance values to obtain the equilibrium value of the resource consumption information corresponding to the expanded initial solution. And, after obtaining the multiple window values of each group in each expanded initial solution, further calculate the equilibrium value of the time window information corresponding to the expanded initial solution. The specific calculation process here is similar to the above, and will not be elaborated one by one.

[0201] Step 202335: Determine the rank sorting result based on the variance value and window value of each of the q groups, and determine the congestion value of the q groups of SQL tasks belonging to the same rank sorting result respectively.

[0202] During the implementation process, after obtaining the variance value and window value of the expanded initial solution of each of the q groups, determine the rank sorting result of each group of expanded initial solutions according to whether the numerical sizes of the variance value and window value can be dominated. The specific calculation process here is similar to the above, and will not be elaborated one by one.

[0203] During the implementation process, after determining the rank sorting results of multiple expanded initial solutions, use formula (3) to calculate the congestion values of multiple expanded initial solutions, so as to determine the congestion values of the q groups of SQL tasks belonging to the same rank sorting result. The specific calculation process here is similar to the above, and will not be elaborated one by one.

[0204] Step 202336: Calculate the comprehensive value of each of the q groups based on the preset sorting weight, rank sorting result, preset congestion weight, and congestion value respectively.

[0205] Similarly, during the implementation process, by presetting the specific proportion values of the sorting weight and the congestion degree weight, it is determined whether to place more emphasis on the ranking result related to the above-mentioned expanded initial solution or the congestion degree value.

[0206] During the implementation process, after calculating the ranking result and the congestion degree value corresponding to the expanded initial solution of each group, the preset sorting weight is multiplied by the ranking result to obtain a sorting value, and the preset congestion degree weight is multiplied by the congestion degree value to obtain a congestion degree value. Then, the sum obtained by adding the sorting value and the congestion degree value is called the comprehensive value. The comprehensive value is calculated for each group represented by an expanded initial solution.

[0207] Step 202337: Select f groups with high comprehensive values from q groups, and determine the expanded initial solutions corresponding to the f groups as the selected multiple initial solutions, where f is a positive integer less than q.

[0208] Similarly, during the implementation process, after calculating the comprehensive value of each group, the comprehensive values are sorted in descending order, and f groups with high comprehensive values are selected from q groups, that is, f groups are obtained. Then, the expanded initial solutions corresponding to the above f groups are selected as multiple initial solutions.

[0209] During the implementation process, after grouping each SQL task and obtaining multiple groups, for the same group, the SQL tasks included in the group are merged. The merging method is not specifically limited, so as to obtain multiple merged SQL task bars, that is, each group is converted into a SQL task bar.

[0210] Step 203: Establish processing items for each of the merged SQL task bars, and submit each processing item to the corresponding server cluster for processing.

[0211] During the implementation process, after obtaining multiple merged SQL task bars, a processing item, such as a Flink processing item, is established for each SQL task bar. In order to enable the sequential execution of each SQL task bar, the established processing items are submitted to the corresponding server cluster, and then the server cluster processes the above SQL task bars.

[0212] It should be supplemented and explained that in the embodiments of the present application, if the above processing item fails to be successfully submitted to the corresponding server cluster, the processing item is submitted to the corresponding server cluster again until the submission is successful, thereby ensuring the integrity of the SQL task execution.

[0213] Based on the same inventive concept, refer to Figure 10 As shown, in the embodiments of the present application, a device for grouping SQL tasks is provided, including:

[0214] An acquisition unit 1001, configured to acquire multiple SQL tasks based on the operation log data of a database, where each SQL task carries resource consumption information and time window information;

[0215] A grouping unit 1002, configured to group each SQL task based on the resource consumption information and time window information to obtain multiple groups, and merge each SQL task included in the same group to obtain multiple merged SQL task items, where the number of task items is the same as the number of groups;

[0216] A submission unit 1003, configured to establish processing matters for each merged SQL task item respectively, and submit each processing matter to a corresponding server cluster for processing.

[0217] Optionally, to acquire multiple SQL tasks based on the operation log data of a database, the acquisition unit 1001 is configured to:

[0218] Establish Flink cdc, and use Flink cdc to monitor in real time whether the operation log data of the database changes;

[0219] If it is monitored that the operation log data changes, extract multiple SQL tasks from the changed operation log data;

[0220] Parse each of the multiple SQL tasks respectively, and extract the resource consumption information and time window information carried by the SQL tasks from the parsing results.

[0221] Optionally, to group each SQL task based on the resource consumption information and time window information to obtain multiple groups, the grouping unit 1002 is configured to:

[0222] Randomly generate multiple initial solutions for each SQL task, where each initial solution includes multiple numbers at the i-th position, each number represents the i-th SQL task, the same number represents that the SQL tasks at the corresponding position belong to the same group, different numbers represent that the SQL tasks at the corresponding position belong to different groups, and the window types represented by the time window information of the SQL tasks in the same group are the same, and i is a natural number;

[0223] Input the multiple initial solutions randomly generated based on each SQL task into the model corresponding to the NSGA-II algorithm, and enable the model to screen the multiple initial solutions in an iterative manner;

[0224] During each iteration, it is determined whether the initial solutions selected in this iteration meet the convergence condition. When the convergence condition is met, multiple groups obtained by grouping each SQL task are determined according to the selected initial solutions. The convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of selected initial solutions is one. The process of each iteration is as follows:

[0225] Based on the resource consumption information and time window information, respectively determine the rank sorting result and crowding degree value of multiple initial solutions before this iteration;

[0226] Based on the rank sorting result and crowding degree value, determine the parent solutions from each initial solution, and perform crossover and mutation on the parent solutions to obtain multiple offspring solutions;

[0227] Combine the parent solutions and offspring solutions to obtain multiple expanded initial solutions, and screen out multiple initial solutions from the expanded initial solutions based on the resource consumption information and time window information.

[0228] Optionally, based on the resource consumption information and time window information, respectively determine the rank sorting result and crowding degree value of multiple initial solutions before this iteration. The grouping unit 1002 is used for:

[0229] Divide each SQL task into k groups represented by the initial solution based on any one initial solution before this iteration, where k is a positive integer;

[0230] Calculate the variance of the resource consumption information of each SQL task in each of the k groups with the resource consumption information of the remaining SQL tasks in each group, so as to obtain multiple variance values;

[0231] Calculate the absolute value of the difference between the time window information of each SQL task in each of the k groups and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values;

[0232] Determine the rank sorting result based on the variance value and window value of each of the k groups, and respectively determine the crowding degree value of multiple groups of SQL tasks belonging to the same rank sorting result.

[0233] Optionally, determine the parent solutions from each initial solution based on the rank sorting result and crowding degree value. The grouping unit 1002 is used for:

[0234] Calculate the comprehensive value of each of the k groups based on the preset sorting weight, rank sorting result, preset crowding degree weight, and crowding degree value;

[0235] Select h groups with high comprehensive values from the k groups, and determine the initial solutions corresponding to the h groups as the parent solutions, where h is a positive integer less than k.

[0236] Optionally, multiple offspring solutions are obtained by performing crossover and mutation on the parent solutions, and the grouping unit 1002 is configured to:

[0237] Perform crossover on the values at any position in the parent solutions through a simulated binary crossover algorithm to obtain multiple crossover parent solutions;

[0238] Randomly change the values at any position in the multiple crossover parent solutions to obtain multiple offspring solutions.

[0239] Optionally, the parent solutions and the offspring solutions are combined to obtain multiple extended initial solutions, and multiple initial solutions are screened from the extended initial solutions based on the resource consumption information and the time window information. The grouping unit 1002 is configured to:

[0240] Take the union of the parent solutions and the offspring solutions to obtain multiple extended initial solutions;

[0241] Divide each SQL task into q groups represented by the initial solutions based on the multiple extended initial solutions, where q is a positive integer;

[0242] Calculate the variance between the resource consumption information of each SQL task in each group of the q groups and the resource consumption information of the remaining SQL tasks in each group, so as to obtain multiple variance values;

[0243] Calculate the absolute value of the difference between the time window information of each SQL task in each group of the q groups and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values;

[0244] Determine the rank sorting result based on the variance value and the window value of each group in the q groups, and respectively determine the congestion degree values of the q groups of SQL tasks belonging to the same rank sorting result;

[0245] Calculate the comprehensive value of each group in the q groups respectively based on the preset sorting weight, the rank sorting result, the preset congestion degree weight, and the congestion degree value;

[0246] Screen out f groups with high comprehensive values from the q groups, and determine the extended initial solutions corresponding to the f groups as the screened multiple initial solutions, where f is a positive integer less than q.

[0247] Based on the same inventive concept, refer to Figure 11 As shown, an embodiment of the present application provides a server, including: a memory 1101 for storing executable instructions; a processor 1102 for reading and executing the executable instructions stored in the memory, and executing any one of the methods in the above aspects.

[0248] Based on the same inventive concept, an embodiment of the present application provides a computer-readable storage medium. When the instructions in the storage medium are executed by a processor, the processor can execute the method described in any item of the first aspect above.

[0249] In summary, in the embodiment of the present application, a method, an apparatus, and a storage medium for grouping SQL tasks are provided. The method includes: obtaining multiple SQL tasks based on the operation log data of a database, where each SQL task carries resource consumption information and time window information; grouping each SQL task based on the resource consumption information and the time window information to obtain multiple groups; and merging each SQL task included in the same group to obtain multiple merged SQL task items, where the number of task items is the same as the number of groups. Processing items are established for each merged SQL task item, and each processing item is submitted to the corresponding server cluster for processing. The above solution intelligently groups and merges a large number of SQL tasks, optimizes the balance of resource consumption and the consistency of time windows, reduces the number of processing items, improves the resource utilization efficiency, and reduces the complexity of system operation and maintenance.

[0250] Those skilled in the art should understand that the embodiments of the present application can be provided as a method, a system, or a computer program product system. Therefore, the present application can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present application can take the form of a computer program product system implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0251] The present application is described with reference to the flowcharts and / or block diagrams of the method, device (system), and computer program product system according to the present application. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, as well as the combination of flows and / or blocks in the flowcharts and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing devices generate a device for implementing the functions specified in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0252] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory generate a manufactured article including an instruction device, and the instruction device implements the functions in the processFigure 1 one process or multiple processes and / or blocks Figure 1 the functions specified in one block or multiple blocks.

[0253] These computer program instructions can also be loaded onto a computer or other programmable data processing device, so that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process. Thus, the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in Figure 1 one process or multiple processes and / or blocks Figure 1 one block or multiple blocks.

[0254] Obviously, those skilled in the art can make various changes and modifications to this application without departing from the spirit and scope of this application. Thus, if these modifications and variations of this application fall within the scope of the claims of this application and their equivalent technologies, this application is also intended to include these changes and modifications.

Claims

1. A method for grouping SQL tasks, characterized in that, The method includes: Obtaining multiple SQL tasks based on the operation log data of the database, where each of the SQL tasks carries resource consumption information and time window information; Grouping each of the SQL tasks based on the resource consumption information and the time window information to obtain multiple groups, and merging each of the SQL tasks included in the same group to obtain multiple merged SQL task bars, where the number of the task bars is the same as the number of the groups; Establishing processing matters for each of the merged SQL task bars respectively, and submitting each of the processing matters to the corresponding server cluster for processing.

2. The method according to claim 1, wherein The obtaining multiple SQL tasks based on the operation log data of the database includes: Establishing Flink cdc, and using Flink cdc to monitor in real time whether the operation log data of the database changes; If it is monitored that the operation log data changes, extracting multiple SQL tasks from the changed operation log data; Parsing each of the multiple SQL tasks respectively, and extracting the resource consumption information and the time window information carried by the SQL tasks from the parsing results.

3. The method according to claim 1, wherein The grouping each of the SQL tasks based on the resource consumption information and the time window information to obtain multiple groups includes: Randomly generating multiple initial solutions for each of the SQL tasks, where each of the initial solutions includes multiple numbers at the i-th position, each of the numbers represents the i-th SQL task, the same numbers represent that the SQL tasks corresponding to the positions belong to the same group, different numbers represent that the SQL tasks corresponding to the positions belong to different groups, and the window types represented by the time window information of the SQL tasks in the same group are the same, and i is a natural number; Inputting the multiple initial solutions randomly generated based on each of the SQL tasks into the model corresponding to the NSGA-II algorithm, and enabling the model to screen the multiple initial solutions in an iterative manner; Judging whether the initial solutions screened out in this round meet the convergence condition in each round of iteration process. When the convergence condition is met, determining the parameters of the model, and determining multiple groups obtained after grouping each of the SQL tasks according to the screened initial solutions; where the convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of the screened initial solutions is one, and each round of iteration process is as follows: Determining the rank sorting results and crowding degree values of the multiple initial solutions before this round of iteration respectively based on the resource consumption information and the time window information; Determining parent solutions from each of the initial solutions based on the rank sorting results and the crowding degree values, and performing crossover and mutation on the parent solutions to obtain multiple offspring solutions; Merging the parent solutions and the offspring solutions to obtain multiple extended initial solutions, and screening out multiple initial solutions from the extended initial solutions based on the resource consumption information and the time window information.

4. The method according to claim 3, wherein Determining the rank sorting results and crowding degree values of multiple initial solutions before this iteration based on the resource consumption information and the time window information respectively, includes: Dividing each SQL task into k groups represented by the initial solution based on any one of the initial solutions before this iteration, where k is a positive integer; Calculating the variances of the resource consumption information of each SQL task in each of the k groups with the resource consumption information of the remaining SQL tasks in each group respectively, so as to obtain a plurality of variance values; Calculating the absolute values of the differences between the time window information of each SQL task in each of the k groups and the time window information of the remaining SQL tasks in each group respectively, so as to obtain a plurality of window values; Determining the rank sorting results based on the variance values and the window values of each of the k groups, and determining the crowding degree values of multiple groups of SQL tasks belonging to the same rank sorting result respectively.

5. The method according to claim 4, characterized in that, Determining the parent solutions from each of the initial solutions based on the rank sorting results and the crowding degree values, includes: Calculating the comprehensive value of each of the k groups respectively based on a preset sorting weight, the rank sorting results, a preset crowding degree weight and the crowding degree values; Selecting h groups with higher comprehensive values from the k groups, and determining the initial solutions corresponding to the h groups as the parent solutions, where h is a positive integer less than k.

6. The method according to claim 5, characterized in that, Performing crossover and mutation on the parent solutions to obtain multiple offspring solutions, includes: Performing crossover on the values at any position in the parent solutions through a simulated binary crossover algorithm to obtain multiple crossover parent solutions; Randomly changing the values at any position in multiple crossover parent solutions to obtain multiple offspring solutions.

7. The method according to claim 3, wherein Merging the parent solutions and the offspring solutions to obtain multiple extended initial solutions, and screening out multiple initial solutions from the extended initial solutions based on the resource consumption information and the time window information, includes: Taking the union of the parent solutions and the offspring solutions to obtain multiple extended initial solutions; Dividing each SQL task into q groups represented by the initial solution based on the multiple extended initial solutions, where q is a positive integer; Calculating the variances of the resource consumption information of each SQL task in each of the q groups with the resource consumption information of the remaining SQL tasks in each group respectively, so as to obtain a plurality of variance values; Calculating the absolute values of the differences between the time window information of each SQL task in each of the q groups and the time window information of the remaining SQL tasks in each group respectively, so as to obtain a plurality of window values; Determining the rank sorting results based on the variance values and the window values of each of the q groups, and determining the crowding degree values of q groups of SQL tasks belonging to the same rank sorting result respectively; Calculating the comprehensive value of each of the q groups respectively based on a preset sorting weight, the rank sorting results, a preset crowding degree weight and the crowding degree values; Screen out f groups with high comprehensive values from the q groups, and determine the expanded initial solutions corresponding to the f groups as the screened multiple initial solutions, where f is a positive integer less than q.

8. An apparatus for grouping SQL tasks, characterized in that, It includes: An acquisition unit for acquiring multiple SQL tasks based on the operation log data of the database, where each SQL task carries resource consumption information and time window information; A grouping unit for grouping each SQL task based on the resource consumption information and the time window information to obtain multiple groups, and merging each SQL task included in the same group to obtain multiple merged SQL task items, where the number of the task items is the same as the number of the groups; A submission unit for establishing processing matters for each of the merged SQL task items and submitting each of the processing matters to the corresponding server cluster for processing.

9. A server, characterized in that, It includes: A memory for storing executable instructions; A processor for reading and executing the executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, When the instructions in the storage medium are executed by the processor, the processor is enabled to execute the method according to any one of claims 1 to 7.

Images