Test data comparison method and system

Abstract

The embodiment of the invention discloses a test data comparison method and system, and the method comprises the steps: obtaining a test demand inputted by a user; analyzing the test requirement to obtain a comparison parameter set, determining a to-be-tested table according to the test requirement, dynamically constructing an instruction template according to the comparison parameter set and a metadata set of the to-be-tested table, inputting the instruction template into the large language model, and outputting a query instruction set; wherein the to-be-tested table comprises a source table and a target table; performing data extraction on the source table according to a source table query instruction in the query instruction set to obtain a source table data set; performing data extraction on the target table according to a target table query instruction in the query instruction set to obtain a target table data set; matching records in the source table data set and the target table data set according to the primary key information to obtain a record pair set; performing data difference comparison on the sets according to the source table data set, the target table data set and the records to obtain a difference result; and carrying out visual display on the difference result. And the data comparison efficiency and accuracy are improved.

Description

Technical Field

[0001] The present invention relates to the field of data testing, and in particular to a test data comparison method and system. Background Art

[0002] In data-driven enterprise information systems, there are often extensive data interaction, synchronization, and migration requirements between different systems or between different versions of the same system. For example, in scenarios such as database migration, system upgrades, business platform replacements, and data warehouse transformation, it is often necessary to verify the consistency of original business data with the data in the target system to ensure data integrity, accuracy, and structural compatibility during the migration process.

[0003] Existing data comparison methods often rely on manual scripting or customized ETL tools, querying fields in the source and target tables and manually comparing field values. This approach is not only inefficient and reusable, but also lacks flexible adaptability in complex data environments, such as those with differing field structures, incompatible field types, and different primary key strategies.

[0004] There is currently no effective solution to the above problems in the prior art. Summary of the Invention

[0005] To solve the above problems, the present invention provides a test data comparison method and system. By setting the metadata of the source table and the target table and the test requirements input by the user, a large language model is used to generate query instructions and extract data, and a difference comparison is performed based on primary key alignment. The difference results are displayed in an interactive and visual manner, thereby solving the problems of low data query efficiency and inflexibility in the existing technology.

[0006] To achieve the above-mentioned purpose, the present invention provides a test data comparison method, comprising: obtaining a test requirement input by a user; parsing the test requirement to obtain a comparison parameter set, and determining a table to be tested according to the test requirement, dynamically constructing an instruction template according to the comparison parameter set and a metadata set of the table to be tested, inputting the instruction template into a large language model, and outputting a query instruction set; wherein, the table to be tested includes a source table and a target table; extracting data from the source table according to the source table query instruction in the query instruction set to obtain a source table data set; extracting data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set; matching records in the source table data set and the target table data set according to primary key information to obtain a record pair set; performing data difference comparison according to the source table data set, the target table data set and the record pair set to obtain a difference result; and visually displaying the difference result.

[0007] Further optionally, extracting data from the source table according to the source table query instructions in the query instruction set to obtain a source table data set includes: performing a syntax validity check on the source table query instructions in the query instruction set; when the syntax validity check passes, executing the source table query instructions in the source table, and using the obtained query results as the first initial data set; and performing format standardization on each field in the first initial data set to obtain a source table data set.

[0008] Further optionally, extracting data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set includes: performing a syntax validation check on the target table query instruction in the query instruction set; when the check passes the syntax validation, executing the target table query instruction in the target table, and using the obtained query result as a second initial data set; and performing format standardization on each field in the second initial data set to obtain a target table data set.

[0009] Further optionally, the data difference comparison is performed based on the source table data set, the target table data set and the record pair set to obtain the difference result, including: performing a set operation on the source table data set and the target table data set, and identifying row differences based on the set operation result; checking whether the fields in each record pair exist and whether the types are compatible, and recording the fields that are missing or have incompatible types as structural differences; for the corresponding numerical fields in each record pair, calculating the error value of the two records in the corresponding record pair, and recording the numerical fields whose error values ​​are greater than a preset error threshold as value differences; and taking the row differences, structural differences and value differences together as the difference results.

[0010] Further optionally, the visual display of the difference results includes: generating and displaying an interactive HTML table based on the difference results, and for the current difference item in the difference results, converting the display format of its corresponding path into a row and column description format, and displaying it in the interactive HTML table; identifying the target position of the current difference item in the table to be tested, using a preset number of data adjacent to the target position as context data, associating the corresponding path of the current difference item with the corresponding context data, for display when the difference item is clicked; dividing the difference level of the current difference item according to preset rules, selecting a preset color according to the difference level of the current difference item, and identifying the current difference item according to the preset color.

[0011] On the other hand, the present invention also provides a test data comparison system, including: a data acquisition module for acquiring test requirements input by a user; a query instruction generation module for parsing the test requirements to obtain a comparison parameter set, and determining a table to be tested according to the test requirements, dynamically constructing an instruction template according to the comparison parameter set and the metadata set of the table to be tested, inputting the instruction template into a large language model, and outputting a query instruction set; wherein, the table to be tested includes a source table and a target table; a data extraction module for extracting data from the source table according to the source table query instruction in the query instruction set to obtain a source table data set; extracting data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set; a data matching module for matching records in the source table data set and the target table data set according to primary key information to obtain a record pair set; a difference comparison module for performing data difference comparison based on the source table data set, the target table data set and the record pair set to obtain a difference result; and a visualization module for visually displaying the difference result.

[0012] Further optionally, the data extraction module includes: a first query submodule, used to perform a syntax validity check on the source table query instruction in the query instruction set; when the syntax validity check passes, the source table query instruction is executed in the source table, and the query result obtained is used as the first initial data set; a first standardization submodule, used to perform format standardization on each field in the first initial data set to obtain a source table data set.

[0013] Further optionally, the data extraction module also includes: a second query submodule, used to perform grammatical validity verification on the target table query instruction in the query instruction set; when the verification passes the grammatical verification, the target table query instruction is executed in the target table, and the query result obtained is used as the second initial data set; a second standardization submodule, used to perform format standardization on each field in the second initial data set to obtain a target table data set.

[0014] Further optionally, the difference comparison module includes: a row comparison submodule, which is used to perform set operations on the source table data set and the target table data set, and identify row differences based on the set operation results; a structure comparison submodule, which is used to check whether the fields in each record pair exist and whether the types are compatible, and record the fields that are missing or have incompatible types as structure differences; a value comparison submodule, which is used to calculate the error values ​​of the two records in the corresponding record pair for the corresponding numerical fields in each record pair, and record the numerical fields whose error values ​​are greater than a preset error threshold as value differences; and a result generation submodule, which is used to take the row differences, structure differences and value differences together as difference results.

[0015] Further optionally, the visualization display module includes: a format conversion submodule, which is used to generate and display an interactive HTML table based on the difference results, and for the current difference item in the difference results, convert the display format of its corresponding path into a row and column description format, and display it in the interactive HTML table, so as to be displayed when the difference item is clicked; a context data association submodule, which is used to identify the target position of the current difference item in the table to be tested, and use the data of a preset number of items adjacent to the target position as context data, and associate the corresponding path of the current difference item with the corresponding context data; a color identification submodule, which is used to divide the difference level of the current difference item according to preset rules, select a preset color according to the difference level of the current difference item, and identify the current difference item according to the preset color.

[0016] The above technical solution has the following beneficial effects: by analyzing the user's test requirements and combining with a large language model to generate dynamic instructions, data from different databases can be searched and compared, and differences can be displayed, thereby reducing manual participation and automatically adapting to field differences or format differences between different systems, thereby improving processing efficiency; after the search and comparison, the data is analyzed and visualized at the field level, so that users can quickly understand the source of the differences and locate the problem, thereby improving data comparison efficiency and display intuitiveness. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0018] Figure 1 is a flow chart of a test data comparison method provided by an embodiment of the present invention;

[0019] Figure 2 is a flow chart of a method for extracting source table data provided by an embodiment of the present invention;

[0020] Figure 3 is a flow chart of a target table data extraction method provided by an embodiment of the present invention;

[0021] Figure 4 is a flow chart of a data difference comparison method provided by an embodiment of the present invention;

[0022] Figure 5 is a flow chart of a visualization display method provided by an embodiment of the present invention;

[0023] Figure 6Schematic diagram of the structure of the test data comparison system provided by an embodiment of the present invention;

[0024] Figure 7 2 is a schematic diagram of the structure of a data extraction module for extracting source table data provided by an embodiment of the present invention;

[0025] Figure 8 1 is a schematic structural diagram of a data extraction module for extracting target table data provided by an embodiment of the present invention;

[0026] Figure 9 is a structural diagram of a difference comparison module provided by an embodiment of the present invention;

[0027] Figure 10 It is a structural diagram of a visual display module provided by an embodiment of the present invention.

[0028] Figure markings: 100-data acquisition module; 200-query instruction generation module; 300-data extraction module; 3001-first query submodule; 3002-first standardization submodule; 3003-second query submodule; 3004-second standardization submodule; 400-data matching module; 500-difference comparison module; 5001-row comparison submodule; 5002-structure comparison submodule; 5003-value comparison submodule; 5004-result generation submodule; 600-visualization display module; 6001-format conversion submodule; 6002-context data association submodule; 6003-color identification submodule. DETAILED DESCRIPTION

[0029] 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 ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0030] In order to solve the problem of low efficiency and inflexibility of data comparison in the prior art, the present invention provides a test data comparison method. Figure 1 Flowchart of the test data comparison method provided by the embodiment of the present invention. Figure 1 As shown, the method includes:

[0031] S1. Test requirements for obtaining user input.

[0032] First, the test requirements input by the user are received. The requirements can be natural language (such as "compare the amount and time fields of the order table and the payment table") or structured configuration (such as a rule file in JSON or YAML format).

[0033] The diversity of input forms ensures that the system can adapt to the comparison intentions submitted by users of different roles (tester, developer, data administrator), and lays the foundation for subsequent automatic parsing.

[0034] S2. Analyze the test requirements to obtain a comparison parameter set, determine the table to be tested based on the test requirements, dynamically construct an instruction template based on the comparison parameter set and the metadata set of the table to be tested, input the instruction template into the large language model, and output a query instruction set; wherein the table to be tested includes a source table and a target table.

[0035] Test requirements are parsed textually to derive a set of comparison rules, or comparison parameter sets, extracted from the user-entered test requirements. These rules include the table name to be tested, key field mappings, and the allowable range of numerical deviations. Key fields, such as the primary key order_id, are important fields used to uniquely identify each record. Key field mappings refer to the correspondence between key fields in the source and target tables that have the same semantics but different field names. For example, mapping order_id in the source table to id in the target table ensures accurate cross-table record matching.

[0036] When performing text parsing on test requirements, the input type of the test requirements is first determined. There are multiple methods for determination. For example, if the test requirements contain a key-value pair structure, it is determined to be a structured configuration; otherwise, it is determined to be a natural language.

[0037] If the input is natural language, the system extracts a comparison parameter set from the test requirement using feature word extraction. This is a structured set of parameters generated by the system after parsing the user-entered test requirement. It guides subsequent comparison operations, such as data extraction, field mapping, and difference detection. This parameter set bridges the gap between user intent and system execution logic, determining the specific methods for query generation, primary key matching, and comparison strategies. It includes table information (source and target table names), field content, and error tolerance rules. For example, if the user-entered test requirement is "Compare the amount and time fields of the order table and payment table," the resulting comparison parameter set after parsing is: "Compared tables": ["Order table", "Payment table"], "Fields to check": ["Amount", "Creation time"].

[0038] If it is a structured configuration, its reading is completed by parsing the JSON or YAML file provided by the user. The system will directly extract the field configuration content from the file, including table information (source table name and target table name), field content, error tolerance rules and other information. This information is clearly listed in a structured form, so it can be directly organized into a comparison parameter set for subsequent comparison processes. Compared with natural language input, the comparison parameter set generated by the structured configuration has no essential difference in the final structure. Both contain table information (source table name and target table name), field content, error tolerance rules and other content. However, the two are obtained in different ways: for structured configuration, the field rules are explicitly provided by the user, and the system only needs to parse the fields; while for natural language input, the user description needs to be semantically understood and the fields extracted through a language model.

[0039] During parsing, determine the tables to be tested based on the test requirements, including source and target tables. The source table is the table to be migrated, and the target table is the table after migration. For example, the Orders table in the MySQL database is the source table, and the Payments table in the Oracle database is the target table.

[0040] After generating the comparison parameter set, a dynamic query generation instruction template is constructed based on the field names, field mappings, and comparison rules contained in the comparison parameter set, combined with the metadata of the source and target tables (including field names, field types, and primary key information). The instruction template is a structured prompt statement that guides the large language model in understanding the relationships between target fields and query requirements. The template includes prompts such as field mappings, necessary type conversion instructions, time format standards, and null value handling requirements.

[0041] According to the comparison parameter set and the metadata set of the table to be tested, the fields in the source table and the target table that need to be compared are determined and a field mapping relationship is established. A structured prompt statement is generated according to the mapping relationship to obtain an instruction template.

[0042] The instruction template generation process is as follows: connect to the database where the source and target tables are located, read the table structure information, including field names (such as user_name); field types (such as VARCHAR(50)); key fields (such as the primary key order_id); automatically associate fields with the same name based on the field content in the comparison parameter set (such as create_time on both sides), and also establish associations for fields with different names but the same meaning (such as user_name→customer_name), and check the compatibility of field types to ensure that the fields involved in the comparison in the source and target tables are comparable and convertible based on semantic consistency, thereby ensuring that the generated comparison instructions will not fail or produce incorrect results due to type mismatches during execution. For example, in the case of converting MySQL VARCHAR to Oracle TEXT, the corresponding conversion function or expression is automatically added to the instruction template, and incompatible fields are prompted. After completing the field association and type compatibility check, the names, types, mapping relationships, and necessary conversion logic of the corresponding fields in the source and target tables are organized into structured prompt statements to form an instruction template as the input for generating query instructions.

[0043] This template is then fed into a large language model (such as DeepSeek, encapsulated by LangChain), which generates a standardized set of SQL query instructions that can be executed across multiple databases, corresponding to the source and target tables. The large language model can directly use an existing large language model or be trained on demand through targeted training.

[0044] S3. Extract data from the source table according to the source table query instruction in the query instruction set to obtain a source table data set; extract data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set.

[0045] Based on the source table query instructions in the aforementioned query instruction set, a data extraction operation is performed on the source table to obtain the field data in the source table relevant to the comparison, forming a source table dataset. Simultaneously, based on the target table query instructions in the query instruction set, a data extraction operation is performed on the target table to extract the corresponding field data from the target table, generating a target table dataset. The source table dataset and target table dataset correspond to the query results of the source and target tables, respectively, and are used for subsequent data comparison processing. This extraction process corresponds to the query execution results of the source and target tables, respectively, ensuring the controllability and consistency of data sources.

[0046] S4. Match the records in the source table dataset and the target table dataset according to the primary key information to obtain a set of record pairs.

[0047] After extracting the source and target datasets, records in both datasets are matched against each other based on pre-defined primary key information. Primary key information specifically refers to one or more fields that uniquely identify each record in a table. This can be a single-field primary key (order_id) or a composite primary key composed of multiple fields (order_id + create_time). During the matching process, the primary key values ​​for each record in the source dataset are extracted and a hash table indexed by the primary key values ​​is constructed. The target dataset is then traversed, extracting primary key values ​​in the order of the same fields and searching for corresponding entries in the hash table. If the hash table contains the same primary key value, the two records are paired and added to the pair set. If not, the source or target record, respectively, is marked as missing. For composite primary keys, the system concatenates or hashes the primary key field values ​​in a predefined order before performing the matching process, ensuring correct handling of multi-field unique identifiers. The system compares source and target records based on the primary key fields, identifying matching pairs and constructing a pair set. This record pair set serves as the basis for subsequent field-level difference comparisons, ensuring that comparisons are performed only between records that logically belong to the same piece of business data.

[0048] S5. Compare the data differences of the source table dataset, the target table dataset, and the records to obtain the difference results.

[0049] After obtaining the source table dataset, the target table dataset, and a set of record pairs, a data comparison operation is performed to identify differences between the two data sets. By comparing corresponding fields in each pair of records, the consistency of values, structure, or existence is determined. Based on this, the comparison results are output, forming a difference result for subsequent processing. This comparison process, based on record pairs, ensures that the comparison operation is performed between records with corresponding relationships, improving the accuracy and specificity of the comparison.

[0050] S6. Visually display the difference results.

[0051] After obtaining the difference results, they are visualized. By presenting the difference content in a graphical interface, the system allows users to intuitively view the comparison results, including information such as field differences, missing records, or structural inconsistencies. The visual display facilitates user understanding and analysis of the difference content, supporting subsequent data repair, problem location, or verification.

[0052] As an optional implementation, Figure 2 is a flow chart of a source table data extraction method provided by an embodiment of the present invention. Figure 2 As shown, data is extracted from the source table according to the source table query instruction in the query instruction set to obtain a source table data set, including:

[0053] S301: Perform a syntax validity check on a source table query instruction in a query instruction set. If the syntax validity check passes, execute the source table query instruction in the source table and use the obtained query result as a first initial data set.

[0054] S302: Standardize the format of each field in the first initial data set to obtain a source table data set.

[0055] In the process of extracting data from the source table, the source table query instruction in the query instruction set is firstly checked for syntax validity. After passing the check, the query instruction is executed in the source table and the execution result is used as the first initial data set for subsequent processing.

[0056] Next, format standardization is performed on each field in the first initial dataset. This standardization process unifies the format of field values ​​to ensure that they conform to pre-defined specifications for data type, time format, numerical precision, and null value representation. This standardization results in a source table dataset with a unified structure that facilitates comparison.

[0057] During the syntax validation process, a triple security verification mechanism is employed. The first is syntax checking to verify compliance with database specifications; the second is permission control to ensure only SELECT queries are executed; and the third is risk interception to detect high-risk operations such as `DROP`. Queries that pass this triple security verification mechanism are considered to have passed syntax validation. Otherwise, an exception alert is generated and an error log is recorded for audit tracking.

[0058] As an optional implementation, Figure 3 Flowchart of the target table data extraction method provided by the embodiment of the present invention. Figure 3 As shown, data is extracted from the target table according to the target table query instruction in the query instruction set to obtain a target table data set, including:

[0059] S303: Perform a syntax validation on the target table query instruction in the query instruction set. If the syntax validation passes, execute the target table query instruction in the target table and use the obtained query result as the second initial data set.

[0060] S304: Standardize the format of each field in the second initial data set to obtain a target table data set.

[0061] In the process of extracting data from the target table, the target table query instruction in the query instruction set is firstly checked for syntax validity. After passing the check, the query instruction is executed in the target table and the execution result is used as the second initial data set for subsequent processing.

[0062] Next, format standardization is performed on each field in the second initial dataset. This standardization process unifies the format of field values ​​to ensure that they conform to pre-defined specifications for data type, time format, numerical precision, and null value representation. This standardization results in a target table dataset with a unified structure that facilitates comparison.

[0063] During the syntax validation process, a triple security verification mechanism is employed. The first is syntax checking to verify compliance with database specifications; the second is permission control to ensure only SELECT queries are executed; and the third is risk interception to detect high-risk operations such as `DROP`. Queries that pass this triple security verification mechanism are considered to have passed syntax validation. Otherwise, an exception alert is generated and an error log is recorded for audit tracking.

[0064] As an optional implementation, Figure 4 is a flow chart of a data difference comparison method provided by an embodiment of the present invention, such as Figure 4 As shown, data differences are compared based on the source table dataset, target table dataset, and records, and the difference results are obtained, including:

[0065] S501: Perform set operations on the source table data set and the target table data set, and identify row differences based on the set operation results.

[0066] Perform a set operation on the source and target datasets, identifying missing rows by comparing the primary keys of the source and target datasets. Missing rows = (AB) ∪ (BA), where A is the source dataset and B is the target dataset. For example, if the order ID is the primary key and source dataset A contains 1000 order IDs and target dataset B contains 980 order IDs, there are 20 rows of data differences between the two.

[0067] Furthermore, specific row differences that only exist in the source table or only in the target table are identified based on primary key comparison. For example, if a certain ID does not exist in the source table dataset but exists in the target table dataset, the target table dataset is considered to have lost data, and a missing record report is generated. If a certain ID exists in the source table dataset but not in the target table, the source table dataset is considered to be abnormal or there is dirty data in the target table dataset, triggering a data traceability check.

[0068] S502: Check whether the fields in each record pair exist and whether their types are compatible, and record missing fields or incompatible fields as structural differences.

[0069] A structural consistency check is performed on the corresponding fields in each record pair, specifically checking whether the fields exist in both the source and target tables and whether the field data types are compatible. If a field exists on one side but not on the other, or if the fields on both sides have the same name but inconsistent types, making direct comparison impossible, the field is considered to have a structural difference and recorded as a structural difference item.

[0070] S503: For the corresponding numeric fields in each record pair, calculate the error value between the two records in the corresponding record pair, and record the numeric fields whose error values ​​are greater than a preset error threshold as value differences.

[0071] When performing a field-level comparison, for each numeric field in a record pair, the error between the source and target table field values ​​is calculated. If the error is greater than the preset error threshold, the field is considered to have a value difference and is recorded as a value difference field.

[0072] S504: Take the row difference, structure difference and value difference as the difference result.

[0073] After completing all types of comparisons, the identified row, structure, and value differences are summarized and output as the final difference result. This difference result comprehensively reflects all inconsistencies between the source and target tables in terms of data existence, field structure, and field values, and is used for subsequent visualization and analysis.

[0074] As an optional implementation, Figure 5 is a flow chart of a visualization display method provided by an embodiment of the present invention, such as Figure 5 As shown, the difference results are visualized, including:

[0075] S601: Generate and display an interactive HTML table based on the difference results. For the current difference item in the difference results, convert the display format of its corresponding path into a row and column description format and display it in the interactive HTML table.

[0076] An interactive HTML table is generated based on the difference results, which is used to visually display various difference items. During the display process, for each difference item in the difference results (value difference items, structure difference items, and row difference items), the system converts its original path representation format into a user-friendly row and column description format to achieve tree path positioning. For example, `root[3]['price']` is converted to a readable "row 4 - price column". Clicking on the path in the HTML table directly jumps to the location of the current difference item in the corresponding table.

[0077] S602: Identify the target position of the current difference item in the table to be tested, use a preset number of data adjacent to the target position as context data, associate the corresponding path of the current difference item with the corresponding context data, and display it when the difference item is clicked.

[0078] When visualizing the difference results, the system identifies the specific target location of the current difference item within the table being tested, specifically the row number or primary key position of the difference item in the dataset. The system then extracts several records before and after the target location as contextual data for the difference item, helping to understand its data context. This contextual data is then associated with a path, allowing the current difference item and its contextual data to be displayed directly when the user clicks the path.

[0079] S603: Classify the difference levels of the current difference items according to preset rules, select preset colors according to the difference levels of the current difference items, and identify the current difference items according to the preset colors.

[0080] According to the preset difference level classification rules, the current difference items are classified and evaluated to determine their severity in the overall comparison results. The difference level may include categories such as key differences (primary key inconsistency / amount exceeded), warning differences (non-key field format does not match) and consistent items. Based on the difference level, the system selects the corresponding color from the preset color coding scheme, such as red for key differences, orange for warning differences, and green for consistent items. Finally, the system identifies the current difference item with the selected color in the visual display, allowing users to intuitively perceive the severity of the difference through color, so as to focus on key issues more efficiently.

[0081] The embodiment of the present invention also provides a test data comparison system, Figure 6 Schematic diagram of the structure of the test data comparison system provided by the embodiment of the present invention. Figure 6 As shown, the system includes:

[0082] The data acquisition module 100 is used to acquire the test requirements input by the user.

[0083] First, the test requirements input by the user are received. The requirements can be natural language (such as "compare the amount and time fields of the order table and the payment table") or structured configuration (such as a rule file in JSON or YAML format).

[0084] The diversity of input forms ensures that the system can adapt to the comparison intentions submitted by users of different roles (tester, developer, data administrator), and lays the foundation for subsequent automatic parsing.

[0085] The query instruction generation module 200 is used to parse the test requirements to obtain a comparison parameter set, determine the table to be tested based on the test requirements, dynamically construct an instruction template based on the comparison parameter set and the metadata set of the table to be tested, input the instruction template into the large language model, and output a query instruction set; wherein the table to be tested includes a source table and a target table.

[0086] Test requirements are parsed textually to derive a set of comparison rules, or comparison parameter sets, extracted from the user-entered test requirements. These rules include the table name to be tested, key field mappings, and the allowable range of numerical deviations. Key fields, such as the primary key order_id, are important fields used to uniquely identify each record. Key field mappings refer to the correspondence between key fields in the source and target tables that have the same semantics but different field names. For example, mapping order_id in the source table to id in the target table ensures accurate cross-table record matching.

[0087] When performing text parsing on test requirements, the input type of the test requirements is first determined. There are multiple methods for determination. For example, if the test requirements contain a key-value pair structure, it is determined to be a structured configuration; otherwise, it is determined to be a natural language.

[0088] If the input is natural language, the system extracts a comparison parameter set from the test requirement using feature word extraction. This is a structured set of parameters generated by the system after parsing the user-entered test requirement. It guides subsequent comparison operations, such as data extraction, field mapping, and difference detection. This parameter set bridges the gap between user intent and system execution logic, determining the specific methods for query generation, primary key matching, and comparison strategies. It includes table information (source and target table names), field content, and error tolerance rules. For example, if the user-entered test requirement is "Compare the amount and time fields of the order table and payment table," the resulting comparison parameter set after parsing is: "Compared tables": ["Order table", "Payment table"], "Fields to check": ["Amount", "Creation time"].

[0089] If it is a structured configuration, its reading is completed by parsing the JSON or YAML file provided by the user. The system will directly extract the field configuration content from the file, including table information (source table name and target table name), field content, error tolerance rules and other information. This information is clearly listed in a structured form, so it can be directly organized into a comparison parameter set for subsequent comparison processes. Compared with natural language input, the comparison parameter set generated by the structured configuration has no essential difference in the final structure. Both contain table information (source table name and target table name), field content, error tolerance rules and other content. However, the two are obtained in different ways: for structured configuration, the field rules are explicitly provided by the user, and the system only needs to parse the fields; while for natural language input, the user description needs to be semantically understood and the fields extracted through a language model.

[0090] During parsing, determine the tables to be tested based on the test requirements, including source and target tables. The source table is the table to be migrated, and the target table is the table after migration. For example, the Orders table in the MySQL database is the source table, and the Payments table in the Oracle database is the target table.

[0091] After generating the comparison parameter set, a dynamic query generation instruction template is constructed based on the field names, field mappings, and comparison rules contained in the comparison parameter set, combined with the metadata of the source and target tables (including field names, field types, and primary key information). The instruction template is a structured prompt statement that guides the large language model in understanding the relationships between target fields and query requirements. The template includes prompts such as field mappings, necessary type conversion instructions, time format standards, and null value handling requirements.

[0092] According to the comparison parameter set and the metadata set of the table to be tested, the fields in the source table and the target table that need to be compared are determined and a field mapping relationship is established. A structured prompt statement is generated according to the mapping relationship to obtain an instruction template.

[0093] The instruction template generation process is as follows: connect to the database where the source and target tables are located, read the table structure information, including field names (such as user_name); field types (such as VARCHAR(50)); key fields (such as the primary key order_id); automatically associate fields with the same name based on the field content in the comparison parameter set (such as create_time on both sides), and also establish associations for fields with different names but the same meaning (such as user_name→customer_name), and check the compatibility of field types to ensure that the fields involved in the comparison in the source and target tables are comparable and convertible based on semantic consistency, thereby ensuring that the generated comparison instructions will not fail or produce incorrect results due to type mismatches during execution. For example, in the case of converting MySQL VARCHAR to Oracle TEXT, the corresponding conversion function or expression is automatically added to the instruction template, and incompatible fields are prompted. After completing the field association and type compatibility check, the names, types, mapping relationships, and necessary conversion logic of the corresponding fields in the source and target tables are organized into structured prompt statements to form an instruction template as the input for generating query instructions.

[0094] This template is then fed into a large language model (such as DeepSeek, encapsulated by LangChain), which generates a standardized set of SQL query instructions that can be executed across multiple databases, corresponding to the source and target tables. The large language model can directly use an existing large language model or be trained on demand through targeted training.

[0095] The data extraction module 300 is configured to extract data from a source table according to source table query instructions in the query instruction set to obtain a source table data set; and to extract data from a target table according to target table query instructions in the query instruction set to obtain a target table data set.

[0096] Based on the source table query instructions in the aforementioned query instruction set, a data extraction operation is performed on the source table to obtain the field data in the source table relevant to the comparison, forming a source table dataset. Simultaneously, based on the target table query instructions in the query instruction set, a data extraction operation is performed on the target table to extract the corresponding field data from the target table, generating a target table dataset. The source table dataset and target table dataset correspond to the query results of the source and target tables, respectively, and are used for subsequent data comparison processing. This extraction process corresponds to the query execution results of the source and target tables, respectively, ensuring the controllability and consistency of data sources.

[0097] The data matching module 400 is used to match the records in the source table data set and the target table data set according to the primary key information to obtain a set of record pairs.

[0098] After extracting the source and target datasets, the system performs a record-by-record match based on pre-determined primary key information. Primary key information specifically refers to one or more fields that uniquely identify each record in a table. This can be a single-field primary key (order_id) or a composite primary key composed of multiple fields (order_id + create_time). During the matching process, the system first extracts the primary key value for each record from the source dataset and constructs a hash table indexed by the primary key value. The system then traverses the target dataset, extracting the primary key values ​​for each record in the same field order and searching for the corresponding entry in the hash table. If the hash table contains the same primary key value, the two records are paired and added to the pair set. If not, the source or target record, respectively, is marked as missing. For composite primary keys, the system concatenates or hashes the primary key field values ​​in a predefined order before performing the matching process, ensuring accurate handling of multi-field unique identifiers. The system compares the source and target records based on the primary key fields, identifying matching pairs and constructing a pair set. This record pair set serves as the basis for subsequent field-level difference comparisons, ensuring that comparisons are performed only between records that logically belong to the same piece of business data.

[0099] The difference comparison module 500 is used to perform data difference comparison based on the source table data set, the target table data set and the record pair set to obtain a difference result.

[0100] After obtaining the source table dataset, the target table dataset, and a set of record pairs, a data comparison operation is performed to identify differences between the two data sets. By comparing corresponding fields in each pair of records, the consistency of values, structure, or existence is determined. Based on this, the comparison results are output, forming a difference result for subsequent processing. This comparison process, based on record pairs, ensures that the comparison operation is performed between records with corresponding relationships, improving the accuracy and specificity of the comparison.

[0101] The visualization display module 600 is used to visualize the difference results.

[0102] After obtaining the difference results, they are visualized. By presenting the difference content in a graphical interface, the system allows users to intuitively view the comparison results, including information such as field differences, missing records, or structural inconsistencies. The visual display facilitates user understanding and analysis of the difference content, supporting subsequent data repair, problem location, or verification.

[0103] As an optional implementation, Figure 7 Schematic diagram of the structure of the data extraction module for extracting source table data provided by an embodiment of the present invention. Figure 7 As shown, the data extraction module 300 includes:

[0104] The first query submodule 3001 is used to perform syntax validation on the source table query instruction in the query instruction set. If the syntax validation passes, the source table query instruction is executed in the source table, and the obtained query result is used as the first initial data set.

[0105] The first standardization submodule 3002 is configured to standardize the format of each field in the first initial data set to obtain a source table data set.

[0106] In the process of extracting data from the source table, the source table query instruction in the query instruction set is firstly checked for syntax validity. After passing the check, the query instruction is executed in the source table and the execution result is used as the first initial data set for subsequent processing.

[0107] Next, format standardization is performed on each field in the first initial dataset. This standardization process unifies the format of field values ​​to ensure that they conform to pre-defined specifications for data type, time format, numerical precision, and null value representation. This standardization results in a source table dataset with a unified structure that facilitates comparison.

[0108] During the syntax validation process, a triple security verification mechanism is employed. The first is syntax checking to verify compliance with database specifications; the second is permission control to ensure only SELECT queries are executed; and the third is risk interception to detect high-risk operations such as `DROP`. Queries that pass this triple security verification mechanism are considered to have passed syntax validation. Otherwise, an exception alert is generated and an error log is recorded for audit tracking.

[0109] As an optional implementation, Figure 8 FIG. 1 is a structural diagram of a data extraction module for extracting target table data provided by an embodiment of the present invention. Figure 8 As shown, the data extraction module 300 includes:

[0110] The second query submodule 3003 is used to perform syntax validation on the target table query instruction in the query instruction set. When the validation passes the syntax validation, the target table query instruction is executed in the target table, and the query result obtained is used as the second initial data set.

[0111] The second standardization submodule 3004 is configured to perform format standardization on each field in the second initial data set to obtain a target table data set.

[0112] In the process of extracting data from the target table, the target table query instruction in the query instruction set is firstly checked for syntax validity. After passing the check, the query instruction is executed in the target table and the execution result is used as the second initial data set for subsequent processing.

[0113] Next, format standardization is performed on each field in the second initial dataset. This standardization process unifies the format of field values ​​to ensure that they conform to pre-defined specifications for data type, time format, numerical precision, and null value representation. This standardization results in a target table dataset with a unified structure that facilitates comparison.

[0114] During the syntax validation process, a triple security verification mechanism is employed. The first is syntax checking to verify compliance with database specifications; the second is permission control to ensure only SELECT queries are executed; and the third is risk interception to detect high-risk operations such as `DROP`. Queries that pass this triple security verification mechanism are considered to have passed syntax validation. Otherwise, an exception alert is generated and an error log is recorded for audit tracking.

[0115] As an optional implementation, Figure 9 is a structural diagram of a difference comparison module provided by an embodiment of the present invention, such as Figure 9 As shown, the difference comparison module 500 includes:

[0116] The row comparison submodule 5001 is used to perform set operations on the source table data set and the target table data set, and identify row differences based on the set operation results.

[0117] Perform a set operation on the source and target datasets, identifying missing rows by comparing the primary keys of the source and target datasets. Missing rows = (AB) ∪ (BA), where A is the source dataset and B is the target dataset. For example, if the order ID is the primary key and source dataset A contains 1000 order IDs and target dataset B contains 980 order IDs, there are 20 rows of data differences between the two.

[0118] Furthermore, specific row differences that only exist in the source table or only in the target table are identified based on primary key comparison. For example, if a certain ID does not exist in the source table dataset but exists in the target table dataset, the target table dataset is considered to have lost data, and a missing record report is generated. If a certain ID exists in the source table dataset but not in the target table, the source table dataset is considered to be abnormal or there is dirty data in the target table dataset, triggering a data traceability check.

[0119] The structure comparison submodule 5002 is used to check whether the fields in each record pair exist and whether the types are compatible, and record the missing fields or the incompatible fields as structure differences.

[0120] A structural consistency check is performed on the corresponding fields in each record pair, specifically checking whether the fields exist in both the source and target tables and whether the field data types are compatible. If a field exists on one side but not on the other, or if the fields on both sides have the same name but inconsistent types, making direct comparison impossible, the field is considered to have a structural difference and recorded as a structural difference item.

[0121] The value comparison submodule 5003 is used to calculate the error value of the two records in each record pair for the corresponding numerical field, and record the numerical field with an error value greater than a preset error threshold as a value difference.

[0122] When performing a field-level comparison, for each numeric field in a record pair, the error between the source and target table field values ​​is calculated. If the error is greater than the preset error threshold, the field is considered to have a value difference and is recorded as a value difference field.

[0123] The result generation submodule 5004 is used to take the row difference, structure difference and value difference as the difference result.

[0124] After completing all types of comparisons, the identified row, structure, and value differences are summarized and output as the final difference result. This difference result comprehensively reflects all inconsistencies between the source and target tables in terms of data existence, field structure, and field values, and is used for subsequent visualization and analysis.

[0125] As an optional implementation, Figure 10 is a structural diagram of a visual display module provided by an embodiment of the present invention, such as Figure 10 As shown, the visual display module 600 includes:

[0126] The format conversion submodule 6001 is used to generate and display an interactive HTML table based on the difference results. For the current difference item in the difference results, the display format of its corresponding path is converted into a row and column description format and displayed in the interactive HTML table.

[0127] An interactive HTML table is generated based on the difference results, which is used to visually display various difference items. During the display process, for each difference item in the difference results (value difference items, structure difference items, and row difference items), the system converts its original path representation format into a user-friendly row and column description format to achieve tree path positioning. For example, `root[3]['price']` is converted to a readable "row 4 - price column". Clicking on the path in the HTML table directly jumps to the location of the current difference item in the corresponding table.

[0128] The context data association submodule 6002 is used to identify the target position of the current difference item in the table to be tested, use the data of a preset number of items adjacent to the target position as context data, associate the corresponding path of the current difference item with the corresponding context data, and display it when the difference item is clicked.

[0129] When visualizing the difference results, the system identifies the specific target location of the current difference item within the table being tested, specifically the row number or primary key position of the difference item in the dataset. The system then extracts several records before and after the target location as contextual data for the difference item, helping to understand its data context. This contextual data is then associated with a path, allowing the current difference item and its contextual data to be displayed directly when the user clicks the path.

[0130] The color identification submodule 6003 is used to classify the difference level of the current difference item according to a preset rule, select a preset color according to the difference level of the current difference item, and identify the current difference item according to the preset color.

[0131] According to the preset difference level classification rules, the current difference items are classified and evaluated to determine their severity in the overall comparison results. The difference level may include categories such as key differences (primary key inconsistency / amount exceeded), warning differences (non-key field format does not match) and consistent items. Based on the difference level, the system selects the corresponding color from the preset color coding scheme, such as red for key differences, orange for warning differences, and green for consistent items. Finally, the system identifies the current difference item with the selected color in the visual display, allowing users to intuitively perceive the severity of the difference through color, so as to focus on key issues more efficiently.

[0132] The above technical solution has the following beneficial effects: by analyzing the user's test requirements and combining with a large language model to generate dynamic instructions, data from different databases can be searched and compared, and differences can be displayed, thereby reducing manual participation and automatically adapting to field differences or format differences between different systems, thereby improving processing efficiency; after the search and comparison, the data is analyzed and visualized at the field level, so that users can quickly understand the source of the differences and locate the problem, thereby improving data comparison efficiency and display intuitiveness.

[0133] The specific implementation methods of the above inventions further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above content is only the specific implementation methods of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A test data comparison method, characterized in that: include: Test requirements for obtaining user input; Parsing the test requirements to obtain a comparison parameter set, determining a table to be tested based on the test requirements, dynamically constructing an instruction template based on the comparison parameter set and a metadata set of the table to be tested, inputting the instruction template into a large language model, and outputting a query instruction set; wherein the table to be tested includes a source table and a target table; Extracting data from the source table according to the source table query instruction in the query instruction set to obtain a source table data set; extracting data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set; Matching the records in the source table data set and the target table data set according to the primary key information to obtain a set of record pairs; Compare the data differences of the source table data set, the target table data set and the records to obtain a difference result; The difference results are visualized.

2. The test data comparison method according to claim 1, characterized in that: Extracting data from the source table according to the source table query instruction in the query instruction set to obtain a source table data set includes: Performing a syntax validity check on the source table query instruction in the query instruction set; if the syntax validity check passes, executing the source table query instruction in the source table, and using the obtained query result as the first initial data set; The format of each field in the first initial data set is standardized to obtain a source table data set.

3. The test data comparison method according to claim 2, characterized in that: The step of extracting data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set includes: Performing a syntax validation on the target table query instruction in the query instruction set; when the syntax validation passes, executing the target table query instruction in the target table, and using the obtained query result as the second initial data set; The format of each field in the second initial data set is standardized to obtain a target table data set.

4. The test data comparison method according to claim 3, characterized in that: The data difference comparison is performed based on the source table data set, the target table data set, and the record pair set to obtain the difference result, including: Performing a set operation on the source table data set and the target table data set, and identifying row differences based on the set operation results; Check whether the fields in each record pair exist and whether their types are compatible, and record missing fields or incompatible fields as structural differences; For the corresponding numeric fields in each record pair, calculate the error value between the two records in the corresponding record pair, and record the numeric fields with error values ​​greater than the preset error threshold as value differences; The row difference, structure difference and value difference are collectively regarded as the difference result.

5. The test data comparison method according to claim 4, characterized in that: The visual display of the difference results includes: Generate and display an interactive HTML table according to the difference result, and for a current difference item in the difference result, convert the display format of its corresponding path into a row and column description format, and display it in the interactive HTML table; Identify a target position of the current difference item in the table to be tested, use a preset number of data items adjacent to the target position as context data, and associate a corresponding path of the current difference item with the corresponding context data for display when the difference item is clicked; The difference levels of the current difference items are divided according to a preset rule, a preset color is selected according to the difference level of the current difference items, and the current difference items are identified according to the preset color.

6. A test data comparison system, characterized in that: include: Data acquisition module, used to obtain test requirements input by users; a query instruction generation module, configured to parse the test requirements to obtain a comparison parameter set, determine a table to be tested based on the test requirements, dynamically construct an instruction template based on the comparison parameter set and the metadata set of the table to be tested, input the instruction template into a large language model, and output a query instruction set; wherein the table to be tested includes a source table and a target table; a data extraction module configured to extract data from the source table according to the source table query instruction in the query instruction set to obtain a source table data set; and to extract data from the target table according to the target table query instruction in the query instruction set to obtain a target table data set; A data matching module is used to match the records in the source table data set and the target table data set according to the primary key information to obtain a set of record pairs; A difference comparison module is used to compare data differences based on the source table data set, the target table data set and the records to obtain difference results; The visualization display module is used to visualize the difference results.

7. The test data comparison system according to claim 6, characterized in that: The data extraction module includes: a first query submodule, configured to perform a syntax validity check on a source table query instruction in the query instruction set; if the syntax validity check passes, execute the source table query instruction in the source table, and use the obtained query result as a first initial data set; The first standardization submodule is configured to standardize the format of each field in the first initial data set to obtain a source table data set.

8. The test data comparison system according to claim 7, characterized in that: The data extraction module includes: A second query submodule is configured to perform a syntax validation on the target table query instruction in the query instruction set, and when the syntax validation passes, execute the target table query instruction in the target table, and use the obtained query result as the second initial data set; The second standardization submodule is configured to standardize the format of each field in the second initial data set to obtain a target table data set.

9. The test data comparison system according to claim 8, characterized in that: The difference comparison module includes: A row comparison submodule is used to perform set operations on the source table data set and the target table data set, and identify row differences based on the set operation results; The structure comparison submodule is used to check whether the fields in each record pair exist and whether the types are compatible. Fields that are missing or have incompatible types are recorded as structural differences. The value comparison submodule is used to calculate the error value of the two records in each record pair for the corresponding numerical field, and record the numerical field with an error value greater than a preset error threshold as the value difference; The result generation submodule is used to take the row difference, structure difference and value difference as the difference result.

10. The test data comparison system according to claim 9, characterized in that: The visual display module includes: a format conversion submodule, configured to generate and display an interactive HTML table based on the difference results, convert the display format of the path corresponding to the current difference item in the difference results into a row and column description format, and display the result in the interactive HTML table, so as to be displayed when the difference item is clicked; a context data association submodule, configured to identify a target position of the current difference item in the table to be tested, use a preset number of data items adjacent to the target position as context data, and associate a corresponding path of the current difference item with the corresponding context data; The color identification submodule is configured to classify the difference levels of the current difference items according to a preset rule, select a preset color according to the difference level of the current difference items, and identify the current difference items according to the preset color.

Images