SQL automatic governance method and system based on intelligent agent

Abstract

The invention provides an intelligent agent-based SQL automatic treatment method and system, and the method comprises the steps: collecting problem SQL data, and carrying out the unified aperture processing of the SQL data; performing clustering analysis on the SQL data to obtain different types of SQL data, and storing the SQL data in a metadatabase; performing governance analysis on the SQL data in the metadatabase by utilizing an SQL governance agent to obtain a corresponding governance analysis result; and performing display feedback and backtracking in combination with the SQL data of the problem and a corresponding governance analysis result. Problem SQL data from a multi-source heterogeneous database and a data warehouse are collected, similar SQL data are gathered by using a clustering algorithm and then stored in a metadatabase, meanwhile, the capacity of an agent is introduced, an SQL governance agent is constructed and debugged, analysis and optimization suggestions are provided for the problem SQL data, and the SQL data governance efficiency and effect are effectively improved.

Description

Technical Field

[0001] This invention belongs to the field of artificial intelligence technology, and specifically relates to an automated SQL governance method and system based on intelligent agents. Background Technology

[0002] In the current field of SQL (Structured Query Language) governance, the long-standing challenges of low efficiency, high cost, and insufficient technical adaptability are core problems plaguing DBAs (Database Administrators) and developers. From the SQL perspective, SQL queries requiring governance typically exhibit low execution efficiency, lengthy and complex structures, high resource consumption, improper index usage, or full table scans, severely impacting database performance and system stability; these are referred to as "problem SQL." From a governance process perspective, the detection and governance of problem SQL still primarily rely on manual operations and notifications by DBAs using traditional tools. The overall process has low automation, making it difficult to improve processing efficiency. Simultaneously, developers generally face difficulties in root cause identification when dealing with problem SQL, especially for complex SQL statements. They often need to frequently communicate with DBAs to obtain modification suggestions, which not only prolongs the tuning cycle but also wastes significant human resources.

[0003] At the tool support level, existing problematic SQL detection tools also have significant shortcomings: on the one hand, they fail to meet practical needs in terms of the completeness and accuracy of problematic SQL detection, and cannot comprehensively and accurately cover the problematic SQL processing requirements in various database and data warehouse scenarios; on the other hand, the variety of detection tools and the inconsistent data formats output add extra difficulty to subsequent SQL cleanup and analysis. Furthermore, existing problematic SQL governance tools have not yet incorporated novel AI agent technology, resulting in a lack of efficient automated processing capabilities when faced with large volumes of problematic SQL text, making it difficult to meet the needs of large-scale SQL governance scenarios. Therefore, how to efficiently clean up, summarize, analyze, and optimize problematic SQL, and scientifically evaluate the optimization effects, is a problem that those skilled in the art need to solve. Summary of the Invention

[0004] This invention provides an agent-based automated SQL governance method and system, aiming to improve the efficiency and effectiveness of SQL governance.

[0005] In a first aspect, embodiments of the present invention provide an agent-based automated SQL governance method, comprising: Collect problematic SQL data and process the problematic SQL data using a unified standard. Cluster analysis is performed on the question SQL data to obtain question SQL data of different categories, which are then stored in the metadata database; The SQL governance intelligence agent is used to perform governance analysis on the problematic SQL data in the metadata database, and the corresponding governance analysis results are obtained. The problematic SQL data and the corresponding governance analysis results are presented, feedback is provided, and a retrospective review is conducted.

[0006] Secondly, embodiments of the present invention provide an agent-based automated SQL governance system, comprising: A data acquisition and storage unit is used to acquire problematic SQL data and process the problematic SQL data using a unified standard. The clustering analysis unit is used to perform clustering analysis on the question SQL data to obtain question SQL data of different categories and store them in the metadata database; The governance analysis unit is used to perform governance analysis on problematic SQL data in the metadata database using an SQL governance intelligent agent, and obtain the corresponding governance analysis results. The feedback display unit is used to display feedback and provide backtracking by combining the problematic SQL data with the corresponding governance analysis results.

[0007] This invention provides an automated SQL governance method and system based on intelligent agents. The method includes: collecting problematic SQL data and processing it using a unified standard; performing cluster analysis on the problematic SQL data to obtain different categories of problematic SQL data and storing them in a metadata database; using an SQL governance intelligent agent to perform governance analysis on the problematic SQL data in the metadata database to obtain corresponding governance analysis results; and combining the problematic SQL data and the corresponding governance analysis results for display, feedback, and backtracking. This invention provides an automated and intelligent SQL governance system integrating the entire process of "collection-storage-analysis-display-feedback-backtracking." This invention collects problematic SQL data from multi-source heterogeneous databases and data warehouses and unifies the standard, uses clustering algorithms to aggregate similar SQL data and store them in a metadata database, and introduces the capabilities of intelligent agents. The SQL governance intelligent agent is constructed and debugged to provide analysis and optimization suggestions for problematic SQL data, effectively improving the efficiency and effectiveness of SQL data governance. Attached Figure Description

[0008] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0009] Figure 1 A flowchart illustrating an agent-based automated SQL management method provided in an embodiment of the present invention; Figure 2 A schematic diagram of the principle architecture of an agent-based automated SQL governance method provided in an embodiment of the present invention; Figure 3 A sub-architecture diagram of an agent-based automated SQL governance method provided in this embodiment of the invention; Figure 4 Another sub-architecture diagram of an agent-based SQL automation governance method provided in this embodiment of the invention; Figure 5 This is a schematic block diagram of an agent-based automated SQL management system provided in an embodiment of the present invention. Detailed Implementation

[0010] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0011] It should be understood that, when used in this specification and the appended claims, the terms "comprising" and "including" indicate the presence of the described features, integrals, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or collections thereof.

[0012] It should also be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the invention. As used in this specification and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms unless the context clearly indicates otherwise.

[0013] It should also be further understood that the term "and / or" as used in this specification and the appended claims refers to any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0014] Please see below. Figure 1 This invention provides an automated SQL governance method based on intelligent agents, specifically including steps S101-S104.

[0015] Step S101: Collect the problematic SQL data and process the problematic SQL data using a unified standard. Step S102: Perform cluster analysis on the question SQL data to obtain question SQL data of different categories, and store them in the metadata database; Step S103: Use the SQL governance agent to perform governance analysis on the problematic SQL data in the metadata database and obtain the corresponding governance analysis results; Step S104: Combine the problematic SQL data and the corresponding governance analysis results to provide feedback and backtracking.

[0016] This embodiment collects problematic SQL data from multiple heterogeneous databases and data warehouses, standardizes the data, and then uses a clustering algorithm to aggregate similar problematic SQL data before storing it in a metadata database. Simultaneously, it introduces intelligent agent capabilities, constructing and debugging an SQL governance intelligent agent to provide analysis and optimization suggestions for problematic SQL data. Finally, it processes the problematic SQL data through display, feedback, and backtracking operations. This solution not only effectively improves the governance of problematic SQL data but also enables an automated and intelligent SQL governance system that covers the entire process of "collection, storage, analysis, display, feedback, and backtracking."

[0017] Specifically, in combination Figure 2 The system comprises four modules: Collection module (for acquiring problematic SQL data from multi-source heterogeneous databases and data warehouses, and standardizing the data); Storage module (for classifying the acquired problematic SQL data using similarity clustering algorithms and storing it in a metadata database); Analysis module (based on the SQL governance intelligence agent, providing problematic SQL analysis capabilities and optimization suggestions, and iteratively improving the accuracy and effectiveness of analysis and suggestions by combining data-driven quantitative analysis with human feedback); Display module (platform-based display of relevant information about problematic SQL data, including capabilities such as access control and hierarchical management); Feedback module (platform-based feedback on the rectification status of problematic SQL data, with DBAs providing secondary confirmation of the governance effectiveness); and Retrospective module (retrospective tracking of the historical changes and governance effects of problematic SQL data, visualizing the governance results, enriching the SQL governance knowledge base, and further enhancing the capabilities of the SQL governance intelligence agent).

[0018] Compared with existing technologies, the SQL data governance method provided in this embodiment has the following advantages: Even simpler, the capture and cleaning of problematic SQL queries are all performed automatically by the system, eliminating the need for manual acquisition and processing; More intuitive, with platform-based display and feedback, making it more intuitive and convenient to use; More intelligent: Build an intelligent SQL governance agent and provide intelligent solutions in conjunction with the system; More efficient clustering and analysis information for large batches of problematic SQL queries, significantly improving the efficiency of problem localization and optimization for both DBAs and R&D personnel; It is more traceable, and relevant information can be persisted in the metadata database, which can trace the performance and quantify the governance effect of problematic SQL.

[0019] In one embodiment, cluster analysis is performed on the problematic SQL data to obtain problematic SQL data of different categories, which are then stored in a metadata database, including: The question SQL data is clustered using a similarity clustering algorithm according to the following formula to obtain question SQL data of different categories: (1); Where Simi represents similarity, s and t are two finite strings, JaroWinkler represents the Jaro-Winkler edit distance, and NormLev represents the normalized Levenshtein edit distance. and This indicates a relatively strict threshold. and This indicates a relatively lenient threshold.

[0020] Here, the acquisition module integrates the ability to interface with multi-source heterogeneous databases and data warehouses. By periodically accessing the database's operation logs and system metadata tables, it extracts SQL execution records within a specified time window. After summarization, noise cleaning, and field normalization, it generates standardized data with consistent structure and outputs it to the storage module.

[0021] The storage module is based on a metadata database, connecting the four modules: collection, analysis, feedback, and backtracking. Issue SQL information, standardized by the collection module, is recorded in the metadata database. The storage module introduces a custom similarity clustering algorithm based on Levenshtein edit distance (an edit distance algorithm that measures the difference between two strings) and Jaro-Winkler edit distance (an edit distance algorithm used to measure string similarity). This clustering algorithm clusters similar issue SQL queries to provide more efficient and intuitive SQL governance functions such as analysis, display, and feedback. The formula for the similarity clustering algorithm is as follows: Levenshtein edit distance: (2); The result after normalization is: (3); Where s and t are two finite strings, (s→t) is the set of all legal edit operation sequences that convert s to t. This represents the total number of operations in the operation sequence E (each operation has a cost of 1, and allowed operations include inserting, deleting, and replacing a character).

[0022] Jaro-Winkler edit distance: (4); (5); Where s and t are two finite strings, m is the number of characters to be matched, and T represents half the number of swaps required to modify the matched characters to match their order. This indicates the number of consecutive matching characters at the beginning of two strings. This is the scaling factor.

[0023] Based on the above results, a custom similarity clustering algorithm is set, as shown in formula (1).

[0024] Specifically, Levenshtein edit distance considers the minimum edit distance between two strings, showing significant clustering effects for SQL queries that differ in spaces or SQL variables; while Jaro-Winkler edit distance considers the influence of prefixes more, performing better clustering for SQL queries in scenarios such as batch insertion and deletion. For these two similarity metrics, this system can design different threshold scales based on the actual situation of multi-source heterogeneous databases, data warehouses, and application scenarios. Two SQL queries are considered similar when the calculation results meet the strict threshold of one algorithm, or the lenient thresholds of both algorithms simultaneously. The similarity clustering operation accurately obtains relevant statistical information such as execution time and number of executions for problematic SQL queries, simplifies the main body of the problematic SQL query, and improves the user experience for developers.

[0025] In one embodiment, the construction process of the SQL governance agent includes: Obtain historical question SQL data with different sources and types, and preprocess the historical question SQL data; The preprocessed historical problem SQL data is sequentially subjected to domain segmentation, semantic segmentation, and fine-grained management to obtain the structured data corresponding to the historical problem SQL data; The structured data is stored in a preset conventional database, and the structured data is semantically processed (embedding) using a semantic vector model to obtain the corresponding high-dimensional vector features; The high-dimensional vector features are subjected to dimensionality reduction, and the dimensionality-reduced vector features are mapped to the same spatial scale. Then, the vector features are stored in a preset vector feature library using compression and indexing. An SQL governance knowledge base is constructed by combining the conventional database and vector feature library, and the SQL governance intelligent agent is built by combining the SQL governance knowledge base and the general large model.

[0026] The analysis module in this embodiment provides fine-grained analysis of problematic SQL queries after similarity clustering. This module primarily relies on the SQL governance agent to provide intelligent, batch, and high-performance analysis conclusions and optimization suggestions. The pre-tested and prepared SQL governance agent inputs relevant information about the problematic SQL queries according to the agent's format requirements, thereby obtaining output results such as performance analysis, index suggestions, optimization examples, detailed optimization suggestions, and performance optimization priorities. Furthermore, the analysis module also has two main functions: quantitative analysis based on statistical information and DBA manual feedback calibration. In terms of quantitative analysis, the system statistically analyzes the execution frequency, average execution time, SQL length, and resource usage of problematic SQL queries within a certain time period, and classifies the governance level of problematic SQL queries into three levels: severe, important, and minor, based on the response time requirements of different application systems. In the manual calibration part, DBAs can sample feedback on the quality of optimization suggestions from the SQL governance agent according to actual needs, calibrate the relevant suggestions, and provide professional DBA optimization suggestions. In addition, the feedback from DBAs in the manual calibration process can further enrich the knowledge base of the SQL governance agent, thereby effectively improving the accuracy of its responses.

[0027] The aforementioned SQL governance intelligent agent, combined with Figure 3 Its specific construction process includes the following four parts: (1) Multi-source information. Multi-source information encompasses raw data from different sources and types, aiming to build a comprehensive knowledge system. Specifically, it includes, but is not limited to, the following categories: document-based information, such as database documents, data warehouse documents, and publicly available online materials; case-based information, such as SQL development specifications, DBA optimization suggestions, and case optimization effects; and question-and-answer information, such as question-and-answer pairs and Frequently Asked Questions (FAQs). Intelligent tools are used to intelligently review the above structured information, including developing parsing toolchains for documents of different formats, using OCR (Optical Character Recognition) technology to process image and table information, and supplementing it with GPU (Graphics Processing Unit) acceleration services to efficiently and intelligently integrate multi-source information into knowledge units.

[0028] (2) Knowledge Units. Knowledge units further process the intelligently integrated information. First, document information is managed by domain, such as classifying and storing different types of databases and data warehouse information to ensure accurate information matching. Second, the parsed text is semantically segmented, and knowledge units are divided according to contextual logic to avoid information fragmentation. Third, fine-grained permission management is implemented for the segmented knowledge units to protect knowledge units that may involve sensitive information and prevent the leakage of sensitive information during output. Finally, the text data is structured and directly stored in the regular database of the knowledge base so that it can be directly output during subsequent matching. In addition, semantic vector models such as BGE (BAAI General Embedding) can be used to semantically process the structured knowledge unit data, extract high-dimensional vector features, and transform them into machine-understandable vector features.

[0029] (3) High-dimensional vector features. After semantic processing, the high-dimensional vector features need to be reduced in dimensionality using methods such as Principal Component Analysis (PCA) to prevent the "curse of high dimensionality." Furthermore, by applying algorithms such as L2 Normalization or L1 Normalization, all high-dimensional vector features are mapped to the same spatial scale, thereby unifying the distance metric and ensuring that similarity calculations only reflect semantic differences. The processed vector features will be stored in a vector feature library, and the IVF_PQ scheme (Inverted File (IVF) - Product Quantization (PQ)) will be used during the storage process to achieve coordinated optimization of "compression + indexing."

[0030] (4) SQL Governance Knowledge Base. The knowledge base mainly consists of a regular database and a vector feature library, among which the vector feature library needs to have millisecond-level high-efficiency vector similarity retrieval capabilities. In addition, the knowledge base also supports a hybrid retrieval mode, that is, combining vector retrieval (to capture semantic similarity) and full-text retrieval (to ensure accurate keyword matching) during the retrieval process, and dynamically optimizing the result ranking through a weighted strategy to improve the retrieval effect.

[0031] In one embodiment, the step of using an SQL governance agent to perform governance analysis on problematic SQL data in the metadata database to obtain corresponding governance analysis results includes: Based on the SQL governance knowledge base, the Retrieval-Augmented Generation (RAG) technology is used to match the problematic SQL data, and prompt words are constructed for the problematic SQL data through the prompting process. Based on the prompt words, the problematic SQL data is input into the SQL governance agent, and the SQL governance agent outputs the corresponding governance analysis results.

[0032] Specifically, in conjunction with the prompt words, the problematic SQL data is input into the SQL governance intelligence, and the SQL governance intelligence outputs the corresponding governance analysis results, including: The problem SQL data is input in a multi-round iterative manner; wherein, the problem SQL data is input in the first round, the SQL execution plan corresponding to the problem SQL data is input in the second round, and the formatted query result corresponding to the problem SQL data is input in the third round.

[0033] The process of obtaining the formatted query results includes: The keyword-based regular expression matching method extracts all table names involved in the problematic SQL data from the execution plan that meets the preset formatting requirements; Based on the database to which the question SQL data belongs, a standard SQL template matching the type is selected, and a query is performed based on the table name to obtain the corresponding formatted query results; wherein, the formatted query results include table structure, data volume and indexes.

[0034] In this embodiment, when performing governance analysis through an SQL governance agent, the first step is to quickly locate knowledge units and historical cases related to the problematic SQL from the SQL governance knowledge base using enhanced search technology. This process can perform multi-dimensional matching based on the characteristics of the SQL statement, key nodes in the execution plan, and table structure information in the formatted query results, ensuring the comprehensiveness and accuracy of the retrieval results. Subsequently, prompting engineering is used to refine the SQL data and construct prompt words. The prompt words not only include the SQL statement itself but also integrate performance bottleneck prompts from the execution plan, successful experiences from historical optimization cases, and data distribution characteristics from the formatted query results. This guides the general model to more accurately understand the SQL governance needs, thereby outputting more targeted governance analysis results. In the input stage of the general model, this embodiment adopts a multi-round iterative approach to gradually deepen the analysis. In the first round, the model receives the original problematic SQL statement and identifies its basic structure and potential problems; in the second round, combined with the execution plan, the model deeply analyzes the execution path and performance bottlenecks of the problematic SQL; in the third round, through the formatted query results, the model further understands the data distribution and index usage, providing data support for the final optimization. Ultimately, based on the aforementioned multi-round inputs and rich cases from the knowledge base, the general-purpose model outputs governance analysis results that include performance analysis, index suggestions, optimization examples, and detailed optimization recommendations. These results not only pinpoint specific problems with the SQL statements but also provide actionable optimization solutions, effectively improving the efficiency of database performance problem governance. Simultaneously, through a DBA-based human feedback calibration mechanism, the system can continuously optimize the accuracy of the SQL governance agent's responses, creating a virtuous cycle.

[0035] Combination Figure 4 In practical applications, the specific operation process for using SQL governance agents for governance analysis is as follows: (1) Provide the initial environment a. Link to the SQL governance knowledge base; b. Provide preset information, such as specifying SQL optimization for a particular database.

[0036] (2) Agent initialization a. Obtain the text data of existing case Q&A pairs from the SQL governance knowledge base, and initialize the Q&A scenario and format; b. Utilize In-Context Learning (ICL) mechanisms to dynamically guide the general large model to learn from existing cases, generate accurate suggestions, and reduce redundant answers and illusions.

[0037] (3) Input SQL statement in the first round. Here, in order to prevent the model output effect from being reduced due to the excessively long input context, multiple rounds of input iteration are used. In the first round, only a single problem SQL statement is input.

[0038] (4) Prompt Engineering Generation. The SQL governance agent uses retrieval enhancement search technology to efficiently match relevant information in the SQL governance knowledge base and constructs prompts through prompt engineering. The content of the prompt engineering includes, but is not limited to, answering questions in the tone and format of the agent's initialization guidance, analyzing SQL from the perspective of a senior database expert, specifying the name of a specific database or data warehouse, supplementing the names of databases or data warehouses with similar functions, and providing references for obtaining information online.

[0039] (5) The SQL governance agent injects the prompt words constructed in the fourth step into the general big model, such as the Qwen3 big model, and at the same time provides the actual text input of the current round to the general big model.

[0040] (6) The general large model outputs information such as performance analysis, indexing suggestions, optimization examples, detailed optimization suggestions, and performance optimization suggestions in a formatted manner.

[0041] (7) The output results of the first round of input are mainly analyzed in depth from the SQL statement level to identify and point out possible unreasonable writing, such as a large number of "union all" association operations of multiple tables, multi-level nested queries, etc.

[0042] (8) In the second round of input, the SQL execution plan recorded in the database or data warehouse in the first round is used. Steps 4 to 6 are repeated. The SQL governance agent analyzes the parts of the record with longer execution times based on the information in the execution plan and compares them with the corresponding content in the SQL statement to further evaluate the unreasonable aspects of the SQL. For parts with a significant impact on performance, the agent will output key optimization suggestions. For example, if a full table scan is performed on a large table, it is recommended to add indexes to optimize performance.

[0043] (9) Quantitatively analyze the execution plan of step 8. Use the keyword-based regular expression matching method to extract all table names involved in the SQL from the well-formatted execution plan.

[0044] (10) In the database or data warehouse where the SQL question in step 3 was tested, select the pre-configured standard SQL template, query the table structure, data volume, indexes and other information of the table obtained in step 9, and return the query results in a uniform format. (11) The third round of input is the formatted query result from step 10. Repeat steps 4 to 6. The SQL governance agent further converges and optimizes suggestions. For example, if a suggestion to create an index is made in step 8, and the index is found to already exist in the query result of step 10, the suggestion will no longer be output.

[0045] In this embodiment, the output of the SQL governance agent is mainly divided into five parts, specifically including performance analysis, detailed optimization suggestions, optimization examples, index suggestions, and performance optimization priorities. The performance analysis section identifies performance bottlenecks through execution plans; the detailed optimization suggestions section elaborates on the specific optimization ideas and rectification logic, providing a useful reference for writing more efficient SQL; the optimization examples section provides a modified SQL example for reference; the index suggestions section provides suggestions on index modification, creation, and deletion; and the performance optimization priorities section concisely summarizes the key optimization points for improving performance, providing developers and DBAs with an intuitive and clear approach to quickly optimize problematic SQL performance.

[0046] In one embodiment, the step of combining the problematic SQL data and the corresponding governance analysis results for display, feedback, and backtracking includes: Based on the access control mechanism, the problematic SQL data and the corresponding governance analysis results are displayed through the data management platform; In response to a user's operation on the problematic SQL data on the data management platform, the problematic SQL data is processed accordingly; Based on the governance analysis results of the problematic SQL data, a quantitative backtracking analysis was performed on the problematic SQL data.

[0047] This embodiment, by demonstrating feedback and backtracking operations, allows developers and DBAs to more intuitively understand the effects of SQL governance and adjust governance strategies according to actual needs. Combined with... Figure 2 Specifically, the display module can showcase information related to problematic SQL queries based on a data management platform. Since SQL queries may contain sensitive information such as hidden data, this embodiment provides a strict permission isolation mechanism based on the data management platform, including three permission roles: DBA permissions, application administrator permissions, and responsible parties for problematic SQL queries. DBAs can manage all problematic SQL queries, and application administrators can manage all problematic SQL queries corresponding to their managed applications. DBAs and application administrators have the authority to assign responsible parties to each problematic SQL query with finer granularity, and the responsible party can only operate on the problematic SQL queries they are responsible for. A user can simultaneously hold one or more of the above three permission roles, and the problematic SQL queries they can manage are the union of all permission roles. Furthermore, this embodiment implements hierarchical management based on the severity, execution frequency, and historical problematic SQL queries of the problematic SQL queries, and provides pagination, filtering, and sorting functions on the display interface. In addition, the display module also provides editing feedback functionality to meet the user's platform-based interaction needs.

[0048] The feedback module relies on the data management platform and provides a user-friendly, platform-based interface. Within this interface, each person responsible for a problematic SQL statement can assign the task to others. For special cases such as one-time index creation, specific business scenario requirements, or maintainable data archiving, the person responsible for the problematic SQL statement can choose to ignore it and briefly explain the reasons for the omission. Furthermore, the system will periodically connect with the workflow system, allowing DBAs and application administrators to jointly evaluate the rationale for the omission. After completing the rectification, the person responsible for the problematic SQL statement must complete a rectification explanation and provide an online testing plan to flexibly test the performance of the rectified SQL. All feedback will be recorded in the storage module, providing data support for subsequent backtracking analysis.

[0049] The backtracking module reads database-related information from the storage module and performs quantitative analysis, covering, but not limited to, the following aspects: tracing the origins of historical situations, such as analyzing whether problematic SQL statements remained unrectified for extended periods; evaluating optimization effectiveness, such as analyzing the trends over time in the number, execution time, and length of problematic SQL statements for each application system; and summarizing common problems, such as common index failure issues and excessively long SQL statements. The backtracking module also provides analysis of the occurrence and rectification of problematic SQL statements in different application systems, as well as a retrospective summary of the rectification ratio and effectiveness for different responsible parties. This data can serve as reference indicators for application security levels and individual performance evaluations. Furthermore, the backtracking module's compilation of optimization methods and effects for historical problematic SQL statements further enriches the knowledge base of the SQL governance agent, thereby improving the analytical accuracy and precision of the SQL governance agent.

[0050] Figure 5 A schematic block diagram of an agent-based SQL automated governance system 500 provided in an embodiment of the present invention. The system 500 includes: The data acquisition and storage unit 501 is used to acquire problem SQL data and perform unified processing on the problem SQL data; Clustering analysis unit 502 is used to perform clustering analysis on the question SQL data to obtain question SQL data of different categories and store them in the metadata database; The governance analysis unit 503 is used to perform governance analysis on the problematic SQL data in the metadata database using the SQL governance intelligent agent, and obtain the corresponding governance analysis results. The feedback unit 504 is used to display feedback and provide backtracking by combining the problematic SQL data and the corresponding governance analysis results.

[0051] In one embodiment, the clustering analysis unit 502 includes: The similarity calculation unit is used to perform cluster analysis on the question SQL data according to the following formula using a similarity clustering algorithm to obtain question SQL data of different categories: ; Where Simi represents similarity, s and t are two finite strings, JaroWinkler represents the Jaro-Winkler edit distance, and NormLev represents the normalized Levenshtein edit distance. and This indicates a relatively strict threshold. and This indicates a relatively lenient threshold.

[0052] In one embodiment, the agent-based SQL automation governance system 500 further includes: A preprocessing unit is used to acquire historical problem SQL data with different sources and types, and to preprocess the historical problem SQL data. The structured processing unit is used to sequentially perform domain-based processing, semantic segmentation, and fine-grained management on the preprocessed historical problem SQL data to obtain the structured data corresponding to the historical problem SQL data. A semantic processing unit is used to store the structured data in a preset conventional database and perform semantic processing on the structured data through a semantic vector model to obtain the corresponding high-dimensional vector features. The dimension reduction mapping unit is used to perform dimension reduction processing on the high-dimensional vector features, map the dimension-reduced vector features to the same spatial scale, and then store the vector features in a preset vector feature library by compression and indexing. The intelligent agent building unit is used to construct an SQL governance knowledge base by combining the conventional database and vector feature library, and to build the SQL governance intelligent agent by combining the SQL governance knowledge base and the general large model.

[0053] In one embodiment, the governance analysis unit 503 includes: The matching construction unit is used to match the problem SQL data based on the SQL governance knowledge base using retrieval enhancement search technology, and to construct prompt words for the problem SQL data through prompt engineering; The model processing unit is used to combine the prompt words, input the problem SQL data into the SQL governance agent, and output the corresponding governance analysis results.

[0054] In one embodiment, the model processing unit includes: A multi-round iteration unit is used to input the problem SQL data through multiple rounds of iteration; wherein, the first round inputs the problem SQL statement, the second round inputs the SQL execution plan corresponding to the problem SQL data, and the third round inputs the formatted query result corresponding to the problem SQL data.

[0055] In one embodiment, the agent-based SQL automation governance system 500 further includes: The table name extraction unit is used to extract all table names involved in the problematic SQL data from the execution plan that meets the preset formatting requirements using a keyword-based regular expression matching method. The query selection unit is used to select a standard SQL template that matches the type of the database to which the question SQL data belongs, and to perform a query based on the table name to obtain the corresponding formatted query result; wherein, the formatted query result includes the table structure, data volume and index.

[0056] In one embodiment, the display feedback unit 504 includes: The display unit is used to display the problematic SQL data and the corresponding governance analysis results through the data management platform based on the permission isolation mechanism; A response unit is used to respond to a user's operation on the problematic SQL data on the data management platform and to perform corresponding processing on the problematic SQL data; The backtracking unit is used to perform quantitative backtracking analysis on the problematic SQL data by combining the governance analysis results of the problematic SQL data.

[0057] Since the embodiments of the system part correspond to the embodiments of the method part, please refer to the description of the embodiments of the method part for the embodiments of the system part, and they will not be repeated here.

[0058] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the systems disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the descriptions are relatively simple; relevant parts can be referred to in the method section. It should be noted that those skilled in the art can make various improvements and modifications to this application without departing from the principles of this application, and these improvements and modifications also fall within the protection scope of the claims of this application.

[0059] It should also be noted that, in this specification, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

Claims

1. An agent-based automated SQL management method, characterized in that, include: Collect problematic SQL data and process the problematic SQL data using a unified standard. Cluster analysis is performed on the question SQL data to obtain question SQL data of different categories, which are then stored in the metadata database; The SQL governance intelligence agent is used to perform governance analysis on the problematic SQL data in the metadata database, and the corresponding governance analysis results are obtained. The system combines the problematic SQL data with the corresponding governance analysis results to provide feedback and backtracking.

2. The agent-based automated SQL management method according to claim 1, characterized in that, The process of clustering the problematic SQL data to obtain different categories of problematic SQL data and storing them in a metadata database includes: The question SQL data is clustered using a similarity clustering algorithm according to the following formula to obtain question SQL data of different categories: ； Where Simi represents similarity, s and t are two finite strings, JaroWinkler represents the Jaro-Winkler edit distance, and NormLev represents the normalized Levenshtein edit distance. and This indicates a relatively strict threshold. and This indicates a relatively lenient threshold.

3. The agent-based automated SQL management method according to claim 1, characterized in that, The construction process of the SQL governance agent includes: Obtain historical question SQL data with different sources and types, and preprocess the historical question SQL data; The preprocessed historical problem SQL data is sequentially subjected to domain segmentation, semantic segmentation, and fine-grained management to obtain the structured data corresponding to the historical problem SQL data; The structured data is stored in a preset conventional database, and the structured data is semantically processed using a semantic vector model to obtain the corresponding high-dimensional vector features. The high-dimensional vector features are subjected to dimensionality reduction processing, and the dimensionality-reduced vector features are mapped to the same spatial scale. Then, the vector features are stored in a preset vector feature library using compression and indexing. An SQL governance knowledge base is constructed by combining the conventional database and vector feature library, and the SQL governance intelligent agent is built and debugged by combining the SQL governance knowledge base and the general large model.

4. The agent-based automated SQL management method according to claim 3, characterized in that, The method of using an SQL governance intelligence agent to perform governance analysis on problematic SQL data in the metadata database and obtaining corresponding governance analysis results includes: Based on the SQL governance knowledge base, the problem SQL data is matched using retrieval enhancement search technology, and prompt words are constructed for the problem SQL data through prompt engineering; Based on the prompt words, the problematic SQL data is input into the SQL governance agent, and the SQL governance agent outputs the corresponding governance analysis results.

5. The agent-based automated SQL management method according to claim 4, characterized in that, The process involves inputting the problematic SQL data into the SQL governance agent in conjunction with the prompt words, and then having the SQL governance agent output the corresponding governance analysis results, including: The problem SQL data is input in a multi-round iterative manner; wherein, the problem SQL data is input in the first round, the SQL execution plan corresponding to the problem SQL data is input in the second round, and the formatted query result corresponding to the problem SQL data is input in the third round.

6. The agent-based automated SQL management method according to claim 5, characterized in that, The process of obtaining the formatted query results includes: The keyword-based regular expression matching method extracts all table names involved in the problematic SQL data from the execution plan that meets the preset formatting requirements; Based on the database type to which the SQL data in question belongs, select the corresponding SQL template and perform a query according to the table name to obtain the corresponding formatted query result; wherein, the formatted query result includes the table structure, data volume and index.

7. The agent-based automated SQL management method according to claim 1, characterized in that, The process of combining problematic SQL data with corresponding governance analysis results for presentation, feedback, and backtracking includes: Based on the access control mechanism, the problematic SQL data and the corresponding governance analysis results are displayed through the data management platform; In response to user operations on the problematic SQL data on the data management platform, perform corresponding processing on the problematic SQL data; Based on the governance analysis results of the problematic SQL data, a quantitative backtracking analysis was performed on the problematic SQL data.

8. An agent-based automated SQL governance system, characterized in that, include: A data acquisition and storage unit is used to acquire problematic SQL data and process the problematic SQL data using a unified standard. The clustering analysis unit is used to perform clustering analysis on the question SQL data to obtain question SQL data of different categories and store them in the metadata database; The governance analysis unit is used to perform governance analysis on problematic SQL data in the metadata database using an SQL governance intelligent agent, and obtain the corresponding governance analysis results. The feedback display unit is used to display feedback and provide backtracking by combining the problematic SQL data with the corresponding governance analysis results.

9. The agent-based SQL automated governance system according to claim 8, characterized in that, Also includes: A preprocessing unit is used to acquire historical problem SQL data with different sources and types, and to preprocess the historical problem SQL data. The structured processing unit is used to sequentially perform domain-based processing, semantic segmentation, and fine-grained management on the preprocessed historical problem SQL data to obtain the structured data corresponding to the historical problem SQL data. A semantic processing unit is used to store the structured data in a preset conventional database and perform semantic processing on the structured data through a semantic vector model to obtain the corresponding high-dimensional vector features. The dimension reduction mapping unit is used to perform dimension reduction processing on the high-dimensional vector features, map the dimension-reduced vector features to the same spatial scale, and then store the vector features in a preset vector feature library by compression and indexing. The intelligent agent building unit is used to construct an SQL governance knowledge base by combining the conventional database and vector feature library, and to build the SQL governance intelligent agent by combining the SQL governance knowledge base and the general large model.

10. The agent-based SQL automated governance system according to claim 9, characterized in that, The governance analysis unit includes: The matching construction unit is used to match the problem SQL data based on the SQL governance knowledge base using retrieval enhancement search technology, and to construct prompt words for the problem SQL data through prompt engineering; The model processing unit is used to combine the prompt words, input the problem SQL data into the SQL governance agent, and have the SQL governance agent output the corresponding governance analysis results.

Images