A method and device for generating SQL from text implemented based on prompts of large language models

Abstract

This application relates to the technical field of natural language processing, and specifically relates to a method and device for generating SQL from text implemented based on prompts of a large language model. Among them, the method includes: obtaining text data; determining a data set in a preset set that matches the text data; where the preset set includes data sets corresponding to different sub-domains in each different field; determining a historical example corresponding to the text data in a preset historical example set as the target historical example; performing text parsing on the text data to determine business data and business metadata in the data set that match the text data; combining the business data, business metadata, target historical example, and text data to obtain a prompt; inputting the prompt into a large language model embedded with an OQL module to obtain an Sql statement corresponding to the text data.

Description

Technical Field

[0001] This application relates to the technical field of natural language processing, and specifically relates to a method and device for generating SQL from text implemented based on large language model prompts. Background Art

[0002] The Text-to-SQL system aims to convert natural language queries into structured SQL queries, so that users can query data in a relational database through simple language descriptions without having to understand SQL syntax. Such a system is particularly suitable for end users who need to extract information from a database but do not have programming or SQL knowledge.

[0003] However, in existing solutions, they rely on manually designed rules and templates and are applicable to simple database scenarios. Summary of the Invention

[0004] In view of this, embodiments of this application are committed to providing a method and device for generating SQL from text implemented based on large language model prompts.

[0005] This application provides a method for generating SQL from text implemented based on large language model prompts, including:

[0006] Obtain text data;

[0007] In a preset set, determine the data set that the text data matches; wherein, the preset set includes data sets corresponding to different sub-domains in each different domain;

[0008] In a preset historical example set, determine the historical example corresponding to the text data as the target historical example;

[0009] Perform text parsing on the text data to determine the business data and business metadata in the data set that match the text data;

[0010] Combine the business data, business metadata, target historical example, and text data to obtain a prompt;

[0011] Input the prompt into a large language model embedded with an OQL module to obtain an Sql statement corresponding to the text data.

[0012] In some embodiments, the step of determining the data set that the text data matches in the preset set;

[0013] Obtain a data set selection instruction input by the user; determine the data set that the text data matches based on the data set selection instruction; or,

[0014] Match the content in the preset set to obtain the information associated with the text data, determine the data set to which this information belongs, and select the top data set according to the custom weight order as the data set matched by the text data; or

[0015] Input the text data into a preset data set matching model to obtain the data set matched by the text data; wherein the data set matching model is a deep learning model.

[0016] In some embodiments, the historical example set includes: general examples and domain examples;

[0017] The domain examples include the examples marked as correct in the user question-answering process;

[0018] The general examples are the examples stored in advance.

[0019] In some embodiments, in the preset historical example set, determining the historical example corresponding to the text data includes:

[0020] Determine the similarity between the text data and each example in the historical example set;

[0021] If there is an example with a similarity greater than the preset value, select the example with the highest similarity as the historical example;

[0022] If there is no example with a similarity greater than the preset value, randomly select an example from the preset number of examples with the highest similarity as the historical example.

[0023] In some embodiments, perform text parsing on the text data to determine the business data and business metadata in the data set that match the text data, including:

[0024] Adopt a character matching-based method to determine the business domain concepts in the data set that match the text data;

[0025] Based on the information summary corresponding to the text data and business domain concepts, determine the business data and business metadata in the data set that match the text data by means of similarity matching.

[0026] In some embodiments, in the large language model embedded with the OQL module, the OQL module is used to regard the multi-table structure as a single-table structure;

[0027] The large language model is used to generate an Sql statement for the single-table structure, and then parse the Sql statement for the single-table structure into an Sql statement for the multi-table structure based on a preset SQL parser.

[0028] In some embodiments, it further includes: optimizing the Sql statement.

[0029] This application also provides a text-to-SQL device implemented based on large language model prompts, including:

[0030] An acquisition module, configured to acquire text data;

[0031] A determination module, configured to determine a data set matching the text data in a preset set; wherein, the preset set includes data sets corresponding to different sub-domains in each different domain; in a preset historical example set, determine a historical example corresponding to the text data as a target historical example; perform text parsing on the text data to determine business data and business metadata in the data set that match the text data;

[0032] A combination module, configured to combine the business data, business metadata, target historical example, and text data to obtain a prompt;

[0033] A generation module, configured to input the prompt into a large language model embedded with an OQL module to obtain an Sql statement corresponding to the text data.

[0034] This application also provides an electronic device, including:

[0035] A processor, and a memory for storing programs executable by the processor;

[0036] The processor is configured to implement the text-to-SQL method implemented based on large language model prompts as described above by running the program in the memory.

[0037] This application also provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is run by a processor, the processor is caused to execute the text-to-SQL method implemented based on large language model prompts as described above.

[0038] A method for generating SQL from text based on prompts of a large language model provided by this application first obtains text data; in a preset set, determines the data set that the text data matches; wherein, the preset set includes data sets corresponding to different sub-domains in each different field; in a preset historical example set, determines the historical example corresponding to the text data as the target historical example; performs text parsing on the text data to determine the business data and business metadata in the data set that match the text data; combines the business data, business metadata, target historical example, and text data to obtain a prompt; inputs the prompt into a large language model embedded with an OQL module preset to obtain an Sql statement corresponding to the text data. With such settings, by matching the data set corresponding to the user's text data in the preset set, it can be ensured that the generated SQL statement is only based on the business fields and sub-domains related to the user's question, avoiding interference from irrelevant information, reducing the hallucination problem, and improving the accuracy of generation. Using the historical example as a prompt and inputting it into the large model can significantly improve the reply accuracy for repetitive questions. The historical example contains verified correct SQL statements, and the large model can "copy the answer", thereby reducing the possibility of generating errors and enhancing the stability of generation. Custom OQL module: By abstracting the complex multi-table query logic into a single-table query, the large model only needs to process the single-table logic, greatly reducing the difficulty of generating complex SQL statements. This design enables the large model to focus more on the core semantics of the user's question rather than being distracted by complex structures such as multi-table associations. The prompt generated by combining business data, business metadata, and historical examples provides accurate context information for the large model, avoiding interference from irrelevant information in the prompt and further reducing the difficulty of generating complex SQL. The solution provided by this application significantly improves the accuracy and stability of the Text-to-SQL system based on the large language model through data set matching, the use of historical examples, custom OQL parsing, and accurate context description, while reducing the difficulty of generating complex SQL statements, enhancing the generalization ability and efficiency of the system, and is particularly suitable for complex queries and multi-field scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The above and other objects, features, and advantages of this application will become more obvious by describing the embodiments of this application in more detail with reference to the accompanying drawings. The accompanying drawings are used to provide a further understanding of the embodiments of this application and constitute a part of the specification, and are used to explain this application together with the embodiments of this application, and do not constitute a limitation to this application. In the accompanying drawings, the same reference numerals generally represent the same components or steps.

[0040] Figure 1 It is a schematic flowchart of a method for generating SQL from text based on prompts of a large language model provided by an embodiment of this application.

[0041] Figure 2 It is a structural diagram of a data set provided by an embodiment of the present application.

[0042] Figure 3 It is a flowchart provided by an embodiment of the present application.

[0043] Figure 4 It is a schematic structural diagram of a text-to-SQL device implemented based on large language model prompts provided by an embodiment of the present application.

[0044] Figure 5 It is a schematic structural diagram of an electronic device provided by an embodiment of the present application. Detailed implementation manners

[0045] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0046] Exemplary method

[0047] Figure 1 It is a schematic flowchart of a text-to-SQL method implemented based on large language model prompts provided by an embodiment of the present application. As Figure 1 shown, the method includes the following contents.

[0048] Step S110, obtain text data;

[0049] The purpose of this step is to receive the user's natural language query input as the basis for subsequent processing. The input forms include: text input (such as input in a chat box), voice input (which needs to be transcribed into text first), or other multimodal input forms. The input is uniformly converted into text data in string format (i.e., the user's question) for subsequent modules to process.

[0050] Step S120, in a preset set, determine the data set that the text data matches; wherein, the preset set includes data sets corresponding to different sub-domains in each different field;

[0051] Find the data set related to the user input in the preset set to ensure that the generated SQL statement is based on the correct business field and sub-field. Preset set: It contains data sets corresponding to different fields and sub-fields. Each data set contains metadata such as library table information and field types in a specific field. Matching methods include:

[0052] Manual selection: The user directly selects the corresponding business field or data set.

[0053] Content retrieval: Through keyword matching or semantic analysis, find information related to the user's question from a preset set, and select the most matching dataset according to the weights.

[0054] Large model analysis: Input the user's question and the dataset description into the large model, and the model determines the most matching dataset.

[0055] Step S130, in the preset set of historical examples, determine the historical example corresponding to the text data as the target historical example;

[0056] Find an example similar to the current user's question from the set of historical examples as part of the prompt word to improve the accuracy of generating SQL.

[0057] Set of historical examples: Includes the Q&A records (domain examples) marked as correct by the user in the past and the general examples defaulted by the system.

[0058] Step S140, perform text parsing on the text data to determine the business data and business metadata in the dataset that match the text data;

[0059] Parse the user input to match relevant business data and metadata, providing precise information for generating the prompt word. Text parsing includes: Character matching: Directly match the keywords in the user input with business domain concepts (such as proper nouns, terms, etc.). Similarity matching: Combine the word vector model to calculate the similarity between the user input and the business data and metadata, and find the content with high relevance. Matching result: Determine the business data (such as query conditions, display fields, etc.) and business metadata (such as table structure, field type, etc.) related to the user's question.

[0060] Step S150, obtain the prompt word by combining the business data, business metadata, target historical example, and text data;

[0061] Integrate the business data, business metadata, target historical example, and user input to generate a structured prompt word as the input to the large model. The content of the prompt word includes: Business data and metadata: Provide table information, field descriptions, etc. related to the user's question. Target historical example: Contains verified correct SQL examples to help the large model understand the user's intention. User input: The original question text to ensure that the large model directly generates SQL according to the user's needs.

[0062] The format of the prompt word can be organized into a specific text format according to the requirements of the large model (such as including context description, task rules, etc.).

[0063] Step S160, input the prompt word into the large language model preset with the OQL module to obtain the Sql statement corresponding to the text data.

[0064] Use a large language model embedded with an OQL module to generate accurate SQL statements based on prompts. Function of the OQL module: Abstract the complex multi-table query logic into a single-table query, reducing the difficulty for the large model to generate complex SQL. Dynamically parse the generated single-table SQL into the final multi-table SQL statements through an SQL parser (such as sqlparse, JSqlParser). Input to the large model: Input the prompt into the large model, and the model generates the corresponding OQL statement based on the prompt. SQL generation: The OQL module parses the OQL statement into an SQL statement that conforms to the target database structure, ensuring that the generated SQL statement accurately executes the user's intention.

[0065] This process ensures the accuracy, stability, and efficiency of SQL generation through fine-grained text parsing, historical example matching, and custom OQL parsing. By combining business data, metadata, and historical examples, the system can adapt to a variety of complex query scenarios, while reducing the generation difficulty of the large model, and is particularly suitable for business scenarios that require processing multi-table associations and complex semantics.

[0066] In some embodiments, in the preset set, determining the data set matched by the text data; includes:

[0067] Obtain a data set selection instruction input by the user; determine the data set matched by the text data based on the data set selection instruction; or, match the content in the preset set to obtain information associated with the text data, determine the data set to which this information belongs, and select the most forward data set as the data set matched by the text data according to the custom weight order; or input the text data into a preset data set matching model to obtain the data set matched by the text data; wherein the data set matching model is a deep learning model.

[0068] Specifically, in the process of converting text data into SQL statements, it is first necessary to determine the data set related to the text data. The following are several methods to achieve this goal:

[0069] Method 1: The user manually selects the data set

[0070] Users can directly select the data set related to the input text according to their own needs and understanding of the business. The system will provide a friendly interface, such as a drop-down menu or radio button, allowing users to easily select the corresponding business area or data set. This method is very intuitive and is particularly suitable for users who are very familiar with business data. In this way, users can ensure that the system uses the correct data set to generate SQL statements, avoiding errors that may be caused by automatic matching.

[0071] Method 2: Content retrieval to match the data set

[0072] If the user is unsure which dataset to choose or wishes for the system to handle it automatically, they can rely on the content retrieval function. The system analyzes the text content input by the user, extracts the key information, and locates the content associated with this information in the preset datasets. For example, if a specific business term or keyword is mentioned in the user input, the system will search for the dataset containing these terms or keywords. To ensure accuracy, the system sorts the matching results according to certain weight rules, and the datasets with higher weights will be preferentially selected. This method can effectively reduce user operations and improve the intelligence level of the system, especially suitable for dealing with complex business scenarios.

[0073] Method 3: Deep learning model to match datasets

[0074] For more complex scenarios, a deep learning model can be utilized to automatically determine the relevance between text data and datasets. This model is trained with a large amount of labeled data, can understand the semantics of the text, and accurately identify the matching datasets. The user only needs to input the input text data into this preset dataset matching model, and the model will automatically analyze and output the most relevant datasets. This method is particularly suitable for dealing with large-scale datasets or complex business scenarios and can significantly improve the accuracy and efficiency of matching.

[0075] Through the above three methods, the system can flexibly determine the dataset related to the user input text, thus providing an accurate basis for subsequent SQL generation. Whether it is the user's manual selection, the system's automatic retrieval, or the intelligent matching using a deep learning model, each method has its unique applicable scenarios and advantages, ensuring the efficient operation of the system in different situations.

[0076] In some embodiments, the historical example set includes: general examples and domain examples; the domain examples include the examples marked as correct during the user question-and-answer process; the general examples are the examples pre-stored in advance.

[0077] In the SQL generation system based on large language model prompts, the historical example set is an important component, which helps the system better understand and generate SQL statements. This historical example set mainly consists of two parts: general examples and domain examples.

[0078] The general examples are a set of examples pre-stored in the system. They are built into the system and are used to provide basic reference and fallback support. These examples are usually carefully designed and verified, covering common query scenarios and SQL structures. Their role is similar to a knowledge base, providing a reliable starting point for the system to ensure that the system can still generate reasonable SQL statements when there are not enough domain examples.

[0079] Domain examples are gradually accumulated during the user's use of the system. When the user annotates the SQL statements generated by the system and confirms their correctness, these verified Q&A records are stored as domain examples. Domain examples are highly targeted and reflect the correspondence between common queries and correct SQL statements in a specific business domain. By annotating and storing these examples, the system can continuously learn and adapt to the query patterns in a specific domain, thereby improving the accuracy and relevance of the generated SQL statements.

[0080] General examples and domain examples together constitute the historical example set, which play different roles in the system. General examples provide broad and applicable basic support, while domain examples provide personalized guidance for specific domains. When the user inputs a new query, the system will first try to find similar records from the domain examples. If no suitable match is found, it will fallback to the general examples. This design ensures that the system can maintain high accuracy and stability when processing various queries.

[0081] By combining general examples and domain examples, the system can better understand the user's intention and generate SQL statements that meet the user's needs. This mechanism not only improves the intelligence level of the system but also enhances its adaptability in different business scenarios.

[0082] In some embodiments, determining the historical example corresponding to the text data in the preset historical example set includes:

[0083] Determining the similarity degree between the text data and each example in the historical example set; if there is an example with a similarity degree greater than the preset value, then selecting the example with the highest similarity degree as the historical example; if there is no example with a similarity degree greater than the preset value, then randomly selecting an example from the preset number of examples with the highest similarity degree as the historical example.

[0084] In some embodiments, to determine the historical example corresponding to the text data, the system will perform the following steps:

[0085] 1. Calculate similarity

[0086] The system will first calculate the similarity degree between the text data input by the user and each example in the historical example set. This can be achieved through various techniques, such as using word vector models (such as Word2Vec, GloVe) or deep learning models (such as BERT) to calculate the semantic similarity between texts. The calculation result of the similarity will help the system judge which historical examples are most relevant to the current input.

[0087] 2. Filter examples

[0088] The system checks for examples with a similarity greater than a preset value. This preset value is a threshold used to determine the relevance of examples. If the similarity of a historical example exceeds this threshold, it is considered highly relevant to the current input.

[0089] 3. Select an example

[0090] High - similarity example: If there are examples with a similarity greater than the preset value, the system selects the example with the highest similarity as the target historical example. This example will be used as part of the prompt to help the large - model generate SQL statements more accurately.

[0091] Random selection: If there are no examples with a similarity greater than the preset value, the system does not give up looking for reference examples. Instead, it randomly selects one from the preset number of examples with the highest similarity. This strategy ensures that even in the absence of a highly - matching example, the system can provide some reference, thereby improving the accuracy and stability of SQL generation.

[0092] Purpose and advantages

[0093] The purpose of this method is to improve the accuracy and efficiency of SQL generation. By selecting the example most relevant to the user input, the system can better understand the user's intention and generate SQL statements that meet the user's needs. At the same time, this mechanism also enhances the robustness of the system. Even in the absence of a perfect match, it can provide some reference through random selection, avoiding the situation where the system fails to generate SQL due to the lack of examples.

[0094] Furthermore, in the case of random selection, if the output does not meet the user's needs, it can be regenerated. When regenerating, different examples are used, which can obtain different outputs and avoid repeatedly outputting the same incorrect results.

[0095] In some embodiments, text parsing is performed on the text data to determine the business data and business metadata in the dataset that match the text data, including:

[0096] Adopt a character - matching - based method to determine the business - domain concepts in the dataset that match the text data; based on the information summary corresponding to the text data and business - domain concepts, adopt a similarity - matching method to determine the business data and business metadata in the dataset that match the text data.

[0097] In a text - to - SQL system implemented based on large - language - model prompts, parsing text data to match business data and metadata is a key step. The following is a detailed introduction to this process:

[0098] 1. Character matching: Determine business - domain concepts

[0099] The system will first use a character matching - based approach to determine the matching relationship between the text data input by the user and the business - domain concepts defined in the dataset. This step is like looking up words in a dictionary. The system will directly compare the keywords in the user input with the pre - defined business - domain concepts (such as proper nouns, terms, etc.) in the dataset.

[0100] Function of character matching: This method is simple and direct, and can quickly identify the business concepts clearly mentioned in the user input. For example, if the user input mentions "average balance", the system will directly search for this concept in the dataset and associate it with relevant query methods or data fields.

[0101] Advantages of character matching: Its advantages lie in high efficiency and accuracy. Especially when dealing with clear and standardized business terms, it can quickly find matching items.

[0102] 2. Similarity matching: Determine business data and metadata

[0103] After determining the business - domain concepts, the system will further use the similarity - matching approach to determine the business data and business metadata that match the user input. This step is more like understanding the meaning of a sentence rather than just looking up words.

[0104] Function of similarity matching: By calculating the semantic similarity between the user input and each item in the dataset, the system can identify the data and metadata that are not directly present in the input but are related to the user's intention. For example, if the user input mentions "sales situation in the most recent month", the system may match related fields such as "sales data" and "time range".

[0105] Advantages of similarity matching: This method can handle more complex queries. Especially when the user uses non - standard expressions or implicit semantics, the system can still understand the user's needs and find relevant data.

[0106] In some embodiments, the two methods can be combined; character matching and similarity matching complement each other. Character matching provides direct and clear matching results, while similarity matching supplements semantic understanding and expansion. By first performing character matching to determine business - domain concepts and then performing similarity matching based on these concepts, the system can understand the user input more comprehensively and accurately and find the related business data and metadata.

[0107] This combined method ensures that when the system processes various queries, it can not only quickly respond to clear requests but also deeply understand complex intentions, thereby improving the accuracy and efficiency of SQL generation.

[0108] In the large language model embedded with the OQL module, the OQL module is used to regard a multi-table structure as a single-table structure; the large language model is used to generate an Sql statement for the single-table structure, and then parse the Sql statement for the single-table structure into an Sql statement for the multi-table structure based on a preset SQL parser.

[0109] In a text-to-SQL system implemented based on large language model prompts, the embedded OQL (Object Query Language) module plays a crucial role. The following is a detailed introduction to the OQL module and its collaborative work with the large language model:

[0110] The core role of the OQL module is to abstract a complex multi-table structure into a single-table structure. This abstraction enables the large language model to focus on single-table logic when generating SQL statements, thereby significantly reducing the difficulty of generating complex SQL statements.

[0111] Specifically, when the input text data of the user involves multi-table queries, the OQL module will abstract these multi-table structures into a logically single-table structure. This is similar to creating a virtual "union table" that contains all relevant fields and data. This abstraction method enables the large language model to focus on the core semantics of the user's question without having to handle complex multi-table association logic.

[0112] The large language model generates an SQL statement for this abstract single table based on the prompts (including business data, business metadata, historical examples, and user input). Due to the simplification of the single-table structure, the model can more accurately understand the user's intention and generate the corresponding SQL statement.

[0113] The generated single-table SQL statement is then input into a preset SQL parser. The SQL parser will identify the fields and table structures in the single-table SQL and map these fields to the actual multi-table structure according to the preset database table association rules.

[0114] In this way, the finally generated SQL statement can correctly execute multi-table queries and meet the user's needs.

[0115] With such settings, by abstracting the multi-table structure into a single-table structure, the OQL module greatly reduces the complexity of the large language model in generating SQL statements, improves the accuracy and stability of the generation. The OQL module enables the system to adapt to various complex business scenarios, especially those involving multi-table associations and complex queries. By simplifying the generation logic, the system can respond to user requests faster and improve the overall efficiency.

[0116] The OQL module abstracts the multi-table structure into a single-table structure, enabling the large language model to focus on generating single-table SQL statements, while the SQL parser is responsible for converting these statements into actual multi-table SQL statements. This design not only improves the accuracy and efficiency of SQL generation but also enhances the system's adaptability in complex business scenarios.

[0117] In some embodiments, it further includes: optimizing the Sql statement.

[0118] Since the Sql statement obtained in the above solution is transformed from the Sql statement obtained based on the OQL module, there may be some unreasonable or bloated and redundant parts. Based on this, the Sql statement is optimized as follows:

[0119] After generating the SQL statement, the system will perform a preliminary analysis on it to identify possible performance bottlenecks or unnecessary parts. This step is similar to proofreading a first draft to find areas for improvement.

[0120] The optimization methods include:

[0121] Table join optimization: Check the table join conditions in the SQL statement to ensure that the join conditions are efficient and necessary. For example, if there are redundant join conditions, the system will remove them to reduce unnecessary calculations.

[0122] Subquery optimization: Convert complex subqueries into join queries or other more efficient expressions to improve execution efficiency.

[0123] Field selection optimization: Ensure that only necessary fields are selected in the SQL statement, avoiding using SELECT *, thereby reducing the data transfer volume and processing time.

[0124] Sorting and grouping optimization: Optimize the ORDER BY and GROUP BY clauses to ensure that they are based on index fields to improve the efficiency of sorting and grouping.

[0125] Index optimization: Based on the query conditions and table structure, recommend or automatically add indexes to accelerate queries.

[0126] Execution plan optimization: Use a query optimizer (such as Apache Calcite) to analyze the execution plan of the SQL statement, find areas for improvement, and generate a more efficient execution plan.

[0127] Syntax optimization: Simplify complex SQL syntax, for example, convert multiple OR conditions into IN conditions to improve readability and execution efficiency.

[0128] Constant expression optimization: Calculate constant expressions in advance to avoid repeated calculations during query execution.

[0129] Subquery optimization: Convert subqueries into join queries or other more efficient expressions.

[0130] Nested query optimization: Expand nested queries into multiple simple queries to improve execution efficiency.

[0131] After optimization, the execution efficiency of the SQL statement will be significantly improved while maintaining its functionality unchanged. This is similar to refining a first draft to make it more concise and efficient.

[0132] The system can also utilize existing SQL parsing and optimization tools, such as Apache Calcite, JSqlParser, etc., to automatically execute the optimization steps. These tools provide powerful functions that can analyze and optimize SQL statements to ensure optimal performance during actual execution.

[0133] Optimization can improve query efficiency: The optimized SQL statement can execute faster, reducing query time. By removing redundant operations and optimizing the execution plan, the consumption of CPU, memory, and disk I / O is reduced. Faster query response time enhances the overall user experience, especially when dealing with large amounts of data.

[0134] By optimizing the SQL statement, the system can ensure that the generated SQL is not only semantically correct but also optimal in terms of execution efficiency. This step is particularly important for handling complex queries and large-scale datasets, and can significantly improve the system's performance and response speed.

[0135] The following is an explanation of the solution provided by this application in combination with the above-mentioned various preferred embodiments:

[0136] The present invention relates to the Text-to-SQL direction in the field of large model technology. The purpose of this system is to significantly improve the accuracy and stability of converting natural language queries into SQL statements by integrating a historical Q&A example module, a dataset module, and a custom OQL (Object Query Language) parsing mechanism. It is applicable to various human language and structured data interaction scenarios, such as intelligent BI, etc.

[0137] Compared with traditional Text-to-SQL solutions, our system adopts innovative methods to overcome many limitations of the prior art: inability to generate complex semantic SQL, low inference efficiency, cost waste caused by invalid tokens, unstable SQL generation, etc.

[0138] First, divide the levels according to the business domain. Each domain contains multiple information sets (i.e., data and). A single information set consists of the data sources required for a single conversation. The ultimate goal of the system is to match the user's question to the most relevant business information as much as possible and put it into the large model prompt to generate the corresponding database query statement. This can not only eliminate the interference of useless information on the large model and reduce the generation of hallucinations, but also minimize the length of the prompt to avoid exceeding the context length of the large model.

[0139] Example management collects the user's past historical questions and answers, allows the user to label the questions and answers with accurate system responses, and provides them to the large model in the form of prompts for reference in future questions and answers to increase the accuracy of system responses.

[0140] In implementation, a vector library can be used for the recall method of similarity retrieval. Generate a similarity vector for the user's question and associate it with the data record of the correct answer. When the user asks a question, retrieve high-weight examples through similarity and put them into the large model prompt.

[0141] Strategically, it can be split into two types: the same question and different questions. When the user's question is the same as the historical example question (with extremely high similarity), this piece of data can be directly used as a prompt example. Otherwise, sort by weight to obtain the top few pieces of data and randomly select a part of them as prompt examples.

[0142] In design, it can be divided into two categories:

[0143] Domain examples: Examples marked as correct during the user question and answer process.

[0144] General examples: Refer to the examples that come with the system by default for backup.

[0145] Refer to Figure 2 , the data set consists of the data sources required for a single conversation, that is, it contains all the information on which the sql statement corresponding to the user's single question depends. The content of the data set needs to be generated into the system database in a configured form. Some of the data set information listed below is not complete, and it needs to be supplemented according to different business needs.

[0146] Business domain concepts: Different business domains have their own unique concepts, including but not limited to proper nouns, terms, customized data algorithms, etc.

[0147] Business metadata: including (environment information, library table information, etc.)

[0148] Environment information: Database type, version, connection address, etc.

[0149] Library Table Information: The database table structures involved in a single session (information such as field codes, field types, field explanation descriptions, etc.), including the association relationships between tables (join relationships between tables, join fields, etc.). Classify and store fields. The main field refers to the core query field (for example, when querying the average balance, the balance field is the main field). At the same time, establish the possible relationships between other fields and the main field. For example, fields to be displayed (associated select fields), fields for conditional queries (associated where fields), to ensure that only field information related to the local problem is matched during a single session query. The matching strategy can adopt recalling all fields associated with the main field and fields matched by similarity.

[0150] Business Data: Includes the real data stored in the business database for querying, dictionary comparison fields (in many cases, there is a conversion relationship for the query fields when users perform SQL queries (for example, the stored values for disabled and enabled in the database may be 0 and 1), etc.).

[0151] Based on the natural recursive nature of the SQL syntax tree, customize the intermediate query language oql. That is, when calling the large model to generate a database query statement, regard the entire dataset as a single database table. In this way, the large model only needs to generate SQL according to the logic of a single table, greatly reducing the difficulty of SQL generation and enhancing the accuracy of the large model's response. The single-table query SQL statement returned by the large model is dynamically parsed into a real SQL statement through the database table association relationship configured in the dataset. Technically, a SQL parser can be used to parse it into a SQL syntax tree. There are many ready-made SQL parsing libraries available for this purpose, such as sqlparse (Python), JSqlParser (Java), etc.

[0152] Refer to Figure 3 , the solution provided by this application includes:

[0153] Receive text input: It can be designed to support a unified multimodal entry, such as chat text input, voice input, etc. Finally, it is converted into a string.

[0154] Business domain, dataset matching: Match the corresponding dataset according to the string content of the user's question. Currently, the common methods are:

[0155] Manual selection: Allow users to manually select to answer questions under the corresponding business domain or dataset

[0156] Content retrieval: Match the information related to the user's question from the content in the full dataset, find out the datasets to which these information belong, and select the datasets in the order of the custom weights, such as according to the number of matching information, etc.

[0157] Large model analysis: By presenting the user's question, business domain description, and dataset description in the form of prompts to the large model to decide which dataset to use.

[0158] Historical examples: Designed and implemented according to the example management method.

[0159] Text parsing: Query according to the dependency order between data:

[0160] First, match the user's question with business domain concepts. The recall method uses character matching. The concept description can be an explanation of the concept (e.g., Average balance: The arithmetic mean of the daily balances of an account within a specific time period.), or the method of querying the corresponding data for this concept (e.g., Average balance: Calculate the average value in the form of SUM() / COUNT(DISTINCT date field) with the 'date' field as the unit). Tests have found that the latter has a higher accuracy rate.

[0161] Summarize the information recalled from the user's question and business domain concepts, and use the summary result to recall information about business data (query conditions, etc.) and business metadata (queried database tables, fields, etc.). The recall method uses the association relationship of the data itself in the database plus the word vector model for similarity matching.

[0162] Prompt assembly: When using large models from different manufacturers, the generation logic may be slightly different. You can define prompts specific to different large models in this module to ensure the stability of the large model output.

[0163] Example: (You are a database administrator proficient in SQL language, familiar with various mainstream database management systems. The task is to convert the #user's question into an accurate SQL query statement according to different database types.

[0164] Here are some examples for reference: {{Historical Q&A examples}};

[0165] The task rules and precautions are as follows:

[0166] 1. Only the values specified in #schema can be included in the generated sql;

[0167] 2. #sideInfo contains business logic-related concepts in the question. Please combine the question and these concepts for sql conversion;

[0168] 5. When performing calculations between parameters in sql, the IFNULL function must be used for judgment to avoid calculation errors;

[0169] # Information about this task is as follows:

[0170] #schema: {{Metadata}};

[0171] #sideInfo: {data};

[0172] #User's question: {question})

[0173] Large model call: When encoding this module, the call context can be abstracted and unified to adapt to different large models.

[0174] OQL syntax parsing:

[0175] Designed and implemented according to the custom OQL parsing narrative. Through this module, the SQL differences between different databases can be masked in hard-coded form, or parsed into different dialect versions of SQL statements according to different database types in dialect form.

[0176] Sql optimization (non-essential step): The sql generated after the oql generated by the large model is parsed by the oql syntax can be optimized by this module to ensure execution efficiency. In implementation, parsing libraries such as Apache Calcite (Java) can be used to generate a more efficient execution plan through its optimizer.

[0177] Execute SQL and return results: Field translation: Perform display replacement according to the enumerated values of the data, or develop graphical displays on the page, etc.

[0178] In the solution provided by this application, adding a historical Q&A example module greatly increases the accuracy of answering repeated questions: The traditional method is to hard-code or organize some examples into the prompt words to facilitate the large model to generate sql more accurately. However, in real usage scenarios, there may be significant differences between these prompt words and the user's questions, and the accuracy of the large model generating sql cannot be guaranteed. Based on the marking of the user's historical Q&A, the correct Q&A responses are recorded in the vector library. When the user asks a similar question again, hitting highly similar questions and sql from the vector library allows the large model to complete sql generation in an approximate "copy the answer" form, greatly increasing the accuracy and stability.

[0179] The dataset module provides the most accurate context description for the large model to the greatest extent: The traditional prompt-word-based method is to put the table DDL into the prompt words. However, the relevant table fields involved in a user's Q&A may only account for a small part of the overall fields, and the relevance cannot be distinguished. Putting all fields into the prompt words contains a large number of irrelevant fields, which will cause problems such as large model hallucinations and result in extremely poor effects. Moreover, when many Q&A contain professional terms, they cannot form a correlation with the table field content, making the prompt words lack a complete business context and thus causing the large model to be unable to generate the correct sql. Based on the design of the dataset, information irrelevant to the user's Q&A can be eliminated by distinguishing the table field types. Based on the design of business domain concepts, the incorrect output caused by the large model's lack of understanding of professional terms during parsing can be compensated for.

[0180] OQL significantly reduces the generation complexity of large models: In the traditional method, using prompt words to directly generate SQL by large models has very low accuracy in complex business scenarios and table structures, and cannot be solved simply by enhancing the capabilities of the large models themselves. The custom OQL parsing method allows large models to process data with a single-table structure, shielding the complexity brought by multi-table structures, which can greatly reduce the generation difficulty and enhance the accuracy and stability.

[0181] In some embodiments, the current information retrieval methods on the market are as follows:

[0182] Retrieval based on keyword matching: (1) Simple string matching: Directly search for content containing these keywords in the database or document set according to the keywords input by the user. This method is simple and direct, but the precision and recall rate may be limited. (2) Inverted index: Accelerate the keyword search process by constructing an inverted index. Each word has a list recording all the positions where it appears, which greatly improves the query speed.

[0183] Retrieval based on semantic understanding: Natural language processing (NLP) technology: Use NLP technology to understand the query intention of the user and convert it into more accurate search conditions. For example, use methods such as named entity recognition (NER) and syntactic analysis to parse the query content. Word vector model: Such as Word2Vec, GloVe, etc., convert the text into vector form, and then calculate the similarity between vectors for retrieval. This method can capture the semantic relationship between words, rather than just surface lexical matching. Pre-trained models such as BERT: Use deep learning models such as BERT to better understand the semantic information at the sentence level, thereby improving the accuracy and relevance of retrieval.

[0184] Retrieval based on knowledge graph: Knowledge graph construction: Build a knowledge graph in the domain, including entities and their relationships. When the user makes a query, relevant entities and information can be found through the links in the graph. Path query: Execute complex path queries on the knowledge graph to discover implicit relationship chains, so as to retrieve those indirectly related data points.

[0185] In practical applications, a better retrieval method can be selected according to different business scenarios. The data retrieval in this application includes two modules (example management and dataset management). Among them, example management uses the word vector model to perform similarity retrieval to improve the generalization ability of question and answer parsing. In dataset management, due to the characteristics of proper nouns in business domain concepts, it is often difficult to retrieve well through semantic understanding. Therefore, it is recommended to use the simple string matching method, and business data is implemented in the form of using the word vector model to perform similarity retrieval.

[0186] Exemplary device

[0187] The device embodiments of the present application can be used to execute the method embodiments of the present application. For details not disclosed in the device embodiments of the present application, please refer to the method embodiments of the present application.

[0188] Figure 4 The block diagram of the text-to-SQL device implemented based on the prompts of the large language model provided by an embodiment of the present application is shown. As Figure 4 shown, the device includes:

[0189] An acquisition module 41, configured to acquire text data;

[0190] A determination module 42, configured to determine the data set matched by the text data in a preset set; wherein, the preset set includes data sets corresponding to different sub-domains in each different domain; determine the historical example corresponding to the text data in a preset historical example set as the target historical example; perform text parsing on the text data to determine the business data and business metadata in the data set that match the text data;

[0191] A combination module 43, configured to combine the business data, business metadata, target historical example, and text data to obtain a prompt;

[0192] A generation module 44, configured to input the prompt into a large language model preset with an OQL module to obtain an Sql statement corresponding to the text data.

[0193] Exemplary electronic device

[0194] Next, with reference to Figure 5 an electronic device according to an embodiment of the present application will be described. Figure 5 The block diagram of the electronic device according to an embodiment of the present application is illustrated.

[0195] As Figure 5 shown, the electronic device 500 includes one or more processors 510 and a memory 520.

[0196] The processor 510 may be a central processing unit (CPU) or other forms of processing units with data processing capabilities and / or instruction execution capabilities, and may control other components in the electronic device 500 to perform desired functions.

[0197] The memory 520 may include one or more computer program products, and the computer program products may include various forms of computer-readable storage media, such as volatile memory and / or non-volatile memory. The volatile memory may include, for example, random access memory (RAM) and / or cache memory, etc. The non-volatile memory may include, for example, read-only memory (ROM), hard disk, flash memory, etc. One or more computer program instructions may be stored on the computer-readable storage media, and the processor 510 may run the program instructions to implement the text-to-SQL method implemented based on the large language model prompts in the various embodiments of the present application described above and / or other desired functions. Various contents such as category correspondence relationships may also be stored in the computer-readable storage media.

[0198] In one example, the electronic device 500 may further include: an input device 550 and an output device 540, and these components are interconnected through a bus system and / or other forms of connection mechanisms (not shown).

[0199] In addition, the input device 530 may further include, for example, a keyboard, a mouse, an interface, etc. The output device 540 may output various information to the outside, including analysis results, etc. The output device 540 may include, for example, a display, a speaker, a printer, and a communication network and its connected remote output devices, etc.

[0200] Of course, for simplicity, Figure 5 only some of the components related to the present application in the electronic device are shown, and components such as buses, input / output interfaces, etc. are omitted. In addition, according to specific application scenarios, the electronic device may further include any other appropriate components.

[0201] Exemplary computer program product and computer-readable storage medium

[0202] In addition to the above methods and devices, an embodiment of the present application may also be a computer program product, which includes computer program instructions that, when run by a processor, cause the processor to execute the steps in the text-to-SQL method implemented based on the large language model prompts in the "Exemplary Method" section of this specification according to various embodiments of the present application.

[0203] The computer program product may be written in any combination of one or more programming languages for executing the program code of the operations of the embodiments of the present application. The programming languages include object-oriented programming languages such as Java, C++, etc., and also include conventional procedural programming languages such as the "C" language or similar programming languages. The program code may be executed entirely on the user computing device, partially on the user device, executed as a stand-alone software package, partially on the user computing device and partially on a remote computing device, or entirely on a remote computing device or server.

[0204] In addition, an embodiment of the present application may also be a computer-readable storage medium having computer program instructions stored thereon. When the computer program instructions are run by a processor, the processor is caused to execute the steps in the method of generating SQL from text based on large language model prompts according to various embodiments of the present application described in the above "Exemplary Method" section of this specification.

[0205] The computer-readable storage medium may employ any combination of one or more readable media. The readable media may be a readable signal medium or a readable storage medium. The readable storage medium may, for example, include but is not limited to an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the above. More specific examples (a non-exhaustive list) of the readable storage medium include: an electrical connection having one or more wires, a portable disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above.

[0206] The foregoing description has been presented for purposes of illustration and description. Furthermore, this description is not intended to limit the embodiments of the present application to the form disclosed herein. Although several example aspects and embodiments have been discussed above, those skilled in the art will recognize some of its variations, modifications, alterations, additions, and sub-combinations.

Claims

1. A method for generating SQL from text implemented based on prompts of large language models, characterized in that, Including: Obtain text data; In a preset set, determine the data set that the text data matches; wherein, the preset set includes data sets corresponding to different sub - fields in each different field; In a preset historical example set, determine the historical example corresponding to the text data as the target historical example; Perform text parsing on the text data to determine the business data and business metadata in the data set that match the text data; Combine the business data, business metadata, target historical example, and text data to obtain a prompt word; Input the prompt word into a large - language model embedded with an OQL module to obtain an Sql statement corresponding to the text data; Performing text parsing on the text data to determine the business data and business metadata in the data set that match the text data includes: Adopt a character - matching - based method to determine the business - field concept in the data set that matches the text data; Based on the information summary corresponding to the text data and the business - field concept, adopt a similarity - matching - based method to determine the business data and business metadata in the data set that match the text data.

2. The SQL generation method based on large language model prompts according to claim 1, wherein The step of determining the data set that the text data matches in the preset set includes: Obtain a data - set selection instruction input by the user; determine the data set that the text data matches based on the data - set selection instruction; or, Match the content in the preset set to find information associated with the text data, determine the data sets to which this information belongs, and select the top - ranked data set according to a custom weight order as the data set that the text data matches; or Input the text data into a preset data - set matching model to obtain the data set that the text data matches; wherein the data - set matching model is a deep - learning model.

3. The SQL generation method based on large language model prompts according to claim 1, wherein, The historical example set includes: general examples and domain examples; The domain examples include examples marked as correct during the user's question - answering process; The general examples are examples stored in advance.

4. The SQL generation method based on large language model prompts according to claim 3, characterized in that In the preset historical example set, determining the historical example corresponding to the text data includes: Determine the similarity degree between the text data and each example in the historical example set; If there is an example with a similarity degree greater than the preset value, select the example with the highest similarity degree as the historical example; If there is no example with a similarity degree greater than the preset value, randomly select an example from the preset number of examples with the highest similarity degree as the historical example.

5. The SQL generation method based on large language model prompts according to claim 1, wherein In the large - language model embedded with the OQL module, the OQL module is used to regard the multi - table structure as a single - table structure; The large - language model is used to generate an Sql statement for the single - table structure, and then based on a preset SQL parser, parse the Sql statement for the single - table structure into an Sql statement for the multi - table structure.

6. The SQL generation method based on large language model prompts according to claim 1, characterized in that, It also includes: Optimize the Sql statement.

7. A text-to-SQL device implemented based on prompts of a large language model, characterized in that, Including: An acquisition module for obtaining text data; A determination module, configured to determine a data set that matches the text data in a preset set; wherein the preset set includes data sets corresponding to different sub - fields in each different field; determine a historical example corresponding to the text data in a preset historical example set as a target historical example; perform text parsing on the text data to determine business data and business metadata in the data set that match the text data; An association module, configured to associate the business data, business metadata, target historical example, and text data to obtain a prompt; A generation module, configured to input the prompt into a large - language model embedded with an OQL module to obtain an Sql statement corresponding to the text data; Performing text parsing on the text data to determine business data and business metadata in the data set that match the text data includes: Adopting a character - matching - based method to determine business - field concepts in the data set that match the text data; Based on the information summary corresponding to the text data and business - field concepts, adopting a similarity - matching - based method to determine business data and business metadata in the data set that match the text data.

8. An electronic device, characterized in that, Including: A processor and a memory for storing executable programs of the processor; The processor is configured to implement the text - to - SQL method based on large - language - model prompts as described in any one of claims 1 to 6 by running the program in the memory; 9. A computer-readable storage medium, characterized in that, A computer program is stored on the computer - readable storage medium, and when the computer program is run by the processor, the processor is caused to execute the text - to - SQL method based on large - language - model prompts as described in any one of claims 1 to 6.

Images