Method, system, program, and data structure for cleaning a database table

• US 7,249,151 B2
• Filed: 07/26/2005
• Issued: 07/24/2007
• Est. Priority Date: 09/21/1999
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Mandel, Richard Henry III, Cesare, Mark Anthony, Jerves, Julie Ann, Christopher, Tom Robert
• Primary Examiner(s): PHAM, HUNG Q
• Application Number:
  US11/190,250
• Publication Number:
  US 20050262121A1
• Time in Patent Office: 728 Days
• Field of Search: 707/4, 707/6, 707/101, 707/102, 707/104.1, 707/200
• US Class Current: 707/694
• CPC Class Codes: G06F 16/215 Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Disclosed is a method, system, program, and data structure for performing a clean operation on an input table. The input table to clean is indicated in an input data table name. At least one rule definition is processed to clean the input table. Each rule definition indicates a find criteria, a replacement value, and an input data column in the input table. For each rule definition, the input data column is searched for any fields that match the find criteria. The replacement value for the particular rule definition is inserted in the fields in the input data column that match the find criteria. Subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column during previously applied rule definitions.

Claims

1. A computer implemented method for performing a clean operation on an input table having an input table name, comprising:

receiving user input identifying the input table name and at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, an input data column in the input table and an optional output table where cleaned data from the input table is to be placed, wherein each rule definition is associated with a rule table including multiple find criteria, a corresponding replacement value for each find criteria, and a sort-key column name for ordering the multiple find criteria;

in response to the user input, generating an Application Program Interface (API) call that includes the identified input table name and the at least one rule definition; and

for each rule definition included in the API call,

searching the input data column for any fields that match the find criteria;

determining whether the rule definition included in the API call specifies an output table;

in response to determining that the rule definition in the API call specifies an output table, inserting the replacement value in the specified output table; and

in response to determining that the rule definition does not specify an output table, directly inserting the replacement value in the fields in the input data column that match the find criteria, wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions.

2. The method of claim 1, wherein the input data column for a first and second applied rule definitions is the same input data column, wherein the replacement value for the first rule definition is inserted into at least one field in the input data column, and wherein the find criteria of the second rule definition is applied to the replacement value inserted in the input data column.

3. The method of claim 1, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

4. The method of claim 1, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, wherein searching comprises searching for any fields that have values within the upper and lower bounds.

5. The method of claim 4, wherein the at least one rule definition including find criteria having upper and lower bounds includes multiple find criteria and a corresponding replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

6. The method of claim 4, wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.

7. The method of claim 1, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound and wherein the replacement value is an upper replacement value and further comprising a lower replacement value, wherein searching comprises searching for any fields that have values within the upper and lower bounds and wherein inserting comprises inserting the upper replacement value if the field has a value greater than the upper bound and inserting the lower replacement value if the field has a value less than the lower bound.

8. The method of claim 7, wherein the at least one rule definition including find criteria having upper and lower bounds includes multiple find criteria and a corresponding upper and lower replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

9. The method of claim 1, wherein the rule definitions include a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising removing any row including a field matching the search criteria from the input table when the row clean flag is set.

10. A computer implemented system for performing a clean operation on an input table having an input table name, comprising:

a processor;

a clean transform program, wherein the clean transform program further;

receiving user input identifying the input table name and at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, an input data column in the input table and an optional output table where cleaned data from the input table is to be placed, wherein each rule definition is associated with a rule table including multiple find criteria, a corresponding replacement value for each find criteria, and a sort-key column name for ordering the multiple find criteria;

in response to the user input, generating an Application Program Interface (API) call that includes the received input table name and the at least one rule definition; and

for each rule definition included in the API call,

searching the input data column for any fields that match the find criteria;

determining whether the rule definition included in the API call specifies an output table;

in response to determining that the rule definition in the API call specifies an output table, inserting the replacement value in the specified output table;

in response to determining that the rule definition does not specify an output table, directly inserting the replacement value in the fields in the input data column that match the find criteria, wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions.

11. The system of claim 10, wherein the input data column for a first and second applied rule definitions is the same input data column, the clean transform program further:

inserting the replacement value for the first rule definition into at least one field in the input data column; and

applying the find criteria of the second rule definition to the replacement value inserted in the input data column.

12. The system of claim 10, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

13. The system of claim 10, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, wherein searching comprises searching for any fields that have values within the upper and lower bounds.

14. The system of claim 13, wherein the at least one rule definition including find criteria having upper and lower bounds includes multiple find criteria and a corresponding replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein the means for inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

15. The system of claim 13, wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.

16. The system of claim 10, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound and wherein the replacement value is an upper replacement value and further comprising a lower replacement value, wherein searching comprises searching for any fields that have values within the upper and lower bounds and wherein inserting comprises inserting the upper replacement value if the field has a value greater than the upper bound and inserting the lower replacement value if the field has a value less than the lower bound.

17. The system of claim 16, wherein the at least one rule definition including find criteria having upper and lower bounds includes multiple find criteria and a corresponding upper and lower replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

18. The system of claim 10, wherein the rule definitions include a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising removing any row including a field matching the search criteria from the input table when the row clean flag is set.

19. An article of manufacture for performing a clean operation on an input table in a database having an input table name, the article of manufacture comprising computer readable media including at least one computer program embedded therein that causes a computer to perform:

receiving user input identifying the input table name and at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, an input data column in the input table and an optional output table where cleaned data from the input table is to be placed, wherein each rule definition is associated with a rule table including multiple find criteria, a corresponding replacement value for each find criteria, and a sort-key column name for ordering the multiple find criteria;

in response to the user input, generating an Application Program Interface (API) call that includes the identified input table name and the at least one rule definition; and

for each rule definition included in the API call,

searching the input data column for any fields that match the find criteria;

determining whether the rule definition included in the API call specifies an output table;

in response to determining that the rule definition in the API call specifies an output table, inserting the replacement value in the spec fled output table; and

in response to determining that the rule definition does not specify an output table, directly inserting the replacement value in the fields in the input data column that match the find criteria, wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions.

20. The article of manufacture of claim 19, wherein the input data column for a first and second applied rule definitions is the same input data column, wherein the replacement value for the first rule definition is inserted into at least one field in the input data column, and wherein the find criteria of the second rule definition is applied to the replacement value inserted in the input data column.

21. The article of manufacture of claim 19, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

22. The article of manufacture of claim 19, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, wherein searching comprises searching for any fields that have values within the upper and lower bounds.

23. The article of manufacture of claim 22, wherein the at least one rule definition including find criteria having upper and lower bounds includes multiple find criteria and a corresponding replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

24. The article of manufacture of claim 22, wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.

25. The article of manufacture of claim 19, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound and wherein the replacement value is an upper replacement value and further comprising a lower replacement value, wherein searching comprises searching for any fields that have values within the upper and lower bounds and wherein inserting comprises inserting the upper replacement value if the field has a value greater than the upper bound and inserting the lower replacement value if the field has a value less than the lower bound.

26. The article of manufacture of claim 25, wherein the at least one rule definition including find criteria having upper and lower bounds includes multiple find criteria and a corresponding upper and lower replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of:

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content.

27. The article of manufacture of claim 19, wherein the rule definitions include a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising removing any row including a field matching the search criteria from the input table when the row clean flag is set.