A method for generating structured simulation data of database

Abstract

The present invention discloses a method for generating structured simulation data of a database, comprising: receiving and parsing a table structure provided by a user, detecting the difference between the actual data type and the defined type of a column, inferring the data distribution and association relationship of the field in combination with a big data model, and automatically adjusting the table structure to match the actual data; parsing the SQL query conditions provided by the user, extracting the field constraints, and generating data generation rules; generating a data dictionary containing possible field values based on the data samples provided by the user, the SQL parsing results, and the field value range and distribution inferred by the big data model; generating structured simulation data that conforms to business logic based on the corrected table structure, data dictionary, and SQL constraint conditions; and exporting the generated simulation data into CSV, JSON, or SQL format.

Description

Technical Field

[0001] The present invention relates to the field of computer data processing, and in particular to a method for generating structured simulation data of a database. Background Art

[0002] With the development of informatization, the demand for data processing in industries such as finance, retail, and healthcare is growing, requiring large amounts of realistic data during testing and development. However, generating simulation data that conforms to business logic and matches the data structure of actual business systems often requires significant manual intervention and time. Most existing simulation data generation tools are unable to automatically generate a data dictionary based on user-provided sample data and SQL conditions. The generated data struggles to meet the requirements of complex business logic and has poor adaptability across multiple industries and scenarios, making it ineffective in supporting system development and performance testing. Summary of the Invention

[0003] To achieve the above-mentioned and other related purposes, the present invention discloses a method for generating structured simulation data of a database, comprising:

[0004] S1: Receives and parses the table structure provided by the user, detects the difference between the actual data type and the defined type of the column, infers the data distribution and association relationship of the field based on the big data model, and automatically adjusts the table structure to match the actual data;

[0005] S2: Parse the SQL query conditions provided by the user, extract field constraints, and generate data generation rules;

[0006] S3: Generates a data dictionary containing possible field values based on user-provided data samples, SQL parsing results, and the field value range and distribution inferred by the big data model;

[0007] S4: Generate structured simulation data that conforms to business logic based on the corrected table structure, data dictionary, and SQL constraints;

[0008] S5: Export the generated simulation data to CSV, JSON, or SQL format.

[0009] Furthermore, S1 includes:

[0010] Compare the stored data of the column with the defined data type, identify mismatched fields and propose correction solutions;

[0011] Combined with big data model inference technology, we analyze the value distribution and correlation of fields and infer the optimal storage method for data.

[0012] Automatically suggest or adjust field types based on column name keywords and actual data format.

[0013] Furthermore, S2 includes:

[0014] Extract field constraints in SQL statements through regular expressions, deduce data distribution range and association conditions based on the constraints, and generate data generation rules that meet the WHERE clause and JOIN conditions.

[0015] Furthermore, S3 generates field value ranges in the following way:

[0016] Extract candidate values based on the data samples provided by the user;

[0017] Infer the value distribution type and association probability of the field based on the big data model;

[0018] Matches columns containing keywords against industry-wide dictionaries.

[0019] Furthermore, S4 prioritizes taking values from the data dictionary and generates random data based on the inferred value distribution type.

[0020] Furthermore, identifying the field type based on the keywords in the column name includes:

[0021] Columns containing the keywords "id" and "tel" are automatically suggested as integer types;

[0022] Columns containing the keywords "date" and "time" are automatically suggested as date or time types;

[0023] Columns containing the "cur" and "amount" keywords are automatically suggested as currency or floating-point types.

[0024] In another aspect, the present invention provides a system for generating structured simulation data of a database, comprising:

[0025] The table structure analysis and correction module receives and parses the table structure provided by the user, detects discrepancies between the actual data type and the defined type of the column, infers the data distribution and association relationships of the fields based on the big data model, and automatically adjusts the table structure to match the actual data;

[0026] SQL condition parsing module, used to parse SQL query conditions provided by users, extract field constraints, and generate data generation rules;

[0027] The data dictionary generation module generates a data dictionary containing possible field values based on the data samples provided by the user, the SQL parsing results, and the field value range and distribution inferred by the big data model;

[0028] The simulation data generation module generates structured simulation data that conforms to business logic based on the corrected table structure, data dictionary, and SQL constraints;

[0029] The data export module is used to export the generated simulation data into CSV, JSON or SQL format.

[0030] This technical solution effectively addresses the existing difficulty in generating simulated data that complies with complex business logic. By automatically parsing table structures and SQL conditions, it generates large-scale simulated data that meets actual needs, significantly improving the efficiency and flexibility of data generation. This tool has broad application prospects in various industries, including finance, retail, and healthcare. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] The above and other features, advantages and aspects of the embodiments of the present disclosure will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. The accompanying drawings are provided for a better understanding of the present disclosure and do not constitute a limitation of the present disclosure. In the accompanying drawings, the same or similar reference numerals represent the same or similar elements, among which:

[0032] Figure 1 is a flow chart of the present invention;

[0033] Figure 2 It is a schematic diagram of the module composition of the present invention. DETAILED DESCRIPTION

[0034] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making any creative efforts shall fall within the scope of protection of the present invention.

[0035] Reference Figure 1 , an embodiment of the present invention provides a method for generating structured simulation data of a database, comprising:

[0036] S1: Receives and parses the table structure provided by the user, detects the difference between the actual data type and the defined type of the column, infers the data distribution and association relationship of the field based on the big data model, and automatically adjusts the table structure to match the actual data.

[0037] S2: Parse the SQL query conditions provided by the user, extract field constraints, and generate data generation rules.

[0038] Among them, the field constraints in the SQL statement are extracted through regular expressions, and the data distribution range and association conditions are deduced based on the constraints to generate data generation rules that meet the WHERE clause and JOIN conditions.

[0039] Specifically include:

[0040] The tool parses customer-provided SQL queries, specifically extracting WHERE conditions and inter-table join conditions, and uses these conditions to generate a data dictionary. Using techniques like regular expressions, the tool extracts fields and values from SQL statements, such as WHERE name = 'Alice' AND transaction_type = 'Deposit'. The tool generates a dictionary that meets the requirements based on these conditions. The tool parses customer-provided SQL query conditions (such as WHERE clauses and JOIN conditions) and constrains the generated data based on these conditions. The tool automatically infers the table field types and data distribution involved in the SQL query. It generates data that meets the conditions based on WHERE conditions (for example, WHERE age > 30 generates data for those aged greater than 30). The tool automatically adjusts the data generation method based on the JOIN conditions in the SQL to ensure the consistency of the linked data.

[0041] S3: Generates a data dictionary containing possible field values based on the data samples provided by the user, the SQL parsing results, and the field value range and distribution inferred by the big data model.

[0042] S4: Generate structured simulation data that conforms to business logic based on the corrected table structure, data dictionary, and SQL constraints.

[0043] S5: Export the generated simulation data to CSV, JSON, or SQL format.

[0044] The tool supports multi-industry adaptation. By switching industry models, it adjusts the data dictionary and field type correction rules to generate simulated data that conforms to the business logic of a specific industry.

[0045] Among them, S1 includes:

[0046] Compare the stored data of the column with the defined data type, identify mismatched fields and propose correction solutions;

[0047] Combined with big data model inference technology, we analyze the value distribution and correlation of fields and infer the optimal storage method for data.

[0048] Automatically suggest or adjust field types based on column name keywords and actual data format.

[0049] The dynamic field type updating unit identifies the field type according to the keyword in the column name, including:

[0050] Columns containing the keywords "id" and "tel" are automatically suggested as integer types;

[0051] Columns containing the keywords "date" and "time" are automatically suggested as date or time types;

[0052] Columns containing the "cur" and "amount" keywords are automatically suggested as currency or floating-point types.

[0053] The tool receives and parses the table structure provided by the customer, extracts each table column separately, and parses the column name. By automatically analyzing the difference between the column data type in the table structure and the actual stored data type, the tool can automatically adjust the table structure based on the following information:

[0054] Data type mismatch detection: The tool analyzes the actual data type of each column against the defined field type and automatically proposes a correction plan if a mismatch is found (e.g., an INT column storing floating values).

[0055] Data pattern inference: Integrating big data model inference technology, we analyze the distribution and correlation of field values and infer the optimal storage method for the data (such as choosing DECIMAL or FLOAT types instead of INT). We also include fixed data in columns with common table structures in industries such as finance, retail, and healthcare. For example, the currency column in banking contains fixed and universal currency abbreviations such as RMB, USD, and HKD. These columns often have the "cur" field in their names. During data generation, we check for column names containing the "cur" keyword and generate a fixed and universal dictionary for them, increasing the probability of business SQL queries hitting the data. Similar keywords include "name," "bank," and "region."

[0056] Dynamic field type update: When customers create tables, they often create columns that should be int types as varchar columns for convenience. By parsing the column names, we can identify int columns, such as those containing the keywords 'id' and 'tel'. For fields with inconsistent storage formats (such as VARCHAR columns storing timestamps), the tool can automatically suggest changing the field type or redefining the field storage method based on the actual data format.

[0057] S3 specifically includes:

[0058] Extract candidate values based on the data samples provided by the user;

[0059] Infer the value distribution type and association probability of the field based on the big data model;

[0060] Matches columns containing keywords against industry-wide dictionaries.

[0061] Specifically, it includes: automatically generating a data dictionary based on some data samples provided by the customer, combined with SQL conditions, fixed general dictionaries, and big data model inferences. The data dictionary contains all possible values for the field and is used to guide the data generation process. For example, the name field can contain customer-provided values such as 'Alice' and 'Bob', while the transaction_type field may contain values such as 'Deposit' and 'Withdraw'; based on historical data and industry big data models, the tool can infer the actual data distribution and correlation of the fields. For example: infer the amount distribution of the transaction_amount field (such as normal distribution, logarithmic distribution, etc.) based on historical transaction data. Infer the probability of association between the customer_id and transaction_type fields based on industry models. This inferred information will help the tool make more accurate decisions when correcting the table structure and generate data that conforms to real business scenarios.

[0062] The specific implementation process includes the following specific embodiments:

[0063] First embodiment:

[0064] Assume that the customer provides the table structure and partial data of bank transaction data:

[0065] Table structure: Contains fields such as customer_id (INT), transaction_amount (DECIMAL), transaction_date (DATE), and transaction_type (VARCHAR).

[0066] Some data: The transaction_amount field contains floating values, and the transaction_type field has multiple different types (such as Deposit and Withdraw).

[0067] By analyzing the actual data, the tool automatically identifies that the transaction_amount field should be of type DECIMAL and adjusts the transaction_type field to a more standardized data type (such as ENUM). Then, combined with SQL conditions (such as WHERE transaction_type = 'Deposit'), the tool automatically generates simulated transaction data that meets these conditions.

[0068] Second embodiment:

[0069] The customer provides the table structure and partial data of medical patient records:

[0070] Table structure: Contains fields such as patient_id (INT), name (VARCHAR), diagnosis (VARCHAR), visit_date (DATE), etc.

[0071] Some data: The diagnosis field stores diagnosis types, such as Flu and Diabetes. The visit_date field has inconsistent formats.

[0072] The tool analyzes the data and finds that the visit_date field stores dates and timestamps. It recommends changing it to the DATETIME type. The tool also uses a big data model to infer the probability of different diagnoses in the diagnosis field, generating patient record data that meets business requirements.

[0073] The beneficial effects of the present invention include: the tool can automatically adjust the field type according to the actual data analysis results to ensure that the table structure matches the stored data type, thereby improving the accuracy of data generation. By combining industry data models with big data inference technology, the actual data distribution and association relationships of the fields are automatically inferred to generate data that is more in line with real scenarios. By parsing SQL query conditions and automatically generating simulated data that meets the constraints, it is ensured that the generated data meets business needs and testing needs. Based on partial data samples, SQL conditions and data model inferences, a data dictionary is automatically generated to guide data generation and improve the quality and controllability of data generation. The data generated by the tool is not only realistic, but also can automatically adapt to business logic, especially complex associations and filtering conditions, which greatly improves the accuracy and effectiveness of system testing, performance evaluation and data analysis.

[0074] The present invention also provides a system for generating structured simulation data of a database, comprising:

[0075] The table structure analysis and correction module receives and parses the table structure provided by the user, detects discrepancies between the actual data type and the defined type of the column, infers the data distribution and association relationships of the fields based on the big data model, and automatically adjusts the table structure to match the actual data;

[0076] SQL condition parsing module, used to parse SQL query conditions provided by users, extract field constraints, and generate data generation rules;

[0077] The data dictionary generation module generates a data dictionary containing possible field values based on the data samples provided by the user, the SQL parsing results, and the field value range and distribution inferred by the big data model;

[0078] The simulation data generation module generates structured simulation data that conforms to business logic based on the corrected table structure, data dictionary, and SQL constraints;

[0079] The data export module is used to export the generated simulation data into CSV, JSON or SQL format.

[0080] Those skilled in the art will understand that, unless otherwise defined, all terms used herein (including technical and scientific terms) have the same meaning as commonly understood by those skilled in the art in the art to which the present invention pertains. It should also be understood that terms such as those defined in common dictionaries should be understood to have meanings consistent with those in the context of the prior art and, unless specifically defined, will not be interpreted in an idealized or overly formal sense.

[0081] For simplicity of description, the method embodiments are described as a series of actions. However, those skilled in the art should be aware that the embodiments of the present invention are not limited by the order of the actions described, because certain steps can be performed in other orders or simultaneously according to the embodiments of the present invention. Secondly, those skilled in the art should also be aware that the embodiments described in this specification are all preferred embodiments, and the actions involved are not necessarily required by the embodiments of the present invention.

[0082] Through the description of the above embodiments, it can be seen that those skilled in the art can clearly understand that the present application can be implemented by means of software plus the necessary general hardware platform. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in various embodiments of the present application or certain parts of the embodiments.

[0083] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for generating structured simulation data of a database, characterized in that: include: S1: Receives and parses the table structure provided by the user, detects the difference between the actual data type and the defined type of the column, infers the data distribution and association relationship of the field based on the big data model, and automatically adjusts the table structure to match the actual data. The defined type is the field type in the table structure provided by the user; S2: Parse the SQL query conditions provided by the user, extract the field constraints in the SQL statement, and generate data generation rules; S3: Generates a data dictionary containing possible field values based on user-provided data samples, SQL parsing results, and the field value range and distribution inferred by the big data model; S4: Generate structured simulation data that conforms to business logic based on the adjusted table structure, data dictionary, and data generation rules; S5: Export the generated simulation data to CSV, JSON, or SQL format.

2. The method for generating structured simulation data of a database according to claim 1, characterized in that: S1 includes: Compare the stored data and defined types of the columns, identify mismatched fields and propose correction solutions; Combined with big data model inference technology, it analyzes the value distribution and correlation of fields and infers the storage method used by the data; Automatically suggest or adjust field types based on column name keywords and actual data format.

3. The method for generating structured simulation data of a database according to claim 1, wherein S2 include: The field constraints in the SQL statement are extracted through regular expressions, and the data distribution range and association conditions are deduced based on the field constraints to generate data generation rules that meet the WHERE clause and JOIN conditions.

4. The method for generating structured simulation data of a database according to claim 1, characterized in that: S3 generates field value ranges in the following ways: Extract candidate values based on the data samples provided by the user; Infer the value distribution type and association probability of the field based on the big data model; Matches columns containing keywords against industry-wide dictionaries.

5. The method for generating structured simulation data of a database according to claim 1, characterized in that: In S4, values are first taken from the data dictionary and random data is generated based on the inferred value distribution type.

6. The method for generating structured simulation data of a database according to claim 2, characterized in that: Identify field types based on keywords in column names, including: Columns containing the "id" and "tel" keywords are automatically suggested as integer types; Columns containing the keywords "date" and "time" are automatically suggested as date or time types; Columns containing the "cur" and "amount" keywords are automatically suggested as currency or floating point types.

7. A system for generating structured simulation data of a database, characterized in that: include: The table structure analysis and correction module is used to receive and parse the table structure provided by the user, detect the difference between the actual data type and the defined type of the column, and automatically adjust the table structure to match the actual data based on the big data model to infer the data distribution and association relationship of the field. The defined type is the field type in the table structure provided by the user; SQL condition parsing module, used to parse SQL query conditions provided by users, extract field constraints in SQL statements, and generate data generation rules; The data dictionary generation module generates a data dictionary containing possible field values based on the data samples provided by the user, the SQL parsing results, and the field value range and distribution inferred by the big data model; The simulation data generation module generates structured simulation data that conforms to business logic based on the adjusted table structure, data dictionary, and data generation rules; The data export module is used to export the generated simulation data into CSV, JSON or SQL format.

Images