Resource configuration optimization method based on query complexity and related device

Abstract

The invention provides a resource configuration optimization method based on query complexity and a related device, and relates to the field of big data. The electronic equipment obtains a structured query language (SQL) to be executed; based on the to-be-executed SQL, obtaining a complexity feature of the to-be-executed SQL; wherein the complexity feature represents the complexity when the to-be-executed SQL is executed; and obtaining resource configuration information required for executing the SQL to be executed according to the complexity feature of the SQL to be executed. Therefore, differentiated resource allocation of queries with different complexities is realized by establishing a quantitative mapping relation from an SQL grammar structure to a resource demand, so that simple queries do not occupy excessive resources any more, complex queries can also obtain sufficient computing power support, the utilization rate of cluster resources is improved, and the risk of task failure is reduced.

Description

Technical Field

[0001] This application relates to the field of big data, and more specifically, to a resource allocation optimization method and related apparatus based on query complexity. Background Technology

[0002] Natural Language to Structured Query Language (SL), also known as NL2SQL, is a technology that automatically transforms users' data query requests, expressed in everyday language, into a structured query language. It is widely used in various intelligent question-answering systems. Using this technology, non-experts don't need to master complex database syntax; they only need to input a question like "What was the product with the highest sales volume last month?" The system can then use a large language model to understand the semantic intent and generate the corresponding SQL, thereby enabling access and manipulation of the database.

[0003] The generated SQL is then executed by a big data processing engine. Taking Apache Spark as an example, Spark employs a distributed computing architecture, enabling parallel processing of massive amounts of data; however, its operation relies on proper resource configuration. When submitting a task, the required computing resources must be explicitly specified, including parameters that directly affect the task's execution efficiency and stability, such as the number of Executors, the memory size of each Executor, the number of CPU cores, and the driver's resource configuration.

[0004] However, in current practical applications, most systems adopt a uniform and excessively high resource allocation strategy to ensure the smooth completion of complex queries. Whether dealing with simple counting operations that only scan a small amount of data or heavy analytical tasks involving multi-table joins and deep aggregations, the same high-specification resources are allocated. This results in a situation where, for lightweight queries, a large number of requested resources are not fully utilized, leading to idle cluster resources and reduced overall throughput. For truly complex query tasks, the fixed resource allocation may still be insufficient to support their operation, and the lack of a dynamic adjustment mechanism may even lead to execution failure or response delays. Furthermore, due to the large scale of requested resources, the resource scheduler needs more time to find nodes that meet the conditions during the allocation process, further extending the task startup waiting time and slowing down query response. Summary of the Invention

[0005] To overcome at least one deficiency in the prior art, this application provides a resource allocation optimization method and related apparatus based on query complexity, comprising: Firstly, this application provides a resource allocation optimization method based on query complexity, the method comprising: Obtain the Structured Query Language (SQL) code to be executed; Based on the SQL to be executed, the complexity characteristics of the SQL to be executed are obtained, wherein the complexity characteristics characterize the complexity of the SQL to be executed. Based on the complexity characteristics of the SQL to be executed, the resource configuration information required to execute the SQL is obtained.

[0006] Secondly, this application provides a resource allocation optimization method based on query complexity, the method comprising: The query language module is used to obtain the structured query language SQL to be executed; The language analysis module is used to obtain the complexity characteristics of the SQL to be executed based on the SQL to be executed, wherein the complexity characteristics characterize the complexity of the SQL to be executed. The resource configuration module is used to obtain the resource configuration information required to execute the SQL based on the complexity characteristics of the SQL to be executed.

[0007] Thirdly, this application provides a storage medium storing a computer program that, when executed by a processor, implements the resource allocation optimization method based on query complexity.

[0008] Fourthly, this application provides an electronic device, which includes a processor and a memory. The memory stores a computer program, which, when executed by the processor, implements the resource allocation optimization method based on query complexity.

[0009] Compared with the prior art, this application has the following beneficial effects: The resource allocation optimization method and related apparatus based on query complexity provided in this application involve an electronic device acquiring a Structured Query Language (SQL) to be executed; obtaining the complexity characteristics of the SQL based on the SQL; wherein the complexity characteristics represent the complexity of the SQL execution; and obtaining the resource configuration information required to execute the SQL based on the complexity characteristics. In this way, by establishing a quantitative mapping relationship from SQL syntax structure to resource requirements, differentiated resource allocation for queries of different complexities can be achieved, ensuring that simple queries no longer consume excessive resources, while complex queries can also receive sufficient computing power support. This improves cluster resource utilization and reduces the risk of task failure. Attached Figure Description

[0010] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0011] Figure 1 A flowchart illustrating the resource allocation optimization method based on query complexity provided in this application embodiment; Figure 2 Complete example diagrams of the methods provided in the embodiments of this application; Figure 3 A schematic diagram of the structure of a resource allocation optimization device based on query complexity provided in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0012] To make the objectives, technical solutions, and advantages of the embodiments of this application (hereinafter referred to as "the embodiments") clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. The components of the embodiments of this application described and shown in the accompanying drawings can generally be arranged and designed in various different configurations.

[0013] Therefore, the following detailed description of the embodiments of this application provided in the accompanying drawings is not intended to limit the scope of the claimed application, but merely to illustrate selected embodiments of the application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.

[0014] It should be noted that similar labels and letters in the following figures indicate similar items. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures.

[0015] In the description of this application, it should be noted that the terms "first," "second," "third," etc., are used only for distinguishing descriptions and should not be construed as indicating or implying relative importance. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0016] Based on the above statements, as introduced in the background technology, in order to meet the needs of complex computing, regardless of the complexity of the generated SQL query (e.g., simple single-table row counting versus complex multi-table join aggregation), a uniform, fixed, and excessive Spark resource configuration (such as the number of Executors, memory, CPU cores, etc.) is used. As a result, for lightweight queries, a large number of requested resources are not fully utilized, resulting in idle cluster resources and reduced overall throughput. For truly complex query tasks, the fixed resource configuration may still be insufficient to support their operation, and the lack of a dynamic adjustment mechanism may lead to execution failure or response delay.

[0017] Although related technologies attempt to optimize by setting rules or static thresholds based on human experience, these methods are difficult to adapt to the constantly changing load characteristics in real production environments due to the highly diverse and unpredictable query patterns in NL2SQL scenarios.

[0018] It should be noted that the defects in the solutions in the prior art are the result of practice and careful research. Therefore, the discovery process of the above problems and the solutions proposed by the embodiments of this application in the following text should be regarded as contributions to this application in the process of invention and creation, and should not be understood as technical content known to those skilled in the art.

[0019] Based on the discovery of the above-mentioned technical problems, this embodiment provides a resource allocation optimization method based on query complexity. For example... Figure 1 As shown, the method includes: S1, obtain the Structured Query Language (SQL) code to be executed.

[0020] S2, based on the SQL to be executed, obtains the complexity characteristics of the SQL to be executed.

[0021] Among them, the complexity feature represents the complexity of the SQL to be executed.

[0022] S3, based on the complexity characteristics of the SQL to be executed, obtains the resource configuration information required to execute the SQL.

[0023] In this way, by establishing a quantitative mapping relationship from SQL syntax structure to resource requirements, differentiated resource allocation can be achieved for queries of different complexities. This ensures that simple queries no longer consume excessive resources, while complex queries can also obtain sufficient computing power support, thereby improving the utilization rate of cluster resources and reducing the risk of task failure.

[0024] It should be understood that, for the query complexity-based resource configuration optimization method provided in this embodiment, the electronic device implementing the method can be, but is not limited to, a driver node, resource proxy server, server, virtual machine (VM), or container instance in a Spark cluster. The server can be a single server or a group of servers. The server group can be centralized or distributed (e.g., the servers can be a distributed system). In some embodiments, the server can be local or remote relative to the user terminal. In some embodiments, the server can be implemented on a cloud platform; by way of example only, the cloud platform can include private cloud, public cloud, hybrid cloud, community cloud, distributed cloud, inter-cloud, multi-cloud, etc., or any combination thereof. In some embodiments, the server can be implemented on an electronic device having one or more components.

[0025] To make the solution provided in this embodiment clearer, a server is used as an example below, and in conjunction with... Figure 1 Each step of the method is described in detail. However, it should be understood that the operations in the flowchart may not be implemented in sequence, and steps without logical contextual relationships may be reversed in order or performed simultaneously. Furthermore, those skilled in the art, guided by the content of this application, may add one or more other operations to the flowchart, or remove one or more operations from the flowchart. See also... Figure 1 The methods include: S1, obtain the Structured Query Language (SQL) code to be executed.

[0026] It should be understood that in current natural language-based data query application scenarios, users typically submit information retrieval requests in an unstructured form. Therefore, it is necessary to convert these requests into a machine-executable structured query language (SQL).

[0027] Specifically, users can input natural language questions on the question-and-answer interface, such as "Calculate the total sales of each product last month." These questions are submitted to a Natural Language to SQL (NL2SQL) system, which integrates a Large Language Model (LLM). This system understands natural language semantics and automatically translates it into grammatically correct SQL statements based on the database schema. In this process, the LLM, as the core component of semantic parsing, generates query statements like the following by recognizing the user's intent and matching it with the database table structure: "SELECT product_name, SUM(sales_amount) FROM sales_records WHEREmonth = '2023-10' GROUP BY product_name" Furthermore, in real-world business environments, some users may possess database query capabilities and be able to directly write SQL that conforms to standard syntax to express their data retrieval needs. In this case, this embodiment also supports using user-written Structured Query Language (SQL) code as input.

[0028] Based on the description of the SQL to be executed in the above embodiments, please refer to [link to previous text]. Figure 1 Next, we will explain step S2: S2, based on the SQL to be executed, obtains the complexity characteristics of the SQL to be executed.

[0029] Among them, the complexity feature represents the complexity of the SQL to be executed.

[0030] Research has found that existing technologies lack quantitative analysis of SQL execution complexity, making it impossible to dynamically adjust resource allocation based on the actual resource requirements of the query. This leads to problems such as simple queries consuming excessive resources and wasting them, while complex queries suffer from insufficient resources, resulting in slow execution or failure. Therefore, this embodiment also provides the following optional implementation methods for step S2: S2-1: Based on the SQL to be executed, determine the syntax parameters of various syntax features.

[0031] This can be understood as follows: the above steps extract various quantifiable syntactic structure features from the Structured Query Language (SQL) statement to be executed and transform them into parameter values ​​with clear physical meaning, thereby quantifying the complexity of the SQL.

[0032] Specifically, the server first performs syntax parsing on the SQL to be executed, identifying the key operation types and their frequency or nesting level, and then determining a set of syntax features that reflect the complexity of the query logic. These syntax features cover typical operations in data processing, including multi-table joins, aggregation calculations, grouping operations, window function applications, user-defined function calls, use of other built-in functions, subquery nesting depth, sorting operations, and the scale of data involved. For each type of syntax feature, the server extracts the corresponding syntax parameters according to preset rules, ensuring that a default value is returned even if a certain type of operation does not occur (for example, the default value can be 0), thus guaranteeing the completeness of the complexity features.

[0033] In this embodiment, the syntax parameters of various syntax features specifically include the number of multi-table join operations (join_count), the number of aggregate functions (aggregate_count), the number of grouping operations (groupby_count), the number of window functions (window_func_count), the number of user-defined functions (udf_count), the number of other functions (other_func_count), the subquery nesting depth (subquery_count), the number of sorting fields (sort_operations), and the size of the dataset to be accessed (table_size_num). These nine parameters together constitute a set of syntax features describing the overall structure of the SQL to be executed. Each parameter corresponds to the presence and intensity of a certain type of typical operation in the SQL.

[0034] The following complete SQL statement to be executed will be used as an example for illustration: { SELECT user_id, COUNT(*), AVG(amount) FROM transactions GROUP BY user_id } During this process, the server checks for the existence of multi-table join operations using the regular expression "\\bJOIN\\b". Since the "JOIN" keyword does not appear in the statement, the number of multi-table join operations (join_count) is 0.

[0035] The server further matched the aggregate functions “COUNT” and “AVG”, both of which are members of the preset function list (“COUNT”, “SUM”, “AVG”, “MIN”, “MAX”), so the number of aggregate functions (aggregate_count) is 2.

[0036] The server then detected the "GROUP BY" keyword once, indicating the existence of a single grouping dimension. Therefore, the number of grouping operations (groupby_count) is 1. Furthermore, no prefixes for scanning window functions, such as "ROW_NUMBER" or "RANK", were found, so the number of window functions (window_func_count) is 0.

[0037] The server continues to check if there are any function calls in the SQL to be executed that start with "udf_". Since there are no matching results, the number of user-defined functions (udf_count) is 0. It also searches for other common functions such as "CONCAT" and "COALESCE", but they also do not appear. Therefore, the number of other functions (other_func_count) is 0.

[0038] For the subquery nesting depth (subquery_count), the server estimates the nesting level by subtracting the number of right parentheses from the number of left parentheses. Since this statement has no nested subqueries, the number of parentheses is balanced, and the difference is 0. Regarding the number of sorting fields (sort_operations), and since the statement does not contain an "ORDER BY" clause, the number of sorting fields is 0. Finally, it should be understood that the data size (table_size_num) of the dataset to be accessed is not directly extracted from the SQL, but is obtained through an external metadata interface from the number of rows or the estimated size of the table "transactions", which is set to 2000 records in the example scenario.

[0039] After the server completes the above syntax feature extraction process, the final syntax parameter result is as follows: { "aggregate_count": 2, "groupby_count": 1, "join_count": 0, "other_func_count": 0, "sort_operations": 0, "subquery_count": 0, "table_size_num": 2000, "udf_count": 0, "window_func_count": 0 } Thus, the syntactic parameters of various syntactic features accurately reflect the structural characteristics of the SQL.

[0040] Based on the above examples illustrating various grammatical features, step S2 further includes: S2-2 transforms the syntax parameters of various syntax features into complexity features of the SQL to be executed, based on the mapping methods of each feature.

[0041] It should be understood that traditional resource allocation methods typically estimate query complexity using linear or fixed threshold approaches, which fail to reflect the actual growth pattern of computational overhead caused by multi-table joins. Research has found that the current Spark execution engine, when processing SQL queries involving multiple table joins, does not increase resource requirements linearly with the number of joins. Instead, it exhibits an almost exponential increase due to the introduction of shuffle operations and the overhead of data redistribution. Therefore, simple linear weighting cannot accurately reflect the impact of such operations on memory, CPU, and execution time. It should be noted that if the non-linear impact of multi-table join operations is not properly quantified, resource allocation may deviate significantly from actual needs. For example, for complex queries involving many JOIN operations, insufficient Executor memory and instance count may be allocated due to underestimating resource consumption, leading to slow task execution or even Out of Memory (OOM) errors. Conversely, for simple queries containing only a few or no JOIN operations, excessive resource allocation may occur due to a lack of fine-grained differentiation mechanisms, resulting in a decrease in overall cluster utilization. Therefore, the syntax parameters for various syntax features include the number of multi-table join operations, and the complexity features of the SQL to be executed include the feature values ​​of multi-table join operations. The following optional implementation methods for step S2-2 are provided: S2-2-1, the number of multi-table join operations is used as the exponent of the first parameter for exponentiation to obtain the characteristic value of the multi-table join operation, expressed as:

[0042] In the formula, This represents the characteristic value of a multi-table join operation. This indicates the number of multi-table join operations, with 1.5 representing the first parameter.

[0043] It should be noted that the design of this function is based on statistical analysis of historical execution data. Research revealed that in current NL2SQL systems, as the number of JOIN operations increases, the actual resource consumption of tasks exhibits an approximately exponential growth pattern. This function... It can fit the trend well.

[0044] For example, when When the eigenvalue is 0, it indicates no connection operation; when When, the eigenvalue is 0.5; when When, the eigenvalue rises to 1.25; when At this point, the eigenvalue reaches approximately 2.375, showing a characteristic of accelerating increase with the number of operations. It should also be understood that the first parameter here is set to 1.5, an empirical coefficient determined based on performance monitoring data from a large number of historical query samples, used to balance model sensitivity and stability.

[0045] Furthermore, the study found that when processing nested queries, the current Spark execution engine may introduce additional physical execution plan branches, intermediate data caching, and multi-stage task scheduling overhead for each subquery level. This results in computational complexity not increasing linearly with the nesting level, but rather exhibiting a segmented, accelerating growth trend. When the nesting level is shallow (e.g., no more than 2 levels), its impact on execution performance is relatively controllable, showing a roughly quadratic growth. However, when the nesting depth exceeds 2 levels, resource requirements rise sharply due to factors such as limited execution plan optimization, a doubling of memory usage, and a lengthening of task dependency chains, exhibiting near-exponential growth characteristics.

[0046] In view of this, the syntax parameters of various syntax features also include the nesting depth of subqueries, the complexity features of the SQL to be executed also include the feature values ​​of subqueries, and the following optional implementation methods for step S2-2 are provided: S2-2-2, If the nesting depth of the subquery is greater than 2, then the nesting depth of the subquery is used as the exponent of the second parameter for exponentiation to obtain the feature value of the subquery, expressed as:

[0047] S2-2-3, If the nesting depth of the subquery is less than or equal to 2, then the nesting depth of the subquery is used as the base for exponentiation to obtain the characteristic value of the subquery, expressed as:

[0048] In the formula, This represents the feature value of the subquery. This indicates the nesting depth of the subquery, and 2 represents the second parameter.

[0049] It should be understood that this design is based on statistical analysis of historical execution data, which revealed that when the nesting level exceeds two levels, the Spark execution engine often struggles to effectively optimize the execution plan, resulting in a near doubling of resource consumption with each additional layer of nesting. Furthermore, it should be noted that the second parameter here is set to 2, which is also an empirical constant determined based on actual performance monitoring data.

[0050] For example, for a simple filtering statement that contains only a single-level subquery, its A feature value of 1 corresponds to a low resource weight; however, for a complex analytical query containing four levels of nesting, its... Its eigenvalue is 8, which is significantly higher than the former, thus prompting the resource mapping model to allocate more Executor memory and core resources to it in order to cope with potential execution bottlenecks.

[0051] The study also found that current resource allocation mechanisms, when faced with queries containing grouping operations (GROUP BY), exhibit computational loads that are not only related to the number of grouping fields but also highly dependent on the size of the accessed dataset. When multiple queries have the same number of groupings but involve datasets of different sizes, using only the number of groupings as the basis for resource requirement assessment cannot accurately reflect the actual differences in resource consumption. For example, performing a single-field grouping operation on a data table containing millions of records consumes significantly more computational resources than performing the same operation on a data table with only hundreds of thousands of records.

[0052] Therefore, the syntax parameters for various syntax features also include the number of grouping operations and the amount of data, and the complexity features of the SQL to be executed also include the feature values ​​of the grouping operations. The following optional implementation methods for step S2-2 are provided: S2-2-4, perform a logarithmic operation on the number of fields in the grouping operation to obtain the characteristic value of the grouping operation, expressed as:

[0053] In the formula, The characteristic value representing the grouping operation, Indicates the number of groups. Indicates the amount of data.

[0054] In practice, the server parses the SQL to be executed to determine if a grouping operation exists and extracts the number of fields involved in the grouping, i.e., the number of groupings. This value reflects the number of logical grouping dimensions. Simultaneously, the server also retrieves metadata information about the tables or partitions involved in the SQL query to determine the amount of data it scanned. In this process, in order to reflect the amplifying effect of data size on resource consumption, and at the same time avoid the numerical overflow problem caused by extremely large tables, this embodiment uses a logarithmic function to compress and transform the data volume.

[0055] It should be noted that the expression contains... The purpose of this operation is to prevent the logarithm from becoming undefined or fluctuating drastically when the data volume is zero or extremely small, ensuring the stability of the function in the low-value range. The logarithmic operation with a base of 10 effectively mitigates the impact of data volume increasing exponentially.

[0056] For example, when two queries have the same number of groupings (e.g. However, when applied to datasets of 10,000 and 1 million records respectively, the corresponding... The values ​​are approximately 4 and 6, respectively, resulting in eigenvalues ​​of 8 and 12. This design allows resource allocation decisions to take into account both the syntactic structure and the actual data size.

[0057] The study also found that when the SQL to be executed contains multiple sorting fields, the complexity of the corresponding sorting algorithm does not increase linearly, but is closely related to the square of the number of fields. For example, in distributed computing engines such as Spark, multi-field sorting requires constructing a composite sorting key and performing a full comparison, which leads to a significant increase in the number of memory comparisons and serialization overhead, resulting in actual resource consumption far exceeding that of the single-field case. In addition, the resource consumption of sorting operations is also highly dependent on the amount of data in the dataset to be accessed. Especially when performing sorting on large-scale datasets, it is very easy to cause memory overflow or frequent disk overflow, resulting in task delays or even failures.

[0058] It should be noted that traditional methods model the number of sorting fields and the amount of data separately, lacking a detailed characterization of the independent influence paths of the two. Specifically, the number of sorting fields directly affects the complexity of the sorting logic, and its influence exhibits power-law characteristics; on the other hand, the amount of data in the dataset to be accessed determines the actual data processing scale of the sorting operation, and the resource pressure brought about by its growth also has non-linear characteristics.

[0059] Therefore, the syntax parameters of various syntax features also include the number of sorting fields and the amount of data in the dataset to be accessed. The complexity features of the SQL to be executed also include the feature values ​​of the sorting fields and the feature values ​​of the dataset to be accessed.

[0060] S2-2-5, using the number of sorting fields as the base, performs an exponentiation to obtain the characteristic value of the sorting field. The expression is:

[0061] In the formula , representing the feature value of the sorting field. Indicates the number of sorting fields; S2-2-6, using the data size of the dataset to be accessed as the base, performs an exponentiation to obtain the feature values ​​of the dataset. The expression is:

[0062] In the formula, This represents the feature values ​​of the dataset to be accessed. This indicates the amount of data in the dataset to be accessed. and This represents a preset constant.

[0063] In practice, the server parses the ORDER BY clause from the SQL to be executed and counts the number of sorting fields it contains, i.e., the number of sorting fields. This value directly determines the dimension of the sorting key and the complexity of the comparison logic. During this process, as the number of sorting fields increases, the comparison overhead for each record during sorting increases quadratically, because multi-field sorting requires lexicographical comparisons field by field, leading to a significant increase in memory access and CPU computational burden.

[0064] At the same time, the server also obtains metadata information about the tables or partitions involved in the SQL query to determine the amount of data it scanned. This data volume represents the total number of rows of records to be sorted or the data size. Because distributed environments also involve risks such as data skew and disk spills, resource consumption exhibits a superlinear trend as the data volume increases; therefore, a simple linear weighting method cannot be used for mapping.

[0065] In addition, in the expression and These represent preset constants, with values ​​of 0.75 and 0.2 respectively. This parameter combination is derived from fitting historical execution data and can effectively describe the growth curve of resource demand over a wide range of data volumes: when the data volume is small, the growth is gradual; when the data volume reaches hundreds of thousands or more, its impact accelerates, but avoids excessively dominating the overall score.

[0066] Furthermore, it should be noted that during the process of quantifying the complexity of the SQL to be executed, the growth patterns of execution resource requirements differ significantly among different syntactic features. Some features exhibit distinctly non-linear effects; for example, the aforementioned multi-table joins, grouping operations, and sorting fields require weighted amplification using exponential or power functions. Other syntactic features, while also increasing computational load, show an approximately linear relationship between resource consumption and feature value growth, thus eliminating the need for complex non-linear transformations.

[0067] Specifically, the syntactic parameters for various syntactic features also include the number of aggregate functions, window functions, user-defined functions, and other functions. Analysis shows that these features have a relatively stable impact on resources during actual execution, and exhibit an approximately linear growth relationship with the feature values. Therefore, the server can directly extract the original feature values ​​and use them as one of the complexity features.

[0068] For example, the number of aggregate functions represents the total number of standard aggregate functions such as "COUNT, SUM, AVG, MIN, MAX" appearing in SQL. Each additional aggregate function typically corresponds to an extra summary calculation, and its computational cost is proportional to the amount of data, showing an overall linear growth trend. Similarly, the number of window functions reflects the number of times window operations such as "ROW_NUMBER, RANK, DENSE_RANK" are called.

[0069] In addition, the number of user-defined functions refers to the number of times user-defined functions identified by the "udf_" prefix or other methods are called in the SQL to be executed. Although these functions may encapsulate complex logic, from the perspective of call frequency, the additional CPU overhead introduced by each call is basically constant, and the overall load increases linearly with the number of calls. The number of other functions covers common scalar functions such as "CONCAT, COALESCE, CAST, CONVERT", which are mainly used for field transformation and concatenation. Their execution cost is low and the effect is cumulative when called repeatedly.

[0070] Based on the above description of the complexity characteristics of the SQL to be executed in the embodiments, please refer to... Figure 1 Next, we will explain step S3: S3, based on the complexity characteristics of the SQL to be executed, obtains the resource configuration information required to execute the SQL.

[0071] The study also found that there is currently a lack of a technical means to establish a precise mapping relationship between the semantic features of SQL and its actual resource requirements. Specifically, when a SQL query is received, it is impossible to automatically determine the key resource configuration parameters required during the execution process, such as the number of Executors, memory size, and CPU cores. Relying on manual experience for configuration is not only time-consuming and labor-intensive, but also difficult to adapt to high-concurrency and variable query scenarios; adopting a uniform high-standard resource configuration will result in a large amount of idle resources and increase computing costs; conversely, if the configuration is too low, it may lead to slow task execution or even failure due to memory overflow.

[0072] It should be noted that the complexity of SQL and its optimal resource allocation are not simply linearly related, but are influenced by the coupling effects of multiple factors, including the number of JOIN operations, the number of grouping fields, the number of sorting fields, the number of aggregate function calls, and the scale of the data involved. These syntactic features each have different impact patterns on resource consumption, and there is a non-linear cumulative effect, making it difficult for traditional rule engines or heuristic algorithms to accurately model them. Therefore, this embodiment also provides the following optional implementation methods for step S3: S3-1 processes the complexity characteristics of the SQL to be executed using a pre-trained resource mapping model to obtain the resource configuration information required to execute the SQL.

[0073] This can be understood as follows: by introducing a pre-trained resource mapping model and utilizing statistical patterns in historical execution data, the above steps learn the implicit relationship between complexity features and resource allocation, thereby achieving automated resource allocation recommendations for new query tasks.

[0074] In practice, the server builds and maintains a historical execution database to record the complete execution context of past query tasks. During this process, historical data is collected, and each record contains three core elements: the complexity characteristics of the historical query (i.e., multi-dimensional syntax and data features extracted from the corresponding SQL, such as join_count, groupby_count, table_size_num, etc.); the actual resource configuration used, including key Spark parameters such as executor_memory_gb, executor_cores, driver_memory_gb, and executor_instances; and execution performance feedback, covering performance metrics such as total task execution time, CPU / memory utilization, success rate, and whether Out of Memory (OOM) occurred. For example, the resource configuration information can be represented as: "optimal_resources": {"executor_memory_gb": 5,"executor_cores": 2,"driver_memory_gb": 1,"executor_instances": 2} Therefore, during the above steps, the model training process is initiated based on the accumulated historical dataset. Using complexity features as input variables and the validated optimal resource allocation as the output target, a regression prediction model is constructed. To improve prediction accuracy and interpretability, a multiple linear regression model can be used as the basic algorithm framework, with the following functional form:

[0075] in, This represents the value of the resource configuration parameter to be predicted (dependent variable), such as executor_memory_gb; Indicates the first The values ​​of each complexity feature; This represents the weight coefficient corresponding to the feature, reflecting its degree of influence on resource allocation; This is the model bias or intercept term, representing the level of basic resource allocation when all feature values ​​are zero; This represents the total number of features involved in the modeling. The goal of training is to find an optimal set of weight coefficients by minimizing the error between the predicted values ​​and the true optimal configuration. and bias terms .

[0076] It is worth noting that, because different resource configuration parameters have different physical meanings and influencing factors, the server trains an independent regression model for each resource configuration parameter. For example, the requirement for executor_memory_gb is mainly affected by the amount of data and JOIN operations, while executor_cores depends more on parallelism and the number of computationally intensive operations. Therefore, the parameter-based modeling approach avoids the interference caused by multi-objective coupling and improves the prediction accuracy of each resource configuration item.

[0077] After the model training is completed, it enters the online recommendation stage. When a new SQL query is received, the server extracts its complexity features and transforms them into a standardized feature vector. Then, it calls the pre-trained resource mapping models to independently predict each resource configuration parameter, ultimately generating a complete resource configuration suggestion scheme.

[0078] Furthermore, the server continuously performs configuration-aware task execution and real-time monitoring during task execution. Specifically, it sets various parameters of the Spark task based on the predicted resource configuration information, submits the SQL to be executed to the Spark cluster, and collects runtime metrics such as execution time, resource utilization, and task status during execution. After the task is completed, the server evaluates the execution effect to determine whether there are any issues such as resource waste (e.g., long-term memory idleness), insufficient resources (e.g., frequent GC or OOM), or execution delays.

[0079] Based on the above description, the server uses a feedback loop mechanism to store the triplet (SQL syntax features to be executed, resource configuration information, and execution effect) of the current execution as a new training sample in the historical database. This closed-loop design allows the model to continuously absorb the latest execution experience and capture the impact of changes in data distribution, business model evolution, and cluster environment changes. Thus, by periodically or trigger-based retraining of the resource mapping model, continuous self-optimization is achieved, ensuring that the resource configuration strategy is always in an optimal state.

[0080] In the above embodiments, each step of the resource allocation optimization method based on query complexity provided in this application has been described in detail. To make the entire method flow clearer and more coherent, the following will combine... Figure 2 The complete execution process of this method is described in its entirety.

[0081] See also Figure 2 The resource configuration optimization method based on query complexity provided in this embodiment can be applied to technical scenarios where front-end Chinese problems are generated into SQL by a large model, executed by a Spark cluster, and the results are returned.

[0082] The server obtains the SQL to be executed; based on this SQL, its complexity characteristics are obtained, where the complexity characteristics represent the complexity of the SQL execution; according to these complexity characteristics, the resource configuration information required for execution is obtained. In the technical implementation, a component called "Intelligent Resource Allocation Mediator Layer" is used to connect the large model with the Spark cluster.

[0083] Specifically, the server first parses the SQL, extracts syntactic parameters of various syntactic features, and transforms them into complexity features according to their respective mapping methods. Then, it processes the complexity features through a pre-trained resource mapping model to obtain recommended configurations and optimal resource configuration information. Subsequently, the task is submitted to the Spark cluster for execution, and execution metrics are monitored in real time. After the task is completed, the actual execution effect is collected, and (the syntactic features of the SQL to be executed, resource configuration information, and execution effect) are used as new samples for the feedback loop to continuously train the model to achieve self-optimization, thus forming a complete closed-loop mechanism from feature extraction, resource configuration, task execution to model evolution.

[0084] Based on the same inventive concept as the query complexity-based resource allocation optimization method provided in this embodiment, this embodiment also provides a query complexity-based resource allocation optimization apparatus. This apparatus includes at least one software functional module that can be stored in memory or embedded in an electronic device. The processor in the electronic device executes the executable module stored in memory. For example, the software functional module and computer program included in this apparatus. Please refer to... Figure 3 Functionally, the device may include: Query language module 11 is used to obtain the structured query language SQL to be executed; The language analysis module 12 is used to obtain the complexity features of the SQL to be executed based on the SQL to be executed, wherein the complexity features characterize the complexity of the SQL to be executed. Resource configuration module 13 is used to obtain the resource configuration information required to execute the SQL based on the complexity characteristics of the SQL to be executed.

[0085] In this embodiment, the query language module 11 is used to implement Figure 1 In step S1, the language analysis module 12 is used to implement Figure 1 In step S2, the resource configuration module 13 is used to implement Figure 1Step S3 in the above process. Therefore, for a detailed description of each of the above modules, please refer to the specific implementation method of the corresponding step.

[0086] Since it shares the same inventive concept as the resource allocation optimization method based on query complexity provided in this embodiment, the device can also implement other steps or sub-steps of the method through the above modules.

[0087] Optionally, the resource configuration module 13 is also specifically used for: By processing the complexity characteristics of the SQL to be executed through a pre-trained resource mapping model, the resource configuration information required to execute the SQL is obtained.

[0088] Optionally, the language analysis module 12 is also specifically used for: Based on the SQL to be executed, determine the syntax parameters of various syntax features; Based on the mapping methods of various syntactic features, the syntactic parameters of various syntactic features are transformed into the complexity features of the SQL to be executed.

[0089] Optionally, the syntax parameters of the various syntax features include the number of multi-table join operations, and the complexity features of the SQL to be executed include the feature values ​​of the multi-table join operations. Language analysis module 12 is also specifically used for: The characteristic value of the multi-table join operation is obtained by exponentiation of the number of times the first parameter is used as the exponent, and the expression is:

[0090] In the formula, This represents the characteristic value of a multi-table join operation. This indicates the number of multi-table join operations, with 1.5 representing the first parameter.

[0091] Optionally, the syntax parameters of various syntax features include the nesting depth of the subquery, and the complexity features of the SQL to be executed include the feature values ​​of the subquery. Language analysis module 12 is also specifically used for: If the nesting depth of the subquery is greater than 2, then the nesting depth of the subquery is used as the exponent of the second parameter for exponentiation to obtain the feature value of the subquery, expressed as:

[0092] If the nesting depth of the subquery is less than or equal to 2, then the nesting depth of the subquery is used as the base for exponentiation to obtain the characteristic value of the subquery, expressed as:

[0093] In the formula, This represents the feature value of the subquery. This indicates the nesting depth of the subquery, and 2 represents the second parameter.

[0094] Optionally, the syntax parameters of various syntax features include the number of grouping operations and the amount of data, and the complexity features of the SQL to be executed include the feature values ​​of the grouping operations. Language analysis module 12 is also specifically used for: Perform a logarithmic operation on the number of fields in the grouping operation to obtain the characteristic value of the grouping operation, expressed as:

[0095] In the formula, The characteristic value representing the grouping operation, Indicates the number of groups. Indicates the amount of data.

[0096] Optionally, the syntax parameters of the various syntax features include the number of sorting fields and the amount of data in the dataset to be accessed, and the complexity features of the SQL to be executed include the feature values ​​of the sorting fields and the feature values ​​of the dataset to be accessed. Language analysis module 12 is also specifically used for: The characteristic value of the sorting field is obtained by exponentiation using the number of sorting fields as the base. The expression is as follows:

[0097] In the formula , representing the feature value of the sorting field. Indicates the number of sorting fields; The feature values ​​of the dataset are obtained by exponentiation using the data size of the dataset as the base. The expression is as follows:

[0098] In the formula, This represents the feature values ​​of the dataset to be accessed. This indicates the amount of data in the dataset to be accessed. and This represents a preset constant.

[0099] In addition, the functional modules in the various embodiments of this application can be integrated together to form an independent part, or each module can exist independently, or two or more modules can be integrated to form an independent part.

[0100] It should also be understood that if the above embodiments are implemented as software functional modules and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application.

[0101] Therefore, this embodiment also provides a storage medium, which is a computer-readable storage medium. This storage medium stores a computer program, which, when executed by a processor, implements the query complexity-based resource allocation optimization method provided in this embodiment. The storage medium can be any medium capable of storing program code, such as a USB flash drive, external hard drive, read-only memory (ROM), random access memory (RAM), magnetic disk, or optical disk.

[0102] This embodiment provides an electronic device that implements a resource allocation optimization method based on query complexity. For example... Figure 4 As shown, the electronic device may include a processor 22 and a memory 21. The memory 21 stores a computer program, and the processor reads and executes the computer program in the memory 21 corresponding to the above embodiments to implement the query complexity-based resource allocation optimization method provided in this embodiment.

[0103] See also Figure 4 The electronic device also includes a communication unit 23. The memory 21, processor 22 and communication unit 23 are electrically connected to each other directly or indirectly through system bus 24 to realize data transmission or interaction.

[0104] The memory 21 can be an information recording device based on any electronic, magnetic, optical, or other physical principles, used to record execution instructions, data, etc. In some embodiments, the memory 21 can be, but is not limited to, volatile memory, non-volatile memory, memory drive, etc.

[0105] In some embodiments, the volatile memory may be random access memory (RAM); in some embodiments, the non-volatile memory may be read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory, etc.; in some embodiments, the storage drive may be a disk drive, solid-state drive, any type of storage disk (such as optical disc, DVD, etc.), or similar storage media, or a combination thereof.

[0106] The communication unit 23 is used to send and receive data over a network. In some embodiments, the network may include a wired network, a wireless network, a fiber optic network, a telecommunications network, an intranet, the Internet, a local area network (LAN), a wide area network (WAN), a wireless local area network (WLAN), a metropolitan area network (MAN), a public switched telephone network (PSTN), a Bluetooth network, a ZigBee network, or a near field communication (NFC) network, or any combination thereof. In some embodiments, the network may include one or more network access points. For example, the network may include wired or wireless network access points, such as base stations and / or network switching nodes, through which one or more components of the service request processing system can connect to the network to exchange data and / or information.

[0107] The processor 22 may be an integrated circuit chip with signal processing capabilities, and may include one or more processing cores (e.g., a single-core processor or a multi-core processor). By way of example only, the processor described above may include a Central Processing Unit (CPU), an Application Specific Integrated Circuit (ASIC), an Application Specific Instruction-set Processor (ASIP), a Graphics Processing Unit (GPU), a Physics Processing Unit (PPU), a Digital Signal Processor (DSP), a Field Programmable Gate Array (FPGA), a Programmable Logic Device (PLD), a controller, a microcontroller unit, a Reduced Instruction Set Computing (RISC) computer, or a microprocessor, or any combination thereof.

[0108] Understandable. Figure 4 The structure shown is for illustrative purposes only. Electronic devices may also have more advanced features. Figure 4 Showing more or fewer components, or having with Figure 4 The different configurations shown. Figure 4 The components shown can be implemented using hardware, software, or a combination thereof.

[0109] It should be understood that the apparatus and methods disclosed in the above embodiments can also be implemented in other ways. The apparatus embodiments described above are merely illustrative. For example, the flowcharts and block diagrams in the accompanying drawings show the architecture, functionality, and operation of possible implementations of apparatus, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions marked in the blocks may occur in a different order than those marked in the drawings. For example, two consecutive blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram and / or flowchart, and combinations of blocks in block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or action, or using a combination of dedicated hardware and computer instructions.

[0110] The above descriptions are merely various embodiments of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

Claims

1. A resource allocation optimization method based on query complexity, characterized in that, The method includes: Obtain the Structured Query Language (SQL) code to be executed; Based on the SQL to be executed, the complexity characteristics of the SQL to be executed are obtained, wherein the complexity characteristics characterize the complexity of the SQL to be executed. Based on the complexity characteristics of the SQL to be executed, the resource configuration information required to execute the SQL is obtained.

2. The resource allocation optimization method based on query complexity according to claim 1, characterized in that, Based on the complexity characteristics of the SQL to be executed, the resource configuration information required to execute the SQL is obtained, including: The complexity characteristics of the SQL to be executed are processed by a pre-trained resource mapping model to obtain the resource configuration information required to execute the SQL.

3. The resource allocation optimization method based on query complexity according to claim 1 or 2, characterized in that, Based on the SQL to be executed, the complexity characteristics of the SQL to be executed are obtained, including: Based on the SQL to be executed, the syntax parameters of various syntax features are determined. Based on the mapping methods of the various grammatical features, the grammatical parameters of the various grammatical features are transformed into the complexity features of the SQL to be executed.

4. The resource allocation optimization method based on query complexity according to claim 3, characterized in that, The syntax parameters of the various syntax features include the number of multi-table join operations, and the complexity features of the SQL to be executed include the feature values ​​of the multi-table join operations. Based on the mapping methods of the various syntactic features, the syntactic parameters of the various syntactic features are transformed into the complexity features of the SQL to be executed, including: The number of multi-table join operations is used as the exponent of the first parameter and raised to a power of 1 to obtain the characteristic value of the multi-table join operation, expressed as: In the formula, This represents the feature value of the multi-table join operation. The number of times the multi-table join operation is performed is represented, and 1.5 represents the first parameter.

5. The resource allocation optimization method based on query complexity according to claim 3, characterized in that, The syntax parameters of the various syntax features include the nesting depth of the subqueries, and the complexity features of the SQL to be executed include the feature values ​​of the subqueries. Based on the mapping methods of the various syntactic features, the syntactic parameters of the various syntactic features are transformed into the complexity features of the SQL to be executed, including: If the nesting depth of the subquery is greater than 2, then the nesting depth of the subquery is used as the exponent of the second parameter for exponentiation to obtain the feature value of the subquery, expressed as: If the nesting depth of the subquery is less than or equal to 2, then the nesting depth of the subquery is used as the base for exponentiation to obtain the feature value of the subquery, expressed as: In the formula, This represents the feature value of the subquery. 2 represents the nesting depth of the subquery, and 2 represents the second parameter.

6. The resource allocation optimization method based on query complexity according to claim 3, characterized in that, The syntax parameters of the various syntax features include the number of grouping operations and the amount of data; the complexity features of the SQL to be executed include the feature values ​​of the grouping operations. Based on the mapping methods of the various syntactic features, the syntactic parameters of the various syntactic features are transformed into the complexity features of the SQL to be executed, including: The characteristic value of the grouping operation is obtained by performing a logarithmic operation on the number of fields in the grouping operation, and the expression is: In the formula, The characteristic value representing the grouping operation, Indicates the number of groupings. This indicates the amount of data.

7. The resource allocation optimization method based on query complexity according to claim 3, characterized in that, The syntax parameters of the various syntax features include the number of sorting fields and the amount of data in the dataset to be accessed; the complexity features of the SQL to be executed include the feature values ​​of the sorting fields and the feature values ​​of the dataset to be accessed. Based on the mapping methods of the various syntactic features, the syntactic parameters of the various syntactic features are transformed into the complexity features of the SQL to be executed, including: The characteristic value of the sorting field is obtained by exponentiation using the quantity of the sorting field as the base, and the expression is: In the formula , representing the feature value of the sorting field. Indicates the number of sorting fields; The feature values ​​of the dataset to be accessed are obtained by exponentiation using the data size of the dataset as the base, and the expression is as follows: In the formula, This represents the feature values ​​of the dataset to be accessed. This indicates the amount of data in the dataset to be accessed. and This represents a preset constant.

8. A resource allocation optimization method based on query complexity, characterized in that, The method includes: The query language module is used to obtain the structured query language SQL to be executed; The language analysis module is used to obtain the complexity features of the SQL to be executed based on the SQL to be executed, wherein the complexity features characterize the complexity of the SQL to be executed. The resource configuration module is used to obtain the resource configuration information required to execute the SQL based on the complexity characteristics of the SQL to be executed.

9. A storage medium, characterized in that, The storage medium stores a computer program, which, when executed by a processor, implements the resource allocation optimization method based on query complexity as described in any one of claims 1-7.

10. An electronic device, characterized in that, The electronic device includes a processor and a memory, the memory storing a computer program, which, when executed by the processor, implements the resource allocation optimization method based on query complexity as described in any one of claims 1-7.

Images