A natural language to SQL conversion system and method based on content and annotations

Abstract

The present invention discloses a system and method for converting natural language to SQL based on content and annotations, and relates to the field of databases. Vectorization is performed through the annotations of a table and a field, and stored in a vector database; the content of the table is minimum hashed and stored in a local file; the natural language of the user is used to extract keywords through a large language model; the extracted keywords are vectorized and compared with the data in the vector database to obtain a first similarity result; the extracted keywords are minimum hashed and compared with the local file to obtain a second similarity result; the first similarity result and the second similarity result are assembled, and a table is selected through a large language model; based on the selected table, a table creation statement is obtained, and an SQL statement is generated through the large language model; based on the query database selected by the user, the SQL statement is executed to obtain data. The present invention has good versatility and improves the accuracy of query data.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a system and method for converting natural language into SQL based on content and annotations. Background Art

[0002] Under the current data-driven development trend, it is crucial to quickly and accurately obtain the required information from the database. However, not everyone is proficient in SQL statements, so the natural language to SQL statement solution came into being. For the current natural language to SQL statement solution, there are two main directions. One is to automatically convert the query intention expressed by humans in natural language into SQL statements. This method requires the construction of a corresponding large-scale data set for the data in the user's on-site database to complete the training of the deep learning model, so as to obtain a relatively good data, and there is no universality; the other is to use a large language model to directly understand the data fields in the user's database to complete the SQL statement generation. This method will cause deviations in model understanding and poor results when the user's natural language is Chinese and the fields in the database are all in English. Therefore, it is necessary to find a natural language to SQL system and method based on content and annotations that can have both good universality and obtain accurate data. Summary of the invention

[0003] The technical problem to be solved by the present invention is that the prior art has poor versatility and poor comprehension resulting in poor data query results. The purpose is to provide a content and annotation-based natural language to SQL system and method that has both good versatility and can obtain accurate data, thereby solving the above technical problems.

[0004] The present invention is achieved through the following technical solutions:

[0005] A content and annotation-based natural language to SQL system, including:

[0006] The annotation vectorization storage module obtains the annotations of the table and the field, vectorizes them through the vectorization model, and stores them in the vector database;

[0007] The content hash storage module obtains the content of the table, performs minimum hashing through the local sensitive hashing algorithm, and stores it in a local file;

[0008] The keyword extraction module constructs a prompt project based on the user's natural language and extracts keywords through a large language model;

[0009] The annotation similarity matching module vectorizes the extracted keywords through the vectorization model and compares them with the data in the vector database to obtain a first similarity result;

[0010] The content similarity matching module performs minimum hashing of the extracted keywords through a local sensitive hashing algorithm, and then compares the extracted keywords with the local file to obtain a second similarity result;

[0011] A table selection module assembles the first similar result and the second similar result, constructs a prompt project, and selects a table through a large language model;

[0012] The SQL statement generation module obtains table creation statements based on the selected table, builds a prompt project, and generates SQL statements through a large language model;

[0013] The SQL statement execution module executes SQL statements to obtain data based on the query database selected by the user.

[0014] The annotation vectorization storage module obtains the annotations of the table and the field, and performs vectorization through the vectorization model, including:

[0015] The comment vectorization storage module vectorizes and concatenates all characters of the table comments and field comments.

[0016] The step of storing the content hash storage module in a local file includes:

[0017] A first number is obtained according to the number of items of each content in the table; a second number is obtained according to the number of characters of each content; and a storage location of a local file is generated according to the first number and the second number of each content.

[0018] The step of executing SQL statements to obtain data based on the query database selected by the user includes:

[0019] According to the storage location of the second similar result, the annotation of the corresponding table and the annotation of the field are obtained to obtain a third similar result; and based on the third similar result, the query database selected by the user is obtained.

[0020] The keyword extraction module constructs a prompt project based on the user's natural language and extracts keywords through a large language model, including:

[0021] The keyword extraction module extracts nouns and verbs according to the user's natural language, and associates multiple nouns according to the extracted verbs to obtain multiple groups of associated keywords.

[0022] The annotation similarity matching module vectorizes the extracted keywords through a vectorization model and compares the extracted keywords with the data in the vector database to obtain a first similarity result, including:

[0023] All characters of each keyword are vectorized and concatenated;

[0024] After comparing each group of associated keywords with the data in the vector database, similarity results of each group of associated keywords are obtained.

[0025] The content similarity matching module performs minimum hashing on the extracted keywords through a local sensitive hashing algorithm and then compares the extracted keywords with the local file to obtain a second similarity result, including:

[0026] All characters of each keyword are vectorized and concatenated;

[0027] After comparing each group of associated keywords with the data in the local file, similarity results of each group of associated keywords are obtained.

[0028] A method for converting natural language to SQL based on content and annotations, comprising:

[0029] Obtain table and field comments, vectorize them using the vectorization model, and store them in the vector database;

[0030] Get the contents of the table, perform minimum hashing using the local sensitive hashing algorithm, and store them in a local file;

[0031] Use the user's natural language to build a prompt project and extract keywords through a large language model;

[0032] After vectorizing the extracted keywords through the vectorization model, the keywords are compared with the data in the vector database to obtain a first similarity result;

[0033] The extracted keywords are minimum hashed using a local sensitive hashing algorithm, and then compared with the local file to obtain a second similarity result;

[0034] Assembling the first similar result and the second similar result to construct a prompting project, and selecting a table through a large language model;

[0035] Based on the selected table, obtain the table creation statement, build the prompt project, and generate SQL statements through the large language model;

[0036] Based on the query database selected by the user, SQL statements are executed to obtain data.

[0037] An electronic device comprises a memory, a processor and a computer program running on the processor, wherein the processor implements the content- and annotation-based natural language-to-SQL system when executing the computer program.

[0038] A computer-readable storage medium stores a computer program, wherein the computer program implements the content- and annotation-based natural language-to-SQL system when executed by a processor.

[0039] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0040] The present invention provides a natural language to SQL system based on content and annotations, which obtains the annotations of a table and a field, vectorizes them through a vectorization model, and stores them in a vector database; obtains the content of the table, performs minimum hashing through a local sensitive hashing algorithm, and stores them in a local file; constructs a prompt project with the natural language of the user, extracts keywords through a large language model; vectorizes the extracted keywords through a vectorization model, and compares them with the data in the vector database to obtain a first similar result; compares the extracted keywords with the local file after performing minimum hashing through a local sensitive hashing algorithm to obtain a second similar result; assembles the first similar result and the second similar result, constructs a prompt project, and selects a table through a large language model; obtains a table building statement based on the selected table, constructs a prompt project, and generates an SQL statement through a large language model; executes the SQL statement to obtain data based on the query database selected by the user. The present invention compares the natural language of the user with the content and annotations in the database, generates an SQL statement of the corresponding database, completes the display of the corresponding data, has good versatility, and improves the accuracy of the obtained data. Compared with the prior art, the present invention solves the problems of poor versatility and poor data query effect caused by poor comprehension ability. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the technical solutions of the exemplary embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. It should be understood that the following drawings only illustrate certain embodiments of the present invention and should not be regarded as limiting the scope. For those of ordinary skill in the art, other relevant drawings can be obtained based on these drawings without creative work. In the drawings:

[0042] Figure 1 This is a flowchart of a natural language to SQL system based on content and annotations according to an embodiment of the present application. DETAILED DESCRIPTION

[0043] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with embodiments and drawings. The exemplary embodiments of the present invention and their description are only used to explain the present invention and are not intended to limit the present invention.

[0044] Example

[0045] like Figure 1 As shown, the embodiment of the present application provides a natural language to SQL system based on content and annotations, including:

[0046] The annotation vectorization storage module obtains the annotations of the table and the field, vectorizes them through the vectorization model, and stores them in the vector database;

[0047] The content hash storage module obtains the content of the table, performs minimum hashing through the local sensitive hashing algorithm, and stores it in a local file;

[0048] The keyword extraction module constructs a prompt project based on the user's natural language and extracts keywords through a large language model;

[0049] The annotation similarity matching module vectorizes the extracted keywords through the vectorization model and compares them with the data in the vector database to obtain a first similarity result;

[0050] The content similarity matching module performs minimum hashing of the extracted keywords through the local sensitive hashing algorithm, and then compares them with the above local files to obtain the second similarity result;

[0051] A table selection module assembles the first similar result and the second similar result, constructs a prompt project, and selects a table through a large language model;

[0052] The SQL statement generation module obtains table creation statements based on the selected table, builds a prompt project, and generates SQL statements through a large language model;

[0053] The SQL statement execution module executes SQL statements to obtain data based on the query database selected by the user.

[0054] The steps of the annotation vectorization storage module, obtaining the annotations of the table and the field, and performing vectorization through the vectorization model, include:

[0055] The above comment vectorization storage module vectorizes all characters of the table comments and field comments and concatenates them.

[0056] The steps of storing the above content hash storage module into a local file include:

[0057] A first number is obtained according to the number of items in the table; a second number is obtained according to the number of characters in each item; and a storage location of a local file is generated according to the first number and the second number of each item.

[0058] The above step of querying the database based on the user's selection and executing the SQL statement to obtain data includes:

[0059] According to the storage location of the second similar result, the annotation of the corresponding table and the annotation of the field are obtained to obtain a third similar result; based on the third similar result, the query database selected by the user is obtained.

[0060] The keyword extraction module constructs a prompt project based on the user's natural language and extracts keywords through a large language model, including the following steps:

[0061] The keyword extraction module extracts nouns and verbs according to the user's natural language, and associates multiple nouns according to the extracted verbs to obtain multiple groups of associated keywords.

[0062] The annotation similarity matching module vectorizes the extracted keywords through the vectorization model and compares them with the data in the vector database to obtain the first similarity result, including:

[0063] All characters of each keyword are vectorized and concatenated;

[0064] After comparing each group of associated keywords with the data in the above vector database, similarity results of each group of associated keywords are obtained.

[0065] The content similarity matching module performs minimum hashing on the extracted keywords through a local sensitive hashing algorithm and compares the extracted keywords with the local file to obtain a second similarity result, including:

[0066] All characters of each keyword are vectorized and concatenated;

[0067] After comparing each group of associated keywords with the data in the above local file, similar results of each group of associated keywords are obtained.

[0068] Optionally, by providing the connection information of the query database selected by the user, the corresponding SQL statement is provided to query the data related to the table and field annotations and the specific content in the table in the corresponding database. Optionally, the connection information of the database includes the data type and access rights required to access the database to generate the SQL statement, etc. Through the annotation vectorization storage module and the content hash storage module, the data is processed accordingly and cached in different locations for use when the user asks questions. After the keywords are obtained through the keyword extraction module, the corresponding similar content is obtained through the annotation similarity matching module and the content similarity matching module respectively. The similarity results assembled based on the vectors converted from the annotations of the table and field and the similarity of the corresponding content of the table, the filtered table structure is obtained through the table selection module, and the results are returned to the SQL statement generation module, thereby generating the corresponding SQL statement, and the query results are returned through the SQL statement execution module, and the query results are displayed to the user.

[0069] In summary, the present application provides a system and method for converting natural language to SQL based on content and annotations:

[0070] By obtaining the annotations of the table and the annotations of the field, vectorizing them through the vectorization model and storing them in the vector database; obtaining the content of the table, performing minimum hashing through the local sensitive hashing algorithm, and storing them in the local file; constructing a prompt project with the user's natural language, extracting keywords through the large language model; vectorizing the extracted keywords through the vectorization model, and comparing them with the content in the vector database to obtain the first similarity result; performing minimum hashing through the local sensitive hashing algorithm on the extracted keywords, and comparing them with the local file to obtain the second similarity result; assembling the first similarity result and the second similarity result, constructing a prompt project, and selecting a table through the large language model; based on the selected table, obtaining a table building statement, constructing a prompt project, and generating an SQL statement through the large language model; based on the query database selected by the user, executing the SQL statement to obtain query data. The present invention compares the user's natural language with the content and annotations in the database, generates an SQL statement of the corresponding database, completes the display of the corresponding data, has good versatility, and improves the accuracy of the obtained data. Compared with the prior art, it solves the problems of poor versatility and poor data query effect caused by poor comprehension ability.

[0071] The specific implementation methods described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation method of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A content and annotation-based natural language to SQL system, characterized in that: include: The annotation vectorization storage module obtains the annotations of the table and the field, vectorizes them through the vectorization model, and stores them in the vector database; The content hash storage module obtains the content of the table, performs minimum hashing through the local sensitive hashing algorithm, and stores it in a local file; The keyword extraction module constructs a prompt project based on the user's natural language and extracts keywords through a large language model; The annotation similarity matching module vectorizes the extracted keywords through the vectorization model and compares them with the data in the vector database to obtain a first similarity result; The content similarity matching module performs minimum hashing of the extracted keywords through a local sensitive hashing algorithm, and then compares the extracted keywords with the local file to obtain a second similarity result; A table selection module assembles the first similar result and the second similar result, constructs a prompt project, and selects a table through a large language model; The SQL statement generation module obtains table creation statements based on the selected table, builds a prompt project, and generates SQL statements through a large language model; The SQL statement execution module executes SQL statements to obtain data based on the query database selected by the user.

2. A content and annotation-based natural language to SQL system according to claim 1, characterized in that: The annotation vectorization storage module obtains the annotations of the table and the field, and performs vectorization through the vectorization model, including: The comment vectorization storage module vectorizes and concatenates all characters of the table comments and field comments.

3. A content and annotation-based natural language to SQL system according to claim 1, characterized in that: The step of storing the content hash storage module in a local file includes: A first number is obtained according to the number of items of each content in the table; a second number is obtained according to the number of characters of each content; and a storage location of a local file is generated according to the first number and the second number of each content.

4. A content and annotation-based natural language to SQL system according to claim 3, characterized in that: The step of executing SQL statements to obtain data based on the query database selected by the user includes: According to the storage location of the second similar result, the annotation of the corresponding table and the annotation of the field are obtained to obtain a third similar result; and based on the third similar result, the query database selected by the user is obtained.

5. A content and annotation-based natural language to SQL system according to claim 1, characterized in that: The keyword extraction module constructs a prompt project based on the user's natural language and extracts keywords through a large language model, including: The keyword extraction module extracts nouns and verbs according to the user's natural language, and associates multiple nouns according to the extracted verbs to obtain multiple groups of associated keywords.

6. A content and annotation-based natural language to SQL system according to claim 5, characterized in that: The annotation similarity matching module vectorizes the extracted keywords through a vectorization model and compares the extracted keywords with the data in the vector database to obtain a first similarity result, including: All characters of each keyword are vectorized and concatenated; After comparing each group of associated keywords with the data in the vector database, similarity results of each group of associated keywords are obtained.

7. A content and annotation-based natural language to SQL system according to claim 1, characterized in that: The content similarity matching module performs minimum hashing on the extracted keywords through a local sensitive hashing algorithm and then compares the extracted keywords with the local file to obtain a second similarity result, including: All characters of each keyword are vectorized and concatenated; After comparing each group of associated keywords with the data in the local file, similarity results of each group of associated keywords are obtained.

8. A method for converting natural language to SQL based on content and annotations, characterized in that: include: Obtain table and field comments, vectorize them using the vectorization model, and store them in the vector database; Get the contents of the table, perform minimum hashing using the local sensitive hashing algorithm, and store them in a local file; Use the user's natural language to build a prompt project and extract keywords through a large language model; After vectorizing the extracted keywords through the vectorization model, the keywords are compared with the data in the vector database to obtain a first similarity result; The extracted keywords are hashed using a local sensitive hashing algorithm, and then compared with the local file to obtain a second similarity result; Assembling the first similar result and the second similar result to construct a prompting project, and selecting a table through a large language model; Based on the selected table, obtain the table creation statement, build the prompt project, and generate SQL statements through the large language model; Based on the query database selected by the user, SQL statements are executed to obtain data.

9. An electronic device comprising a memory, a processor, and a computer program running on the processor, characterized in that: When the processor executes the computer program, the natural language to SQL system based on content and annotations is implemented as described in any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, a natural language to SQL system based on content and annotations as described in any one of claims 1 to 7 is implemented.

Images