A data quality inspection rule construction method, storage medium and system

Abstract

This invention provides a method, storage medium, and system for constructing data quality inspection rules. The method includes the following steps: obtaining data quality inspection rule templates preset by the user for various data standard types; collecting metadata of multiple fields used to describe business data and data information of each field metadata from the business system; for each field metadata, determining whether there exists a data standard among the multiple data standards preset by the user that has a text similarity greater than a preset threshold and is of the same type as the name of this field metadata, and if so, constructing a mapping relationship between the data standard and the field metadata; for field metadata with established mapping relationships, obtaining condition parameters based on instances of the data standards with which the mapping relationship has been established; obtaining the preset data quality rule template corresponding to the data standard as an SQL template; and constructing an SQL instance of the data quality inspection rule based on the condition parameters, the SQL template, and the data information of the field metadata.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method for constructing data quality inspection rules, a storage medium, and a system. Background Art

[0002] The power grid system generates a large amount of operational data during operation. This data reflects the system's operational status and needs to be collected and stored in the operational system. Currently, data quality inspection rules are typically used to check the quality of this data. If any data quality inspection result is abnormal, staff must monitor the corresponding power grid operation. In this process, different templates of data quality inspection rules are required for different types of data. Even for the same type of data, if the business objects differ, data quality inspection rules with the same template but different parameters are used. However, the multiple data quality inspection rules currently used are usually pre-defined. Given the massive volume and constant changes in the operational data generated by the power grid system, it is difficult to ensure that a suitable data quality inspection rule is matched for each piece of data. Summary of the Invention

[0003] The technical problem to be solved by this invention is how to provide appropriate data quality inspection rules for business data to perform quality inspection.

[0004] To address the aforementioned technical problems, this invention provides a method for constructing data quality inspection rules, comprising the following steps:

[0005] A. Obtain user-preset data quality inspection rule templates for various data standard types, wherein the data standard types include numerical, encoded, text, and date types;

[0006] B. Collect metadata of multiple fields used to describe business data and data information of each field metadata from the business system, wherein the data information includes name information, source information and data type information;

[0007] C. For each field metadata, determine whether there is a data standard among the multiple data standards preset by the user that has a text similarity greater than the preset threshold with the name of this field metadata and is consistent with the type of this field metadata. If it exists, construct the mapping relationship between the data standard and this field metadata.

[0008] D. Perform the following steps D1, D2, and D3 on the metadata of fields with established mapping relationships:

[0009] —D1. Based on the instance of the data standard that establishes a mapping relationship with the metadata of this field, obtain the condition parameters of the data quality inspection rule to be constructed;

[0010] —D2. Obtain the preset data quality rule template corresponding to the data standard that establishes a mapping relationship with the metadata of this field, and use it as the SQL template for the data quality inspection rules to be constructed;

[0011] —D3. Using the SQL generation engine, construct an SQL instance of the data quality check rules based on the condition parameters, the SQL template, and the data information of the field metadata.

[0012] Preferably, in step C, for the metadata of this field, first calculate the text similarity between each data standard and the name of the metadata of this field, and determine whether there is a data standard whose text similarity with the name of the metadata of this field is greater than a preset threshold. If there is, then compare and determine whether the data standard and the metadata of this field are of the same type. If not, then no further type determination is performed on the metadata of this field.

[0013] Preferably, in step C, if it is determined that among the multiple data standards preset by the user, there is a data standard whose text similarity to the metadata name of this field is greater than a preset threshold, but it is determined that the data standard is inconsistent with the metadata type of this field, then the mapping relationship between the data standard and the metadata of this field is not constructed.

[0014] Preferably, in step C, the preset threshold is 80%.

[0015] Preferably, in step D3, the SQL template is first decomposed into a select clause, a from clause, and a where clause. Then, the select clause is filled with the name information of the field metadata, the from clause is filled with the source information of the field metadata, and the where clause is filled with the condition parameters. Finally, the filled select clause, from clause, and where clause are combined into an SQL instance of the data quality check rule.

[0016] The present invention also provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps in the data quality inspection rule construction method described above.

[0017] The present invention also provides a data quality inspection rule construction system, including a computer-readable storage medium and a processor interconnected, wherein the computer-readable storage medium is as described above.

[0018] This invention has the following beneficial effects: After collecting multiple field metadata and data information of business data from the business system, for each field metadata, it determines whether there is a data standard among the multiple data standards preset by the user that has a text similarity greater than a preset threshold with the field metadata name and is consistent with the field metadata type. If it exists, it means that the data standard is applicable to the field metadata. Therefore, a mapping relationship between the data standard and the field metadata is constructed. Then, the condition parameters of the data quality inspection rule to be constructed are obtained according to the instance of the data standard. The preset data quality rule template corresponding to the data standard is obtained as the SQL template of the data quality inspection rule to be constructed. Then, the SQL instance of the data quality inspection rule is constructed according to the condition parameters, the SQL template and the data information of the field metadata. That is, the construction of the SQL instance of the data quality inspection rule is based on the field metadata used to describe the business data. In this way, even if the amount of business data is large and changes constantly, for each piece of business data, this invention will construct a suitable SQL instance of the data quality inspection rule based on the field metadata of the business data, and there will be no situation where a suitable data quality inspection rule cannot be matched to perform quality inspection on the business data. Attached Figure Description

[0019] Figure 1 This is a flowchart illustrating the method for constructing data quality inspection rules. Detailed Implementation

[0020] The present invention will be further described in detail below with reference to specific embodiments.

[0021] This embodiment provides a data quality inspection rule construction system. The system includes a computer-readable storage medium and a processor interconnected. The computer-readable storage medium stores a computer program, which, when executed by the processor, implements... Figure 1 The data quality inspection rule construction method shown includes the following steps A, B, C, and D.

[0022] A. Obtain user-preset data quality inspection rule templates for various data standard types, including numerical, encoded, text, and date types.

[0023] Data standards include numerical data standards, encoded data standards, text data standards, and date data standards; that is, data standard types include numerical, encoded, text, and date. For each of these data standard types, the user has preset corresponding data quality inspection rule templates. For example, if the user has preset two data standards, namely Data Standard One and Data Standard Two, specifically:

[0024] Data standard one is the length of the name. In a specific example, length <= 32. Therefore, data standard one belongs to the numerical data standard. The data quality check rule template preset by the user for this data standard one type (numerical) is "select ${colName}from ${tabName}where length <= ${stdParam}", where ${colName}, ${tabName}, and ${stdParam} are parameters to be filled.

[0025] Data standard two is the generated date. A specific example is form = YYYY-MM-DD. Therefore, data standard two belongs to the date data standard. The user's preset data quality check rule template for this data standard two type (date type) is "select ${Date}from ${tabName}where form = ${stdParam}", where ${Date}, ${tabName}, and ${stdParam} are parameters to be filled.

[0026] B. Collect metadata of multiple fields used to describe business data and data information of each field metadata from the business system. The data information includes name information, source information and data type information.

[0027] In this embodiment, the business system stores a large amount of business data generated by the power grid system during operation. This business data reflects the operating status of the power grid system. The data quality inspection rule construction system collects metadata for multiple fields used to describe the business data from the business system, as well as data information such as the name, source, and data type of each field's metadata. For example, the name of field metadata one is user_name, the source is user_info, and the data type is numeric; the name of field metadata two is PDate, the source is user_info, and the data type is date.

[0028] C. For each field's metadata, determine whether there exists a data standard among the user-preset multiple data standards whose text similarity to the name of this field's metadata is greater than a preset threshold and whose data type is consistent with this field's metadata. If such a standard exists, construct a mapping relationship between the data standard and the metadata of this field.

[0029] For each field's metadata, the system first uses the Levenshtein Distance algorithm to calculate the text similarity between each data standard and the name of this field's metadata. It then determines if any data standard has a text similarity greater than a preset threshold (specifically 80%). If any data standard has a text similarity greater than the preset threshold, it further compares and determines if the data standard and the metadata of this field are of the same type. If no data standard has a text similarity greater than the preset threshold, no further type determination is performed on the metadata of this field. If it is determined that among the user-preset data standards, there is one with a text similarity greater than the preset threshold and a type consistent with the metadata of this field, a mapping relationship is established between that data standard and the metadata of this field. If it is determined that among the user-preset data standards, there is one with a text similarity greater than the preset threshold but a type inconsistent with the metadata of this field, no mapping relationship is established between that data standard and the metadata of this field.

[0030] It should be noted that the Levenshtein Distance algorithm, also known as the Edit Distance algorithm, is an edit distance algorithm. It calculates the minimum number of editing operations required to transform one string into another, and obtains the edit distance between the two strings. The smaller the edit distance, the greater the text similarity between the two strings. The editing operations include replacing one character with another, inserting a character, and deleting a character.

[0031] For example, the metadata for this field is field metadata one, named "Name Length," with a specific instance of 10, and its type is numeric. The system uses the Levenshtein Distance algorithm to calculate the text similarity between data standard one (name length) and the name of this field metadata, and also calculates the text similarity between data standard two (generation date) and the name of this field metadata. The calculation result is that the text similarity between data standard one and the name of this field metadata is greater than a preset threshold, while the text similarity between data standard two and the name of this field metadata is not greater than the preset threshold. In this case, the system compares whether data standard one and the metadata of this field are of the same type, but does not compare whether data standard two and the metadata of this field are of the same type. The comparison result is that data standard one and the metadata of this field are of the same type, that is, the text similarity between the user-preset data standard one and the name of this field metadata is greater than the preset threshold and is of the same type as the metadata of this field. However, the user-preset data standard two is not greater than the preset threshold and is of the same type as the metadata of this field. Therefore, the system constructs a mapping relationship between data standard one and the metadata of this field, but does not construct a mapping relationship between data standard two and the metadata of this field.

[0032] For example, if the metadata for this field is field metadata two, its name is "Quantity," a specific instance is 5, and its type is numeric, the system uses the Levenshtein Distance algorithm to calculate the text similarity between data standard one (name length) and the name of this field metadata, and also calculates the text similarity between data standard two (generation date) and the name of this field metadata. The calculation result is that the text similarity between data standard one and the name of this field metadata is not greater than a preset threshold, and the text similarity between data standard two and the name of this field metadata is not greater than a preset threshold. In this case, the system does not compare whether data standard one and this field metadata are of the same type, nor does it compare whether data standard two and this field metadata are of the same type. That is, neither the user-preset data standard one nor data standard two has a text similarity greater than the preset threshold with the name of this field metadata and is of the same type as this field metadata. Therefore, the system neither constructs a mapping relationship between data standard one and this field metadata, nor constructs a mapping relationship between data standard two and this field metadata.

[0033] For example, the metadata for this field is field metadata three, named "Name," with the specific instance being "A," and its type being text. The system uses the Levenshtein Distance algorithm to calculate the text similarity between data standard one (name length) and the name of this field metadata, and to calculate the text similarity between data standard two (generation date) and the name of this field metadata. The calculation result is that the text similarity between data standard one and the name of this field metadata is greater than a preset threshold, while the text similarity between data standard two and the name of this field metadata is not greater than the preset threshold. In this case, the system compares whether data standard one and the metadata of this field are of the same type, but does not compare whether data standard two and the metadata of this field are of the same type. The comparison result is that data standard one and the metadata of this field are of different types. That is, neither the user-preset data standard one nor data standard two has a text similarity greater than the preset threshold with the name of this field metadata and is of the same type as the metadata of this field. Therefore, the system neither constructs a mapping relationship between data standard one and the metadata of this field, nor constructs a mapping relationship between data standard two and the metadata of this field.

[0034] D. Perform the following steps D1, D2, and D3 on the metadata of fields with established mapping relationships.

[0035] In this embodiment, field metadata one has established a mapping relationship with data standard one, while field metadata two and field metadata three have not established a mapping relationship with data standard one. Therefore, the following steps D1, D2 and D3 are performed on field metadata one, which has established a mapping relationship.

[0036] —D1. Based on the instance of the data standard that establishes a mapping relationship with the metadata of this field, obtain the condition parameters of the data quality inspection rule to be constructed.

[0037] For the field metadata 1 with an established mapping relationship, the system obtains the instance length of the data standard 1 with which it has an established mapping relationship, which is less than or equal to 32, and obtains the condition parameter of the data quality inspection rule to be constructed as 32 based on the instance.

[0038] —D2. Obtain the preset data quality rule template corresponding to the data standard that establishes a mapping relationship with the metadata of this field, and use it as the SQL template for the data quality inspection rules to be constructed.

[0039] For the metadata of the field with an established mapping relationship, the system obtains the preset data quality rule template "select ${colName}from ${tabName}where length<=${stdParam}" corresponding to the data standard I with which it has an established mapping relationship, and uses this data quality rule template as the SQL template for the data quality inspection rule to be constructed. That is, the SQL template for the data quality inspection rule to be constructed is "select ${colName}from ${tabName}where length<=${stdParam}".

[0040] It should be noted that SQL is an abbreviation for Structured Query Language, which is a computer language used to access, query, update, and manage data in relational databases.

[0041] —D3. Using the SQL generation engine, construct an SQL instance of data quality check rules based on the data information of the condition parameters, SQL template, and the metadata of the field.

[0042] In this embodiment, the data information of the field metadata 1, which has been mapped to data standard 1, is as follows: name information is user_name, source information is user_info, and data type information is numeric. The SQL generation engine comprises three modules: a SQL block module, a parameter filling module, and a SQL combination module. The specific process by which the system uses the SQL generation engine to construct an SQL instance of a data quality check rule is as follows: First, the SQL block module decomposes the data quality rule template “select ${colName} from ${tabName} where length <= ${stdParam}” into the select clause “select ${colName}”, the from clause “from ${tabName}”, and the where clause “where length <= ${stdParam}”. Then, the parameter filling module fills the select clause with the name information “user_name” of the first metadata field, the from clause with the source information “user_info”, and the where clause with the condition parameter “32” obtained in step D1. Finally, the SQL combination module combines the filled select, from, and where clauses to obtain the SQL instance of the data quality check rule “select user_name from user_info where length <= 32”. Thus, the SQL instance of this data quality check rule “select user_name from user_info where”... The construction of "length<=32" is based on the field metadata one used to describe the business data. It is more appropriate to use the SQL instance of the data quality check rule to perform quality checks on the business data described by the field metadata one. In this way, even if the amount of business data is large and changes constantly, this invention will construct a suitable SQL instance of data quality check rule based on the field metadata of the business data for each type of business data, and there will be no situation where a suitable data quality check rule cannot be matched to perform quality checks on the business data.

[0043] The above description is merely an embodiment of the present invention and does not limit the scope of patent protection. Any non-substantial changes or substitutions made by those skilled in the art based on the present invention will still fall within the scope of patent protection.

Claims

1. A method for constructing data quality inspection rules, characterized in that, Includes the following steps: A. Obtain user-preset data quality inspection rule templates for various data standard types, wherein the data standard types include numerical, encoded, text, and date types; B. Collect metadata of multiple fields used to describe business data and data information of each field metadata from the business system, wherein the data information includes name information, source information and data type information; C. For each field metadata, determine whether there is a data standard among the multiple data standards preset by the user that has a text similarity greater than the preset threshold with the name of this field metadata and is consistent with the type of this field metadata. If it exists, construct the mapping relationship between the data standard and this field metadata. D. Perform the following steps D1, D2, and D3 on the metadata of fields with established mapping relationships: —D1. Based on the instance of the data standard that establishes a mapping relationship with the metadata of this field, obtain the condition parameters of the data quality inspection rule to be constructed; —D2. Obtain the preset data quality rule template corresponding to the data standard that establishes a mapping relationship with the metadata of this field, and use it as the SQL template for the data quality inspection rules to be constructed; —D3. Using the SQL generation engine, construct an SQL instance of the data quality check rules based on the condition parameters, the SQL template, and the data information of the field metadata.

2. The data quality inspection rule construction method according to claim 1, characterized in that, In step C, for the metadata of this field, first calculate the text similarity between each data standard and the name of the metadata of this field, and determine whether there is a data standard whose text similarity with the name of the metadata of this field is greater than a preset threshold. If there is, then compare and determine whether the data standard and the metadata of this field are of the same type. If not, then no further type determination is performed on the metadata of this field.

3. The data quality inspection rule construction method according to claim 2, characterized in that, In step C, if it is determined that among the multiple data standards preset by the user, there is a data standard whose text similarity to the metadata name of this field is greater than a preset threshold, but it is determined that the data standard is inconsistent with the metadata type of this field, then the mapping relationship between the data standard and the metadata of this field will not be constructed.

4. The data quality inspection rule construction method according to claim 1, characterized in that, In step C, the preset threshold is 80%.

5. The data quality inspection rule construction method according to claim 1, characterized in that, In step D3, the SQL template is first decomposed into a select clause, a from clause, and a where clause. Then, the select clause is filled with the name information of the field metadata, the from clause is filled with the source information of the field metadata, and the where clause is filled with the condition parameters. Finally, the filled select clause, from clause, and where clause are combined into an SQL instance of the data quality check rule.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps in the data quality inspection rule construction method as described in any one of claims 1 to 5.

7. A data quality inspection rule building system, comprising interconnected computer-readable storage media and a processor, characterized in that... The computer-readable storage medium is as described in claim 6.

Images