CN116226168A - Method and system for verifying wide-table data - Google Patents

Abstract

The application relates to a method and a system for verifying wide-table data, and belongs to the technical field of data processing. Comprising the following steps: storing the related data of the query condition and the expected data into a test data table to form a test case; splicing the query condition related data in the test data table to obtain an SQL sentence, and executing the SQL sentence in the data table to be verified to obtain execution result data; comparing the execution result data with expected data to obtain a data verification result; according to the method, the SQL sentence is automatically analyzed and generated according to the related data of the query condition, the SQL sentence is executed in the data table to be verified to obtain the execution result data, the execution result data is compared with the expected data to obtain the data verification result, and the manual verification is not needed in the data query and comparison processing process, so that the data verification is more accurate and efficient.

Description

Method and system for verifying wide-table data

Technical Field

The application belongs to the technical field of data processing, and particularly relates to a method and a system for verifying wide-table data.

Background

In the traditional industry, with the development of services, the system architecture becomes more and more complex, and the storage and demand for data also become more and more diversified; for the current complicated business, the wide table in the database is very widely used, and for the back-end service of software, after a scheduler (a module in a kernel for scheduling process execution is called a scheduler) usually walks through the process, the data values of a plurality of fields in the data table need to be verified, and then a plurality of SQL (structured query language) is written to check, and whether the result is an expected result is judged by naked eyes after checking, so that inaccurate and low efficiency data verification is easily caused by the reasons of missed view, bad view and the like.

Disclosure of Invention

Therefore, the invention provides a method and a system for verifying wide-table data, which are used for solving the problems that the existing method for verifying the wide-table data is easy to cause inaccurate and low in efficiency of data verification due to missed view, poor view and other human reasons.

In order to achieve the above purpose, the present application adopts the following technical scheme:

in a first aspect, the present invention provides a method for broad-table data validation, comprising:

storing the related data and the expected data of the query condition into a test data table;

splicing the query condition related data in the test data table to obtain an SQL sentence, and executing the SQL sentence in the data table to be verified to obtain execution result data;

and comparing the execution result data with expected data to obtain a data verification result.

Further, the query condition related data includes a case name, a target data table name, a field to be verified, and a screening condition, wherein the case name is not repeated as a unique identifier, and the field to be verified corresponds to the expected data.

Further, the step of splicing the field data in the test data table to obtain an SQL sentence is to automatically splice the target data table name, the field to be verified and the screening condition into the SQL sentence.

Further, the execution result data includes a field name and corresponding actual result data, and/or,

the expected data includes expected field names and corresponding expected result data.

Further, the comparing the execution result data with the expected data to obtain a data verification result is to sequentially execute a query comparison operation on each piece of data in the execution result data, where the query comparison operation is as follows: and searching a corresponding expected field name according to the field name in each piece of data, and comparing the corresponding actual result data with the expected result data to obtain a comparison result as a data verification result.

Further, the searching for the corresponding expected field name according to the field name in each piece of data is searching for the expected field name identical to the field name in the execution result data from the expected data, and/or the comparing operation is comparing whether the corresponding actual result data is equal to the expected result data.

In a second aspect, the present invention provides a system for broad-table data validation, the system comprising:

the new case module is used for storing the related data of the query condition and the expected data into the test data table;

the execution case module is used for splicing the query condition related data in the test data table to obtain an SQL sentence, executing the SQL sentence in the data table to be verified to obtain execution result data, and comparing the execution result data with expected data to obtain a data verification result;

and the checking result module is used for checking the data verification result.

Further, the query condition related data includes a case name, a target data table name, a field to be verified, and a screening condition, wherein the case name is not repeated as a unique identifier, and the field to be verified corresponds to the expected data.

Further, the execution case module comprises an SQL generating unit, an SQL executing unit and a query comparing unit, wherein,

the SQL generating unit is used for automatically splicing the target data table name, the field to be verified and the screening condition into an SQL sentence;

the SQL execution unit is used for executing the generated SQL statement in the data table to be verified to obtain execution result data;

the query comparison unit is used for comparing the execution result data with the expected data to finally obtain a data verification result.

The application adopts the technical scheme, possesses following beneficial effect at least:

and automatically analyzing and generating SQL sentences according to the related data of the query conditions, executing the SQL sentences in the data table to be verified to obtain execution result data, comparing the execution result data with expected data to obtain a data verification result, and ensuring that the data verification is more accurate and efficient because no manual verification is needed in the data query and comparison processing process.

It is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the application.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described below, it being obvious that the drawings in the following description are only some embodiments of the present application, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

FIG. 1 is a flow chart of a method for wide table data verification in an exemplary embodiment of the invention;

FIG. 2 is a schematic diagram of an input page of a system for wide table data verification in an exemplary embodiment of the invention;

FIG. 3 is a schematic diagram of a processing page of a system for wide table data verification in an exemplary embodiment of the invention;

FIG. 4 is a diagram of a data verification results page of a system for wide table data verification in an exemplary embodiment of the invention;

FIG. 5 is a diagram of a data structure E-R of a system for wide table data verification in an exemplary embodiment of the invention;

the invention is further described below with reference to the drawings and the detailed description.

Detailed Description

For the purpose of making the objects, technical solutions and advantages of the present application more apparent, the technical solutions of the present application will be described in detail below. It will be apparent that the described embodiments are only some, but not all, of the embodiments of the present application. All other embodiments, based on the examples herein, which are within the scope of the protection sought by those of ordinary skill in the art without undue effort, are intended to be encompassed by the present application.

In the existing wide-table data verification service, a plurality of SQL sentences are required to be written according to requirements to search in a corresponding database, and then whether the searched data is an expected result is judged through naked eye comparison, so that inaccurate and low efficiency data verification is easily caused by human reasons such as missed view, poor view and the like.

According to the method and the system for verifying the wide table data, the SQL statement can be automatically generated by analyzing the related data of the pre-query condition, then the SQL statement is executed in the data table to be verified to obtain the execution result data, and the execution result data is compared with the expected data to obtain the data verification result, wherein manual intervention is not needed in the process of generating the SQL statement, querying the data and comparing the SQL statement, so that the data verification is more accurate and efficient.

The method for pushing the message in real time in the application is described below through specific embodiments.

Referring to fig. 1, fig. 1 is a flowchart of a method for verifying broad-table data according to an exemplary embodiment of the present invention, including:

storing the related data and the expected data of the query condition into a test data table;

splicing the query condition related data in the test data table to obtain an SQL sentence, and executing the SQL sentence in the data table to be verified to obtain execution result data;

and comparing the execution result data with expected data to obtain a data verification result.

It should be noted that, the method for verifying the data of the wide table provided in this embodiment may be used for verifying the data table after the scheduler walks through the process in the back-end service, and may also be used for the wide table case with more data table fields in other services.

In specific practice, the manual operation is to input and store the related data and expected data of the query condition into a test data table, and other data processing processes are all processed by adopting program automation.

Specifically, the query condition related data includes a case name, a target data table name, a field to be verified, and a screening condition, wherein the case name is not repeated as a unique identifier, and the field to be verified corresponds to the expected data.

It can be understood that, according to the technical scheme provided by the embodiment, the provided method can automatically analyze and generate the SQL statement according to the related data of the query condition, execute the SQL statement in the data table to be verified to obtain the execution result data, compare the execution result data with the expected data to obtain the data verification result, and the processing procedures of data query and comparison do not need manual verification, so that the data verification is more accurate and efficient.

In one embodiment, the splicing the field data in the test data table to obtain an SQL statement automatically splices the target data table name, the field to be verified and the screening condition into the SQL statement.

Specifically, the name of the target data table is the data table to be detected by the target and is used as a table in the SQL sentence; the field to be verified is a field to be verified in a data table to be detected by the target; the screening condition is a query condition on a data table to be detected by the target and forms a SQL internal sphere condition with a field to be verified.

It should be noted that, the field to be verified is designed as a list set, and because there may be multiple fields to be verified, when the SQL statement is automatically generated, the list set needs to be decomposed.

It can be understood that, according to the method in this embodiment, the SQL statement can be automatically generated after splicing according to the preset target data table name, the field to be verified and the screening condition, so that the manual writing of the SQL statement is replaced, and writing errors caused by manual misoperation are reduced.

In one embodiment, the execution result data includes a field name and corresponding actual result data, and/or the expected data includes an expected field name and corresponding expected result data.

Specifically, comparing the execution result data with expected data to obtain a data verification result, and sequentially executing query comparison operation on each piece of data in the execution result data, wherein the query comparison operation is as follows: and searching a corresponding expected field name according to the field name in each piece of data, and comparing the corresponding actual result data with the expected result data to obtain a comparison result as a data verification result.

It should be noted that, the searching for the corresponding expected field name according to the field name in each piece of data is searching for the expected field name identical to the field name in the execution result data from the expected data, and the comparing operation is comparing whether the corresponding actual result data is identical to the expected result data.

It can be understood that the query comparison operation is performed by using the program, so that the efficiency of the query comparison operation can be improved, and the accuracy is high.

In one embodiment, a system for wide-table data validation is provided, the system comprising:

the new case module is used for storing the related data of the query condition and the expected data into the test data table;

the execution case module is used for splicing the query condition related data in the test data table to obtain an SQL sentence, executing the SQL sentence in the data table to be verified to obtain execution result data, and comparing the execution result data with expected data to obtain a data verification result;

and the checking result module is used for checking the data verification result.

Specifically, the query condition related data includes a case name, a target data table name, a field to be verified, and a screening condition, wherein the case name is not repeated as a unique identifier, and the field to be verified corresponds to the expected data.

Further, the execution case module comprises an SQL generating unit, an SQL executing unit and a query comparing unit, wherein,

the SQL generating unit is used for automatically splicing the target data table name, the field to be verified and the screening condition into an SQL sentence; the SQL execution unit is used for executing the generated SQL statement in the data table to be verified to obtain execution result data; the query comparison unit is used for comparing the execution result data with the expected data to finally obtain a data verification result.

In actual operation, an environment is built by using SpringBoot+mybatis+mysql/oracle+maven+jQuery, maven items are created, jar of spring, mybatis and mysql is introduced, data connection information is configured by using application. Yml files, and hierarchical management is set by using SSM, wherein the management steps are as follows: an entity layer, attribute classes, storage entities; the dao layer, the data access layer, the operation database; a service layer, a business logic layer, a detailed logic writing layer and a Dao layer interface operation database calling layer; and the controller layer requests the forwarding layer, receives page data to the service, and returns the result to the page after processing.

It should be noted that, referring to fig. 2, fig. 2 is a schematic diagram of an input page of a system for verifying wide-table data, and a user inputs relevant data of query conditions and expected data into a corresponding table and submits the relevant data and the expected data to directly store in a test data table; referring to fig. 3, fig. 3 is a schematic view of a processing page of a system for verifying wide-table data, after clicking and executing, performing SQL retrieval of source data on a table field newly added into a test data table, locating data to be verified according to a target data table name, a field to be verified and a screening condition of a new case, actually inquiring to execute result data, extracting actual result values of the expected fields according to expected data in the case, updating the actual result values to the actual result data fields, comparing the expected result data with the actual result data, updating corresponding data verification result fields, if the expected result data is consistent with the actual result data, the updated data verification result is true, and if the expected result data is inconsistent with the actual result data, the updated data verification result is false; referring to fig. 4, in the data verification result, if true, the corresponding field is marked with a hook, and if false, the corresponding field is marked with a fork.

It can be understood that the system of the implementation realizes the whole-course automatic data processing after the related data and the expected data of the query condition are preset, so that the data verification is more accurate and has high efficiency; and the data verification result can be displayed in an imaging way, so that the data verification result is convenient to check.

In the description of the present specification, a description referring to terms "one embodiment," "some embodiments," "examples," "specific examples," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the present application. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiments or examples. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.

Although embodiments of the present application have been shown and described above, it will be understood that the above embodiments are illustrative and not to be construed as limiting the application, and that variations, modifications, alternatives, and variations may be made to the above embodiments by one of ordinary skill in the art within the scope of the application.

Claims (9)

1. A method for wide-table data verification, the method comprising:

storing the related data and the expected data of the query condition into a test data table;

splicing the query condition related data in the test data table to obtain an SQL sentence, and executing the SQL sentence in the data table to be verified to obtain execution result data;

and comparing the execution result data with expected data to obtain a data verification result.

2. The method of claim 1, wherein the query condition related data includes a case name, a target data table name, a field to be verified, and a screening condition, wherein the case name is not repeated as a unique identification, and the field to be verified corresponds to the expected data.

3. The method of claim 2, wherein the splicing the field data in the test data table to obtain an SQL statement automatically splices the target data table name, the field to be verified and the filtering condition into the SQL statement.

4. The method according to claim 2, wherein the execution result data comprises a field name and corresponding actual result data, and/or,

the expected data includes expected field names and corresponding expected result data.

5. The method according to claim 4, wherein the comparing the execution result data with the expected data to obtain the data verification result is specifically:

and sequentially executing query comparison operation on each piece of data in the execution result data, wherein the query comparison operation is to find out the corresponding expected field name according to the field name in each piece of data, and compare the corresponding actual result data with the expected result data to obtain a comparison result as a data verification result.

6. The method of claim 5, wherein the searching for the corresponding expected field name according to the field name in each piece of data is searching for the same expected field name as the field name in the execution result data from the expected data, and/or the comparing operation is comparing whether the corresponding actual result data is equal to the expected result data.

7. A system for wide-table data verification, the system comprising:

the new case module is used for storing the related data of the query condition and the expected data into the test data table;

the execution case module is used for splicing the query condition related data in the test data table to obtain an SQL sentence, executing the SQL sentence in the data table to be verified to obtain execution result data, and comparing the execution result data with expected data to obtain a data verification result;

and the checking result module is used for checking the data verification result.

8. The system of claim 7, wherein the query condition related data includes a case name, a target data table name, a field to be verified, and a screening condition, wherein the case name is not repeated as a unique identification, and the field to be verified corresponds to the expected data.

9. The system of claim 8, wherein the execution case module comprises an SQL generation unit, an SQL execution unit, and a query comparison unit, wherein,

the SQL generating unit is used for automatically splicing the target data table name, the field to be verified and the screening condition into an SQL sentence;

the SQL execution unit is used for executing the generated SQL statement in the data table to be verified to obtain execution result data;

the query comparison unit is used for comparing the execution result data with the expected data to finally obtain a data verification result.

Images