CN116860781A - Data verification method, device, terminal equipment and storage medium - Google Patents
Data verification method, device, terminal equipment and storage medium Download PDFInfo
- Publication number
- CN116860781A CN116860781A CN202310959251.1A CN202310959251A CN116860781A CN 116860781 A CN116860781 A CN 116860781A CN 202310959251 A CN202310959251 A CN 202310959251A CN 116860781 A CN116860781 A CN 116860781A
- Authority
- CN
- China
- Prior art keywords
- rule
- verification
- data
- data verification
- sql
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000013524 data verification Methods 0.000 title claims abstract description 136
- 238000000034 method Methods 0.000 title claims abstract description 71
- 238000012795 verification Methods 0.000 claims abstract description 185
- 238000004458 analytical method Methods 0.000 claims abstract description 24
- 230000014509 gene expression Effects 0.000 claims description 38
- 238000012216 screening Methods 0.000 claims description 12
- 238000007405 data analysis Methods 0.000 claims description 7
- 230000000694 effects Effects 0.000 abstract description 8
- 230000002708 enhancing effect Effects 0.000 abstract description 4
- 238000012545 processing Methods 0.000 description 29
- 230000008569 process Effects 0.000 description 12
- 238000004364 calculation method Methods 0.000 description 9
- 238000010586 diagram Methods 0.000 description 9
- 230000006870 function Effects 0.000 description 5
- 238000004891 communication Methods 0.000 description 3
- 238000007726 management method Methods 0.000 description 3
- 238000012946 outsourcing Methods 0.000 description 3
- 239000002699 waste material Substances 0.000 description 3
- 230000009471 action Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 238000013461 design Methods 0.000 description 1
- 238000001514 detection method Methods 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000000605 extraction Methods 0.000 description 1
- 230000010354 integration Effects 0.000 description 1
- 230000005055 memory storage Effects 0.000 description 1
- 238000010295 mobile communication Methods 0.000 description 1
- 238000012544 monitoring process Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000002688 persistence Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 230000001960 triggered effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2458—Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
- G06F16/2462—Approximate or statistical queries
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Probability & Statistics with Applications (AREA)
- Software Systems (AREA)
- Fuzzy Systems (AREA)
- Debugging And Monitoring (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a data verification method, a device, terminal equipment and a storage medium, wherein the method comprises the following steps: responding to the verification execution command, and acquiring input parameters; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. The target verification rule is obtained through matching based on the input parameters, and can adapt to different database environments; the invention also analyzes the target verification rule, executes the SQL to obtain a data verification result, and realizes data verification based on the SQL, thereby improving the data verification effect and enhancing the timeliness of the data verification.
Description
Technical Field
The present invention relates to the field of data processing technologies, and in particular, to a data verification method, a data verification device, a terminal device, and a storage medium.
Background
In recent years, with the increasing national supervision of banks, various authorities require more and more types of data submitted by banks, and the requirements on data quality are higher and higher. How to find the data quality problem in advance before the data report is sent to the supervision department becomes the first problem to be solved in the bank report type products. The data verification method of the supervision and reporting products on the market at present is mainly realized based on a rule engine, the data verification method is based on the calculation of a memory rule, a large amount of calculation resource support is needed, and middleware such as Redis and the like is needed to be relied on (a certain outsourcing type verification engine needs a Redis cluster of 170G memory as calculation resource thereof); the middleware such as Redis is used as the computing resource of the verification engine, has the advantages of high performance, memory storage, various data structures, persistence support, high availability, simplicity, easiness in use and the like, can meet the requirement of large-scale data verification, and provides quick response, stability and reliability.
However, the report data of the bank has a longer processing link before the report, and the conventional data checking method only can check the checksum quality of the outlet at the end of the processing link, so that the report with high timeliness cannot meet the requirement of early processing of the early discovery problem, thereby resulting in low timeliness of data checking.
Disclosure of Invention
The invention mainly aims to provide a data verification method, a data verification device, terminal equipment and a storage medium, and aims to enhance timeliness of data verification by improving data verification effect.
In order to achieve the above object, the present invention provides a data verification method, which is applied to a database environment, and includes the following steps:
responding to the verification execution command, and acquiring input parameters;
matching a predefined verification rule based on the input parameters to obtain a target verification rule;
and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result.
Optionally, the step of screening the verification rule based on the input parameter to obtain the target verification rule includes:
Defining the verification rule specifically comprises the following steps:
acquiring rule configuration template information;
and defining rules based on the rule configuration template information to obtain the check rule.
Optionally, the step of obtaining the verification rule further includes:
and synchronizing the check rule into a pre-established check rule table to obtain a check rule definition table.
Optionally, the step of matching the predefined verification rule based on the input parameter to obtain the target verification rule includes:
carrying out data analysis based on the input parameters to obtain data table information;
and matching the predefined check rule based on the data table information to obtain the target check rule.
Optionally, the target verification rule at least comprises one or more of a single-table verification rule, an association verification rule, a multi-table verification rule and an intra-table data verification rule.
Optionally, the step of performing rule analysis based on the target verification rule to generate and execute the structured query statement SQL to obtain a data verification result includes:
traversing the target verification rule, and selecting a rule expression to be analyzed;
Generating and executing SQL based on the rule expression to obtain and update an error number statistical result and an error detail result;
judging whether the target check rule has an unresolved rule or not;
if yes, executing the steps of: traversing the target verification rule, and selecting a rule expression to be analyzed;
and if not, obtaining the data verification result based on the error number statistical result and the error detail result.
Optionally, the step of generating and executing SQL based on the rule expression to obtain and update the error count statistics and the error detail results includes:
analyzing the rule expression to generate a first SQL type and a second SQL type;
executing the first type SQL to obtain and update the error count statistical result;
and executing the second class SQL based on the error statistics result to obtain and update the error detail result.
In addition, to achieve the above object, the present invention also provides a data verification apparatus, including:
the data acquisition module is used for responding to the verification execution command and acquiring input parameters;
the rule configuration module is used for matching a predefined check rule based on the input parameters to obtain a target check rule;
And the rule analysis module is used for carrying out rule analysis based on the target verification rule, generating and executing a structured query statement SQL, and obtaining a data verification result.
Optionally, the rule configuration module is further configured to:
defining the verification rule specifically comprises the following steps:
acquiring rule configuration template information;
and defining rules based on the rule configuration template information to obtain the check rule.
Optionally, the rule configuration module is further configured to:
and synchronizing the check rule into a pre-established check rule table to obtain a check rule definition table.
Optionally, the rule configuration module is further configured to:
carrying out data analysis based on the input parameters to obtain data table information;
and matching the predefined check rule based on the data table information to obtain the target check rule.
The target verification rule at least comprises one or more of a single-table verification rule, an association verification rule, a multi-table verification rule and an intra-table data verification rule.
Optionally, the rule parsing module is further configured to:
traversing the target verification rule, and selecting a rule expression to be analyzed;
generating and executing SQL based on the rule expression to obtain and update an error number statistical result and an error detail result;
Judging whether the target check rule has an unresolved rule or not;
if yes, executing the steps of: traversing the target verification rule, and selecting a rule expression to be analyzed;
and if not, obtaining the data verification result based on the error number statistical result and the error detail result.
Optionally, the rule parsing module is further configured to:
analyzing the rule expression to generate a first SQL type and a second SQL type;
executing the first type SQL to obtain and update the error count statistical result;
and executing the second class SQL based on the error statistics result to obtain and update the error detail result.
In addition, to achieve the above object, the present invention also provides a terminal device including a memory, a processor, and a data verification program stored on the memory and executable on the processor, the data verification program implementing the data verification method as described above when executed by the processor.
In addition, in order to achieve the above object, the present invention also provides a computer-readable storage medium having stored thereon a data verification program which, when executed by a processor, implements the data verification method as described above.
The embodiment of the invention provides a data verification method, a data verification device, terminal equipment and a storage medium, wherein input parameters are obtained by responding to a verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. According to the invention, the target verification rule is obtained by matching from the predefined verification rules based on the input parameters, then the target verification rule is analyzed, the SQL is executed to obtain the data verification result, and the SQL-based data verification is realized, so that the data verification effect is improved, and the timeliness of the data verification is enhanced.
Drawings
FIG. 1 is a schematic diagram of functional modules of a terminal device to which a data verification device of the present invention belongs;
FIG. 2 is a flowchart illustrating a data verification method according to a first exemplary embodiment of the present invention;
FIG. 3 is a diagram of a system frame for data verification in a Gauss database environment according to a first exemplary embodiment of the data verification method of the present invention;
FIG. 4 is a flowchart of a second exemplary embodiment of a data verification method according to the present invention;
FIG. 5 is a flowchart illustrating a data verification method according to a third exemplary embodiment of the present invention;
FIG. 6 is a flowchart of a fourth exemplary embodiment of a data verification method according to the present invention;
FIG. 7 is a flowchart illustrating a workflow for screening target verification rules according to a fourth exemplary embodiment of the data verification method of the present invention;
FIG. 8 is a flowchart of a data verification method according to a fifth exemplary embodiment of the present invention;
FIG. 9 is a schematic diagram of a workflow for implementing one-place configuration multi-place execution in a fifth embodiment of the data verification method of the present invention;
fig. 10 is a diagram illustrating an example of SQL generated based on a verification rule in a fifth exemplary embodiment of the data verification method of the present invention.
The achievement of the objects, functional features and advantages of the present invention will be further described with reference to the accompanying drawings, in conjunction with the embodiments.
Detailed Description
It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention.
The main solutions of the embodiments of the present invention are: responding to the verification execution command, and acquiring input parameters; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result.
The embodiment of the application considers that the current industry is realized based on a rule engine, the data checking method is based on the calculation of the rule of the memory, needs a great deal of calculation resource support, and needs to rely on middleware such as Redis (certain outsourcing type checking engine needs a Redis cluster of 170G memory as the calculation resource thereof); the traditional data checking method can only carry out the check sum quality check of the export at the tail end of the processing link, and the report with high timeliness can not meet the requirement of advanced processing of the problem of finding in advance, thereby leading to low timeliness of data checking.
Based on the above, the embodiment of the application provides a solution, and the target verification rule is obtained by matching from the predefined verification rules based on the input parameters, then the target verification rule is analyzed, and the SQL is executed to obtain the data verification result, so that the SQL-based data verification is realized, the data verification effect is improved, and the timeliness of the data verification is enhanced.
Specifically, referring to fig. 1, fig. 1 is a schematic diagram of functional modules of a terminal device to which a data verification apparatus of the present application belongs. The data verification device may be a device independent of the terminal device and capable of performing data verification and recommendation, and may be carried on the terminal device in a form of hardware or software. The terminal device can be an intelligent mobile terminal with a data processing function, a fixed terminal device or a server with a data processing function, and the like, and the data checking device can be also borne in a data checking system.
In this embodiment, the terminal device to which the data verification apparatus belongs at least includes an output module 110, a processor 120, a memory 130, and a communication module 140.
The memory 130 stores an operating system and a data verification program; the output module 110 may be a display screen or the like. The communication module 140 may include a WIFI module, a mobile communication module, a bluetooth module, and the like, and communicates with an external device or a server through the communication module 140.
Wherein the data verification program in the memory 130 when executed by the processor performs the steps of:
responding to the verification execution command, and acquiring input parameters;
matching a predefined verification rule based on the input parameters to obtain a target verification rule;
and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result.
Further, the data verification program in the memory 130 when executed by the processor also implements the steps of:
defining the verification rule specifically comprises the following steps:
acquiring rule configuration template information;
and defining rules based on the rule configuration template information to obtain the check rule.
Further, the data verification program in the memory 130 when executed by the processor also implements the steps of:
And synchronizing the check rule into a pre-established check rule table to obtain a check rule definition table.
Further, the data verification program in the memory 130 when executed by the processor also implements the steps of:
carrying out data analysis based on the input parameters to obtain data table information;
and matching the predefined check rule based on the data table information to obtain the target check rule.
The target verification rule at least comprises one or more of a single-table verification rule, an association verification rule, a multi-table verification rule and an intra-table data verification rule.
Further, the data verification program in the memory 130 when executed by the processor also implements the steps of:
traversing the target verification rule, and selecting a rule expression to be analyzed;
generating and executing SQL based on the rule expression to obtain and update an error number statistical result and an error detail result;
judging whether the target check rule has an unresolved rule or not;
if yes, executing the steps of: traversing the target verification rule, and selecting a rule expression to be analyzed;
and if not, obtaining the data verification result based on the error number statistical result and the error detail result.
Further, the data verification program in the memory 130 when executed by the processor also implements the steps of:
analyzing the rule expression to generate a first SQL type and a second SQL type;
executing the first type SQL to obtain and update the error count statistical result;
and executing the second class SQL based on the error statistics result to obtain and update the error detail result.
According to the embodiment, through the scheme, the input parameters are obtained by responding to the verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. The target verification rule is obtained through matching based on the input parameters, and can adapt to different database environments; the application also analyzes the target verification rule, executes the SQL to obtain a data verification result, and realizes data verification based on the SQL, thereby improving the data verification effect and enhancing the timeliness of the data verification.
The method embodiment of the application is proposed based on the above-mentioned terminal equipment architecture but not limited to the above-mentioned architecture.
Referring to fig. 2, fig. 2 is a flowchart illustrating a data verification method according to a first exemplary embodiment of the present application. The data verification method comprises the following steps:
step S10, responding to a verification execution command, and acquiring input parameters;
specifically, the embodiment can be applied to a database environment, the calculation process of the data verification method of the embodiment depends on database resources, does not depend on other calculation resources, does not need to deploy additional middleware and other calculation components, and saves cost (taking a certain outsourcing verification engine as an example, 170G Redis resources take approximately 10 ten thousand yuan each year). The implementation body of the embodiment takes a data verification execution engine as an example, and an applied database environment takes a Gauss database as an example, wherein the data verification execution engine comprises components such as a Gsql (structured query language) client, a check engine script (also called as a check engine script), a set of configuration tables, a result table and the like. Among them, gauss is a distributed relational database management system that provides a high performance, high reliability and scalability database solution. The input parameters in this embodiment may be a data date parameter, a data table of a target data verification object, and the data verification execution engine may dynamically select an object for executing verification and verification content according to the input parameters in the data verification execution command.
Step S20, matching a predefined verification rule based on the input parameters to obtain a target verification rule;
specifically, in this embodiment, the pre-defined verification rule is matched based on the header name in the input parameter, so as to obtain the target verification rule. The verification rule needs to be predefined based on a rule configuration template, and in addition, the verification rule can be matched according to different data types. For example, for data of a numeric type, matching may be performed using rules such as numeric ranges, regular expressions, and the like; for string type data, rules such as length, character set, etc. may be used for matching. The matching process may be performed using conditional judgment, regular expression, comparison operation, and the like. According to the matching result, which verification rules are applicable to the current input parameters can be determined, and corresponding data verification is further carried out. In this embodiment, the target verification rule may also be one or more of a single table verification rule, an association verification rule, a multi-table verification rule, and an intra-table data verification rule.
And step S30, carrying out rule analysis based on the target verification rule, generating and executing a structured query statement SQL, and obtaining a data verification result.
Specifically, the embodiment runs the analysis script corresponding to the target verification rule, and dynamically configures parameters according to the target verification rule to generate a rule SQL. Among them, rule SQL is a method for implementing and executing a specific business rule using an SQL statement. It may be used to verify the validity of data, execute computational logic, apply entitlement control, and the like. Regular SQL is commonly used in databases to ensure data integrity, accuracy, and consistency. By writing and executing the rule SQL, the business rule can be automatically executed, so that manual errors are avoided and the data quality is improved.
More specifically, referring to fig. 3, fig. 3 is a system frame diagram of data verification in a Gauss database environment according to the data verification method of the present application.
Firstly, generating rule definition through a rule configuration template, and generating a check rule definition table;
secondly, synchronizing the check rule definition table to a Gauss library;
thirdly, receiving a data verification execution command which is initiated manually or sent by other system scheduling services through the Gsql client, responding to the execution command to analyze script generation and generating dynamic rule SQL according to rule configuration;
then, sending a rule SQL to a Gauss database, and generating a check result interface table and an error detail interface table for executing the rule SQL, wherein the check result table stores a data check error number statistical result, and the error detail interface table stores specific information of errors checked in data check;
And finally, synchronizing the checking result interface table and the error detail interface table to a statistics report platform.
According to the embodiment, through the scheme, the input parameters are obtained by responding to the verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. The design of the application realizes a configurable verification engine based on SQL, the engine can be deployed in different links on a processing link for reporting data, the forward movement of the verification link is realized, and the problem is found out at the source to solve the problem. In addition, the data verification engine adapts multiple databases, can be executed in different database environments on the processing link, and is independent of other middleware and software deployment environments. The application realizes data verification based on SQL, thereby improving the data verification effect and enhancing the timeliness of data verification.
Referring to fig. 4, fig. 4 is a flowchart illustrating a second exemplary embodiment of a data verification method according to the present application.
Based on the first embodiment, a second embodiment of the present application is proposed, which differs from the first embodiment in that:
In this embodiment, step S20, performing a verification rule screening based on the input parameter, further includes:
step S15, defining the check rule;
specifically, the verification rule needs to be predefined based on a rule configuration template, and in addition, the verification rule can be matched according to different data types. For example, for data of a numeric type, matching may be performed using rules such as numeric ranges, regular expressions, and the like; for string type data, rules such as length, character set, etc. may be used for matching. The matching process may be performed using conditional judgment, regular expression, comparison operation, and the like.
Further, in step S15, the embodiment further refines the definition of the verification rule.
In this embodiment, step S15, defining the verification rule for refinement includes:
step S151, obtaining rule configuration template information;
specifically, the rule configuration template information is manually preconfigured, and the rule configuration is completed based on various databases. This embodiment takes Gauss database as an example. The embodiment firstly determines rule types, wherein the rule types comprise data verification rules, business rules or other types of rules; the rule configuration template information is stored in a database or a configuration file, and can be provided through an API (Application Programming Interface ) or other interfaces, and the rule configuration template information needs to be acquired by using a corresponding API document or interface description when the rule configuration template information is called.
And step S152, rule definition is carried out based on the rule configuration template information, and the verification rule is obtained.
Specifically, the implementation body of the present embodiment takes a check execution engine as an example, and each structural function in the check execution engine can refer to the above-mentioned first embodiment. The present embodiment first determines data verification requirements, such as which aspects of verification need to be performed on the input data, which conditions need to be checked for satisfaction, and so on, based on the rule template configuration information. In this embodiment, taking data verification for each link on the processing link as an example, the verification rule needs to determine that corresponding rule definition is performed at three points of source processing, data integration and reporting. The specific rule definition method may be: and writing definition codes or configuration of the verification rules according to specific business requirements in the actual implementation process and fields in the configuration template. The particular implementation depends on the programming language, framework or tool used.
According to the embodiment, through the scheme, the input parameters are obtained by responding to the verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. The verification rule of the invention can be adapted to various different databases, and a proper target verification rule can be selected when the databases are matched, so that the embodiment of the invention can be executed in different database environments on a processing link, does not depend on other middleware and software deployment environments, can meet the requirement of early processing of early discovery problems for reporting with high timeliness, and enhances the timeliness of data verification.
Referring to fig. 5, fig. 5 is a flowchart of a third exemplary embodiment of a data verification method according to the present application.
Based on the second embodiment, a third embodiment of the present application is proposed, which differs from the second embodiment in that:
in this embodiment, step S152, performing rule definition based on the rule configuration template information, includes:
and step 153, synchronizing the check rule into a pre-established check rule table to obtain a check rule definition table.
Specifically, the present embodiment first creates a check rule table, which may be created in a database or in another storage system. The check rule table should have appropriate fields and data types to meet data check requirements; secondly, the embodiment defines the table structure of the check rule table, wherein the table structure comprises the steps of determining the field of each check rule record and the corresponding data type; thirdly, the embodiment converts the check rules into data, specifically, each rule can be converted into a corresponding data record based on the actual meaning of the check rules, and the field value of each record is filled to represent a specific check rule; then, the embodiment synchronizes the check rule to the check rule table, and writes the check rule into a corresponding field in the check rule table through a corresponding operation (such as a built-in insert, an upload update, etc.) of the main database, thereby obtaining a check rule definition table.
According to the embodiment, through the scheme, the input parameters are obtained by responding to the verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. The verification rule of the application can be adapted to a plurality of different databases, and after the target verification rule is obtained based on the input parameter matching, the target verification rule can be directly configured into the corresponding database, so that after one rule configuration is realized, SQL dynamic generation and data verification effect can be carried out according to different input parameters. Based on the above, the embodiment of the application can meet the requirement of early detection problem early processing for reporting with high timeliness, and the timeliness of data verification is enhanced.
Referring to fig. 6, fig. 6 is a flowchart illustrating a fourth exemplary embodiment of a data verification method according to the present application.
Based on the first embodiment, a fourth embodiment of the present application is proposed, which differs from the first embodiment in that: in the embodiment, in step S20, the predefined verification rule is matched based on the input parameter, so as to obtain the target verification rule for refinement.
In this embodiment, step 20 of matching the predefined verification rule based on the input parameter may include:
step S201, data analysis is carried out based on the input parameters, and data table information is obtained;
specifically, in this embodiment, the input parameters may be data date parameters, a data table of a target data verification object, etc., and first, the embodiment determines the structure and format of the input parameters to select a corresponding analysis script for data analysis, and in this embodiment, data table information is obtained through data analysis, where the data table information is used to query a related verification rule.
Step S202, matching the predefined verification rule based on the data table information to obtain the target verification rule.
Specifically, the present embodiment matches the data table information with a predefined check rule. And comparing the information with the definition of the check rule according to the information such as the fields, the data types, the constraint conditions and the like of the data table. Then, according to the matched result, the embodiment screens out the target verification rule conforming to the data table information. These target check rules may be specific check rules corresponding to the data table fields, or may be general check rules applicable to the entire table or under specific conditions. The target verification rule comprises one or more of a single-table verification rule, an association verification rule, a multi-table verification rule and an intra-table data verification rule.
More specifically, referring to fig. 7, fig. 7 is a schematic workflow diagram of screening target verification rules according to the data verification method of the present application; as shown in the figure, in this embodiment, the object for performing verification and the verification content may be selected according to the program parameters i_tabs and i_stg in the input parameters when the data verification engine is operated, so as to implement screening of the target verification rule. Screening the target checking rule can specifically execute inter-table association checking or single-table checking rule through parameter control; executing the verification of a certain table or the verification rules of a plurality of tables through parameter control; checking partial data in a certain table through parameter control; the program parameters i_tabs and i_stg are described as follows:
i_tabs: screening tables to be checked according to the present parameters, the tables being separated by #, e.g.
0102_YGB#0101_JGXXB, PRM_TBL in the rule table is 0102_YGB and
a verification rule of 0101_JGXXB; wherein 0102_ygb is the check rule table 1 of the present embodiment, 0101_jgxxb is the check rule table 2 of the present embodiment, and prm_tbl is the main parameter of the check rule table;
I_STG: and screening the single-table check or the association check according to the parameter, wherein a value of 0 represents the single-table check, and a value of 1 represents the association check.
As shown in fig. 7, in this embodiment, taking a common control table association as an example, after the data processing of the table a and the table B is completed, the table a and the table B may be checked by performing the input parameter configuration control; or the association check between table a, table B, table C may be performed after table a, table B, table C are completed.
According to the embodiment, through the scheme, the input parameters are obtained by responding to the verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. The application refines the target verification rule, and particularly selects the object for verification and the verification content when the data verification engine is operated according to the program parameters I_TABLES and I_STG in the input parameters, thereby realizing the screening of the target verification rule. The target verification rule can adapt to different database environments, so that cost waste of repeated configuration of the verification rule is saved, and timeliness of data verification is enhanced.
Referring to fig. 8, fig. 8 is a flowchart of a fifth exemplary embodiment of a data verification method according to the present application.
Based on the first embodiment, a fifth embodiment of the present application is proposed, which differs from the first embodiment in that: in this embodiment, in step S30, rule analysis is performed based on the target verification rule, and a structured query statement SQL is generated and executed, so as to obtain a data verification result for refinement.
In this embodiment, step S30 performs rule analysis based on the target verification rule, generates and executes a structured query statement SQL, and obtains a data verification result including:
step S301, traversing the target verification rule, and selecting a rule expression to be analyzed;
specifically, in this embodiment, the target verification rule needs to be traversed first, where the traversing process may be traversing according to a rule priority, screening and traversing according to a preset condition, and sorting the verification rules according to a trigger frequency of the preset verification rule, so that the target verification rule is traversed based on the trigger frequency. Wherein the rule expression is a formalized language, a specific syntax structure of a programming language, or a combination of a set of conditions and operations. The rule expression is used for defining the condition of the rule and the operation executed after the rule is triggered. May generally include: conditions, actions, and logic rules. More specifically, the condition section in the rule expression describes preconditions for rule triggering. The conditions may be constructed using comparison operators, logical operators, functions, etc., to determine whether certain specific conditions are met. The action part in the rule expression defines the operation to be performed after the rule triggers. These operations may be function calls, data stores, output information, etc. for implementing specific business logic; a rule expression may also contain logical relationships between rules, such as AND (AND), OR (OR), NOT (NOT), etc.; these logical operators may be used to combine multiple conditions to more precisely describe the triggering conditions of a rule.
Step S302, generating and executing SQL based on the rule expression to obtain and update an error number statistical result and an error detail result;
specifically, the present embodiment analyzes the target verification rule, obtains the rule expression, and analyzes the rule expression by comparing with the rule definition table in the above embodiment to generate the SQL of the corresponding database. In this embodiment, a Gauss database environment is taken as an example, so the SQL provided in this embodiment is applicable to Gauss databases. In this embodiment, the data verification result is obtained by executing the SQL for counting the number of errors and the SQL for extracting the details of errors, and obtaining and updating the result of counting the number of errors and the result of details of errors.
More specifically, referring to fig. 9, fig. 9 is a schematic diagram of a workflow in which the data verification method of the present application is implemented to be executed at a plurality of places; as shown in fig. 9, in the data processing flow of the monitoring report, there is a longer processing link between the data from the service source system to the final report, and more than one team is involved, so that in order to make the responsible units of each link on the processing link responsible for the quality of the data produced by each link, the embodiment adapts multiple data sources, and each processing link can verify the data produced by each link after the processing data is completed through simple configuration, and a set of rules is configured to operate simultaneously in each link of the processing link, thereby achieving the aims of forward verification and source management. Meanwhile, the cost waste of repeated development of each team is saved.
Further, referring to fig. 10, fig. 10 is a diagram illustrating an SQL generated by the data verification method according to the present application based on verification rules; as shown in fig. 10, the embodiment can analyze the rule expression according to the input parameters during operation, so that the SQL rule adapted to the corresponding database is dynamically generated to realize one-place configuration, multiple-place execution, and the verification links are deployed in different links on the data processing link. Wherein, the check rule shown in fig. 10 is "employee table name cannot be empty"; v_prefix and v_suffix in fig. 10 are PREFIX parameters of the employee table, which may be any custom string that identifies the name of the database object and is named according to a particular specification. It can be used to distinguish between different types of database objects, such as tables, views, stored procedures, etc.; the method comprises the steps that { V_PREFIX } needs to be replaced by a mode name of a specific database when being executed, and { V_SUFFIX } is used for meeting the personalized definition requirement of a processing link on a table name and can be set in the operation parameters of a data check engine; XM in FIG. 10 is a custom variable that stores employee names; dw_nsh_dt= $ { tx_date } in fig. 10 represents the setting of the data period for running the data check engine, and is one of the trigger conditions that the employee table name cannot be empty.
Step S303, judging whether the unresolved rule exists in the target verification rule;
specifically, the embodiment determines whether the target verification rule has a rule that is not parsed, so as to ensure that all target verification rules that should be executed are completely executed.
Step S304, if yes, executing the steps of: traversing the target verification rule, and selecting a rule expression to be analyzed;
specifically, if the target verification rule has a rule that is not analyzed, the step is skipped to step S301, and the rule is continuously traversed and the rule expression to be analyzed is selected; then, step S302 and step S303 are executed until all the rule expressions corresponding to the target verification rule are parsed, and the loop is skipped.
Step S305, if not, obtaining the data verification result based on the error count result and the error detail result.
Specifically, in this embodiment, the data verification result is obtained by integrating the updated error count statistics result and the updated error detail result, and synchronizing the error count statistics result and the error detail result to a verification result interface table (storing the error count statistics result) and an error detail interface table (storing the error detail result) in a statistics reporting platform.
Further, in step S302, SQL is generated and executed based on the rule expression, and the error count statistics and the error detail results are obtained and updated for refinement.
In this embodiment, step S302, generating and executing SQL based on the rule expression, where obtaining the error count result and the error detail result includes:
step S3021, parsing the rule expression to generate a first type SQL and a second type SQL;
specifically, the first type of SQL is error count SQL for counting the number of errors found in the data verification process, and the second type of SQL is error detail extraction SQL for extracting the specific conditions of errors found in the data verification process.
Step S3022, executing the first type SQL to obtain and update the error count result;
specifically, in this embodiment, by executing the first type of SQL script, an SQL execution result is obtained, so as to obtain the error count result, and the error count result is stored in a temporary storage area.
Step S3023, executing the second type SQL based on the error statistics result to obtain and update the error details result.
In particular, this embodiment first needs to ensure that a successful connection can be made to a database containing error detail data. The correct database hostname, port number, username and password information is provided to connect to the database server using appropriate database connection tools. And then copying the second type SQL into an SQL editor, and modifying screening conditions in the SQL statement according to the requirement to obtain error details of a specific date range, error types or other related conditions to obtain the error detail result.
According to the embodiment, through the scheme, the input parameters are obtained by responding to the verification execution command; matching a predefined verification rule based on the input parameters to obtain a target verification rule; and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result. According to the embodiment, multiple data sources are adapted, each processing link can verify the data produced by each processing link after the processing data are finished through simple configuration, and one set of regular configuration is operated at the same time in each link of the processing link, so that the aims of forward verification and source management are achieved. Meanwhile, the cost waste of repeated development of each team is saved. The method of the embodiment can be applied to different links on the data processing link, achieves forward movement of the verification link, and finds out the problem at the source to quickly solve the problem, thereby improving the data verification effect and enhancing the timeliness of the data verification.
It should be noted that, the foregoing embodiments may be implemented in a reasonable combination according to actual situations, which is not described in detail in this embodiment.
In addition, an embodiment of the present application further provides a data verification device, where the data verification device includes:
The data acquisition module is used for responding to the verification execution command and acquiring input parameters;
the rule configuration module is used for matching a predefined check rule based on the input parameters to obtain a target check rule;
and the rule analysis module is used for carrying out rule analysis based on the target verification rule, generating and executing a structured query statement SQL, and obtaining a data verification result.
The principle and implementation process of data verification are implemented in this embodiment, please refer to the above embodiments, and are not repeated here.
In addition, the embodiment of the application also provides a terminal device, which comprises a memory, a processor and a data verification program stored on the memory and capable of running on the processor, wherein the data verification program realizes the steps of the data verification method when being executed by the processor.
Because the data verification program is executed by the processor and adopts all the technical schemes of all the embodiments, the data verification program at least has all the beneficial effects brought by all the technical schemes of all the embodiments and is not described in detail herein.
In addition, the embodiment of the application also provides a computer readable storage medium, wherein the data verification readable storage medium stores a data verification program, and the data verification program realizes the steps of the data verification method when being executed by a processor.
Because the data verification program is executed by the processor and adopts all the technical schemes of all the embodiments, the data verification program at least has all the beneficial effects brought by all the technical schemes of all the embodiments and is not described in detail herein.
It should be noted that, in this document, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or system that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or system. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or system that comprises the element.
The above ordering of embodiments of the invention is merely for illustration, and does not represent the advantages or disadvantages of the embodiments.
From the description of the above embodiments, it will be apparent to those skilled in the art that the above embodiment methods may be implemented by means of software plus necessary general hardware platforms. Based on such understanding, the technical solution of the present invention may be embodied essentially or in a part contributing to the prior art in the form of a software product stored in a storage medium (e.g. ROM/RAM, magnetic disk, optical disk) as described above, comprising instructions for causing a terminal device (which may be a mobile phone, a computer, a server, or a network device, etc.) to perform the method according to the embodiments of the present invention.
The foregoing description is only of the preferred embodiments of the present invention, and is not intended to limit the scope of the invention, but rather is intended to cover any equivalents of the structures or equivalent processes disclosed herein or in the alternative, which may be employed directly or indirectly in other related arts.
Claims (10)
1. A data verification method, wherein the method is applied to a database environment, the data verification method comprising the steps of:
responding to the verification execution command, and acquiring input parameters;
matching a predefined verification rule based on the input parameters to obtain a target verification rule;
and carrying out rule analysis based on the target verification rule, and generating and executing a structured query statement SQL to obtain a data verification result.
2. The data verification method according to claim 1, wherein the step of screening the verification rule based on the input parameter to obtain the target verification rule includes, before:
defining the verification rule specifically comprises the following steps:
acquiring rule configuration template information;
and defining rules based on the rule configuration template information to obtain the check rule.
3. The data verification method according to claim 2, wherein the step of obtaining the verification rule further comprises, after the rule definition based on the rule configuration template information:
and synchronizing the check rule into a pre-established check rule table to obtain a check rule definition table.
4. The data verification method as claimed in claim 1, wherein said step of matching a predefined verification rule based on said input parameters to obtain a target verification rule comprises:
carrying out data analysis based on the input parameters to obtain data table information;
and matching the predefined check rule based on the data table information to obtain the target check rule.
5. The data verification method of claim 4, wherein the target verification rules include at least one or more of a single table verification rule, an association verification rule, a multi-table verification rule, and an intra-table data verification rule.
6. The method of claim 1, wherein the step of generating and executing the structured query language SQL to obtain the data verification result comprises:
Traversing the target verification rule, and selecting a rule expression to be analyzed;
generating and executing SQL based on the rule expression to obtain and update an error number statistical result and an error detail result;
judging whether the target check rule has an unresolved rule or not;
if yes, executing the steps of: traversing the target verification rule, and selecting a rule expression to be analyzed;
and if not, obtaining the data verification result based on the error number statistical result and the error detail result.
7. The data verification method according to claim 6, wherein the step of generating and executing SQL based on the rule expression, obtaining and updating the error count statistics and the error detail results comprises:
analyzing the rule expression to generate a first SQL type and a second SQL type;
executing the first type SQL to obtain and update the error count statistical result;
and executing the second class SQL based on the error statistics result to obtain and update the error detail result.
8. A data verification device, characterized in that the data verification device comprises:
the data acquisition module is used for responding to the verification execution command and acquiring input parameters;
The rule configuration module is used for matching a predefined check rule based on the input parameters to obtain a target check rule;
and the rule analysis module is used for carrying out rule analysis based on the target verification rule, generating and executing a structured query statement SQL, and obtaining a data verification result.
9. A terminal device, characterized in that it comprises a memory, a processor and a data verification program stored on the memory and executable on the processor, which data verification program, when executed by the processor, implements the data verification method according to any one of claims 1-7.
10. A computer readable storage medium, characterized in that the computer readable storage medium has stored thereon a data verification program, which when executed by a processor, implements the data verification method according to any of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310959251.1A CN116860781A (en) | 2023-07-31 | 2023-07-31 | Data verification method, device, terminal equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310959251.1A CN116860781A (en) | 2023-07-31 | 2023-07-31 | Data verification method, device, terminal equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116860781A true CN116860781A (en) | 2023-10-10 |
Family
ID=88230607
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310959251.1A Pending CN116860781A (en) | 2023-07-31 | 2023-07-31 | Data verification method, device, terminal equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116860781A (en) |
-
2023
- 2023-07-31 CN CN202310959251.1A patent/CN116860781A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10678669B2 (en) | Field content based pattern generation for heterogeneous logs | |
| US11354219B2 (en) | Machine defect prediction based on a signature | |
| US9110945B2 (en) | Support for a parameterized query/view in complex event processing | |
| US11080305B2 (en) | Relational log entry instituting system | |
| CN110427188B (en) | Configuration method, device, equipment and storage medium of single-test assertion program | |
| US20210326248A1 (en) | Method, apparatus, device and storage medium for testing code | |
| CN109857630B (en) | Code detection method, system and equipment | |
| US10970343B2 (en) | Adapting database queries for data virtualization over combined database stores | |
| US11385898B2 (en) | Task orchestration method for data processing, orchestrator, device and readable storage medium | |
| CN111651451A (en) | Scene-driven single system micro-service splitting method | |
| US12010182B2 (en) | System and methods for application programming interface validation and testing | |
| CN115934097A (en) | Method, device, storage medium and electronic device for generating executable statement | |
| CN112650526B (en) | Method, device, electronic equipment and medium for detecting version consistency | |
| CN110018835B (en) | YANG model configuration data processing method and device, terminal device and storage medium | |
| CN116578585B (en) | Data query method, device, electronic equipment and storage medium | |
| CN106919630A (en) | A kind of risk indicator formula generation method and system based on SQL expression | |
| CN111984826A (en) | XML-based data automatic storage method, system, device and storage medium | |
| US20070033178A1 (en) | Quality of service feedback for technology-neutral data reporting | |
| CN116860781A (en) | Data verification method, device, terminal equipment and storage medium | |
| US11366642B1 (en) | Change migration: processes for ensuring successful deployment of design changes | |
| US10003492B2 (en) | Systems and methods for managing data related to network elements from multiple sources | |
| CN117472641B (en) | Data quality detection method and device, electronic equipment and storage medium | |
| CN111401825B (en) | Instantiation method and device | |
| US20190196945A1 (en) | Entity performance and usability analysis framework | |
| CN115391369A (en) | Operation method, device and equipment of data engine and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |