A SQL full-link collection method for text-to-SQL

Abstract

The application discloses a SQL full-link collection method for text-to-SQL, and belongs to the technical field of diagnosis, and comprises the following steps: step one, interface call interception, step two, method execution tracking, step three, SQL execution collection, step four, context association management, step five, data alignment processing, and step six, data persistence. The application automatically constructs a data set, automatically collects interface-SQL corresponding relations from an actual business system, greatly reduces data acquisition cost, retains business context, completely records a call chain from an interface to SQL, retains business semantics and context information, supports customized training, collects data in a specific business field, and supports a Text-to-SQL model specific to a targeted training field.

Description

Technical Field

[0001] This invention relates to the field of diagnostic technology, specifically to a method for full-link SQL acquisition oriented towards text-to-SQL conversion. Background Technology

[0002] With the rapid development of artificial intelligence technology, Natural Language Transmission (Text-to-SQL) technology has received widespread attention, especially with the current proliferation of large-scale model languages. This technology allows users to describe their query needs in natural language, which is then semantically understood by large models and automatically converted into structured SQL statements, enabling non-technical users to easily query data. The core of Text-to-SQL technology lies in natural language understanding and SQL generation, and its performance and accuracy largely depend on the quality and quantity of training data. Furthermore, in existing business system scenarios, understanding current business data and logical relationships requires the support of business system data.

[0003] Currently, there are several ways to obtain training data for Text-to-SQL models: 1) Manual annotation: Professionals manually create natural language queries and corresponding SQL statement pairs, which is costly and inefficient; 2) Public datasets: such as Spider, WikiSQL, etc., but these datasets often differ significantly from actual business scenarios; 3) Template generation: Using predefined templates to generate query-SQL pairs, but lacking diversity and realism; 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: It is difficult to acquire text-SQL pairs in real business scenarios 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 inconsistent 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 the training of Text-to-SQL models.

[0006] In view of this, this invention proposes an interface-method-SQL correlation data acquisition method for natural language to SQL conversion. This method establishes a mapping relationship between actual business systems and data by collecting interface-method-SQL correlation 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 this invention aims to solve is: how to collect the mapping relationship between interfaces, methods, and query statements in actual business systems, and support persistent data storage to provide training data support for the ChatBI system. This invention provides a method for collecting interface-method-SQL correlation data for natural language to SQL conversion. By automatically associating API calls, business method executions, and SQL executions, it collects interface-SQL corresponding data in real business scenarios, providing a dataset for Text-to-SQL model training.

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

[0009] Step 1: Intercepting API Calls: Intercept HTTP requests using a web interceptor, record the request path, method, parameters, and API description, and generate a unique request identifier.

[0010] Step 2, Method Execution Tracing: Intercept the execution of business methods using AOP technology, record the class name, method name, parameter values, and comment information, and associate them with the request identifier.

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

[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 processing: 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] A globally unique request identifier is generated using the UUID algorithm.

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

[0019] Furthermore, step two specifically includes:

[0020] Define AOP aspects to intercept methods in the Controller and Service layers.

[0021] Obtain JavaDoc comment information using reflection technology.

[0022] Generate a method execution identifier and associate it with the request identifier, then 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] Record the original SQL statement, parameterized values, and the final executed SQL.

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

[0027] Furthermore, context management in step four includes:

[0028] Context passing within a single thread is achieved using ThreadLocal.

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

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

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

[0032] Data is extracted from the SQL execution queue at the smallest granularity of SQL execution.

[0033] Comment information for business methods in the upward association method queue.

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

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

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

[0037] The stored fields must include at least the request identifier, interface description, method comments, raw SQL, and parameterized SQL.

[0038] Furthermore, data alignment processing also includes:

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

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

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

[0042] Data collection can be achieved through dynamic proxy technology without modifying the business system code.

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

[0044] An electronic device includes a processor and a memory, the memory storing a computer program, wherein the processor executes the computer program to implement the SQL full-link acquisition method as described in any of the preceding claims.

[0045] This invention provides a full-link SQL data collection method for text-to-SQL conversion, which has the following advantages:

[0046] (1) This invention automatically constructs a high-quality training set, subverting the traditional data acquisition mode. By intercepting business system traffic (interface → method → ​​SQL) in real time, it automatically generates a large number of <natural language description, SQL> training pairs, completely solving the problems of high cost of manual annotation and disconnect between public datasets and scenarios, ensuring the authenticity and diversity of data, collecting parameterized SQL and execution context under real business scenarios, covering complex query logic (such as multi-table association, aggregation calculation), and avoiding the data uniformity defects of template generation.

[0047] (2) This invention achieves zero loss of business semantics, preserves the complete context of the three-level association chain, 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 semantic alignment problem, deeply understands business logic, extracts query intent keywords through JavaDoc annotations and automatically maps them with SQL structure, providing structured support for the model to understand business domain knowledge.

[0048] (3) This invention can be deployed without any intrusion or modification of the business system. Based on dynamic proxy technology (Spring AOP + Druid JDBC proxy), the data collection module is decoupled from the business code. Enterprises only need to introduce the component library to automatically start data collection. The entire link context is transparent. The unique three-level context protection mechanism of ThreadLocal + TaskDecorator + cross-service transmission ensures that data association is not lost in asynchronous calls and distributed scenarios.

[0049] (4) This invention drives the evolution of domain-customized models, with a vertical domain-specific training library. For specific industries such as finance and healthcare, it can quickly build high-quality datasets containing business terms, solve the pain point of poor adaptability of general large models, continuously feed back into model iteration, and persistently stored related 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 the generated training data directly empowers the Text-to-SQL model, enabling non-technical personnel to complete complex data queries through natural language, and promotes the implementation of enterprise-level ChatBI systems. Attached Figure Description

[0051] Figure 1 This is a flowchart illustrating the SQL end-to-end data collection method for text-to-SQL conversion according to the present invention.

[0052] Figure 2 This is a schematic diagram of the non-intrusive data acquisition process of the SQL end-to-end collection method for text-to-SQL according to the present invention. Detailed Implementation

[0053] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to specific embodiments.

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

[0055] A method for full-link SQL data collection oriented towards text-to-SQL conversion.

[0056] The specific implementation steps are as follows:

[0057] Step 1: Based on the Web interceptor mechanism, intercept API calls in the system and record key information. For example, use Spring's HandlerInterceptor to intercept all HTTP requests, recording key information such as request path, HTTP method, request parameters, request headers, and interface description. Use the UUID algorithm to generate a globally unique request identifier, store this 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 status of business methods. For example, define an aspect to intercept method calls in the Controller and Service layers, recording information such as class name, method name, method signature, parameter values, and comments, and generating a unique identifier for method execution associated with the corresponding request identifier. JavaDoc comment information can be obtained using reflection technology. Finally, store the above data in the method queue. Step 3: Based on JDBC data source proxy, implement SQL execution collection, intercept and record SQL execution status. For example, use Druid monitoring to intercept JDBC operations, such as the `execute` method of `PreparedStatement`, and record the original SQL. The process involves taking the L statement, execution parameters, final SQL, and SQL type, and then associating the SQL execution with the corresponding method execution identifier and request identifier. This information is stored in the SQL execution queue. Step four, context information management: To ensure context relationships, ThreadLocal is used to pass context within the same thread. Since the focus is on a monolithic system, ThreadLocal is used for context management. If asynchronous calls exist, TaskDecorator is used to pass context information between asynchronous threads. Remote calls pass context across services via HTTP headers or RPC context. Step five, data alignment and persistence: Aligning request identifier - method execution - SQL execution, with SQL execution as the finest granularity, extracts SQL execution information from the SQL execution queue, associates it with method calls in the method queue, and then associates it with API requests in the request queue. The request identifier - method execution - SQL execution data pair is stored in the database as the final data. Step six, data persistence: The collected interface-SQL association data and 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, the memory storing a computer program, and the processor executing the computer program to implement the SQL full-link acquisition method as described above.

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

Claims

1. A method for end-to-end SQL data collection for text-to-SQL conversion, characterized in that, Includes the following steps: Step 1: API call interception: Intercept HTTP requests using a web interceptor, record the request path, method, parameters, and API description, and generate a unique request identifier; Step 2, Method Execution Tracing: Intercept the execution of business methods using AOP technology, record the class name, method name, parameter values, and comment information, and associate them with the request identifier; Step 3: SQL Execution Collection: Intercept JDBC operations through the data source proxy, record the SQL statement, execution parameters and results, and associate them with the corresponding method call; 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 Processing: Extract data from the SQL execution queue with SQL execution as the smallest granularity; associate it upwards with the business method annotation information in the method queue; continue to associate it upwards with the interface description information in the request queue to form a mapping chain of interface description-method annotation-SQL statement, generating text-SQL training pairs; Step 6: Data persistence: Store the associated data and training pairs in the database.

2. The SQL end-to-end acquisition method for text-to-SQL conversion according to claim 1, characterized in that, Step one specifically includes: Use Spring's HandlerInterceptor to intercept HTTP requests; Generate a globally unique request identifier using the UUID algorithm; Store the request identifier in ThreadLocal and the request queue.

3. The SQL end-to-end acquisition method for text-to-SQL conversion according to claim 1, characterized in that, Step two specifically includes: Define AOP aspects to intercept methods in the Controller and Service layers; Obtain JavaDoc comment information using reflection technology; Generate a method execution identifier and associate it with the request identifier, then store it in the method queue.

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

5. The SQL end-to-end acquisition method for text-to-SQL conversion according to claim 1, characterized in that, Step four, context management, includes: Context passing within a single thread is achieved using ThreadLocal; For asynchronous calls, the TaskDecorator is used to pass the context; For cross-service calls, the request identifier is passed through HTTP headers or RPC context.

6. The SQL end-to-end acquisition method for text-to-SQL conversion according to claim 1, characterized in that, In step five, the data alignment process involves using the interface descriptions in the mapping chain as natural language text, which are then paired with the corresponding SQL statements to form training pairs.

7. The SQL end-to-end acquisition method for text-to-SQL conversion according to claim 1, characterized in that, Step five, data alignment, also includes: extracting query intent keywords based on method annotations; and semantically matching and aligning the keywords with table names, field names, and conditions in the SQL statement.

8. A method for full-link SQL acquisition oriented towards text-to-SQL according to any one of claims 1-7, characterized in that, Employs a non-invasive design: Data collection can be achieved through dynamic proxy technology without modifying the business system code; The data collection process is completed independently through interceptors, AOP proxies, and data source proxies.

9. An electronic device comprising a processor and a memory, the memory storing a computer program, characterized in that, When the processor executes the computer program, it implements the SQL full-link acquisition method as described in any one of claims 1-8.

Images