Text-to-SQL (Structured Query Language) full-link acquisition method

Abstract

The invention discloses a text-to-SQL (Structured Query Language) full-link acquisition method, which belongs to the technical field of diagnosis and comprises the following steps: step 1, interface call interception, step 2, method execution tracking, step 3, SQL execution acquisition, step 4, context association management, step 5, data alignment processing and step 6, data persistence. A data set is automatically constructed, an interface-SQL corresponding relation is automatically collected from an actual service system, the data acquisition cost is greatly reduced, service contexts are reserved, a call chain from an interface to an SQL is completely recorded, service semantics and context information are reserved, customized training is supported, data in a specific service field is collected, and the service efficiency is improved. And a specific Text-to-SQL (Structured Query Language) model in the targeted training field is supported.

Description

Technical Field

[0001] The present invention relates to the field of diagnostic technology, and in particular to a full-link SQL collection method for converting text to SQL. Background Art

[0002] With the rapid development of artificial intelligence (AI), natural language to SQL (Text-to-SQL) technology has garnered widespread attention, particularly with the current proliferation of large-scale model languages. This technology allows users to describe query requirements in natural language, which is then automatically converted into structured SQL statements through semantic understanding by large models, making it easy for even non-technical users to perform data queries. The core of Text-to-SQL technology lies in natural language understanding and SQL generation, but its performance and accuracy rely heavily on the quality and quantity of training data. In the context of existing business systems, understanding current business data and business logic relationships requires the support of business system data.

[0003] Currently, there are several main ways to obtain training data for Text-to-SQL models: 1) Manual annotation: Professionals manually create natural language query and corresponding SQL statement pairs, which is costly and inefficient; 2) Public datasets: Such as Spider and WikiSQL, but these datasets often differ significantly from actual business scenarios; 3) Template generation: Using predefined templates to generate query-SQL pairs, but this lacks diversity and authenticity; 4) Log mining: Extracting SQL statements from database logs, but lacking corresponding natural language descriptions.

[0004] Existing technologies have the following problems: 1) Difficulty in data acquisition: Text-SQL pairs in real business scenarios are difficult to obtain on a large scale; 2) Lack of context: Simple SQL statements lack business context, making it difficult to understand their semantic intent; 3) Alignment challenges: Aligning natural language descriptions with SQL statements requires professional knowledge and has a low degree of automation; 4) Inconsistent data quality: The generated data may have uneven quality, affecting the model training effect.

[0005] Therefore, there is an urgent need for a method that can automatically collect text-SQL pairs from actual business systems to provide sufficient data support for Text-to-SQL model training.

[0006] In light of this, this paper proposes a method for collecting interface-method-SQL association data for natural language to SQL conversion. This method collects interface-method-SQL association data to establish a mapping relationship between the actual business system and the data, thereby providing better data support for large language models to understand natural language and generate SQL queries. Summary of the Invention

[0007] The core technical problem to be solved by this invention is how to collect the mapping relationship between actual business system interfaces, methods, and query statements, and support data storage and persistence to provide training data support for the ChatBI system. This invention provides a method for collecting interface-method-SQL association data for natural language to SQL conversion. By automatically correlating API interface calls, business method execution, and SQL execution, it collects interface-SQL corresponding data in real business scenarios and provides a dataset for text-to-SQL model training.

[0008] To achieve the above effects, the present invention provides the following technical solution: a SQL full-link collection method for converting text to SQL, comprising the following steps:

[0009] Step 1: Interface call interception: Intercept HTTP requests through the Web interceptor, record the request path, method, parameters and interface description, and generate a unique request identifier.

[0010] Step 2: Method execution tracking: Use AOP technology to intercept business method execution, record class name, method name, parameter values ​​and annotation information, and associate them with the request identifier.

[0011] Step 3: SQL execution collection: intercept JDBC operations through the data source proxy, record SQL statements, execution parameters and results, and associate them with corresponding method calls.

[0012] Step 4. Context association management: Maintain the call chain context based on ThreadLocal and establish a three-level association relationship between interface, method and SQL.

[0013] Step 5: Data alignment: Semantically align the interface description, method comments, and SQL statements to generate text-SQL training pairs.

[0014] Step 6. Data persistence: Store the associated data and training pairs in the database.

[0015] Furthermore, step one specifically includes:

[0016] Use Spring's HandlerInterceptor to intercept HTTP requests.

[0017] Generate a globally unique request identifier using the UUID algorithm.

[0018] Store the request identifier in ThreadLocal and the request queue.

[0019] Furthermore, step 2 specifically includes:

[0020] Define AOP aspects to intercept Controller layer and Service layer methods.

[0021] Get JavaDoc comment information through reflection technology.

[0022] Generate a method execution ID, associate it with the request ID, and store it in the method queue.

[0023] Furthermore, step three specifically includes:

[0024] Intercept the execute method of PreparedStatement through the Druid data source proxy.

[0025] Records the original SQL statement, parameterized values, and final executed SQL.

[0026] Associate the SQL execution information with the method identifier and store it in the SQL execution queue.

[0027] Furthermore, the context management in step 4 includes:

[0028] Context transfer within a single thread is achieved through ThreadLocal.

[0029] For asynchronous calls, TaskDecorator is used to pass context.

[0030] For cross-service calls, the request identifier is passed through HTTP headers or RPC context.

[0031] Furthermore, the data alignment in step five includes:

[0032] Extract data from the SQL execution queue at the smallest granularity, using SQL execution as the minimum granularity.

[0033] Associate the business method annotation information in the method queue upward.

[0034] Continue to associate the interface description information in the request queue upward to form a mapping chain.

[0035] Furthermore, the persistent storage in step 6 includes:

[0036] The interface description is treated as a natural language text and is paired with the corresponding SQL statement to form a training pair.

[0037] The storage fields at least include request identifier, interface description, method comment, original SQL and parameterized SQL.

[0038] Furthermore, the data alignment process also includes:

[0039] Extract query intent keywords based on method annotations.

[0040] Semantically align keywords with table names, field names, and conditions in SQL statements.

[0041] Furthermore, a non-invasive design is adopted:

[0042] Data collection is achieved through dynamic proxy technology without the need to modify business system codes.

[0043] The collection process is completed independently through interceptors, AOP agents and data source agents.

[0044] An electronic device includes a processor and a memory, wherein the memory stores a computer program, and when the processor executes the computer program, it implements the SQL full-link collection method as described in any one of the above items.

[0045] The present invention provides a SQL full-link collection method for converting text to SQL, which has the following beneficial effects:

[0046] (1) This invention automatically constructs high-quality training sets, subverting the traditional data acquisition model. By intercepting business system traffic (interface → method → ​​SQL) in real time, it automatically generates massive <natural language description, SQL> training pairs, completely solving the problems of high manual labeling costs and disconnection between public data sets and scenarios, ensuring data authenticity and diversity, collecting parameterized SQL and execution context in real business scenarios, covering complex query logic (such as multi-table association, aggregate calculation), and avoiding the data singleness defect generated by templates.

[0047] (2) The present invention has zero loss of business semantics, and the three-level association chain retains the complete context. It establishes a strong association relationship between interface description → method annotation → SQL statement, accurately anchors business intent with SQL semantics (SELECT * FROM orders WHERE status = 'unpaid'), solves the problem of semantic alignment, deeply understands business logic, extracts query intent keywords through JavaDoc comments, and automatically maps them with SQL structure, providing structured support for the model to understand business domain knowledge.

[0048] (3) The present invention has zero-intrusive deployment and no business system modification. It decouples the collection module from the business code based on dynamic proxy technology (Spring AOP+Druid JDBC proxy). Enterprises only need to introduce the component library to automatically start data collection. The full-link context is transparently transmitted, and the original ThreadLocal+TaskDecorator+cross-service transmission three-level context guarantee mechanism ensures that data association is not lost in asynchronous calls and distributed scenarios.

[0049] (4) This invention drives the evolution of customized models in different fields and provides specialized training libraries for vertical fields. It can quickly build high-quality data sets containing business terms for specific industries such as finance and healthcare, addressing the pain point of poor adaptability of general large models in different fields. It continuously feeds back model iterations, and the persistently stored associated data supports SQL execution effect backtracking, providing closed-loop verification for model optimization.

[0050] (5) This invention activates the intelligent capabilities of ChatBI, provides core fuel for natural language interaction, and produces training data that directly empowers the Text-to-SQL model, allowing non-technical personnel to complete complex data queries through natural language, thereby promoting the implementation of enterprise-level ChatBI systems. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] Figure 1 This is a flow chart of a SQL full-link collection method for converting text to SQL in the present invention;

[0052] Figure 2 This is a schematic diagram of the non-invasive data acquisition process of the SQL full-link collection method for text-to-SQL conversion of the present invention. DETAILED DESCRIPTION

[0053] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the present invention is further described in detail below with reference to specific embodiments.

[0054] Example 1, refer to Figure 1 - Figure 2 :The present invention provides a technical solution:

[0055] A full-link SQL collection method for converting text to SQL.

[0056] The specific implementation steps are as follows:

[0057] Step 1. Based on the Web interceptor mechanism, intercept the API interface calls in the system and record key information. For example, use Spring's HandlerInterceptor to intercept all HTTP requests, record key information such as request path, HTTP method, request parameters, request header and interface description, and use the UUID algorithm to generate a globally unique request identifier, store its data in the request queue, and store the request identifier in ThreadLocal. Step 2. Based on Spring AOP dynamic proxy technology, intercept and record the execution of business methods. For example: define aspects to intercept method calls in the Controller and Service layers, and record class name, method name, method signature, parameter values, comments and other information and generate a unique identifier for method execution to associate with the corresponding request identifier. Use reflection technology to obtain JavaDoc comment information, and finally store the above data in the method queue. Step 3. Implement SQL execution collection based on the JDBC data source agent, intercept and record SQL execution. For example: use Druid monitoring to intercept JDBC operations, such as the execute method of PreparedStatement, and record the original SQ L statement, execution parameters, final SQL, and SQL type. Finally, the SQL execution is associated with the corresponding method execution identifier and request identifier, and stored in the SQL execution queue. Step 4: Context Information Association Management: To ensure contextual relationships, ThreadLocal will be used to pass context within the same thread. Since the focus is on monolithic systems, ThreadLocal is used for context management. If asynchronous calls are used, TaskDecorator will be used to pass context information between asynchronous threads. Remote calls pass context across servers via HTTP headers or RPC context. Step 5: Data Alignment and Persistence: Request identifier, method execution, and SQL execution are aligned. The alignment method uses SQL execution as the finest granularity to extract SQL execution information from the SQL execution queue. Upward association is performed on the method call in the method queue, and then upward association is performed on the request queue to obtain API requests. The request identifier, method execution, and SQL execution data pairs are stored in the database as the final data. Step 6: Data Persistence: The collected interface-SQL association data and the generated training pairs are stored in the database to support model training and iterative optimization.

[0058] An electronic device includes a processor and a memory, wherein the memory stores a computer program, and when the processor executes the computer program, it implements any of the above-mentioned SQL full-link collection methods.

[0059] While embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and variations may be made to these embodiments without departing from the principles and spirit of the invention, and that the scope of the invention is defined by the appended claims and their equivalents.

Claims

1. A SQL full-link collection method for converting text to SQL, characterized by: The following steps are involved: Step 1: Interface call interception: intercept HTTP requests through the Web interceptor, record the request path, method, parameters and interface description, and generate a unique request identifier; Step 2: Method execution tracking: Use AOP technology to intercept business method execution, record class name, method name, parameter values ​​and annotation information, and associate them with the request identifier; Step 3: SQL execution collection: intercept JDBC operations through the data source proxy, record SQL statements, execution parameters and results, and associate them with corresponding method calls; Step 4. Context association management: Maintain the call chain context based on ThreadLocal and establish a three-level association relationship between interface, method and SQL; Step 5: Data alignment: Semantically align the interface description, method comments, and SQL statements to generate text-SQL training pairs. Step 6: Data persistence: Store the associated data and training pairs in the database.

2. The SQL full-link collection method for converting text to SQL according to claim 1 is characterized in that: Step 1 specifically includes: Use Spring's HandlerInterceptor to intercept HTTP requests; Generate a globally unique request identifier through the UUID algorithm; Store the request identifier in ThreadLocal and the request queue.

3. The SQL full-link collection method for converting text to SQL according to claim 1 is characterized in that: Step 2 specifically includes: Define AOP aspects to intercept Controller layer and Service layer methods; Get JavaDoc comment information through reflection technology; Generate a method execution ID, associate it with the request ID, and store it in the method queue.

4. The SQL full-link collection method for converting text to SQL according to claim 1 is characterized in that: Step three specifically includes: Intercept the execute method of PreparedStatement through the Druid data source proxy; Record the original SQL statement, parameterized values, and final executed SQL; Associate the SQL execution information with the method identifier and store it in the SQL execution queue.

5. The SQL full-link collection method for converting text to SQL according to claim 1 is characterized in that: Context management in step 4 includes: Context transfer within a single thread is achieved through ThreadLocal; For asynchronous calls, use TaskDecorator to pass context; For cross-service calls, the request identifier is passed through HTTP headers or RPC context.

6. The SQL full-link collection method for converting text to SQL according to claim 1 is characterized in that: Step 5 of data alignment includes: Extract data from the SQL execution queue with SQL execution as the minimum granularity; Upward association of business method annotation information in the method queue; Continue to associate the interface description information in the request queue upward to form a mapping chain.

7. The SQL full-link collection method for converting text to SQL according to claim 1 is characterized in that: The persistent storage in step 6 includes: The interface description is used as a natural language text and is paired with the corresponding SQL statement to form a training pair; The storage fields at least include request identifier, interface description, method comment, original SQL and parameterized SQL.

8. The SQL full-link collection method for converting text to SQL according to claim 7 is characterized in that: Data alignment processing also includes: Extract query intent keywords based on method annotations; Semantically align keywords with table names, field names, and conditions in SQL statements.

9. The SQL full-link collection method for converting text to SQL according to any one of claims 1 to 8, characterized in that: Adopt non-intrusive design: Data collection is achieved through dynamic proxy technology, without the need to modify business system codes; The collection process is completed independently through interceptors, AOP agents and data source agents.

10. An electronic device comprising a processor and a memory, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the SQL full-link collection method according to any one of claims 1 to 9 is implemented.

Images