A query method based on a knowledge graph and a Text2SQL model
By constructing a knowledge graph and vector database technology for multi-dimensional metadata, and combining it with the Text2SQL model, the SQL generation process for multi-table queries was optimized, solving the problems of low accuracy and efficiency in existing technologies, and achieving higher SQL statement accuracy and query efficiency.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- LIANYI TECHNOLOGY CO LTD
- Filing Date
- 2024-12-11
- Publication Date
- 2026-06-09
Smart Images
Figure CN119848065B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database query optimization technology, and in particular to a query method based on knowledge graph and Text2SQL model. Background Technology
[0002] With the rapid development of natural language processing (NLP) technology, Text2SQL systems have become an important tool for converting NLP queries into SQL statements. However, when dealing with complex scenarios involving multi-table joins, traditional large-scale models often lack structural knowledge of specific databases (such as private university databases), making it difficult to accurately understand the relationships between tables, the meaning of fields, and their dependencies. This results in low accuracy and inefficient querying of the generated SQL statements. A knowledge graph is a semantic network that reveals the relationships between entities. It stores knowledge in the form of a graph, where nodes represent entities and edges represent relationships between entities. The architecture of a knowledge graph includes a schema layer and a data layer, providing a comprehensive description of the database's structure and metadata. Knowledge graph technology is often used in conjunction with Graph Retrieval Augmentation (Graph RAG) technology. Graph RAG leverages the structured characteristics of knowledge graphs to enhance the depth and contextual relevance of retrieved information, becoming a powerful complement to traditional vector search methods.
[0003] However, existing Rag models, when handling multi-table queries, primarily rely on language vectorization similarity for intent recognition and table extraction, neglecting the complex relationships between tables and between tables and fields, such as containment relationships, field comments, primary key and foreign key relationships. These relationships are crucial for accurate SQL generation, but traditional methods often fail to effectively utilize this information.
[0004] Therefore, how to more accurately generate SQL statements in scenarios involving multi-table queries using knowledge graphs and Text2SQL large models is an urgent problem to be solved. Summary of the Invention
[0005] This application provides a query method based on knowledge graphs and Text2SQL models, which can generate SQL statements more accurately in scenarios where multi-table queries are performed using large knowledge graph and Text2SQL models.
[0006] To achieve the above objectives, the present invention adopts the following technical solution:
[0007] In a first aspect, the present invention provides a query method based on a knowledge graph and a Text2SQL model, the method comprising:
[0008] Collect the metadata to be queried, extract the structural information of the metadata, and construct a knowledge graph based on the structural information;
[0009] A vector index is created based on the metadata, and the index of the metadata is scored based on weights. The processed metadata is then stored in a vector database.
[0010] Collect user query data, use the Text2SQL model to perform intent recognition on the metadata in the vector database that matches the context of the query data, extract keywords and analyze the user's query intent;
[0011] Based on the context of the user's query data and the identified intent, the relationship between keywords is analyzed using the knowledge graph to determine whether the keywords meet the conditions for SQL generation. If the conditions are not met, the user is interacted with through multiple rounds of question and answer, prompting the user to supplement the missing conditions or delete redundant information until the conditions for SQL generation are met.
[0012] The context of the query data, the parsing results of the knowledge graph, and the metadata information and relationships obtained through multiple rounds of question answering are integrated into the prompt. The Text2SQL model generates SQL statements based on the adjusted prompt.
[0013] The query operation is performed on the vector database based on the SQL statement to obtain the query results.
[0014] In a preferred example of this application, the collection of metadata to be queried may be further configured to include:
[0015] Collect the basic business metadata, view and storage information, ETL cleaning SQL information, indicator caliber algorithm information and interface link tracing SQL to be queried, and obtain the metadata.
[0016] In a preferred embodiment of this application, the construction of the knowledge graph based on the structural information may be further configured as follows:
[0017] Different business domains are defined based on business scenarios, and knowledge graphs are built based on these business domains.
[0018] In a preferred example of this application, it may further be configured to include:
[0019] When a user enters query data, the system matches the user's current input question with the metadata, and provides relevant suggestions based on the matching results.
[0020] In a preferred example of this application, it may further be configured to include:
[0021] When scoring the index of the metadata based on weight, the greater the weight, the higher the score of the table and field. Unscored tables and fields are all assigned a default base score.
[0022] When performing matching in the vector database, the minimum cosine distance between all vectors in the vector database and the query vector with the closest semantic meaning is calculated, and the vectors are sorted according to the score.
[0023] In a preferred example of this application, it may further be configured to include:
[0024] When using the knowledge graph to analyze the relationships between keywords and determine whether the keywords can meet the conditions for SQL generation, the relationship between the keywords is determined based on the table-to-table relationship, table-to-field relationship, field-to-field relationship, field comment, or field-to-primary key / foreign key relationship in the knowledge graph. If the determination is satisfied, the table and field information and link conditions in the keywords are added to the prompt.
[0025] Secondly, this application provides a query device based on a knowledge graph and a Text2SQL model, the device comprising:
[0026] The data acquisition module is used to collect metadata to be queried, extract the structural information of the metadata, and construct a knowledge graph based on the structural information;
[0027] The vector database module is used to create vector indexes based on the metadata, score the indexes of the metadata based on weights, and store the processed metadata into the vector database.
[0028] The context analysis module is used to collect user query data, use the Text2SQL model to perform intent recognition on the metadata in the vector database that matches the context of the query data, extract keywords and analyze the user's query intent.
[0029] The judgment module is used to analyze the relationship between keywords based on the context of the user's query data and the intent recognition, and to determine whether the keywords can meet the conditions for SQL generation. If the conditions are not met, the module interacts with the user through multiple rounds of question and answer, prompting the user to supplement the missing conditions or delete redundant information until the conditions for SQL generation are met.
[0030] The query module is used to integrate the context of the query data, the parsing results of the knowledge graph, and the metadata information and relationships obtained through multiple rounds of question answering into a prompt. The Text2SQL model generates an SQL statement based on the adjusted prompt. The query operation is performed on the vector database based on the SQL statement to obtain the query results.
[0031] Thirdly, this application provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the query method based on the knowledge graph and Text2SQL model as described in any of the preceding claims.
[0032] Fourthly, this application provides a computer-readable storage medium storing a program, wherein when the program is executed by a processor, it implements the query method based on knowledge graph and Text2SQL model as described in any of the preceding claims.
[0033] Fifthly, this application provides a computer program product, including computer instructions that, when executed by a processor, implement the steps of the query method based on the knowledge graph and Text2SQL model as described in any of the preceding claims.
[0034] In summary, compared with the prior art, the beneficial effects of the technical solution provided in this application include at least the following:
[0035] 1. This application constructs a knowledge graph containing multi-dimensional metadata information and integrates it into a system based on a Text2SQL large-scale model. It utilizes vector database technology for efficient retrieval of metadata tags, providing a foundation for intent recognition. Simultaneously, based on the user query context and the parsed content of intent recognition, keywords are extracted, and the relationships between keywords are pre-analyzed to determine whether they meet the SQL generation conditions. If the conditions are met, table and field information and link conditions from the keywords are added to the prompt; if not, multiple rounds of question-and-answer sessions are used to supplement the user intent. Finally, SQL generation is performed based on the finely tuned prompt, significantly improving the accuracy of SQL statements generated by the Text2SQL large-scale model when handling multi-table join queries. Experimental data shows that compared to the traditional RAG model, the Graph RAG model using this invention improves accuracy by 65% (from 20% to 85%), while also significantly increasing the speed of fulfilling requirements (from 1 minute 35 seconds to 17 seconds). This not only optimizes query efficiency but also enhances user experience, providing new ideas and methods for the development of natural language processing and database query optimization technologies.
[0036] 2. This application adds the metadata information and relationships supplemented by the query context and knowledge graph parsing context to the prompt, and provides it to the Text2SQL large model. This allows the Text2SQL large model to reduce or eliminate the need for association and directly output the results based on the known conditions, thereby improving the accuracy of the generated SQL statements. Attached Figure Description
[0037] Figure 1 This is a flowchart illustrating a query method based on a knowledge graph and Text2SQL model, provided as an embodiment of this application.
[0038] Figure 2 This is a structural diagram of a query device based on a knowledge graph and Text2SQL model, provided in one embodiment of this application. Detailed Implementation
[0039] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0040] In one embodiment of this application, a query method based on knowledge graphs and a Text2SQL model is provided. Please refer to [link / reference]. Figure 1 As shown, the method includes:
[0041] S100: Collect the metadata to be queried, extract the structural information of the metadata, and construct a knowledge graph based on the structural information.
[0042] Specifically, design automated scripts or tools to periodically extract metadata from the data warehouse generated by information standards through multi-dimensional metadata collection methods.
[0043] Furthermore, the collection of metadata to be queried includes:
[0044] Collect the basic business metadata, view and storage information, ETL cleaning SQL information, indicator caliber algorithm information and interface link tracing SQL to be queried, and obtain the metadata.
[0045] S200: Create a vector index based on the metadata, score the index of the metadata based on weights, and store the processed metadata in the vector database;
[0046] In practice, after collecting the metadata, a vector index is created, a series of tags are added to the main metadata index, and the data is scored according to weights and then stored in the vector database.
[0047] S300: Collect user query data, use the Text2SQL model to perform intent recognition on the metadata in the vector database that matches the context of the query data, extract keywords and analyze the user's query intent;
[0048] In practice, the Text2SQL big data model performs intent recognition based on the query context and matching metadata information from the vector database, extracts keywords, and analyzes the user's query intent.
[0049] S400: Based on the context of the user's query data and the intent recognition, the relationship between keywords is analyzed using the knowledge graph to determine whether the keywords meet the conditions for SQL generation; if the conditions are not met, the user is interacted with through multiple rounds of question and answer, prompting the user to supplement the missing conditions or delete redundant information until the conditions for SQL generation are met.
[0050] In practice, keywords are extracted based on the user's query context and the parsed intent, and the relationships between these keywords are pre-analyzed. Based on known table-to-table relationships, table-to-field relationships, field-to-field relationships, field comments, and relationships between fields and primary / foreign keys, it is determined whether the keywords can satisfy SQL generation. If the determination is satisfied, the table and field information and link conditions from the keywords are added to the prompt. If the determination is not satisfied, multiple rounds of questioning are used to supplement the user's intent.
[0051] S500: The context of the query data, the parsing results of the knowledge graph, and the metadata information and relationships obtained through multiple rounds of question answering are integrated into the prompt. The Text2SQL model generates SQL statements based on the adjusted prompt.
[0052] In practice, the query context and the metadata information and relationships supplemented by the knowledge graph parsing context are added to the prompt and provided to the Text2SQL model, so that the Text2SQL model does not need to or reduces the use of association and directly outputs the results based on the known conditions.
[0053] Furthermore, it also includes:
[0054] When using the knowledge graph to analyze the relationships between keywords and determine whether the keywords can meet the conditions for SQL generation, the relationship between the keywords is determined based on the table-to-table relationship, table-to-field relationship, field-to-field relationship, field comment, or field-to-primary key / foreign key relationship in the knowledge graph. If the determination is satisfied, the table and field information and link conditions in the keywords are added to the prompt.
[0055] S600: Execute a query operation on the vector database based on the SQL statement to obtain the query results.
[0056] In this embodiment, the beneficial effects include at least the following:
[0057] 1. This application constructs a knowledge graph containing multi-dimensional metadata information and integrates it into a system based on a Text2SQL large-scale model. It utilizes vector database technology for efficient retrieval of metadata tags, providing a foundation for intent recognition. Simultaneously, based on the user query context and the parsed content of intent recognition, keywords are extracted, and the relationships between keywords are pre-analyzed to determine whether they meet the SQL generation conditions. If the conditions are met, table and field information and link conditions from the keywords are added to the prompt; if not, multiple rounds of question-and-answer sessions are used to supplement the user intent. Finally, SQL generation is performed based on the finely tuned prompt, significantly improving the accuracy of SQL statements generated by the Text2SQL large-scale model when handling multi-table join queries. Experimental data shows that compared to the traditional RAG model, the Graph RAG model using this invention improves accuracy by 65% (from 20% to 85%), while also significantly increasing the speed of fulfilling requirements (from 1 minute 35 seconds to 17 seconds). This not only optimizes query efficiency but also enhances user experience, providing new ideas and methods for the development of natural language processing and database query optimization technologies.
[0058] 2. This application adds the metadata information and relationships supplemented by the query context and knowledge graph parsing context to the prompt, and provides it to the Text2SQL large model. This allows the Text2SQL large model to reduce or eliminate the need for association and directly output the results based on the known conditions, thereby improving the accuracy of the generated SQL statements.
[0059] In some embodiments, constructing a knowledge graph based on the structural information includes:
[0060] Different business domains are defined based on business scenarios, and knowledge graphs are built based on these business domains.
[0061] In practice, the data is fine-tuned based on metadata, and different business domains such as student management domain and human resources domain are divided according to business scenarios. A knowledge graph is then constructed based on the above content.
[0062] In this embodiment, the accuracy of knowledge graph construction is improved, which in turn improves the accuracy of subsequent keyword extraction, while also improving the efficiency of Text2SQL large model in processing data.
[0063] In some embodiments, it also includes:
[0064] When a user enters query data, the system matches the user's current input question with the metadata, and provides relevant suggestions based on the matching results.
[0065] In this embodiment, not only is the user's query experience improved, but the accuracy of the query statements entered by the user is also improved, thereby improving the accuracy of the query results.
[0066] In some embodiments, it also includes:
[0067] When scoring the index of the metadata based on weight, the greater the weight, the higher the score of the table and field. Unscored tables and fields are all assigned a default base score.
[0068] When performing matching in the vector database, the minimum cosine distance between all vectors in the vector database and the query vector with the closest semantic meaning is calculated, and the vectors are sorted according to the score.
[0069] In this embodiment, the accuracy and efficiency of the query results are improved by improving the index of the vector database.
[0070] This application also provides a query device based on a knowledge graph and a Text2SQL model; please refer to [link / reference]. Figure 2 As shown, the device includes:
[0071] The data acquisition module 100 is used to collect metadata to be queried, extract the structural information of the metadata, and construct a knowledge graph based on the structural information;
[0072] The vector database module 200 is used to create a vector index for the metadata, score the index of the metadata based on weights, and store the processed metadata into the vector database.
[0073] The context analysis module 300 is used to collect user query data, use the Text2SQL model to perform intent recognition on the metadata in the vector database that matches the context of the query data, extract keywords and analyze the user's query intent.
[0074] The judgment module 400 is used to determine whether the relationship between keywords can be satisfied by analyzing the relationship between keywords based on the context of the user query data and the intent recognition, and by using the knowledge graph. If the judgment is not satisfied, the module interacts with the user through multiple rounds of question and answer, prompting the user to supplement the missing conditions or delete the redundant information until the SQL generation conditions are satisfied.
[0075] The query module 500 is used to integrate the context of the query data, the parsing results of the knowledge graph, and the metadata information and relationships obtained through multiple rounds of question answering into a prompt. The Text2SQL model generates an SQL statement based on the adjusted prompt. The query operation is performed on the vector database based on the SQL statement to obtain the query results.
[0076] The functional implementation of each module in the above-mentioned query device based on knowledge graph and Text2SQL model corresponds to the steps in the above-mentioned query method embodiment based on knowledge graph and Text2SQL model. Their functions and implementation processes will not be described in detail here.
[0077] This application also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the query method based on the knowledge graph and Text2SQL model as described in any of the above embodiments.
[0078] This application also provides a computer-readable storage medium on which a program is stored. The computer-readable storage medium refers to a carrier for storing data, and may include, but is not limited to, floppy disks, optical disks, hard disks, flash memory, USB flash drives, and / or Memory Sticks. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The working process, details, and technical effects of the computer-readable storage medium provided in this embodiment can be found in the above embodiment regarding a query method based on a knowledge graph and Text2SQL model, and will not be repeated here.
[0079] The application also provides a computer program product, including computer instructions that, when executed by a processor, implement the steps of the query method based on the knowledge graph and Text2SQL model as described in any of the above embodiments.
[0080] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), RAMbus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and RAMbus dynamic RAM (RDRAM).
[0081] The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as the combination of these technical features does not contradict each other, it should be considered within the scope of this specification. The above embodiments only illustrate several implementation methods of this application, and their descriptions are relatively specific and detailed, but they should not be construed as limiting the scope of the invention patent. It should be noted that for those skilled in the art, several modifications and improvements can be made without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this patent application should be determined by the appended claims.
Claims
1. A query method based on knowledge graph and Text2SQL model, characterized in that, include: Collect the metadata to be queried, including basic business metadata, view and storage information, ETL cleaning SQL information, indicator caliber algorithm information and interface link tracing SQL, to obtain metadata, extract the structural information of the metadata, construct a knowledge graph based on the structural information, divide different business domains according to business scenarios, and construct a knowledge graph based on these business domains. A vector index is created based on the metadata, and the index of the metadata is scored based on weight. The higher the weight, the higher the score of the table and field. Unscored tables and fields are all assigned a base score by default. When performing matching in the vector database, the minimum cosine distance between all vectors in the vector database and the query vector with the closest semantics is calculated. The vectors are sorted according to the score, and the processed metadata is stored in the vector database. Collect user query data, use the Text2SQL model to perform intent recognition on the metadata in the vector database that matches the context of the query data, extract keywords and analyze the user's query intent; Based on the context of the user's query data and the identified intent, the relationship between keywords is analyzed using the knowledge graph. Based on the relationships between tables, fields, and annotations in the knowledge graph, or between fields and primary / foreign keys, it is determined whether the keywords satisfy the SQL generation criteria. If they do, the table and field information and linking conditions from the keywords are added to the prompt to determine if the keywords satisfy the SQL generation criteria. If they do not satisfy the criteria, multiple rounds of question-and-answer interaction are conducted with the user, prompting them to supplement missing conditions or delete redundant information until the SQL generation criteria are met. The context of the query data, the parsing results of the knowledge graph, and the metadata information and relationships obtained through multiple rounds of question answering are integrated into the prompt. The Text2SQL model generates SQL statements based on the adjusted prompt. The query operation is performed on the vector database based on the SQL statement to obtain the query results.
2. The query method based on knowledge graph and Text2SQL model according to claim 1, characterized in that, Also includes: When a user enters query data, the system matches the user's current input question with the metadata, and provides relevant suggestions based on the matching results.
3. A query device based on knowledge graph and Text2SQL model, characterized in that, include: The data acquisition module is used to collect metadata to be queried, including basic business metadata, view and storage information, ETL cleaning SQL information, indicator caliber algorithm information and interface link tracing SQL, to obtain metadata, extract the structural information of the metadata, construct a knowledge graph based on the structural information, divide different business domains according to business scenarios, and construct a knowledge graph based on these business domains. The vector database module is used to create vector indexes based on the metadata and score the indexes of the metadata based on weights. The higher the weight, the higher the score of the table and field. Unscored tables and fields are all assigned a default base score. When performing matching in the vector database, the minimum cosine distance between all vectors in the vector database and the query vector with the closest semantics is calculated. The vectors are sorted according to the scores, and the processed metadata is stored in the vector database. The context analysis module is used to collect user query data, use the Text2SQL model to perform intent recognition on the metadata in the vector database that matches the context of the query data, extract keywords and analyze the user's query intent. The judgment module is used to analyze the relationships between keywords based on the context of the user's query data and the identified intent, using the knowledge graph. Based on the relationships between tables, fields, and annotations in the knowledge graph, or between fields and primary / foreign keys, it determines whether the keywords satisfy the SQL generation criteria. If the criteria are met, the module adds the table and field information and linking conditions from the keywords to the prompt, further determining whether the keywords meet the SQL generation conditions. If the criteria are not met, the module interacts with the user through multiple rounds of questioning, prompting the user to supplement missing conditions or delete redundant information until the SQL generation conditions are met. The query module is used to integrate the context of the query data, the parsing results of the knowledge graph, and the metadata information and relationships obtained through multiple rounds of question answering into a prompt. The Text2SQL model generates an SQL statement based on the adjusted prompt. The query operation is performed on the vector database based on the SQL statement to obtain the query results.
4. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the query method based on the knowledge graph and Text2SQL model as described in any one of claims 1 to 2.
5. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a program, wherein when the program is executed by a processor, it implements the query method based on the knowledge graph and Text2SQL model as described in any one of claims 1 to 2.
6. A computer program product comprising computer instructions, characterized in that, When executed by a processor, the computer instructions implement the steps of the query method based on knowledge graph and Text2SQL model as described in claims 1 to 2.