Database Join path discovery method based on knowledge graph and graph theory search

Abstract

The invention discloses a database Join path discovery method based on knowledge graph and graph theory search. The method comprises two stages of offline construction of a rich knowledge graph and online discovery of an optimal path. In the atlas construction stage, tables and columns of a database are mapped into nodes, strong connection edges with weights are established through primary key-foreign key constraint, columns with similar semantics are identified by using a large language model, weak connection edges with weights are established, and statistical attributes such as cardinal numbers are added to the nodes. In the path discovery stage, candidate paths are evaluated by adopting a composite cost function, connection reliability and query performance are comprehensively considered, and an optimal path is searched through a Dijkstra or A * algorithm. According to the method, the fuzzy Join selection problem is converted into the deterministic graph theory problem, the illusion error of an end-to-end model is avoided, the implicit connection relation can be found, an interpretable path selection basis is provided, and the accuracy and performance of complex multi-table query are remarkably improved.

Description

Technical Field

[0001] This invention relates to the field of database query optimization technology, and in particular to a database join path discovery method based on knowledge graph and graph theory search. Background Technology

[0002] In today's data-driven era, enabling non-technical users to directly query databases using natural language has become a crucial research direction. Text-to-SQL technology, which automatically converts natural language queries into structured query language, was developed to address this need. The core challenge of this technology lies in understanding the user's query intent and accurately mapping it to the database table structure to generate syntactically correct and semantically accurate SQL queries. However, when queries involve multiple tables, determining the correct table joins becomes a critical bottleneck to the overall system accuracy. This is akin to building a bridge; we not only need to know the starting and ending points but also find the most stable and logical connection path.

[0003] Existing Text-to-SQL solutions suffer from three major flaws, significantly limiting their reliability in practical applications. First, there's the limited understanding of flat schemas. Traditional methods typically provide the database schema as a flat text or a simple list to the model, much like giving a student a textbook without a table of contents or chapter structure. The model struggles to effectively deduce the implicit, complex join relationships between tables, especially when explicit foreign key constraints are lacking. Second, there's the unreliability of end-to-end generation. While end-to-end generation methods based on large language models perform well on simple queries, the process of generating JOIN clauses is like a black box, lacking a definite logical reasoning process. This can lead to "illusion" phenomena, generating JOIN conditions that appear syntactically correct but are logically flawed, or omitting necessary intermediate join tables in complex queries. Third, there's the lack of ability to evaluate optimal paths. Even when multiple possible join paths exist between two tables, existing methods cannot assess which path is semantically most reasonable or potentially more performant. They often only find a seemingly feasible path, not the truly optimal one.

[0004] These technical shortcomings are particularly pronounced when handling complex queries involving three or more tables, where system accuracy drops dramatically. Imagine needing to extract information from an enterprise database containing dozens of interrelated tables; if the system fails to accurately identify the join relationships or chooses inefficient join paths, it will not only produce incorrect query results but may also cause a severe degrade in database performance. Therefore, there is an urgent need in the art for an innovative, non-end-to-end, interpretable method capable of systematically discovering and evaluating multiple JOIN paths and selecting the optimal one—this is precisely the core problem that this invention aims to solve. Summary of the Invention

[0005] The purpose of this invention is to provide a database join path discovery method based on knowledge graph and graph theory search. By transforming the fuzzy join path selection problem into a deterministic graph theory problem of solving the weighted shortest path on the knowledge graph, the accuracy and reliability problems of existing Text-to-SQL technology in multi-table queries are fundamentally solved.

[0006] To achieve the above objectives, this invention provides a database join path discovery method based on knowledge graphs and graph theory search, comprising the following steps: Construct a rich knowledge graph, mapping each table and column in the database to an entity node in the knowledge graph; establish has_column edges between table nodes and their contained column nodes; establish fk_joinable edges between corresponding table nodes based on primary key-foreign key constraints in the database, and assign weight values. The column-level semantic similarity between tables without foreign key constraints is calculated using a large language model. When the similarity exceeds a preset threshold, a semantically joinable edge is established between the corresponding table nodes, and a weight value is assigned. ,in Greater than ;Add cardinality attribute to each column node through data sampling analysis; After receiving the target table set T_target, locate the graph node corresponding to each table in the target table set in the rich knowledge graph; For candidate paths between target tables, the total cost of the path is calculated using a composite cost function: in, Let k be the weight of the k-th edge in the path. The penalty term is based on the cardinality of the joined columns, and w1 and w2 are preset weight parameters; The graph search algorithm is used to find the path with the lowest total cost as the optimal join path. The optimal path is then converted into a structured output containing an ordered list of tables and join conditions.

[0007] Furthermore, the steps for calculating column-level semantic similarity using a large language model include: extracting the name, annotation information, data type, and sample data of each column and organizing them into a structured text description; inputting the structured text description into the large language model for semantic similarity evaluation to obtain a similarity score; and establishing a semantic_joinable edge between corresponding table nodes when the similarity score exceeds 0.8.

[0008] Furthermore, The value of is 1. The value is 5; the cardinality attribute includes the number of unique values, data distribution characteristics, and null value rate.

[0009] Furthermore, the graph search algorithm is either Dijkstra's algorithm or A* algorithm; during the search process, a priority queue is maintained, candidate paths are sorted according to cumulative cost, all adjacent nodes are checked for each expanded node, the cost of reaching through different edges is calculated and the shortest path information is updated, and the search terminates when all target tables are included in the path set.

[0010] Furthermore, It can be a logarithmic or linear function; when the joined columns are high cardinality columns, a lower penalty value is assigned; when the joined columns are low cardinality columns, a higher penalty value is assigned.

[0011] Furthermore, the structured output is in JSON format, containing a tables field that stores an ordered list of tables that need to be joined, and a joins field that stores the connection information between each pair of tables, including the column names of the left table, the column names of the right table, the join type, and the weight value.

[0012] Furthermore, when there are multiple candidate paths with similar costs, the top K optimal paths are retained; where K is a preset positive integer.

[0013] Furthermore, the location of graph nodes in the rich knowledge graph is achieved through hash indexing, with a complexity of O(1); the construction of the rich knowledge graph is performed offline, while the discovery of the optimal join path is performed online.

[0014] The technical solution employed in the aforementioned invention comprises two core stages. Like building an intelligent navigation system, it first requires drawing a detailed map and then finding the optimal path on that map. In the offline knowledge graph construction stage, this invention innovatively proposes the concept of a rich knowledge graph. This is not a simple database structure graph, but a comprehensive knowledge system integrating structural information, semantic information, and data statistical information. Specifically, the system maps each table and column in the database to an entity node in the graph, connecting tables and their contained columns through `has_column` edges, and representing strong connections based on primary key-foreign key constraints through `fk_joinable` edges, assigning them low weight values ​​such as... =1 indicates that this is the most reliable join method. A more significant innovation lies in the system's use of the semantic understanding capabilities of a large language model to identify join relationships between tables that are semantically related but lack explicit foreign key constraints, establishing semantically joinable edges and assigning them high weight values, such as... =5 indicates that the reliability of this connection is relatively low but still effective. In addition, the system adds rich data profile attributes to each column node, such as cardinality, number of unique values, and other statistical information, providing data support for subsequent path optimization.

[0015] In the online path discovery phase, this invention cleverly formalizes the problem of finding the optimal join path as the shortest path problem in graph theory. When a query request that needs to join multiple tables is received, the system first quickly locates the node corresponding to the target table in the rich knowledge graph, and then applies a carefully designed composite cost function to evaluate each candidate path. This cost function... Not only does it consider the reliability of connections (reflected by EdgeWeight), but it also innovatively introduces performance evaluation based on data features, reflected by the Cardinality function, enabling path selection to balance semantic correctness and query efficiency. The system employs mature graph search algorithms such as Dijkstra's or A* to find the path with the lowest total cost, and finally converts the found optimal path into a structured output format, explicitly specifying the join order of tables and the specific join conditions.

[0016] The beneficial effects of this invention are: The beneficial effects of this invention are reflected in several aspects, bringing a qualitative leap to Text-to-SQL technology. First, by performing deterministic graph search on a rich knowledge graph, this method systematically avoids erroneous joins caused by the "illusion" of traditional end-to-end models, especially in complex queries requiring multiple intermediate tables for joining, significantly improving accuracy. Second, this invention provides complete interpretability and reliability; the output join path is no longer a black box result. Each path selection has clear cost calculations and comparison criteria, allowing developers to clearly understand why the system chooses a particular join path. Third, by introducing semantic join edges, this method can discover "implicit" join paths in the database that lack explicit foreign key constraints but should logically be connectable. This is particularly important for handling legacy systems or poorly designed databases, greatly expanding the system's applicability. Finally, the design of the composite cost function lays the foundation for future integration of more complex query optimizers, ensuring that the generated queries are not only logically correct but also perform better in execution. This is like providing a smart GPS navigation system for database queries, not only finding the path to the destination but also selecting the fastest and most optimal one. Attached Figure Description

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

[0018] Figure 1 This is a flowchart illustrating the overall process of the method of the present invention, showing the two core stages of the invention; Figure 2 This is a schematic diagram illustrating the construction of the Rich Knowledge Graph (Rich-KG) of the present invention, showing the structure of the Rich Knowledge Graph; Figure 3 This is a schematic diagram of the optimal join path search process of the present invention. Through a specific search example, it shows how to find the optimal path connecting target tables A, B, and C in the graph. Figure 4 This is a schematic diagram illustrating the calculation of the composite cost function of the present invention, explaining the method for calculating path cost; Figure 5 This is a comparison diagram of the effects of the conventional method and the method of the present invention. Detailed Implementation

[0019] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. It should also be noted that, to make the embodiments more comprehensive, the following embodiments are the best and preferred embodiments, and those skilled in the art can use other alternative methods to implement some well-known technologies; moreover, the accompanying drawings are only for more specific description of the embodiments and are not intended to specifically limit the present invention.

[0020] It should be noted that the use of terms such as "an embodiment," "an embodiment," "an exemplary embodiment," and "some embodiments" in the specification indicates that the described embodiment may include a specific feature, structure, or characteristic, but not every embodiment necessarily includes that specific feature, structure, or characteristic. Furthermore, when a specific feature, structure, or characteristic is described in connection with an embodiment, implementing such a feature, structure, or characteristic in conjunction with other embodiments (whether explicitly described or not) should be within the knowledge of those skilled in the art.

[0021] Generally, terms can be understood at least partly from their use in context. For example, depending at least partly on the context, the term "one or more" as used herein can be used to describe any feature, structure, or characteristic in a singular sense, or a combination of features, structures, or characteristics in a plural sense. Additionally, the term "based on" can be understood not necessarily to convey an exclusive set of factors, but rather, alternatively, depending at least partly on the context, to allow for the presence of other factors that are not necessarily explicitly described.

[0022] See Figures 1 to 5 As shown This invention provides a database join path discovery method based on knowledge graphs and graph theory search. This method constructs a rich knowledge graph and performs graph theory search on it, achieving intelligent discovery and optimization of join paths in multi-table database queries. The following detailed implementation steps illustrate how the technical solution of this invention is implemented.

[0023] The process of constructing a rich knowledge graph: In the first implementation phase of this invention, the system needs to construct a rich knowledge graph (Rich-KG) offline, which includes structural information, semantic information, and data statistics. This construction process specifically includes the following steps: First, the system scans the complete schema information of the target database, mapping each table and column in the database to entity nodes in the knowledge graph, forming the basic node set of the graph. For example, for an e-commerce database containing tables named users, orders, and products, the system will create corresponding table nodes {users_node, orders_node, products_node}, as well as column nodes for each table, such as {users.id_node, users.name_node, orders.user_id_node, orders.product_id_node}, etc.

[0024] Next, the system establishes relationship edges between nodes. For constructing structural relationship edges, the system first creates `has_column` edges, connecting each table node to all its contained column nodes. These edges have no weight or zero weight, simply representing a containment relationship. Then, by analyzing the database's metadata, the system identifies all defined primary key-foreign key constraints and creates `fk_joinable` edges for table pairs with FK constraints. These strongly joined edges are assigned low base weight values. For example, setting it to 1 indicates that this is the most reliable connection method.

[0025] For constructing semantic relationship edges, the system employs a large language model-assisted approach for in-depth analysis. Specifically, the system performs column-level semantic similarity calculations for each pair of tables without direct FK constraints. This process includes extracting the name, annotation information, data type, and partial sample data of each column, organizing this information into a structured text description, and then inputting it into the large language model for semantic similarity evaluation. When two columns from different tables are semantically highly similar (e.g., the user_name column in the users table and the operator_name column in the logs table, although the column names are different, they both represent user names), if the similarity score exceeds a preset threshold (e.g., 0.8), the system establishes a semantically joinable edge between the corresponding table nodes and assigns it a higher weight value. For example, setting it to 5 indicates that the reliability of this connection is lower than that of a strong connection based on FK.

[0026] After constructing the edges, the system needs to attach rich attribute information to each node. For column nodes, the system samples and analyzes actual data to calculate and store statistical attributes including cardinality, number of unique values, data distribution characteristics, and null value rate. These attributes will play a crucial role in subsequent path evaluation, helping the system determine the query performance differences between different join paths.

[0027] Online discovery mechanism for optimal join path: When the system receives a query request that needs to join multiple tables, the online path discovery phase begins. Assuming the target table set T_target={T_A,T_B,T_C} to be joined is parsed from the natural language query, the system will perform the following detailed steps to discover the optimal join path.

[0028] First, node localization is performed. The system quickly locates the graph node corresponding to each table in the target table set within the pre-built Rich-KG. This process achieves O(1) lookup complexity through hash indexing, ensuring fast response even in large databases containing thousands of tables. After localization, the system obtains the set of graph nodes that need to be connected.

[0029] The next step is the core graph theory search and path evaluation process. The system models the problem of finding all target tables as a graph theory problem of solving for the minimum spanning tree or the shortest path between multiple points on a Rich-KG. For any two candidate paths Path between target tables T_i and T_j, the system uses a composite cost function to calculate its total cost: ,in It is the edge The basic weights reflect the reliability of the connection; This is a penalty term based on the cardinality of the joined columns, used to assess the potential impact on query performance. For example, when joining involves high-cardinality columns, such as user IDs, a lower penalty value may be obtained due to their higher selectivity; while joining low-cardinality columns, such as gender fields, may produce a large Cartesian product, thus incurring a higher penalty value. Weighting parameters and It can be optimized according to the actual application scenario to balance the needs of semantic correctness and query performance.

[0030] The system employs either an improved Dijkstra's algorithm or the A* algorithm for path search. During the search process, the algorithm maintains a priority queue, sorting candidate paths according to their cumulative cost. For each expanded node, the algorithm checks all possible adjacent nodes, calculates the cost of reaching it via different edges, and updates the shortest path information. The search process terminates when all target tables are included in the set of found paths. If multiple paths with similar costs exist, the system retains the top K optimal paths for subsequent selection.

[0031] Finally, the system converts the found optimal path into a structured output format. This output contains an ordered list of tables that need to be joined, along with the specific join conditions between each pair of tables, including the column names used and the join type. The output can be in JSON format, for example: {"tables":["users","orders","products"],"joins":[{"left":"users.id","right":"orders.user_id","type":"fk","weight":1},{"left":"orders.product_id","right":"products.id","type":"fk","weight":1}]}. This structured representation can be directly used by downstream SQL generators.

[0032] Example 1: Multi-table query scenario in e-commerce database Consider a typical e-commerce database scenario with the following table structure: `users` table (containing columns such as id, name, email, and created_at), `orders` table (containing columns such as id, user_id, product_id, order_date, and total_amount), `products` table (containing columns such as id, name, category, and price), and `reviews` table (containing columns such as id, reviewer_name, product_name, rating, and content). The `orders` table is linked to the `users` table via a foreign key `user_id`, and to the `products` table via a foreign key `product_id`. However, the `reviews` table has no defined foreign key constraints and only has potential semantic relationships with other tables through the `reviewer_name` and `product_name` fields.

[0033] When the system receives the query "Find all user information who purchased a specific product and left a review", it needs to join four tables: users, orders, products, and reviews. In the offline phase, the system has already constructed a rich knowledge graph containing these tables. In this graph, there is a weighted fk_joinable edge (based on the user_id foreign key) with a weight of 1 between users and orders, and another weighted fk_joinable edge (based on the product_id foreign key) with a weight of 1 between orders and products. Simultaneously, through semantic analysis using a large language model, the system found that reviews.reviewer_name and users.name have a semantic similarity of 0.85, and reviews.product_name and products.name have a semantic similarity of 0.90. Therefore, corresponding semantic_joinable edges were established, each with a weight of 5.

[0034] During the online path discovery phase, the system first locates the nodes of the four target tables in the graph. Then, it runs a graph search algorithm, identifying two main join paths: Path 1 is users→orders→products, which semantically joins with products.name and reviews.product_name, with a total cost of 1+1+5=7; Path 2 is users→orders→products, which semantically joins with users.name and reviews.reviewer_name, then verifies the product information, also with a total cost of 1+1+5=7. Considering the cardinality of the columns, products.name typically has a lower cardinality than users.name (due to a limited number of product types but a large number of users). Therefore, Path 1 achieves a lower total cost after applying the cardinality penalty function. The system ultimately selects Path 1 as the optimal join path and generates the corresponding structured output.

[0035] Example 2: Legacy system database lacking foreign key constraints In another embodiment, consider a legacy enterprise resource management system database where, due to historical reasons, no foreign key constraints are defined between the tables. The database contains tables such as `employee` (columns for `emp_id`, `emp_name`, `dept_code`, etc.), `department` (columns for `dept_id`, `dept_name`, `manager_emp_id`, etc.), `salary` (columns for `employee_number`, `base_salary`, `bonus`, etc.), and `attendance` (columns for `staff_id`, `check_in_time`, `check_out_time`, etc.). Although these tables have clear logical relationships, the lack of foreign key constraints makes it difficult for traditional methods to automatically discover the correct join path.

[0036] During the rich knowledge graph construction phase, due to the lack of available Freehand constraints, the system relied entirely on semantic analysis to discover relationships between tables. Through large language model analysis, the system identified the following: employee.emp_id and salary.employee_number are highly similar in semantics and data pattern (both are employee IDs), with a similarity of 0.92; employee.emp_id and attendance.staff_id also represent the same concept, with a similarity of 0.88; although employee.dept_code and department.dept_id have slightly different names, analysis of data samples revealed that their value ranges completely match, with a similarity of 0.95; department.manager_emp_id and employee.emp_id represent the employee IDs of managers, with a similarity of 0.90. Based on these semantic similarities, the system established corresponding semantically joinable edges.

[0037] When querying "salary and attendance information for all employees in a department," the system needs to join all four tables. Through graph search, the system found the optimal path: `department` joins `dept_id` with `employee.dept_code` (weight 5), `employee` joins `emp_id` with `salary.employee_number` (weight 5), and `employee` joins `emp_id` with `attendance.staff_id` (weight 5), with a total base cost of 15. Although the cost is higher than the case with FK constraints, the system still successfully found a semantically correct join path. By further analyzing the cardinality of each column (`emp_id` has a high cardinality, `dept_code` has a low cardinality), the system applied a composite cost function for optimization, ensuring that the generated query is logically correct while also having good execution performance.

[0038] These two examples demonstrate that the method of the present invention can not only handle standard databases with complete FK constraints, but also effectively handle legacy systems lacking constraint definitions through semantic analysis, exhibiting good versatility and practicality. This method transforms the fuzzy join path selection problem into a deterministic graph theory problem, and combines semantic understanding and performance optimization to provide Text-to-SQL systems with reliable, interpretable, and efficient join path discovery capabilities.

[0039] This invention encompasses any substitutions, modifications, equivalent methods, and solutions made within the spirit and scope of this invention. To provide the public with a thorough understanding of this invention, specific details are described in detail in the following preferred embodiments; however, those skilled in the art will fully understand the invention even without these details. Furthermore, to avoid unnecessary misunderstanding of the essence of this invention, well-known methods, processes, procedures, components, and circuits are not described in detail.

[0040] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A database Join path discovery method based on knowledge graph and graph theory search, characterized in that, The method comprises the following steps: Constructing a knowledge graph, mapping each table and each column in the database to an entity node in the knowledge graph; establishing a has_column edge between the table node and the column node contained therein; based on the primary key-foreign key constraint in the database, establishing a fk_joinable edge between the corresponding table nodes, and assigning a weight value ; The column-level semantic similarity between tables without foreign key constraints is calculated by a large language model, and when the similarity exceeds a preset threshold, a semantic_joinable edge is established between the corresponding table nodes, and a weight value is assigned wherein greater than ; a cardinality attribute is attached to each column node through data sampling analysis; After receiving the target table set T_target, locating the graph nodes corresponding to each table in the target table set in the rich knowledge graph; For each candidate path Path between the target tables, calculating the total cost of the path using a composite cost function: wherein, is the weight of the kth edge in the path, is a penalty term based on the join column cardinality, and w1 and w2 are preset weight parameters; Using a graph search algorithm to find the path with the lowest total cost as the optimal join path, and converting the optimal path into a structured output containing an ordered list of tables and join conditions.

2. The method of claim 1, wherein, The step of calculating the column-level semantic similarity through the large language model comprises: extracting the name, annotation information, data type and sample data of each column, and organizing them into a structured text description; inputting the structured text description into the large language model for semantic similarity evaluation to obtain a similarity score; and when the similarity score exceeds 0.8, establishing a semantic_joinable edge between the corresponding table nodes.

3. The method of claim 1, wherein, The is 1, the is 5; the base attribute includes unique value quantity, data distribution characteristics and null rate.

4. The method of claim 1, wherein, The graph search algorithm is Dijkstra's algorithm or A* algorithm; a priority queue is maintained during the search process, candidate paths are sorted according to the cumulative cost, all adjacent nodes of each expanded node are checked, the cost of reaching through different edges is calculated and the shortest path information is updated, and the search is terminated when all target tables are included in the path set.

5. The method according to claim 1 or 4, characterized in that, The is a logarithmic function or a linear function; a lower penalty value is given when the join column is a high cardinality column; a higher penalty value is given when the join column is a low cardinality column.

6. The method of claim 1, wherein, The structured output is in JSON format, containing a tables field storing an ordered list of tables that need to be joined, and a joins field storing the connection information between each pair of tables, wherein the connection information includes left table column name, right table column name, connection type and weight value.

7. The method of claim 1, wherein, When there are multiple candidate paths with similar costs, the top K optimal paths are retained; wherein K is a predetermined positive integer.

8. The method according to any one of claims 1 to 7, characterized in that, The locating of the graph nodes in the rich knowledge graph is achieved through hash indexing, with a complexity of O(1); the construction of the rich knowledge graph is performed offline, and the discovery of the optimal join path is performed online.

Images