A method and device for detecting data quality

Abstract

The present invention relates to the field of quality detection and control of structured and semi-structured data, and a method for data quality detection has the following steps: S1, defining basic rules; S2, creating business rules; S3, creating detection tasks; S4, executing quality detection; S5, performing quality scoring and overall volatility analysis. Compared with the prior art, the present invention provides a script engine to allow users to write detection rules in a scripting manner, thereby reducing the difficulty of rule creation and being able to more flexibly meet the needs of various scenarios.

Description

Technical Field

[0001] The present invention relates to the field of quality detection and control of structured and semi-structured data, and specifically provides a method and device for data quality detection. Background Art

[0002] With the advent of the digital age, data quality management has become an important part of data governance. All data governance tasks are carried out around the goal of improving data quality. Continuous quality monitoring of the entire process of data governance, providing report analysis, and demonstrating governance effectiveness are the core functions that the data quality system needs to provide.

[0003] In the era of big data, facing the ever-increasing amount of massive data and people's increasing attention to the value of data, the requirements for data quality systems are getting higher and higher, and traditional data quality systems are difficult to meet the needs. Traditional data quality systems have the following problems: ① Often based on built-in detection rules to detect data, the rule expansion flexibility is insufficient, or although the rule expansion is supported, the expansion method is complex and difficult to use; ② Lack of ability to analyze the causes of data problems and provide improvement suggestions; ③ Often based on the column detection method, it is not conducive to the subsequent further processing of the detection results. How to meet different industry business scenarios, provide flexible quality inspection rule expansion capabilities, discover problems and provide suggestions are issues that need to be solved urgently. Summary of the invention

[0004] The present invention aims at the above-mentioned deficiencies of the prior art and provides a method for data quality detection with strong practicability.

[0005] A further technical task of the present invention is to provide a data quality detection device that is reasonably designed, safe and applicable.

[0006] The technical solution adopted by the present invention to solve its technical problem is:

[0007] A method for data quality detection comprises the following steps:

[0008] S1. Define basic rules;

[0009] S2. Create business rules;

[0010] S3, create a detection task;

[0011] S4. Perform quality inspection;

[0012] S5. Conduct quality scoring and overall volatility analysis.

[0013] Furthermore, in step S1, the detection script complies with the JavaScript language specification, the method signature of the retrieval script includes at least two parameters: tuple data and detection column, and the return value of the detection script is of Boolean type, returning true if the detection passes, and false if the detection fails.

[0014] Furthermore, the client performs a basic regular expression check on the detection script created by the user to determine whether the grammar of the script meets the basic requirements of the JavaScript. After the client check passes, the script is checked on the server side, including lexical, grammatical and semantic analysis of the script. If the check succeeds, it is persisted in the database. Otherwise, the error reason is given to the user, prompting the user to modify the script.

[0015] Furthermore, in step S2, the defined basic rules are bound to the data source to create business rules, and according to the basic rules selected by the user, the problem description and suggestion information of the basic rules are obtained by AJAX and are echoed to the page.

[0016] Furthermore, in step S3, the user creates a detection task according to business needs and transfers the detection results in a structured or unstructured manner;

[0017] During structured transfer, the target data table for transfer is set. The selected target data table for transfer contains a detection time column, a detection version column, an error flag column, and an error information description column. The JDBC method is used to actively connect to the target table for transfer set by the user, obtain the metadata information of the table, and check whether the table contains the above detection columns. If not, the user will be prompted to modify the target table for transfer.

[0018] When transferring in unstructured form, the test result data is transferred to a storage medium.

[0019] Further, in step S4, the script engine is called to execute the detection task, the target data is verified according to the configured verification rules, and a problem data list and a scoring report are generated. The specific steps are as follows:

[0020] S401, perform distributed scheduling on the detection task, create a distributed task lock by setting the redis key value, and perform distributed scheduling on the task;

[0021] S402, executing parameter parsing to parse the task parameters;

[0022] S403, parsing the detection rule list, obtaining the corresponding JavaScript script list, creating a Java ScriptEngineManager script execution environment, parsing the JavaScript script written by the user, and preloading the script;

[0023] S404, obtaining target detection data, parsing SQL according to the detection range set by the user, and generating an abstract syntax tree;

[0024] S405, generating data extraction SQL according to the syntax tree, pulling data from the target data source, and transferring the acquired data into the script execution environment;

[0025] S406. Record the verification results of all rules in the tuple. For the rules that fail the verification, the system obtains the problem description and suggested processing solution corresponding to the rule, and records the abnormal data tuple together with the obtained problem description and suggested processing solution.

[0026] S407, determine whether the problem data storage option is enabled for the task, and if enabled, save the problem data and the problem description and suggestions to the elasticSearch non-relational database;

[0027] Determine whether the problem data transfer option is enabled for the task. If enabled, the problem data will be transferred to the target data source at the same time. During the data dump process, continue to determine whether the correct data will also be transferred to the data source. If enabled, the verified correct data will also be transferred to the data source. Otherwise, the problem data, problem description and suggestions will be stored in the data source specified by the user.

[0028] Furthermore, in step S5, the user configures a scoring weight w for each rule K in the detection task. k , and calculate the quality score S according to the weight. The score is calculated as follows:

[0029] Where S is the score, n is the number of rules contained, and the score weight for rule k is w k The total amount of detection data is t k , the amount of problem data is e k ;

[0030] The above formula calculates the quality score of each dimension respectively. According to the detection field of the rule, the above formula is used to calculate the quality score of each field respectively.

[0031] Further, in step S5, the overall quality fluctuation value is calculated according to the verification factor E, comparison interval N, and warning deviation K configured by the user, and a warning prompt is given in the report for the detection result greater than the warning deviation K. The calculation method of the overall quality fluctuation is:

[0032] Where k is the fluctuation value, N is the score of the most recent n times, S i is the score of the i-th test, and E is a constant factor.

[0033] A device for data quality detection, comprising: at least one memory and at least one processor;

[0034] The at least one memory is used to store a machine-readable program;

[0035] The at least one processor is used to call the machine-readable program to execute a data quality detection method.

[0036] Compared with the prior art, the data quality detection method and device of the present invention have the following outstanding beneficial effects:

[0037] (1) The present invention provides a script engine to allow users to write detection rules in a scripting manner, thereby reducing the difficulty of rule creation and being able to more flexibly meet the needs of various scenarios.

[0038] (2) While ensuring the detection speed, a tuple-by-tuple detection method is adopted to reflect the problems existing in the data at the tuple level and give relevant suggestions, which is conducive to the repair and further processing of problematic data and improves data quality.

[0039] When testing large-scale data, testing by tuple can save only one test result for each tuple in the same test, saving network and disk resources. When testing large-scale data, testing by tuple can avoid the additional processing overhead caused by data merging after the verification is completed, compared with checking by attributes, and reduce the complexity of data processing.

[0040] (3) By providing data transfer capabilities, users can meet their needs for further processing of verification results and improve the value of data. By providing quality scoring and fluctuation analysis functions, users can grasp the overall quality situation and understand quality trends. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0042] Attached Figure 1 It is a flowchart of a method for data quality detection;

[0043] Attached Figure 2 It is a schematic diagram of the verification process of the detection script in a method of data quality detection;

[0044] Attached Figure 3It is a flowchart of calling a script engine to execute a detection task in a method of data quality detection;

[0045] Attached Figure 4 It is a schematic diagram of the structure of a syntax tree in a method for data quality detection. DETAILED DESCRIPTION

[0046] In order to enable those skilled in the art to better understand the solution of the present invention, the present invention is further described in detail below in conjunction with specific implementation methods. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0047] A best embodiment is given below:

[0048] like Figure 1 As shown, a method for data quality detection in this embodiment has the following steps:

[0049] S1. Define basic rules:

[0050] like Figure 2 As shown, the system provides the function of creating basic script detection rules, including rule name, detection script, problem description and suggestions, quality dimension, supported data source and other items. The system supports detection scripts that comply with the JavaScript language specification. The method signature of the detection script should contain at least two parameters: tuple data and detection column. Users can also add other parameters as needed. The return value of the script should be of Boolean type, returning true if the detection is passed and false if the detection is not passed.

[0051] The system performs basic regular expression verification on the detection script created by the user on the client side to determine whether the grammar of the script meets the basic requirements of JavaScript. After the client-side verification is passed, the system performs a more detailed syntax check on the script on the server side, including lexical, grammatical, and semantic analysis of the script. If the verification is successful, it is persisted in the database. Otherwise, the user is given the reason for the error and is prompted to modify the script.

[0052] S2. Create business rules:

[0053] The user selects the basic rules to be used and the data source to be tested, binds the basic rules to the data source, and creates business rules. Based on the basic rules selected by the user, the system automatically obtains the problem description and suggestion information of the basic rules using AJAX, and displays them on the page; the system allows the user to further refine the problem description and suggestion information based on specific business scenarios.

[0054] S3. Create a detection task:

[0055] The system allows users to create detection tasks according to business needs. Users need to set task attributes such as detection target, detection scope, and data transfer. Detection targets include: data source, data table, and business primary key to be detected; detection scope includes: full detection, incremental detection, and custom condition range detection; data transfer includes: whether to transfer, the target data source for transfer, the target data table for transfer, only transfer the detection column or transfer all columns, whether to transfer the correct data, etc.

[0056] Users can transfer the test results in structured or unstructured form. The system allows structured transfer, and the transfer target data table needs to be set. The selected transfer target data table needs to contain the following attributes: detection time column, detection version column, error flag column, and error information description column; the system uses JDBC to actively connect to the transfer target table set by the user, obtain the metadata information of the table, and check whether the above detection columns exist in the detection table. If not, the user will be prompted to modify the transfer target table; the system supports transferring the test result data to multiple relational databases such as Hive, Greenplum, mysql, oracle, SQLserver, DaMeng, KingBase, HighGo, Oscar, etc.

[0057] The system allows unstructured transfer and supports transferring test result data to various storage media such as ElasticSearch and HDFS. The system allows users to call the interface to quickly create a data transfer table. The interface should contain the following parameters: target data source ID, target data table name, target data table Chinese name, source data source ID, source data table.

[0058] S4. Perform quality inspection:

[0059] like Figure 3 As shown, the system calls the script engine to perform the detection task, verifies the target data according to the configured verification rules, and generates a list of problem data and a scoring report. The specific steps are as follows:

[0060] S401: Distributed scheduling is performed on the detection task. The system creates a distributed task lock by setting the redis key value and performs distributed scheduling on the task. The key of the distributed lock is composed of the following two representations:

[0061] idlf_quality_(32-bit UUID string, indicating the tenant space ID)_(32-bit UUID string, indicating the task ID).

[0062] idlf_quality_taskstatus_(32-bit UUID string, indicating the task instance ID).

[0063] S402, executing parameter parsing, parsing the task parameters, and obtaining information such as the detection scope, detection target, verification rules, and transfer configuration of the task.

[0064] S403, parsing the detection rule list, obtaining the corresponding JavaScript script list, creating a Java ScriptEngineManager script execution environment, parsing the JavaScript script written by the user, and preloading the script.

[0065] S404, such as Figure 4 As shown, the target detection data is obtained, and the system parses the SQL according to the detection range set by the user (full, incremental, custom condition range) to generate an abstract syntax tree. The system hangs the columns to be detected and the primary key configured by the user under the FIELDS node of the syntax tree, hangs the table to be detected under the TABLES node, and hangs the detection conditions under the CONDITIONS node; when the detection range is full detection, there is no limiting condition under the CONDITIONS node; when the detection range is incremental, the system will generate data range limiting conditions (AND (>, partition column, start time), (<, partition column, end time)), and hang the limiting conditions under the CONDITIONS node of the syntax tree. The limiting conditions satisfy the ternary (OP, arg1, arg2), where OP is an operator, arg1 and arg2 are the first and second operands respectively, where OP corresponds to the root node of the syntax tree, and arg1 and arg2 correspond to the left and right child nodes of the syntax tree; when the detection range is a custom condition range, the system parses the limiting conditions entered by the user into the corresponding sub-syntax tree and hangs them under the CONDITIONS node of the syntax tree.

[0066] S405. The system generates data extraction SQL based on the syntax tree, pulls data from the target data source, and passes the acquired data to the script execution environment. The system passes parameters to the script execution environment, including: the data tuple to be detected, the attribute name to be detected, and other user-defined parameters. The script engine passes the parameters to the user-defined detection script and executes the script. After the script is executed, it returns a boolean type detection result, which returns true if the detection passes and false if it fails.

[0067] S406. The system records the verification results of all rules for a certain tuple. For the rules that fail the verification, the system obtains the problem description and suggested processing solution corresponding to the rule, and records the abnormal data tuple together with the obtained problem description and suggested processing solution.

[0068] S407, the system determines whether the problem data storage option is enabled for the task. If enabled, the system saves the problem data and its description and suggestions to the elasticSearch non-relational database. The system determines whether the problem data transfer option is enabled for the task. If enabled, the system will transfer the problem data to the target data source at the same time.

[0069] During the data dump process, the system will continue to determine whether to also transfer the correct data to the data source. If it is turned on, the system will also transfer the verified correct data to the data source. Otherwise, the system will only store the problem data and its problem description and suggestions to the data source specified by the user.

[0070] S5. Perform quality scoring and overall volatility analysis:

[0071] The system allows users to configure the scoring weight w for each rule k in the detection task k , and calculate the quality score S based on the weight. The score is calculated as follows:

[0072] Where S is the score, n is the number of rules contained, and the score weight for rule k is w k The total amount of detection data is t k , the amount of problem data is e k .

[0073] The system uses the above formula to calculate the quality score of each dimension according to the dimensions to which the rule belongs, including: completeness, standardization, consistency, accuracy, uniqueness, relevance, timeliness, and security. According to the detection field of the rule, the above formula is used to calculate the quality score of each field.

[0074] The system calculates the overall quality fluctuation value based on the verification factor E, comparison interval N, and warning deviation K configured by the user, and issues a warning in the report for the test results that are greater than the warning deviation K. The calculation method for the overall quality fluctuation is:

[0075] Where k is the fluctuation value, N is the score of the most recent n times, S i is the score of the i-th test, and E is a constant factor.

[0076] A device for data quality detection, comprising: at least one memory and at least one processor;

[0077] at least one memory for storing a machine-readable program;

[0078] At least one processor is used to call the machine-readable program to execute a method for data quality detection.

[0079] The above-mentioned specific implementations are only specific cases of the present invention. The patent protection scope of the present invention includes but is not limited to the above-mentioned specific implementations. Any method and device for data quality detection that conforms to the claims of the present invention and any appropriate changes or substitutions made by a person of ordinary skill in the technical field shall fall within the patent protection scope of the present invention.

[0080] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A method for data quality detection, characterized in that: The steps are as follows: S1. Define basic rules; The detection script complies with the JavaScript language specification. The method signature of the detection script includes at least two parameters: tuple data and detection column. The return value of the detection script is of Boolean type. If the detection is passed, true is returned, and if the detection is not passed, false is returned. The client performs a basic regular expression check on the detection script created by the user to determine whether the grammar of the script meets the basic requirements of the JavaScript. After the client passes the check, the server performs a lexical, grammatical and semantic analysis check on the script. If the check succeeds, it is persisted to the database. Otherwise, the user is given the reason for the error and is prompted to modify the script. S2. Create business rules; Bind the defined basic rules to the data source, create business rules, and use AJAX to obtain the problem description and suggestion information of the basic rules according to the basic rules selected by the user, and echo them to the page; S3, create a detection task; Users create testing tasks based on business needs and store the test results in structured or unstructured form; During structured transfer, the target data table for transfer is set. The selected target data table for transfer contains a detection time column, a detection version column, an error flag column, and an error information description column. The JDBC method is used to actively connect to the target table for transfer set by the user, obtain the metadata information of the table, and check whether the table contains the above detection columns. If not, the user will be prompted to modify the target table for transfer. When transferring unstructured data, the test result data is transferred to the storage medium; S4. Perform quality inspection; Call the script engine to perform the detection task, verify the target data according to the configured verification rules, and generate a list of problem data and a scoring report. The specific steps are as follows: S401, perform distributed scheduling on the detection task, create a distributed task lock by setting the redis key value, and perform distributed scheduling on the task; S402, executing parameter parsing to parse the task parameters; S403, parsing the detection rule list, obtaining the corresponding JavaScript script list, creating a Java ScriptEngineManager script execution environment, parsing the JavaScript script written by the user, and preloading the script; S404, obtaining target detection data, parsing SQL according to the detection range set by the user, and generating an abstract syntax tree; S405, generating data extraction SQL according to the syntax tree, pulling data from the target data source, and transferring the acquired data into the script execution environment; S406, recording the verification results of all rules in the tuple. For the rules that failed the verification, the system obtains the problem description and suggested processing solution corresponding to the rule, and records the abnormal data tuple together with the obtained problem description and suggested processing solution; S407, determine whether the problem data storage option is enabled for the task, and if enabled, save the problem data and the problem description and suggestions to the elasticSearch non-relational database; Determine whether the task has enabled the problem data transfer option. If enabled, the problem data will be transferred to the target data source at the same time. During the data transfer process, continue to determine whether the correct data will also be transferred to the data source. If enabled, the verified correct data will also be transferred to the data source. Otherwise, the problem data, problem description and suggestions will be stored in the data source specified by the user. S5. Conduct quality scoring and overall volatility analysis.

2. A method for data quality detection according to claim 1, characterized in that: In step S5, the user configures a scoring weight wk for each rule K in the detection task, and calculates the quality score S based on the weight. The score is calculated as follows: Where S is the score, n is the number of rules, the score weight for rule K is wk, the total amount of test data is tk, and the amount of problem data is ek; The above formula calculates the quality score of each dimension respectively. According to the detection field of the rule, the above formula is used to calculate the quality score of each field respectively.

3. A method for data quality detection according to claim 2, characterized in that: In step S5, the overall quality fluctuation value is calculated according to the verification factor E, comparison interval N, and warning deviation P configured by the user, and a warning prompt is given in the report for the detection result greater than the warning deviation P. The calculation method of the overall quality fluctuation is: Among them, Q is the fluctuation value, N is the comparison interval value configured by the user, the score values ​​of the most recent times are taken, Si is the score value of the i-th detection, and E is the verification factor, which is a constant.

4. A data quality detection device, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to call the machine-readable program to execute the method according to any one of claims 1 to 3.

Images