Data query statement generation method and device, equipment, medium and program product

Abstract

The invention relates to the technical field of data processing, and provides a data query statement generation method and device, equipment, a medium and a program product.The data query statement generation method comprises the steps that firstly, based on a received natural language query request, a query problem and prediction result description information corresponding to the query problem are determined, wherein the prediction result description information is used for representing query features contained in the prediction query result aiming at the query problem; then, on the basis of the prediction result description information, target reference information matched with the query problem is determined in a preset knowledge base, and the knowledge base comprises at least one of metadata information describing a target database structure and historical data query records; and finally, based on the query problem and the target reference information, generating a target data query statement. The accuracy of data query statement generation can be effectively improved.

Description

Technical Field

[0001] This disclosure relates to the field of data processing technology, specifically to methods, apparatus, devices, media, and program products for generating data query statements. Background Technology

[0002] With the popularization of big data technology, generating structured queries directly from natural language has become a key technology for lowering the barrier to data use. While existing natural language-to-query technologies attempt to directly convert users' natural language into queries, they often rely on direct parsing of the user's original question or simple keyword matching. Because natural language is often ambiguous, and there is a significant semantic gap between user questions and the underlying database's physical structure or historical query logic, direct conversion methods struggle to accurately capture the user's true intent, leading to frequent field mapping errors or logical deviations in the generated queries.

[0003] Therefore, there is an urgent need for a data query statement generation method to solve the problem of low accuracy in data query statement generation in related technologies. Summary of the Invention

[0004] This disclosure provides a method, apparatus, device, medium, and program product for generating data query statements to solve the problem of low accuracy in generating data query statements in related technologies.

[0005] Firstly, this disclosure provides a method for generating data query statements, the method comprising: Based on the received natural language query request, determine the query question and the corresponding prediction result description information; the prediction result description information is used to characterize the query features contained in the predicted query results for the query question. Based on the prediction results, target reference information matching the query question is determined from a pre-set knowledge base; the pre-set knowledge base includes at least one of the following: metadata information describing the target database structure and historical data query records. Based on the query question and target reference information, generate the target data query statement.

[0006] The data query statement generation method disclosed herein determines the predicted result description information based on the natural language query request, and then determines the target reference information in a pre-set knowledge base based on this information, ultimately generating the target data query statement. This approach utilizes the predicted result description information (i.e., the pre-judgment of the query result) as an intermediary, effectively bridging the semantic differences between the user's natural language query and the underlying database storage structure. Simultaneously, by combining it with a knowledge base containing metadata and historical query records, it fully leverages existing data assets and historical experience, thereby significantly improving the accuracy and usability of the generated data query statement.

[0007] In one optional implementation, based on the received natural language query request, the query question and the corresponding prediction result description information are determined, including: Semantic disambiguation is performed on natural language query requests to generate standardized query questions that conform to preset specifications; The standardized query question is input into a pre-defined large language model for hypothetical reasoning to obtain a hypothetical response, which is then used as the description information for the prediction result. The hypothetical response includes the key prediction indicators, prediction result features, and prediction field attributes for the standardized query question.

[0008] This disclosure also transforms vague user requests into canonical expressions and explicitly generates potential database-related terms through hypothetical reasoning, thereby establishing a semantic connection between natural language and database domain knowledge before retrieving the knowledge base, which greatly improves the accuracy of subsequent knowledge retrieval.

[0009] In one optional implementation, semantic disambiguation processing is performed on the natural language query request, including: Determine whether the natural language query request meets the preset rewriting conditions; the preset rewriting conditions include at least one of the following: vague expression, semantic ambiguity, lack of key filtering conditions, and non-compliance with business semantic specifications. If the preset rewriting conditions are met, the natural language query request is rewritten based on the preset question template to generate a standardized query question. The data structure of the standardized query question includes a description of the requirement target, a list of output fields, and filter condition fields.

[0010] This disclosure can also automatically identify and correct irregularities in user queries, ensuring that the questions input to subsequent processes have clear requirements, output fields, and filtering conditions, effectively avoiding errors in query generation due to low input quality.

[0011] In one optional implementation, target reference information matching the query question is determined from a preset knowledge base, including: Based on the information described by the prediction results, vector semantic matching and keyword precision matching are performed in the knowledge base; Based on a preset weighting strategy, the semantic matching results and keyword matching results are weighted and sorted to determine the target reference information.

[0012] This disclosure also combines the generalization ability of semantic retrieval (solving the word mismatch problem) and the precision ability of keyword retrieval (solving the specific entity location problem). Through a weighted fusion strategy, it ensures that the target reference information retrieved from the massive knowledge base is both semantically relevant and literally accurate, thereby providing the most accurate contextual basis for generating high-quality query statements.

[0013] In one optional implementation, the step of constructing historical data query records in a preset knowledge base includes: Retrieve the target data table; the target data table is the data table in the target database that has been read more than a preset number of times. Based on the target data table, identify the historical query statements that have read the target data table; Parse historical query statements to extract query fragments and descriptions of the corresponding use cases; The query fragments and usage scenario descriptions are stored as historical data query records in the knowledge base.

[0014] This disclosure also utilizes verified historical high-quality data query statement fragments as references, enabling the generated query statements to directly reuse verified complex calculation logic (such as specific filtering conditions or calculation formulas), which not only improves generation efficiency but also avoids errors that may occur when regenerating complex logic.

[0015] In one optional implementation, the step of constructing metadata information in a pre-defined knowledge base includes: Retrieve the original table structure information and field attribute information from heterogeneous data sources; Convert the original table structure information and field attribute information into a unified intermediate format data; Obtain business semantic information and establish an association mapping between intermediate format data and business semantic information to generate metadata information; business semantic information includes at least one of business indicator definitions, data tags, and subject domain divisions.

[0016] This disclosure also enables accurate responses to query requests involving specific business metrics by converting the raw information from heterogeneous data sources into a unified intermediate format and associating it with business semantic information (such as indicator definitions and tags).

[0017] In one optional implementation, the preset knowledge base also includes industry domain knowledge and system preset knowledge; the industry domain knowledge includes preset mapping relationships between industry terms and database fields; the system preset knowledge includes system variable definitions built into the database engine.

[0018] This disclosure also enables the generation process to adapt to industry-specific terminology mappings and system variable definitions for specific database engines, improving the professionalism and executability of query statements.

[0019] In one optional implementation, a target data query statement is generated based on the query question and target reference information, including: The query question and target reference information are injected into a preset prompt word template to obtain the target prompt word; the preset prompt word template includes preset role definitions, task execution process constraints and output structure specifications; Input the target prompts into a pre-defined large language model to generate a target data query statement.

[0020] This disclosure also sets strict boundaries and thought processes for the generation of large models through standardized prompts, ensuring the consistency and standardization of the output data query statements in terms of format and structure.

[0021] In one alternative implementation, before the step of inputting the target prompt word into a preset large language model, the method further includes: Parse the tenant identity identifier in a natural language query request; Based on the tenant's identity, the target large language model and its corresponding model parameter configuration are determined from the preset resource configuration strategy; the model configuration parameters include at least one of the following: randomness parameter, number of candidate words, and maximum generation length. The target large language model is invoked based on the model parameter configuration.

[0022] This disclosure also achieves resource isolation and personalized services in multi-tenant scenarios, and can flexibly adjust parameters such as the randomness or generation length of the model according to the business level or needs of different tenants, which not only ensures data security, but also optimizes resource utilization efficiency and user experience.

[0023] In one alternative implementation, after the step of generating the target data query statement, the method further includes: Perform syntax and business logic validation on the target data query statement; If the verification passes, record the generation log of the target data query statement; the generation log includes the adoption behavior or evaluation data of the target data query statement; The knowledge base is updated based on the generated logs.

[0024] This disclosure also ensures delivery quality through grammatical and logical checks; and the knowledge base is continuously updated based on user feedback, enabling it to learn from errors and accumulate new correct cases.

[0025] Secondly, this disclosure provides a data query statement generation apparatus, the apparatus comprising: The receiving module is used to determine the query question and the corresponding prediction result description information based on the received natural language query request; the prediction result description information is used to characterize the query features contained in the predicted query results for the query question. The search module is used to determine target reference information that matches the query question in a preset knowledge base based on the prediction result description information; the preset knowledge base contains at least one of the following: metadata information describing the target database structure and historical data query records. The generation module is used to generate target data query statements based on the query question and target reference information.

[0026] Thirdly, this disclosure provides an electronic device, including: a memory and a processor, which are communicatively connected to each other. The memory stores computer instructions, and the processor executes the computer instructions to perform the data query statement generation method described in the first aspect or any corresponding embodiment.

[0027] Fourthly, this disclosure provides a computer-readable storage medium storing computer instructions for causing a computer to execute the data query statement generation method described in the first aspect or any corresponding embodiment.

[0028] Fifthly, this disclosure provides a computer program product, including computer instructions for causing a computer to execute the data query statement generation method described in the first aspect or any corresponding embodiment thereof. Attached Figure Description

[0029] To more clearly illustrate the technical solutions in the specific embodiments of this disclosure or the prior art, the drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this disclosure. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0030] Figure 1 This is a schematic diagram illustrating an application scenario according to an embodiment of this disclosure; Figure 2 This is a schematic flowchart of a first method for generating data query statements according to an embodiment of the present disclosure; Figure 3 This is a schematic diagram of a second method for generating data query statements according to an embodiment of the present disclosure; Figure 4 This is a schematic diagram of the overall architecture of the SQL generation system according to an embodiment of the present disclosure; Figure 5This is a schematic diagram illustrating the process of constructing SQL fragment knowledge according to embodiments of this disclosure; Figure 6 This is a schematic diagram of the SQL generation process according to an embodiment of the present disclosure; Figure 7 This is a flowchart illustrating the problem rewriting and hypothetical reasoning based on embodiments of this disclosure; Figure 8 This is a structural block diagram of a data query statement generation apparatus according to an embodiment of the present disclosure; Figure 9 This is a schematic diagram of the hardware structure of an electronic device according to an embodiment of this disclosure. Detailed Implementation

[0031] To make the objectives, technical solutions, and advantages of the embodiments of this disclosure clearer, the technical solutions of the embodiments of this disclosure will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this disclosure, and not all embodiments. Based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0032] It is understood that before using the technical solutions disclosed in the various embodiments of this disclosure, users should be informed of the types, scope of use, and usage scenarios of the personal information involved in this disclosure in an appropriate manner in accordance with relevant laws and regulations, and user authorization should be obtained.

[0033] Before providing a detailed description of the embodiments of this disclosure, some of the nouns and terms involved in the embodiments of this disclosure will be explained.

[0034] LLM (Large Language Model): refers to a deep learning model trained on massive amounts of text data, which has the ability to understand and generate natural language. Typical examples include the GPT series, Claude series, PaLM, etc.

[0035] SQL (Structured Query Language): A standard programming language specifically designed for managing and manipulating relational databases, supporting operations such as adding, deleting, modifying, and querying data (CRUD), as well as defining table structures and controlling access permissions.

[0036] MCP (Model Context Protocol): A general standard interface protocol designed for efficiently obtaining the context information required by a model, aiming to promote the standardization and decentralization of interaction between large language model applications and underlying data sources.

[0037] RAG (Retrieval-Augmented Generation): An AI technology framework that combines information retrieval technology with large model generation capabilities, enhancing the accuracy of model responses by retrieving external knowledge bases.

[0038] DSL (Domain-Specific Language): A specific syntax language designed for database queries and operations in a specific domain.

[0039] Data Lakehouse: A new data architecture that combines the flexibility of a data lake (storing structured, semi-structured, and unstructured data) with the high-performance management capabilities of a data warehouse (supporting transactions and schema validation).

[0040] As one optional application scenario of this disclosure embodiment, such as Figure 1 As shown, this data query statement generation method can be executed in a system that includes at least one terminal device and at least one server. Figure 1 The system is illustrated in the example, which includes a computer 101, a mobile terminal 102, and a server 103, and the terminal devices such as the computer 101 and the mobile terminal 102 are connected to the server 103 through a network 110.

[0041] The terminal device can be a smartphone, tablet, laptop, PDA, or desktop computer, etc. Users can input natural language query requests through the terminal device. Server 103 can be a standalone physical server, a server cluster, a distributed system, or a cloud server providing cloud services. Server 103 is deployed with a pre-defined knowledge base and a large language model. In practical applications, users send natural language query requests containing business query intent to server 103 through a terminal device (such as computer 101). After receiving the request, server 103 executes the data query statement generation method of this embodiment. Network 110 can be a wired network or a wireless network, examples of which include, but are not limited to, the Internet, corporate intranets, local area networks, wide area networks, mobile communication networks, and combinations thereof.

[0042] In related technologies, when users want to retrieve data from a database using natural language, they often face technical problems such as complex database table structures and inconsistencies between business terminology and underlying field naming. This results in low accuracy when directly converting natural language into query statements (such as SQL), and the generated statements are prone to errors in field referencing or logical illusions. This disclosure provides a method for generating data query statements, significantly improving the accuracy and usability of data query statement generation.

[0043] According to an embodiment of this disclosure, a method for generating data query statements is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0044] This embodiment provides a method for generating data query statements. Figure 2 This is a flowchart of a data query statement generation method according to an embodiment of the present disclosure, such as... Figure 2 As shown, the process includes the following steps: Step S201: Based on the received natural language query request, determine the query question and the corresponding prediction result description information; the prediction result description information is used to characterize the query features contained in the predicted query results for the query question.

[0045] Natural language query requests refer to text or voice commands entered by users in non-programming languages ​​(such as Chinese or English) to express their need to retrieve data. For example, a user might enter "Check the sales figures for region A last month" in a dialog box.

[0046] A query question refers to the core business query intent extracted from the original request. For example, for the request above, the extracted query question would be "Query time range = last month, region = A, target metric = sales revenue".

[0047] Predictive result description information refers to the characteristic description of the query result estimated based on understanding of the problem and experience or reasoning before actually querying the database. It is not the final query statement, but a characteristic description of the expected returned data (such as which fields are included, data types, etc.). For example, for a query on "sales amount", the generated predictive result description information might be: Assuming the query result is a table containing "region name" (string type) and "total sales amount" (numeric type), and also includes a "date" field.

[0048] The action of "determining" refers to the action of reasoning through semantic analysis, intent recognition, or generative models. For example, after receiving a request, a pre-built large language model is invoked, the request content is input, and the model outputs its understanding of the question (query question) and a hypothetical result description (predicted result description information).

[0049] The core of this step lies in prediction. Before retrieving the database structure, based on an understanding of the original question input by the user, and using experience or reasoning, the feature descriptions of the query results are predicted. This mechanism avoids directly matching database fields with fuzzy natural language.

[0050] For example, suppose a user enters the request: "Check which new users registered yesterday." First, the query is defined as "Query the list of new users on a specific date." Next, a predicted result description is generated: "The predicted result should include fields such as user ID and registration time, with the registration time field limited to yesterday. The result may be a list containing multiple rows of user records." This step translates natural language into a preparatory language that the database can understand.

[0051] Step S202: Based on the prediction result description information, determine the target reference information that matches the query question in the preset knowledge base; the preset knowledge base includes at least one of the following: metadata information describing the target database structure and historical data query records.

[0052] A pre-built knowledge base refers to a database or storage system that is pre-built and stores the information needed to help generate query statements. For example, a vector database that stores the table names, column definitions (metadata) of all data tables in a company, as well as the SQL statements successfully executed by employees in the past month (historical records).

[0053] Target reference information refers to materials retrieved from the knowledge base that can directly help generate the final SQL. For example, retrieved table structure information: "Table name: t_sales_order, fields: sales_name, amount"; or a similar historical SQL: "SELECT name, sum(amt) FROM orders".

[0054] Metadata information refers to data that describes the data, such as table names, column names, and field comments. For example, a database may have a table named user_info with two fields: uid (user ID) and reg_date (registration date).

[0055] Historical data query records refer to successful query cases executed by the system in the past. Example: The record shows that a user queried "new registrations" last week, and the SQL used at that time was "SELECT count(*) FROM user_info WHERE reg_date =".

[0056] The action of "confirming" refers to the process of finding corresponding content in a vast amount of knowledge based on clues, which usually involves retrieval, matching, and filtering.

[0057] In this step, the prediction result description information generated in step S201 is used to perform precise retrieval in the knowledge base. Because the prediction result description information contains specific field features (such as registration time and ID), which are highly consistent with the metadata (table structure definition) in the knowledge base in semantics, the retrieval will be very accurate.

[0058] Continuing from the example in the previous step, we retrieve the description information "the prediction result includes user ID and registration time" from the knowledge base.

[0059] Metadata matching: A table called t_customer_reg was found in the database, whose metadata description contains customer_id (corresponding to user ID) and create_time (corresponding to registration time). Therefore, the structure of this table was used as the target reference information.

[0060] Matching historical records: A historical record was also found in the knowledge base, with the note "Query daily new entries," and its SQL fragment contained "WHERE create_time = CURRENT_DATE - 1," which highly matches the predicted "yesterday." The table structure and historical SQL fragment found above were identified as target reference information.

[0061] Step S203: Based on the query question and target reference information, generate the target data query statement.

[0062] The target data query statement refers to the final generated code that a computer database can directly recognize and execute. For example, a standard SQL statement such as SELECT * FROM t_customer_reg WHERE create_time = '2023-10-27'.

[0063] The "generation" action refers to the process of combining the user's intent (query question) with the retrieved expertise (target reference information) to construct code that conforms to the syntax rules.

[0064] For example, consider the following information: Question: "Check which new users registered yesterday."

[0065] Target reference information: The table name is t_customer_reg, and the fields are customer_id and create_time; the historical syntax is WHERE create_time = DATE_SUB(CURDATE(), INTERVAL 1 DAY).

[0066] Perform logical assembly: determine the operation type as query (SELECT); determine the query object as t_customer_reg; determine the filter condition as based on create_time and the time range as yesterday.

[0067] The final generated query statement for the target data is as follows: .

[0068] In this way, the generated statements not only conform to the user's natural language intent, but also accurately match the actual physical structure of the database.

[0069] In summary, the data query statement generation method provided in this embodiment determines the description information of the predicted result based on the natural language query request, and then determines the target reference information in a preset knowledge base based on this, ultimately generating the target data query statement. This approach uses the description information of the predicted result (i.e., the prediction of the query result) as an intermediary, which can effectively bridge the semantic differences between the user's natural language question and the underlying storage structure of the database; at the same time, combined with a knowledge base containing metadata and historical query records, it can make full use of existing data assets and historical experience, thereby significantly improving the accuracy and usability of the generated data query statement.

[0070] This embodiment provides a method for generating data query statements. Figure 3 This is a flowchart of a data query statement generation method according to an embodiment of the present disclosure, such as... Figure 3 As shown, the process includes the following steps: Step S301: Based on the received natural language query request, determine the query question and the corresponding prediction result description information; the prediction result description information is used to characterize the query features contained in the predicted query results for the query question.

[0071] In practice, the request is first processed by semantic disambiguation. Semantic disambiguation refers to identifying and correcting ambiguous parts of the user input. For example, if the user says "recently," it is clarified as "the past 7 days" according to business rules.

[0072] If the request is determined to meet preset rewriting conditions, which include at least one of the following: vague expression, semantic ambiguity, lack of key filtering conditions, and non-compliance with business semantic specifications (e.g., vague expression like "recently" or lack of key filtering conditions), the request will be rewritten based on a preset question template. Rewriting refers to mapping natural language to a preset question template. For example, the colloquial phrase "pick out" is rewritten as the "SELECT" intent, and "does not meet the criteria" is associated with specific filtering condition fields. A standardized query question is generated that includes a description of the requirement objective, a list of output fields, and filtering condition fields.

[0073] For example, a user enters "recent abnormal orders". The system recognizes that "recent" is a vague expression and, combined with the current time, rewrites it as "order placed between 2024-05-01 and 2024-05-07". It also recognizes that "abnormal" does not conform to business semantics and maps it to the business definition "order status = refund in progress". The rewritten standardization problem is clear: the goal is to query orders, the output fields must include order number and amount, and the filtering conditions are time and status.

[0074] Subsequently, the standardized question is input into a pre-defined large language model (such as GPT-4) for hypothetical reasoning. Hypothetical reasoning refers to having the large model pretend to have already retrieved the data and describe it. For example, given the input "check inventory", the large model infers: "Assuming the query returns an inventory table, it should contain fields such as product ID, inventory quantity, and warehouse location."

[0075] The large model does not directly generate SQL, but instead generates a hypothetical response in natural language. This response includes the key metrics for prediction, outcome features (such as data should be sorted by time), and field attributes (such as the inferred table should have user_id and order_amount fields).

[0076] For example, regarding the order issue mentioned above, the hypothetical response generated by the large model might be: "The query results are expected to include fields such as order ID, refund amount, and application time, and the order status column should be marked as Refund_Processing, with the data sourced from the order details table." Although this response is predictive, it contains rich database terminology, providing an excellent anchor for subsequent retrieval.

[0077] Step S302: Based on the prediction result description information, determine the target reference information that matches the query question in the preset knowledge base; the preset knowledge base includes at least one of the following: metadata information describing the target database structure and historical data query records.

[0078] In this step, the pre-defined knowledge base is a comprehensive information repository used to store various contexts that assist in the generation of knowledge. It includes not only metadata information (such as the field amt in the table t_sales), but also historical data query records, industry domain knowledge (such as the return_rate field corresponding to "ROI"), and system-predefined knowledge (such as the system variable @@version).

[0079] The target reference information is the most relevant table structure or historical experience retrieved from it.

[0080] The "confirm" action employs a hybrid retrieval strategy: simultaneously using the predicted result description information for vector semantic matching (finding those with similar meanings) and keyword exact matching (finding those with the same name), and weighting and sorting the results of both based on a preset weight strategy, thereby accurately locking onto the target.

[0081] Vector semantic matching refers to calculating the vector similarity between the predicted result's descriptive information and entries in the knowledge base. For example, if the predicted information contains "sales amount," it can match a field in the knowledge base described as "transaction amount," even though the words are different, because their vector distances are close. Keyword exact matching refers to traditional string matching. For example, directly matching "sales" in a table name.

[0082] To support this high-precision retrieval, the construction of the knowledge base is extremely crucial, specifically including: Metadata construction: Obtain the raw structure from heterogeneous data sources such as Hive and MySQL, convert it into a unified intermediate format (such as standard JSON format), and establish a mapping with business semantic information. Business semantic information refers to the business interpretation of physical fields, such as business indicator definitions (active means more than 3 logins), data tags (core tables), and subject domain divisions (financial domain).

[0083] Metadata knowledge, in addition to basic physical information (such as database names and table names), supplements business semantic information. This includes table descriptions, used to explain the meaning of tables; table hierarchies, used to identify the depth of data processing; and table subject areas, used to define business affiliation. This information helps large models transform obscure code symbols into concrete business concepts.

[0084] Historical data query record construction: The SQL extraction process employs a reverse tracing strategy based on data popularity. First, the core hot tables with the highest reading frequency in the platform are identified. Then, all scheduled tasks for reading these tables are traced in reverse. Finally, the scripts of these tasks are parsed to accurately extract reusable SELECT query fragments and their corresponding use cases.

[0085] For example, there is a record where, when calculating repurchase rate, the SQL fragment "count(distinctuser_id)... group by month" is typically used.

[0086] Industry and system knowledge: The database also includes pre-defined industry-specific knowledge (such as the calculation formula for the financial term ROE) and system-specific knowledge (such as the date function date_sub specific to the current database engine).

[0087] For example, the process of generating knowledge base content may include: identifying target data tables that have been read more than a preset number of times (such as 100 times), extracting their historical query statements, parsing query fragments (such as a complex join logic) and usage scenario descriptions (such as for calculating monthly reports), and storing them in the knowledge base.

[0088] This step is the precise knowledge retrieval process. Using the prediction results obtained in step S301 as clues, the system searches within a pre-defined knowledge base. The retrieval process employs a dual-path strategy: on one hand, vector semantic matching is performed to capture potential associations; on the other hand, precise keyword matching is performed to ensure accuracy. Based on a pre-defined weighting strategy, the results from both paths are weighted and ranked, and the target reference information with the highest score is selected. This reference information may be a table (derived from metadata information, whose construction involves obtaining the original table structure from heterogeneous data sources and associating it with business semantic information), or a similar query fragment (derived from historical data query records, whose construction is based on parsing and extracting scenarios from historical query statements of high-frequency target data tables). Furthermore, the retrieval may also involve industry domain knowledge and system-predefined knowledge to aid in understanding specific terminology.

[0089] Step S303: Based on the query question and target reference information, generate the target data query statement.

[0090] This step is the large model generation process. Before invoking the large model generation, a resource allocation strategy is executed first. The resource allocation strategy refers to the rules for allocating resources based on tenant level. Example: Department A uses a high-precision GPT-4 model, while Department B uses a fast-responding lightweight model.

[0091] Parse the tenant identity identifier in the request (referring to the ID that distinguishes different user departments. Example: Tenant_A represents department A, Tenant_B represents department B). Assuming that the parsed tenant identity identifier means that the user belongs to department A, determine to use a higher precision target large language model (such as GPT-4 version) and configure specific model parameters (such as setting a lower randomness parameter to ensure rigor, and setting a longer maximum generation length to accommodate complex SQL).

[0092] After configuration, the generation logic is executed, which includes: Step S3031: Inject the query question and target reference information into the preset prompt word template to obtain the target prompt word; the preset prompt word template includes preset role definitions, task execution process constraints and output structure specifications.

[0093] A prompt template is a pre-designed fill-in-the-blank text framework. For example, it might include phrases like, "You are a data expert (role definition). Please refer to the following table structure (reference information) and generate SQL for the following question (query question). The use of DELETE statements is prohibited (process constraints)."

[0094] Injection refers to filling dynamic information into the prompt word template. For example, the table structure found in step S302 is filled into the context area of ​​the prompt word template.

[0095] Step S3032: Input the target prompt words into the preset large language model to generate the target data query statement.

[0096] This step involves simultaneously injecting the query question determined in step S301 and the target reference information determined in step S302 into a preset prompt template to generate target prompts. This template clearly defines the roles, task execution flow constraints, and output structure specifications. Finally, the target prompts are input into the selected target large language model to obtain the final target data query statement.

[0097] Furthermore, after generating the target data query statement, it does not return it directly. Instead, it performs syntax validation (checking if it conforms to SQL standards) and business logic validation (such as checking if it references tables without sufficient permissions). If the validation passes, a generation log is recorded, especially user adoption behavior (user clicked "run") or evaluation data (user liked or modified the SQL).

[0098] Finally, the knowledge base is updated based on these logs. For example, the corrected SQL statements corrected by the user are stored as new historical data query records, so that the knowledge base can continuously optimize itself as it is used, and further improve the accuracy of subsequent generation.

[0099] In summary, the data query statement generation method provided in this embodiment constructs a complete intelligent processing solution from user natural language input to high-quality data query statement output. First, by introducing hypothetical reasoning and semantic disambiguation mechanisms, it effectively solves the semantic gap between natural language and structured databases, significantly improving the accuracy of understanding user intent. Second, by constructing a multi-dimensional integrated knowledge base including metadata, historical records, and industry knowledge, and employing a multi-way weighted recall strategy, it ensures that the generation process has accurate and rich contextual basis, solving the problem of inaccurate mapping of complex business logic. Finally, combined with multi-tenant resource management and a feedback loop mechanism, it achieves flexible deployment and self-updating and improvement. This embodiment can efficiently, accurately, and securely automatically generate data query statements that conform to business logic in complex heterogeneous data environments.

[0100] To better illustrate the data query statement generation method of the embodiments, a preferred embodiment will be provided below. This embodiment is intended to describe the implementation process of this disclosure in detail, but is not intended to limit the scope of protection of this disclosure.

[0101] This embodiment takes a lakeware architecture as an example and proposes a SQL generation system for such architectures. This system aims to solve technical challenges such as chaotic AI resource management in multi-tenant environments, difficulty in unifying knowledge from heterogeneous data sources, and low SQL generation accuracy in complex scenarios.

[0102] Figure 4 This is a schematic diagram of the overall architecture of the SQL generation system in this embodiment, as shown below. Figure 4 As shown, it is divided into three core layers from bottom to top: the infrastructure layer, the business layer, and the application layer.

[0103] Application Layer: Located at the top of the architecture, it directly faces various business subsystems. Each business line builds specific intelligent functions (such as intelligent SQL generation) based on standardized interfaces and is responsible for the construction and maintenance of related knowledge. In addition, the application layer also establishes a stable long-term connection channel through the WebSocket (a network communication protocol that allows clients and servers to establish a persistent bidirectional data channel for real-time and efficient interaction) real-time communication mechanism. This allows for real-time collection and periodic summarization of the usage of specific intelligent functions (such as model call counts, token consumption, user adoption rate, likes / dislikes feedback, etc.), providing data support for subsequent model tuning and function optimization.

[0104] Business Layer: As the core control plane of the system, it undertakes three core functions: AI resource management, knowledge base management, and AI function management. Through a two-way mapping mechanism between resources and business operations, it achieves precise matching and flexible scheduling of underlying AI resources to upper-layer business operations.

[0105] The infrastructure layer, serving as the system's underlying support, adopts a plug-in architecture combined with an abstraction layer. It encapsulates LLM interfaces from multiple vendors (such as OpenAI and Claude) and AI development platform interfaces like Dify and Coze, and standardizes the protocol through the MCP Client (Model Context Protocol Client). This design shields the underlying technologies from differences, allowing upper-layer business logic to focus on the implementation details of specific models.

[0106] The core function of this embodiment is intelligent SQL generation, whose execution process covers the entire process from user natural language input to final SQL output, and is mainly scheduled by the business layer.

[0107] Before specific business processing, the AI ​​resource management module at the business layer uniformly allocates resources such as LLM, AI APP, and MCP. For multi-tenant scenarios, the system implements a dynamic resource scheduling strategy, allocating specific model resources and quotas based on tenant ID. Simultaneously, the client management module maintains an independent client connection pool. The system employs a dynamic connection pool management mechanism, supporting two refresh strategies: Active refresh: Automatically detects and refreshes client connections that have been idle for more than 5 minutes.

[0108] Passive refresh: When the system administrator updates the model parameters (such as API Key changes) through the console, the system notifies the connection pool through an event-driven mechanism, marks the old connection as invalid, and automatically rebuilds it on the next call, thus realizing hot update of the configuration.

[0109] High-quality SQL generation relies on a rich knowledge base. This embodiment defines and manages six core knowledge categories: table metadata, field metadata, SQL fragments, industry knowledge, system knowledge, and case knowledge.

[0110] Table metadata knowledge includes table name, description, and data governance information (such as hierarchy and tags).

[0111] Field metadata knowledge: including field names, types, business metrics, data dictionaries, etc.

[0112] SQL Fragment Knowledge: High-quality query logic extracted from historical high-frequency tasks.

[0113] Industry knowledge: Terminology mapping for specific fields (such as ROE in the financial field and BMI in the medical field).

[0114] System knowledge: Specific parameters built into the platform (such as time parameter macros).

[0115] Case study knowledge: Solutions to similar historical problems and common error correction methods.

[0116] Among these, the construction process of SQL fragment knowledge is particularly crucial, and its construction process is as follows: Figure 5 As shown, the process includes the following: First, the system identifies a set of target data tables with high popularity.

[0117] Secondly, find all historical SQL queries that have read these tables.

[0118] Finally, the system uses a large model to parse and extract these historical SQL statements, identifying reusable SELECT segments (excluding irrelevant logic) and automatically summarizing their usage scenarios (e.g., for calculating daily active users over the past 7 days), storing them in a knowledge base. Furthermore, for heterogeneous data sources, the system establishes a unified abstraction layer, transforming scattered table structures and field attributes into a consistent JSON format and integrating business metrics, data tags, and other business semantic information.

[0119] The content in the SQL fragment knowledge base is abstracted and summarized into structured information in four dimensions: First, overview information, which clarifies the underlying data tables involved and the overall goal of data acquisition; second, granularity and logic, which defines the statistical subject of the data (such as user + song dimensions), key indicator attributes, and filtering conditions for multi-table joins; third, scenarios and value, which explain the specific intent of the analysis and the supported business applications (such as strategy optimization and behavior analysis); and finally, SQL entities, which store reusable code fragments containing core calculation logic (such as aggregate functions and field processing).

[0120] The SQL generation process in this embodiment is not merely a simple translation, but a complex process involving intent enhancement and multi-path recall. The core process of SQL generation is as follows: Figure 6 As shown, it mainly includes the following steps: Phase 1: Problem rewriting and hypothetical reasoning.

[0121] To address the issues of vague user queries (such as searching for recent data) and semantic space misalignment, this embodiment implements a question rewriting and hypothetical reasoning process. The detailed process of question rewriting and hypothetical reasoning is as follows: Figure 7 As shown, the following operations are included: Rewriting the question: First, determine whether the user's question needs to be rewritten (e.g., if there is ambiguity, vagueness, or missing conditions). If so, rewrite it based on a preset template into a standardized question that includes the target requirements, output fields (automatically complete implicit metrics, such as completing "daily active users" into a specific field), and filtering conditions (clearly defining the time granularity).

[0122] Hypothetical reasoning: This involves a large model generating a hypothetical response based on a rewritten question. This response includes predicted table structure features, key metrics, and even a hypothetical description of the query results. It's important to note that this response serves only as an intermediate variable in the retrieval process, transforming the user's vague query intent into a structured document description. This aligns semantically with the metadata in the knowledge base, significantly improving retrieval accuracy.

[0123] Phase Two: Multi-channel Knowledge Recall.

[0124] The system employs a multi-path recall strategy, utilizing rewritten questions and hypothetical responses to execute parallel operations within the knowledge base: semantic recall, which retrieves relevant tables, fields, and cases based on vector similarity; and keyword recall, which precisely matches table names or column names. The system then reorders the results according to a preset weighting strategy (such as prioritizing semantic matching or keyword matching) to select the most relevant knowledge entities.

[0125] Figure 7 Parallel recall strategies for different types of knowledge are demonstrated, including: Path 1: Semantic Vectorization Computation. This primarily targets table metadata, field metadata, and SQL fragment knowledge. The "rewrite question" and "reasoning response" are converted into vectors and semantically matched with the knowledge base. For example, for table metadata and field metadata, the top 10 documents with the highest similarity are extracted; for SQL fragments, the top 3 documents are extracted.

[0126] Path Two: Keyword Extraction. This primarily targets time-related knowledge, case-related knowledge, and supplementary metadata retrieval. Key entities (such as "2024" and "DAU") are extracted from the questions. For both time-related and case-related knowledge, the top 3 documents are extracted respectively.

[0127] Figure 7 The document also demonstrates how to clean and optimize the selected fragmented knowledge to ultimately form high-quality reference information, specifically including: For each retrieved data (table, field, SQL fragment, time, case), deduplication and reordering are performed separately.

[0128] For table metadata, the top 3 after re-sorting are selected. For field metadata, the top 10 after re-sorting are selected. For SQL fragments, time-related knowledge, and case knowledge, the top 3 after re-sorting are all selected.

[0129] The system does not simply list data; it also incorporates weight calculations to merge the recall results of tables and fields.

[0130] The weight of table metadata is set to 0.6, and the weight of field metadata is set to 0.4. The retrieved table metadata is then calculated and merged using a weighted average.

[0131] The system filters field data based on the retrieved table data. This means that if a field name has a high match, but its table was not retrieved, that field will also be filtered out.

[0132] Figure 7The final output is a carefully selected, structured knowledge package, including: knowledge of recalled table and field metadata, knowledge of recalled SQL fragments, knowledge of recalled time information, and knowledge of recalled case information. This information will then be used to guide the large model in generating the final SQL.

[0133] Phase 3, SQL generation.

[0134] When generating the final SQL, the system dynamically configures the model based on the multi-tenant characteristics, adjusting parameters such as Temperature (randomness), topK, and maxTokens according to the current tenant's configuration information.

[0135] Construct baseline prompts, inject role settings, task constraints, and output formats, and embed the recalled six-dimensional knowledge (especially industry knowledge and business metrics) as context to ensure that the generated SQL conforms to both syntax and business logic.

[0136] The generated SQL will be checked by a syntax parser and business logic validation rules before being output, intercepting potential errors.

[0137] Through the above implementation methods, this embodiment achieves end-to-end optimization from underlying resource isolation to upper-layer business semantic alignment, effectively solving the knowledge retrieval problem in the lake warehouse integrated scenario and significantly improving the accuracy and usability of SQL generation.

[0138] This embodiment also provides a data query statement generation device, which is used to implement the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can be a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0139] This embodiment provides a data query statement generation device, such as... Figure 8 As shown, it includes: The receiving module 801 is used to determine the query question and the corresponding prediction result description information based on the received natural language query request; the prediction result description information is used to characterize the query features contained in the predicted query results for the query question. The lookup module 802 is used to determine target reference information that matches the query question in a preset knowledge base based on the prediction result description information; the preset knowledge base includes at least one of metadata information describing the target database structure and historical data query records. The generation module 803 is used to generate a target data query statement based on the query question and target reference information.

[0140] In one alternative implementation, the receiving module 801 is configured to: Semantic disambiguation is performed on natural language query requests to generate standardized query questions that conform to preset specifications; The standardized query question is input into a pre-defined large language model for hypothetical reasoning to obtain a hypothetical response, which is then used as the description information for the prediction result. The hypothetical response includes the key prediction indicators, prediction result features, and prediction field attributes for the standardized query question.

[0141] In one alternative implementation, the receiving module 801 is configured to: Determine whether the natural language query request meets the preset rewriting conditions; the preset rewriting conditions include at least one of the following: vague expression, semantic ambiguity, lack of key filtering conditions, and non-compliance with business semantic specifications. If the preset rewriting conditions are met, the natural language query request is rewritten based on the preset question template to generate a standardized query question. The data structure of the standardized query question includes a description of the requirement target, a list of output fields, and filter condition fields.

[0142] In one alternative implementation, the lookup module 802 is used for: Based on the information described by the prediction results, vector semantic matching and keyword precision matching are performed in the knowledge base; Based on a preset weighting strategy, the semantic matching results and keyword matching results are weighted and sorted to determine the target reference information.

[0143] In one alternative embodiment, the device further includes: Module 804 is used to obtain the target data table; the target data table is a data table in the target database that has been read more than a preset number of times. Based on the target data table, identify the historical query statements that have read the target data table; Parse historical query statements to extract query fragments and descriptions of the corresponding use cases; The query fragments and usage scenario descriptions are stored as historical data query records in the knowledge base.

[0144] In one alternative implementation, the construction module 804 is configured to: Retrieve the original table structure information and field attribute information from heterogeneous data sources; Convert the original table structure information and field attribute information into a unified intermediate format data; Obtain business semantic information and establish an association mapping between intermediate format data and business semantic information to generate metadata information; business semantic information includes at least one of business indicator definitions, data tags, and subject domain divisions.

[0145] In one optional implementation, a target data query statement is generated based on the query question and target reference information, including: The query question and target reference information are injected into a preset prompt word template to obtain the target prompt word; the preset prompt word template includes preset role definitions, task execution process constraints and output structure specifications; Input the target prompts into a pre-defined large language model to generate a target data query statement.

[0146] In one alternative implementation, the generation module 803 is used for: Parse the tenant identity identifier in a natural language query request; Based on the tenant's identity, the target large language model and its corresponding model parameter configuration are determined from the preset resource configuration strategy; the model configuration parameters include at least one of the following: randomness parameter, number of candidate words, and maximum generation length. The target large language model is invoked based on the model parameter configuration.

[0147] In one alternative implementation, the generation module 803 is used for: Perform syntax and business logic validation on the target data query statement; If the verification passes, record the generation log of the target data query statement; the generation log includes the adoption behavior or evaluation data of the target data query statement; The knowledge base is updated based on the generated logs.

[0148] The data query statement generation apparatus provided in this disclosure can execute the data query statement generation method provided in any embodiment of this disclosure, and has the corresponding functional modules and beneficial effects of the method execution. Further functional descriptions of the various modules and units described above are the same as in the corresponding embodiments described above, and will not be repeated here.

[0149] Figure 9 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this disclosure.

[0150] The following is a detailed reference. Figure 9The diagram illustrates a structural schematic suitable for implementing an electronic device according to embodiments of the present disclosure. The electronic device may include a processor (e.g., a central processing unit, graphics processor, etc.) 901, which can perform various appropriate actions and processes according to a program stored in read-only memory (ROM) 902 or a program loaded from memory 908 into random access memory (RAM) 903. The RAM 903 also stores various programs and data required for the operation of the electronic device. The processor 901, ROM 902, and RAM 903 are interconnected via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0151] Typically, the following devices can be connected to I / O interface 905: input devices 906 including, for example, touchscreens, touchpads, keyboards, mice, cameras, microphones, accelerometers, gyroscopes, etc.; output devices 907 including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; memory devices 908 including, for example, magnetic tapes, hard disks, etc.; and communication devices 909. Communication device 909 allows electronic devices to exchange data via wireless or wired communication with other devices. Although Figure 9 Electronic devices with various devices are shown, but it should be understood that it is not required to implement or have all of the devices shown, and more or fewer devices may be implemented or have instead.

[0152] In particular, according to embodiments of this disclosure, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments of this disclosure include a computer program product comprising a computer program carried on a non-transitory computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device 909, or installed from a memory 908, or installed from a ROM 902. When the computer program is executed by the processor 901, it performs the functions defined in the data query statement generation method of embodiments of this disclosure.

[0153] Figure 9 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of the embodiments disclosed herein.

[0154] This disclosure also provides a computer-readable storage medium in which the methods described in this disclosure can be implemented in hardware or firmware, or implemented as recordable on a storage medium, or implemented as computer code downloaded over a network and originally stored on a remote storage medium or a non-transitory machine-readable storage medium and subsequently stored on a local storage medium. Thus, the methods described herein can be processed by software stored on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. The storage medium can be a magnetic disk, optical disk, read-only memory, random access memory, flash memory, hard disk, or solid-state drive, etc.; further, the storage medium may also include combinations of the above types of memory. It is understood that computers, processors, microprocessor controllers, or programmable hardware include storage components capable of storing or receiving software or computer code. When the software or computer code is accessed and executed by the computer, processor, or hardware, the data query statement generation method shown in the above embodiments is implemented.

[0155] A portion of this disclosure can be applied to computer program products, such as computer program instructions, which, when executed by a computer, can invoke or provide methods and / or technical solutions according to this disclosure through the operation of the computer. Those skilled in the art will understand that the forms in which computer program instructions exist in a computer-readable medium include, but are not limited to, source files, executable files, and installation package files. Accordingly, the ways in which computer program instructions are executed by a computer include, but are not limited to: the computer directly executing the instructions; the computer compiling the instructions and then executing the corresponding compiled program; the computer reading and executing the instructions; or the computer reading and installing the instructions and then executing the corresponding installed program. Here, the computer-readable medium can be any available computer-readable storage medium or communication medium accessible to a computer.

[0156] Although embodiments of the present disclosure have been described in conjunction with the accompanying drawings, those skilled in the art can make various modifications and variations without departing from the spirit and scope of the present disclosure, and such modifications and variations all fall within the scope defined by the appended claims.

Claims

1. A method for generating data query statements, characterized in that, The method includes: Based on the received natural language query request, the query question and the corresponding prediction result description information are determined; the prediction result description information is used to characterize the query features contained in the predicted query result for the query question. Based on the prediction result description information, target reference information matching the query question is determined in a preset knowledge base; the preset knowledge base includes at least one of metadata information describing the target database structure and historical data query records. Based on the query question and the target reference information, a target data query statement is generated.

2. The method according to claim 1, characterized in that, The step of determining the query question and the corresponding prediction result description information based on the received natural language query request includes: The natural language query request is semantically disambiguated to generate a standardized query question that conforms to a preset specification; The standardized query question is input into a preset large language model for hypothetical reasoning to obtain a hypothetical response, and the hypothetical response is determined as the prediction result description information; the hypothetical response includes the prediction key indicators, prediction result features and prediction field attributes for the standardized query question.

3. The method according to claim 2, characterized in that, The semantic disambiguation processing of the natural language query request includes: Determine whether the natural language query request meets preset rewriting conditions; the preset rewriting conditions include at least one of the following: vague expression, semantic ambiguity, lack of key filtering conditions, and non-compliance with business semantic specifications. If the preset rewriting conditions are met, the natural language query request is rewritten based on the preset question template to generate the standardized query question; the data structure of the standardized query question includes a requirement target description, an output field list, and filter condition fields.

4. The method according to claim 1, characterized in that, The step of determining target reference information matching the query question in a preset knowledge base includes: Based on the prediction results and description information, vector semantic matching and keyword precision matching are performed in the knowledge base. Based on a preset weighting strategy, the semantic matching results and keyword matching results are weighted and sorted to determine the target reference information.

5. The method according to claim 1, characterized in that, The steps for constructing historical data query records in the preset knowledge base include: Obtain the target data table; the target data table is a data table in the target database that has been read more than a preset number of times. Based on the target data table, determine the historical query statements that have read the target data table; The historical query statements are parsed to extract query fragments and descriptions of the usage scenarios corresponding to the query fragments; The query fragment and the description of the usage scenario are stored as historical data query records in the knowledge base.

6. The method according to claim 1, characterized in that, The steps for constructing metadata information in the preset knowledge base include: Retrieve the original table structure information and field attribute information from heterogeneous data sources; The original table structure information and field attribute information are converted into a unified intermediate format data; Obtain business semantic information and establish an association mapping between the intermediate format data and the business semantic information to generate the metadata information; the business semantic information includes at least one of business indicator definitions, data tags, and subject domain divisions.

7. A data query statement generation device, characterized in that, The device includes: The receiving module is used to determine the query question and the prediction result description information corresponding to the query question based on the received natural language query request; the prediction result description information is used to characterize the query features contained in the predicted query results for the query question. The search module is used to determine target reference information that matches the query question in a preset knowledge base based on the prediction result description information; the preset knowledge base includes at least one of metadata information describing the target database structure and historical data query records. The generation module is used to generate a target data query statement based on the query question and the target reference information.

8. An electronic device, characterized in that, include: A memory and a processor are communicatively connected, the memory stores computer instructions, and the processor executes the computer instructions to perform the data query statement generation method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions for causing a computer to execute the data query statement generation method according to any one of claims 1 to 6.

10. A computer program product, characterized in that, Includes computer instructions for causing a computer to execute the data query statement generation method according to any one of claims 1 to 6.

Images