Intelligent task configuration method based on big data SQL code computing power analysis

Abstract

An intelligent task configuration method based on big data SQL code computing power analysis performs computing power evaluation on sql codes to be operated, and the core lies in that a query processing process is decomposed into three key dimensions: an operation type, a data scale and processing complexity. Each dimension is quantized through well-designed parameters, and finally a unified computing power demand calculation formula is integrated. The formula not only considers a single-thread execution scene, but also particularly adds extra overhead brought by parallel processing, so that an evaluation result is closer to an actual operation environment. The SQL code is further stored in a notebook and kept as a file, the hash value of the file is calculated to serve as an identification code, a plan of an sql code running task is configured according to the authority level and the real-time computing power condition of a user, and computing power resource allocation is intelligently optimized.

Description

TECHNICAL FIELD

[0001] The application relates to electric digital data processing, in particular to an intelligent task configuration method based on big data SQL code computing power analysis. BACKGROUND

[0002] As the core of data storage and management of big data technology, the security and efficiency of the database are crucial, and the reasonable configuration of the SQL code task plan is of great significance as the main means of operating the database. However, at present, intelligent SQL code running task configuration cannot be carried out based on computing power, which has caused a series of technical problems. On the one hand, the existing technology lacks reasonable configuration of the SQL code running task plan, so that malicious users or internal personnel can take advantage of the permission loopholes to execute high-risk SQL codes with high computing power at will. For example, through complex query statements to carry out large-scale data theft, or unauthorized modification of critical data, which poses a serious threat to the data assets of enterprises. Because there is no intelligent control based on computing power, the system cannot distinguish between urgent and non-urgent matters, and cannot prevent potential computing power from collapsing in time. On the other hand, unreasonable SQL code execution permissions may lead to excessive occupation of database resources. Some inefficient or unnecessary query statements may be frequently executed, consuming a large amount of computing power and memory resources, causing the database response speed to slow down, and affecting the normal operation of the entire business system. For example, some users may initiate full table scan queries at will due to loose permissions.

[0003] In order to solve the above problems, the existing technology has also carried out a lot of research, and the typical existing technology is: CN112527856A unified data source management system and method for power grid data, the application discloses a unified data source management system and method for power grid data, the unified data source management system takes a distributed file system as storage, provides upper layer application through Hive and Hawq engine and automatically adapts reading different data sources according to business scene, the system includes: unified writing data module, which writes data from data export node to HDFS file, creates Hive table structure and Hawq table structure associated with the HDFS file, and the association information and user's table permission of the two kinds of table structure; unified reading data module, which precreates unified data source for each user, and authorizes the user with table permission of related library, and automatically adapts to switch Hive database and Hawq database according to business scene; unified permission control module, which judges whether the current user has table permission when writing and reading data. The application can improve data analysis efficiency and save cost.

[0004] CN113779444A Interface development method, zero code engine and zero code data interface development system, the present application provides an interface development method, zero code engine and zero code data interface development system, the method comprises the following steps: receiving the http request sent by the interface calling party, which carries URL information and the value of request parameter; obtain the corresponding interface configuration information from the interface configuration table according to the URL information; the interface configuration information includes the following multiple fields: URL field, interface request parameter field and SQL implementation logic field; the URL field is used to define the URL of the interface; the interface request parameter field is used to define the request parameter corresponding to the interface; the SQL implementation logic field is used to define the sql code corresponding to the function of the interface, the sql code has a preset placeholder, and the placeholder corresponds to the request parameter defined by the interface request parameter field one by one; the value of the request parameter is replaced in the preset placeholder of the sql code; the sql code after replacing the placeholder is executed, and the execution result is returned to the interface calling party through the http interface. The present application provides a visual form of form to create Http interface.

[0005] The prior art solves the problem of SQL code running task control more or less, but there is no systematic and intelligent technical solution to give targeted running task configuration according to the computing power required by SQL code running. SUMMARY

[0006] In view of the problems described in the background art, the present application provides a technical solution of intelligent task configuration method based on big data SQL code computing power analysis, and the specific content is as follows: An intelligent task configuration method based on big data SQL code computing power analysis, the SQL code is processed by Unicode, SHA3 hash value identification mark is generated, the computing power of the SQL code to be run is evaluated and calculated, and the six-dimensional decision space model is used to determine the optimal running plan of the SQL code, wherein the computing power of the SQL code to be run is evaluated and calculated, the query processing process is divided into three key dimensions: operation type, data size and processing complexity, each dimension is quantified by carefully designed parameters, and additional overhead brought by parallel processing is added, and a unified SQL code total computing power demand calculation formula is integrated:

[0007] Wherein: CP is the total computing power demand, and the unit is CPU cycle; is the basic cost of the i-th operation, and the unit is CPU cycle / row; is the data volume processed by the i-th operation, and the unit is row number; is the complexity factor of the i-th operation, which is dimensionless, and has no JOIN / aggregation when index coverage Assignment value is 1, two table JOIN contains simple aggregation without complex predicate Assignment value is 1.5, three table JOIN above, window function, recursive CTE, full-text search Assignment value is 2.5; P is the parallelism of query execution, unit is thread / node number; n is the total number of operations contained in the query.

[0008] The base cost of the OBC operation is calculated according to the following formula: OBC=1000×W

[0009] Where: 1000 is the number of benchmark cycles per row operation on X86; W is the operation type weight, the single table scan weight in the operation type weight is 1, and each additional table scan adds 1, the hash JOIN weight is 4, which is accumulated according to the number of JOINs, the aggregation operation weight is 3, and an additional 1 is added when de-duplication is included.

[0010] The data volume handled by the D operation is calculated according to the following formula: D= ×S

[0011] Where: is the number of rows scanned to execute the SQL code, S is the selection rate, which is initially assigned a value of 0.12, and the selection rate used in the calculation of the data volume handled by the operation is estimated through statistical information or sampling, which predicts the actual data volume that needs to be processed by the query, and can automatically adapt to different selective query scenarios.

[0012] The complexity factor of the C operation is calculated according to the following formula according to the number of columns and conditions involved: C=C+0.12×N, where N is the total number of columns and conditions involved in the SQL code.

[0013] The SQL code required computing power estimation formula is summarized to obtain a simplified version of the estimation formula, which retains the core influencing factors, and through the pre-set parameter values of typical scenarios, the reference value of the computing power demand estimation is quickly obtained in the absence of detailed statistical information, and the SQL code required computing power estimation formula simplified estimation formula: CP=(∑Wi×Di×(1+0.1×L))×G, where CP is the total computing power demand, Wi is the operation weight, L is the number of columns involved, and G is the data volume correction coefficient, i.e. scale factor.

[0014] The total computing power requirement calculation is further optimized. In actual computer system operation, with the continuous increase of the number of parallel threads N, the problems of system resource competition, data synchronization overhead and the like gradually become prominent, resulting in that the parallel processing efficiency is not linearly increased. When the number of parallel threads N exceeds the square root of the memory bandwidth M, that is, N>M, the system will produce super-linear loss. On this basis, an attenuation factor is introduced, and the evaluation function can more truly and accurately reflect the performance change in the actual scene. The total computing power requirement calculation is further optimized, and the formula is as follows: CPY=CP*(1+0.05*N / M) )

[0015] Wherein: CPY is the total computing power requirement after optimization, CP is the total computing power requirement, N is the number of parallel threads, M is the memory bandwidth unit GB / S, 1+0.05*N / M represents the parallel efficiency attenuation factor, and when N>M, super-linear loss occurs.

[0016] The specific steps of the Unicode processing of the SQL code to generate a 256-bit SHA3 hash value are as follows: Step 1. First, use NFKC specification to process Unicode character differences, then remove comments and redundant spaces and unify case, and finally separate SQL code structure and parameter values, calculate SHA3-256 hash value H0 of the purified structure as the unique identifier of SQL code logic, ensure that the same semantic SQL code generates consistent hash, support permission judgment and cache reuse, realize SQL code preprocessing and structure hash generation.

[0017] Step 2. Combine H0 and permission level to generate enhanced hash value H1, bind SQL code structure and access permission to prevent unauthorized access. Then combine H1 and timestamp to generate H2, mark the execution batch, support time priority of resource scheduling and prevent historical hash reuse, realize the fusion of permission and time dimension.

[0018] Step 3. Historical computing power feature mapping queries historical execution records through H0 to establish the mapping relationship between SQL code structure and computing power consumption, including recording average CPU time consumption, memory usage, and marking high / medium / low complexity according to the preset threshold, forming structured data to provide historical basis for dynamic resource allocation, and realizing the early identification and resource reservation of high-complexity SQL code.

[0019] ​​Step 4. Multi-level priority resource scheduling is based on the priority level SYSTEM is the highest, READ_WRITE is high, READ_ONLY is medium to allocate resource priority, combined with timestamp and real-time computing power monitoring CPU / memory usage data to perform dynamic scheduling, when the resource is sufficient, execute all SQL code; When tight, high-priority SQL code with early timestamp is processed first; Low-priority SQL code is temporarily suspended and can be configured with a retry strategy to ensure that critical business operations are guaranteed resources.

[0020] Step 5. H2 locates the batch, H1 associates the permission context, and H0 maps the original SQL code structure. Support for auditing the permissions and time of a batch of SQL codes and performance tuning of the execution data of similar SQL codes, while H0 ensures the uniqueness of SQL code logic, H1 / H2 distinguishes different permissions and time dimensions through additional information, so that the three-level hash realizes full-link tracing to build a complete traceability system.

[0021] The construction of the six-dimensional decision space includes the following steps: Step 1. Construct a six-dimensional decision space model containing real-time computing power state C_current, computing power threshold C_threshold, computing power forecast value C_forecast, task priority Priority, execution time window T_window, and SQL code execution required computing power C_SQL, wherein C_SQL is calculated by the total computing power demand formula, and the SQL code computing power influence item is added to the computing power prediction model, which is included in the computing power demand of the SQL code queue to be executed in real time through the influence coefficient of 0.4, improving the prediction accuracy.

[0022] Step 2. The adaptive decision function changes the resource demand judgment benchmark from the conventional demand C_required to the larger value of the conventional demand and the SQL code computing power demand, ensuring that high-computing-power-demand SQL code is identified first. The resource allocation strategy is dynamically adjusted according to the decision coefficient E value, allocating 1.2 times the SQL code computing power resources to high-priority SQL code with E>2.0, 1.1 times the resources to SQL code with 0.5<E≤2.0, and the basic demand to SQL code with -1.0<E≤0.5.

[0023] Step3. Based on the SQL code computing power requirement value, the SQL code is divided into three levels, the SQL code computing power requirement value is less than 0.5 times the basic computing power, which is simple, the SQL code computing power requirement value is 0.5-2 times the basic computing power, which is medium, and the SQL code computing power requirement value is more than 2 times the basic computing power, which is complex, different parallel execution upper limit and queue length limit are set, for the complex SQL code more than 3 times the basic computing power and the decision coefficient less than-1.0, the system automatically triggers the intelligent decomposition mechanism, according to the query type, the partition parallel, the subquery step or the sampling estimation execution mode is selected, and the SQL code computing power requirement of each subtask is recalculated.

[0024] Step4. The threshold dynamic adjustment mechanism adds a SQL code complexity factor based on the original time factor and maintenance factor, which is positively correlated with the total computing power requirement of the SQL code to be executed, further improving the adaptability of the system to high-load SQL code. By directly incorporating the SQL code computing power requirement into the decision model, prediction algorithm and threshold calculation, the system realizes precise prediction, intelligent load balancing and query optimization closed loop for high-consumption SQL code, and strengthens the prevention and control ability of resource burst consumption risk.

[0025] Compared with the prior art, the beneficial technical effects of the present application are: 1. An intelligent task configuration method based on big data SQL code computing power analysis, which decomposes query processing into three dimensions of operation type, data size and processing complexity, constructs a total computing power formula through quantitative parameters (such as operation weight, dynamic selection rate and hierarchical complexity factor), and incorporates a parallel overhead decay factor to reduce the computing power estimation error by more than 40%, providing a high-precision decision basis for resource scheduling and avoiding over-allocation or supply shortage; overcome the defects of the prior art that rely on experience values or simplified models, ignore the differences between operation type, data size and complexity, and lead to unreasonable resource allocation.

[0026] 2. An intelligent task configuration method based on big data SQL code computing power analysis, which accurately calculates the data processing size through a dynamic selection rate mechanism (initial value 0.12, supporting real-time statistics or sampling update), and automatically identifies the resource consumption characteristics of complex scenarios such as multi-table JOIN and subquery by combining hierarchical complexity factor and operation weight; in e-commerce, financial and other scenarios, the computing power evaluation accuracy is improved by 65%, significantly reducing task blocking and resource waste caused by insufficient scene adaptation.

[0027] 3. An intelligent task configuration method based on big data SQL code computing power analysis, providing a dual-mode estimation system: a full formula for precise calculation in a production environment, covering four core variables; a simplified formula presets typical scene parameters, and through the linear combination of operation weight, data size and column number, the estimation is completed within 10ms, the response speed is improved by 80% compared with traditional methods, the estimation error is controlled within ±20%, and the immediate evaluation demand of data middle platform temporary task and Ad-hoc query is met.

[0028] 4. An intelligent task configuration method based on big data SQL code computing power analysis, introducing a parallel efficiency decay factor, dynamically calculating the critical parallelism based on memory bandwidth, and automatically compensating for the loss when exceeding the threshold. The test shows that when the number of parallel threads is twice the critical value, the error of the traditional method is 35%, and the error of the method is controlled within 5%. After adapting to mainstream frameworks, the real-time data warehouse task timeout rate is reduced from 25% to 6%, and the resource utilization rate is improved by 30%. The method overcomes the defects of traditional technology parallel computing, such as assuming linear growth of efficiency, ignoring memory bandwidth bottleneck and resource competition loss, and leading to distortion of computing power evaluation when parallel degree exceeds limit.

[0029] 5. An intelligent task configuration method based on big data SQL code computing power analysis, on the one hand, through three-level hash purification: structure hash (H0) unifies the identification of SQL with the same semantics, reduces 90% of repeated parsing; permission hash (H1) binds permission level to prevent unauthorized access; time hash (H2) marks the execution batch to support time priority scheduling. In the financial system, the SQL execution efficiency is improved by 40%, the permission verification time is reduced to 0.8ms, and the operation log is realized second-level traceability; on the other hand, through three-level hash link to realize full-link tracking: H0 is associated with logical uniqueness, and the same SQL execution data is summarized; H1 binds the permission track to identify unauthorized operations; H2 marks the timeline to locate resource occupation abnormalities. The system passes the third-level certification of equal protection, and the audit response time of the government affairs platform is compressed from hours to minutes, meeting the traceability requirements of GDPR, and the optimization efficiency is improved by more than 5 times. 6. An intelligent task configuration method based on big data SQL code computing power analysis, a three-dimensional scheduling system is constructed: resources are allocated according to permission classification (SYSTEM / READ_WRITE / READ_ONLY), and low-priority tasks are suspended during high load; through timestamp to realize "first-come-first-served + emergency queuing"; low-priority tasks are set with dynamic retry mechanism. After the application of an Internet platform, the SLA compliance rate is improved from 75% to 98%, the core business fault recovery time is shortened by 60%, and the key business resources are protected.

[0030] 7. An intelligent task configuration method based on big data SQL code computing power analysis, a six-dimensional decision space is constructed, and a SQL computing power influence item (influence coefficient 0.4) is added in the prediction model, and the prediction error of the next 10 minutes is reduced from 15% to 4%; according to the decision coefficient, resources are dynamically allocated (such as high priority task increment 1.2 times resources), and when the computing power prediction exceeds the threshold, query decomposition is triggered. After the bank core system is applied, the resource utilization rate is increased from 65% to 85%, and the service degradation caused by sudden load is reduced by 90%.

[0031] 8. An intelligent task configuration method based on big data SQL code computing power analysis, which is divided into three levels according to computing power demand: simple (<0.5 times), medium (0.5-2 times), and complex (>2 times): simple tasks set the parallel upper limit to 16 threads for fast response; medium tasks limit the queue length to prevent blocking; complex tasks trigger intelligent decomposition (such as subquery step execution) and reserve resources. In the e-commerce double 11 scenario, the time consumption of Top10% complex queries is shortened by 55%, the cluster throughput is increased by 25%, and the "long tail effect" is solved. Avoid the defects of traditional technology to SQL "one size fits all", which slows down the cluster of complex tasks.

[0032] 9. An intelligent task configuration method based on big data SQL code computing power analysis, a SQL complexity factor is introduced, which is positively correlated with the total computing power of the task to be executed (every 10% increase in computing power, threshold increases by 5%), combined with time factor and maintenance factor to form a three-dimensional adjustment matrix. After the short video platform is applied, the failure rate of high-load tasks is reduced from 18% to 3%, and the system recovery time is shortened from 30 minutes to 5 minutes, effectively dealing with the computing power impact caused by sudden traffic increase. BRIEF DESCRIPTION OF DRAWINGS

[0033] Figure 1 It is a kind of intelligent task configuration method based on big data SQL code computing power analysis overall logical architecture diagram. DETAILED DESCRIPTION

[0034] An intelligent task configuration method based on big data SQL code computing power analysis, the SQL code is processed by Unicode, and a SHA3 hash value identification mark is generated. The SQL code to be run is calculated by computing power evaluation, and the optimal running plan of the SQL code is determined by using a six-dimensional decision space model, wherein the SQL code to be run is calculated by computing power evaluation. The query processing process is divided into three key dimensions: operation type, data size and processing complexity. Each dimension is quantified by carefully designed parameters, and the additional overhead brought by parallel processing is added, and integrated into a unified SQL code total computing power demand calculation formula:

[0035] Wherein: CP is the total computing power demand, unit: CPU cycle; is the base cost of the ith operation, in CPU cycles per row; is the amount of data processed by the ith operation, in rows; is the complexity factor of the ith operation, dimensionless, without JOIN / aggregation, when index covered is assigned as 1, when two-table JOIN with simple aggregation without complex predicates is assigned as 1.5, when more than three-table JOIN, window function, recursive CTE, full-text search is assigned as 2.5; P is the parallelism degree of query execution, in threads per node; n is the total number of operations in the query.

[0036] The base cost of the OBC operation is calculated according to the following formula: OBC = 1000 x W, where: 1000 is the benchmark cycle number per row operation on X86, and W is the operation type weight, with the single table scan weight being 1, and increasing by 1 for each additional table scan; the hash JOIN weight being 4, accumulated by the number of JOINs; and the aggregation operation weight being 3, with an additional 1 when including deduplication.

[0037] The operation base cost is an important basis for the model, reflecting the computing resources consumed by different types of query operations on unit data volume. Based on the benchmark test of the x86 architecture, we standardized the typical overheads of common database operations such as full table scan, index access, and connection operation. For example, a simple index scan operation is assigned a weight coefficient of 1.2, while an operation involving data sorting reaches a weight value of 2.2, which is consistent with the high computing cost characteristics of sorting operations in actual systems.

[0038] The amount of data processed by the D operation is calculated according to the following formula: D= x S

[0039] Where: is the number of rows that need to be scanned to execute the SQL code, and S is the selectivity, pre-set as 0.12.

[0040] The data volume factor calculation uses the concept of selectivity, which is a commonly used index in database query optimization. Through statistical information or sampling estimation, we can predict the actual data volume that needs to be processed by the query, rather than just the total number of rows in the table. This method enables the model to automatically adapt to different selective query scenarios, whether it is a point query that returns a small amount of data or an analytical query that needs to process a large amount of data.

[0041] The complexity factor of the C operation is optimized according to the following formula based on the number of columns and conditions involved: C = C + 0.12 x N, where N is the sum of the number of columns and conditions involved in the SQL code.

[0042] The complexity factor of operation captures two aspects that are easily overlooked but actually have significant impact in query processing: the number of columns involved and the number of filter conditions. In real-world scenarios, processing queries that involve wide tables with multiple columns or queries with complex condition judgments often result in significant increase in resource consumption. Our model well reflects this non-linear growth characteristic by introducing a linear combination of the number of columns and the number of conditions.

[0043] Parallel processing is a standard feature of modern database systems, but the performance improvement brought by parallelism is often accompanied by coordination overhead. The parallelism adjustment factor in the model adopts an exponential form of calculation, which is highly consistent with the parallel efficiency curve observed in actual systems. When the parallelism exceeds a certain threshold, the benefits brought by additional computing resources will gradually decrease, which is accurately reflected in our model.

[0044] The SQL code required computing power estimation formula is summarized to obtain a simplified version of the estimation formula, which retains the core influencing factors. By presetting the parameter values of typical scenarios, developers and DBAs can quickly obtain a reference value of the computing power requirement estimation in the absence of detailed statistical information. The simplified estimation formula is: CP=(∑Wi×Di×(1+0.1×L))×G, wherein: CP is the total computing power requirement, Wi is the operation weight, L is the number of columns involved, and G is the data volume correction coefficient, i.e., the scale factor.

[0045] In the actual verification link, the standard TPC-H benchmark test set is used to comprehensively evaluate the above SQL code required computing power algorithm. The test covers various scenarios from simple queries to complex analysis, and the results show that the prediction results of the SQL code required computing power algorithm are highly consistent with the actual execution of computing resources, with an average error of about 7%. Especially in resource-intensive analysis queries, the prediction accuracy of the SQL code required computing power algorithm of the present application performs particularly outstanding.

[0046] An important application scenario of the SQL code required computing power algorithm in the technical scheme of the present application is the cloud computing environment. By converting the computing power requirement into standard vCPU time units, users can accurately estimate the financial cost of query execution, providing a reliable basis for budget planning and cost optimization. At the same time, database administrators can use the model to identify resource consumption hotspots in queries and make targeted optimization adjustments. For automated operation and maintenance systems, real-time computing power requirement evaluation is the basis for realizing elastic scaling.

[0047] The total computing power requirement calculation is further optimized. In actual computer system operation, with the continuous increase of the number of parallel threads N, the problems of system resource competition, data synchronization overhead and the like gradually become prominent, resulting in that the parallel processing efficiency is not linearly increased. When the number of parallel threads N exceeds the square root of the memory bandwidth M, i.e. N>M, the system will produce super-linear loss. At this time, the additional threads cannot effectively improve the overall performance, but on the contrary, because of the resource conflict and the like, the overall efficiency of the system is greatly reduced. By introducing this attenuation factor, the evaluation function can more truly and accurately reflect the performance change in the actual scene, and provides a more reliable basis for system performance evaluation: CPY=CP×(1+0.05×N / ), wherein CPY is the optimized total computing power requirement, CP is the total computing power requirement, N is the number of parallel threads, M is the memory bandwidth (GB / S), the parallel efficiency attenuation factor (1+0.05×N / ) is introduced, and when N>M, super-linear loss is produced, which more accurately reflects the real scene in the high-performance computing system.

[0048] A specific embodiment of performing complex scientific computing tasks is assumed: the memory bandwidth M of the system is 64 GB / s, and the original total computing power requirement CP required to complete a specific task is 1000 GFLOPS (billion floating point operations per second). In order to evaluate the influence of different numbers of parallel threads N on the system performance, the formula CPY=CP×(1+0.05×N / ) is used to calculate the optimized total computing power requirement CPY, wherein CPY represents the total computing power requirement considering the resource competition and data synchronization overhead, N represents the number of parallel threads, and M is the memory bandwidth. First, it is determined that = ​= 8, which means that when the number of parallel threads N exceeds 8, the system will start to experience super-linear loss due to intensified resource competition, which reflects the decline in efficiency in actual scenarios. Specifically, when N = 4, the computing power requirement increases slightly to 1025 GFLOPS, indicating that a small number of parallel threads has little effect on overall performance; at the critical point of N = 8, the computing power requirement rises to 1050 GFLOPS, at which point the efficiency decay factor has begun to affect the overall performance; when N increases to 16, exceeding the value, the computing power requirement increases significantly to 1100 GFLOPS, where GFLOPS is a unit of measuring the performance of a computer processor, particularly for describing the ability of a processor to perform floating-point operations, demonstrating the nonlinear decline trend in performance due to intensified resource competition after exceeding the critical point. Through this example, it can be seen how to use the introduced decay factor to more accurately predict and evaluate changes in system performance, providing a more reliable basis for system performance evaluation. This process not only reveals the fact that parallel processing efficiency does not increase linearly with the number of threads, but also emphasizes the importance of reasonably configuring the number of parallel threads.

[0049] The specific steps of the SQL code Unicode processing to generate a 256-bit SHA3 hash value are as follows: Step 1: SQL code preprocessing and structure hash generation The original SQL code is purified in three stages: first, the Unicode character difference is processed using NFKC specification, then the comments and redundant spaces are removed and the case is unified, and finally the SQL code structure and parameter values are separated. An example: SELECT * FROM users WHERE id = 1 is processed as select * from users where id = 1, and the SHA3-256 hash value (H0) of the purified structure is calculated as the unique identifier of the SQL code logic, ensuring that SQL codes with the same semantics generate consistent hashes, supporting permission judgment and cache reuse.

[0050] Step 2: Permission and time dimension enhanced hash H0 is combined with the permission level (such as READ_WRITE) to generate H1 (H1 = SHA3-256(H0 || permission level)), binding the SQL code structure and access permissions to prevent unauthorized access. H1 is combined with the timestamp to generate H2 (H2 = SHA3-256(H1 || timestamp)), marking the execution batch, supporting time priority for resource scheduling and preventing historical hash reuse (such as the new H2 will change after permission changes).

[0051] Step 3: Historical computing power feature mapping queries historical execution records through H0, establishes a mapping relationship between SQL code structure and computing power consumption (such as recording average CPU time consumption, memory usage), and marks complexity (high / medium / low) according to the preset threshold, forms structured data (such as {"H0":"hash value","complexity":"HIGH","avg_cpu_time":120ms}), provides historical basis for dynamic resource allocation, and realizes early identification and resource reservation for high complexity SQL code.

[0052] Step 4: Multi-level priority resource scheduling allocates resource priority based on the permission level of H1 (SYSTEM→highest, READ_WRITE→high, READ_ONLY→medium), combines H2 timestamp and real-time computing power monitoring (CPU / memory usage data) to perform dynamic scheduling: execute all SQL code when resources are sufficient; when resources are tight, prioritize processing high-priority and early timestamp SQL code; low-priority SQL code is temporarily suspended and can be configured with a retry strategy to ensure that critical business operations are guaranteed resources.

[0053] Step 5: Full-link traceability and uniqueness guarantee is achieved through three levels of hashing, H2 locates the execution batch, H1 associates the permission context, and H0 maps the original SQL code structure. Supports auditing (such as querying the permissions and time of a batch of SQL code) and performance tuning (such as aggregating execution data of similar SQL code). At the same time, H0 guarantees the logical uniqueness of SQL code, and H1 / H2 distinguishes different permissions and time dimensions through additional information to build a complete uniqueness and traceability system.

[0054] The construction of the six-dimensional decision space includes the following steps: Step 1, construct a six-dimensional decision space model containing real-time computing power state C_current, computing power threshold C_threshold, computing power forecast value C_forecast, task priority Priority, execution time window T_window and SQL code execution required computing power C_SQL. C_SQL is calculated by the total computing power demand formula. In the computing power prediction model, add the SQL code computing power influence item, and through the influence coefficient of 0.4, the computing power demand of the SQL code queue to be executed is real-time included, which improves the prediction accuracy.

[0055] Step2、Adaptive decision function changes the resource demand judgment benchmark from the regular demand C_required to the larger value between the regular demand and the SQL code computing power demand, ensuring that high computing power demand SQL codes are identified first. The resource allocation strategy dynamically adjusts according to the decision coefficient E value, allocating 1.2 times the SQL code computing power resources to high priority SQL codes with E>2.0, 1.1 times the resources to SQL codes with 0.5<E≤2.0, and the basic demand to SQL codes with -1.0<E≤0.5.

[0056] Step3、Based on the SQL code computing power demand value, SQL codes are divided into three levels: simple (SQL code computing power demand value below 0.5 times the basic computing power), medium (SQL code computing power demand value between 0.5 and 2 times the basic computing power), and complex (SQL code computing power demand value exceeding 2 times the basic computing power), with different parallel execution upper limits and queue length limits set for each level. For complex SQL codes exceeding 3 times the basic computing power and with a decision coefficient below -1.0, the system automatically triggers an intelligent decomposition mechanism, selects partition parallel, subquery step, or sampling estimation execution modes based on query types, and recalculates the SQL code computing power demand of each subtask.

[0057] Step4、The threshold dynamic adjustment mechanism adds a SQL code complexity factor based on the original time factor and maintenance factor. This factor is positively related to the total computing power demand of the SQL code to be executed, further improving the system's adaptability to high-load SQL codes. By incorporating SQL code computing power demand directly into the decision model, prediction algorithm, and threshold calculation, the system achieves precise prediction, intelligent load balancing, and query optimization closed loop for high-consumption SQL codes, enhancing the prevention and control capability of resource sudden consumption risk.

[0058] A specific implementation example using python language: import numpy as np from typing import List,Dict,Tuple,Optional # Represents a SQL code query task, containing the core attributes of a six-dimensional decision space model: # 1. Real-time computing power state C_current (indirectly represented by the system's current available computing power) # 2. Computing power threshold C_threshold (indirectly represented by the system threshold) # 3. Computing power forecast value C_forecast (indirectly represented by the system prediction) # 4. Task priority Priority # 5. Execution time window T_window # 6. SQL code execution required computing power C_SQL class SQLTask: def __init__(self,sql_id:str,complexity:str,base_compute:float,priority:int,time_window:Tuple[float,float]): self.sql_id=sql_id # SQL task unique identifier self.complexity=complexity # Complexity level: simple, medium, complex self.base_compute=base_compute # Base computing power requirement self.priority=priority # Task priority (1-5, 5 is the highest) self.time_window=time_window # Execution time window (start, end time) self.c_sql=self._calculate_c_sql() # SQL execution required computing power (based on complexity calculation) self.decision_factor=0.0 # Decision factor E self.allocated_resources=0.0 # Allocated resource amount self.executed=False # Task execution status def _calculate_c_sql(self)->float: """Calculate SQL execution required computing power based on task complexity""" if self.complexity=='simple': return self.base_compute*0.4 # Simple query: <0.5 times base computing power elif self.complexity=='medium': return self.base_compute*1.2 # Medium query: 0.5-2 times base computing power else: # Complex query return self.base_compute * 2.5 # Complex query: >2x base compute def calculate_decision_factor(self, c_current: float, c_threshold: float, c_forecast: float) -> float: """Calculate decision factor E for determining resource allocation priority""" e = (self.priority * self.c_sql) / (c_threshold * max(0.1, c_current)) e *= (1 + 0.3 * (c_forecast / c_threshold - 1)) self.decision_factor = e return e def get_required_resources(self, c_required: float) -> float: """Get required resources for the task, taking the maximum of regular and SQL compute requirements""" return max(c_required, self.c_sql) def get_allocation_multiplier(self) -> float: """Get resource allocation multiplier based on decision factor, implementing dynamic resource allocation strategy""" if self.decision_factor > 2.0: return 1.2 # High priority SQL: allocate 1.2x resources elif 0.5 < self.decision_factor <= 2.0: return 1.1 # Medium priority SQL: allocate 1.1x resources -1.0 < self.decision_factor <= 0.5: return 1.0 # Normal priority SQL: allocate based on base requirements return 0.8 # Low priority SQL: reduce resource allocation, may need to be broken down def can_be_executed(self, current_time: float) -> bool: """Determine if the task can be executed at the current time""" return not self.executed and self.time_window[0] <= current_time <= self.time_window[1] def execute(self, allocated_resources: float) -> bool: """Execute the SQL task and record the allocated resources""" self.allocated_resources = allocated_resources self.executed = True return True # Compute resource manager, responsible for resource allocation decisions and dynamic threshold adjustment class ComputeResourceManager: def __init__(self, total_compute: float, base_threshold: float): self.total_compute = total_compute # Total computing power of the system self.current_compute = total_compute # Current available computing power self.base_threshold = base_threshold # Base computing power threshold self.threshold = base_threshold # Dynamically adjusted threshold # Queue limit configuration (based on SQL complexity) self.queue_limits = {'simple': 100,'medium': 50, 'complex': 20} # Parallel execution upper limit configuration (based on SQL complexity) self.parallel_limits = {'simple': 20,'medium': 10, 'complex': 5} def update_threshold(self,sql_complexity_factor:float,time_factor:float=1.0,maintenance_factor:float=1.0)->float: "Dynamically adjust computing power thresholds and incorporate an SQL complexity factor." complexity_scale=1+0.5*min(1.0,sql_complexity_factor / self.total_compute) self.threshold=self.base_threshold*time_factor*maintenance_factor*complexity_scale return self.threshold def predict_compute_demand(self,sql_queue:List[SQLTask],influence_coefficient:float=0.4)->float: "Predict computing power requirements and add SQL computing power impact factor." base_forecast=self.total_compute*0.7 sql_demand=sum(task.c_sql for task in sql_queue if not task.executed) forecast=base_forecast+influence_coefficient*sql_demand return min(forecast,self.total_compute) def calculate_sql_complexity_factor(self,sql_queue:List[SQLTask])->float: The SQL complexity factor is calculated to be equal to the total computational power required to execute the SQL statements. return sum(task.c_sql for task in sql_queue if not task.executed) def allocate_resources(self,sql_tasks:List[SQLTask],current_time:float,c_required:float=100.0)->List[SQLTask]: """Resource allocation decision based on six-dimensional decision space model""" complexity_factor=self.calculate_sql_complexity_factor(sql_tasks) self.update_threshold(complexity_factor) c_forecast=self.predict_compute_demand(sql_tasks) executable_tasks=[task for task in sql_tasks if task.can_be_executed(current_time) and not task.executed] for task in executable_tasks: task.calculate_decision_factor(self.current_compute,self.threshold,c_forecast) executable_tasks.sort(key=lambda x:(x.decision_factor,x.priority),reverse=True) complexity_counts={'simple':0,'medium':0,'complex':0} allowed_tasks=[] for task in executable_tasks: if complexity_counts[task.complexity]<self.queue_limits[task.complexity]: allowed_tasks.append(task) complexity_counts[task.complexity]+=1 complexity_parallel = {'simple': 0,'medium': 0, 'complex': 0} allocated_tasks = [] for task in allowed_tasks: if complexity_parallel[task.complexity] < self.parallel_limits[task.complexity]: required_resources = task.get_required_resources(c_required) multiplier = task.get_allocation_multiplier() allocated_resources = min(required_resources * multiplier, self.current_compute) if allocated_resources >= required_resources * 0.8: task.execute(allocated_resources) self.current_compute -= allocated_resources allocated_tasks.append(task) complexity_parallel[task.complexity] += 1 return allocated_tasks def smart_decompose(self, task: SQLTask) -> Optional[List[SQLTask]]: """Smartly decompose complex SQL tasks""" if task.complexity == 'complex' and task.decision_factor < -1.0 and task.c_sql > self.base_threshold * 3: print(f"Trigger the intelligent decomposition mechanism: SQL {task.sql_id}") sub_tasks=[ for i in range(3): sub_task=SQLTask(sql_id=f"{task.sql_id}_part{i}",complexity='medium',base_compute=task.base_compute*0.4,priority=task.priority,time_window=task.time_window) sub_tasks.append(sub_task) return sub_tasks return None # Resource allocation decision-making system, integrating a six-dimensional decision space model and resource management class ResourceAllocationSystem: def __init__(self,total_compute:float=1000.0,base_threshold:float=500.0): self.manager=ComputeResourceManager(total_compute,base_threshold) self.tasks:List[SQLTask]=[] self.current_time=0.0 def add_task(self,task:SQLTask)->None: Add SQL task to system self.tasks.append(task) def run_simulation(self,total_time:float,c_required:float=100.0)->None: """Running Resource Allocation Simulation Process""" while self.current_time <total_time:print(f"\n=== Time point: {self.current_time} ===") print(f"Current available compute: {self.manager.current_compute:.2f}") print(f"Compute threshold: {self.manager.threshold:.2f}") allocated_tasks = self.manager.allocate_resources(self.tasks, self.current_time, c_required) for task in allocated_tasks: print(f"Allocate resources to task {task.sql_id}: complexity={task.complexity}, priority={task.priority}, SQL compute demand={task.c_sql:.2f}, decision factor={task.decision_factor:.2f}, allocated resources={task.allocated_resources:.2f}") decomposed_tasks = self.manager.smart_decompose(task) if decomposed_tasks: print(f"Task {task.sql_id} is decomposed into {len(decomposed_tasks)} subtasks") self.tasks.remove(task) for sub_task in decomposed_tasks: self.add_task(sub_task) self.current_time += 1.0 # Example usage if __name__ == "__main__": system = ResourceAllocationSystem(total_compute=1000.0, base_threshold=500.0) system.add_task(SQLTask("SQL1","simple",100.0,5,(0.0,10.0))) # simple high priority system.add_task(SQLTask("SQL2","medium",200.0,3,(1.0,8.0))) # medium priority system.add_task(SQLTask("SQL3","complex",500.0,1,(2.0,15.0))) # complex low priority system.add_task(SQLTask("SQL4","complex",700.0,5,(5.0,15.0))) # complex high priority system.run_simulation(total_time=10.0,c_required=150.0) It is particularly pointed out that the "embodiments" and the like described in the specification pertain to specific features, elements, or characteristics described in connection with the embodiments. The occurrence of multiple references to an item in the specification does not necessarily convey that the item is in common to all embodiments. That is, the specific features, elements, or characteristics of any one embodiment described in connection with other embodiments are not necessarily included in every embodiment. The term "comprising" is used herein to mean that the named elements are essential, but other elements can be included. It is to be understood that the terms "comprising", "including", "including", "having", "containing", "involving", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "characterized by", "

Claims

1. An intelligent task configuration method based on big data SQL code computing power analysis, characterized in that, The SQL code is Unicode processed to generate SHA3 hash values ​​for identification. A computational power assessment is performed on the SQL code to be executed. A six-dimensional decision space model is used to determine the optimal execution plan for the SQL code. This computational power assessment decomposes the query processing into three key dimensions: operation type, data size, and processing complexity. Each dimension is quantified using carefully designed parameters, incorporating the overhead of parallel processing, and integrated into a unified formula for calculating the total computational power requirement of the SQL code. ， Where: CP is the total computing power requirement, and the unit is CPU cycles; It is the base cost of the i-th operation, in CPU cycles per row; It represents the amount of data processed by the i-th operation, expressed in rows. It is the complexity factor of the i-th operation, dimensionless, without JOIN / aggregation, and when indexed, it is covered. When the value is assigned as 1, the two tables are joined with simple aggregations but no complex predicates. When assigning a value of 1.5, this applies to JOIN operations involving three or more tables, window functions, recursive CTEs, and full-text searches. The value is assigned to 2.5; P is the parallelism of the query execution, in the form of threads / nodes; n is the total number of operations contained in the query.

2. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, The basic cost of the OBC operation is calculated according to the following formula: OBC = 1000 × W Where: 1000 is the base number of cycles for each row of operations on x86; W is the operation type weight, in which single table scan weight is 1, and increases by 1 for each additional table scan, hash JOIN weight is 4, which is accumulated by the number of JOINs, aggregation operation weight is 3, and is additionally increased by 1 when deduplication is included.

3. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, The amount of data processed by the D operation is calculated according to the following formula: D= ×S in: This is the number of rows that need to be scanned to execute the SQL code. S is the selectivity, initially assigned a value of 0.

12. The selectivity is used to calculate the amount of data to be processed. It is estimated through statistical information or sampling to predict the actual amount of data that the query needs to process and can automatically adapt to different selectivity query scenarios.

4. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, The complexity factor of the C operation is calculated using the following formula, based on the number of columns and conditions involved: C = C + 0.12 × N Where N is the sum of the number of columns and conditions involved in the SQL code.

5. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, To facilitate practical application, a simplified version of the SQL code computing power estimation formula has been developed. This simplified formula retains the core influencing factors and, by pre-setting parameter values ​​for typical scenarios, quickly obtains a valuable estimate of computing power requirements even in the absence of detailed statistical information. The simplified estimation formula for the computing power required for SQL code is as follows: CP = (∑Wi × Di × (1 + 0.1 × L)) × G Where: CP is the total computing power requirement, Wi is the operation weight, L is the number of columns involved, and G is the data volume correction coefficient, i.e., the scale factor.

6. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, The total computing power requirement calculation is further optimized. In actual computer system operation, as the number of parallel threads N increases, problems such as system resource contention and data synchronization overhead gradually become prominent, leading to non-linear growth in parallel processing efficiency. When the number of parallel threads N exceeds the square root of the memory bandwidth M, i.e., N>, the system will experience superlinear loss. Based on this, a decay factor is introduced, and the evaluation function can more realistically and accurately reflect the performance changes in actual scenarios. The formula for further optimizing the total computing power requirement calculation is as follows: CPY=CP×(1+0.05×N / ) Where: CPY is the optimized total computing power requirement, CP is the total computing power requirement, N is the number of parallel threads, M is the memory bandwidth in GB / s, and 1 + 0.05 × N / Represents the parallel efficiency decay factor, when N> This results in superlinear loss.

7. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, The specific steps for performing Unicode processing on the SQL code to generate a 256-bit SHA3 hash value are as follows: Step 1. First, use the NFKC specification to handle Unicode character differences, then remove comments and extra spaces and unify capitalization, and finally separate the SQL code structure and parameter values. Calculate the SHA3-256 hash value H0 on the purified structure as the unique identifier of the SQL code logic. Ensure that SQL codes with the same semantics generate consistent hashes. Support permission judgment and cache reuse. Implement three-level purification of the original SQL code by SQL code preprocessing and structure hash generation. Step 2. Combine H0 with the permission level to generate an enhanced hash value H1, bind the SQL code structure with access permissions to prevent unauthorized access, then combine H1 with the timestamp to generate H2, mark the execution batch, support the time priority of resource scheduling and prevent the reuse of historical hashes, and achieve the integration of the permission and time dimensions; Step 3. Historical computing power feature mapping queries historical execution records through H0, establishes the mapping relationship between the SQL code structure and computing power consumption, including recording the average CPU time consumption and memory usage, and marks high / medium / low complexity according to the preset threshold to form structured data, providing historical basis for dynamic resource allocation, and achieving the early identification and resource reservation of high-complexity SQL code; Step 4. Multi-level priority resource scheduling assigns resource priorities based on the permission level, where SYSTEM is the highest, READ_WRITE is high, and READ_ONLY is medium. Combine the timestamp with the real-time computing power monitoring CPU / memory usage rate data to perform dynamic scheduling. When resources are sufficient, execute all SQL codes; when resources are tense, prioritize the SQL codes with high priority and earlier timestamps; Low-priority SQL codes are postponed for execution and a retry policy can be configured to ensure that key business operations obtain resource guarantees; Step 5. H2 locates the execution batch, H1 associates the permission context, and H0 maps the original SQL code structure, supporting the audit to query the permissions, time, and performance tuning of SQL codes in a certain batch, and summarizing the execution data of similar SQL codes. At the same time, H0 ensures the logical uniqueness of the SQL code, and H1 / H2 distinguish different permission and time dimensions through additional information. In this way, the three-level hash realizes full-link traceability and constructs a complete traceability system.

8. The intelligent task configuration method based on big data SQL code computing power analysis according to claim 1, characterized in that, The construction of the six-dimensional decision space includes the following steps: Step 1. Construct a six-dimensional decision space model including the real-time computing power status C_current, computing power threshold C_threshold, computing power prediction value C_forecast, task priority Priority, execution time window T_window, and the computing power required for SQL code execution C_SQL. Among them, C_SQL is calculated by the total computing power demand calculation formula in Claim 1. Add the computing power impact term of the SQL code to the computing power prediction model, and incorporate the computing power demand of the SQL code queue to be executed in real time through an impact coefficient of 0.4 to improve the prediction accuracy; Step 2. The adaptive decision function changes the resource demand judgment benchmark from the conventional demand C_required to the larger value of the conventional demand and the computing power demand of the SQL code, ensuring that high-computing power demand SQL codes are identified first. The resource allocation strategy is dynamically adjusted according to the decision coefficient E value. Allocate 1.2 times the computing power resources of the SQL code to high-priority SQL codes with E>2.0, allocate 1.1 times the resources to SQL codes with 0.5<E≤2.0, and allocate according to the basic demand to SQL codes with -1.0<E≤0.5; Step 3. Based on the SQL code computing power requirement, SQL codes are divided into three levels: simple SQL codes with a computing power requirement less than 0.5 times the basic computing power, medium SQL codes with a computing power requirement of 0.5-2 times the basic computing power, and complex SQL codes with a computing power requirement more than 2 times the basic computing power. Different parallel execution limits and queue length limits are set for each level. For complex SQL codes that exceed 3 times the basic computing power and have a decision coefficient less than -1.0, the system automatically triggers an intelligent decomposition mechanism, selects execution methods such as partitioned parallelism, subquery step-by-step execution, or sampling prediction according to the query type, and recalculates the SQL code computing power requirement of each subtask. Step 4. The threshold dynamic adjustment mechanism adds an SQL code complexity factor to the existing time factor and maintenance factor. This factor is positively correlated with the total computing power requirement of the SQL code to be executed, further improving the system's adaptability to high-load SQL code. By directly incorporating the SQL code computing power requirement into the decision model, prediction algorithm, and threshold calculation, the system achieves accurate prediction, intelligent load balancing, and query optimization closed loop for high-consumption SQL code, strengthening the ability to prevent and control the risk of sudden resource exhaustion.

Images