A method for NL2SQL based on foreign key completion and knowledge graph

Abstract

This disclosure belongs to the field of database query and natural language processing technology. It provides an NL2SQL method based on foreign key completion and knowledge graph. The method includes: extracting candidate foreign key relationships from database metadata and data content, and pruning and completing the candidate foreign key relationships through a three-layer funnel verification mechanism to construct a schema knowledge graph; determining a set of seed tables directly related to the query intent based on natural language queries; calculating the connection paths between seed table nodes based on the schema knowledge graph, completing intermediate tables on the paths, and expanding the seed tables into a set of subgraphs containing complete connection relationships; generating multiple candidate SQL statements based on the subgraph set; executing the multiple candidate SQL statements, and selecting the final SQL statement based on the consistency of the execution results.

Description

Technical Field

[0001] This disclosure belongs to the field of database query and natural language processing technology, and in particular relates to an NL2SQL method based on foreign key completion and knowledge graph. Background Technology

[0002] In the current wave of digital transformation, massive amounts of business data are being stored in increasingly complex database systems. NL2SQL (Natural Language to SQL) technology automatically converts natural language into executable SQL, aiming to lower the barrier to entry for database use. Current mainstream solutions rely on the "Schema Linking" approach of Large Language Models (LLM): concatenating the database schema with the user's question to form a Prompt, which is then directly used by the LLM to generate SQL. This approach is highly effective on small, well-structured databases and has become the foundation of many data analysis tools. However, when applied to real-world, large-scale, and non-standardized enterprise databases, this approach suffers a significant decline in both efficiency and accuracy, becoming a key bottleneck for technology implementation. Its specific technical shortcomings include:

[0003] 1. Passive schema understanding leads to inference bottlenecks. When explicit foreign keys are missing, LLMs struggle to infer implicit business relationships, and multi-table JOIN queries are prone to illusions.

[0004] 2. The dual contradiction of context length and noise. The full schema is too long and contains a large number of irrelevant tables, resulting in high token costs, distraction, and a decrease in accuracy instead of an increase.

[0005] 3. Poor cross-domain generalization and high customization costs. Each database change requires redesigning or fine-tuning the Prompt, making it impossible to use plug and play and hindering large-scale deployment. Summary of the Invention

[0006] This disclosure provides an NL2SQL method based on foreign key completion and knowledge graph, which can effectively solve the above problems.

[0007] This disclosure is implemented as follows:

[0008] This disclosure provides an NL2SQL method based on foreign key completion and knowledge graph, the method comprising:

[0009] Candidate foreign key relationships are extracted from database metadata and data content, and the candidate foreign key relationships are pruned and completed through a three-layer funnel verification mechanism to construct a schema knowledge graph;

[0010] Based on natural language queries, determine the set of seed tables that are directly related to the query intent;

[0011] Based on the Schema knowledge graph, the connection paths between seed table nodes are calculated, intermediate tables on the paths are completed, and the seed tables are expanded into a set of sub-graphs containing complete connection relationships.

[0012] Based on the aforementioned set of sub-charts, multiple candidate SQL statements are generated;

[0013] Execute the multiple candidate SQL statements, and select the final SQL statement based on the consistency of the execution results.

[0014] Compared with the prior art, the beneficial effects of this disclosure are:

[0015] This disclosure provides an NL2SQL method based on foreign key completion and knowledge graphs. It proactively extracts implicit foreign key relationships from database metadata and data content and accurately completes them through a three-layer funnel verification mechanism, constructing a high-precision schema knowledge graph. This effectively overcomes the reliance on missing foreign keys in existing methods, improving the accuracy of multi-table join queries and the ability to identify implicit business logic. It accurately recalls intent-related seed tables based on natural language queries, avoiding noise and redundancy from full schema input, significantly reducing context complexity, and improving query efficiency and cross-domain generalization. It automatically calculates join paths and completes intermediate bridging tables using the knowledge graph, forming a complete subgraph, solving the path omission problem in complex JOIN scenarios and making SQL more reliable and executable in non-normalized databases. The refined subgraph is input into a large language model to generate multiple candidate SQL statements, reducing random errors in single generation. Finally, the candidate statements are executed, and a closed-loop verification and optimization mechanism is formed through result consistency voting, significantly improving the accuracy, executability, and system robustness of the final SQL. It is particularly suitable for large-scale, structurally complex real-world query scenarios at the enterprise level. Attached Figure Description

[0016] Figure 1 This is a flowchart of the complexity-priority dual-driven intelligence processing task scheduling method S100 provided in the embodiments of this disclosure.

[0017] Figure 2 This is a flowchart illustrating the construction process of the database schema knowledge graph provided in this embodiment.

[0018] Figure 3 This is a flowchart of SQL generation and reasoning based on dynamic subgraph retrieval provided in an embodiment of this disclosure. Detailed Implementation

[0019] The present disclosure will now be described in further detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the disclosure. Furthermore, it should be noted that, for ease of description, only the parts relevant to the present disclosure are shown in the accompanying drawings.

[0020] It should be noted that, where there is no conflict, the embodiments and features described in this disclosure can be combined with each other. The technical solutions of this disclosure will now be described in detail with reference to the accompanying drawings and embodiments.

[0021] Unless otherwise stated, the exemplary implementations / embodiments shown are to be understood as providing exemplary features of various details that provide ways in which the technical concepts of this disclosure can be implemented in practice. Therefore, unless otherwise stated, the features of various implementations / embodiments may be additionally combined, separated, interchanged and / or rearranged without departing from the technical concepts of this disclosure.

[0022] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of this disclosure. The singular forms “a,” “the,” and “the” as used in embodiments of this disclosure are also intended to include the plural forms unless the context clearly indicates otherwise.

[0023] It should be understood that the term "and / or" used in this article is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. Additionally, the character " / " in this article generally indicates that the preceding and following related objects have an "or" relationship.

[0024] Depending on the context, the word "if" as used here can be interpreted as "when," "when," "in response to determination," or "in response to detection." Similarly, depending on the context, the phrase "if determination" or "if detection (of the stated condition or event)" can be interpreted as "when determination," "in response to determination," "when detection (of the stated condition or event)," or "in response to detection (of the stated condition or event)."

[0025] The terms "first" and "second" used herein are merely to distinguish similar objects and do not represent a specific ordering of the objects. Understandably, the specific order or sequence of "first" and "second" can be interchanged where permitted. It should be understood that the objects distinguished by "first" and "second" can be interchanged where appropriate so that the embodiments described herein can be implemented in orders other than those illustrated or described herein.

[0026] Example 1

[0027] Please refer to Figure 1 This disclosure provides an NL2SQL method S100 based on foreign key completion and knowledge graph.

[0028] Specifically, method S100 includes:

[0029] S102, Extract candidate foreign key relationships from database metadata and data content, and prune and complete the candidate foreign key relationships through a three-layer funnel verification mechanism to construct a schema knowledge graph;

[0030] S104, Based on natural language queries, determine the set of seed tables directly related to the query intent;

[0031] S106. Based on the Schema knowledge graph, calculate the connection paths between seed table nodes, complete the intermediate tables on the paths, and expand the seed table into a set of sub-graphs containing complete connection relationships.

[0032] S108, Based on the set of sub-charts, generate multiple candidate SQL statements;

[0033] S110, execute the multiple candidate SQL statements, and select the final SQL statement by voting based on the consistency of the execution results.

[0034] Method S100 is primarily designed to address the challenge of accurately querying data using natural language in complex, large-scale, and non-standardized enterprise database environments where non-SQL professionals struggle. Specifically, when faced with databases containing hundreds to thousands of tables that historically lack explicit foreign key constraints, this method ensures that implicit business logic and table relationships are automatically understood without requiring manual schema maintenance or model fine-tuning, transforming the user's natural language queries into precise, executable SQL statements.

[0035] In intelligent enterprise report generation, business personnel can use this method to directly query operational data such as sales and inventory through dialogue, without relying on the IT department to write complex code. In legacy system data mining, for old databases with missing documents and disorganized structures, the system can automatically infer inter-table relationships, helping managers quickly revitalize dormant assets. In cross-business line data platforms, different departments can plug and play into various new business databases without specific model training, achieving cross-domain universal query capabilities. This method, by integrating the features of knowledge graph proactive relationship inference, dynamic subgraph recall, and large language model generation optimization, provides a reliable technical solution for the above-mentioned scenarios where non-SQL professionals can efficiently query complex databases.

[0036] To address the issue of ambiguous relationships between tables due to missing foreign keys in enterprise databases, step S102 designed an automated graph construction process from metadata extraction to three-layer funnel verification.

[0037] Step S102 enables proactive and systematic discovery and high-precision completion of implicit / missing foreign key relationships commonly found in enterprise databases, significantly improving schema integrity and semantic accuracy. The constructed knowledge graph serves as a reliable structured foundation for all subsequent steps, avoiding the low accuracy problem of existing technologies that rely entirely on models to "guess" relationships between tables. It greatly enhances the ability of association reasoning in complex multi-table query scenarios and reduces JOIN errors and logical illusions in subsequent SQL generation.

[0038] A knowledge graph is a semantic network that describes entities and their relationships in the objective world using a graph structure. It transforms discrete data into a structured knowledge system through nodes and edges, demonstrating significant advantages in complex relationship reasoning and semantic disambiguation. In database semantic understanding, method S100 replaces the schema text description with entities (tables / columns) and relationships (foreign keys / associations) in the graph through step S102. Graph theory algorithms naturally represent multi-hop associations and implicit logic between data entities, thereby efficiently modeling complex business scenarios.

[0039] Traditional NL2SQL methods rely on the model passively receiving full schema information. In contrast, method S100, through step S102, actively analyzes data distribution and column name semantics to automatically infer and complete missing foreign key relationships, explicitly transforming implicit business logic into graph relationships. This demonstrates a high degree of structured cognitive ability and fault tolerance. Furthermore, while traditional NL2SQL methods require manual customization of prompts or fine-tuning of models for different domains, method S100, through step S102, transforms the database adaptation problem into a graph construction and dynamic retrieval problem, achieving general modeling capabilities for heterogeneous databases within a unified framework. This not only avoids the tedious process of retraining or writing static rules for new scenarios but also achieves true "plug-and-play" functionality through automated graph querying. During the cross-domain adaptation phase, no prior domain fine-tuning or carefully designed static examples are required, enabling rapid adaptation to different domains and significantly improving the system's flexibility and scalability.

[0040] In some implementations, candidate foreign key relationships are extracted from database metadata and data content, and these candidate foreign key relationships are pruned and completed using a three-layer funnel verification mechanism to construct a schema knowledge graph, including:

[0041] The database metadata is read through the data access module to obtain the database schema information;

[0042] An external dependency discovery tool was introduced to perform inclusion dependency analysis, scanning the entire data content to identify cross-table references between columns.

[0043] Generate a set of candidate foreign key relationships and transform the schema information into a graph structure.

[0044] Specifically, the cross-table reference relationship refers to the inclusion dependency where the set of column values ​​of one table is a subset of the set of column values ​​of another table.

[0045] By introducing a data access module to read database metadata and quickly obtain database schema information, combined with inclusion dependency analysis using external dependency discovery tools, the system can comprehensively scan all data content and identify cross-table references between columns. This process not only significantly improves the probability of discovering implicit foreign key relationships but also enhances the system's understanding of complex database structures, enabling it to more accurately capture potential inter-table relationships. Furthermore, the modular design of the data access module improves data reading efficiency and enhances the system's scalability, allowing it to flexibly adapt to different types of database systems and reducing access barriers caused by differences in database types. In this way, the system achieves offline, automated, and data-driven foreign key mining of all data content, significantly improving efficiency and accuracy when handling complex database structures, thereby enhancing the overall performance and applicability of the system.

[0046] In some implementations, the three-layer funnel verification mechanism includes:

[0047] Physical layer: Verify whether the uniqueness index of the candidate foreign key relationship reaches a preset threshold by performing uniqueness statistics operations;

[0048] Lexical rules layer: performs word segmentation and heuristic semantic matching checks on candidate foreign key column names that pass through the physical layer;

[0049] Semantic layer: The candidate foreign key relationships, which are still ambiguous in the first two layers, are input into the large language model. Semantic arbitration is performed based on column names, table comments, and sample data to determine the final retained foreign key relationships.

[0050] The initial graph contains numerous noisy associations based on statistical findings but lacking business significance. Method S100 employs a three-layer funnel verification mechanism through the Graph Evolution Manager, cleaning and pruning candidate edges through hierarchical filtering. At the physical layer, data uniqueness is verified by performing actual unique operations in the computation engine. If the physical statistical indicators fail to meet a preset threshold, the edge is deemed invalid. This process effectively filters out erroneous relationships that do not meet uniqueness requirements, ensuring the basic accuracy of foreign key relationships.

[0051] In theory, a strict referential integrity constraint requires that the referenced column must be unique, meaning its uniqueness ratio should always be equal to 1.0. However, in real-world industrial data environments, due to factors such as incomplete historical data cleaning, ETL process anomalies, or soft constraints in business logic, a small amount of data noise or outliers are common. Using an absolute threshold of 1.0 can easily lead to the algorithm missing genuine foreign key relationships (false negatives). Conversely, if the threshold is too low, it will introduce spurious foreign keys / false positives with similar statistical characteristics but no actual business relationship. Based on a comprehensive consideration of recall and precision, this disclosure proposes a fault-tolerant judgment mechanism that presets the uniqueness threshold to [0.97, 0.99]. This range, as an empirical parameter, can effectively filter out data noise interference, and while ensuring the accuracy of foreign key identification, it significantly improves the robustness of the algorithm to non-standardized data.

[0052] At the lexical rule layer, edges that pass physical verification undergo name compliance checks. Using word segmentation tools and heuristic rules, the semantics of column names are assessed for match (e.g., `user_id` is valid when associated with `uid`, but invalid when associated with `create_time`), further eliminating candidate relationships that do not conform to semantic logic and improving the semantic consistency of foreign key relationships. At the semantic layer, for ambiguous edges that cannot be clearly determined in the first two layers, LLM (Local Level Model) is introduced for semantic arbitration. The model determines whether the association conforms to common business logic based on column names, table comments, and sample data. This process not only solves the ambiguity problem of foreign key relationships in complex scenarios but also significantly improves the system's ability to understand implicit business logic. Finally, only edges that pass all verifications are retained and updated back to the knowledge graph, forming a high-precision database semantic network. This layered verification mechanism ensures high-quality foreign key relationships while reducing query errors and logical illusions caused by missing or incorrect foreign keys, providing a more reliable structured foundation for subsequent SQL generation and execution, and significantly improving the overall performance and applicability of the system. Furthermore, this mechanism constructs a high-precision, low-noise foreign key relationship network, which greatly reduces the risk of error propagation introduced by noisy edges, enabling the quality of the knowledge graph to meet the requirements of industrial applications.

[0053] Step S104 accurately determines the set of seed tables directly related to the query intent based on natural language queries, significantly improving the understanding of the query intent while reducing contextual noise caused by processing full schema information. This process not only reduces computational complexity but also provides a precise starting point for subsequent path expansion, reducing invalid calculations and erroneous expansions, which is particularly effective when dealing with large-scale, complex databases. This step does not rely on domain-specific predefined rules or fine-tuning but dynamically determines seed tables based on natural language understanding, thereby enhancing the system's cross-domain generalization ability and enabling it to better adapt to different domains and database types without requiring extensive manual configuration or adjustments for each new scenario. Overall, step S104 provides more accurate input for subsequent subgraph expansion, SQL generation, and execution, optimizing the entire NL2SQL processing flow and significantly improving the overall performance and applicability of the system.

[0054] In some implementations, a set of seed tables directly related to the query intent is determined based on natural language queries, including:

[0055] Based on natural language queries, a dual-path hybrid recall mechanism combining vector similarity and large-scale model semantic judgment is used to determine a set of seed tables directly related to the query intent.

[0056] Specifically, for natural language queries, the system employs a dual-path recall strategy to lock onto the seed tables:

[0057] Vector Recall: The similarity between the vector embedding of the natural language query and the vector representation of the table schema is calculated. In some implementations, the top-K related tables are selected as candidate seed tables for the vector recall path by calculating cosine similarity.

[0058] LLM Semantic Recall: Uses a large language model to determine the relevance between a table and a query, infers whether a single table may involve the query, and selects relevant tables as candidate seed tables for the semantic recall path.

[0059] Finally, the two recall results are merged and deduplicated to obtain the core entity node most directly related to the query intent.

[0060] By employing a dual-path hybrid recall mechanism, the accuracy and robustness of seed table set recall are significantly improved. The vector recall path ensures efficient initial matching based on statistical features; the LLM semantic recall path effectively compensates for the limitations of vector recall in complex semantic scenarios by deeply understanding the semantic connotation of query intent. The merging and deduplication of the two recall results not only achieves the complementary advantages of literal similarity and deep semantic understanding, but also significantly reduces the risk of misjudgment from a single recall path, thereby accurately identifying the core table set most directly related to the query intent and achieving a balance between high recall and high precision. This process significantly reduces noise interference from irrelevant tables, keeping the number of candidate tables within a reasonable range. This reduces the computational cost of subsequent processing and significantly improves the recall completeness and accuracy in multi-table query scenarios, laying a high-quality data foundation for subsequent subgraph expansion and SQL generation.

[0061] Since multi-table queries often involve intermediate tables (bridge tables) not explicitly mentioned in the query, the seed table alone cannot complete the joins. Step S106 utilizes the constructed knowledge graph to calculate the connection paths between seed table nodes based on foreign key edges. The system automatically completes the intermediate tables on the path, expanding the "seed table" into a "seed + path table set" containing complete join relationships. This step effectively utilizes the graph's topology, automatically discovering and completing the implicit intermediate tables (bridge tables) between seed table nodes through join path calculation, constructing a subgraph set containing complete JOIN relationships. This effectively solves the major defect of existing NL2SQL technology in its inability to automatically identify missing intermediate tables in complex multi-table queries. This not only ensures that the generated subgraph covers all necessary join relationships, significantly improving the logical correctness and executability of SQL in multi-table join scenarios, but also transforms the complex task of "guessing" dozens of hops of relationships from a model into a structured and controllable graph computation process, significantly enhancing the system's robustness and interpretability to non-normalized databases.

[0062] In some implementations, based on the schema knowledge graph, the connection paths between seed table nodes are calculated, intermediate tables along the paths are completed, and the seed tables are expanded into a set of sub-graphs containing complete connection relationships, including:

[0063] Calculate the shortest path between seed table nodes based on foreign key edges, and include all intermediate bridge tables involved in the shortest path into the subgraph set.

[0064] Employing a shortest path algorithm to calculate the connections between nodes in the seed table automatically locates the most efficient and direct JOIN link with the minimum number of hops. This avoids the exponential computational overhead of exhaustively searching all paths and ensures that the generated subgraph contains the most concise and complete intermediate bridge table. It transforms the complex multi-table JOIN path completion problem into an efficient graph theory shortest path solution, significantly reducing JOIN logic errors caused by excessively long paths or convoluted relationships. Furthermore, the deterministic calculation process of the shortest path is highly interpretable, facilitating debugging and verification, further improving the system's robustness and execution efficiency against non-normalized and highly complex databases.

[0065] Due to the massive table structures and missing foreign keys inherent in enterprise databases, full schema input exposes serious drawbacks in LLM inference. Full input transforms the structures of hundreds or thousands of tables into extremely long text sequences. Large model attention mechanisms struggle to focus on key information when processing such long texts, leading to illusions due to excessive "noise." In traditional Text-to-SQL processes, the context window not only limits the number of tables that can be processed, but its high token consumption also causes system costs to increase linearly or even superlinearly with the number of tables. For example, in an ERP system containing thousands of tables, schema description alone can consume hundreds of thousands of tokens, resulting in extremely high inference latency and the model often ignoring core table join paths due to irrelevant information. More importantly, existing NL2SQL methods, in the absence of explicit foreign keys, rely entirely on "guessing" from the model's internal knowledge. In complex multi-table JOIN scenarios, the accuracy of this guessing is extremely low.

[0066] The method S100 first constructs a complete database knowledge graph through active inference and verification. Then, based on the query intent, it extracts only subgraphs containing core entities and their multi-hop neighbors from the graph. This approach compresses the context of the input model from a "full schema" to a "precise subgraph." This dynamic subgraph recall mechanism achieves a fundamental breakthrough in processing efficiency and accuracy in step S108. Step S108, based on the precise subgraph set, significantly reduces contextual noise and illusion risks caused by irrelevant tables, greatly improving the accuracy and executability of SQL generation. By generating multiple candidate SQL statements in parallel, it provides diverse logical expression methods, laying the foundation for subsequent consistency voting and improving the overall fault tolerance and accuracy of the final output.

[0067] In practice, 5-8 candidate SQL statements are typically generated. This empirical value avoids both the random errors caused by too few candidates and the token overhead caused by too many candidates.

[0068] In some implementations, multiple candidate SQL statements are generated based on the sub-chart set, including:

[0069] Input the schema information corresponding to the sub-graph set into the large language model to generate multiple candidate SQL statements.

[0070] By generating multiple candidate SQL statements in parallel based on a significantly compressed, high signal-to-noise ratio subgraph schema input, the randomness and uncertainty of single-batch LLM generation are effectively suppressed, significantly improving generation quality and stability, and reducing illusions and errors caused by irrelevant table noise. This not only reduces token consumption by an order of magnitude and significantly shortens context length and inference latency, offering significant economic and real-time advantages, but also enhances the system's robustness to denormalized databases by replacing the traditional "single guess" with a "multiple-selection verification" paradigm, providing a stable and reliable generation paradigm for industrial-grade NL2SQL applications.

[0071] Step S110 establishes a closed-loop verification mechanism from generation to execution, effectively filtering out syntactically correct but semantically incorrect SQL statements, thus improving the actual executability and accuracy of the final output SQL. Through result set consistency voting, the self-consistency of multiple candidate SQL statements is fully utilized, significantly reducing the uncertainty and random error of a single model generation. This voting mechanism not only enhances the system's robustness to complex, non-normalized databases but also upgrades the decision-making basis for SQL generation from a single model output to empirical verification of execution results, giving the system higher credibility and stability in industrial applications.

[0072] In some implementations, the multiple candidate SQL statements are executed, and a final SQL statement is selected by voting based on the consistency of the execution results, including:

[0073] Execute the multiple candidate SQL statements, and group the candidate SQL statements according to the consistency of the execution results;

[0074] If there is a unique majority of execution results, the corresponding candidate SQL statement is output as the final result.

[0075] By introducing a majority consensus voting mechanism based on real execution results, the diversity of generation is effectively utilized to combat the uncertainty of the model, significantly improving the executability and reliability of the final SQL.

[0076] In some implementations, if no majority result is generated or it is determined that all the candidate SQL statements have failed to execute, a dynamic backtracking signal is triggered to expand the scope of the connection path search in the schema knowledge graph, and table retrieval and SQL generation are performed again for multiple iterations of optimization.

[0077] Specifically, if no winner is generated or the entire execution is deemed to have failed, the system triggers a dynamic backtracking signal of "need_more_tables", expands the shortest path search range of the graph (e.g., increases the number of hops), recalls more relevant tables and repeats the generation process until a unique majority execution result is generated or the preset iteration limit is reached.

[0078] By establishing an adaptive iterative optimization closed loop, the subgraph range can be automatically expanded and retried when a single round of generation is insufficient, which greatly improves the system's fault tolerance for difficult queries and the final success rate.

[0079] In some implementations, the multiple candidate SQL statements are executed, and a final SQL statement is selected by voting based on the consistency of the execution results, including:

[0080] Execute each of the candidate SQL statements; if execution fails, retrieve the error log.

[0081] Based on the error log, the candidate SQL statement is corrected using a large language model to generate a corrected SQL statement.

[0082] The candidate SQL statement is replaced with the corrected SQL statement and executed again, and the number of repair iterations is recorded;

[0083] Determine whether the number of repair iterations has reached a preset maximum repair threshold;

[0084] If the maximum number of repair attempts is not reached and the execution is successful, the execution result of the corrected SQL statement will be included in the consensus vote; if the maximum number of repair attempts is reached but the execution is still unsuccessful, the candidate SQL statement is determined to have failed to execute, and its execution result will not be included in the consensus vote.

[0085] Specifically, after generating multiple candidate SQL statements, each candidate SQL statement is executed in the database, and the database feedback is obtained. If the execution is successful, the execution result data is obtained; if the execution fails, the error information returned by the database is obtained as an execution error log. The system inputs the error log, the current candidate SQL statement, and the corresponding sub-graph set into the large language model to generate a corrected SQL statement. If a candidate SQL statement is determined to have failed to execute, it is removed from the candidate set.

[0086] The above mechanism, through intelligent judgment of execution feedback, improves the system's fault tolerance for difficult queries while achieving a balance between efficient resource utilization and user experience, significantly enhancing the overall system's practicality and user-friendliness.

[0087] This disclosure proposes an NL2SQL technology solution based on knowledge graph retrieval enhancement (Graph-RAG), whose core system architecture mainly consists of two subsystems:

[0088] 1. An automated system for constructing database schema knowledge graphs. For the specific processing flow of this subsystem, please refer to [reference needed]. Figure 2 ;

[0089] 2. SQL generation and inference system based on dynamic subgraph retrieval. The specific processing flow of this subsystem (corresponding to the Agent flow) can be found in [reference needed]. Figure 3 .

[0090] This technical solution is further refined into a dynamic processing framework of "construction-recall-generation-optimization," consisting of a graph construction layer, a dynamic recall layer, an SQL generation layer, and an iterative optimization layer. Based on the combination of knowledge graphs and large language models, this framework achieves a technological leap from "passively receiving the full schema" to "actively understanding and retrieving on demand": it constructs a high-precision knowledge graph by actively inferring foreign key relationships, dynamically recalls core subgraphs based on query intent, and combines a multi-candidate SQL generation and execution feedback voting mechanism. This significantly improves the accuracy and robustness of query transformation in complex enterprise-level database scenarios with low resource consumption, effectively solving the three core bottlenecks of existing technologies: difficulty in understanding missing foreign keys, high cost and severe noise interference from full input, and reliance on manual customization for cross-domain generalization.

[0091] It should be understood that various parts of this disclosure can be implemented in hardware, software, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented in software stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.

[0092] Those skilled in the art will understand that all or part of the steps of the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a readable storage medium, and when executed, the program includes one or a combination of the steps of the method embodiments.

[0093] Furthermore, the functional units in the various embodiments of this disclosure can be integrated into a single processing module, or each unit can exist physically separately, or two or more units can be integrated into a single module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a readable storage medium. The storage medium can be a read-only memory, a disk, or an optical disk, etc.

[0094] Those skilled in the art should understand that the above embodiments are merely for illustrating the present disclosure and are not intended to limit the scope of the disclosure. Those skilled in the art can make other changes or modifications based on the above disclosure, and these changes or modifications still fall within the scope of the present disclosure.

Claims

1. An NL2SQL method based on foreign key completion and knowledge graph, characterized in that, The method includes: Candidate foreign key relationships are extracted from database metadata and data content, and these relationships are pruned and completed using a three-layer funnel verification mechanism to construct a schema knowledge graph. The three-layer funnel verification mechanism includes: Physical layer: Verify whether the uniqueness index of the candidate foreign key relationship reaches a preset threshold by performing uniqueness statistics operations; Lexical rules layer: performs word segmentation and heuristic semantic matching checks on candidate foreign key column names that pass through the physical layer; Semantic layer: The candidate foreign key relationships, which are still ambiguous after passing through the first two layers, are input into the large language model. Semantic arbitration is performed based on column names, table comments, and sample data to determine the final retained foreign key relationships. Candidate foreign key relationships are extracted from database metadata and data content, and these relationships are pruned and completed using a three-layer funnel verification mechanism to construct a schema knowledge graph, including: The database metadata is read through the data access module to obtain the database schema information; An external dependency discovery tool was introduced to perform inclusion dependency analysis, scanning the entire data content to identify cross-table references between columns. Generate a set of candidate foreign key relationships and transform the schema information into a graph structure; Based on natural language queries, a seed table set directly related to the query intent is determined by a dual-path hybrid recall mechanism of vector similarity and large model semantic judgment. Based on the Schema knowledge graph, the connection paths between seed table nodes are calculated, intermediate tables on the paths are completed, and the seed tables are expanded into a set of sub-graphs containing complete connection relationships. Based on the aforementioned set of sub-charts, multiple candidate SQL statements are generated; Execute the multiple candidate SQL statements, and select the final SQL statement based on the consistency of the execution results.

2. The method according to claim 1, characterized in that, Based on the aforementioned schema knowledge graph, the connection paths between seed table nodes are calculated, intermediate tables along the paths are completed, and the seed tables are expanded into a set of sub-graphs containing complete connection relationships, including: Calculate the shortest path between seed table nodes based on foreign key edges, and include all intermediate bridge tables involved in the shortest path into the subgraph set.

3. The method according to claim 1, characterized in that, Based on the aforementioned set of sub-charts, multiple candidate SQL statements are generated, including: Input the schema information corresponding to the sub-graph set into the large language model to generate multiple candidate SQL statements.

4. The method according to claim 1, characterized in that, Execute the multiple candidate SQL statements, and select the final SQL statement based on the consistency of the execution results, including: Execute the multiple candidate SQL statements, and group the candidate SQL statements according to the consistency of the execution results; If there is a unique majority of execution results, the corresponding candidate SQL statement is output as the final result.

5. The method according to claim 4, characterized in that, If no majority result is generated or all candidate SQL statements are determined to have failed to execute, a dynamic backtracking signal is triggered to expand the scope of the connection path search in the schema knowledge graph, and table retrieval and SQL generation are performed again for multiple iterations of optimization.

6. The method according to claim 5, characterized in that, Execute the multiple candidate SQL statements, and select the final SQL statement based on the consistency of the execution results, including: Execute each of the candidate SQL statements; if execution fails, retrieve the error log. Based on the error log, the candidate SQL statement is corrected using a large language model to generate a corrected SQL statement. The candidate SQL statement is replaced with the corrected SQL statement and executed again, and the number of repair iterations is recorded; Determine whether the number of repair iterations has reached a preset maximum repair threshold; If the maximum number of repair attempts is not reached and the execution is successful, the execution result of the corrected SQL statement will be included in the consensus vote; if the maximum number of repair attempts is reached but the execution is still unsuccessful, the candidate SQL statement is determined to have failed to execute, and its execution result will not be included in the consensus vote.

Images