A Semantic-Aware Method for Automatic Generation of Database Query Logs

Abstract

This invention discloses a semantically aware method for automatically generating database query logs, belonging to the interdisciplinary field of database technology and natural language processing. The method pre-analyzes the metadata of the target database to construct a metadata database, and simultaneously statistically analyzes the distribution of query features from public benchmarks or real logs to form a feature template library. Based on the metadata database and the feature template library, simulated SQL logs conforming to business semantics are generated. The generated SQL undergoes syntax validation, business rationality filtering, and diversity deduplication. The SQL is executed on the target database; successfully executed statements are back-translated to generate natural language questions, and failed statements are analyzed for error types for optimization, forming a paired dataset. This invention can automatically generate high-quality simulated query logs, solving the problem of missing real logs in industrial scenarios and improving the query accuracy and robustness of Text-to-SQL models.

Description

Technical Field

[0001] This invention belongs to the interdisciplinary field of database technology and natural language processing, and specifically relates to a method for automatically generating database query logs based on semantic awareness. Background Technology

[0002] With the deepening of enterprise digital transformation, database systems, as a core component of information infrastructure, bear the responsibility of storing, managing, and analyzing massive amounts of business data. Efficiently retrieving information from databases has become crucial for improving enterprise decision-making efficiency. Text-to-SQL (TTO) technology aims to automatically convert users' natural language questions into executable SQL queries through semantic parsing, thereby lowering the barrier to database use and enabling business personnel without professional programming skills to easily explore and analyze data. In recent years, with the rapid development of deep learning and Large Language Models (LLM), TTO technology has made significant progress, achieving breakthrough results in public benchmarks such as Spider and WikiSQL. However, when these models are deployed in real-world industrial scenarios, their performance often drops drastically. Research shows that even advanced models like GPT-4 achieve an average accuracy reduction of over 30% on real enterprise-level logs compared to the Spider benchmark. The core reason for this huge gap lies in the fact that real-world database environments are far more complex than academic benchmarks: database schemas are massive, often containing hundreds of fields; business query logic is complex, involving advanced syntax such as multi-table joins, window functions, and nested subqueries; and at the same time, natural language expressions are often ambiguous and contain referential ambiguities. All these factors combined make it difficult for existing Text-to-SQL models to accurately understand user intent and generate correct SQL code.

[0003] To improve the robustness and accuracy of Text-to-SQL models in real-world scenarios, academia and industry have begun exploring the use of metadata to enhance semantic parsing capabilities. By mining database metadata, such as business constraints between fields, relationships between tables, and the distribution characteristics of field values, rich contextual knowledge can be provided to Text-to-SQL models, helping them to more accurately understand database structure and business logic. Among various metadata sources, query logs are considered one of the most valuable pieces of information. Real query logs record the historical interactions between users and the database, containing implicit knowledge such as expert-level query patterns, frequently used table combinations, and common business metric calculation methods. By analyzing query logs, valuable metadata can be automatically extracted for optimizing the hint engineering or fine-tuning process of Text-to-SQL models. Existing research has confirmed that automatic metadata extraction technology combined with query log analysis can achieve state-of-the-art performance on authoritative benchmarks such as BIRD. However, a serious practical problem is that high-quality query logs are lacking in most real-world industrial scenarios, especially in the databases of small and medium-sized enterprises. On the one hand, due to considerations of data security, storage costs, and privacy protection, production databases typically do not retain complete query histories for extended periods. On the other hand, many legacy or newly built systems do not have logging functionality enabled. This leaves metadata mining technology facing a "no rice to cook" dilemma.

[0004] One intuitive solution to the problem of missing logs is to manually construct query logs. However, this approach is extremely costly and difficult to scale. Taking the industry-recognized Text-to-SQL benchmark BIRD as an example, its 12,000 SQL-question pairs are time-consuming, labor-intensive, and cost nearly $100,000, with limited coverage of database domains. Manual construction not only requires deep involvement from domain experts but also struggles to guarantee log diversity and comprehensive coverage of real-world business scenarios. Therefore, there is an urgent need for an automated, low-cost, and scalable technology to generate high-quality simulated query logs for databases lacking real logs.

[0005] In the field of automated testing, fuzzing techniques are widely used for vulnerability detection and stability testing of database management systems. Traditional database fuzzing tools, such as SQLsmith and SQLancer, trigger internal errors or security vulnerabilities in the database system by randomly generating a large number of SQL statements. These tools can guarantee the syntactic correctness of the generated SQL, but the queries they generate often lack business semantics—they may arbitrarily concatenate irrelevant tables, use meaningless calculations (such as summing user IDs), or filter values ​​outside a reasonable range. This kind of "syntactically valid but business-meaningless" random SQL cannot simulate the query behavior of real experts, and therefore cannot be directly used as a source of logs for metadata mining. In recent years, researchers have begun to explore more intelligent fuzzing methods; however, the goal of this work is still to uncover vulnerabilities in the underlying code of the database system. Although the generated SQL has a certain degree of semantic correctness, it still differs significantly from the characteristics of real business queries (such as aggregation statistics, trend analysis, and business indicator calculations). In other words, existing fuzzing techniques focus on "system robustness" rather than "business authenticity."

[0006] In summary, existing technologies suffer from the following shortcomings: First, the performance of Text-to-SQL models is limited in practical applications due to a lack of business context understanding, necessitating high-quality query logs for metadata enhancement. Second, query logs are generally lacking in real-world industrial scenarios, and manual construction is costly and difficult to promote. Third, while traditional SQL fuzzing techniques can automatically generate a large number of SQL queries, these queries lack business logic constraints and fail to reflect the characteristic distribution of real expert queries, thus rendering them unsuitable for metadata mining. Therefore, how to automatically generate simulated SQL logs that conform to grammatical specifications, possess business semantics, and cover the characteristic distribution of real queries has become a pressing technical challenge for the implementation of Text-to-SQL technology and intelligent database operation and maintenance. Summary of the Invention

[0007] This invention aims to address the problem in existing technologies where the widespread lack of real database query logs hinders the effective implementation of log-based metadata mining techniques, thus limiting the performance improvement of Text-to-SQL models in industrial scenarios. To this end, a semantically aware method and system for automatically generating database query logs are proposed. This method pre-analyzes the schema information and data distribution of the target database, constructs semantic constraint templates based on the feature distribution of real queries, and employs a strategy combining hierarchical generation and constraint sampling to generate simulated SQL logs that conform to grammatical rules, possess business semantics, and cover the feature distribution of real queries. The generated logs can be used for automatic metadata mining and ultimately used to improve the accuracy and robustness of Text-to-SQL models, forming a complete closed loop of "generation—mining—application." To achieve the above objectives, this invention provides the following technical solution:

[0008] I. Pre-analysis of database metadata

[0009] This module performs multi-dimensional analysis of the target database before generating simulated logs, extracting necessary semantic constraints for subsequent generation to ensure that the generated SQL reflects the actual business meaning of the database. Specifically, it includes the following sub-steps:

[0010] 1. Pattern Information Extraction

[0011] The system automatically connects to the target database and obtains its complete schema information, including all table names, field names, field data types, primary and foreign key constraints, index information, and field comments. For field comments, natural language processing is used for preliminary parsing to identify any potentially hidden business meanings. The extracted information is stored in a structured format, forming a schema knowledge base.

[0012] 2. Field value distribution statistics

[0013] Statistical analysis of data distribution characteristics is performed on each field to provide a basis for generating reasonable filtering conditions. Specifically, this includes:

[0014] Numeric fields: Calculate the minimum, maximum, mean, standard deviation, and common value ranges, and record the percentage of null values.

[0015] Date / Time Fields: Statistical time range, common time granularities, and possible periodic patterns.

[0016] Text fields: If the text is short and the cardinality is low, it can be processed similarly to enumeration; if the text is long, it will not be included in the condition generation range for the time being, but its existence can be recorded.

[0017] The statistical results are stored in the metadata database with the fields as keys, which is used to guide the value sampling when generating conditional expressions.

[0018] 3. Mining relationships between tables

[0019] The system extracts direct joins between tables based on explicitly defined foreign key constraints in the database. For fields without defined foreign keys but with similar names (e.g., "user_id" in the order table and "id" in the user table), it further infers potential joins by combining the data types of the fields and the degree of overlap in their value distributions (e.g., calculating the Jaccard similarity of two field values, i.e., the proportion of identical values ​​in two fields). This identifies possible join paths between tables; for example, the order table can be connected to the user table via "user_id," and the user table can be connected to the region table via "region_id," forming a multi-table join chain. All discovered relationships are constructed and stored as a graph structure as follows: a directed graph is created, with each table in the database as a node; for each identified relationship, a directed edge is added between the corresponding nodes in the two tables, with the direction of the edge determined by the dependency of the join fields (e.g., from the table containing the foreign key to the table containing the primary key); each edge stores the following attributes: join field pair, join type, and confidence score. Once constructed, the graph structure is persistently stored for use in subsequent multi-table join queries. In practice, high-confidence edges are selected based on the confidence threshold, or weighted sampling is performed using confidence as the weight when selecting table combinations in a random walk.

[0020] II. Summary of Characteristics of Real Query Logs

[0021] This module is used to statistically analyze the core feature distribution of real expert queries from publicly available Text-to-SQL benchmarks (such as Spider, BIRD, and WikiSQL) or a small amount of real logs provided by users, forming a feature sampling template for the generation stage. The statistical dimensions include three aspects: syntax structure, business logic, and join patterns. The specific steps are as follows:

[0022] 1. Statistics on syntactic structural features

[0023] For each SQL statement in the benchmark set, perform Abstract Syntax Tree (AST) parsing to extract the frequency of occurrence of the following structural features:

[0024] Clause types: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, etc.

[0025] Subqueries: scalar subqueries, derived tables, EXISTS / IN subqueries, etc., and statistics on the proportion of different types of subqueries.

[0026] 2. Business Logic Feature Statistics

[0027] Analyze the fields and tables involved in the query, and summarize common business semantic patterns:

[0028] Aggregated metrics mode: Calculates common aggregated metrics in the SELECT clause, such as "total sales", "average unit price", "number of users", etc., and records the corresponding field combinations and aggregate functions.

[0029] Grouping Dimension Mode: Statistically analyzes commonly used grouping fields in the GROUP BY clause, such as "Region" and "Product Category".

[0030] Sorting requirement pattern: Analyze the sorting fields and sorting direction in the ORDER BY clause, such as sorting by sales revenue in descending order or by time in ascending order.

[0031] 3. Connection mode feature statistics

[0032] Analysis and statistics on multi-table join queries:

[0033] Connection types: INNER JOIN, LEFT / RIGHT JOIN, self-join, cross join, etc.

[0034] Complexity of connection conditions: whether it includes a computational expression, and whether it involves AND combinations of multiple conditions.

[0035] The statistical results are used to guide the selection of join methods when generating multi-table joins.

[0036] 4. Feature Distribution Quantization and Template

[0037] The statistical results above are transformed into programmable feature distribution vectors, and a feature template library is constructed. Each template contains a set of feature labels and their probability weights, such as template A: {"Contains GROUP BY": 0.6, "Contains window function": 0.1, "Equijoin": 0.8, ...} (In example template A, the number 0.6 represents the statistical frequency of SQL statements containing GROUP BY clauses in all queries in the actual query logs, i.e., the probability weight of this feature; the other numbers follow the same principle.). During the generation phase, random sampling will be performed based on these weights to ensure that the generated logs are consistent with the actual queries in terms of macro-statistics.

[0038] III. Semantic-Aware Targeted SQL Fuzzing Generation

[0039] This module is the core generation engine. Based on the metadata library and feature template library output by the first two modules, it employs a hierarchical generation and constrained sampling strategy to generate SQL statements that conform to business semantics and cover the real feature distribution. Specifically, it includes the following sub-steps:

[0040] 1. Generation of basic syntax tree structure

[0041] First, based on the distribution of syntax structures in the feature template library, a SQL syntax skeleton is randomly selected. The skeleton consists of AST node types, for example:

[0042] Simple query: SELECT {aggregate field?} FROM {table} WHERE {condition} GROUP BY {field}? ORDER BY {field}? LIMIT {number}?

[0043] Subquery: SELECT ... FROM (SELECT ...) AS tmp WHERE ...

[0044] Set query: SELECT ... UNION SELECT ...

[0045] The skeleton has pre-defined slots for filling (such as tables, fields, conditions, aggregate functions, etc.). The generation process ensures that the final SQL conforms to the ANSI SQL syntax specification.

[0046] 2. Semantic selection of tables and fields

[0047] Based on the table relationships and business annotations in the metadata database, priority is given to selecting table combinations that are related and frequently appear together in business contexts. The selection process employs a probabilistic graphical model: tables are treated as nodes, and the relationship strength (based on foreign keys or value overlap) is considered as edge weights. Two to four tables are selected through random walks or weighted sampling to form the FROM clause. Then, fields are selected from the chosen tables according to the following rules:

[0048] If the feature requires the inclusion of aggregate functions, then numeric fields should be preferred as the aggregation objects.

[0049] If features require grouping, then categorical fields or time fields with low cardinality should be preferred as grouping dimensions.

[0050] If the feature requires a window function, then select the appropriate type of field based on the typical pattern of the window function (such as partitioning or sorting).

[0051] 3. Instantiation of business constraints in conditional expressions

[0052] For the conditions in the WHERE clause, the field value distribution information in the metadata database is used to generate comparison values ​​that conform to the actual data distribution. Specific method:

[0053] For enumerated fields, a value is randomly selected from its list of values ​​according to frequency, such as gender = 'F'.

[0054] For numeric fields, a value is randomly generated within its value range according to a uniform or normal distribution, and can be truncated in combination with common business ranges (such as price > 0); range conditions (such as price BETWEEN 100 AND 500) can also be generated.

[0055] For date fields, relative date expressions are generated based on the time patterns summarized from business logic characteristics.

[0056] The comparison operators in the conditions are also randomly selected based on the feature distribution to ensure coverage of various types such as =, >, <, LIKE, and IN. Multiple conditions are combined using AND / OR, and the combination method also refers to the feature distribution.

[0057] 4. Rational generation of aggregation and computation logic

[0058] When the skeleton requires aggregation or calculation, it generates expressions that align with the business logic. For example:

[0059] Aggregate functions: SUM(pay_amount), COUNT(DISTINCT user_id), AVG(score), etc.

[0060] Arithmetic operations such as price * quantity and pay_amount - refund_amount must ensure that the data types of the fields involved in the operation are compatible and that the business logic is reasonable (e.g., avoid multiplying the amount by the ID).

[0061] Window functions: ROW_NUMBER() OVER (PARTITION BY category ORDER BY salesDESC), etc.

[0062] During generation, business annotations and common indicator patterns in the metadata database are referenced to ensure that the calculation logic has practical business meaning.

[0063] 5. Feature Distribution-Guided Sampling Control

[0064] In each generation step, random decisions are made by referencing the probability distribution in the feature template library. For example, a GROUP BY clause is added with a 60% probability, a window function with a 20% probability, and a subquery with a 35% probability. At the same time, rejection sampling is used to ensure that the generated SQL conforms to the preset feature distribution as a whole, avoiding excessive local deviations due to randomness.

[0065] IV. Filtering and Optimization of Log Generation

[0066] Because a small number of unreasonable or invalid SQL statements may still be generated during the targeted generation process, this module performs multiple rounds of filtering and optimization on the original generated results to ensure log quality. Specific steps:

[0067] 1. Syntax correctness check

[0068] Each generated SQL statement is parsed using a mature SQL parser (such as sqlparse or the syntax analyzer generated by ANTLR), and syntax errors are captured. SQL statements with syntax errors are discarded directly, and the error type is recorded for debugging purposes.

[0069] 2. Business rationality verification

[0070] The business rationale of SQL is determined using a rule engine or a lightweight machine learning model. The rules include:

[0071] Meaningless aggregations are prohibited, such as SUM or AVG calculations on primary key or ID fields.

[0072] Unreasonable filtering is prohibited: such as filtering conditions that exceed the field value range (e.g., sales volume > 10^9), and date filters that include future times.

[0073] Semantic conflicts are prohibited: such as WHERE conditions containing mutually exclusive conditions (e.g., gender='M' AND gender='F').

[0074] Connection condition validity: Ensure that the connection field types are consistent and that the connection conditions are complete.

[0075] Each SQL statement is scored according to rules, and those below the threshold are removed.

[0076] 3. Enhanced diversity and deduplication

[0077] The SQL statements processed in the first two steps are deduplicated based on similarity to avoid the logs containing a large number of structurally similar or semantically repetitive statements. The following method is used:

[0078] Structural similarity calculation based on AST: Parse SQL into AST, calculate edit distance or tree structure similarity, and set a threshold to merge SQL with excessive similarity.

[0079] Based on the execution result summary: Select a small amount of representative data to execute SQL, and compare the pattern of the result set (such as the number of columns, the number of rows, and the range of values). If the results are highly similar, they are considered redundant.

[0080] Retain the most representative SQL statements to ensure that logs cover different table combinations, syntax structures, and business scenarios.

[0081] 4. Log quality scoring and sorting

[0082] Each retained SQL statement is given a comprehensive quality score, calculated using a weighted summation method: Feature matching degree is determined by calculating the deviation between the actual features of the SQL statement and the distribution of features in the feature template library; the smaller the deviation, the higher the score. Complexity is scored based on the normalized number of syntax elements in the SQL statement, such as aggregate functions, JOIN counts, subquery depths, and window functions. Business clarity is scored based on the consistency between field comments and the operation type of that field in the SQL statement; for example, a bonus is added to a monetary field aggregated with SUM, while a deduction is added to an ID field aggregated with SUM. The comprehensive quality score = α × Feature matching degree score + β × Complexity score + γ × Business clarity score, where α, β, and γ are preset weight coefficients. The scoring results are used for subsequent annotation priority ranking and can be output as part of the metadata.

[0083] V. Execution Verification and Feedback Utilization of Simulated Logs

[0084] After generating high-quality simulated query logs, this module further verifies the generated SQL statements through actual execution and extracts valid information based on the execution results to form a paired dataset that can be used for Text-to-SQL optimization.

[0085] 1. SQL Execution and Log Collection

[0086] The generated SQL statements are executed sequentially on the target database, and execution log information such as the execution status (success or failure), execution time, number of rows and columns in the returned result set, and error messages (if execution fails) for each SQL statement is recorded.

[0087] 2. Reverse translation of correct execution results

[0088] For successfully executed SQL statements, the actual execution results are extracted from the execution log. Reverse translation technology is then used to convert the SQL and its execution results into corresponding natural language question descriptions. Reverse translation can employ template filling: based on the SQL's syntactic structure and the characteristics of the execution results, predefined natural language templates are filled to generate question statements such as "Statistics on sales revenue of each city in January 2024." Alternatively, a pre-trained SQL-to-Text model can be used for end-to-end semantic summarization. The generated (natural language question, SQL) paired data is stored in a database as high-quality training data for subsequent model fine-tuning or retrieval enhancement.

[0089] 3. Analysis of the use of erroneous execution results

[0090] For SQL statements that fail to execute, analyze the error type and cause. If the error type is a syntax error, record it and use it to optimize the syntax skeleton of the generation module. If the error type is a runtime error (such as a field not existing, type mismatch, etc.), analyze whether the error message reflects a problem at the business semantic level (such as filter conditions exceeding the field value range, incomplete join conditions, etc.). These errors can be used as negative samples to train the model's error detection capabilities, or as feedback information to guide the generation module to adjust parameters and improve the semantic correctness of subsequent generated SQL. Error results that have no useful value are discarded.

[0091] 4. Data Integration and Output

[0092] Successful pairing data is combined with valuable error feedback information to form the final output dataset. The pairing data is stored in JSON format and includes fields such as sql, question, execution_status, and result_metadata. Attached Figure Description

[0093] Figure 1 Database Analysis and Feature Induction Architecture Diagram

[0094] Figure 2 Semantic-aware targeted SQL fuzzing architecture diagram

[0095] Figure 3 Simulated log generation architecture diagram Detailed Implementation

[0096] The semantically aware SQL fuzzing test log generation method provided by this invention will be described in detail below with reference to specific embodiments. The method of this invention includes three main modules: a database analysis and feature summarization module, a semantically aware targeted SQL fuzzing generation module, and a simulated log generation module. Those skilled in the art should understand that the embodiments described herein are for illustrative purposes only and are not intended to limit the scope of protection of this invention.

[0097] Example Environment Configuration

[0098] This embodiment selects a typical e-commerce database as the target database, including a user table (user), an order table (order_info), a product table (product), and an order detail table (order_detail). The database uses MySQL 8.0 and stores approximately 100,000 user records, 500,000 order records, and 2 million order detail records. The goal of this embodiment is to generate 5,000 high-quality simulated query logs for this database, which will be used for subsequent metadata mining and Text-to-SQL model optimization.

[0099] The system was deployed on a Linux server configured with an Intel Xeon 2.3GHz CPU and 32GB of memory. Python 3.9 was used as the primary development language, and the dependent libraries included: pymysql (database connection), sqlparse (SQL parsing), numpy / pandas (data processing), and transformers (semantic vector generation).

[0100] Module 1: Database Analysis and Feature Induction

[0101] like Figure 1 As shown, this module comprises two core subtasks: database metadata pre-analysis and real query log feature summarization. First, it constructs a metadata database by connecting to the target database to extract schema information, statistically analyze field value distributions, and mine inter-table relationships. Then, it statistically analyzes the feature distributions of syntax structure, business logic, and join patterns from publicly available benchmarks or a small amount of real logs provided by users, forming a feature template library.

[0102] Algorithm Input: Database connection object db_conn, optional real SQL list real_sqls Algorithm Output: Meta-information dictionary meta_info, feature statistics dictionary feature_stats

[0103] Algorithm steps:

[0104] 1) Connect to the database, extract the field names, data types, comments, and foreign key constraints of all tables, and store them in meta_info.

[0105] 2) Perform value distribution statistics for each field: calculate the maximum, minimum, mean, and standard deviation for numeric fields; calculate the time range for date fields; and collect a list of high-frequency values ​​for enumeration fields.

[0106] 3) Infer the relationships between tables based on foreign keys and field naming similarity, calculate the confidence level, and store it in meta_info.

[0107] 4) If a real list of SQL statements is provided, the syntax features of each SQL statement (GROUP BY, window functions, subqueries, number of joins, etc.) are parsed, the frequency of each feature is counted, and feature_stats is generated; otherwise, the built-in default distribution is used.

[0108]

[0109] Module 2: Semantic-Aware Targeted SQL Fuzzing Generation

[0110] like Figure 2 As shown, this module, based on a metadata library and a feature template library, employs a hierarchical generation and constraint sampling strategy to generate SQL statements that conform to business semantics and cover the true feature distribution. The generation process includes: selecting the syntax skeleton, selecting tables and fields, instantiating conditional expressions, generating aggregation calculations, and guiding sampling control through feature distribution.

[0111] Algorithm Input: Meta-information database (meta_info), feature statistics (feature_stats), number of generated targets (num_desired) Algorithm Output: Raw SQL list (raw_sqls)

[0112] Algorithm steps:

[0113] 1) Randomly select SQL skeletons (simple queries, subqueries, set queries, etc.) based on the syntactic structure distribution in feature_stats.

[0114] 2) Based on the relationships between tables, a weighted random walk is used to select 2 to 4 tables to form the FROM clause.

[0115] 3) Select fields based on feature requirements: If aggregation is required, select numeric fields; if grouping is required, select low cardinality numeric fields or time fields.

[0116] 4) Use the field value distribution information to generate WHERE conditions to ensure that the filtered values ​​are within a reasonable range.

[0117] 5) If the skeleton contains aggregation or calculation, generate expressions that conform to business meaning (such as SUM, AVG, window functions).

[0118] 6) Repeat the above steps until a sufficient number of SQL statements are generated.

[0119]

[0120] Module 3: Simulated Log Generation

[0121] like Figure 3 As shown, this module filters, deduplicates, and semantically annotates the original generated SQL, forming a dataset that can be used for Text-to-SQL optimization. Specifically, this includes: syntax correctness verification, business rationality rule checking, AST-based similarity deduplication, natural language question generation, and optional model fine-tuning or retrieval enhancement applications.

[0122] Algorithm input: Raw SQL list (raw_sqls), metadata database (meta_info) Algorithm output: Final log file (containing SQL and natural language processing)

[0123] Algorithm steps:

[0124] 1) Perform syntax parsing on each SQL statement and discard statements with syntax errors.

[0125] 2) Apply business rules (such as prohibiting summation of ID fields and prohibiting meaningless joins) to score the reasonableness of SQL queries and eliminate low-scoring SQL queries.

[0126] 3) Calculate the structural similarity between SQL statements (based on AST edit distance or string similarity), remove duplicate statements with high similarity, and preserve diversity.

[0127] 4) Use template filling or a pre-trained SQL-to-Text model to generate a corresponding natural language question for each SQL statement.

[0128] 5) Output the generated pairing data as a JSON file for use by downstream tasks.

[0129]

Claims

1. A method for automatically generating database query logs based on semantic awareness, characterized in that, The method includes: (1) Database metadata pre-analysis: Connect to the target database, extract database schema information, statistically analyze the value distribution characteristics of each field, mine the relationship between tables and calculate the confidence level, and construct a metadata database containing table structure, field distribution and relationship; (2) Feature summarization of real query logs: From public benchmarks or real logs provided by users, statistical analysis is conducted on the syntactic structure features, business logic features, and join pattern features of SQL queries, and a feature template library is constructed. (3) Semantic-aware targeted SQL fuzz test generation: Based on the metadata library and feature template library, a hierarchical generation and constraint sampling strategy is adopted to generate targeted SQL statements that conform to business semantics and cover the distribution of real query features. (4) Filtering and optimization of generated logs: Perform syntax correctness verification, business rationality verification and diversity deduplication on the generated SQL statements, and output high-quality simulated logs; (5) Execution verification and feedback utilization of simulated logs: Execute the generated SQL statements on the target database, record the execution status and results, reverse translate the successfully executed SQL to generate natural language questions, analyze the error types of the failed SQL and use them to optimize the generation module, forming a paired dataset that can be used for Text-to-SQL optimization.

2. The method according to claim 1, characterized in that, The database metadata pre-analysis specifically includes: (1) Extract the table names, field names, data types, field comments, primary key and foreign key constraints of the target database to form a schema knowledge base; (2) Perform value distribution statistics for each field: calculate the minimum, maximum, mean, and standard deviation for numeric fields; calculate the time range for date fields; and collect a list of high-frequency values ​​for text fields. (3) Based on foreign key constraints and field naming similarity, infer the relationship between tables, calculate the confidence score by combining the overlap of field value distribution, and store the relationship in a graph structure, where nodes are tables, edges are connecting field pairs, and each edge is accompanied by connection type and confidence score.

3. The method according to claim 1, characterized in that, The specific features summarized from the actual query logs include: (1) Syntactic structure feature statistics: Parse the abstract syntax tree of SQL and count the frequency of occurrence of clause types and subquery types; (2) Business logic feature statistics: summarizing aggregation indicator patterns, grouping dimension patterns, and sorting requirement patterns; (3) Connection mode characteristics statistics: Statistics on connection types and connection condition complexity; (4) Feature distribution quantization and template conversion: convert statistical results into feature distribution vectors and construct a feature template library.

4. The method according to claim 1, characterized in that, The semantically aware targeted SQL fuzz test generation specifically includes: (1) Randomly select the SQL syntax skeleton based on the distribution of syntax structure in the feature template library; (2) Based on the inter-table relationships in the metadata database, a weighted random walk is used to select 2 to 4 tables to form the FROM clause; (3) Select fields based on feature requirements: If aggregation is required, choose numeric fields first; if grouping is required, choose low cardinality numeric fields or time fields first. (4) Generate WHERE conditions that conform to the actual data distribution using field value distribution information; (5) Generate aggregate functions, arithmetic operations, or window function expressions that conform to business meaning; (6) Sampling control is performed based on the probability distribution in the feature template library to ensure that the generated SQL conforms to the preset feature distribution.

5. The method according to claim 1, characterized in that, The filtering and optimization of the generated logs specifically include: (1) Syntax correctness check: Use the SQL parser to parse the syntax of each SQL statement and discard statements with syntax errors; (2) Business rationality verification: The rule engine judges the business rationality of SQL and removes statements with meaningless aggregation, unreasonable filtering, semantic conflict and incomplete join conditions; (3) Diversity Enhancement Deduplication: Based on the structural similarity of the abstract syntax tree or the summary of the execution result, duplicate statements with high similarity are removed; (4) Log quality scoring and sorting: The retained SQL is scored based on feature matching degree, complexity and business clarity, and the overall quality score is calculated by weighted summation.

6. The method according to claim 1, characterized in that, The execution verification and feedback utilization of the simulated logs specifically includes: (1) SQL execution and log collection: Execute the generated SQL statements sequentially on the target database and record the execution status, execution time, result set information and error information; (2) Reverse translation of correct execution results: For successfully executed SQL statements, extract the actual execution results and convert them into corresponding natural language questions through template filling or pre-trained models to form paired data; (3) Analysis of the use of error execution results: For SQL statements that fail to execute, analyze the error type and cause, use syntax errors to optimize the syntax skeleton of the generation module, and use runtime errors as negative samples or feedback information to guide the generation module to adjust parameters; (4) Data integration and output: Integrate the paired data with valuable error feedback information into the final output dataset.

7. A semantically aware SQL fuzzy test log generation system that implements the method as described in any one of claims 1 to 6, characterized in that, The system includes: (1) Database analysis and feature summarization module, which is used to connect to the target database to extract metadata and statistically analyze feature distribution, and output metadata library and feature template library; (2) Semantic-aware directional SQL fuzz test generation module, used to generate SQL statements that conform to business semantics based on the meta-information library and feature template library; (3) Simulated log generation module, used to filter, optimize, verify and reverse translate the generated SQL, and output a paired dataset that can be used for Text-to-SQL optimization.

Images