A data cleaning method and device

Abstract

The embodiment of the present application provides a kind of data cleaning method and device, the method includes determining data attribute field from each business field of business scene, by data attribute field, determine the search field set from each database table, and determine the database table set matched with search field set, the source data associated with business scene is analyzed, determine the business data value of the business field to be cleaned, according to search field set and database table set, the structured query language sql data cleaning script corresponding to the business data value of the business field to be cleaned is constructed, and the relevant business data in the database table set associated with the business data value of the business field to be cleaned is cleaned by sql data cleaning script.It is thus, the scheme can realize the accurate cleaning of dirty data automatically by the constructed sql data cleaning script, so as to effectively reduce the time consumed by test personnel for cleaning dirty data.

Description

Technical Field

[0001] This invention relates to the field of financial technology (Fintech), and more particularly to a data cleaning method and apparatus. Background Technology

[0002] With the development of computer technology, more and more technologies are being applied in the financial field, and the traditional financial industry is gradually transforming into fintech. However, due to the security and real-time requirements of the financial industry, higher demands are being placed on technology. During financial product testing, as the complexity of data formats and the evolution of data lifecycles increase, the amount of dirty data (such as data caused by defects, human modification, or incomplete data cleaning) is also increasing. This dirty data can cause unnecessary problems and low testing efficiency. Therefore, to ensure the smooth progress of testing, effectively cleaning up dirty data has become an urgent problem to be solved.

[0003] Currently, there are two main methods for cleaning up dirty data. One method is to delete all data in the table. However, this method will result in no data available for subsequent testing. The other method is to delete business data of a certain data type. That is, to manually organize the database tables and determine the deletion conditions to delete the data. However, this method requires a long time and a lot of manpower.

[0004] In summary, there is an urgent need for a data cleaning method to achieve automated and accurate cleaning of dirty data. Summary of the Invention

[0005] This invention provides a data cleaning method and apparatus for automatically and accurately cleaning dirty data.

[0006] In a first aspect, embodiments of the present invention provide a data cleaning method, comprising:

[0007] Identify the data attribute fields from the various business fields in the business scenario;

[0008] Using the data attribute fields, a set of search fields is determined from each database table, and a set of database tables that match the set of search fields is determined; wherein, any search field in the set of search fields has a distinguishability in the database table where the search field is located that meets a set requirement; the distinguishability is the percentage of the number of search fields with different field values ​​in the database table where the search field is located.

[0009] The source data associated with the business scenario is analyzed to determine the business data values ​​of the business fields to be cleaned; the business fields to be cleaned are included in the set of search fields.

[0010] Based on the set of search fields and the set of database tables, a structured query language (SQL) data cleanup script is constructed corresponding to the business data values ​​of the business fields to be cleaned. The SQL data cleanup script is then used to clean up the relevant business data in the set of database tables associated with the business data values ​​of the business fields to be cleaned.

[0011] In the above technical solution, a set of retrieval fields is constructed by using any business field associated with a business scenario as a data attribute field. The scope of data cleanup is determined by combining this set of retrieval fields with a set of database tables and the business fields to be cleaned. This facilitates automated and precise cleanup of dirty data in the database tables involved in the business scenario. Specifically, for any business scenario, data attribute fields are determined from the business fields within that scenario. Using these data attribute fields, a set of retrieval fields and a set of database tables matching the retrieval field set are identified. By calculating the discriminability of each business field in each database table to determine the strong correlation between each business field and the data attribute fields, some business fields with weak correlation can be excluded. This results in a set of highly correlated retrieval fields, effectively ensuring more targeted cleanup of the corresponding dirty data. Furthermore, the business fields causing data anomalies in the source data associated with the business scenario are analyzed and located, accurately identifying the business data values ​​of the business fields to be cleaned that are causing the anomalies. Then, based on the set of search fields and the set of database tables, an accurate SQL data cleanup script can be constructed corresponding to the business data values ​​of the business fields to be cleaned. This SQL data cleanup script can then be used to automatically and precisely clean the relevant business data in the set of database tables associated with the business data values ​​of the business fields to be cleaned. In this way, the solution, through the constructed SQL data cleanup script, can achieve automated and precise cleanup of dirty data, thereby ensuring, to the greatest extent possible, that there is no dirty data during testing of business scenarios, effectively reducing the time and manpower costs spent by testers on cleaning up dirty data.

[0012] Optionally, determining the set of retrieval fields from each database table using the data attribute fields includes:

[0013] Identify the first database tables that have the data attribute fields from each database table;

[0014] For any first database table, the primary key field in the first database table is used as the search field in the search field set, and the index field in the first database table that meets the set discrimination requirement is used as the search field in the search field set.

[0015] Using the primary key field as the data attribute field, the retrieval field is further determined from each database table;

[0016] The index fields in the first database table whose discrimination does not meet the set requirements are used as the data attribute fields, and the retrieval fields are determined from each of the database tables.

[0017] In the above technical solution, since the primary key field in each first database table determined by the data attribute field has a strong correlation with the data attribute field, that is, the primary key field has high distinguishability in the first database table where the primary key field resides, it can be directly used as the retrieval field in the first database table where the primary key field resides. Furthermore, for any index field in each first database table, the distinguishability of the index field in the first database table is calculated to determine whether the index field has a strong correlation with the data attribute field, thereby determining whether the index field can be used as the retrieval field in the first database table where the index field resides. Simultaneously, if the primary key field in a database table is not a data attribute field, the primary key field in that database table can be used as a data attribute field to return to the step of determining the first database tables with data attribute fields from each database table, thereby continuing to determine the retrieval field. Moreover, if the distinguishability of an index field in a database table does not meet the set requirements, the index field can be used as a data attribute field to return to the step of determining the first database tables with data attribute fields from each database table, thereby continuing to determine the retrieval field. In this way, the set of highly relevant search fields can be accurately determined.

[0018] Optionally, the step of constructing a Structured Query Language (SQL) data cleaning script corresponding to the business data values ​​of the business fields to be cleaned, based on the retrieval field set and the database table set, includes:

[0019] Based on the set of search fields, retrieve the field values ​​of each search field that are related to the business data value of the business field to be cleaned from the set of database tables;

[0020] Based on the field values ​​of each search field and the database table where each search field is located, construct an SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned.

[0021] In the above technical solution, since the business fields to be cleaned are also included in the search field set, the field values ​​of each search field related to the business data values ​​of the business fields to be cleaned can be retrieved from the database table set through the search field set. Based on the field values ​​of each search field and the database table where each search field is located, an SQL data cleaning script for automatically cleaning dirty data can be constructed.

[0022] Optionally, it also includes:

[0023] Determine the master-slave relationship among the database tables in the database table set;

[0024] For any database table in the database table set, the search field in the database table is used as the search condition for the database table;

[0025] Based on the set of search fields, the values ​​of each search field related to the business data value of the business field to be cleaned are retrieved from the set of database tables, including:

[0026] Based on the master-slave relationship between the database tables, the field values ​​of each retrieval field related to the business data value of the business field to be cleaned are retrieved in sequence;

[0027] Based on the field values ​​of each search field and the database table where each search field is located, construct an SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned, including:

[0028] Based on the search conditions of each database table, and using the field values ​​of each search field and the database table where each search field is located, an SQL data cleanup script is constructed corresponding to the business data values ​​of the business fields to be cleaned.

[0029] In the above technical solution, by determining the master-slave relationship among the database tables in the database table set, it is possible to quickly query the field values ​​of each retrieval field related to the business data values ​​of the business field to be cleaned, thereby improving the efficiency of constructing SQL data cleanup scripts. Furthermore, for any database table, by using the retrieval fields in that table as retrieval conditions, and based on the retrieval conditions of each database table, using the field values ​​of each retrieval field and the database table where each retrieval field resides, an SQL data cleanup script for automated and accurate cleanup of dirty data can be constructed.

[0030] Optionally, determining the master-slave relationship between the database tables in the database table set includes:

[0031] Determine the intersection field between the first database table and the second database table in the database table set; the first database table and the second database table are any two database tables in the database table set;

[0032] If the intersection field is a primary key field in the first database table and an index field in the second database table, then a master-slave relationship is established between the first database table and the second database table; the first database table is the master table; and the second database table is the slave table.

[0033] If the intersection field is a primary key field in the second database table and an index field in the first database table, then a master-slave relationship is established between the second database table and the first database table; the second database table is the master table; and the first database table is the slave table.

[0034] In the above technical solution, the master-slave relationship between the database tables is determined by the intersection field of the first and second database tables in the database table set. After determining the intersection field, it is determined whether the intersection field is a primary key field or an index field in the first (or second) database table. If the intersection field is a primary key field in the first database table and an index field in the second database table, then a master-slave relationship is established between the first and second database tables (i.e., the first database table is the master table, and the second database table is the slave table). If the intersection field is an index field in the first database table and a primary key field in the second database table, then a master-slave relationship is established between the second and first database tables (i.e., the second database table is the master table, and the first database table is the slave table). In this way, the determined master-slave relationship between the database tables can realize fast querying of field values ​​of each retrieval field related to the business data value of the business field to be cleaned.

[0035] Optionally, each search field in the set of search fields is determined in the following manner:

[0036] For each business field in any database table, perform deduplication on the corresponding business data values ​​in the database table where the business field is located, and count the first number of at least one deduplicated business data value.

[0037] The second count is calculated based on the total number of business data entries for the business field in the corresponding database table.

[0038] The distinguishability of the business field is determined based on the first quantity and the second quantity;

[0039] If the distinguishability of the business field meets the set requirements, then the business field is determined as the retrieval field.

[0040] In the above technical solution, for each business field in any database table, the business data values ​​corresponding to the business field in the database table are deduplicated, and the first number of at least one deduplicated business data value is counted. Combined with the second number of the total number of business data records in the database table where the business field is located, the distinguishability of the business field can be accurately determined. This provides support for determining whether the business field is a retrieval field, that is, determining whether the business field is a field with a strong correlation with the data attribute field.

[0041] Optionally, the step of analyzing the source data associated with the business scenario to determine the business data values ​​of the business fields to be cleaned includes:

[0042] Set any one of the search fields in the set of search fields corresponding to the business scenario as the batch processing field, and construct at least one first SQL data query script using the batch processing field and the source data associated with the business scenario to determine whether the batch processing field is a business field to be cleaned; determine the business data value of the business field to be cleaned using the at least one first SQL data query script; and / or,

[0043] Based on the source data associated with the business scenario, multiple second SQL data query scripts are constructed to detect whether there are any business fields in the source data that need to be cleaned and cause data anomalies. The query results determined by the multiple second SQL data query scripts are compared. If there are inconsistent result data in the query results corresponding to the multiple second SQL data query scripts, the database tables associated with the multiple second SQL data query scripts are queried in reverse to determine the values ​​of each business field corresponding to the inconsistent business fields in the multiple query results. The values ​​of each business field corresponding to the inconsistent business fields in the multiple query results are determined as the business data values ​​of the business fields to be cleaned.

[0044] In the above technical solution, by analyzing and locating some business fields in the source data associated with the business scenario that cause data anomalies, the business data values ​​of the business fields that need to be cleaned can be accurately determined, thereby providing support for the subsequent accurate construction of SQL data cleaning scripts for automated and precise cleaning of dirty data.

[0045] Secondly, embodiments of the present invention also provide a data cleaning apparatus, comprising:

[0046] The determination unit is used to identify data attribute fields from various business fields in a business scenario.

[0047] The processing unit is configured to determine a set of retrieval fields from various database tables using the data attribute fields, and to determine a set of database tables that match the set of retrieval fields; wherein, any retrieval field in the set of retrieval fields has a distinguishability in the database table where the retrieval field is located that meets a set requirement; the distinguishability is the percentage of different field values ​​of the retrieval field in the database table where the retrieval field is located; analyze the source data associated with the business scenario to determine the business data values ​​of the business fields to be cleaned; the business fields to be cleaned are included in the set of retrieval fields; construct a structured query language (SQL) data cleanup script corresponding to the business data values ​​of the business fields to be cleaned based on the set of retrieval fields and the set of database tables, and clean the relevant business data in the set of database tables associated with the business data values ​​of the business fields to be cleaned using the SQL data cleanup script.

[0048] Optionally, the processing unit is specifically used for:

[0049] Identify the first database tables that have the data attribute fields from each database table;

[0050] For any first database table, the primary key field in the first database table is used as the search field in the search field set, and the index field in the first database table that meets the set discrimination requirement is used as the search field in the search field set.

[0051] Using the primary key field as the data attribute field, the retrieval field is further determined from each database table;

[0052] The index fields in the first database table whose discrimination does not meet the set requirements are used as the data attribute fields, and the retrieval fields are determined from each of the database tables.

[0053] Optionally, the processing unit is specifically used for:

[0054] Based on the set of search fields, retrieve the field values ​​of each search field that are related to the business data value of the business field to be cleaned from the set of database tables;

[0055] Based on the field values ​​of each search field and the database table where each search field is located, construct an SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned.

[0056] Optionally, the processing unit is further configured to:

[0057] Determine the master-slave relationship among the database tables in the database table set;

[0058] For any database table in the database table set, the search field in the database table is used as the search condition for the database table;

[0059] The processing unit is specifically used for:

[0060] Based on the master-slave relationship between the database tables, the field values ​​of each retrieval field related to the business data value of the business field to be cleaned are retrieved in sequence;

[0061] The processing unit is specifically used for:

[0062] Based on the search conditions of each database table, and using the field values ​​of each search field and the database table where each search field is located, an SQL data cleanup script is constructed corresponding to the business data values ​​of the business fields to be cleaned.

[0063] Optionally, the processing unit is specifically used for:

[0064] Determine the intersection field between the first database table and the second database table in the database table set; the first database table and the second database table are any two database tables in the database table set;

[0065] If the intersection field is a primary key field in the first database table and an index field in the second database table, then a master-slave relationship is established between the first database table and the second database table; the first database table is the master table; and the second database table is the slave table.

[0066] If the intersection field is a primary key field in the second database table and an index field in the first database table, then a master-slave relationship is established between the second database table and the first database table; the second database table is the master table; and the first database table is the slave table.

[0067] Optionally, the processing unit is specifically used for:

[0068] For each business field in any database table, perform deduplication on the corresponding business data values ​​in the database table where the business field is located, and count the first number of at least one deduplicated business data value.

[0069] The second count is calculated based on the total number of business data entries for the business field in the corresponding database table.

[0070] The distinguishability of the business field is determined based on the first quantity and the second quantity;

[0071] If the distinguishability of the business field meets the set requirements, then the business field is determined as the retrieval field.

[0072] Optionally, the processing unit is specifically used for:

[0073] Set any one of the search fields in the set of search fields corresponding to the business scenario as the batch processing field, and construct at least one first SQL data query script using the batch processing field and the source data associated with the business scenario to determine whether the batch processing field is a business field to be cleaned; determine the business data value of the business field to be cleaned using the at least one first SQL data query script; and / or,

[0074] Based on the source data associated with the business scenario, multiple second SQL data query scripts are constructed to detect whether there are any business fields in the source data that need to be cleaned and cause data anomalies. The query results determined by the multiple second SQL data query scripts are compared. If there are inconsistent result data in the query results corresponding to the multiple second SQL data query scripts, the database tables associated with the multiple second SQL data query scripts are queried in reverse to determine the values ​​of each business field corresponding to the inconsistent business fields in the multiple query results. The values ​​of each business field corresponding to the inconsistent business fields in the multiple query results are determined as the business data values ​​of the business fields to be cleaned.

[0075] Thirdly, embodiments of the present invention provide a computing device, including at least one processor and at least one memory, wherein the memory stores a computer program, and when the program is executed by the processor, the processor performs any of the data cleaning methods described in the first aspect.

[0076] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing a computer program executable by a computing device, wherein when the program is run on the computing device, the computing device performs any of the data cleaning methods described in the first aspect. Attached Figure Description

[0077] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0078] Figure 1 A flowchart illustrating a data cleaning method provided in an embodiment of the present invention;

[0079] Figure 2 A schematic diagram of a process for establishing a data feature model provided in an embodiment of the present invention;

[0080] Figure 3 A schematic diagram of a process for updating a data feature model provided in an embodiment of the present invention;

[0081] Figure 4 A flowchart illustrating the process of constructing an SQL data cleanup script, provided as an embodiment of the present invention;

[0082] Figure 5 This is a schematic diagram of the structure of a data cleaning device provided in an embodiment of the present invention;

[0083] Figure 6 This is a schematic diagram of the structure of a computing device provided in an embodiment of the present invention. Detailed Implementation

[0084] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are merely some embodiments of this invention, and not all embodiments. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0085] The following section will first explain some of the terms used in the embodiments of the present invention to facilitate understanding by those skilled in the art.

[0086] (1) Execution batch: A system application that switches and schedules batch tasks for the day to be executed in the background to complete a specific function.

[0087] (2) Data form: Data in the system at a certain business lifecycle stage. Data in the same cycle and state have characteristic values ​​that satisfy the consistency criteria. Each different cycle / state corresponds to a data form.

[0088] (3) Data feature model: a model of relationships between data attributes, data feature values, and persistent data.

[0089] (4) Learning: Known data attribute samples are identified by a program to identify other related data attributes of these samples.

[0090] (5) Search field: By restricting the search by field in the database, the relevance of the search results can be controlled and the search efficiency can be improved.

[0091] (6) TSP (Task Scheduling Platform): A task scheduling platform that integrates scattered and unreliable timed tasks into a unified platform and enables cluster management, scheduling, and distributed deployment of timed tasks.

[0092] The above describes some of the terms used in the embodiments of the present invention. The technical features involved in the embodiments of the present invention will be described below.

[0093] Figure 1 An exemplary illustration shows the flow of a data cleaning method provided by an embodiment of the present invention, which can be executed by a data cleaning device.

[0094] like Figure 1 As shown, the process specifically includes:

[0095] Step 101: Determine the data attribute fields from the various business fields in the business scenario.

[0096] In this embodiment of the invention, for any product (such as a financial product), the business scenario involves a large amount of business data, which may contain dirty data. To ensure the smooth and successful testing of the product, it is usually necessary to clean up the dirty data in the business data involved in the product. Therefore, in practical applications, since the data cleaning conditions differ for different products, a SQL data cleaning script is constructed for each product to clean up dirty data. Before constructing the SQL data cleaning script, it is necessary to determine a business field from the various business fields in the business data involved in the product's business scenario as a data attribute field to determine the set of retrieval fields and the set of database tables required for constructing the SQL data cleaning script.

[0097] For example, taking a financial loan product business scenario, the business data involved in this scenario includes fields such as customer number, customer ID number, loan amount, loan start time, and loan maturity time. Before testing this financial loan product, it's necessary to clean up any dirty data in the test data to ensure the test data is free of contaminated data, thus ensuring the test can proceed smoothly and efficiently. Therefore, to automatically and accurately clean up the dirty data in the test data, a data feature model for the financial loan product needs to be established (this model includes a set of retrieval fields and database tables required to build SQL data cleaning scripts). When establishing this model, a business field relevant to the financial loan product should be selected as a data attribute field for learning. For example, the customer ID number can be used as a data attribute field to identify business fields (i.e., fields that can be used for global retrieval) and database tables that are strongly correlated with the customer ID number.

[0098] Step 102: Using the data attribute fields, determine the set of search fields from each database table, and determine the set of database tables that match the set of search fields.

[0099] In this embodiment of the invention, any retrieval field in the retrieval field set has a distinguishability in the database table where it resides that meets a set requirement; the distinguishability is the percentage of different field values ​​that the retrieval field has in the database table where it resides. Specifically, after selecting a business field as a data attribute field from the various business fields in the business scenario, each first database table containing the data attribute field can be determined from the database tables contained in at least one database. For any first database table, the primary key field in that first database table is used as the retrieval field and added to the retrieval field set. Simultaneously, the index field in that first database table whose distinguishability meets the set requirement can be used as the retrieval field and added to the retrieval field set. That is, since the primary key field in each first database table determined by the data attribute field has a strong correlation with the data attribute field, the distinguishability of the primary key field in the first database table where the primary key field resides is high. Therefore, the primary key field can be directly used as the retrieval field in the first database table where the primary key field resides, and thus can be directly added to the retrieval field set. Furthermore, for any index field in each first database table, the distinguishability of the index field in that first database table is calculated, and it is determined whether the distinguishability of the index field meets the set requirements to determine whether the index field has a strong correlation with the data attribute field, thereby determining whether the index field can be used as the retrieval field in the first database table where the index field is located. Specifically, if the primary key field in a database table is not a data attribute field, the primary key field in that database table can be used as a data attribute field to return to the step of determining each first database table with the data attribute field, thereby continuing to determine the retrieval field from each database table. Similarly, if the distinguishability of an index field in a database table does not meet the set requirements, the index field can be used as a data attribute field to return to the step of determining each first database table with the data attribute field, thereby continuing to determine the retrieval field from each database table. In this way, this scheme can accurately determine the set of highly correlated retrieval fields and the set of database tables matching the set of retrieval fields. The set requirements can be set based on the experience of those skilled in the art or according to the actual application scenario, and this embodiment of the invention does not limit this. For example, the requirement can be a discrimination threshold, which can be flexibly set according to actual application needs, such as 0.1, 0.2 or 0.5.

[0100] The retrieval field in the retrieval field set can be determined as follows: For each business field in any database table, deduplicate the corresponding business data values ​​in the table containing that business field to obtain at least one deduplicated business data value. Count the first number of these at least one deduplicated business data value, and simultaneously count the second number of total business data records for that business field in the corresponding database table. Then, based on the first and second counts, the distinguishability of the business field can be accurately determined. If the distinguishability of the business field meets the set requirements, it can be designated as a retrieval field and added to the retrieval field set.

[0101] Furthermore, in practical applications, the data feature models of different products may differ, and the multiple databases involved may also vary. Therefore, a product-specific data feature model can be established. For any product (such as a financial product), a business field related to that product is selected as a data attribute field, and learning is performed using this data attribute field to build the product's data feature model (this data feature model includes the set of retrieval fields and database tables required to construct SQL data cleaning scripts).

[0102] Among them, combined Figure 2 , Figure 2 This is a flowchart illustrating a process for establishing a data feature model according to an embodiment of the present invention. The process of establishing a product's data feature model through learning from the product's data attribute fields is described below:

[0103] Step a: Identify at least one database table containing the product's data attribute field from at least one database, and identify each business field in the at least one database table. For each database table, if it is determined that the data attribute field is not the primary key field of the database table, then the primary key field of the database table is used as the new data attribute field, and the step of identifying at least one database table containing the product's data attribute field from at least one database is repeated. In this way, a list of relevant business fields can be learned, and the initial set of search fields can be obtained by deduplicating each business field in the list of business fields. A set of database tables that match the initial set of search fields can also be obtained.

[0104] For example, continuing with the above-mentioned financial loan product, based on the selected data attribute field (such as the customer's ID number), all database tables containing the customer's ID number are scanned from the database field configuration table (such as mysqlinformation_schema.COLUMNS) involved in the business scenario of the financial loan product. The primary key fields and index fields of each database table containing the customer's ID number are then scanned through the database index configuration table (such as mysql information_schema.STATISTICS). For each database table, determine if the primary key field in that table is the customer's ID number. If not, use that primary key field as a new data attribute field and scan the database field configuration table (e.g., mysqlinformation_schema.COLUMNS) related to the financial loan product's business scenario to find all database tables containing this new data attribute field. Then, scan the database index configuration table (e.g., mysqlinformation_schema.STATISTICS) to find the primary key and index fields of each database table containing the new data attribute field. Continue this process until the primary key field in all database tables containing the new data attribute field is the same. This will generate a list of business fields based on the customer's ID number. This list contains the primary key and index fields of all the scanned database tables. After deduplicating the business fields in this list, you will get the initial set of search fields and the corresponding set of database tables. For example, each customer of a financial loan product has an ID_NO (national ID number) and a unique CUST_ID (customer ID). Each customer also has one or more CARD_NOs (card numbers, such as bank card numbers or credit card numbers), which are also unique. Similarly, mobile phone numbers, loan receipt numbers, transaction serial numbers, business serial numbers, etc., can all be found through the primary key field of the database table to find the relationships between them. Ultimately, a set of data feature attribute fields can be generated, and then other related attribute fields can be found through any attribute field of the data feature.

[0105] Step b: Considering that some indexed fields may have low distinguishability in the database table, and the factors of composite primary keys and composite indexes, it is necessary to exclude some indexed fields. For any database table, calculate the distinguishability of each business field in the table, and determine whether the distinguishability of each business field meets the set requirements, thereby determining whether to add the business field as a search field to the search field set. Simultaneously, for business fields whose distinguishability does not meet the set requirements, the business field can be returned as a data attribute field to perform the step of determining at least one database table containing the data attribute field from at least one database through the product's data attribute field, in order to continue determining the search fields. In this way, the target search field set can be finally determined, which is also the search field set needed to clean up dirty data. Of course, if there are duplicate search fields in the target search field set, deduplication will be performed on each search field in the target search field set to ensure that each search field in the target search field set is different.

[0106] For example, let's take database table A as an example. By scanning database table A, we can identify all the business fields contained in it, as well as the business data values ​​corresponding to each business field and the total number of rows of business data in the table. The discrimination of each business field is then calculated using the discrimination formula: Discrimination = Enumerated value of business field / Total number of rows of business data in the table. The higher the discrimination of a business field, the stronger the correlation between that business field and the data attribute field. Here, the enumerated value of a business field refers to the number of at least one business data value obtained after deduplication of the business data values ​​corresponding to that business field. It should be noted that, considering that some database tables may have a small total number of rows of business data, affecting the dispersion result, the total number of rows of business data in the database table can be configured to 10,000, and the discrimination threshold can be set to 0.1 to determine whether the business field has a strong correlation with the data attribute field, thereby excluding weakly correlated business fields and ultimately obtaining business fields with strong correlation with the data attribute field. Since the primary key field in a database table has a strong correlation with the data attribute field—meaning it has high distinguishability within the table—it can be directly used as the retrieval field. Alternatively, the distinguishability can be calculated for each business field in the table, and each field's distinguishability can be checked against the set requirements to determine the retrieval field.

[0107] For example, assuming a discrimination threshold of 0.1, and assuming database table A has three business fields: business field a, business field b, and business field c, where business field a is the primary key field, and business fields b and c are composite index fields, then the total number of business data rows in database table A can be calculated as: `num = select count(*) from A`; the discrimination of business field b = (select count(distinct b) from A) / num; the discrimination of business field c = (select count(distinct c) from A) / num. Since business field a is the primary key field, it can be directly used as the retrieval field in database table A. For example, if the discriminancy of business field b is calculated to be 0.3, then 0.3 is greater than 0.1, and therefore business field b can be used as the retrieval field in database table A, indicating a strong correlation between business field b and the data attribute field. Alternatively, if the discriminancy of business field c is calculated to be 0.2, then 0.2 is greater than 0.1, and therefore business field c can be used as the retrieval field in database table A, indicating a strong correlation between business field c and the data attribute field. Or, if the discriminancy of business field b is calculated to be 0.3, then 0.3 is greater than 0.1, and therefore business field b can be used as the retrieval field in database table A, indicating a strong correlation between business field b and the data attribute field. Or, if the discriminancy of business field c is calculated to be 0.05, then 0.05 is less than 0.1, indicating a weak correlation between business field c and the data attribute field, and this business field c can be used as a data attribute field. The process then returns to the step of determining at least one database table containing the data attribute field from at least one database using the product's data attribute field, in order to further determine the retrieval field. If multiple business fields in database table A are primary key fields, the distinguishability of these multiple business fields can be calculated to determine whether they can all be used as retrieval fields.

[0108] Step c: Based on the final set of search fields obtained in step b, for some business fields with different names but the same meaning in different database tables, the problem can be solved by configuring the corresponding field mapping table. This is applicable to special scenarios.

[0109] For example, if the attributes and business data values ​​of the retrieval field 'a' in database table A and the business field 'a1' in database table B are the same, then the business field 'a1' also needs to be maintained in the retrieval field set.

[0110] Step d: Based on the final set of search fields and the set of database tables obtained in step b, construct the search conditions for the database tables, and determine the master-slave relationship between the database tables in the set of database tables.

[0111] Specifically, in combination Figure 3 , Figure 3 This is a flowchart illustrating an embodiment of the present invention for updating a data feature model, and the implementation process of updating the data feature model is described below:

[0112] For any database table in the set of database tables (e.g., database table 1, database table 2, and database table 3), and based on the set of search fields (e.g., business field A, business field B, and business field C), use the search fields in that database table as the search conditions for the database table. For example, use business field A as the search condition for database table 1, use business fields A and B as the search conditions for database table 2, and use business fields B and C as the search conditions for database table 3. Also, add the search conditions of each database table to the data feature model. Furthermore, the intersection field between the first and second database tables in the database table set is determined. If this intersection field is a primary key field in the first database table and an index field in the second database table, a master-slave relationship is established between the first and second database tables (i.e., the first database table is the master table, and the second database table is the slave table). If this intersection field is a primary key field in the second database table and an index field in the first database table, a master-slave relationship is established between the second and first database tables (i.e., the second database table is the master table, and the first database table is the slave table). Here, the first and second database tables can be any two database tables in the database table set. In this way, the determined master-slave relationships between the database tables enable fast querying of field values ​​related to the business data values ​​of the business fields to be cleaned. For example, taking database table 1 and database table 2 as an example, if we determine that the intersection field between database table 1 and database table 2 is business field A, and since business field A is the primary key field in database table 1 and the index field in database table 2, we can determine that database table 1 is the primary table and database table 2 is the secondary table. Therefore, we can establish a primary-secondary relationship between database table 1 and database table 2.

[0113] For example, taking database table 1 as an example, the retrieval conditions for determining the database table are described. Assume that database table 1 has three business fields: business field A, business field B, and business field C. The retrieval fields are business field A and business field B. When business field A is a primary key field, the retrieval condition for database table 1 can be set as: business field A in (the set of business data values ​​of business field A). This eliminates the need to use retrieval field B, thus speeding up data retrieval. When business field A is not a primary key field, the retrieval condition for database table 1 can be set as: business field A in (the set of business data values ​​of business field A) (and or or) business field B in (the set of business data values ​​of business field B). When deleting related data, the 'and' operator can be used; when retrieving related data, the 'or' operator can be used to prevent accidental deletion. Simultaneously, the retrieval conditions for each database table can be maintained in the data feature model. Furthermore, taking database table 1 and database table 2 as examples, the master-slave relationship between the database tables is described. Assume database table 1 has three business fields: business field A, business field C, and business field D, where business field A is the primary key field. Also assume database table 2 has three business fields: business field A, business field B, and business field E, where business field B is the primary key field. Thus, the intersection of database tables 1 and 2 is determined to be business field A. Since business field A is the primary key field in database table 1 and an index field in database table 2, database table 1 is the primary table, and database table 2 is the secondary table. A primary-secondary relationship can be established between database tables 1 and 2, meaning they have a direct association. This primary-secondary relationship between the database tables is also maintained in the data feature model.

[0114] It should be noted that as business needs expand, different products may add new business fields. Therefore, for products that add business fields, steps a to d need to be re-executed to determine the new set of search fields, the new set of database tables, the new search conditions for each database table, and the new master-slave relationship between the database tables, so as to update the data feature model of the product.

[0115] Step 103: Analyze the source data associated with the business scenario to determine the business data values ​​of the business fields to be cleaned.

[0116] In this embodiment of the invention, the business fields to be cleaned are included in a set of search fields. Specifically, any search field in the set of search fields corresponding to the business scenario is set as a batch processing field. At least one first SQL data query script is constructed using the batch processing field and the source data associated with the business scenario to determine whether the batch processing field is a business field to be cleaned. The business data value of the business field to be cleaned is determined by the at least one first SQL data query script. And / or, based on the source data associated with the business scenario, multiple second SQL data query scripts are constructed to detect whether there are any business fields to be cleaned in the source data that cause data anomalies. The query results determined by the multiple second SQL data query scripts are compared. If there are inconsistent result data in the query results corresponding to the multiple second SQL data query scripts, the database tables associated with the multiple second SQL data query scripts are queried in reverse to determine the business field values ​​corresponding to the inconsistent business fields in the multiple query results. The business field values ​​corresponding to the inconsistent business fields in the multiple query results are determined as the business data values ​​of the business fields to be cleaned. In this way, by analyzing and locating some business fields in the source data associated with the business scenario that cause data anomalies, the business data values ​​of the business fields that need to be cleaned can be accurately determined, thereby providing support for the subsequent accurate construction of SQL data cleaning scripts for automated and precise cleaning of dirty data.

[0117] For example, the source data associated with the business scenario can be analyzed in at least one way to determine the business data values ​​of the business fields to be cleaned. That is, one way is to clean the business data values ​​of the business fields in a batch of data feature models. In test environments where batch processing is frequent every day, dirty data caused by manual data generation or program errors during testing is often required to clean up such data in order to run batch processing smoothly; otherwise, batch processing will be interrupted.

[0118] For example, taking a special vehicle loan product as an example, the SQL data query script set below can be used to query the abnormal data that caused the batch processing to fail. Here, the AGREEMENT_NO field (i.e. the business field to be cleaned) is specified as the batch processing field.

[0119] 1. Locate the debit documents that have been synchronized for the current batch date, and for which there are no corresponding orders.

[0120] select AGREEMENT_NO from TM_LOAN t where BATCH_DATE=(select PROCESS_DATE from TM_SYSTEM_STATUS)and NOT exists(select*from TM_AGREEMENT whereAGREEMENT_NO=t.AGREEMENT_NO);

[0121] 2. Find places with loan receipts but no order details.

[0122] select AGREEMENT_NO from TM_LOAN t where NOT exists(select*from TM_AGREEMENT where AGREEMENT_NO=t.AGREEMENT_NO);

[0123] 3. Search for orders without loan receipts, only checking orders after the loan has been disbursed.

[0124] select AGREEMENT_NO from TM_AGREEMENT t where AGREEMENT_STATUS in('S04','S06','S11')and NOT EXISTS(select*from TM_LOAN where AGREEMENT_NO=t.AGREEMENT_NO);

[0125] 4. Look for records of funds being tied up, but no orders.

[0126] select AGREEMENT_NO from TM_AGREEMENT_FUND TAF where FEE_STATUS in('CHARGING','CHARGE_FAIL')and NOT EXISTS(select*from TM_AGREEMENT TA whereAGREEMENT_NO=TAF.AGREEMENT_NO);

[0127] The above-mentioned batch interruptions are all related to orders. Therefore, the corresponding order number AGREEMENT_NO can be obtained by querying the reasons for the batch interruptions. If there is no retrieval field for the data feature model corresponding to the order number AGREEMENT_NO, the above-mentioned steps of establishing a data feature model are triggered, and a data feature model corresponding to the AGREEMENT_NO field is established. Then, an SQL data cleanup script is constructed using the order number and the corresponding data feature model to clean up the relevant business data in the database table set associated with the order number.

[0128] Another approach is to establish a Task Scheduling Platform (TSP) in the test environment to ensure data integrity and consistency and assist in test quality. However, encountering dirty data can lead to numerous false alarms, increasing the manual analysis time and manpower costs for testers. Therefore, the anomaly level detected by the anomaly monitoring platform determines whether automatic cleanup is necessary. For anomalous business fields with low anomaly levels, the corresponding anomalous business data is retrieved from multiple database tables within the data feature model corresponding to that anomalous business field, assisting testers in analyzing the cause of the anomaly. Currently, TSP anomaly monitoring uses SQL for checks, allowing variable passing between SQL statements. This TSP anomaly monitoring is mainly divided into two categories: comparing single result values ​​and comparing multiple result sets. The SQL parsing uses the SQLparse third-party plugin.

[0129] 1. Querying a single result value comparison class: (e.g., Consumer Finance - Account without PowerDesign Code, Credit Limit Cannot Expiry).

[0130] Configured sql: select count(*)from TM_CUST_LIMIT_O ta where EXPIRE_DATE<(select BUSINESS_DATE from TM_SYSTEM_STATUS tss)and CUST_LIMIT_ID in(selectCUST_LIMIT_ID from TM_ACCOUNT ta where LAST_POST_DATE=(select PROCESS_DATEfrom TM_SYSTEM_STATUS tss2)and BLOCK_CODE not LIKE'%P%'and BLOCK_CODE notlike'%d%'or BLOCK_CODE is null);

[0131] Monitoring: If the query result is greater than 0, it indicates that the monitoring data is abnormal.

[0132] Here, the SQL needs to be parsed (corresponding to the result table), and `count(*)` needs to be replaced with a business field or several business fields in the database table (for example, replacing `count(*)` with a business field in the `TM_CUST_LIMIT_O` table). This is to determine whether the business field or several business fields belong to the business fields that cause data anomalies, and to determine whether the business field that causes data anomalies exists in the retrieval field set in the corresponding data feature model. If it does not exist, the above steps of establishing the data feature model are triggered, and the data feature model corresponding to the business field is established. If it exists, based on the business data value of the business field that causes data anomalies (i.e. the business field to be cleaned) queried above, and in combination with the data feature model, an SQL data cleanup script is constructed to clean up the relevant business data in the database table set associated with the business data value of the business field that causes data anomalies.

[0133] 2. Comparison of multiple result sets: (e.g., CPS identity verification and JPS identity verification check).

[0134] sql1: select IDENTIFY_TYPE,IDENTIFY_STATUS from rcjpsdb.TM_CUST_BUSINESS_IDENTIFY_INFO;

[0135] sql2: select IDENTIFY_TYPE,IDENTIFY_STATUS from cpsdb.TM_CUST_BUSI_IDENTIFY_INFO;

[0136] Monitoring: Compare the result set returned by SQL1 with the result set returned by SQL2 to see if they are consistent.

[0137] Here, based on the inconsistent abnormal data in the monitoring results, a reverse query can be performed to retrieve the business fields and field values ​​associated with the abnormal data in the corresponding database tables (rcjpsdb.TM_CUST_BUSINESS_IDENTIFY_INFO and cpsdb.TM_CUST_BUSI_IDENTIFY_INFO). If the corresponding business field exists in the data feature model, then based on the business field corresponding to the abnormal data and in combination with the corresponding data feature model, an SQL data cleanup script is constructed to clean up the relevant business data in the database table set associated with the business data value of the abnormal data's business field (i.e., the business field to be cleaned). If it does not exist, the above steps of establishing the data feature model are triggered, and the data feature model corresponding to the business field is established.

[0138] In addition, another approach is to apply this method in actual production. However, when defects occur in production, the resulting abnormal data is generally not easy to detect. It is necessary to find all the transaction history corresponding to the data. In this case, the established data feature model can be used to retrieve the data information of all database tables and the relationship between each database table to assist in the analysis and location of the defect, thereby identifying the business fields that need to be cleaned up.

[0139] Step 104: Based on the set of search fields and the set of database tables, construct a structured query language (SQL) data cleaning script corresponding to the business data value of the business field to be cleaned, and use the SQL data cleaning script to clean the relevant business data in the set of database tables associated with the business data value of the business field to be cleaned.

[0140] In this embodiment of the invention, based on the set of search fields, the field values ​​of each search field related to the business data value of the business field to be cleaned are retrieved from the set of database tables. Then, based on the field values ​​of each search field and the database table where each search field resides, an SQL data cleanup script corresponding to the business data value of the business field to be cleaned is constructed. Specifically, according to the master-slave relationship between the database tables, the field values ​​of each search field related to the business data value of the business field to be cleaned are retrieved sequentially. For any database table in the set of database tables, the search fields in the database table are used as search conditions for the database table. Then, based on the search conditions of each database table, and using the field values ​​of each search field and the database table where each search field resides, the SQL data cleanup script corresponding to the business data value of the business field to be cleaned can be accurately constructed.

[0141] Among them, combined Figure 4 , Figure 4This invention provides a flowchart illustrating the process of constructing an SQL data cleanup script, describing the implementation of such a script for automatically cleaning up dirty data.

[0142] Step a: Based on the master-slave relationship between the database tables in the database table set, sequentially query the field values ​​of each search field that are related to the business data value of the business field to be cleaned.

[0143] Step b: Based on the field values ​​of each search field, the search conditions of each database table, and the database table where each search field is located, construct the SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned.

[0144] Step c: Execute the deletion command for relevant business data in the corresponding database tables according to the constructed SQL data cleanup script.

[0145] For example, let's describe several core tables of a financial loan product, such as the customer table TM_CUSTOMER, the account table TM_ACCOUNT, the loan agreement table TM_LOAN, and the repayment plan table TM_SCHEDULE. Specifically, for the customer table, the query is: `SELECT CUST_ID, ID_NO, ... FROM TM_CUSTOMER`, where `CUST_ID` (customer ID) is the primary key field. For the account table, the query is: `SELECT CARD_NO, CUST_ID, ... FROM TM_ACCOUNT`, where `CARD_NO` (card number) is the primary key field. For the loan agreement table, the query is: `SELECT LOAN_ID, CARD_NO, ... FROM TM_LOAN`, where `LOAN_ID` (loan agreement ID) is the primary key field. For the repayment plan table, the query is: `SELECT SCHEDULE_ID, LOAN_ID, CARD_NO, ... FROM TM_SCHEDULE`, where `SCHEDULE_ID` (repayment plan ID) is the primary key field.

[0146] When testing this financial loan product, test anomalies are often caused by business data values ​​corresponding to certain IOU IDs or repayment plan IDs. Therefore, it's necessary to clean up the business data in the database tables associated with these IOU IDs or repayment plan IDs as dirty data. Thus, all related business data can be cleaned based on the IOU ID or repayment plan ID. The following describes the implementation process of constructing an SQL data cleanup script to clean up the relevant business data in the database table set associated with the IOU ID, using the IOU ID as an example.

[0147] Step a: Based on LOAN_ID, the set of search fields (CUST_ID, ID_NO, CARD_NO, LOAN_ID, SCHEDULE_ID) and the master-slave relationships between the corresponding database tables can be retrieved from the corresponding data feature model. Based on the master-slave relationships between the database tables, the business data value of CARD_NO is obtained from the loan table TM_LOAN based on the business data value of LOAN_ID, the business data value of CUST_ID is obtained from the account table TM_ACCOUNT based on the business data value of CARD_NO, the business data value of ID_NO is obtained from the customer table TM_CUSTOMER based on the business data value of CUST_ID, and the business data value of SCHEDULE_ID is obtained from the repayment plan table TM_SCHEDULE based on the business data value of LOAN_ID. Finally, the set of these search field values ​​is obtained.

[0148] Step b: Based on the search conditions and sets of search field values ​​of the core tables of the financial loan product, and the core tables of the financial loan product, construct an SQL data cleanup script to clean up the relevant business data in the database table set associated with the loan ID.

[0149] Step c: Submit the constructed SQL data cleanup script to the cleanup service platform for unified transaction processing.

[0150] The above embodiments demonstrate that by using any business field associated with a business scenario as a data attribute field, a set of retrieval fields can be constructed. By combining this set of retrieval fields with a set of database tables and the business fields to be cleaned, the scope of data cleanup can be determined. This facilitates automated and precise cleanup of dirty data in the database tables involved in the business scenario. Specifically, for any business scenario, data attribute fields are determined from the business fields within that scenario. Using these data attribute fields, a set of retrieval fields and a set of database tables matching the retrieval field set are determined. Since the distinguishability of each business field in each database table is calculated to determine the strong correlation between each business field and the data attribute fields, some business fields with weak correlation can be excluded. This results in a set of highly correlated retrieval fields, effectively ensuring more targeted cleanup of the corresponding dirty data. Furthermore, by analyzing and locating the business fields in the source data associated with the business scenario that cause data anomalies, the business data values ​​of the business fields to be cleaned that cause the anomalies can be accurately determined. Then, based on the set of search fields and the set of database tables, an accurate SQL data cleanup script can be constructed corresponding to the business data values ​​of the business fields to be cleaned. This SQL data cleanup script can then be used to automatically and precisely clean the relevant business data in the set of database tables associated with the business data values ​​of the business fields to be cleaned. In this way, the solution, through the constructed SQL data cleanup script, can achieve automated and precise cleanup of dirty data, thereby ensuring, to the greatest extent possible, that there is no dirty data during testing of business scenarios, effectively reducing the time and manpower costs spent by testers on cleaning up dirty data.

[0151] Based on the same technological concept Figure 5 An exemplary embodiment of the present invention provides a data cleaning apparatus that can execute the process of a data cleaning method.

[0152] like Figure 5 As shown, the device includes:

[0153] The determining unit 501 is used to determine the data attribute fields from the various business fields of the business scenario;

[0154] Processing unit 502 is configured to determine a set of search fields from each database table using the data attribute fields, and to determine a set of database tables that match the set of search fields; wherein, any search field in the set of search fields has a distinguishability in the database table where the search field is located that meets a set requirement; the distinguishability is the percentage of different field values ​​of the search field in the database table where the search field is located; analyze the source data associated with the business scenario to determine the business data values ​​of the business fields to be cleaned; the business fields to be cleaned are included in the set of search fields; construct a structured query language (SQL) data cleanup script corresponding to the business data values ​​of the business fields to be cleaned based on the set of search fields and the set of database tables, and clean the relevant business data in the set of database tables associated with the business data values ​​of the business fields to be cleaned using the SQL data cleanup script.

[0155] Optionally, the processing unit 502 is specifically used for:

[0156] Identify the first database tables that have the data attribute fields from each database table;

[0157] For any first database table, the primary key field in the first database table is used as the search field in the search field set, and the index field in the first database table that meets the set discrimination requirement is used as the search field in the search field set.

[0158] Using the primary key field as the data attribute field, the retrieval field is further determined from each database table;

[0159] The index fields in the first database table whose discrimination does not meet the set requirements are used as the data attribute fields, and the retrieval fields are determined from each of the database tables.

[0160] Optionally, the processing unit 502 is specifically used for:

[0161] Based on the set of search fields, retrieve the field values ​​of each search field that are related to the business data value of the business field to be cleaned from the set of database tables;

[0162] Based on the field values ​​of each search field and the database table where each search field is located, construct an SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned.

[0163] Optionally, the processing unit 502 is further configured to:

[0164] Determine the master-slave relationship among the database tables in the database table set;

[0165] For any database table in the database table set, the search field in the database table is used as the search condition for the database table;

[0166] The processing unit 502 is specifically used for:

[0167] Based on the master-slave relationship between the database tables, the field values ​​of each retrieval field related to the business data value of the business field to be cleaned are retrieved in sequence;

[0168] The processing unit 502 is specifically used for:

[0169] Based on the search conditions of each database table, and using the field values ​​of each search field and the database table where each search field is located, an SQL data cleanup script is constructed corresponding to the business data values ​​of the business fields to be cleaned.

[0170] Optionally, the processing unit 502 is specifically used for:

[0171] Determine the intersection field between the first database table and the second database table in the database table set; the first database table and the second database table are any two database tables in the database table set;

[0172] If the intersection field is a primary key field in the first database table and an index field in the second database table, then a master-slave relationship is established between the first database table and the second database table; the first database table is the master table; and the second database table is the slave table.

[0173] If the intersection field is a primary key field in the second database table and an index field in the first database table, then a master-slave relationship is established between the second database table and the first database table; the second database table is the master table; and the first database table is the slave table.

[0174] Optionally, the processing unit 502 is specifically used for:

[0175] For each business field in any database table, perform deduplication on the corresponding business data values ​​in the database table where the business field is located, and count the first number of at least one deduplicated business data value.

[0176] The second count is calculated based on the total number of business data entries for the business field in the corresponding database table.

[0177] The distinguishability of the business field is determined based on the first quantity and the second quantity;

[0178] If the distinguishability of the business field meets the set requirements, then the business field is determined as the retrieval field.

[0179] Optionally, the processing unit 502 is specifically used for:

[0180] Set any one of the search fields in the set of search fields corresponding to the business scenario as the batch processing field, and construct at least one first SQL data query script using the batch processing field and the source data associated with the business scenario to determine whether the batch processing field is a business field to be cleaned; determine the business data value of the business field to be cleaned using the at least one first SQL data query script; and / or,

[0181] Based on the source data associated with the business scenario, multiple second SQL data query scripts are constructed to detect whether there are any business fields in the source data that need to be cleaned and cause data anomalies. The query results determined by the multiple second SQL data query scripts are compared. If there are inconsistent result data in the query results corresponding to the multiple second SQL data query scripts, the database tables associated with the multiple second SQL data query scripts are queried in reverse to determine the values ​​of each business field corresponding to the inconsistent business fields in the multiple query results. The values ​​of each business field corresponding to the inconsistent business fields in the multiple query results are determined as the business data values ​​of the business fields to be cleaned.

[0182] Based on the same technical concept, embodiments of the present invention also provide a computing device, such as... Figure 6 As shown, it includes at least one processor 601 and a memory 602 connected to at least one processor. In this embodiment of the invention, the specific connection medium between the processor 601 and the memory 602 is not limited. Figure 6 Taking the connection between the processor 601 and the memory 602 via a bus as an example, the bus can be divided into address bus, data bus, control bus, etc.

[0183] In this embodiment of the invention, the memory 602 stores instructions that can be executed by at least one processor 601. By executing the instructions stored in the memory 602, at least one processor 601 can perform the steps included in the aforementioned data cleaning method.

[0184] The processor 601 is the control center of the computing device. It can connect to various parts of the computing device using various interfaces and lines, and performs data processing by running or executing instructions stored in the memory 602 and calling data stored in the memory 602. Optionally, the processor 601 may include one or more processing units. The processor 601 may integrate an application processor and a modem processor. The application processor mainly handles the operating system, user interface, and applications, while the modem processor mainly handles issuing instructions. It is understood that the modem processor may not be integrated into the processor 601. In some embodiments, the processor 601 and the memory 602 may be implemented on the same chip; in some embodiments, they may also be implemented on separate chips.

[0185] Processor 601 can be a general-purpose processor, such as a central processing unit (CPU), digital signal processor, application-specific integrated circuit (ASIC), field-programmable gate array (FPGA), or other programmable logic device, discrete gate or transistor logic device, or discrete hardware component, capable of implementing or executing the methods, steps, and logic block diagrams disclosed in the embodiments of this invention. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in the data cleaning method embodiments can be directly manifested as being executed by a hardware processor, or executed by a combination of hardware and software modules within the processor.

[0186] Memory 602, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules. Memory 602 may include at least one type of storage medium, such as flash memory, hard disk, multimedia card, card-type memory, random access memory (RAM), static random access memory (SRAM), programmable read-only memory (PROM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), magnetic storage, magnetic disk, optical disk, etc. Memory 602 can be any other medium capable of carrying or storing desired program code in the form of instructions or data structures that can be accessed by a computer, but is not limited thereto. In embodiments of the present invention, memory 602 can also be a circuit or any other device capable of implementing storage functions for storing program instructions and / or data.

[0187] Based on the same technical concept, embodiments of the present invention also provide a computer-readable storage medium storing a computer program executable by a computing device, which, when run on the computing device, causes the computing device to perform the steps of the above-described data cleaning method.

[0188] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0189] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing device to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing device, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxesFigure 1 A device that provides the functions specified in one or more boxes.

[0190] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0191] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0192] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention.

[0193] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the claims of this application and their equivalents, this invention also intends to include these modifications and variations.

Claims

1. A data cleaning method, characterized in that, include: Identify the data attribute fields from the various business fields in the business scenario; Using the data attribute fields, a set of search fields is determined from each database table, and a set of database tables that match the set of search fields is determined; wherein, any search field in the set of search fields has a distinguishability in the database table where the search field is located that meets a set requirement; the distinguishability is the percentage of the number of search fields with different field values ​​in the database table where the search field is located. The source data associated with the business scenario is analyzed to determine the business data values ​​of the business fields to be cleaned; the business fields to be cleaned are included in the set of search fields. Based on the set of search fields and the set of database tables, a structured query language (SQL) data cleaning script is constructed corresponding to the business data value of the business field to be cleaned, and the relevant business data in the set of database tables associated with the business data value of the business field to be cleaned is cleaned using the SQL data cleaning script; The analysis of the source data associated with the business scenario to determine the business data values ​​of the business fields to be cleaned includes: Set any one of the search fields in the set of search fields corresponding to the business scenario as the batch processing field, and construct at least one first SQL data query script using the batch processing field and the source data associated with the business scenario to determine whether the batch processing field is a business field to be cleaned; determine the business data value of the business field to be cleaned using the at least one first SQL data query script; and / or, Based on the source data associated with the business scenario, multiple second SQL data query scripts are constructed to detect whether there are any business fields in the source data that need to be cleaned and cause data anomalies. The query results determined by the multiple second SQL data query scripts are compared. If there are inconsistent result data in the query results corresponding to the multiple second SQL data query scripts, the database tables associated with the multiple second SQL data query scripts are queried in reverse to determine the values ​​of each business field corresponding to the inconsistent business fields in the multiple query results. The values ​​of each business field corresponding to the inconsistent business fields in the multiple query results are determined as the business data values ​​of the business fields to be cleaned.

2. The method as described in claim 1, characterized in that, The step of determining the set of retrieval fields from each database table using the data attribute fields includes: Identify the first database tables that have the data attribute fields from each database table; For any first database table, the primary key field in the first database table is used as the search field in the search field set, and the index field in the first database table that meets the set discrimination requirement is used as the search field in the search field set. Using the primary key field as the data attribute field, the retrieval field is further determined from each database table; The index fields in the first database table whose discrimination does not meet the set requirements are used as the data attribute fields, and the retrieval fields are determined from each of the database tables.

3. The method as described in claim 1, characterized in that, The step of constructing a Structured Query Language (SQL) data cleanup script corresponding to the business data values ​​of the business fields to be cleaned, based on the retrieval field set and the database table set, includes: Based on the set of search fields, retrieve the field values ​​of each search field that are related to the business data value of the business field to be cleaned from the set of database tables; Based on the field values ​​of each search field and the database table where each search field is located, construct an SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned.

4. The method as described in claim 3, characterized in that, Also includes: Determine the master-slave relationship among the database tables in the database table set; For any database table in the database table set, the search field in the database table is used as the search condition for the database table; Based on the set of search fields, the values ​​of each search field related to the business data value of the business field to be cleaned are retrieved from the set of database tables, including: Based on the master-slave relationship between the database tables, the field values ​​of each retrieval field related to the business data value of the business field to be cleaned are retrieved in sequence; Based on the field values ​​of each search field and the database table where each search field is located, construct an SQL data cleanup script corresponding to the business data values ​​of the business fields to be cleaned, including: Based on the search conditions of each database table, and using the field values ​​of each search field and the database table where each search field is located, an SQL data cleanup script is constructed corresponding to the business data values ​​of the business fields to be cleaned.

5. The method as described in claim 4, characterized in that, Determining the master-slave relationship between the database tables in the database table set includes: Determine the intersection field between the first database table and the second database table in the database table set; the first database table and the second database table are any two database tables in the database table set; If the intersection field is a primary key field in the first database table and an index field in the second database table, then a master-slave relationship is established between the first database table and the second database table; the first database table is the master table; and the second database table is the slave table. If the intersection field is a primary key field in the second database table and an index field in the first database table, then a master-slave relationship is established between the second database table and the first database table; the second database table is the master table; and the first database table is the slave table.

6. The method as described in claim 1, characterized in that, Each search field in the set of search fields is determined in the following manner: For each business field in any database table, perform deduplication on the corresponding business data values ​​in the database table where the business field is located, and count the first number of at least one deduplicated business data value. The second count is calculated based on the total number of business data entries for the business field in the corresponding database table. The distinguishability of the business field is determined based on the first quantity and the second quantity; If the distinguishability of the business field meets the set requirements, then the business field is determined as the retrieval field.

7. A data cleaning device, characterized in that, include: The determination unit is used to identify data attribute fields from various business fields in a business scenario. The processing unit is configured to determine a set of retrieval fields from various database tables based on the data attribute fields, and to determine a set of database tables that match the set of retrieval fields; wherein, any retrieval field in the set of retrieval fields has a distinguishability in the database table where the retrieval field is located that meets a set requirement; the distinguishability is the percentage of different field values ​​of the retrieval field in the database table where the retrieval field is located; analyze the source data associated with the business scenario to determine the business data values ​​of the business fields to be cleaned; the business fields to be cleaned are included in the set of retrieval fields; construct a structured query language (SQL) data cleanup script corresponding to the business data values ​​of the business fields to be cleaned based on the set of retrieval fields and the set of database tables, and clean the relevant business data in the set of database tables associated with the business data values ​​of the business fields to be cleaned using the SQL data cleanup script; The processing unit is specifically used for: Set any one of the search fields in the set of search fields corresponding to the business scenario as the batch processing field, and construct at least one first SQL data query script using the batch processing field and the source data associated with the business scenario to determine whether the batch processing field is a business field to be cleaned; determine the business data value of the business field to be cleaned using the at least one first SQL data query script; and / or, Based on the source data associated with the business scenario, multiple second SQL data query scripts are constructed to detect whether there are any business fields in the source data that need to be cleaned and cause data anomalies. The query results determined by the multiple second SQL data query scripts are compared. If there are inconsistent result data in the query results corresponding to the multiple second SQL data query scripts, the database tables associated with the multiple second SQL data query scripts are queried in reverse to determine the values ​​of each business field corresponding to the inconsistent business fields in the multiple query results. The values ​​of each business field corresponding to the inconsistent business fields in the multiple query results are determined as the business data values ​​of the business fields to be cleaned.

8. A computing device, characterized in that, The method includes at least one processor and at least one memory, wherein the memory stores a computer program that, when executed by the processor, causes the processor to perform the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, It stores a computer program executable by a computing device, which, when run on the computing device, causes the computing device to perform the method described in any one of claims 1 to 6.

Images