A natural language to structured query language optimization method, system and device

Abstract

The application provides a natural language to structured query language optimization method, system and device, and the method comprises the following steps: extracting business entities and business terms according to the business background information of the data map and the received natural language question; querying the data map according to the business entities to obtain an entity table result set based on semantic similarity and user access permission, and querying a hierarchical knowledge base according to the business terms to obtain a term table result set; merging and deduplicating the entity table result set and the term table result set to obtain a final table set; performing association relationship retrieval in the graph structure of the data map according to the final table set to obtain an association relationship subgraph; serializing the association relationship subgraph, combining and generating prompt words according to key query information; inputting the prompt words into a large language model to generate candidate SQL statements, and then performing execution test, and outputting a final SQL statement according to the test result. The application improves the accuracy, safety and reliability of the generated SQL statement.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to an optimization method, system, and device for converting natural language into structured query language. Background Technology

[0002] With the rise of Retrieval Augmented Generation (RAG) technology and its variants in the field of artificial intelligence, the demand and scenarios for natural language data analysis using database metadata and business knowledge bases combined with Large Language Models (LLMs) are growing. Against this backdrop, Natural Language to Structured Query Language (NL2SQL) technology has become crucial for realizing intelligent data querying and interaction.

[0003] In a typical NL2SQL scenario, the AI ​​agent usually needs to combine two core steps to understand user intent and generate SQL queries: first, schema linking of database metadata, which involves matching references in natural language with specific tables and fields in the database; second, semantic similarity retrieval of the knowledge base to understand business terminology and complex logic. However, current mainstream implementations suffer from the following three main drawbacks: First, there's the issue of rigid knowledge bases. Existing knowledge bases are often simply constructed from fragmented business documents, lacking granularity in access control and business understanding, and treating all users equally. This makes unauthorized access, generating illusions containing false information, and inconsistent results when executing the same query multiple times during NL2SQL operations highly susceptible to problems, severely impacting the system's credibility.

[0004] Second, there is the vulnerability of schema linking. Existing schema linking processes rely heavily on limited and potentially incomplete table and field comments and a small number of sample SQL statements in the database metadata. This reliance creates numerous vulnerabilities in data access control, the accuracy of entity extraction, and the completeness of understanding business terminology, making it difficult to handle complex and ever-changing real-world business queries.

[0005] Third, there is the issue of incomplete relational modeling. Existing solutions primarily rely on foreign key constraints in the database and limited sample data to infer table relationships when generating SQL statements. For the numerous business logic relationships in real-world enterprise environments that are not defined by foreign keys at the database level, this approach leads to significant information loss and misunderstandings, resulting in incorrect or inefficient queries. Summary of the Invention

[0006] In view of this, it is necessary to provide an optimized method, system, and device for converting natural language to structured query language, in order to solve the technical problems of low accuracy, poor security, and incomplete expression of business logic caused by rigid knowledge base, fragile schema links, and incomplete association modeling in the existing NL2SQL technology.

[0007] To address the aforementioned technical problems, in a first aspect, the present invention provides an optimized method for converting natural language to structured query language, comprising: Extract business entities and business terms based on the business background information of the data map and the received natural language questions; Based on semantic similarity and user access permissions, the entity table result set is obtained by querying the data map according to the business entities, and the term table result set is obtained by querying the hierarchical knowledge base according to the business terms. The entity table result set and the terminology table result set are merged and deduplicated to obtain the final table set; In the graph structure of the data map, a relationship retrieval is performed based on the final set of tables to obtain a subgraph of relationships containing related tables and their relationships; The relationship subgraph is serialized, and prompt words are generated based on key query information; the key query information includes the serialized relationship subgraph, the business background information, the terminology knowledge fragments obtained from the query, and the natural language question. Input the prompt words into the large language model to generate candidate structured query language statements, perform execution tests on the candidate structured query language statements, and output the final structured query language statement based on the test results.

[0008] In one possible implementation, the step of obtaining an entity table result set by querying a data map based on the business entities and user access permissions, and obtaining a terminology table result set by querying a hierarchical knowledge base based on the business terms, includes: The extracted business entities and business terms are converted into vector representations; The business entity vectors are matched with the entity label vectors of the tables in the data map based on similarity, and then filtered according to the user's access permissions to the data map to obtain the entity table result set. The business term vectors are matched with the vectors of atomic knowledge fragments in the hierarchical knowledge base based on similarity. The term knowledge fragments are then filtered according to the user's access permission level to the hierarchical knowledge base. Finally, the relevant tables are extracted from the term knowledge fragments to obtain the term table result set.

[0009] In one possible implementation, merging and deduplicating the entity table result set and the terminology table result set to obtain the final table set includes: Examine each table in the entity table result set and the terminology table result set to determine whether the current user has access permissions; Remove all tables without access permissions from the entity table result set and the terminology table result set. The tables without access permissions are those that do not have the access permissions. The entity table result set and the terminology table result set after removing the table with no permissions are combined into a list, and the table with duplicate table names in the list are removed to obtain the final table set.

[0010] In one possible implementation, the data map is a graph structure containing database metadata, the nodes of the graph structure are tables, the attributes of the nodes include entity labels and the business background information, the edges of the graph structure represent the relationships between the tables, and the relationships include foreign key relationships and logical relationships; The hierarchical knowledge base stores the atomic knowledge fragments, which are structured query language code fragments corresponding to the business terms.

[0011] In one possible implementation, the step of performing a relational retrieval based on the final table set within the graph structure of the data map to obtain a relational subgraph containing related tables and their relationships includes: Using the tables in the final set of tables as target nodes, retrieve the minimum association path connecting all the target nodes in the graph structure of the data map; Extract the subgraph formed by all nodes and edges on the minimum associated path as the associated relationship subgraph.

[0012] In one possible implementation, the serialization of the association subgraph includes: The node and edge information in the relationship subgraph are converted into a structured text format supported by the large language model.

[0013] In one possible implementation, generating prompt words based on key query information includes: The structured prompts are generated according to a predetermined template based on the key query information; or, The structured prompts are generated according to a predetermined template, combining the key query information and the optimized query information.

[0014] In one possible implementation, the step of performing an execution test on the candidate structured query language statements and outputting the final structured query language statement based on the test results includes: Execute the test on the candidate structured query language statements and obtain the test results; If the test result is an execution exception, the exception information will be fed back to the large language model for correction; If the test result is normal, the returned sample data will be fed back to the large language model for verification. After the verification is passed, the candidate structured query language statement will be output as the final structured query language statement.

[0015] Secondly, the present invention also provides an optimized system for converting natural language to structured query language, comprising: The extraction module is used to respond to the received natural language question, and combine the business background information from the data map to perform semantic analysis and intent recognition on the natural language question in order to extract business entities and business terms. The query module is used to obtain an entity table result set by querying the data map based on the business entity and user access permissions, and to obtain a term table result set by querying the hierarchical knowledge base based on the business terminology. The merge and deduplication module is used to merge and deduplicat the entity table result set and the terminology table result set to obtain the final table set. The retrieval module is used to perform relationship retrieval in the graph structure of the data map based on the final set of tables, and obtain a subgraph of relationships containing related tables and their relationships; The generation module is used to serialize the relationship subgraph, and combine the serialized relationship subgraph, the business background information, the queried terminology knowledge fragments, and the natural language question into prompt words, which are then input into the large language model to generate a structured query language statement. The processing module is used to perform execution tests on the structured query language statement and output the final structured query language statement based on the test results.

[0016] Thirdly, the present invention also provides an electronic device, including a memory and a processor, wherein, The memory is used to store programs; The processor, coupled to the memory, is used to execute the program stored in the memory to implement the steps in the optimization method for converting natural language to structured query language as described in any of the above implementations.

[0017] The beneficial effects of this invention are as follows: The optimized method for converting natural language to structured query language provided by this invention, firstly, by introducing business background information from the data map, allows LLM to understand the problem at the semantic level rather than the literal level, significantly reducing intent drift. Simultaneously, it extracts business entities and business terms, laying the foundation for parallel input in subsequent "dual-path retrieval" and preventing terminology information from being missed in later stages. By merging and deduplicating the entity table result set and the terminology table result set to generate the minimum necessary table set (the final table set), irrelevant table noise is avoided, the LLM context length is reduced, and refined input information is provided for the next step of dynamically generating relational subgraphs, improving graph retrieval efficiency. Furthermore, serializing the relational subgraphs facilitates LLM recognition and significantly improves the accuracy of SQL statement generation. Furthermore, multiple candidate SQL statements are generated at once and automatically executed in a sandbox to filter syntax errors. If an error occurs, the error information is sent back to the LLM for syntax correction; if execution is successful, sample data is sent back to the LLM for result confirmation, forming a closed loop of execution-feedback-correction, completely eliminating manual debugging. Moreover, the final SQL statement returned in this way is both syntactically and semantically correct and can be directly used in production. This application utilizes data maps and hierarchical knowledge bases to jointly construct a semantically accurate, permission-controlled, dynamically structured, and closed-loop verified NL2SQL end-to-end process, which can realize automatic reasoning and generation of SQL statements, effectively utilize the powerful semantic capabilities of data maps and hierarchical knowledge bases, and strictly adapt to their permission scope restrictions, so as to systematically improve the accuracy, security, reliability, and business relevance of natural language data queries. Attached Figure Description

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

[0019] Figure 1 A schematic flowchart of an embodiment of the optimization method for converting natural language to structured query language provided by the present invention; Figure 2 For the present invention Figure 1 A schematic diagram of an embodiment of S200; Figure 3 For the present invention Figure 1 A schematic diagram of an embodiment of S300; Figure 4 For the present invention Figure 1 A schematic diagram of an embodiment of the S400; Figure 5A schematic diagram of an embodiment of the optimized system for converting natural language to structured query language provided by the present invention; Figure 6 A schematic diagram of an embodiment of the electronic device provided by the present invention. Detailed Implementation

[0020] 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 a part of the embodiments of the present invention, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0021] In the description of the embodiments of the present invention, unless otherwise stated, "multiple" means two or more. "And / or" describes the relationship between related objects, indicating that there can be three relationships. For example, A and / or B can represent three situations: A exists alone, A and B exist simultaneously, and B exists alone.

[0022] The terms "first," "second," etc., used in the embodiments of this invention are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a technical feature defined with "first" or "second" may explicitly or implicitly include at least one of that feature.

[0023] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of the invention. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0024] Before demonstrating the embodiments, the following terms will be explained.

[0025] NL2SQL is an abbreviation for "Natural Language to SQL," which refers to the technology that automatically translates data query requests made by users in everyday language into a structured query language (SQL) that the database can execute.

[0026] Top-K strategy: This refers to keeping only the top K candidates with the highest probability or score at each step of the decision or output, and discarding all others. Simply put, it's about "taking the top K".

[0027] This invention provides an optimization method, system, and device for converting natural language to structured query language, which will be described below.

[0028] Figure 1 A schematic flowchart of an embodiment of the optimization method for converting natural language to structured query language provided by the present invention is shown below. Figure 1 As shown, optimization methods for converting natural language to structured query language include: S100. Extract business entities and business terms based on the business background information of the data map and the received natural language questions.

[0029] In some embodiments of the present invention, the data map is a graph structure containing database metadata. The nodes of the graph structure are tables, and the attributes of the nodes include entity labels and the business background information. The edges of the graph structure represent the relationships between the tables, including foreign key relationships and logical relationships. The hierarchical knowledge base stores the atomic knowledge fragments, which are structured query language code fragments corresponding to the business terms.

[0030] It should be noted that the data map is a cross-system metadata graph, which mainly includes: a) Metadata: providing table and field annotations. b) Relationships: including foreign key relationships and logical relationships, stored in a graph structure. c) Entity tags: in addition to table annotations, each table will have multiple entity tags attached as needed. For example, the "t_department" table may contain multiple entity tags such as department, organizational structure, department structure, department level, department type, and department status. d) Business background: an overview of the business involved in the current data domain, descriptions of sub-businesses, etc.

[0031] In response to user-received natural language questions, the data map utilizes "business background information" to provide context for the LLM (Local Management Module). This context allows the LLM to better understand the semantics of the "natural language questions," thereby accurately decomposing and extracting business entities and business terms. Business entities typically refer to the main objects and noun concepts in the business, often corresponding to tables in the database. Business terms typically refer to metrics, calculation rules, and specific conditions in the business, often corresponding to SQL fragments (calculation formulas, WHERE conditions, etc.).

[0032] S200. Based on semantic similarity and user access permissions, query the data map according to the business entities to obtain the entity table result set, and query the hierarchical knowledge base according to the business terms to obtain the terminology table result set.

[0033] It should be noted that the hierarchical knowledge base is a dedicated knowledge base built for NL2SQL, which mainly includes: a) Atomic knowledge: business terms and their representation in SQL statements, which can be combinations of column names, combinations of filter conditions, or subqueries, i.e., SQL fragments. Aliases in these fragments have been processed into actual table names and column names. b) Access permissions: Priority knowledge merging from personal domain, business domain, and organizational domain based on the current user information. c) User personal information (not mandatory): The current user's work background, such as areas of expertise, business scope, job responsibilities, personal habits, etc. The hierarchical knowledge base is initially empty or contains only a small amount of seed knowledge. User access permissions follow a hierarchical priority strategy: personal domain permissions > business domain permissions > organizational domain permissions. The entity table result set is obtained by matching business entities with the data map, and the terminology table result set is obtained by matching business terms with the hierarchical knowledge base. A more accurate term for the "terminology table result set" is the terminology knowledge fragment result set. From these terminology knowledge fragments, the tables associated with these business term definitions can be parsed, forming another set of tables, which is the terminology knowledge fragment result set.

[0034] S300. Merge and deduplicate the entity table result set and the terminology table result set to obtain the final table set.

[0035] It should be noted that entity paths and terminology paths may both hit the same table (e.g., the "Orders" table is hit by both the entity "Orders" and the term "Sales"), leading to redundancy in subsequent subgraphs and interfering with the LLM. Therefore, the entity table result set and the terminology table result set will be merged, and duplicate tables in the merged result will be deleted or filtered out to obtain the final table set.

[0036] S400. In the graph structure of the data map, perform a relational retrieval based on the final table set to obtain a relational subgraph containing related tables and their relational relationships.

[0037] It should be noted that the relationship retrieval is performed using query statements written in a graph query language. The graph query statement takes the final set of tables as input parameters. The graph query language is Cypher or Gremlin. The relationship retrieval is subject to user access permissions and only retrieves tables and their relationships that the user has the right to access.

[0038] S500: Serialize the relationship subgraph and generate prompt words based on key query information; the key query information includes the serialized relationship subgraph, the business background information, the terminology knowledge fragments obtained from the query, and the natural language question. S600. Input the prompt words into the large language model to generate candidate structured query language statements, perform execution tests on the candidate structured query language statements, and output the final structured query language statement based on the test results.

[0039] In summary, the optimized method for converting natural language to structured query language provided in this invention, by introducing business background information from a data map, enables LLM to understand the problem at a semantic level rather than a literal level, significantly reducing intent drift. Simultaneously, it extracts business entities and terms, laying the foundation for parallel input in subsequent "dual-path retrieval" and preventing terminology information from being missed in later stages. By merging and deduplicating the entity table result set and the terminology table result set to generate the minimum necessary table set (the final table set), irrelevant table noise is avoided, the LLM context length is reduced, and refined input information is provided for the next step of dynamically generating relational subgraphs, improving graph retrieval efficiency. Furthermore, serializing the relational subgraphs facilitates LLM recognition and significantly improves the accuracy of SQL statement generation. Furthermore, multiple candidate SQL statements are generated at once and automatically executed in a sandbox to filter syntax errors. If an error occurs, the error information is sent back to the LLM for syntax correction; if execution is successful, sample data is sent back to the LLM for result confirmation, forming a closed loop of execution-feedback-correction, completely eliminating manual debugging. Moreover, the final SQL statement returned in this way is both syntactically and semantically correct and can be directly used in production. This application utilizes data maps and hierarchical knowledge bases to jointly construct a semantically accurate, permission-controlled, dynamically structured, and closed-loop verified NL2SQL end-to-end process, which can realize automatic reasoning and generation of SQL statements, effectively utilize the powerful semantic capabilities of data maps and hierarchical knowledge bases, and strictly adapt to their permission scope restrictions, so as to systematically improve the accuracy, security, reliability, and business relevance of natural language data queries.

[0040] In some embodiments of the present invention, such as Figure 2 As shown, step S200 includes: S210. Convert the extracted business entities and business terms into vector representations.

[0041] It's important to note that mapping words or phrases in human language to points in a high-dimensional numerical space that computers can understand and compute ensures that semantically similar words are also located close together in the vector space. The following is a specific implementation method for converting business entities and terms into vector representations: Select a suitable pre-trained text embedding model. Depending on computational resources and accuracy requirements, different models can be chosen (e.g., lightweight / fast models like Sentence-T5 and all-MiniLM-L6-v2, or high-precision models like BERT and RoBERTa). Perform simple preprocessing on the extracted business entities and terms to ensure the quality of the text before inputting it into the text embedding model. Preprocessing may include cleaning and standardization. Cleaning may include removing leading and trailing spaces and special characters, while standardization may include converting to lowercase (optional, depending on the model). Input the preprocessed text into the loaded text embedding model to obtain its corresponding fixed-dimensional vector (e.g., 384-dimensional, 768-dimensional, or 1536-dimensional). Use the above text embedding model to convert all business entities and terms into vectors. These vectors are stored in a vector database (such as Chroma, Weaviate, Qdrant, Pinecone) and associated with the original text (label name, term definition) and the corresponding metadata (table name, term ID). In addition, the vector database creates efficient indexes (such as HNSW) for these vectors, so that the most similar vector can be found quickly during queries without the need for a full table scan.

[0042] S220. Perform similarity matching between the business entity vector and the entity label vector of the table in the data map, and filter according to the user's access permissions to the data map to obtain the entity table result set. S230. Perform similarity matching between the business term vector and the vector of atomic knowledge fragment in the hierarchical knowledge base, filter according to the user's access permission level to the hierarchical knowledge base to obtain the term knowledge fragment, and extract the relevant tables from the term knowledge fragment to obtain the term table result set.

[0043] It should be noted that after vectorizing the business entities to obtain business entity vectors, cosine similarity is calculated between these business entity vectors and the vectors of entity labels attached to all tables in the data map (such as the "product" label). Based on a preset threshold (e.g., similarity > 0.8), all tables with high similarity are filtered out, forming a preliminary set of entity tables. Subsequently, according to the system's user access permission configuration (Access Control List), this set of entity tables is filtered, retaining only the tables that users have permission to access, forming the final entity table result set.

[0044] After vectorizing business terms to obtain business term vectors, cosine similarity is calculated between these business term vectors and the vectors of atomic knowledge fragments stored in the hierarchical knowledge base, and preliminary filtering is performed based on a similarity threshold. Subsequently, permission filtering is performed, that is, the system checks which level the current user has permission to access the term definition according to the priority of personal domain > business domain > organizational domain, and only retains the highest priority and matching term knowledge fragments, and extracts the relevant tables from the term knowledge fragments to obtain the term table result set.

[0045] This invention maps business entities and business terms to a unified vector space, enabling semantically similar but literally different expressions (such as "order quantity" vs. "order_qty" vs. "sales quantity") to be identified as belonging to the same category based on vector distance, greatly improving recall. Even with varied expressions of business terms, the correct target can be found. Furthermore, converting unstructured text data into structured numerical vectors allows computers to accurately calculate the semantic relevance between two concepts (through measures such as cosine similarity), which forms the mathematical foundation for automated and intelligent retrieval. Furthermore, by using cosine similarity calculations between entity label vectors in the data map and business entity vectors, the "synonymous but different characters" problem is solved, improving recall. Immediately after vector recall, Boolean filtering based on "user-table-level visibility" is applied to ensure the entity table result set is within the "user's permission range." This permission filtering significantly reduces the data scope that subsequent steps (such as relational retrieval and SQL generation) need to process, leaving only semantically relevant tables that the user has access to. This improves overall system performance and prevents unauthorized queries when generating SQL statements, seamlessly integrating intelligent semantic retrieval with enterprise-level data security. This addresses one of the biggest security vulnerabilities of NL2SQL in commercial applications, improving the security of generated SQL queries. Additionally, the similarity score naturally provides a ranking criterion; the system can directly use the Top-N as the entity table result set, avoiding empirical errors caused by manually set thresholds. Furthermore, by using cosine similarity calculations between atomic knowledge fragments stored in the hierarchical knowledge base and business term vectors, the "synonymous but different characters" problem is solved, improving recall. Immediately after vector recall, Boolean filtering based on "user-table level visibility" is applied to ensure the entity table result set is within the "user's permission range." This permission filtering significantly reduces the data scope that subsequent steps (such as relational retrieval and SQL generation) need to process, leaving only semantically relevant tables that the user has permission to access. This improves overall system performance and prevents unauthorized queries during subsequent SQL statement generation, seamlessly integrating intelligent semantic retrieval with enterprise-level data security. This addresses one of the biggest security vulnerabilities of NL2SQL in commercial applications, improving the security of generated SQL queries. Additionally, the similarity score naturally provides a ranking criterion, allowing the system to directly select the Top-N as the entity table result set, avoiding empirical errors caused by manually set thresholds. Moreover, the hierarchical knowledge base and its permission-level filtering mechanism (personal domain > business domain > organizational domain) allow the system to provide knowledge fragments most suitable for the context of different users, making the generated SQL more consistent with the user's true intent and business habits, greatly improving user experience and result accuracy.Unlike static data maps, hierarchical knowledge bases can be continuously accumulated and evolved through use. Each successful query can be precipitated as new atomic knowledge, enabling the system to leverage this ever-growing hierarchical knowledge base to further improve user experience and result accuracy.

[0046] In many data applications, permission checks are often performed after the SQL statement is generated but before its actual execution. This can lead to the system generating SQL statements that the user does not have permission to access, which are then rejected by the database during final execution, resulting in wasted computing resources and an interrupted user experience. To address the above problems, in some embodiments of the present invention, such as... Figure 2 As shown, step S300 includes: S310. Check each table in the entity table result set and the terminology table result set to determine whether the current user has access permissions; S320. Remove all tables without permissions from the entity table result set and the terminology table result set, wherein the tables without permissions are tables that do not have the access permissions. S330. Combine the entity table result set and the terminology table result set after removing the table with no permissions into a list, and remove tables with duplicate names from the list to obtain the final table set.

[0047] It should be noted that a unified permission management platform (such as Apache Ranger, Sentry, or an internally developed permission center) can be used to query the current user's valid permission list by calling the API provided by the permission management platform. Alternatively, the valid permission list in the underlying database can be queried directly. A system-wide updated valid permission list can also be maintained (e.g., in Redis or memory) and periodically synchronized from the centralized permission management system. This significantly improves the checking speed and avoids initiating a network API call for each retrieval. The valid permission list is iterated and queried repeatedly, checking if the table identifier exists in the list of valid permissions the user has access to. If it exists, access is confirmed; otherwise, access is denied. Then, all tables without permissions in the entity table result set and the terminology table result set are filtered out. Finally, the entity table result set and terminology table result set (excluding tables with no permissions) are merged and deduplicated to obtain the final table set.

[0048] This application provides a method to pre-emptively check permissions, cutting off the possibility of any unauthorized data entering subsequent processes from the source (input set), thereby improving the overall system security. This application filters all unauthorized tables in the entity table result set, leaving only tables with access permissions, and filters all unauthorized tables in the terminology table result set, leaving only tables with access permissions. The entity table result set and the terminology table result set after filtering out unauthorized tables are combined into a list. Then, this list is deduplicated, removing elements or data with duplicate table names. The final table set contains tables that are visible to the user, without redundancy, and with authorized access. This ensures that when generating SQL statements, the possibility of LLM generating SQL statements containing unauthorized table access is fundamentally eliminated, achieving the most thorough data security. Furthermore, one method of deduplication is to remove adjacent and identical data after sorting; that is, first sort the list, then iterate and remove elements identical to the previous element. This considers both entity semantics (Set_Entity) and business logic semantics (Set_Term), and the combination of these two ensures the integrity of the table set. The deduplication operation ensures that subsequent steps (graph relationship retrieval) focus only on the most essential tables, avoiding the introduction of irrelevant tables and relationships into the massive data map, thus guaranteeing simplicity and accuracy, and improving the accuracy and efficiency of SQL generation. The deduplication operation significantly reduces the computational complexity of graph retrieval algorithms, avoiding the retrieval of a large number of irrelevant relationship paths, thereby improving the overall system processing efficiency. It also avoids introducing multiple copies of a table or irrelevant tables into the relationship subgraph, thus preventing the Large Language Model (LLM) from being interfered with by excessive and redundant schema information, significantly improving the accuracy and readability of the final generated SQL statement.

[0049] In some embodiments of the present invention, such as Figure 4 As shown, step S400 includes: S410. Using the tables in the final set of tables as target nodes, retrieve the minimum association path connecting all the target nodes in the graph structure of the data map. S420. Extract the subgraph formed by all nodes and edges on the minimum associated path as the associated relationship subgraph.

[0050] It's important to note that the system treats the tables in the final table set as target nodes. It executes a graph retrieval algorithm in a graph database (such as Neo4j) to find the shortest path connecting all these target nodes. The graph retrieval algorithm can employ a shortest path algorithm to calculate the shortest path between any two tables and construct a subgraph by merging all paths. This involves traversing all possible table pairs in the final table set and using a graph database's shortest path algorithm (such as Dijkstra's algorithm) for each pair to find the shortest path between them. The shortest path may not directly connect the two tables but rather through one or more intermediate tables. Merging all nodes and edges along the shortest paths forms the final association subgraph. Alternatively, the graph retrieval algorithm can use a Steiner tree approximation algorithm to find the subgraph containing all target nodes and having the smallest total edge weight. The resulting association subgraph contains all tables in the final table set, necessary intermediate tables, and all associations (foreign keys or logical relationships) connecting these tables. When there are multiple associated paths between nodes in two tables, the optimal path is selected and included in the association subgraph according to a predefined path selection strategy, which is based on at least one of association type, confidence level, or path length.

[0051] For example, a data map contains relationships between tables. Tables A and B might be linked by a common field `id`, and tables B and C might be linked by a common field `bh`. These relationships between A, B, C, `id`, and `bh` are stored as graph data in the data map and can be retrieved using the Cyhper statement.

[0052] This application treats the final set of tables as a dynamic, ever-changing "target node set." It calculates the shortest path connecting them in real-time within the graph database, adapting to different users' query intentions and generating the most suitable association scheme for each question, greatly improving flexibility. Furthermore, it provides a minimum association path algorithm (such as the shortest path algorithm or Steiner tree approximation algorithm), aiming to find the path with the fewest edges or the optimal weight connecting all target nodes. This ensures that all queried tables are effectively connected to form a complete data view, avoiding data silos. It also automatically finds the "shortest" or "most reliable" path, avoiding the generation of inefficient SQL statements containing redundant JOIN operations, thus improving query efficiency from the source. Furthermore, the minimum path retrieval algorithm naturally discovers and introduces necessary intermediate tables. For example, to join tables A and C, the algorithm might find that joining through table B is the shortest path (A->B->C), resolving indirect association problems that users and LLMs might not be aware of, thereby generating correct SQL. Furthermore, the extracted relationship subgraph is a highly condensed context. The relationship subgraph only contains tables, fields and their relationships that are strongly related to the current problem. It is a minimal complete subset of the global data map. In this way, it provides the LLM with the most accurate and concise schema information, greatly reducing the understanding burden of the LLM and enabling it to focus on the core business logic, thereby significantly improving the accuracy and reliability of SQL generation.

[0053] In some embodiments of the present invention, the serialization of the association subgraph includes: The node and edge information in the relationship subgraph are converted into a structured text format supported by the large language model.

[0054] It's important to note that this involves converting complex graph data structures into a text format that large models can directly understand and process, or converting structured data (such as graphs and objects) into linear text. The complete data map contains all tables and relationships. In a graph database, tables are nodes, and relationships are edges, forming a network structure. After intent recognition and similarity retrieval, a small number of relevant nodes are obtained. Then, based on the complete information in the network structure, the necessary relationships are extracted to obtain a subgraph of relationships. The node and edge information in the subgraph of relationships is then converted into a structured text format. This structured text format can be JSON. The JSON format contains a `nodes` array to describe table definitions and an `edges` array to describe the relationships between tables. Each node object in the `nodes` array contains the table name, table comments, and column definitions. Each edge object in the `edges` array contains the source table name, target table name, relationship type, and relationship conditions.

[0055] Of course, the structured text format can also be natural language descriptive text generated based on a predefined template, which sequentially describes the definition of each table in the relationship subgraph and the relationships between the tables. Alternatively, the structured text format can be a hybrid format, where table definition information is organized in tabular form, while the relationships between tables are organized in natural language descriptive form.

[0056] In some embodiments of the present invention, the step of generating prompt words based on key query information includes: The structured prompts are generated according to a predetermined template based on the key query information; or, The structured prompts are generated according to a predetermined template, combining the key query information and the optimized query information.

[0057] It should be noted that optimizing query information provides additional context, helping the large model make judgments more aligned with user preferences in ambiguous or polysemous situations. This is a "gain" to improve experience and accuracy, not a "necessary" requirement for ensuring functionality. Optimized query information includes user personal information from the above embodiments. User personal information plays a role in personalization and context supplementation, rather than being a fundamental requirement for schema linking or SQL generation. By making it optional, it can be applied in the initial implementation phase or in environments where collecting user information is inconvenient. Even without personalized information, the system can still generate accurate SQL statements based on objective business knowledge in the data map and knowledge base, ensuring the robustness of the method. With user personal information available, the system can further optimize the results to better reflect the user's true intent and personal habits, especially when handling fuzzy queries.

[0058] For example, the system can combine multiple key pieces of information into a final prompt word based on a predefined structured template. These key pieces of information include, in order: business background information, terminology knowledge fragments, serialized relational subgraphs, and natural language questions input by the user.

[0059] In some embodiments of the present invention, the step of performing execution tests on the candidate structured query language statements and outputting the final structured query language statement based on the test results includes: Execute the test on the candidate structured query language statements and obtain the test results; If the test result is an execution exception, the exception information will be fed back to the large language model for correction; If the test result is normal, the returned sample data will be fed back to the large language model for verification. After the verification is passed, the candidate structured query language statement will be output as the final structured query language statement.

[0060] It should be noted that the process involves sequentially executing multiple candidate structured query language statements generated by the large language model until one succeeds or all fail. The generated structured prompts are input into the LLM (e.g., GPT-4). The LLM generates one or more candidate SQL statements based on the rich context provided. The system employs a Top-K strategy, executing these candidate SQL statements sequentially in a secure test database environment. If an exception occurs (e.g., syntax error, missing field), the exception information is fed back to the LLM, requesting correction and regeneration, forming a correction loop. If execution is successful, the returned sample data (the first N rows) is retrieved and resubmitted to the LLM for semantic consistency verification (i.e., determining whether the sample data reasonably answers the original question). Upon successful verification, the SQL statement is determined as the final SQL statement and output. If all candidate SQL statements fail or cannot pass verification, the process terminates and returns an error.

[0061] This application employs parallel retrieval: based on the decomposition of business entities and business terms into a large model, it simultaneously triggers entity table retrieval and terminology retrieval. Retrieval results are dynamically constrained by user access permissions (personal domain > business domain > organizational domain), addressing scenarios where business terms are independent of table structures (such as calculated metrics and general filtering conditions) and avoiding semantic loss. Furthermore, the NL2SQL process integrates data maps and terminologies, providing more accurate and richer prompts. In addition, through a unified metadata layer of the data map, tables from different databases (such as MySQL and Hive) are abstracted into virtual relational graphs, enabling the generation of SQL statements from multiple databases.

[0062] To better implement the optimization method for converting natural language to structured query language in this invention embodiment, based on the optimization method for converting natural language to structured query language, correspondingly, as follows: Figure 5 As shown, this embodiment of the invention also provides an optimization system for converting natural language to structured query language. The optimization system 500 for converting natural language to structured query language includes: The extraction module 501 is used to respond to the received natural language question, and combine the business background information from the data map to perform semantic analysis and intent recognition on the natural language question in order to extract business entities and business terms. The query module 502 is used to obtain an entity table result set by querying the data map based on the business entity according to semantic similarity and user access permissions, and to obtain a term table result set by querying the hierarchical knowledge base based on the business terminology. The merging and deduplication module 503 is used to merge and deduplicat the entity table result set and the terminology table result set to obtain the final table set. The retrieval module 504 is used to perform a relational retrieval in the graph structure of the data map based on the final set of tables, and obtain a relational subgraph containing related tables and their relational relationships. The generation module 505 is used to serialize the association subgraph, and combine the serialized association subgraph, the business background information, the queried terminology knowledge fragments and the natural language question into prompt words, which are then input into the large language model to generate a structured query language statement. The processing module 506 is used to perform execution tests on the structured query language statement and output the final structured query language statement based on the test results.

[0063] The natural language to structured query language optimization system 500 provided in the above embodiments can implement the technical solutions described in the above natural language to structured query language optimization method embodiments. The specific implementation principles of each module or unit can be found in the corresponding content in the above natural language to structured query language optimization method embodiments, and will not be repeated here.

[0064] like Figure 6 As shown, the present invention also provides an electronic device 600. The electronic device 600 includes a processor 601, a memory 602, and a display 603. Figure 6 Only some components of the electronic device 600 are shown, but it should be understood that it is not required to implement all the components shown, and more or fewer components may be implemented instead.

[0065] In some embodiments, processor 601 may be a central processing unit (CPU), microprocessor, or other data processing chip, used to run program code stored in memory 602 or process data, such as the natural language to structured query language optimization method of the present invention.

[0066] In some embodiments, processor 601 may be a single server or a group of servers. The server group may be centralized or distributed. In some embodiments, processor 601 may be local or remote. In some embodiments, processor 601 may be implemented on a cloud platform. In one embodiment, the cloud platform may include a private cloud, public cloud, hybrid cloud, community cloud, distributed cloud, intranet, multi-cloud, etc., or any combination thereof.

[0067] In some embodiments, memory 602 may be an internal storage unit of electronic device 600, such as a hard disk or memory of electronic device 600. In other embodiments, memory 602 may also be an external storage device of electronic device 600, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc. equipped on electronic device 600.

[0068] Furthermore, the memory 602 may include both internal storage units of the electronic device 600 and external storage devices. The memory 602 is used to store application software and various types of data installed on the electronic device 600.

[0069] In some embodiments, display 603 may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, or an OLED (Organic Light-Emitting Diode) touchscreen. Display 603 is used to display information from electronic device 600 and to display a visual user interface. Components 601-603 of electronic device 600 communicate with each other via a system bus.

[0070] In one embodiment, when processor 601 executes the natural language to structured query language optimization program stored in memory 602, the following steps can be implemented: Extract business entities and business terms based on the business background information of the data map and the received natural language questions; Based on semantic similarity and user access permissions, the entity table result set is obtained by querying the data map according to the business entities, and the term table result set is obtained by querying the hierarchical knowledge base according to the business terms. The entity table result set and the terminology table result set are merged and deduplicated to obtain the final table set; In the graph structure of the data map, a relationship retrieval is performed based on the final set of tables to obtain a subgraph of relationships containing related tables and their relationships; The relationship subgraph is serialized, and prompt words are generated based on key query information; the key query information includes the serialized relationship subgraph, the business background information, the terminology knowledge fragments obtained from the query, and the natural language question. Input the prompt words into the large language model to generate candidate structured query language statements, perform execution tests on the candidate structured query language statements, and output the final structured query language statement based on the test results.

[0071] It should be understood that when the processor 601 executes the optimization method program for converting natural language to structured query language in the memory 602, in addition to the functions mentioned above, it can also implement other functions, as can be found in the description of the corresponding method embodiments above.

[0072] Furthermore, this embodiment of the invention does not specifically limit the type of electronic device 600 mentioned. Electronic device 600 can be a mobile phone, tablet computer, personal digital assistant (PDA), wearable device, laptop computer, or other portable electronic device. Exemplary embodiments of portable electronic devices include, but are not limited to, portable electronic devices running iOS, Android, Microsoft, or other operating systems. The aforementioned portable electronic device can also be other portable electronic devices, such as a laptop computer with a touch-sensitive surface (e.g., a touch panel). It should also be understood that in some other embodiments of the invention, electronic device 600 may not be a portable electronic device, but rather a desktop computer with a touch-sensitive surface (e.g., a touch panel).

[0073] The above provides a detailed description of the optimization method, system, and device for converting natural language to structured query language provided by this invention. Specific examples have been used to illustrate the principles and implementation methods of this invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, those skilled in the art will recognize that there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.

Claims

1. An optimized method for converting natural language to structured query language, characterized in that, include: Extract business entities and business terms based on the business background information of the data map and the received natural language questions; Based on semantic similarity and user access permissions, the entity table result set is obtained by querying the data map according to the business entities, and the term table result set is obtained by querying the hierarchical knowledge base according to the business terms. The entity table result set and the terminology table result set are merged and deduplicated to obtain the final table set; In the graph structure of the data map, a relationship retrieval is performed based on the final set of tables to obtain a subgraph of relationships containing related tables and their relationships; The relationship subgraph is serialized, and prompt words are generated based on key query information; the key query information includes the serialized relationship subgraph, the business background information, the terminology knowledge fragments obtained from the query, and the natural language question. Input the prompt words into the large language model to generate candidate structured query language statements, perform execution tests on the candidate structured query language statements, and output the final structured query language statement based on the test results.

2. The optimization method for converting natural language to structured query language according to claim 1, characterized in that, The process of obtaining an entity table result set by querying the data map based on semantic similarity and user access permissions, and obtaining a terminology table result set by querying the hierarchical knowledge base based on the business terms, includes: The extracted business entities and business terms are converted into vector representations; The business entity vectors are matched with the entity label vectors of the tables in the data map based on similarity, and then filtered according to the user's access permissions to the data map to obtain the entity table result set. The business term vectors are matched with the vectors of atomic knowledge fragments in the hierarchical knowledge base based on similarity. The term knowledge fragments are then filtered according to the user's access permission level to the hierarchical knowledge base. Finally, the relevant tables are extracted from the term knowledge fragments to obtain the term table result set.

3. The optimization method for converting natural language to structured query language according to claim 1, characterized in that, The process of merging and deduplicating the entity table result set and the terminology table result set to obtain the final table set includes: Examine each table in the entity table result set and the terminology table result set to determine whether the current user has access permissions; Remove all tables without access permissions from the entity table result set and the terminology table result set. The tables without access permissions are those that do not have the access permissions. The entity table result set and the terminology table result set after removing the table with no permissions are combined into a list, and the table with duplicate table names in the list are removed to obtain the final table set.

4. The optimization method for converting natural language to structured query language according to claim 3, characterized in that, The data map is a graph structure containing database metadata. The nodes of the graph structure are tables, and the attributes of the nodes include entity labels and business background information. The edges of the graph structure represent the relationships between the tables, including foreign key relationships and logical relationships. The hierarchical knowledge base stores the atomic knowledge fragments, which are structured query language code fragments corresponding to the business terms.

5. The optimization method for converting natural language to structured query language according to claim 4, characterized in that, The step of retrieving relationships based on the final table set within the graph structure of the data map to obtain a subgraph of relationships containing related tables and their relationships includes: Using the tables in the final set of tables as target nodes, retrieve the minimum association path connecting all the target nodes in the graph structure of the data map; Extract the subgraph formed by all nodes and edges on the minimum associated path as the associated relationship subgraph.

6. The optimization method for converting natural language to structured query language according to claim 1, characterized in that, The serialization of the association subgraph includes: The node and edge information in the relationship subgraph are converted into a structured text format supported by the large language model.

7. The optimization method for converting natural language to structured query language according to claim 1, characterized in that, The process of generating prompts based on key query information includes: The structured prompts are generated according to a predetermined template based on the key query information; or, The structured prompts are generated according to a predetermined template, combining the key query information and the optimized query information.

8. The optimization method for converting natural language to structured query language according to claim 1, characterized in that, The step of executing the candidate structured query language statements and outputting the final structured query language statement based on the test results includes: Execute the test on the candidate structured query language statements and obtain the test results; If the test result is an execution exception, the exception information will be fed back to the large language model for correction; If the test result is normal, the returned sample data will be fed back to the large language model for verification. After the verification is passed, the candidate structured query language statement will be output as the final structured query language statement.

9. An optimized system for converting natural language to structured query language, characterized in that, include: The extraction module is used to respond to the received natural language question, and combine the business background information from the data map to perform semantic analysis and intent recognition on the natural language question in order to extract business entities and business terms. The query module is used to obtain an entity table result set by querying the data map based on the business entity and user access permissions, and to obtain a term table result set by querying the hierarchical knowledge base based on the business terminology. The merge and deduplication module is used to merge and deduplicat the entity table result set and the terminology table result set to obtain the final table set. The retrieval module is used to perform relationship retrieval in the graph structure of the data map based on the final set of tables, and obtain a subgraph of relationships containing related tables and their relationships; The generation module is used to serialize the relationship subgraph, and combine the serialized relationship subgraph, the business background information, the queried terminology knowledge fragments, and the natural language question into prompt words, which are then input into the large language model to generate a structured query language statement. The processing module is used to perform execution tests on the structured query language statement and output the final structured query language statement based on the test results.

10. An electronic device comprising a memory and a processor, The memory is used to store programs; The processor, coupled to the memory, is used to execute the program stored in the memory to implement the steps in the optimization method for converting natural language to structured query language as described in any one of claims 1 to 8.

Images