Processing method, system, electronic device, medium and program product of SQL task

Abstract

This disclosure provides a method, system, electronic device, medium, and program product for processing SQL tasks. The method includes: filtering target rule conditions that match the metadata information and execution logs of the SQL task to be processed; invoking a diagnostic agent to diagnose problems in the SQL task based on the target rule conditions; verifying the diagnostic results based on the execution logs and a base large language model; optimizing the SQL task based on the metadata information and reasonable diagnostic results; and verifying and repairing the optimized SQL task until it meets preset conditions. This disclosure, by invoking a diagnostic agent based on the filtered target rule conditions and combining this with a base large language model to obtain reasonable diagnostic results for optimizing the SQL task and verifying and repairing the optimized SQL task, achieves on-demand invocation of rule conditions, improving processing efficiency and accuracy.

Description

Technical Field

[0001] This disclosure relates to the field of structured query language optimization technology, and in particular to a method, system, electronic device, medium and program product for processing SQL (Structured Query Language) tasks. Background Technology

[0002] With the rapid development of big data technology, data warehouses are expanding in scale, and the complexity of SQL scripts based on computing engines such as Spark SQL, Hive SQL, and BigQuery SQL is constantly increasing. To reduce the cost of manual tuning, the industry has begun to explore the use of Large Language Models (LLM) to assist SQL optimization. Existing large model-based SQL optimization solutions mainly rely on "Prompt Engineering." A typical processing flow is as follows: the optimization rule base, historical optimization cases, SQL script text, and some database metadata are concatenated into an extremely long context input, which is then used to call a general large language model for reasoning, directly outputting problem analysis, optimization suggestions, or rewritten SQL statements.

[0003] However, the aforementioned existing technologies have significant limitations in practical applications, making it difficult to meet the stability and accuracy requirements of enterprise-level large-scale production environments. Specific shortcomings include: 1. Lack of self-verification and continuous learning mechanisms, resulting in stagnant accuracy improvements. Existing solutions typically employ a "single-round inference, single-output" model, where the model's first output is taken as the final result, lacking a systematic self-verification and error correction loop. On one hand, the model cannot automatically verify the generated SQL's syntactic correctness, compatibility with the target computing platform (such as Spark / Hive), and consistency with the original SQL results. Once an error occurs, there is a lack of automated rollback and repair paths. On the other hand, existing methods fail to build a feedback mechanism, unable to structurally accumulate "verified effective optimization patterns," "frequently falsely reported error cases," or "novel defects not covered by rules." Because general-purpose models lack specialized training for specific data governance domains and vertical computing platforms, coupled with highly homogenized SQL writing styles within enterprises (such as specific multi-level CTE nesting habits and fixed Cartesian product misuse patterns), the models, when faced with recurring defects, cannot improve their recognition rate based on historical experience, nor can they converge errors due to repeated misjudgments. This results in optimization accuracy remaining at the initial level of general-purpose models for a long time, failing to improve with business accumulation. 2. Instability and information pollution caused by long contexts: As SQL optimization rules continue to expand, existing technologies tend to unconditionally concatenate all rules, examples, and constraints into the input prompts. This not only leads to a sharp increase in inference latency and computing costs but also causes a serious "long context illusion" problem. Under extremely long text input, the model's attention mechanism is easily diluted, making it difficult to accurately locate rules strongly related to the current SQL, easily confusing the applicable boundaries of different rules, leading to the omission of key optimization points (missed judgment) or the introduction of incorrect suggestions (misjudgment) that are not applicable to the current scenario, seriously affecting the reliability of optimization results. 3. Detached from the real-world operating environment, leading to "illusionary" optimization conclusions: Existing methods primarily rely on static analysis of SQL text, lacking the ability to perceive the real execution environment. Without crucial evidence such as execution logs, physical execution plans, real-time metadata (e.g., partition distribution, data skew), and platform syntax differences, models are highly susceptible to relying on statistical patterns in pre-training corpora for "guessing." For example, the model might incorrectly suggest materializing specific CTEs, misjudge whether partition pruning is effective, or even generate SQL code that violates the target computing engine's syntax, resulting in optimization suggestions that are out of touch with production realities. 4. Loose output formats and lack of quality assurance mechanisms: Existing solutions primarily output natural language suggestions such as "narrowing the partition range" and "optimizing the join order," still requiring developers to manually rewrite the code, resulting in limited efficiency improvements. Even when some solutions attempt to directly generate rewritten SQL, they generally lack end-to-end automated quality assurance mechanisms.Due to the lack of automated syntax checking, semantic equivalence verification, and performance benchmarking, the rewritten SQL often cannot simultaneously guarantee "syntactic correctness, execution feasibility, and consistent results," making it difficult to implement directly.

[0004] In summary, while existing technologies have incorporated large models into SQL optimization, they still suffer from the following drawbacks: First, existing solutions typically concatenate all rules, examples, and constraints into the model at once, easily leading to verbose context, interference from irrelevant information, and unstable inference. Second, existing solutions often rely solely on the SQL text itself for judgment, lacking runtime factual constraints such as execution logs, physical execution plans, and input / output table metadata, making them prone to unsupported misjudgments. Third, existing solutions mostly remain at the level of outputting textual suggestions or rewriting SQL in one go, lacking automatic syntax verification, execution verification, and result consistency verification of the optimization results. Fourth, existing solutions lack a feedback mechanism for false positives, making it difficult to form a continuous rule iteration capability. Summary of the Invention

[0005] The technical problem to be solved by this disclosure is to overcome the shortcomings of existing SQL optimization schemes, such as the inability to call detection rules on demand and inaccurate optimization results, and to provide a method, system, electronic device, medium and program product for processing SQL tasks.

[0006] This disclosure solves the above-mentioned technical problems through the following technical solution:

[0007] The first aspect of this disclosure provides a method for processing SQL tasks, the method comprising:

[0008] Obtain metadata information and execution logs for the SQL task to be processed;

[0009] Filter out the target rule conditions that match the metadata information and execution logs of the SQL task to be processed;

[0010] Based on the target rule conditions, the corresponding diagnostic agent is invoked to perform problem diagnosis on the SQL task to be processed, and a diagnostic result is obtained.

[0011] The diagnostic results are verified based on the execution logs and the base large language model to obtain reasonable diagnostic results;

[0012] Based on the metadata information and the reasonable diagnostic results, the SQL task to be processed is optimized to obtain the optimized SQL task;

[0013] The optimized SQL task is then validated and repaired until it meets preset conditions.

[0014] Optionally, after the step of obtaining the metadata information and execution log of the SQL task to be processed, the processing method further includes:

[0015] Obtain the preset rule base;

[0016] The step of filtering out target rule conditions that match the metadata information and execution logs of the SQL task to be processed includes:

[0017] Target rule conditions that match the metadata information and execution logs of the SQL task to be processed are selected from the preset rule base.

[0018] Optionally, the step of verifying and repairing the optimized SQL task until the optimized SQL task meets preset conditions includes:

[0019] Perform syntax validation on the optimized SQL task and obtain the SQL task that passes the syntax validation.

[0020] The SQL task that passes the syntax check is compared with the SQL task to be processed to obtain the comparison result;

[0021] Based on the comparison results, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

[0022] The preset conditions include correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed.

[0023] Optionally, the processing method further includes:

[0024] Obtain false alarm cases, failure cases, and historical repair records for SQL tasks;

[0025] The false alarm cases, failed cases, and historical repair trajectories are aggregated and analyzed according to the rule identifiers to generate rule revision suggestion information. The rule revision suggestion information includes at least one of the following: adding exemption conditions, threshold revision suggestions, difference explanations, and modification basis.

[0026] The preset rule base is updated based on the rule revision suggestion information.

[0027] Optionally, the step of repairing the optimized SQL task based on the comparison results until the optimized SQL task meets preset conditions includes:

[0028] The feature indicators are obtained from the comparison results, and the feature indicators include at least one of the following: number of result rows, hash feature value, and error log;

[0029] In response to the characteristic indicator failing to meet the result consistency judgment condition, the optimized SQL task is repaired until the optimized SQL task meets the preset condition.

[0030] Optionally, the step of repairing the optimized SQL task in response to the characteristic indicator failing to meet the result consistency judgment condition, until the optimized SQL task meets the preset condition, includes:

[0031] Obtain the first execution result of the SQL task that passed the syntax validation and the second execution result of the SQL task to be processed;

[0032] Perform a first query on the SQL task that has passed the syntax check and a second query on the SQL task to be processed;

[0033] In response to the following: the number of rows in the first execution result is inconsistent with the number of rows in the second execution result; or, at least one of the error logs returned by the first query and the error log returned by the second query is a non-empty set; or, the number of rows in the first execution result is consistent with the number of rows in the second execution result and the hash feature values ​​of the first execution result and the second execution result sampled according to a preset ratio are inconsistent, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

[0034] A second aspect of this disclosure provides a system for processing SQL tasks, the system comprising:

[0035] The first acquisition module is used to acquire metadata information and execution logs of the SQL task to be processed;

[0036] The filtering module is used to filter out target rule conditions that match the metadata information and execution logs of the SQL task to be processed.

[0037] The problem diagnosis module is used to call the corresponding diagnostic agent to perform problem diagnosis on the SQL task to be processed based on the target rule conditions, and obtain the diagnosis result;

[0038] The verification module is used to verify the diagnostic results based on the execution log and the base large language model, and obtain reasonable diagnostic results.

[0039] An optimization module is used to optimize the SQL task to be processed based on the metadata information and the reasonable diagnostic results, so as to obtain an optimized SQL task.

[0040] The processing module is used to verify and repair the optimized SQL task until the optimized SQL task meets the preset conditions.

[0041] Optionally, the processing system further includes:

[0042] The second acquisition module is used to acquire a preset rule base;

[0043] The filtering module is used to filter out target rule conditions from the preset rule base that match the metadata information and execution logs of the SQL task to be processed.

[0044] Optionally, the processing module includes:

[0045] The syntax verification unit is used to perform syntax verification on the optimized SQL task and obtain the SQL task that passes the syntax verification.

[0046] The comparison unit is used to compare the SQL task that has passed the syntax check with the SQL task to be processed, and obtain the comparison result.

[0047] The repair unit is used to repair the optimized SQL task based on the comparison results until the optimized SQL task meets the preset conditions.

[0048] The preset conditions include correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed.

[0049] Optionally, the processing system further includes:

[0050] The third acquisition module is used to acquire false alarm cases, failure cases, and historical repair records of SQL tasks;

[0051] The aggregation analysis module is used to aggregate and analyze the false alarm cases, failed cases, and historical repair trajectories according to the rule identifier, and generate rule revision suggestion information. The rule revision suggestion information includes at least one of the following: adding exemption conditions, threshold revision suggestions, difference explanations, and modification basis.

[0052] The update module is used to update the preset rule base based on the rule revision suggestion information.

[0053] Optionally, the repair unit includes:

[0054] A sub-unit is used to obtain feature indicators from the comparison results, wherein the feature indicators include at least one of the following: number of result rows, hash feature value, and error log;

[0055] The repair unit subunit is used to repair the optimized SQL task in response to the characteristic indicator failing to meet the result consistency judgment condition, until the optimized SQL task meets the preset condition.

[0056] Optionally, the repair unit subunit is used to obtain the first execution result of the SQL task that has passed the syntax check and the second execution result of the SQL task to be processed;

[0057] Perform a first query on the SQL task that has passed the syntax check and a second query on the SQL task to be processed;

[0058] In response to the inconsistency between the number of result rows of the first execution result and the number of result rows of the second execution result, and the inconsistency between the hash feature values ​​of the first execution result and the second execution result sampled according to a preset ratio, or at least one of the error logs returned by the first query and the error logs returned by the second query being a non-empty set, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

[0059] A third aspect of this disclosure provides an electronic device including a memory, a processor, and a computer program stored in the memory and for running on the processor, wherein the processor executes the computer program to implement the SQL task processing method described in the first aspect.

[0060] The fourth aspect of this disclosure provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the SQL task processing method described in the first aspect.

[0061] The fifth aspect of this disclosure provides a computer program product, including a computer program that, when executed by a processor, implements the SQL task processing method as described in the first aspect.

[0062] Based on common knowledge in the field, the above-mentioned preferred conditions can be combined arbitrarily to obtain various preferred embodiments of this disclosure.

[0063] The positive and progressive effects of this disclosure are as follows: Based on the selected target rule conditions, this disclosure calls the diagnostic agent to perform automated problem diagnosis on the SQL task to be processed, and then combines the base large language model to obtain reasonable diagnostic results to optimize the SQL task to be processed. Then, the optimized SQL task is verified and repaired until the optimized SQL task meets the preset conditions, realizing the on-demand invocation of rule conditions, improving processing efficiency and accuracy. Attached Figure Description

[0064] Figure 1 This is a flowchart of the SQL task processing method provided in Embodiment 1 of this disclosure.

[0065] Figure 2 This is a schematic diagram of the modules of the SQL task processing system provided in Embodiment 2 of this disclosure.

[0066] Figure 3 This is a schematic diagram of the structure of an electronic device that implements the SQL task processing method according to Embodiment 3 of this disclosure. Detailed Implementation

[0067] The present disclosure is further illustrated below by way of embodiments, but the present disclosure is not limited to the scope of the embodiments described herein.

[0068] The prefixes such as "first" and "second" used in this disclosure are merely for distinguishing different descriptive objects and do not limit the position, order, priority, quantity, or content of the described objects. The use of ordinal numbers and other prefixes used to distinguish descriptive objects in this disclosure does not constitute a limitation on the described objects. The description of the described objects is given in the claims or the context of the embodiments, and should not be construed as an unnecessary limitation. Furthermore, in the description of this embodiment, unless otherwise stated, "multiple" means two or more.

[0069] In this embodiment of the disclosure, the collection, storage, use, processing, transmission, provision, and disclosure of user personal information comply with relevant laws and regulations and do not violate public order and good morals.

[0070] Example 1

[0071] Figure 1 A flowchart of a method for processing an SQL task provided in Embodiment 1 of this disclosure is shown below. Figure 1 As shown, the processing method includes:

[0072] S1. Obtain the metadata information and execution log of the SQL task to be processed;

[0073] In this embodiment, metadata information includes, but is not limited to, the original SQL script text, the full text of the stored procedure or its surrounding context, input table metadata, output table metadata, task frequency, target platform task attribute information, etc.; the execution log includes the physical execution plan.

[0074] In this implementation, the metadata information and execution log of the SQL task to be processed are obtained by using the task identifier as an index. Data from different sources are converted into a unified structured input object and written into the task context cache to ensure that the same task fact basis is used in subsequent gating, diagnosis, adjudication and repair stages.

[0075] It should be noted that this implementation method uses execution logs, physical execution plans, input table metadata, and task frequency as diagnostic criteria, requiring all conclusions to be supported by facts in order to achieve "no diagnosis without evidence, no recommendation without facts," thereby enabling the diagnostic process to be constrained by real-world operational facts in a large model environment.

[0076] Additionally, it can read the full text of upstream view definitions, function definitions, or stored procedures to enhance contextual integrity in complex SQL scenarios.

[0077] S2. Filter out the target rule conditions that match the metadata information and execution logs of the SQL task to be processed;

[0078] In this embodiment, a deterministic pre-judgment is performed on each target rule condition. This deterministic pre-judgment covers at least the following three dimensions: SQL syntax features, metadata features, and execution log features. Specifically, it can determine whether the current SQL contains a common expression; whether there is a join operation; whether a partitioned table is used; whether the size of the target table or partition exceeds a preset threshold; and whether there is a physical execution plan or event log.

[0079] It should be noted that, for the same target rule condition, the system performs logical AND aggregation on multiple target rule conditions, marking the rule as executable only if all conditions are met; if any condition is not met, the filtering reason is recorded and subsequent calls to the rule are terminated. Through this gating process, a large number of inapplicable rules can be eliminated before entering the base large language model for diagnosis, thereby reducing contextual redundancy and invalid reasoning.

[0080] S3. Based on the target rule conditions, the corresponding diagnostic agent is invoked to diagnose the problem of the SQL task to be processed and obtain the diagnostic results.

[0081] In this implementation, gating rules are assigned to different autonomous diagnostic agents (intelligent agents) according to problem type. Each agent autonomously completes perception, analysis, and problem output based on the tailored context, execution evidence, and task objectives. Understandably, the rule pre-gating is preferably implemented using a declarative configuration file and supports the combined judgment of SQL syntax features, metadata features, and execution log features. Multi-agent diagnosis preferably adopts a parallel execution method split by problem type, including at least a partition diagnosis agent, a join and common expression diagnosis agent, an execution log diagnosis agent, and a reuse logic identification agent.

[0082] Specifically, after obtaining the set of executable rules, the system distributes the rules to different diagnostic agents based on the problem type. Preferably, this may include: a partitioning and metadata optimization agent; a connection and common expression optimization agent; an execution log diagnostic agent; and a reuse logic identification agent.

[0083] Each Agent only receives rule templates and input fields relevant to its own responsibilities. The system also dynamically trims the context content based on the Agent's input requirements to prevent the injection of irrelevant information.

[0084] Each agent does not passively execute a fixed template, but rather autonomously selects the evidence to focus on within the Workflow constraints, judges whether the problem is valid, and outputs the corresponding processing results. During the diagnostic process, each agent must extract factual evidence that supports the current diagnostic conclusion from the execution log, physical execution plan, and metadata sources of input and output tables; and compare the factual evidence with rule thresholds for judgment. For example, when the system identifies that a daily task scans the data of the most recent 365 days of a partition table, and the metadata information shows that the partition table only updates one partition per day, the partition diagnostic agent will calculate the ratio of the number of scanned partitions to the number of actually updated partitions; if the ratio exceeds the threshold and there are no exemption scenarios such as historical backtracking or full data supplementation, a structured problem form for "partition scan range is too large" will be output. When a common expression in the SQL is repeatedly referenced by subsequent logic and multiple similar scan nodes appear in the physical execution plan, the join and common expression optimization agent can determine that there is a duplicate materialization problem and output optimization suggestions to rewrite the common expression as a temporary materialization result or merge the referenced logic. A structured question form should include at least the following: question type, question description, relevant SQL snippets, optimization suggestions, evidence citations, and expected benefits.

[0085] S4. Verify the diagnostic results based on the execution log and the base large language model, and obtain reasonable diagnostic results;

[0086] In this embodiment, the base large language model can be a multi-base large language model. After the diagnostic results are independently judged through a multi-base large language model voting and adjudication mechanism, the system automatically generates complete optimized SQL. To ensure the absolute correctness of the optimization results, this disclosure introduces a progressive consistency verification algorithm driven autonomously by an agent. The verification agent autonomously calls a tool based on the Model Context Protocol (MCP) to first determine whether a local rewritten fragment can be extracted to reduce verification costs; secondly, it calls the MCP tool to perform a Dry Run to perform static syntax and schema consistency verification; finally, it calls the MCP tool to perform dynamic data result verification through "total number of rows + sampling hash comparison" or "bidirectional EXCEPT DISTINCT difference set comparison". Only when the MCP returns a result that meets the preset consistency threshold is it delivered as the final result; otherwise, the agent will extract the difference features to trigger a semantic repair closed loop for automatic error correction, thereby ensuring that the delivered SQL is both executable and completely equivalent to the original logic.

[0087] Specifically, the system aggregates diagnostic problem reports output by multiple agents and then proceeds to the voting and adjudication process.

[0088] The base language model does not directly use the initial diagnostic results. Instead, the main verification agent initiates three sub-agents (models A and B) with different base language models to conduct independent, back-to-back evaluations based on the structured diagnostic results. These three sub-agents share identical prompts, knowledge bases, and system settings, and each outputs a "reasonable" or "unreasonable" diagnostic result. The main verification agent then aggregates the results based on a majority voting mechanism, inputting only the diagnostic results deemed "reasonable" into the subsequent optimization phase.

[0089] Furthermore, to avoid the illusion and bias of a single-base large language model and to eliminate the need for expensive model fine-tuning training, these three sub-agents adopt a "homogeneous setting, heterogeneous base" configuration. They share the same prompts, the same knowledge base retrieval content, the same contextual evidence, and the same operating parameters (e.g., temperature). The only difference is that the underlying large language model is called differently (e.g., calling models from different vendors or architectures, such as model A and model B).

[0090] Each sub-agent, based on the inherent reasoning characteristics of its own base model, independently reviews optimization suggestions from dimensions such as expected benefits, platform feasibility, and sufficiency of evidence, and independently outputs a clear judgment and basis for "reasonable" or "unreasonable". Understandably, the preferred voting decision of the base large language model involves the three sub-agents calling different large language model APIs on the market (such as Model A, Model B, etc.), independently outputting judgment results under the same diagnostic context and judgment criteria. The main verification agent uses a majority vote (i.e., at least two votes in favor) or a veto mechanism to arrive at the final "reasonable" or "unreasonable" conclusion.

[0091] The main verification agent collects the diagnostic results from the three sub-agents and executes a voting mechanism:

[0092] The majority voting rule is adopted, meaning that the final decision is "reasonable" when at least two sub-agents determine it to be "reasonable"; otherwise, the final decision is "unreasonable".

[0093] Only diagnostic results deemed "reasonable" by the final vote proceed to the subsequent SQL generation optimization stage; for diagnostic results deemed "unreasonable," the system retains their diagnostic records, voting details of each sub-Agent, and reasons for rejection, but does not proceed with automatic rewriting. This mechanism cleverly utilizes the inherent differences in pre-training corpora and logical reasoning paths between different base-based large language models, achieving highly reliable cross-validation with zero training cost.

[0094] S5. Based on metadata information and reasonable diagnostic results, optimize the SQL task to be processed to obtain the optimized SQL task.

[0095] In this implementation, for diagnostic results that have been confirmed as reasonable by adjudication, a complete optimized SQL is generated based on the following: the original SQL, a problem summary, an execution plan summary, and target platform constraints. The complete optimized SQL is generated autonomously, rather than simply outputting natural language suggestions.

[0096] Optimizing SQL should meet the following requirements: keep the output field names consistent with the original SQL; keep the field order consistent with the original SQL; keep the business semantics consistent with the original SQL; and only modify the parts of the statement that are directly related to diagnosing the problem.

[0097] For example, if the problem is that the partition scan range is too large, the system prefers to rewrite the wide-range historical partition filtering conditions into incremental scan conditions based on the most recently updated partitions; if the problem is that the common expression is repeatedly materialized, the system prefers to refactor the relevant intermediate logic to reduce repeated scans and calculations.

[0098] Understandably, optimizing the SQL generation process is preferably constrained by the target platform's syntax rules, keeping the output field names, field order, and business semantics consistent with the original SQL;

[0099] In this embodiment, the verification agent preferably has the ability to call tools, and can autonomously select the corresponding MCP tool (such as `execute_dry_run` or `execute_query`) according to the current verification stage, and pass in the SQL before and after optimization and its partial fragments as parameters.

[0100] S6. Verify and repair the optimized SQL task until it meets the preset conditions.

[0101] In this embodiment, it is preferable to support batch processing of multiple single SQL tasks, and to maintain the diagnostic results, repair process and final output of each SQL task independently.

[0102] This implementation method automatically diagnoses the SQL task to be processed by calling the diagnostic agent based on the selected target rule conditions. Then, it combines the base large language model to obtain reasonable diagnostic results to optimize the SQL task to be processed. The optimized SQL task is then verified and repaired until it meets the preset conditions. This realizes the on-demand calling of rule conditions, improving processing efficiency and accuracy.

[0103] In an optional implementation, after S1, the processing method further includes:

[0104] Obtain the preset rule base;

[0105] S2 includes:

[0106] Filter the target rule conditions from the preset rule base to match the metadata information and execution logs of the SQL task to be processed.

[0107] In this embodiment, based on the preset rule conditions configuration, a deterministic pre-check is performed on each candidate rule, and only the target rule conditions that match the current task features are retained. Specifically, through multi-dimensional feature pre-gating and on-demand rule assembly mechanism, only the rules, constraints and input data related to the current SQL scenario are provided to the corresponding diagnostic unit, avoiding the excessively long context caused by indiscriminate splicing of all rules.

[0108] In this implementation, to address the issue of repeated misdiagnosis / missed diagnosis of similar defects caused by the lack of specialization in the general-purpose large language model and the highly homogenized and repetitive SQL writing style of the team, high-value samples such as "established optimization points", "false alarm cases overturned by rulings" and "new patterns not covered by rules" generated during the diagnostic process are automatically structured and accumulated. Based on this, the rule templates, prompt strategies and discrimination conditions are backflowed, calibrated and continuously iterated, and optimized rule candidate versions (i.e. target rule conditions) are output. This process accumulates the team's unique diagnostic experience, so that the recognition accuracy continues to improve with business accumulation and the increase of cases.

[0109] In an optional implementation, S6 includes:

[0110] Perform syntax validation on the optimized SQL task and obtain the SQL task that passes the syntax validation;

[0111] In this implementation, the generated optimized SQL is subjected to syntax validation. When the validation fails, the error correction agent is triggered to autonomously select a repair strategy and perform iterative correction until the syntax passes or the preset termination condition is met.

[0112] The SQL tasks that pass the syntax check are compared with the SQL tasks to be processed to obtain the comparison results;

[0113] Based on the comparison results, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

[0114] The preset conditions include correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed. It should be noted that the repair of the optimized SQL task will only be paused when all three conditions are met: correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed.

[0115] In this implementation, the execution results of SQL tasks that pass syntax validation are compared for consistency. When execution failure or inconsistent execution results are found, a secondary repair agent is triggered to autonomously complete the cause judgment and semantic repair until the execution results are consistent or the preset termination conditions are met.

[0116] In the specific implementation process, progressive consistency verification and dual closed-loop error correction based on the Agent invoked by the MCP tool are employed. Specifically, to ensure the absolute security and usability of the generated optimized SQL in the production environment, this disclosure abandons the traditional hard-coded comparison module and instead allows a "verification Agent" with tool invocation capabilities to autonomously complete progressive verification through the standardized Model Context Protocol (MCP): Phase 1: Local rewrite extraction and autonomous planning of verification strategies: The verification Agent compares the abstract syntax tree (AST) or text structure of the original SQL with that of the optimized SQL. If it is determined that the optimization action only modifies local logic (such as a specific CTE, subquery, or JOIN condition), the Agent autonomously extracts this local fragment to construct a local verification query, thereby reducing the computational overhead when the MCP tool executes the actual query. If it is a global structural refactoring, the complete SQL is verified. Phase 2: Agent autonomously invokes MCP to execute Dry Run (first closed loop): The verification Agent autonomously invokes a pre-built MCP database interaction tool (such as `execute_dry_run`) to submit the SQL to be verified (local or global) to the target platform's parsing engine. The Agent receives and parses the JSON payload returned by the MCP tool;

[0117] The Agent autonomously compares the syntax state and output schema in the returned payload (strictly comparing the number, name, type, and order of fields before and after rewriting). If the MCP returns a syntax error or schema inconsistency, the Agent uses the error stack as feedback context, triggering the first closed loop, regenerating the SQL, and calling the MCP tool again until the Dry Run passes. Third stage: The Agent autonomously calls the MCP to perform dynamic result verification (second closed loop): After the Dry Run passes, the verification Agent performs dynamic verification on real data. The Agent autonomously constructs the verification SQL based on the data scale and calls the MCP's actual execution tool (such as `execute_query`), then evaluates the returned results according to a preset threshold: Algorithm A (statistical and sampling hash comparison): suitable for large data volume scenarios. The Agent constructs `COUNT(*)` and a sampling hash query and sends it through the MCP. When the MCP returns the results, the Agent evaluates the total row count difference threshold (must be 0) and the sampling hash matching degree (must be 100%) of a preset ratio (preferably 30%). If the threshold is met, the results are considered equivalent. Algorithm B (Bidirectional Difference Exact Comparison): Suitable for local verification or scenarios with small to medium data volumes. The Agent constructs a bidirectional EXCEPT DISTINCT query and sends it via MCP. The Agent parses the query result set returned by MCP, and rigorously proves from the perspective of mathematical set theory that the results before and after the rewrite are completely consistent if and only if the number of returned record rows is strictly equal to 0 rows (i.e., the empty set threshold).

[0118] If the verification agent finds that the MCP return result does not meet any of the above consistency thresholds (such as unequal number of rows, hash mismatch, or non-empty difference set), it determines that there is semantic violation and triggers the second closed loop. The agent will autonomously extract the specific difference data returned by the MCP (such as extra abnormal rows, offset field values), combine it with historical repair experience, and revise the SQL logic until the result is completely consistent or the maximum number of retries is reached.

[0119] In an optional implementation, the processing method further includes:

[0120] Obtain false alarm cases, failure cases, and historical repair records for SQL tasks;

[0121] In this embodiment, the automatic error correction process preferably retains historical repair trajectories to avoid repeatedly using failed repair strategies.

[0122] Based on the rule identifiers, false alarm cases, failed cases, and historical repair trajectories are aggregated and analyzed to generate rule revision suggestions. The rule revision suggestions include at least one of the following: adding exemption conditions, threshold revision suggestions, difference explanations, and basis for modification.

[0123] In this implementation, it is preferable to output the rule revision candidates, the difference descriptions, and the basis for modification, rather than directly overwriting the existing rules, so that they can be merged after manual review.

[0124] The preset rule base is updated based on the rule revision suggestions.

[0125] In this implementation, a History-Based Optimization (HBO) mechanism is introduced to feed back false positives, failed cases, historical fixes, and experience samples to the rule base and knowledge base, forming a continuous improvement loop. These data are then used to continuously revise rule templates, knowledge items, and error correction strategies, improving the diagnostic quality of subsequent versions, reducing the manpower cost of rule maintenance, and supporting the long-term evolution of the rule system. The feedback of false positives, failed cases, historical fixes, and experience samples is essential for the system to become more accurate with use. Without this feedback, the rule recognition accuracy will remain at its initial level and cannot continuously improve with business accumulation. In other words, false positives, failed cases, and fixes are automatically collected, and historical samples are aggregated and analyzed according to rules based on the History-Based Optimization mechanism to output rule revision candidates, supporting the continuous iteration of the rule base and knowledge base.

[0126] Specifically, after the task is completed, the following content is written into the result record: diagnostic process; structured issues; multi-pedestal model voting decision results; syntax repair trajectory; result comparison record; and final optimized SQL. For cases confirmed by manual review as false alarms, the following content is further sent to the rule feedback module. In addition, for cases that fail to execute but converge successfully after error correction, the system also records the reason for failure, the repair trajectory, and the final effective strategy as historical input for the History-Based Optimization mechanism: false alarm cases; corresponding diagnostic reports; input metadata; execution plan; and manual confirmation results.

[0127] Based on the History-Based Optimization mechanism, historical samples are aggregated and analyzed according to rule identifiers to identify the root causes of false alarms, failure modes, and effective remediation experiences, and generate rule candidate versions containing the following: new exemption conditions; threshold revision suggestions; difference explanations; and basis for modification.

[0128] Preferably, the candidate version does not directly cover the online rules, but is submitted to manual review and confirmation before being included in the rule base and knowledge base, in order to reduce the false alarm rate of subsequent similar tasks, improve the success rate of repair, and achieve continuous evolution based on historical feedback.

[0129] This disclosure focuses on a single SQL statement to be processed, constrained by real-world execution information, and employs a collaborative architecture between a workflow orchestration unit (hereinafter referred to as Workflow) and an autonomous diagnostic unit (hereinafter referred to as Agent). It utilizes dual closed-loop verification for quality assurance, achieving a complete closed loop from problem identification, problem adjudication, optimization generation to automatic verification. Furthermore, the method introduces a history-based optimization (HBO) mechanism in the feedback optimization phase, using historical false alarm cases, failure cases, repair trajectories, and experience samples as self-optimization inputs to the knowledge base. This input is used to automatically revise rule triggering conditions, exemption conditions, threshold parameters, prompt templates, and error correction strategies.

[0130] In an optional implementation, S6 includes:

[0131] S61. Obtain feature indicators from the comparison results. Feature indicators include at least one of the following: number of result rows, hash feature value, and error log.

[0132] S62. In response to the characteristic indicator failing to meet the result consistency judgment condition, the optimized SQL task is repaired until the optimized SQL task meets the preset condition.

[0133] In this embodiment, the verification agent preferably has built-in threshold feedback evaluation logic. When the MCP tool returns the execution result, the agent autonomously extracts key indicators (such as the number of result rows, hash feature value, error log, etc.) and compares them with the preset threshold. If the consistency threshold is not reached, the agent will autonomously extract the difference features or error stack returned by the MCP as the context for the next prompt word, triggering the semantic repair loop.

[0134] In progressive result consistency verification, when the verification agent recognizes that the optimization action only modifies the local structure of the original SQL (such as a specific subquery, common expression CTE, join condition or filter condition), it is preferable to extract the SQL fragment before and after the local rewrite to construct a local verification query, and only call the MCP tool to perform consistency verification on the local query, without executing the complete SQL task.

[0135] In an optional implementation, S62 includes:

[0136] Obtain the first execution result of the SQL task that passed syntax validation and the second execution result of the SQL task to be processed;

[0137] Perform a first query on SQL tasks that pass syntax validation and a second query on SQL tasks to be processed.

[0138] If the number of rows in the first execution result is inconsistent with the number of rows in the second execution result, or if at least one of the error logs returned by the first query and the error log returned by the second query is a non-empty set, or if the number of rows in the first execution result is consistent with the number of rows in the second execution result and the hash feature values ​​of the first execution result and the second execution result sampled according to the preset ratio are inconsistent, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

[0139] In this embodiment, when the number of rows in the first execution result is inconsistent with the number of rows in the second execution result, the optimized SQL task is directly repaired. For example, semantic or logical issues can be fixed in the optimized SQL task. When the number of rows in the first execution result is consistent with the number of rows in the second execution result, and the hash feature values ​​of the first execution result and the second execution result sampled according to a preset ratio are inconsistent, the optimized SQL task is repaired. For example, precision or field mapping issues can be modified in the optimized SQL task.

[0140] Furthermore, the verification of the comparison results preferably adopts one or a combination of the following two methods:

[0141] (1) Statistical and sampling hash comparison: The Agent calls the MCP tool to compare the total number of rows in the original result set and the optimized result set (i.e., compare the first execution result of the SQL task that passed the syntax check with the second execution result of the SQL task to be processed). If they are consistent, the first execution result and the second execution result are randomly sampled according to a preset ratio (preferably 30%), and the hash feature value of the sampled record is calculated and compared. If the hash feature values ​​are consistent, the results are considered equivalent.

[0142] (2) Bidirectional difference set precise comparison: The Agent constructs and calls the MCP tool to execute two queries: `SELECT * FROM A EXCEPT DISTINCT SELECT * FROM B` and `SELECT * FROM B EXCEPT DISTINCT SELECT * FROM A` (where A is the original logic and B is the optimized logic). The results of the two queries are determined to be completely consistent if and only if the return results of both queries are empty sets.

[0143] In this embodiment, in actual deployment, this disclosure can adopt multiple implementation forms. The diagnostic agent, adjudication agent, optimization agent and error correction agent can run in the same service process or be deployed as multiple collaborative service instances. The syntax verification and result comparison can either call the verification service of the same platform or call different external execution services respectively. The task processing method can be online real-time triggering or offline batch processing.

[0144] Furthermore, this disclosure can also be adapted to different SQL dialects and different computing engines, including but not limited to Spark SQL, BigQuery SQL, Hive SQL, etc.

[0145] For different platforms, only the corresponding syntax constraints, metadata sources and result comparison interfaces need to be replaced, without changing the core technical solution of this disclosure: "rule pre-gating + evidence constraint diagnosis + multi-base model voting adjudication + dual closed-loop verification + rule feedback optimization".

[0146] In its implementation, this disclosure is deployed on a computing device equipped with a processor and memory, and adopts a Workflow orchestration engine that works collaboratively with multiple autonomous agents. The memory stores at least the following: rule condition configuration, diagnostic templates, adjudication templates, platform syntax constraints, result comparison strategies, error correction strategies, Workflow orchestration configurations, and historical false alarm samples.

[0147] The processor executes program instructions to complete a closed-loop process for a single SQL task, from input acquisition, problem diagnosis, optimization generation to automatic verification. Specifically: Workflow defines the sequence of processing stages, input / output interfaces, state transitions, exception rollback, and termination conditions; each Agent is responsible for autonomously completing evidence perception, rule judgment, problem output, SQL generation, and error correction decisions within its corresponding stage.

[0148] Specifically, this implementation provides a structured input mechanism for single SQL tasks: It inputs not only SQL text, but also execution logs, physical execution plans, input / output table metadata, and task attribute information, providing the diagnosis with a realistic runtime context. This structured input mechanism is a prerequisite for subsequent evidence constraint judgments; without it, the diagnosis degenerates into language pattern inference based solely on SQL text, making it impossible to avoid illusionary conclusions. A rule-based pre-gating and differentiated context assembly mechanism is also included: Before invoking the base large language model, rules are filtered through configurable conditional expressions, and differentiated contexts are assembled for different diagnostic units according to problem type. This assembly mechanism controls the input scope from the source. The absence of this assembly mechanism, in addition to addressing noise information, will lead to unresolved issues such as full rule input, context pollution, and instability in inference. A collaborative diagnostic mechanism based on evidence constraints is also crucial. Specifically, the Workflow collaborates with multiple autonomous agents, requiring diagnostic conclusions to be supported by factual evidence from execution logs, physical execution plans, or metadata. Insufficient evidence will prevent the output of affirmative conclusions. This collaborative diagnostic mechanism is essential for balancing process controllability and diagnostic reliability; without it, the output of the general-purpose large model cannot be stably constrained to a production-ready level. Furthermore, a multi-base model voting and complete optimization SQL generation mechanism based on shared origin settings is employed. Specifically, after the initial diagnosis, an independent verification process is added. The main verification agent schedules three sub-agents calling different base language models to independently review optimization suggestions back-to-back. Each sub-agent uses the exact same prompt word template, knowledge base context, and runtime parameters, relying solely on the inherent differences in inference paths between different base language models to eliminate the illusions and biases of a single model. The final conclusion is reached through a voting mechanism, and only suggestions deemed "reasonable" are allowed to enter the optimization stage, outputting complete optimized SQL. This optimized SQL generation mechanism does not require fine-tuning the model, achieving cross-validation and verification of diagnostic results at extremely low cost. The Agent autonomous closed-loop verification mechanism based on MCP tool calls: Specifically, after generating the optimized SQL, the system does not deliver it directly, but the verification Agent with tool call capabilities autonomously calls the database interface encapsulated based on the Model Context Protocol (MCP) to perform multi-level verification.The agent first autonomously calls the MCP tool to perform a Dry Run static validation, ensuring correct syntax and consistency in the names, quantities, types, and order of output fields. After passing the static validation, the agent further autonomously constructs dynamic validation SQL, calls the MCP tool to obtain the execution results, and autonomously determines whether the results are equivalent based on preset consistency thresholds (e.g., 0 differences in total rows, 100% matching of sampling hashes, and an empty difference set). Simultaneously, when the rewrite only involves a local structure, the agent only extracts local SQL fragments for validation to reduce computational overhead. This closed-loop validation mechanism upgrades traditional hard-coded system validation to an agent-driven autonomous tool invocation and decision-making closed loop, which is the core threshold for ensuring the correctness of optimized SQL results. A history-based optimization mechanism is introduced, specifically, a history-based optimization mechanism is implemented. HBO (Hardware-Based Optimization) uses false positives, failures, historical fixes, and experience samples to improve the rule base and knowledge base, creating a continuous improvement loop. This self-optimization mechanism is essential for the system to become more accurate with use. Without it, the rule recognition accuracy will remain at the initial level and cannot be continuously improved with business accumulation.

[0149] Compared with existing SQL optimization schemes based on large models, this disclosure has at least the following differences:

[0150] (1) Existing solutions usually input all rules and examples into the model at once. This disclosure first filters relevant rules through rule pre-gating and then performs context assembly to reduce interference from invalid information;

[0151] (2) Existing solutions usually make inferences based solely on SQL text. This disclosure requires that conclusions be supported by execution logs, physical execution plans, and metadata facts to reduce illusionary conclusions.

[0152] (3) Existing solutions often use single-round model output or fixed pipeline processing. This disclosure adopts a workflow orchestration and multiple autonomous agents to perform collaborative execution. The workflow is responsible for stage control, and the agents are responsible for autonomous perception, analysis, decision-making and generation.

[0153] (4) Existing solutions often take the initial model output directly as the final result. This disclosure sets up an independent multi-base model voting decision process after diagnosis to screen benefits, evidence and exemption scenarios again.

[0154] (5) Existing solutions mostly only output text suggestions or rewrite SQL once. This publication outputs complete optimized SQL and automatically repairs it through syntax verification closed loop and result consistency closed loop.

[0155] (6) Existing solutions lack self-evolution capabilities. This disclosure enables the rule system to continuously iterate through false alarm feedback and rule revision candidate output.

[0156] Thus, this disclosure forms a complete data closed loop from input collection to rule iteration; the above stages are connected end to end under the Workflow orchestration control, where Workflow is responsible for stage scheduling, state transition and abnormal rollback, and each Agent is responsible for autonomously completing analysis and execution in the corresponding stage. For example, the following quantitative data are all from the batch verification deployed in the production environment of this disclosure: taking the set of high-consumption SQL tasks selected by resource consumption in the enterprise's internal production environment for a statistical period (the sample size is more than 2,600, covering typical defect scenarios such as partition scanning, connection duplication, CTE materialization, and reuse logic) as the evaluation object, the same sample was processed before and after the introduction of this disclosure, and the statistical inference call count, hit rate, repair success rate and resource saving rate were obtained. Compared with the prior art, this disclosure has the following technical effects: (1) Large-scale automatic diagnosis: through multi-Agent classification parallel and pre-gating mechanism, the system can complete the batch diagnosis of the above 2,600 high-resource-consumption SQL tasks in a few hours, and the overall processing efficiency is more than 100 times higher than manual review. (2) Reduce false alarm rate: Through a multi-layered filtering mechanism of "pre-gating + evidence constraint diagnosis + multi-base model voting decision verification", pre-gating can reduce invalid inference calls by about 40% to 60%; in batch task verification, the effective hit rate of the final retained questions is stable at over 21%, significantly reducing systematic false alarms. (3) Directly output high-quality optimized SQL consistent with the original SQL result: This disclosure not only outputs text suggestions, but also directly outputs complete optimized SQL for questions that have been confirmed as reasonable by multi-base model voting decision, and records each optimization action, repair process and expected benefits, so that the optimization results can directly enter the verification and implementation stage. Through the syntax repair closed loop and the execution result consistency closed loop, the system can continuously perform self-verification and self-correction on the automatically generated optimized SQL, so that it converges to the state of "grammatically correct, executable and consistent with the original SQL result"; in the above production environment verification, its average resource saving rate after optimization reached 78%, indicating that the output result is not only executable, but also has clear actual benefits. (4) Support for continuous iteration of the rule system: By introducing a historical feedback-based optimization mechanism (HBO), the system can continuously revise rule templates, knowledge items and error correction strategies by using false alarm cases, failure cases, repair trajectories and experience samples, improve the diagnostic quality of subsequent versions, reduce the manpower cost of rule maintenance, and support the long-term evolution of the rule system.

[0157] This implementation relates to a method and system for automatic diagnosis and optimization of single SQL queries on distributed computing platforms such as Spark SQL, BigQuery SQL, and Hive SQL. More specifically, this disclosure relates to a method and system that achieves automatic convergence from problem identification to executable, optimized SQL queries through multi-agent collaborative diagnosis, dynamic knowledge retrieval, evidence-constrained reasoning, independent secondary adjudication, and dual closed-loop verification of syntax and result consistency; and further introduces a history-based optimization (HBO) mechanism to continuously optimize the knowledge base and rule base through historical false alarms, failure repair records, and experience samples.

[0158] Example 2

[0159] Corresponding to the aforementioned embodiment of a method for processing SQL tasks, this disclosure also provides an embodiment of a system for processing SQL tasks.

[0160] Figure 2 This is a schematic diagram of the modules of an SQL task processing system provided in Embodiment 2 of this disclosure, as shown below. Figure 2 As shown, the processing system includes:

[0161] The first acquisition module 21 is used to acquire metadata information and execution logs of the SQL task to be processed;

[0162] In this embodiment, metadata information includes, but is not limited to, the original SQL script text, the full text of the stored procedure or its surrounding context, input table metadata, output table metadata, task frequency, target platform task attribute information, etc.; the execution log includes the physical execution plan.

[0163] In this implementation, the metadata information and execution log of the SQL task to be processed are obtained by using the task identifier as an index. Data from different sources are converted into a unified structured input object and written into the task context cache to ensure that the same task fact basis is used in subsequent gating, diagnosis, adjudication and repair stages.

[0164] It should be noted that this implementation method uses execution logs, physical execution plans, input table metadata, and task frequency as diagnostic criteria, requiring all conclusions to be supported by facts in order to achieve "no diagnosis without evidence, no recommendation without facts," thereby enabling the diagnostic process to be constrained by real-world operational facts in a large model environment.

[0165] Additionally, it can read the full text of upstream view definitions, function definitions, or stored procedures to enhance contextual integrity in complex SQL scenarios.

[0166] The filtering module 22 is used to filter out the target rule conditions that match the metadata information and execution logs of the SQL task to be processed.

[0167] In this embodiment, a deterministic pre-judgment is performed on each target rule condition. This deterministic pre-judgment covers at least the following three dimensions: SQL syntax features, metadata features, and execution log features. Specifically, it can determine whether the current SQL contains a common expression; whether there is a join operation; whether a partitioned table is used; whether the size of the target table or partition exceeds a preset threshold; and whether there is a physical execution plan or event log.

[0168] It should be noted that, for the same target rule condition, the system performs logical AND aggregation on multiple target rule conditions, marking the rule as executable only if all conditions are met; if any condition is not met, the filtering reason is recorded and subsequent calls to the rule are terminated. Through this gating process, a large number of inapplicable rules can be eliminated before entering the base large language model for diagnosis, thereby reducing contextual redundancy and invalid reasoning.

[0169] Problem diagnosis module 23 is used to call the corresponding diagnostic agent based on the target rule conditions to perform problem diagnosis on the SQL task to be processed and obtain the diagnosis result;

[0170] In this embodiment, gating rules are assigned to different autonomous diagnostic agents (intelligent agents) according to the problem type. Each agent autonomously completes perception, analysis and problem output based on the tailored context, execution evidence and task objectives. It should be noted that other related content can be found in the corresponding section of Embodiment 1, and will not be repeated here.

[0171] The verification module 24 is used to verify the diagnostic results based on the execution log and the base large language model, and obtain reasonable diagnostic results.

[0172] For specific details, please refer to the corresponding section of Example 1, which will not be repeated here.

[0173] Optimization module 25 is used to optimize the SQL task to be processed based on metadata information and reasonable diagnostic results, so as to obtain the optimized SQL task.

[0174] In this implementation, for diagnostic results that have been confirmed as reasonable by adjudication, a complete optimized SQL is generated based on the following: the original SQL, a problem summary, an execution plan summary, and target platform constraints. The complete optimized SQL is generated autonomously, rather than simply outputting natural language suggestions.

[0175] Optimizing SQL should meet the following requirements: keep the output field names consistent with the original SQL; keep the field order consistent with the original SQL; keep the business semantics consistent with the original SQL; and only modify the parts of the statement that are directly related to diagnosing the problem.

[0176] For example, if the problem is that the partition scan range is too large, the system prefers to rewrite the wide-range historical partition filtering conditions into incremental scan conditions based on the most recently updated partitions; if the problem is that the common expression is repeatedly materialized, the system prefers to refactor the relevant intermediate logic to reduce repeated scans and calculations.

[0177] Understandably, optimizing the SQL generation process is preferably constrained by the target platform's syntax rules, keeping the output field names, field order, and business semantics consistent with the original SQL;

[0178] In this embodiment, the verification agent preferably has the ability to call tools, and can autonomously select the corresponding MCP tool (such as `execute_dry_run` or `execute_query`) according to the current verification stage, and pass in the SQL before and after optimization and its partial fragments as parameters.

[0179] Processing module 26 is used to verify and repair the optimized SQL task until the optimized SQL task meets the preset conditions.

[0180] In this embodiment, it is preferable to support batch processing of multiple single SQL tasks, and to maintain the diagnostic results, repair process and final output of each SQL task independently.

[0181] This implementation method automatically diagnoses the SQL task to be processed by calling the diagnostic agent based on the selected target rule conditions. Then, it combines the base large language model to obtain reasonable diagnostic results to optimize the SQL task to be processed. The optimized SQL task is then verified and repaired until it meets the preset conditions. This realizes the on-demand calling of rule conditions, improving processing efficiency and accuracy.

[0182] In an optional implementation, the processing system further includes:

[0183] The second acquisition module is used to acquire a preset rule base;

[0184] The filtering module is used to filter out target rule conditions that match the metadata information and execution logs of the SQL task to be processed from the preset rule base.

[0185] In this embodiment, based on the preset rule conditions configuration, a deterministic pre-check is performed on each candidate rule, and only the target rule conditions that match the current task features are retained. Specifically, through multi-dimensional feature pre-gating and on-demand rule assembly mechanism, only the rules, constraints and input data related to the current SQL scenario are provided to the corresponding diagnostic unit, avoiding the excessively long context caused by indiscriminate splicing of all rules.

[0186] In this implementation, to address the issue of repeated misdiagnosis / missed diagnosis of similar defects caused by the lack of specialization in the general-purpose large language model and the highly homogenized and repetitive SQL writing style of the team, high-value samples such as "established optimization points", "false alarm cases overturned by rulings" and "new patterns not covered by rules" generated during the diagnostic process are automatically structured and accumulated. Based on this, the rule templates, prompt strategies and discrimination conditions are backflowed, calibrated and continuously iterated, and optimized rule candidate versions (i.e. target rule conditions) are output. This process accumulates the team's unique diagnostic experience, so that the recognition accuracy continues to improve with business accumulation and the increase of cases.

[0187] In an optional implementation, the processing module includes:

[0188] The syntax verification unit is used to perform syntax verification on the optimized SQL task and obtain the SQL task that passes the syntax verification.

[0189] In this implementation, the generated optimized SQL is subjected to syntax validation. When the validation fails, the error correction agent is triggered to autonomously select a repair strategy and perform iterative correction until the syntax passes or the preset termination condition is met.

[0190] The comparison unit is used to compare the SQL task that has passed the syntax check with the SQL task to be processed and obtain the comparison result;

[0191] The repair unit is used to repair the optimized SQL task based on the comparison results until the optimized SQL task meets the preset conditions.

[0192] The preset conditions include correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed. It should be noted that the repair of the optimized SQL task will only be paused when all three conditions are met: correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed.

[0193] In this implementation, the execution results of SQL tasks that pass syntax validation are compared for consistency. When execution failure or inconsistent execution results are found, a secondary repair agent is triggered to autonomously complete the cause judgment and semantic repair until the execution results are consistent or the preset termination conditions are met.

[0194] In the specific implementation process, please refer to the relevant content in the corresponding part of Example 1, which will not be repeated here.

[0195] In an optional implementation, the processing system further includes:

[0196] The third acquisition module is used to acquire false alarm cases, failure cases, and historical repair records of SQL tasks;

[0197] In this embodiment, the automatic error correction process preferably retains historical repair trajectories to avoid repeatedly using failed repair strategies.

[0198] The aggregation analysis module is used to aggregate and analyze false alarm cases, failed cases, and historical repair trajectories according to rule identifiers, and generate rule revision suggestions. The rule revision suggestions include at least one of the following: adding exemption conditions, threshold revision suggestions, difference explanations, and modification basis.

[0199] In this implementation, it is preferable to output the rule revision candidates, the difference descriptions, and the basis for modification, rather than directly overwriting the existing rules, so that they can be merged after manual review.

[0200] The update module is used to update the preset rule base based on rule revision suggestions.

[0201] In this implementation, a History-Based Optimization (HBO) mechanism is introduced to feed back false positives, failed cases, historical fixes, and experience samples to the rule base and knowledge base, forming a continuous improvement loop. These data are then used to continuously revise rule templates, knowledge items, and error correction strategies, improving the diagnostic quality of subsequent versions, reducing the manpower cost of rule maintenance, and supporting the long-term evolution of the rule system. The feedback of false positives, failed cases, historical fixes, and experience samples is essential for the system to become more accurate with use. Without this feedback, the rule recognition accuracy will remain at its initial level and cannot continuously improve with business accumulation. In other words, false positives, failed cases, and fixes are automatically collected, and historical samples are aggregated and analyzed according to rules based on the History-Based Optimization mechanism to output rule revision candidates, supporting the continuous iteration of the rule base and knowledge base.

[0202] Specifically, after the task is completed, the following content is written into the result record: diagnostic process; structured issues; multi-pedestal model voting decision results; syntax repair trajectory; result comparison record; and final optimized SQL. For cases confirmed by manual review as false alarms, the following content is further sent to the rule feedback module. In addition, for cases that fail to execute but converge successfully after error correction, the system also records the reason for failure, the repair trajectory, and the final effective strategy as historical input for the History-Based Optimization mechanism: false alarm cases; corresponding diagnostic reports; input metadata; execution plan; and manual confirmation results.

[0203] Based on the History-Based Optimization mechanism, historical samples are aggregated and analyzed according to rule identifiers to identify the root causes of false alarms, failure modes, and effective remediation experiences, and generate rule candidate versions containing the following: new exemption conditions; threshold revision suggestions; difference explanations; and basis for modification.

[0204] Preferably, the candidate version does not directly cover the online rules, but is submitted to manual review and confirmation before being included in the rule base and knowledge base, in order to reduce the false alarm rate of subsequent similar tasks, improve the success rate of repair, and achieve continuous evolution based on historical feedback.

[0205] This disclosure focuses on a single SQL statement to be processed, constrained by real-world execution information, and employs a collaborative architecture between a workflow orchestration unit (hereinafter referred to as Workflow) and an autonomous diagnostic unit (hereinafter referred to as Agent). It utilizes dual closed-loop verification for quality assurance, achieving a complete closed loop from problem identification, problem adjudication, optimization generation to automatic verification. Furthermore, the method introduces a history-based optimization (HBO) mechanism in the feedback optimization phase, using historical false alarm cases, failure cases, repair trajectories, and experience samples as self-optimization inputs to the knowledge base. This input is used to automatically revise rule triggering conditions, exemption conditions, threshold parameters, prompt templates, and error correction strategies.

[0206] In an optional implementation, the repair unit includes:

[0207] The acquisition sub-unit is used to obtain feature indicators from the comparison results. The feature indicators include at least one of the following: number of result rows, hash feature value, and error log.

[0208] The repair unit subunit is used to repair the optimized SQL task in response to the failure of the feature index to meet the result consistency judgment condition, until the optimized SQL task meets the preset condition.

[0209] In this embodiment, the verification agent preferably has built-in threshold feedback evaluation logic. When the MCP tool returns the execution result, the agent autonomously extracts key indicators (such as the number of result rows, hash feature value, error log, etc.) and compares them with the preset threshold. If the consistency threshold is not reached, the agent will autonomously extract the difference features or error stack returned by the MCP as the context for the next prompt word, triggering the semantic repair loop.

[0210] In progressive result consistency verification, when the verification agent recognizes that the optimization action only modifies the local structure of the original SQL (such as a specific subquery, common expression CTE, join condition or filter condition), it is preferable to extract the SQL fragment before and after the local rewrite to construct a local verification query, and only call the MCP tool to perform consistency verification on the local query, without executing the complete SQL task.

[0211] In an optional implementation, the repair unit subunit is used to obtain the first execution result of the SQL task that has passed syntax validation and the second execution result of the SQL task to be processed;

[0212] Perform a first query on SQL tasks that pass syntax validation and a second query on SQL tasks to be processed.

[0213] In response to the discrepancy between the number of rows in the first execution result and the number of rows in the second execution result, or at least one of the error logs returned by the first query and the second query is a non-empty set, or the discrepancy between the number of rows in the first execution result and the number of rows in the second execution result and the hash feature values ​​of the first execution result and the second execution result sampled according to a preset ratio are inconsistent, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

[0214] In this embodiment, when the number of rows in the first execution result is inconsistent with the number of rows in the second execution result, the optimized SQL task is directly repaired. For example, semantic or logical issues can be fixed in the optimized SQL task. When the number of rows in the first execution result is consistent with the number of rows in the second execution result, and the hash feature values ​​of the first execution result and the second execution result sampled according to a preset ratio are inconsistent, the optimized SQL task is repaired. For example, precision or field mapping issues can be modified in the optimized SQL task.

[0215] Furthermore, the verification of the comparison results preferably adopts one or a combination of the following two methods:

[0216] (1) Statistical and sampling hash comparison: The Agent calls the MCP tool to compare the total number of rows in the original result set and the optimized result set (i.e., compare the first execution result of the SQL task that passed the syntax check with the second execution result of the SQL task to be processed). If they are consistent, the first execution result and the second execution result are randomly sampled according to a preset ratio (preferably 30%), and the hash feature value of the sampled record is calculated and compared. If the hash feature values ​​are consistent, the results are considered equivalent.

[0217] (2) Bidirectional difference set precise comparison: The Agent constructs and calls the MCP tool to execute two queries: `SELECT * FROM A EXCEPT DISTINCT SELECT * FROM B` and `SELECT * FROM B EXCEPT DISTINCT SELECT * FROM A` (where A is the original logic and B is the optimized logic). The results of the two queries are determined to be completely consistent if and only if the return results of both queries are empty sets.

[0218] In this embodiment, in actual deployment, this disclosure can adopt multiple implementation forms. The diagnostic agent, adjudication agent, optimization agent and error correction agent can run in the same service process or be deployed as multiple collaborative service instances. The syntax verification and result comparison can either call the verification service of the same platform or call different external execution services respectively. The task processing method can be online real-time triggering or offline batch processing.

[0219] Furthermore, this disclosure is also compatible with different SQL dialects and different computing engines, including but not limited to Spark SQL, BigQuery SQL, Hive SQL, etc.

[0220] For different platforms, only the corresponding syntax constraints, metadata sources and result comparison interfaces need to be replaced, without changing the core technical solution of this disclosure: "rule pre-gating + evidence constraint diagnosis + multi-base model voting adjudication + dual closed-loop verification + rule feedback optimization".

[0221] In its implementation, this disclosure is deployed on a computing device equipped with a processor and memory, and adopts a Workflow orchestration engine that works collaboratively with multiple autonomous agents. The memory stores at least the following: rule condition configurations, diagnostic templates, adjudication templates, platform syntax constraints, result comparison strategies, error correction strategies, Workflow orchestration configurations, and historical false alarm samples. For a detailed description of the implementation process and the technical effects achieved compared to existing technologies, please refer to Example 1; further details are omitted here.

[0222] For the system embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to in the description of the method embodiments. The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this disclosure according to actual needs.

[0223] Example 3

[0224] Figure 3 This is a schematic diagram of the structure of an electronic device according to Embodiment 3 of this disclosure. The electronic device includes a memory, a processor, and a computer program stored in the memory and used to run on the processor. When the processor executes the computer program, it implements the SQL task processing method described in any of the above embodiments. Figure 3 The electronic device 90 shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments disclosed herein.

[0225] like Figure 3 As shown, the electronic device 90 can be manifested as a general-purpose computing device, such as a server device. The components of the electronic device 90 may include, but are not limited to: at least one processor 91, at least one memory 92, and a bus 93 connecting different system components (including memory 92 and processor 91).

[0226] Bus 93 includes a data bus, an address bus, and a control bus.

[0227] The memory 92 may include volatile memory, such as random access memory (RAM) 921 and / or cache memory 922, and may further include read-only memory (ROM) 923.

[0228] The memory 92 may also include a program tool 925 (or utility) having a set (at least one) program module 924, such program module 924 including but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of these examples may include an implementation of a network environment.

[0229] The processor 91 executes various functional applications and data processing by running computer programs stored in the memory 92, such as the SQL task processing method provided in any of the above embodiments.

[0230] Electronic device 90 can also communicate with one or more external devices 94 (e.g., keyboard, pointing device, etc.). This communication can be performed via input / output (I / O) interface 95. Furthermore, electronic device 90 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via network adapter 96. Figure 3 As shown, network adapter 96 communicates with other modules of electronic device 90 via bus 93. It should be understood that, although not shown in the figure, other hardware and / or software modules may be used in conjunction with electronic device 90, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID (disk array) systems, tape drives, and data backup storage systems.

[0231] It should be noted that although several units / modules or sub-units / modules of the electronic device have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of this disclosure, the features and functions of two or more units / modules described above can be embodied in one unit / module. Conversely, the features and functions of one unit / module described above can be further divided and embodied by multiple units / modules.

[0232] Example 4

[0233] Embodiment 4 of this disclosure also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the SQL task processing method provided in any of the above embodiments.

[0234] The readable storage medium may be more specifically adopted, including but not limited to: portable disk, hard disk, random access memory, read-only memory, erasable programmable read-only memory, optical storage device, magnetic storage device, or any suitable combination thereof.

[0235] Example 5

[0236] Embodiment 5 of this disclosure also provides a computer program product, including a computer program that, when executed by a processor, implements the SQL task processing method described in any of the above embodiments.

[0237] The program code for executing the computer program product of this disclosure can be written in any combination of one or more programming languages, and the program code can be executed entirely on a user device, partially on a user device, as a stand-alone software package, partially on a user device and partially on a remote device, or entirely on a remote device.

[0238] While specific embodiments of this disclosure have been described above, those skilled in the art should understand that these are merely illustrative examples, and the scope of protection of this disclosure is defined by the appended claims. Those skilled in the art can make various changes or modifications to these embodiments without departing from the principles and essence of this disclosure, but all such changes and modifications fall within the scope of protection of this disclosure.

Claims

1. A method for processing SQL tasks, characterized in that, The processing method includes: Obtain metadata information and execution logs for the SQL task to be processed; Filter out the target rule conditions that match the metadata information and execution logs of the SQL task to be processed; Based on the target rule conditions, the corresponding diagnostic agent is invoked to perform problem diagnosis on the SQL task to be processed, and a diagnostic result is obtained. The diagnostic results are verified based on the execution logs and the base large language model to obtain reasonable diagnostic results; Based on the metadata information and the reasonable diagnostic results, the SQL task to be processed is optimized to obtain the optimized SQL task; The optimized SQL task is then validated and repaired until it meets preset conditions.

2. The method for processing SQL tasks as described in claim 1, characterized in that, After the step of obtaining the metadata information and execution log of the SQL task to be processed, the processing method further includes: Obtain the preset rule base; The step of filtering out target rule conditions that match the metadata information and execution logs of the SQL task to be processed includes: Target rule conditions that match the metadata information and execution logs of the SQL task to be processed are selected from the preset rule base.

3. The method for processing SQL tasks as described in claim 1, characterized in that, The step of verifying and repairing the optimized SQL task until the optimized SQL task meets the preset conditions includes: Perform syntax validation on the optimized SQL task and obtain the SQL task that passes the syntax validation. The SQL task that passes the syntax check is compared with the SQL task to be processed to obtain the comparison result; Based on the comparison results, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions. The preset conditions include correct syntax, executable capability, and consistency with the execution result of the SQL task to be processed.

4. The method for processing SQL tasks as described in claim 2, characterized in that, The processing method further includes: Obtain false alarm cases, failure cases, and historical repair records for SQL tasks; The false alarm cases, failed cases, and historical repair trajectories are aggregated and analyzed according to the rule identifiers to generate rule revision suggestion information. The rule revision suggestion information includes at least one of the following: adding exemption conditions, threshold revision suggestions, difference explanations, and modification basis. The preset rule base is updated based on the rule revision suggestion information.

5. The method for processing SQL tasks as described in claim 3, characterized in that, The step of repairing the optimized SQL task based on the comparison results until the optimized SQL task meets the preset conditions includes: The feature indicators are obtained from the comparison results, and the feature indicators include at least one of the following: number of result rows, hash feature value, and error log; In response to the characteristic indicator failing to meet the result consistency judgment condition, the optimized SQL task is repaired until the optimized SQL task meets the preset condition.

6. The method for processing SQL tasks as described in claim 5, characterized in that, The step of repairing the optimized SQL task in response to the characteristic indicator failing to meet the result consistency judgment condition, until the optimized SQL task meets the preset condition, includes: Obtain the first execution result of the SQL task that passed the syntax validation and the second execution result of the SQL task to be processed; Perform a first query on the SQL task that has passed the syntax check and a second query on the SQL task to be processed; In response to the following: the number of rows in the first execution result is inconsistent with the number of rows in the second execution result; or, at least one of the error logs returned by the first query and the error log returned by the second query is a non-empty set; or, the number of rows in the first execution result is consistent with the number of rows in the second execution result and the hash feature values ​​of the first execution result and the second execution result sampled according to a preset ratio are inconsistent, the optimized SQL task is repaired until the optimized SQL task meets the preset conditions.

7. A system for processing SQL tasks, characterized in that, The processing system includes: The first acquisition module is used to acquire metadata information and execution logs of the SQL task to be processed; The filtering module is used to filter out target rule conditions that match the metadata information and execution logs of the SQL task to be processed. The problem diagnosis module is used to call the corresponding diagnostic agent to perform problem diagnosis on the SQL task to be processed based on the target rule conditions, and obtain the diagnosis result; The verification module is used to verify the diagnostic results based on the execution log and the base large language model, and obtain reasonable diagnostic results. An optimization module is used to optimize the SQL task to be processed based on the metadata information and the reasonable diagnostic results, so as to obtain an optimized SQL task. The processing module is used to verify and repair the optimized SQL task until the optimized SQL task meets the preset conditions.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and for running on the processor, characterized in that, When the processor executes the computer program, it implements the method for processing the SQL task according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the method for processing the SQL task according to any one of claims 1 to 6.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the SQL task processing method as described in any one of claims 1 to 6.

Images