Database structured simulation data generation method

Abstract

The invention discloses a method for generating structured simulation data of a database, which comprises the following steps of: receiving and analyzing a table structure provided by a user, detecting the difference between an actual data type and a definition type of a column, speculating the data distribution and association relationship of a field by combining a big data model, and automatically adjusting the table structure to match actual data; analyzing an SQL query condition provided by a user, extracting a field constraint, and generating a data generation rule; based on a data sample provided by a user, an SQL analysis result and a field value range and distribution speculated by a big data model, generating a data dictionary containing possible field values; according to the corrected table structure, the data dictionary and the SQL constraint condition, generating structured simulation data conforming to the business logic; and exporting the generated analog data in a 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, and a large amount of realistic data is required during testing and development. However, generating simulation data that conforms to business logic and matches the data structure of the actual business system usually requires a lot of manual intervention and time costs. Most of the existing simulation data generation tools cannot automatically generate data dictionaries based on the sample data and SQL conditions provided by users. The generated data is difficult to meet the needs of complex business logic, and the adaptability to multiple industries and multiple scenarios is poor, which cannot effectively support system development and performance testing. Summary of the invention

[0003] To achieve the above-mentioned purpose 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 the data samples provided by the user, the 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, 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 formats.

[0013] Furthermore, S2 includes:

[0014] 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.

[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 industry-wide dictionaries for columns containing keywords.

[0019] Furthermore, S4 preferentially takes values ​​from the data dictionary and generates random data according to the inferred value distribution type.

[0020] Furthermore, identifying the field type according to 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 keywords "cur" and "amount" 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 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, infer the data distribution and association relationship of the field based on the big data model, and automatically adjust 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] Data export module, used to export the generated simulation data into CSV, JSON or SQL format.

[0030] By adopting the above technical solution, the problem that it is difficult to generate simulation data that conforms to complex business logic in the existing technology is effectively solved. By automatically parsing the table structure and SQL conditions, large-scale simulation data that meets actual needs is generated, which greatly improves the efficiency and flexibility of data generation. This tool has broad application prospects in multiple industries such as finance, retail, and medical care. 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 used to better understand the present solution 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 be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. 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 creative work are within the scope of protection of the present invention.

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

[0036] S1: 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, infer the data distribution and association relationship of the field based on the big data model, and automatically adjust 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 derived based on the constraints to generate data generation rules that meet the WHERE clause and JOIN conditions.

[0039] Specifically include:

[0040] The tool can parse the SQL query statements provided by customers, especially extract the WHERE conditions and the association conditions between tables, and use these conditions to generate data dictionaries. Through regular expressions and other technologies, fields and values ​​are extracted from SQL statements, such as WHERE name = 'Alice' AND transaction_type = 'Deposit'. The tool will generate a dictionary that meets the requirements based on these conditions. The tool can parse the SQL query conditions provided by customers (such as WHERE clauses, JOIN conditions, etc.) and constrain the generated data based on these conditions. The tool will automatically deduce the table field types and data distribution involved in the SQL query. Generate qualified data based on the WHERE conditions (such as generating data with an age greater than 30 based on WHERE age > 30). Automatically adjust the data generation method based on the JOIN conditions in SQL to ensure the consistency of related 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 simulation 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, 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 formats.

[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 keywords "cur" and "amount" are automatically suggested as currency or floating point types.

[0053] Receive and parse the table structure provided by the customer, extract each table column separately, parse the column name, and automatically analyze 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 and the defined field type. If a mismatch is found (such as an INT column storing floating values), it automatically proposes a correction plan.

[0055] Data pattern inference: Combined with big data model inference technology, the numerical distribution and correlation of the fields are analyzed to infer the optimal storage method for the data (such as choosing DECIMAL or FLOAT types instead of INT). Fixed data of columns with common table structures in industries such as finance, retail, and medical care, such as the currency column of banking business, which contains fixed and universal currency abbreviations such as RMB, USD, and HKD. The column name usually contains the 'cur' field. When generating data, the column name containing the 'cur' keyword will be checked and a fixed and universal dictionary will be generated for it to increase the probability of business SQL hitting the data. Similar keywords include name, bank, region, etc.

[0056] Dynamic field type update: When customers create tables, they often create columns that should be int type as varchar columns for convenience. By parsing the column names, int columns can be found, such as columns containing the keywords 'id' and 'tel'. For such 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 industry-wide dictionaries for columns containing keywords.

[0061] Specifically, it includes: automatically generating a data dictionary based on some data samples provided by customers, combined with SQL conditions, fixed general dictionaries, and big data model inferences. The data dictionary contains all possible values ​​of 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 inference 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] Partial data: The data in 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 recognizes that transaction_amount should be of DECIMAL type, and adjusts the transaction_type field to a more standardized data type (such as classifying it as ENUM type). 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] Partial data: The diagnosis field stores the diagnosis type, such as Flu, Diabetes, etc. The visit_date has inconsistent formats.

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

[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 the industry data model with big data inference technology, the actual data distribution and association relationship of the field are automatically inferred to generate data that is more in line with the real scene. By parsing the SQL query conditions and automatically generating simulated data that meets the constraints, it is ensured that the generated data meets the 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 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, infer the data distribution and association relationship of the field based on the big data model, and automatically adjust 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] Data export module, used to export the generated simulation data into CSV, JSON or SQL format.

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

[0081] For the method embodiments, for the sake of simplicity, they are all described as a series of action combinations, but those skilled in the art should know that the embodiments of the present invention are not limited by the order of the actions described, because according to the embodiments of the present invention, some steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also know that the embodiments described in the specification are all preferred embodiments, and the actions involved are not necessarily required by the embodiments of the present invention.

[0082] It can be known from the description of the above implementation modes that those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly contributed to the prior art in the form of a software product, which can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes several 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 the various implementation modes of the present application or certain parts of the implementation modes.

[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 aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein by 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; S2: Parse the SQL query conditions provided by the user, extract field constraints, and generate data generation rules; 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; S4: Generate structured simulation data that conforms to business logic based on the corrected table structure, data dictionary, and SQL constraints; 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 of the column with the defined data type, identify mismatched fields and propose correction solutions; Combined with big data model inference technology, analyze the value distribution and correlation of fields, and infer the optimal storage method for data; Automatically suggest or adjust field types based on column name keywords and actual data formats.

3. The method for generating structured simulation data of a database according to claim 1, characterized in that 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 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 industry-wide dictionaries for columns containing keywords.

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

6. The method for generating structured simulation data of a database according to claim 1, characterized in that: The identifying of the field type according to the keywords in the column name includes: Columns containing the keywords "id" and "tel" 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" or "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, infer the data distribution and association relationship of the field based on the big data model, and automatically adjust the table structure to match the actual data; SQL condition parsing module, used to parse SQL query conditions provided by users, extract field constraints, 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 corrected table structure, data dictionary and SQL constraints; Data export module, used to export the generated simulation data into CSV, JSON or SQL format.

Images