NL2SQL system based on vector embedding and locality sensitive hashing

Abstract

The invention provides an NL2SQL system based on vector embedding and locality sensitive hashing. The NL2SQL system comprises a data acquisition module, a preprocessing module, a list screening module, an SQL generation module, a result verification module and a cache management module. Collecting a natural language query problem; performing multi-modal integration processing on the data in the database to obtain corresponding multi-modal data, and performing vector conversion processing to obtain a multi-modal data vector table and a multi-modal data vector column; constructing a locality sensitive hash index; performing feature processing on the natural language query problem to obtain query feature data, performing vector conversion processing to obtain a query feature vector, further obtaining a query multi-modal data vector table and a query multi-modal data vector column, and generating an SQL query statement; verifying the SQL query statement to obtain a query verification result, and generating an execution error processing scheme; and the accuracy and the efficiency of the NL2SQL system are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of natural language processing and database query, and in particular to an NL2SQL system based on vector embedding and locality sensitive hashing. Background Art

[0002] Traditional database queries usually require users to have SQL programming knowledge and have a deep understanding of the database structure to be queried. However, the emergence of NL2SQL (Natural Language to SQL) technology has changed this situation. NL2SQL technology allows users to directly obtain the required information through query questions described in natural language without understanding the details of the database. The NL2SQL system can automatically convert the user's natural language query into a standard SQL query statement, thereby achieving the goal of querying the database without code.

[0003] At present, there are two main approaches to building the NL2SQL system: one is to train a Seq2Seq model, which usually includes an encoder and a decoder. The encoder is responsible for encoding the database information and the user's query questions, generating an intermediate representation, and the decoder decodes the intermediate representation into a SQL query statement. Although this method has achieved certain success in the early days, its performance and accuracy are still greatly limited when dealing with complex database models. The other is the NL2SQL system based on a large language model (LLM). With the continuous improvement of the pre-trained LLM capabilities, this type of system has gradually become mainstream. It inputs the database information (mainly the CREATE TABLE statements of each table) and the user's query questions into the context of the LLM, uses the context capture capability of the LLM to automatically extract the required tables and columns, and generates the final SQL query statement.

[0004] Although the LLM-based NL2SQL system has excellent performance, it also faces some challenges as the database size increases. First, the problem of context information overload is becoming increasingly prominent. As the database size increases, the number of CREATE TABLE statements that need to be input into the LLM context will also increase dramatically, which will not only reduce the LLM's ability to capture key information, but may also cause errors in the generated SQL statements; more seriously, if the length of the CREATE TABLE statement exceeds the maximum limit of the LLM context, the entire NL2SQL system will fail directly.

[0005] Secondly, unclear or mismatched information often occurs. The table name and column name in the CREATE TABLE statement sometimes do not match the actual information stored in the database, or the description is not detailed enough (such as using pinyin abbreviations). Such unclear or brief information will cause LLM to be unable to accurately identify the relevant tables and columns, thus affecting the accuracy of SQL generation.

[0006] In addition, the importance of the actual stored value cannot be ignored. When generating SQL statements, the actual content stored in the database should be used as an important reference. For example, when a user query contains an entity that needs to be matched with a value stored in the database, LLM usually directly puts the matching entity in the user's question into the WHERE condition of the SQL; however, if the matching entity provided by the user is synonymous with the actual stored value but does not completely match it, the generated SQL statement may fail to execute.

[0007] Therefore, a NL2SQL system based on vector embedding and locality sensitive hashing is provided. Summary of the invention

[0008] In order to solve the above technical problems, the object of the present invention is to provide a NL2SQL system based on vector embedding and locality sensitive hashing.

[0009] In order to achieve the above object, the present invention provides the following technical solutions: an NL2SQL system based on vector embedding and locality sensitive hashing, comprising: a data acquisition module, a preprocessing module, a table column screening module, an SQL generation module, a result verification module and a cache management module; The data collection module is used to receive natural language query questions input by users; The preprocessing module is used to perform multimodal integration processing on the text description, data type, and data distribution in the database to obtain multimodal data; and perform vector conversion processing to obtain a multimodal data vector table and a multimodal data vector column, and then construct a local sensitive hash index; The table column screening module is used to perform feature processing on natural language query questions to obtain query feature data, and perform vector conversion processing to obtain query feature vectors, thereby obtaining query multimodal data vector tables and query multimodal data vector columns; The SQL generation module is used to generate SQL query statements according to querying the multimodal data vector table and querying the multimodal data vector column; The result verification module is used to verify the syntax and execution results of SQL query statements, obtain the corresponding query verification results, and generate corresponding execution error handling solutions; The cache management module is used to cache the SQL query statement storage and obtain the cached SQL query statement.

[0010] According to one preferred embodiment of the present invention, the process of performing feature processing on a natural language query question includes: Obtain natural language query questions input by users; The process of preprocessing the natural language query includes: removing punctuation marks, HTML tags, special symbols, etc. in the natural language query; if the natural language query is in English, converting it to lowercase to unify the uppercase and lowercase format; dividing the natural language query into individual words; removing stop words; The process of feature processing the preprocessed natural language query problem includes: building a vocabulary; for each query, counting each word in the vocabulary and the number of times it appears in the query, setting an occurrence threshold, and if the number is greater than or equal to the threshold, the corresponding word is recorded as query feature data.

[0011] According to one of the preferred embodiments of the present invention, the process of performing vector conversion processing on the query feature data includes: Get query feature data and obtain the number of occurrences; According to the number of occurrences of the query feature data, the number of occurrences of the query feature data in the query is calculated and divided by the total number of words in the query, which is recorded as the word frequency; Set the corpus; Calculate the inverse document frequency; count the total number of documents in the corpus and the number of documents containing each word. Divide the total number of documents by the number of documents containing the word, then take the logarithm to get the inverse document frequency; According to the word frequency and inverse document frequency, the feature value is obtained as , the eigenvalue for: ;in, is the word frequency, is the inverse document frequency; According to the characteristic value of the corresponding query characteristic data , arranged in the order of the vocabulary to form a vector, recorded as the query feature vector.

[0012] According to one preferred embodiment of the present invention, the process of performing multimodal integration processing on the text description, data type, and data distribution of the columns in the database includes: Obtain text description information of each column from the database, and obtain text feature data based on text feature extraction technology; clarify the data type of each column; perform statistical analysis on the data of each column to obtain data distribution information; The text feature data, data type and data distribution information of the corresponding column are recorded as multimodal data.

[0013] According to one preferred embodiment of the present invention, the process of performing vector conversion processing on multimodal data includes: Obtain multimodal data, calculate the embedding vector of each description based on the text vector embedding model, and associate the embedding vector with the column description and its corresponding table column by storing value pairs, obtain the corresponding multimodal data vector table and multimodal data vector column, and store them in the vector database; The process of preprocessing the stored values ​​includes: Use the n-gram method to divide the string into a set of substrings; calculate the minimum hash of the substring set to generate a discrete value vector of a fixed length, which is the minimum hash of the stored value; establish an association between the minimum hash and the stored value and its table column through key-value pairs; use the locality sensitive hashing (LSH) technology to divide these minimum hashes into different buckets, denoted as hash buckets, each hash bucket stores a set of feature vector indexes, denoted as locality sensitive hash indexes.

[0014] According to one of the preferred embodiments of the present invention, the process of obtaining a query multimodal data vector table and a query multimodal data vector column according to a local sensitive hash index and a query feature vector includes: The tables and columns involved in the user query are determined through a two-way filtering mechanism, including embedding vector recall and LSH recall, which are respectively recorded as the first-way filtering and the second-way filtering; The first screening process includes: Based on the text vector embedding model, calculate the embedding vector of the user query question; Calculate the cosine similarity between the embedding vector of the user query and the text description embedding vectors of all columns in the database; Filter out the top-k embedding vectors with the highest cosine similarity, and obtain the corresponding query multimodal data vector table and query multimodal data vector column of the database as the first screening result according to the embedding vectors; The second screening process includes: Extract matching entities from user query questions based on the Large Language Model (LLM); For each extracted matching entity, the minimum hash of the matching entity is calculated based on the minimum hash calculation method; The locality sensitive hashing (LSH) technique is used to divide the minimum hash of the matching entity into different buckets; Extract all minimum hashes in the same LSH bucket as the minimum hash of the matching entity; Calculate the minimum hash of the matching entity and the corresponding Jaccard similarity, and select the top-n minimum hashes with the highest Jaccard similarity; the actual storage value corresponding to the minimum hash and the query multimodal data vector table and query multimodal data vector column where it is located are the screening results of this matching entity; The filtering results of all matching entities are collected together to obtain the second filtering result.

[0015] According to one of the preferred embodiments of the present invention, the process of generating an SQL query statement includes: Obtain query multimodal data vector table, query multimodal data vector column, first path screening result and second path screening result; The CREATE TABLE statements of the first filtering result and the second filtering result are trimmed, and only the CREATE TABLE statements of the query multimodal data vector table obtained by filtering are retained, and the information of the primary key column, the foreign key column and the filtered column is retained, and the text description and the actual storage value filtered by the second filtering are added to the annotation of the corresponding query multimodal data vector column; The trimmed CREATE TABLE statement and user query question are simultaneously input into the context of the LLM model, and the context capture capability of the LLM model is used to generate the final SQL query statement.

[0016] According to one of the preferred embodiments of the present invention, the process of caching SQL query statements includes: Set up the cache database; Perform hash processing on the user's query statement, generate a hash value of the user's query statement based on a hash algorithm, and use the hash value as a cache key; When the SQL generation module generates a new SQL query statement and passes the verification by the result verification module, the hash value of the query statement corresponding to the user and the corresponding SQL query statement are stored in the cache database, and the SQL query statement in the cache database is recorded as a cache SQL query statement; When a user initiates a new query, the hash value of the corresponding query statement is first obtained, and then the SQL query statement corresponding to the hash value is checked in the cache database. If it exists, the cached SQL query statement is directly obtained from the cache database; if it does not exist, the new query initiated by the user is passed to the table column screening module for subsequent processing.

[0017] According to one of the preferred embodiments of the present invention, the syntax and execution result of the SQL query statement are verified, and the process of obtaining the corresponding query verification result includes: Get the SQL generation module to generate SQL query statements; Set up an in-memory database; use the sqlite3 library to establish a connection with the in-memory database; after creating the connection, obtain a connection object; A cursor object can be created through the cursor() method of the connection object; Use the execute() method of the cursor object to execute the SQL query statement generated by the SQL generation module; and send the SQL query statement to the memory database for processing; Depending on the type of SQL query statement, which includes query statements and non-query statements, different processing methods are required; if it is a query statement, the query result is obtained; if it is a non-query statement, it is necessary to check whether the statement is executed successfully.

[0018] For query statements, use the fetchall(), fetchone(), or fetchmany() methods of the cursor object to obtain the query verification results; The query verification result includes verification passed and verification abnormal.

[0019] The present invention further provides a computer-readable storage medium, which stores a computer program. The computer program can be executed by a processor to implement the above-mentioned NL2SQL system based on vector embedding and locality sensitive hashing.

[0020] Compared with the prior art, the beneficial effect of the present invention is that the description of the meaning and storage value of each column and the recall of the embedded vector generated by the preprocessing module can effectively solve the problem of unclear or mismatched information. Through the second-way screening (LSH recall), the system can recall the actual storage values ​​similar to the matching entities in the user query, and provide these storage values ​​to the LLM, so that the large model can judge what value should be matched in the WHERE condition by itself, thereby preventing the match from failing. At the same time, the results of the second-way screening (i.e., the tables and columns related to the storage values) are provided to the LLM as important information, further enhancing the relevance of the context. Overall, the two-way screening mechanism extracts the tables and columns that the LLM is most likely to use, instead of putting all the table and column information into the context, which not only helps the LLM focus on the most critical table and column information, but also avoids the failure of the NL2SQL system caused by insufficient LLM context length, thereby significantly improving the success rate and accuracy of the NL2SQL system. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the present invention. For ordinary technicians in this field, other drawings can also be obtained based on these drawings.

[0022] Figure 1 Schematic diagram of the steps of an NL2SQL system based on vector embedding and locality sensitive hashing.

[0023] Figure 2 It is a process decision diagram of the NL2SQL system based on vector embedding and locality sensitive hashing.

[0024] Figure 3 A module diagram of an NL2SQL system based on vector embedding and locality sensitive hashing. DETAILED DESCRIPTION

[0025] To make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be described in detail below. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other implementation methods obtained by ordinary technicians in this field without creative work belong to the scope of protection of the present invention.

[0026] like Figure 3 As shown, a NL2SQL system based on vector embedding and locality sensitive hashing includes: a data acquisition module, a preprocessing module, a table column screening module, an SQL generation module, a result verification module and a cache management module; The data collection module is used to receive natural language query questions input by users; The preprocessing module is used to perform multimodal integration processing on the text description, data type, and data distribution of the columns in the database to obtain corresponding multimodal data; perform vector conversion processing on the multimodal data to obtain corresponding multimodal data vector tables and multimodal data vector columns, and construct a local sensitive hash index; The table column screening module is used to perform feature processing on the natural language query question to obtain query feature data, perform vector conversion processing on the query feature data to obtain a query feature vector, and obtain a query multimodal data vector table and a query multimodal data vector column based on the local sensitive hash index and the query feature vector; The SQL generation module is used to generate SQL query statements based on the query multimodal data vector table and the query multimodal data vector column and based on the large language model; The result verification module is used to verify the syntax and execution result of the SQL query statement, obtain the corresponding query verification result, and generate the corresponding execution error handling solution according to the query verification result; The cache management module is used to cache the SQL query statement storage and obtain the cached SQL query statement; It should be further explained that, in the specific implementation process, the specific process of performing feature processing on the natural language query question and obtaining the query feature data includes: Obtain natural language query questions input by users; Preprocess the natural language query problem. The specific process includes: 1. Remove special characters: Remove punctuation marks, HTML tags, special symbols, etc. in the natural language query problem to avoid interference of these characters on feature extraction; 2. Convert to lowercase: If the natural language query problem is in English, convert it to lowercase to unify the case format and avoid being regarded as different words due to different cases; 3. Word segmentation: Segment the natural language query problem into individual words. Different languages and scenarios can use different word segmentation tools. If the natural language query problem is in English, use spaces for simple word segmentation. If the natural language query problem is in Chinese, use a word segmentation library; 4. Remove stop words: It should be further noted that the stop words refer to words that frequently appear in the natural language query problem but contribute little to semantic understanding, such as "de", "shi", "zai", etc.; Removing stop words can reduce the noise of data and improve the effectiveness during feature extraction.

[0027] For example, if the natural language query problem is "Query the user's name, age!", after removing special characters, it becomes "Query the user's name age", after word segmentation, it becomes "Query user's name age", and after removing stop words, it becomes "Query user name age"; If the natural language query problem is "QueryUser's Name", after converting to lowercase, it becomes "query user's name".

[0028] Perform feature processing on the preprocessed natural language query problem. The specific process includes: 1. Build a vocabulary: Count the different words that appear in all query texts and build a vocabulary; 2. For each query, count each word in the vocabulary and the number of times it appears in the query. Set a threshold for the number of appearances. If the number of times is greater than or equal to the threshold, record the corresponding word as query feature data to improve the efficiency during feature extraction.

[0029] It should be further noted that in the specific implementation process, the specific process of performing vector conversion processing on the query feature data to obtain the query feature vector includes: Obtain the query feature data and the number of times it appears; 1. Calculate the word frequency: According to the number of times the query feature data appears, calculate the number of times it appears in the query divided by the total number of words in the query, and record the word frequency as ; 2. Calculate the inverse document frequency; count the total number of documents in the corpus, and the number of documents containing each term. Divide the total number of documents by the number of documents containing the term, and then take the logarithm to obtain the inverse document frequency, denoted as ; It should be further noted that the corpus is set according to the actual situation, such as the e-commerce field, the medical field, etc. The natural language query questions of users in this field in the past can be collected as the corpus; these historical query data reflect the common query needs and expression methods in this field, and are more in line with the actual application scenario; 3. Calculate the eigenvalue: According to the term frequency and the inverse document frequency , obtain the eigenvalue denoted as , and the eigenvalue is: ; Arrange according to the eigenvalues corresponding to the query feature data in the order of the vocabulary to form a vector, denoted as the query feature vector; For example, if the word "query" appears 1 time in the vocabulary and the total number of words is 4, then the term frequency of "query" is .

[0030] It should be further noted that in the specific implementation process, the specific process of multi-modal integration processing of the text description, data type, and data distribution of columns in the database to obtain the corresponding multi-modal data includes: Obtain the text description information of each column from the database. Based on the text feature extraction technology, obtain the text feature data. It should be further noted that the text description information is usually a brief description of the meaning represented by the column. For example, for the columns named A2 to A16 in a certain table, through the meaning description, it can be clearly known that A2 represents district_name, A3 represents region, A4 represents the number of inhabitants, etc.; this description enables the large model to understand the actual meaning of the column and avoid misunderstandings caused by non-intuitive column names; Clarify the data type of each column. Common data types include integers (int), floating-point numbers (float), strings (string), dates (date), etc. The data type can be obtained from the table structure definition of the database. For example, the frequency column of the account table may contain the following enumerated values: "POPLATEK MESICNE" represents monthly issuance, "POPLATEK TYDNE" represents weekly issuance, "POPLATEK PO OBRATU" represents issuance after transaction; The storage value description mainly targets the following three columns: 1. Columns with fixed storage formats (such as time columns), such as "date column, format: YYYY - MM - DD"; 2. Columns storing enumeration values ​​(such as frequency columns), such as "frequency columns, enumeration values: daily, weekly, monthly"; 3. Columns storing continuous values, such as "age column, value range Indicates minors, indicates adults, and 60 and above indicates the elderly”; Perform statistical analysis on the data in each column to obtain data distribution information. It should be further explained that common statistical indicators include mean, median, standard deviation, minimum, maximum, mode, etc. For numerical columns, these indicators can be calculated using statistical functions; for categorical columns, the frequency of occurrence of each category can be counted. The text feature data, data type and data distribution information of the corresponding column are recorded as multimodal data.

[0031] It should be further explained that, in the specific implementation process, the multimodal data is subjected to vector conversion processing to obtain the corresponding multimodal data vector table and multimodal data vector column. The specific process includes: Obtain multimodal data, calculate the embedding vector of each description based on the text vector embedding model, and associate the embedding vector with the column description and its corresponding table column by storing value pairs, obtain the corresponding multimodal data vector table and multimodal data vector column, and store them in the vector database; It should be further explained that, in the specific implementation process, the stored value is preprocessed, and the specific steps are as follows: It should be further explained that the DISTINCT storage value is limited to the column whose storage type is string; 1. Use the n-gram method to divide the string into a set of substrings; 2. Calculate the minimum hash of the substring set and generate a discrete value vector of fixed length, which is the minimum hash of the stored value; 3. Establish the association between the minimum hash and the stored value and its table column through the key-value pair; 4. Use the local sensitive hashing (LSH) technology to divide these minimum hashes into different buckets, recorded as hash buckets. Each hash bucket stores the index of a set of feature vectors, recorded as the local sensitive hash index, for subsequent fast matching.

[0032] It should be further explained that, in the specific implementation process, the specific process of obtaining the query multimodal data vector table and the query multimodal data vector column according to the local sensitive hash index and the query feature vector includes: The tables and columns involved in the user query are determined through a two-way filtering mechanism, including embedding vector recall and LSH recall, which are respectively recorded as the first-way filtering and the second-way filtering; It should be further explained that, in the specific implementation process, the specific steps of the first screening are as follows: 1. Based on the text vector embedding model, calculate the embedding vector of the user query question; 2. Calculate the cosine similarity between the embedding vector of the user query and the text description embedding vectors of all columns in the database; 3. Filter out the top-k embedding vectors with the highest cosine similarity, and obtain the database corresponding query multimodal data vector table and query multimodal data vector column as the first screening result according to the embedding vectors; it should be further explained that the top-k value is dynamically adjusted according to the large model context length and database scale, and is usually set to 5 to 10; It should be further explained that, in the specific implementation process, the specific steps of the second screening are as follows: 1. Extract matching entities from the user query using a large language model (LLM). It should be further noted that the number of matching entities is greater than or equal to 1; 2. For each extracted matching entity, calculate the minimum hash of the matching entity based on the minimum hash calculation method; 3. Use locality sensitive hashing (LSH) technology to divide the minimum hash of matching entities into different buckets; 4. Extract all minimum hashes in the same LSH bucket as the minimum hash of the matching entity; 5. Calculate the minimum hash of the matching entity and the corresponding Jaccard similarity, and select the top-n minimum hashes with the highest Jaccard similarity; it should be further explained that the actual storage value corresponding to the minimum hash and the query multimodal data vector table and query multimodal data vector column where it is located are the screening results of this matching entity; the value of top-n is dynamically adjusted according to the large model context length and database size, and is usually set to 3 to 5; 6. Collect all the screening results of the matching entities together to form the second screening result; For example, the user query "How many accounts who choose issuance after transaction are staying in east region of Bohemia?" explains the specific process of first-pass screening and second-pass screening: 1. First screening: The frequency column of the account table is recalled through the embedding vector. Because the column description "POPLATEK PO OBRATU means issuance after transaction" is highly similar to the semantics of "issuance after transaction" in the user query, the cosine similarity calculated by the embedding vectors of the two is high, so this column is retained; 2. Second filtering: Extract the matching entities "accounts", "issuance aftertransaction", and "east region of Bohemia" in the user query. "east region of Bohemia" has a high structural similarity with the actual stored value "East Bohemia" in column A3 of the district table. The minimum hash value of the two is assigned to the same LSH bucket, and the Jaccard similarity is within the top-n range. Therefore, the stored value and its table and column are retained.

[0033] For example, the district table contains several columns, such as columns A2 to A4, where the enumeration value district_name in column A2 represents the district name, the enumeration value region in column A3 represents the region, and the enumeration value number of inhabitants in column A4 represents the number of residents.

[0034] It should be further explained that, in the specific implementation process, the specific process of generating SQL query statements includes: Obtain query multimodal data vector table, query multimodal data vector column, first path screening result and second path screening result; The CREATE TABLE statements of the first filtering result and the second filtering result are trimmed, and only the CREATE TABLE statements of the query multimodal data vector table obtained by filtering are retained, and the information of the primary key column, the foreign key column and the filtered column is retained, and the text description and the actual storage value filtered by the second filtering are added to the annotation of the corresponding query multimodal data vector column; The trimmed CREATE TABLE statement and user query question are simultaneously input into the context of the LLM model, and the context capture capability of the LLM model is used to generate the final SQL query statement.

[0035] It should be further explained that, in the specific implementation process, the SQL query statement storage is cached, and the specific process of obtaining the cached SQL query statement includes: Set up the cache database; Perform hash processing on the user's query statement, generate a hash value of the user's query statement based on a hash algorithm, and use the hash value as a cache key to quickly find the corresponding SQL query statement; When the SQL generation module generates a new SQL query statement and passes the verification by the result verification module, the hash value of the query statement corresponding to the user and the corresponding SQL query statement are stored in the cache database, and the SQL query statement in the cache database is recorded as a cache SQL query statement; It should be further explained that when a user initiates a new query, the hash value of the corresponding query statement is first obtained, and then the cache database is checked to see whether the SQL query statement corresponding to the hash value exists; If it exists, the cached SQL query statement is directly obtained from the cache database; if it does not exist, the new query initiated by the user is passed to the table column screening module for subsequent processing.

[0036] It should be further explained that, in the specific implementation process, the syntax and execution result of the SQL query statement are verified, and the specific process of obtaining the corresponding query verification result includes: Get the SQL generation module to generate SQL query statements; Setting up an in-memory database; it should be further explained that the in-memory database is a temporary database, data is stored in memory, and is suitable for quickly verifying the syntax and execution results of SQL query statements; Use the sqlite3 library to establish a connection with the in-memory database; after creating the connection, a connection object is obtained, and subsequent operations will be carried out based on this connection object.

[0037] A cursor object can be created through the cursor() method of the connection object; it should be further explained that the cursor object provides functions such as executing SQL query statements and obtaining query results; Use the execute() method of the cursor object to execute the SQL query statement generated by the SQL generation module; and send the SQL query statement to the memory database for processing; According to the type of SQL query statement, it needs to be further explained that the type of SQL query statement includes query statements and non-query statements, and different processing methods are required; if it is a query statement (such as a SELECT statement), it is necessary to further obtain the query result; if it is a non-query statement (such as INSERT, UPDATE, DELETE, etc.), it is necessary to check whether the execution of the statement is successful.

[0038] For query statements, use the fetchall(), fetchone(), or fetchmany() methods of the cursor object to obtain query verification results. It should be further explained that the fetchall() method will return all query verification results at once, the fetchone() method will return one result, and the fetchmany() method can specify the number of results to be returned. The query verification result includes verification passed and verification abnormal.

[0039] It should be further explained that, in the specific implementation process, the specific process of generating the corresponding execution error handling solution according to the query verification result includes: If the query verification result is verified, the query verification result will be returned to the user, and the user can perform subsequent data analysis and processing based on the query verification result, and upload the corresponding SQL query statement to the cache management module for caching; If the query verification result is a verification exception, it should be further explained that the verification exception includes syntax error, table does not exist, column does not exist, etc.; then a corresponding execution error handling solution is generated; the execution error handling solution includes: 1. Record error information: When a verification exception is captured, print detailed error information, including error type and error description. This information helps developers quickly locate and solve the problem.

[0040] 2. Mark query results invalid: When a validation exception is captured, the query results need to be marked invalid to avoid returning incorrect results to the user; 3. Rollback transaction: When a validation exception is captured, roll back the transaction for the SQL statements involved in the transaction (such as INSERT, UPDATE, DELETE) to ensure the consistency of the database.

[0041] The above embodiments are only used to illustrate the technical method of the present invention rather than to limit it. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical method of the present invention may be modified or replaced by equivalents without departing from the spirit and scope of the technical method of the present invention.

Claims

1. A NL2SQL system based on vector embedding and locality sensitive hashing, characterized in that: include: Data collection module, preprocessing module, table column screening module, SQL generation module, result verification module and cache management module; The data collection module is used to receive natural language query questions input by users; The preprocessing module is used to perform multimodal integration processing on the text description, data type, and data distribution in the database to obtain multimodal data; and perform vector conversion processing to obtain a multimodal data vector table and a multimodal data vector column, and then construct a local sensitive hash index; The table column screening module is used to perform feature processing on natural language query questions to obtain query feature data, and perform vector conversion processing to obtain query feature vectors, thereby obtaining query multimodal data vector tables and query multimodal data vector columns; The SQL generation module is used to generate SQL query statements according to querying the multimodal data vector table and querying the multimodal data vector column; The result verification module is used to verify the syntax and execution results of SQL query statements, obtain the corresponding query verification results, and generate corresponding execution error handling solutions; The cache management module is used to cache the SQL query statement storage and obtain the cached SQL query statement.

2. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 1, characterized in that: The process of feature processing for natural language query questions includes: Obtain natural language query questions input by users; The process of preprocessing the natural language query includes: removing punctuation marks, HTML tags, and special symbols in the natural language query; if the natural language query is in English, converting it to lowercase to unify the uppercase and lowercase format; dividing the natural language query into individual words; and removing stop words; The process of feature processing the preprocessed natural language query problem includes: building a vocabulary; for each query, counting each word in the vocabulary and the number of times it appears in the query, setting an occurrence threshold, and if the number is greater than or equal to the threshold, the corresponding word is recorded as query feature data.

3. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 2, characterized in that: The process of vector conversion of query feature data includes: Get query feature data and obtain the number of occurrences; According to the number of occurrences of the query feature data, the number of occurrences of the query feature data in the query is calculated and divided by the total number of words in the query, which is recorded as the word frequency; Set the corpus; Calculate the inverse document frequency; count the total number of documents in the corpus and the number of documents containing each word. Divide the total number of documents by the number of documents containing the word, then take the logarithm to get the inverse document frequency; According to the word frequency and inverse document frequency, the feature value is obtained as , the eigenvalue for: ;in, is the word frequency, is the inverse document frequency; According to the corresponding query feature data feature value , arranged in the order of the vocabulary to form a vector, recorded as the query feature vector.

4. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 3, characterized in that: The process of multimodal integration of text descriptions, data types, and data distribution of columns in the database includes: Obtain text description information of each column from the database, and obtain text feature data based on text feature extraction technology; clarify the data type of each column; perform statistical analysis on the data of each column to obtain data distribution information; The text feature data, data type and data distribution information of the corresponding column are recorded as multimodal data.

5. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 4, characterized in that: The process of vector conversion processing of multimodal data includes: Obtain multimodal data, calculate the embedding vector of each description based on the text vector embedding model, and associate the embedding vector with the column description and its corresponding table column by storing value pairs, obtain the corresponding multimodal data vector table and multimodal data vector column, and store them in the vector database; The process of preprocessing the stored values ​​includes: Use the n-gram method to divide the string into a set of substrings; calculate the minimum hash of the substring set to generate a discrete value vector of a fixed length, which is the minimum hash of the stored value; establish an association between the minimum hash and the stored value and its table column through key-value pairs; use the locality sensitive hashing (LSH) technology to divide these minimum hashes into different buckets, denoted as hash buckets, each hash bucket stores a set of feature vector indexes, denoted as locality sensitive hash indexes.

6. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 5, characterized in that: The process of obtaining a query multimodal data vector table and a query multimodal data vector column according to the local sensitive hash index and the query feature vector includes: The tables and columns involved in the user query are determined through a two-way filtering mechanism, including embedding vector recall and LSH recall, which are respectively recorded as the first-way filtering and the second-way filtering; The first screening process includes: Based on the text vector embedding model, calculate the embedding vector of the user query question; Calculate the cosine similarity between the embedding vector of the user query and the text description embedding vectors of all columns in the database; Filter out the top-k embedding vectors with the highest cosine similarity, and obtain the corresponding query multimodal data vector table and query multimodal data vector column of the database as the first screening result according to the embedding vectors; The second screening process includes: Extract matching entities from user query questions based on the Large Language Model (LLM); For each extracted matching entity, the minimum hash of the matching entity is calculated based on the minimum hash calculation method; The locality sensitive hashing (LSH) technique is used to divide the minimum hash of the matching entity into different buckets; Extract all minimum hashes in the same LSH bucket as the minimum hash of the matching entity; Calculate the minimum hash of the matching entity and the corresponding Jaccard similarity, and select the top-n minimum hashes with the highest Jaccard similarity; the actual storage value corresponding to the minimum hash and the query multimodal data vector table and query multimodal data vector column where it is located are the screening results of this matching entity; The filtering results of all matching entities are collected together to obtain the second filtering result.

7. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 6, characterized in that: The process of generating SQL query statements includes: Obtain query multimodal data vector table, query multimodal data vector column, first path screening result and second path screening result; The CREATE TABLE statements of the first filtering result and the second filtering result are trimmed, and only the CREATE TABLE statements of the query multimodal data vector table obtained by filtering are retained, and the information of the primary key column, the foreign key column and the filtered column is retained, and the text description and the actual storage value filtered by the second filtering are added to the annotation of the corresponding query multimodal data vector column; The trimmed CREATE TABLE statement and user query question are simultaneously input into the context of the LLM model, and the context capture capability of the LLM model is used to generate the final SQL query statement.

8. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 7, characterized in that: The process of caching SQL query statements includes: Set up the cache database; Perform hash processing on the user's query statement, generate a hash value of the user's query statement based on a hash algorithm, and use the hash value as a cache key; When the SQL generation module generates a new SQL query statement and passes the verification by the result verification module, the hash value of the query statement corresponding to the user and the corresponding SQL query statement are stored in the cache database, and the SQL query statement in the cache database is recorded as a cache SQL query statement; When a user initiates a new query, the hash value of the corresponding query statement is first obtained, and then the SQL query statement corresponding to the hash value is checked in the cache database. If it exists, the cached SQL query statement is directly obtained from the cache database; if it does not exist, the new query initiated by the user is passed to the table column screening module for subsequent processing.

9. The NL2SQL system based on vector embedding and locality sensitive hashing according to claim 8, characterized in that: Verify the syntax and execution results of SQL query statements. The process of obtaining the corresponding query verification results includes: Get the SQL generation module to generate SQL query statements; Set up an in-memory database; use the sqlite3 library to establish a connection with the in-memory database; after creating the connection, obtain a connection object; A cursor object can be created through the cursor() method of the connection object; Use the execute() method of the cursor object to execute the SQL query statement generated by the SQL generation module; and send the SQL query statement to the in-memory database for processing; According to the type of SQL query statement, which includes query statements and non-query statements, different processing methods need to be adopted; if it is a query statement, the query result is obtained; if it is a non-query statement, it is necessary to check whether the statement is executed successfully; For query statements, use the fetchall(), fetchone(), or fetchmany() methods of the cursor object to obtain the query verification results; The query verification result includes verification passed and verification abnormal.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and the computer program can be executed by a processor to implement an NL2SQL system based on vector embedding and locality sensitive hashing as described in any one of claims 1 to 9.

Images