Method for generating sql statement and electronic device

Abstract

This specification provides a method for generating SQL statements, comprising: obtaining user-inputted query information based on natural language; determining a set of candidate data tables that match the query semantics of the query information from data tables contained in an SQL database, and querying a set of related data tables that are associated with each candidate data table in the candidate data table set, as well as first metadata describing the association relationship, from a metadata database; wherein the metadata database stores a set of related data tables that are associated with each successfully executed historical SQL statement, as well as first metadata describing the association relationship; and inputting the query information, the set of related data tables, and the first metadata as context into an SQL statement generation model, so that the SQL statement generation model generates a target SQL statement that matches the query semantics of the query information based on the context.

Description

Technical Field

[0001] This specification relates to one or more embodiments in the field of databases, and more particularly to a method and electronic device for generating SQL statements. Background Technology

[0002] As enterprise data continues to grow, enabling business personnel without technical backgrounds to easily and accurately retrieve information from databases has become crucial for improving operational and decision-making efficiency. For example, in data-intensive business areas such as finance and credit reporting, business personnel (such as risk analysts) often need to extract information from complex business databases to support analytical decisions. However, these personnel typically lack the ability to write professional SQL statements.

[0003] For this reason, NL2SQL (Natural Language to SQL) technology was developed. Based on NL2SQL technology, users can describe their query requirements in everyday language, and the system will automatically generate professional SQL statements for the user.

[0004] However, existing NL2SQL technologies struggle to cope with the flexibility of business semantics, the evolution of database structures, and the complexity of data relationships in the real world when dealing with complex and dynamic data environments at the enterprise level. Summary of the Invention

[0005] In view of the above, one or more embodiments of this specification provide the following technical solutions: According to a first aspect of one or more embodiments of this specification, a method for generating SQL statements is provided, comprising: Obtain query information based on natural language input from the user; The SQL database contains a set of candidate data tables that match the query semantics of the query information. The database also contains a set of associated data tables that are related to each candidate data table in the set of candidate data tables, as well as first metadata describing the relationship. The database stores a set of associated data tables that are related to each candidate data table in the set of candidate data tables, as well as first metadata describing the relationship. The query information, the set of related data tables, and the first metadata are input as context into the SQL statement generation model, so that the SQL statement generation model can generate a target SQL statement that matches the query semantics of the query information based on the context.

[0006] Optionally, a set of candidate tables that match the query semantics of the query information can be queried from the data tables contained in the SQL database, including: The query information is converted into a query vector; Calculate the vector similarity between the query vector and the semantic vector stored in the vector database; wherein, the vector database stores semantic vectors generated based on the semantic description information of all data tables contained in the SQL database; the semantic description information is used to describe the business semantics of the data tables; The data table corresponding to at least one semantic vector with the highest similarity to the query vector stored in the vector database is determined as a set of candidate data tables that match the query semantics of the query information.

[0007] Optionally, the method further includes: Periodically retrieve second metadata used to describe the various data tables contained in the SQL database; The obtained second metadata is input into the LLM, so that the LLM can generate semantic description information corresponding to each data table based on the second metadata; The semantic description information is converted into a semantic vector, and the semantic vector is saved to the vector database.

[0008] Optionally, the method further includes: If the second metadata used to describe any data table contained in the SQL database is updated, the updated second metadata is re-entered into the LLM so that the LLM can regenerate the semantic description information corresponding to the data table based on the updated second metadata; The semantic description information is converted into a semantic vector, and the semantic vector is saved to the vector database to update the semantic vectors corresponding to the data table stored in the vector database.

[0009] Optionally, the semantic description information is converted into a semantic vector, and the semantic vector is saved to the vector database, including: The semantic description information and the second metadata are merged to obtain the merged target data. The target data is converted into semantic vectors, and the semantic vectors are saved to the vector database.

[0010] Optionally, the second metadata includes table annotation data related to each of the SQL data tables; and column annotation data related to the data columns contained in each of the SQL data tables.

[0011] Optionally, the method further includes: Periodically retrieve successfully executed historical SQL statements; The historical SQL statements are parsed, and based on the parsing results, a set of related data tables with relationships are extracted from the historical SQL statements, as well as first metadata used to describe the relationships; The extracted set of related data tables and the first metadata are saved to the metadata database.

[0012] Optionally, the first metadata includes an association column corresponding to the association relationship; and an association semantics corresponding to the association relationship.

[0013] Optionally, the query information, the set of related data tables, and the first metadata are input as context into the SQL statement generation model, so that the SQL statement generation model generates a target SQL statement that matches the query semantics of the query information based on the context, including: A candidate table relationship graph is constructed based on the relationships between each candidate data table in the candidate data table set and each associated data table in the associated data table set; wherein, the nodes in the candidate table relationship graph represent the candidate data table or the associated data table; the edges in the candidate table relationship graph represent the relationships between the candidate data table and the associated data table; the attribute information of the edges in the candidate table relationship graph includes the associated columns corresponding to the relationships and the associated semantics corresponding to the relationships; The query information and the candidate table relationship graph are used as context input to the SQL statement generation model, so that the SQL statement generation model can generate a target SQL statement that matches the query semantics of the query information based on the context.

[0014] Optionally, the association semantics corresponding to the association relationship include the association type corresponding to the association relationship.

[0015] According to a second aspect of one or more embodiments of this specification, an electronic device is also provided, comprising: a processor; a memory for storing processor-executable instructions; wherein the processor performs the executable instructions to implement the steps of the method as described in any of the first aspects above.

[0016] According to a third aspect of one or more embodiments of this specification, a computer program product is also provided, comprising a computer program / instructions that, when executed by a processor, implement the steps of the method as described in any of the first aspects above.

[0017] In the above embodiments, by providing the SQL statement generation model with the context of the related data tables and metadata describing the relationships dynamically extracted and maintained from historically successfully executed SQL statements, the SQL statement generation model can effectively perceive the dynamic changes in the SQL database structure and adapt to these changes in real time. Furthermore, it avoids the problem of inaccurate semantics and business logic in the generated SQL statements due to the SQL statement generation model's lack of understanding of the relationships between multiple data tables involved in data-intensive business domains. This significantly improves the semantic accuracy and correctness of the SQL statements generated by the SQL statement generation model when processing natural language queries involving relationships between multiple data tables. Attached Figure Description

[0018] Figure 1 This is a schematic diagram of the architecture of an anomaly detection system provided in an exemplary embodiment.

[0019] Figure 2 This is a flowchart of a method for generating SQL statements provided in an exemplary embodiment.

[0020] Figure 3 This is an exemplary embodiment of a flowchart for generating SQL statements for a user.

[0021] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an exemplary embodiment.

[0022] Figure 5 This is a block diagram of an exemplary embodiment of a device for generating SQL statements. Detailed Implementation

[0023] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0024] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties. The collection, use and processing of related data shall comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals shall be provided for users to choose to authorize or refuse.

[0025] Currently, mainstream NL2SQL technical solutions mainly adopt the following methods: One approach is based on keyword matching and static rule mapping.

[0026] The core of this type of approach is to build a large, predefined "dictionary" or "mapping table". This dictionary statically associates natural language terms that business users might use (e.g., "customer", "sales", "date") with specific technical objects in the target database (e.g., table names, column names).

[0027] Its workflow typically includes: segmenting the input natural language and extracting keywords; then searching and matching in a dictionary; and finally, assembling the matched technical objects into a framework of SQL statements (such as SELECT [column] FROM [table] WHERE [condition]) according to a set of pre-compiled syntax rules.

[0028] This method is relatively simple to implement and has low hardware resource requirements. However, because the richness and flexibility of natural language far exceed the semantic range that any static dictionary can cover, this method cannot effectively handle synonyms, business slang, polysemy, or complex phrase structures that depend on context. Any changes to the database structure (such as adding columns or modifying table names) must be manually synchronized with the dictionary and rule base, resulting in high maintenance costs and a high risk of errors due to untimely updates.

[0029] Secondly, the method based on predefined SQL templates.

[0030] These methods solidify common, repetitive query requirements into a set of parameterized SQL statement templates.

[0031] For example, for analysis needs with a fixed pattern, such as "querying the sales revenue of [products] in [a certain region] within a [time range]", a pre-written SQL template containing placeholders is prepared. Users select or input specific parameter values ​​(such as time range, region, product name) through drop-down menus, forms, etc. on the front-end interface. The system then fills these parameter values ​​into the corresponding placeholders in the template and generates an executable SQL statement.

[0032] This method is highly efficient in scenarios such as report generation and fixed-format data extraction, and can generate SQL statements with relatively accurate syntax and semantics. However, the application scope of this method is strictly limited to a preset set of templates. The system is completely powerless to handle complex query requirements that the template library cannot cover.

[0033] Third, an end-to-end generation method based on a general deep learning model.

[0034] This is currently a cutting-edge research direction in both academia and industry. These methods employ advanced deep learning models (such as sequence-to-sequence models based on the Transformer architecture) and are trained on a large-scale paired dataset consisting of "natural language questions - corresponding SQL statements." The model's learning objective is to directly establish a mapping relationship from natural language sequences to SQL syntax sequences, without requiring manually defined rules or templates. These models demonstrate strong semantic understanding capabilities and a certain degree of generalization, capable of handling relatively complex and diverse sentence structures.

[0035] However, in practical applications, the performance of such deep learning models is highly dependent on the scale and quality of the training data, and collecting and labeling a high-quality training ensemble that perfectly matches a specific business database is extremely costly. Secondly, as a black-box model, these models lack awareness of the unique structure of the database. When the database structure is updated, the model cannot adapt automatically, usually requiring data to be collected and retrained, making it difficult to meet the stability and maintainability requirements of enterprise-level applications. Finally, when handling complex queries that require precise joins between multiple tables, these models, due to their lack of understanding of the relationships between the underlying tables, are prone to errors in table selection and join conditions, resulting in poor accuracy in the query semantics and business logic of the generated SQL statements.

[0036] It is evident that keyword- and rule-based methods cannot effectively understand the rich semantics of business terms and their flexible correspondence with underlying technical metadata (such as table names and column names). They struggle to handle polysemy or business slang, leading to inaccurate query intent identification. Furthermore, both template-based and general model-based methods are ill-suited to adapting to dynamic changes in database structures in production environments. Once the underlying data model is updated, the original mapping rules or trained models may become invalid, requiring significant manpower for synchronous maintenance and exhibiting poor scalability.

[0037] Moreover, for complex queries involving multiple table joins that are common in data-intensive business areas such as finance and credit reporting, existing solutions lack an effective mechanism for perceiving the relationships between tables. In particular, they cannot automatically discover and utilize implicit relationships that are not explicitly defined in the database but are frequently used in real business queries, resulting in low accuracy when generating SQL statements for executing complex join queries.

[0038] Based on this, this specification proposes an NL2SQL solution that can effectively detect changes in the database structure and deeply understand the relationships between multiple data tables involved in complex queries.

[0039] In this solution, the first step is to obtain the user's query information based on natural language.

[0040] Secondly, after obtaining the user's query information based on natural language, a set of candidate data tables that match the semantics of the query can be determined from the data tables contained in the target SQL database. Then, from a pre-built and maintained metadata database, other data tables (i.e., the set of related data tables) that are associated with these candidate data tables are queried, and the first metadata describing these relationships (such as the specific columns associated, the relationship type, etc.) is obtained. Note that the aforementioned metadata database is not a statically configured metadata database; the content stored in this metadata comes from the continuous parsing of successfully executed historical SQL statements, extracting the set of related data tables with existing relationships, and the first metadata describing these relationships.

[0041] Finally, the user-inputted original query information, the retrieved set of related data tables, and the aforementioned first metadata can be combined into a structured context and input into the SQL statement generation model. This allows the SQL statement to generate a target SQL statement that matches the query semantics of the aforementioned query information based on the structured context.

[0042] In the above technical solution, by providing the SQL statement generation model with the context of dynamically extracted and maintained related data tables and metadata describing the relationships from historically successfully executed SQL statements, the SQL statement generation model can effectively perceive the dynamic changes in the SQL database structure and adapt to these changes in real time. Furthermore, it avoids the problem of inaccurate semantics and business logic in the generated SQL statements due to the SQL statement generation model's lack of understanding of the relationships between multiple data tables involved in data-intensive business domains. This significantly improves the semantic accuracy and correctness of the generated SQL statements when processing natural language queries involving relationships between multiple data tables.

[0043] Figure 1 This is a schematic diagram of the architecture of an SQL statement generation system provided in an exemplary embodiment. For example... Figure 1As shown, the system may include a server 11, a network 12, and several electronic devices, such as a PC (Personal Computer) 13, a mobile phone 14, etc.

[0044] Server 11 can be a physical server containing an independent host, or it can be a virtual server hosted in a host cluster. During operation, server 11 can run a server-side program to implement the relevant functions of the application. For example, when server 11 runs the server-side program of an SQL statement generation system, it can act as the server for that SQL statement generation system.

[0045] PC13 and mobile phone 14 are just some of the types of electronic devices that users can use. In reality, users can obviously also use electronic devices such as tablets, laptops, PDAs (Personal Digital Assistants), wearable devices (such as smart glasses, smartwatches, etc.), etc., and one or more embodiments in this specification do not limit this. During operation, the electronic device can run a client-side program to implement the relevant functions of the application. For example, when the electronic device runs the client program of the SQL statement generation system, it can act as a client of the SQL statement generation system.

[0046] The client application of the aforementioned SQL statement generation system can be launched and run on an electronic device. This client-side program can be a native application installed on the electronic device, or it can be a mini-program, quick app, or other similar form. Alternatively, when using web technologies such as HTML5, the relevant functions can be achieved through a browser-displayed page. This browser can be a standalone browser application or a browser module embedded within some applications.

[0047] As for the network 12 that enables interaction between electronic devices such as PC13 and mobile phone 14 and server 11, communication can be achieved using either wired or wireless networks, depending on the communication methods supported by the respective electronic devices. This specification does not impose any restrictions on this. For example, PC13 can support both wired and wireless communication, so it can use either wired or wireless networks as needed. Mobile phone 14 typically only supports wireless communication, so it can use a wireless network for communication.

[0048] The technical solution of this specification will be described in detail below with reference to the accompanying drawings.

[0049] Please see Figure 2 , Figure 2 This document presents a flowchart illustrating a method for generating SQL statements, the method comprising the following execution process: Step 202: Obtain the user-input natural language-based query information; The execution entity of the above method can specifically be an enhancement component used to generate SQL statements for users based on natural language-based query information input by the user. For example, in some embodiments, taking the application scenario of credit data query as an example, this enhancement component can specifically be an enhancement component deployed on a credit service platform that generates SQL statements based on natural language. As a software service or module, this enhancement component encapsulates the core logic of the above method, aiming to receive natural language-based query information input by non-technical personnel (such as risk control analysts) and return SQL statements that can be directly executed on an SQL database.

[0050] In some application scenarios, when users need to initiate queries to an SQL database, they can input query information based on natural language through a graphical user interface. In practical applications, the query information entered by the user can specifically be a natural language question about retrieving information from the SQL database. For example, the query information entered by the user could be "Query the list of customers in XX region with a credit score higher than 700".

[0051] Please see Figure 3 , Figure 3 This document illustrates a flowchart for generating SQL statements for a user.

[0052] like Figure 3 As shown, after a user inputs a query based on natural language, the system can retrieve this query information and generate an SQL statement that semantically matches the user's input. For example, in practical applications, the aforementioned enhancement component can capture the user's query input via API or front-end event listeners and pass it to the back-end core processing logic as the starting point for subsequent processing.

[0053] Step 204: Determine a set of candidate data tables that match the query semantics of the query information from the data tables contained in the SQL database, and query a set of related data tables that are associated with each candidate data table in the set of candidate data tables, as well as first metadata describing the association relationship, from the metadata database; wherein, the metadata database stores a set of related data tables that are associated with each candidate data table in the set of candidate data tables, extracted from successfully executed historical SQL statements, as well as first metadata describing the association relationship. This step is the core preprocessing step of the above method, which aims to provide a structured context for the subsequent generation of SQL statements.

[0054] After obtaining the user's query information based on natural language, a set of candidate tables that match the query semantics of the query information can be further determined from the data tables contained in the SQL database.

[0055] In some embodiments, when querying a set of candidate tables that match the query semantics of the query information from the data tables contained in the SQL database, this can be specifically achieved through vector retrieval.

[0056] To support the aforementioned vector retrieval, a vector database can be pre-built and maintained. Specifically, this vector database can store semantic vectors generated based on the semantic description information of all data tables contained in the SQL database; this semantic description information is used to describe the business semantics of the data tables.

[0057] The construction process of the aforementioned vector database can be achieved by periodically executing an offline task.

[0058] Specifically, the system can periodically (e.g., every morning) connect to the target SQL database to obtain secondary metadata that describes the various data tables it contains.

[0059] Specifically, the aforementioned second metadata may include any form of metadata that can describe the business semantics of the various data tables contained in the SQL database.

[0060] For example, in some embodiments, the aforementioned second metadata may specifically include table comments (TABLE_COMMENT) related to each SQL data table, and column comments (COLUMN_COMMENT) related to the data columns contained in each SQL data table. These comments are typically part of the table's metadata, created by the database designer, and contain the business meaning of the tables and columns. Of course, in practical applications, the aforementioned second metadata may also include other forms of metadata that describe the business semantics of the various data tables contained in the SQL database.

[0061] After obtaining the second metadata, it can be input into an LLM (Large Language Model), and by designing appropriate prompts, the LLM can be guided to generate a coherent, complete text description rich in business semantics based on these annotations, which serves as the semantic description information corresponding to each data table.

[0062] For example, for a data table named "Customer Information Table" used to store customer credit information, the semantic description information generated by LLM can be: "This table is the main customer information table, which stores the core profiles of all registered customers, including key fields such as customer unique identifier (ID), name, mobile phone number, registration time, and region code. It is the basic table for customer identification and correlation analysis in credit reporting business." Of course, in addition to using LLM to generate semantic description information corresponding to each data table, other methods or other forms of deep learning models can also be used to generate semantic description information corresponding to each data table. No specific restrictions will be imposed in this specification.

[0063] Then, the semantic description information generated by LLM corresponding to each data table can be converted into semantic vectors, and the generated semantic vectors can be saved to a pre-created vector database to complete the creation of the vector database.

[0064] For example, in practical applications, the semantic description information corresponding to each data table can be transformed into a fixed-dimensional semantic vector using a text embedding model (such as the BERT model). This semantic vector represents the semantics of the semantic description information in a high-dimensional space. Then, the mapping relationship between the table names and their corresponding semantic vectors is saved to a vector database.

[0065] In this way, the semantic descriptions of each data table in the SQL database can be learned periodically and continuously, and the learned semantic description information can be dynamically saved into a vector database in the form of semantic vectors. This allows for dynamic adaptation to semantic changes in each data table in the SQL database (such as adding columns or modifying table names).

[0066] In some embodiments, to ensure that the vector database can dynamically reflect the semantic changes of the various data tables contained in the SQL database, a triggered update mechanism can also be provided for the vector database.

[0067] When an update to the second metadata of any table in the SQL database is detected (e.g., the database administrator modifies a table comment or adds a commented column), the process of regenerating semantic description information for that table and vectorizing that semantic description information can be automatically triggered. The old records in the vector database are then updated with the new semantic vectors, thereby achieving real-time synchronization of the knowledge base.

[0068] For example, the updated secondary metadata can be re-input into the LLM, so that the LLM can regenerate the semantic description information corresponding to the data table based on the updated secondary metadata; then the semantic description information can be re-input into the text embedding model to be converted into a fixed-dimensional semantic vector, and then the semantic vector can be saved to the vector database to update the semantic vector corresponding to the data table stored in the vector database.

[0069] In some embodiments, in order to preserve the key information in the original annotation, when converting the above semantic description information into a semantic vector, the semantic description information generated by LLM can be merged with the original second metadata (such as table annotations and column annotations) (e.g., by directly concatenating text) to obtain a merged target data, and then the target data is input into the text embedding model to be converted into a semantic vector.

[0070] In some embodiments, please continue to see Figure 3 When querying a set of candidate tables that match the query semantics of a query from a database using vector retrieval, the query information can first be converted into a query vector. For example, the acquired natural language-based query information can be transformed into a fixed-dimensional query vector using the same text embedding model. This query vector represents the query semantics of the natural language-based query information in a high-dimensional space.

[0071] Next, the vector similarity between the query vector and the semantic vectors stored in the aforementioned vector database can be calculated.

[0072] The specific method for calculating the vector similarity described above is not further defined in this specification. For example, in practical applications, the vector similarity can be represented using cosine similarity. Cosine similarity is commonly used due to its insensitivity to vector magnitude.

[0073] Then, please continue to see Figure 3 The semantic vectors contained in the above vector database can be sorted according to the calculated semantic similarity, and the top K semantic vectors (e.g., Top 5) with the highest similarity to the query vector can be selected as the candidate data table set for matching the query semantics of the query information.

[0074] In this specification, after determining the set of candidate data tables that match the query semantics of the above query information from the data tables contained in the SQL database, it is possible to further query the set of associated data tables that are related to each candidate data table in the set of candidate data tables, as well as the first metadata used to describe these relationships.

[0075] To support queries on the aforementioned related data tables, a metadata database can be pre-built and maintained. This metadata database can specifically store a set of related data tables extracted from successfully executed historical SQL statements, along with primary metadata describing these relationships.

[0076] The construction process of the aforementioned vector database can also be achieved by periodically executing an offline task.

[0077] Specifically, the system can periodically access the SQL database to retrieve historical SQL statements that have been successfully executed.

[0078] For example, in practical applications, SQL statements that were successfully executed (i.e., syntactically correct and executed by the database engine) in the past can be retrieved from the SQL database logs.

[0079] Then, these historical SQL statements can be parsed, and based on the parsing results, a set of related data tables with relationships can be extracted from these historical SQL statements, as well as the first metadata used to describe the above relationships.

[0080] For example, an SQL parser can be used to parse it into an Abstract Syntax Tree (AST). Based on the AST, the identifiers of all tables appearing in the FROM and JOIN clauses can be extracted, as well as the predicates in the ON and WHERE clauses that involve column comparisons across tables; based on these predicates, the related tables with relationships and the aforementioned first metadata can be extracted.

[0081] Specifically, the aforementioned first metadata may include any form of metadata that can describe the relationships between data tables.

[0082] For example, in some embodiments, the first metadata may specifically include the association column (also known as the association key) corresponding to the above association relationship; and the association semantics corresponding to the above association relationship.

[0083] It should be noted that the semantics corresponding to the above-mentioned association can specifically include any semantic type that can be used to represent such association, and no specific restrictions are imposed in this specification.

[0084] For example, in some embodiments, the association semantics corresponding to the above association relationship may specifically include the association type corresponding to the association relationship.

[0085] Specifically, the aforementioned association types can include 1:1, 1:N, N:1, and N:M relationships. In practical applications, this association type can be inferred by analyzing the uniqueness constraints of the associated columns (such as whether they are primary keys or foreign keys) and combining this with statistical patterns from historical queries.

[0086] For example, if customer.id is the primary key, and customer.id = order.customer_id appears frequently in the extracted join conditions, and the same customer_id corresponds to multiple records in the order table, then it can be inferred that this is a "1:N" relationship.

[0087] Then, the extracted set of related data tables and the first set of metadata can be saved to the metadata database to complete the creation of the metadata database. For example, the extracted structured information such as "related table A - related columns - related table B - related semantics" can be saved to the metadata database.

[0088] In this way, the set of related data tables and the primary metadata describing these relationships can be learned periodically from the historical SQL statements that have been successfully executed, and the learned set of related data tables and primary metadata are dynamically saved to the metadata database.

[0089] In some embodiments, please continue to see Figure 3 Once a set of candidate data tables matching the query semantics of the above query information is determined from the data tables contained in the SQL database, a query can be initiated to the metadata database to query the set of associated data tables that are related to each candidate data table in the candidate data table set, as well as the first metadata used to describe these relationships.

[0090] For example, in practical applications, a query can be initiated to the aforementioned metadata database. Specifically, the table name of each candidate data table in the candidate data table set can be used as the key to retrieve all other data tables (i.e., the set of related data tables) that have a direct relationship with these candidate data tables stored in the metadata database, and the corresponding related columns and related semantics (first metadata) can be returned.

[0091] Step 206: Input the query information, the set of related data tables, and the first metadata as context into the SQL statement generation model, so that the SQL statement generation model can generate a target SQL statement that matches the query semantics of the query information based on the context.

[0092] Please continue reading Figure 3After retrieving the set of associated data tables that are related to each of the candidate data tables in the above candidate data table set from the metadata database, as well as the first metadata used to describe the association, the user-inputted original query information, the retrieved set of associated data tables, and the first metadata can be used as a structured context input to the SQL statement generation model. The SQL statement generation model can then generate a target SQL statement that matches the query semantics of the query information based on the structured context.

[0093] The specific type of the SQL statement generation model is not specifically limited in this specification. For example, in some embodiments, the model may specifically include a pre-trained model based on the Transformer architecture.

[0094] In some embodiments, in order to organize contextual information more effectively, before inputting the original query information, the set of related data tables obtained from the query, and the first metadata as structured context into the SQL statement generation model, a candidate table relationship graph can be constructed based on the candidate data table set, the set of related data tables, and the relationships between them.

[0095] Specifically, the candidate table relationship graph is a knowledge graph structure composed of nodes and edges. Nodes can represent candidate data tables or related data tables; the edges in the candidate table relationship graph represent the relationships between candidate data tables and related data tables; each edge can be labeled with attribute information, which can specifically include the associated columns corresponding to these relationships and the associated semantics (such as association type) corresponding to these relationships.

[0096] This graph structure can intuitively reflect the connection paths between multiple tables, providing a model-friendly contextual representation. It's understandable that in practical applications, instead of explicitly constructing the candidate table relationship graph, similar technical effects can be achieved by providing the query information, the set of related data tables, and primary metadata to the model in the form of structured text (such as JSON) or a specific sequence of tags.

[0097] Those skilled in the art will understand that the specific selection of the LLM model, text embedding model, and SQL generation model, the vector similarity algorithm, and the database type involved in the above embodiments can all be replaced according to actual resources and technology stack. For example, semantic vector generation can also use non-LLM-driven semantic representation methods; the meta-database relationship mining can also be supplemented by combining existing foreign key constraints in the database. These variations are all within the scope of the core concept proposed in this application of enhancing SQL generation using a dynamic contextual knowledge base.

[0098] As can be seen from the above description of specific implementation methods, the solution of this application integrates user queries, real-time data semantics, and verified table relationships into a high-quality context by constructing and linking two dynamically updated knowledge bases (vector database and meta database). This guides the SQL generation model to output accurate, adaptable SQL statements that conform to complex business logic. On the one hand, it allows the SQL statement generation model to effectively perceive the dynamic changes in the SQL database structure, thereby adapting to the dynamic changes in the SQL database structure in real time. For example, when the SQL database structure changes, the changes in the semantic description information of some data tables caused by the change in the database structure can be dynamically updated to the vector database in a timely manner.

[0099] On the other hand, it can avoid the problem that the SQL statement generation model lacks an understanding of the relationships between multiple data tables involved in data-intensive business domains, resulting in inaccurate semantics and business logic in the generated SQL statements. This can significantly improve the semantic accuracy and correctness of the business logic of the SQL statements generated by the SQL statement generation model when processing natural language queries involving relationships between multiple data tables.

[0100] For example, by continuously learning the related data tables and the primary metadata describing the relationships from successfully executed historical SQL statements, the relationships between multiple data tables involved in data-intensive business domains can be fully explored. This allows the SQL statement generation model to accurately refer to the latest data table relationships learned from successfully executed historical SQL statements when generating SQL statements for user-input natural language-based query information. As a result, the SQL statement generation model has the ability to understand the latest data table relationships in a timely and accurate manner.

[0101] The following two specific examples further illustrate the complete execution flow of the method in actual business scenarios.

[0102] Example 1: Customer queries "Sales figures for each department this month" In this embodiment, it is assumed that the user (such as a business operations personnel) enters the following natural language-based query information on the data query interface of the credit reporting service platform: "Statistics on the sales revenue of each department this month".

[0103] First, the aforementioned enhanced component can capture the user's input query text "Statistics on the sales revenue of each department this month" through the front-end interface.

[0104] Secondly, the enhancement component can convert the query text into a query vector.

[0105] Subsequently, the vector database is queried to calculate the similarity between the vector and all semantic vectors in the database.

[0106] Suppose the top 5 most similar data tables are matched, and their semantic descriptions are as follows: department (department table): The semantic description may be "stores organizational structure information of internal departments of the company".

[0107] employee (employee table): The semantic description may be "stores employee personal information and their department".

[0108] The sales table is semantically described as "storing sales order records, including fields such as sales order number, sales date, sales amount, department ID, and product ID". product (product table): The semantic description may be "stores product catalog information".

[0109] region (region table): The semantic description may be "stores sales region division information".

[0110] These 5 tables were identified as the candidate data table set {department, employee, sales, product, region}.

[0111] The enhanced component can use these five tables as query keys to query the metadata database. The metadata database returns other tables that are directly related to these tables, as well as primary metadata.

[0112] Assume the returned information includes: The sales table and the department table are linked by sales.department_id = department.id, with the semantics of "N:1" (one sales record belongs to one department, and one department has multiple sales records).

[0113] The sales table and the employee table are linked by sales.salesperson_id = employee.id.

[0114] The sales table and the product table are linked by sales.product_id = product.id.

[0115] The department table and the region table are linked by department.region_id = region.id.

[0116] The enhancement component can integrate the original query "statistics on the sales revenue of each department this month", the candidate data table set, and the aforementioned primary metadata into a structured context, which is then input into the SQL statement generation model.

[0117] Based on this context, the SQL generation model ultimately outputs the following target SQL statement: SELECT d.name, SUM(s.amount) AS total_sales FROM sales s JOIN department d ON s.department_id = d.id WHERE s.sale_date>= DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY d.name; This SQL statement accurately reflects the user's intent: The system calculates the total sales for each department (d.name) over the past month and correctly links the sales records with the department information using a JOIN statement.

[0118] Example 2: Customer query "List all customers who have not placed an order" In this embodiment, it is assumed that the user enters the following natural language-based query information on the data query interface of the credit reporting service platform: "List all customers who have not placed an order".

[0119] First, the aforementioned enhanced component can capture the user's query text "List all customers who have not placed an order" through the front-end interface.

[0120] Secondly, the enhancement component can convert the query text into a query vector.

[0121] Subsequently, the vector database is queried to calculate the similarity between the vector and all semantic vectors in the database.

[0122] Suppose the top 5 most similar data tables are matched, and their semantic descriptions are as follows: customer (customer table): Semantically described as "stores basic customer information, including fields such as customer ID, name, registration time, and contact information".

[0123] order (order table): Semantically described as "stores customer order header information, including fields such as order number, customer ID, order time, order status, amount, etc."

[0124] product (product table): Semantically described as "stores product / service catalog information, such as credit products, data service packages, etc. in credit reporting or financial scenarios, including fields such as product ID, product name, type, status, etc."

[0125] region (region table): Semantically described as "stores geographical regions or administrative division information, used to associate customers, departments or sales regions, including fields such as region ID, region name, parent region ID, etc."

[0126] payment (payment table): Semantically described as "stores payment transaction records related to orders, including fields such as payment ID, order ID, payment method, payment amount, payment status, and payment time".

[0127] These 5 tables were identified as the candidate data table set {customer, order, product, region, payment}.

[0128] The enhanced component can use these five tables as query keys to query the metadata database. The metadata database returns other tables that are directly related to these tables, as well as primary metadata.

[0129] Assume the returned information includes: The order table and the customer table are linked by order.customer_id = customer.id, with a link semantic of "N:1".

[0130] The order table and the product table are linked through an intermediate table order_item (N:M relationship).

[0131] The customer table and the region table are linked by customer.region_id = region.id.

[0132] The `order` table and the `payment` table are linked by `order.id = payment.order_id`, which is used to query the payment status.

[0133] The enhancement component can integrate the original query "list all customers who have not placed an order", the candidate data table set, and the aforementioned primary metadata into a structured context, which is then input into the SQL statement generation model.

[0134] Based on this context, the SQL generation model ultimately outputs the following target SQL statement: SELECT c.name FROM customer c LEFT JOIN order o ON c.id = o.customer_id WHERE o.id IS NULL; This SQL statement starts from the customer table, left joins the order table, and filters out customer records with empty order IDs, i.e. "customers who have not placed an order", which perfectly matches the query intent.

[0135] Figure 4 This is a schematic structural diagram of an electronic device provided in an exemplary embodiment. For example... Figure 4 As shown, device 400 mainly consists of a communication interface 402, a user interface 404, a processor 406, and a data storage 408. These components are interconnected and communicate with each other via a system bus, network, or other connection mechanism 410. The communication interface 402 enables device 400 to communicate with other devices, access networks, and transmission networks via analog or digital modulation. For example, the communication interface 402 may include a chipset and antenna for wireless communication with a radio access network or access point. Furthermore, the communication interface 402 can be a wired interface such as Ethernet, Token Ring, or a USB port, or a wireless interface such as Wi-Fi, Bluetooth, Global Positioning System (GPS), or a wide-area wireless interface (e.g., WiMAX or LTE). Of course, the communication interface 402 can also support other forms of physical layer interfaces and standard or proprietary communication protocols. The communication interface 402 may also include multiple physical communication interfaces, such as Wi-Fi, Bluetooth, and wide-area wireless interfaces.

[0136] User interface 404 includes receiving user input and providing output to the user. Therefore, user interface 404 may include input components such as a keypad, keyboard, touch-sensitive or presence-sensitive panel, computer mouse, trackball, joystick, microphone, still camera, and video camera, and output components such as a display screen (which may be combined with a touch-sensitive panel), CRT, LCD, LED, display using DLP technology, printer, and other similar devices known or developed in the future. User interface 404 may also generate auditory output via speakers, speaker jacks, audio output ports, audio output devices, headphones, and other similar devices known or developed in the future. In some embodiments, user interface 404 may include software, circuitry, or other forms of logic capable of transmitting and receiving data from external user input / output devices. Additionally or alternatively, device 400 may support remote access from other devices via communication interface 402 or another physical interface (not shown). User interface 404 may be configured to receive user input, the position and movement of which may be indicated by an indicator or cursor described herein. User interface 404 may also be configured as a display device for rendering or displaying text fragments.

[0137] Processor 406 may contain one or more general-purpose processors and / or special-purpose processors.

[0138] Data storage 408 may include one or more volatile and / or non-volatile storage components and may be integrated wholly or partially with processor 406. Data storage 408 may include removable and non-removable components.

[0139] Processor 406 is capable of executing program instructions 418 (e.g., compiled or uncompiled program logic and / or machine code) stored in data storage 408 to perform the various functions described herein. Data storage 408 may comprise a non-transitory computer-readable medium on which program instructions are stored, which, when executed by device 400, enable device 400 to perform any methods, processes, or functions disclosed in this specification and / or the accompanying drawings. Processor 406 executing program instructions 418 may result in processor 406 using data 412.

[0140] For example, program instructions 418 may include an operating system 422 (e.g., an operating system kernel, device drivers, and / or other modules) installed on device 400 and one or more applications 420 (e.g., a browser, social application, or game application). Similarly, data 412 may include operating system data 416 and application data 414. Operating system data 416 is primarily accessible to the operating system 422, while application data 414 is primarily accessible to one or more applications 420. Application data 414 may reside in a file system visible or hidden from the user of device 400.

[0141] Application 420 can communicate with operating system 422 through one or more application programming interfaces (APIs). These APIs help application 420 read and / or write application data 414, transmit or receive information via communication interface 402, receive or display information on user interface 404, etc.

[0142] In some terminology, application 420 may be simply referred to as "app". Furthermore, application 420 can be downloaded to device 400 through one or more online app stores or app markets. However, applications can also be installed on device 400 in other ways, such as through a web browser or a physical interface on device 400 (e.g., a USB port).

[0143] Please refer to Figure 5 This specification also proposes a device for generating SQL statements, which can be applied to applications such as... Figure 4 The device shown is used to implement the technical solution of this specification. The device may include: The acquisition module 501 acquires query information based on natural language input by the user; The query module 502 determines a set of candidate data tables that match the query semantics of the query information from the data tables contained in the SQL database, and queries a set of associated data tables that are related to each candidate data table in the set of candidate data tables and first metadata used to describe the association relationship from the metadata database; wherein, the metadata database stores a set of associated data tables that are related to each candidate data table in the set of candidate data tables and first metadata used to describe the association relationship. The input module 503 inputs the query information, the set of related data tables, and the first metadata as context into the SQL statement generation model, so that the SQL statement generation model can generate a target SQL statement that matches the query semantics of the query information based on the context.

[0144] For ease of description, the above devices are described by dividing them into various modules or units based on their functions. Of course, when implementing one or more of these specifications, the functions of each module or unit can be implemented in the same or different software and / or hardware, or a module that performs the same function can be implemented by a combination of multiple sub-modules or sub-units, etc. The device embodiments described above are merely illustrative. For example, the division of units is only a logical functional division; in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed.

[0145] Based on the same concept as the methods described above, this specification also provides an electronic device, including: a processor; a memory for storing processor-executable instructions; wherein the processor performs the steps of the method as described in any of the above embodiments by executing the executable instructions.

[0146] Based on the same concept as the methods described above, this specification also provides a computer-readable storage medium having computer instructions stored thereon that, when executed by a processor, implement the steps of the methods as described in any of the above embodiments.

[0147] Based on the same concept as the methods described above, this specification also provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of the methods as described in any of the above embodiments.

[0148] What those skilled in the art will understand is: In this specification, the terms "comprising," "including," or any other variations thereof are intended to cover a non-exclusive inclusion, such that a process, method, product, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, product, or apparatus. Without further limitation, the presence of additional identical or equivalent elements in a process, method, product, or apparatus that includes said elements is not excluded.

[0149] In this specification, “a,” “an,” and “the” do not specifically refer to the singular, but may also include the plural.

[0150] In this specification, ordinal numbers such as "first," "second," etc., do not necessarily indicate order; they are often used to distinguish between objects. For example, "first server" and "second server" usually refer to two servers. To differentiate between these two servers, they are described as "first server" and "second server." Of course, sometimes these two servers may be the same server.

[0151] In this specification, unless explicitly stated otherwise, "receiving and sending data" does not necessarily mean direct receiving and sending; it can also mean indirect receiving and sending. For example, A receiving data sent by B can be understood as A directly receiving the data sent by B, or it can be understood as A indirectly receiving the data sent by B through other entities such as C. Similarly, B sending data to A can be understood as B sending the data directly to A, or it can be understood as B indirectly sending the data to A through other entities such as C. Here, C can be one entity, or it can be two or more entities.

[0152] In this specification, unless explicitly stated otherwise, the relationships between structures can be direct or indirect. For example, when describing "A is connected to B," unless it is explicitly stated that A and B are directly connected, it should be understood that A can be directly connected to B or indirectly connected to B. Similarly, when describing "A is on top of B," unless it is explicitly stated that A is directly above B (AB is adjacent and A is above B), it should be understood that A can be directly above B or indirectly above B (AB is separated by other elements, and A is above B). And so on.

[0153] This specification uses specific terms to describe embodiments thereof. Terms such as "an embodiment," "one embodiment," and / or "some embodiments" refer to a particular feature, structure, or characteristic associated with at least one embodiment of this specification. Therefore, it should be emphasized and noted that references to "an embodiment," "one embodiment," or "an alternative embodiment" in different locations throughout this specification do not necessarily refer to the same embodiment. Furthermore, those skilled in the art can combine and integrate the different embodiments or examples described herein, as well as the features of those different embodiments or examples, without contradiction.

[0154] Although one or more embodiments of this specification provide method steps as described in the embodiments or flowcharts, it is understood that the order of steps listed in the embodiments or flowcharts is only one of many possible execution orders and does not represent the only execution order. Therefore, when the claims involve method steps, any changes or adjustments to the order of such steps, or the parallelism between steps, are also within the scope of protection of the claims.

Claims

1. A method for generating SQL statements, comprising: Obtain query information based on natural language input from the user; The SQL database contains a set of candidate data tables that match the query semantics of the query information. The database also contains a set of associated data tables that are related to each candidate data table in the set of candidate data tables, as well as first metadata describing the relationship. The database stores a set of associated data tables that are related to each candidate data table in the set of candidate data tables, as well as first metadata describing the relationship. The query information, the set of related data tables, and the first metadata are input as context into the SQL statement generation model, so that the SQL statement generation model can generate a target SQL statement that matches the query semantics of the query information based on the context.

2. The method as described in claim 1, wherein querying a set of candidate data tables that match the query semantics of the query information from the data tables contained in the SQL database includes: The query information is converted into a query vector; Calculate the vector similarity between the query vector and the semantic vector stored in the vector database; wherein, the vector database stores semantic vectors generated based on the semantic description information of all data tables contained in the SQL database; the semantic description information is used to describe the business semantics of the data tables; The data table corresponding to at least one semantic vector with the highest similarity to the query vector stored in the vector database is determined as a set of candidate data tables that match the query semantics of the query information.

3. The method of claim 2, further comprising: Periodically retrieve second metadata used to describe the various data tables contained in the SQL database; The obtained second metadata is input into the LLM, so that the LLM can generate semantic description information corresponding to each data table based on the second metadata; The semantic description information is converted into a semantic vector, and the semantic vector is saved to the vector database.

4. The method of claim 3, further comprising: If the second metadata used to describe any data table contained in the SQL database is updated, the updated second metadata is re-entered into the LLM so that the LLM can regenerate the semantic description information corresponding to the data table based on the updated second metadata; The semantic description information is converted into a semantic vector, and the semantic vector is saved to the vector database to update the semantic vectors corresponding to the data table stored in the vector database.

5. The method of claim 4, wherein the semantic description information is converted into a semantic vector and the semantic vector is saved to the vector database, comprising: The semantic description information and the second metadata are merged to obtain the merged target data. The target data is converted into semantic vectors, and the semantic vectors are saved to the vector database.

6. The method of claim 4 or 5, wherein the second metadata includes table annotation data associated with each of the SQL data tables; and column annotation data associated with the data columns contained in each of the SQL data tables.

7. The method of claim 1, further comprising: Periodically retrieve successfully executed historical SQL statements; The historical SQL statements are parsed, and based on the parsing results, a set of related data tables with relationships are extracted from the historical SQL statements, as well as first metadata used to describe the relationships; The extracted set of related data tables and the first metadata are saved to the metadata database.

8. The method of claim 7, wherein the first metadata includes an association column corresponding to the association relationship; and an association semantics corresponding to the association relationship.

9. The method of claim 8, wherein the query information, the set of related data tables, and the first metadata are input as context into an SQL statement generation model, so that the SQL statement generation model generates a target SQL statement that semantically matches the query information based on the context, comprising: A candidate table relationship graph is constructed based on the relationships between each candidate data table in the candidate data table set and each associated data table in the associated data table set; wherein, the nodes in the candidate table relationship graph represent the candidate data table or the associated data table; the edges in the candidate table relationship graph represent the relationships between the candidate data table and the associated data table; the attribute information of the edges in the candidate table relationship graph includes the associated columns corresponding to the relationships and the associated semantics corresponding to the relationships; The query information and the candidate table relationship graph are used as context input to the SQL statement generation model, so that the SQL statement generation model can generate a target SQL statement that matches the query semantics of the query information based on the context.

10. The method of claim 8, wherein the association semantics corresponding to the association relationship includes the association type corresponding to the association relationship.

11. An electronic device, characterized in that, include: processor; A memory for storing processor-executable instructions; wherein the processor implements the steps of the method as described in any one of claims 1-10 by executing the executable instructions.

12. A computer program product, characterized in that, Includes a computer program / instructions that, when executed by a processor, implement the steps of the method as described in any one of claims 1-10.

Images