Method, system, program, and data structure for cleaning a database table using a look-up table

• US 6,965,888 B1
• Filed: 09/21/1999
• Issued: 11/15/2005
• 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
• Primary Examiner(s): Alam, Shahid
• Assistant Examiner(s): FLEURANTIN, JEAN B
• Application Number:
  US09/401,006
• Time in Patent Office: 2,247 Days
• Field of Search: 707 1- 10, 707200-206, 707100-1041, 704/9, 706/46, 706/47, 706/50, 706/52, 706/54, 706/56, 706/57, 706/60
• US Class Current: 707/708
• 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/99931 Database or file accessing

Abstract

Disclosed is a method, system, program, and data structure for performing a clean operation on an input table. An input data table name indicates the input table. A first column in a table including a plurality of entries and at least one rule definition are processed. Each rule definition indicates a look-up column including a plurality of entries and a clean operation. A determination is made, for each record in the input table column, whether the record matches one entry in the look-up column. If so, the clean operation indicated in the rule definition is performed on each record that matches one entry in the look-up column.

Claims

1. A method for performing a clean operation on an input table having an input data table name and at least one input table column including a plurality of records, comprising:

receiving at least one rule definition, wherein the at least one rule definition indicates a look-up column including a plurality of entries and a clean operation, wherein the at least one rule definition is associated with at least one rule table column including a find criteria and replacement value;

for each record in the input table column, determining whether the record matches one entry in the at least one look-up column; and

performing the clean operation indicated in the rule definition on each record that matches one entry in the at least one look-up column by processing the rule table column for the at least one rule definition, wherein the rule table columns include the find criteria and replacement value for the rule definition.

2. The method of claim 1, further comprising writing information on each record that does not match one entry in the look-up column into a log record.

3. The method of claim 2, further comprising writing each record that does not match one entry in the look-up column to the log record.

4. The method of claim 1, wherein each clean operation indicates a match condition and replacement operation, and wherein the clean operation performs the replacement operation on the record if the record satisfies the match condition.

5. The method of claim 1, wherein the input table is included in a database oil a server, wherein the rule definition is received from a client-side stored procedure program, and wherein a server-side stored procedure executing in the server receives the rule definition from the client-side stored procedure, determines whether the records in the input table match one entry in the look-up column, and performs the clean operation on the database in the server.

6. The method of claim 1, wherein the rule definition further indicates a rule table including a plurality of different clean operations and at least one parameter indicating at least one rule from the rule table to apply, wherein the rules in the rule table define clean operations to perform, wherein performing the clean operation comprises applying the at least one rule from the rule table indicated by the parameter to each record that matches one entry in the look-up column.

7. The method of claim 6, wherein the rule table includes columns of different rule definitions, and wherein the parameter indicates at least one column in the rule table providing the at least one rule to apply to each record.

8. A method for performing a clean operation on an input table having an input data table name and at least one input table column including a plurality of records, comprising:

receiving at least one rule definition, wherein each rule definition indicates a look-up column including a plurality of entries and a clean operation and a find criteria and a replacement value;

for each record in the input table column, determining whether the record matches one entry in the look-up column; and

performing the clean operation indicated in the rule definition on each record that matches one entry in the look-up column by;

(i) determining whether each record that matches one entry in the look-up table matches the find criteria; and

(ii) inserting the replacement value into each record in the input data column that matches the find criteria, wherein subsequent applications of additional rule definitions applied to the same record operate on replacement values inserted in the record during previously applied rule definitions.

9. The method of claim 8, 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.

10. The method of claim 8, wherein the rule definition comprises a type of rule that is a member of the set of rules consisting of:

find and replace, discretization, and numeric clip, wherein at least two rule definitions are comprised of different rule types.

11. The method of claim 8, 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.

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

13. A method for performing a clean operation on an input table having an input data table name and at least one input table column including a plurality of records composing:

receiving at least one rule definition, wherein each rule definition indicates a look-up column including a plurality of entries and a clean operation, wherein each rule definition is associated with one rule table including find criteria and replacement value

for each record in the input table column, determining whether the record matches one entry in the look-up column; and

performing the clean operation indicated in the rule definition on each record that matches one entry in the look-un column processing a rule table column for each rule definition, wherein the columns in the rule table include the find criteria and replacement value for the rule definition.

14. A system for performing a clean operation on an input table having an input data table name and at least one input table column including a plurality of entries, comprising:

means for receiving at least one rule definition, wherein at least one rule definition indicates a look-up column including a plurality of entries and a clean operation, wherein the at least one rule definition is associated with at least one rule table column including a find criteria and replacement value;

means for determining, for each record in the input table column, whether the record matches one entry in the look-up column; and

means for performing the clean operation indicated in the at least one rule definition on each record that matches one entry in the look-up column by processing the rule table column for the at least one rule definition, wherein the rule table columns include the find criteria and replacement value for the rule definition.

15. The system of claim 14, further comprising means for writing information on each record that does not match one entry in the look-up column into a log record.

16. The system of claim 15, further comprising means for writing each record that does not match one entry in the look-up column to the log record.

17. The system of claim 14, wherein each clean operation indicates a match condition and replacement operation, and wherein the means for performing the clean operation performs the replacement operation on the record if the record satisfies the match condition.

18. The system of claim 14, further comprising:

a server; and

a database in the server, wherein the input table is included in the database on a server, wherein the rule definition is received from a client-side stored procedure program, and wherein the means for receiving the rule definition from the client-side stored procedure, determining whether the records in the input table match one entry in the look-up column, and performing the clean operation on the database in the server comprise a server-side stored procedure executing in the server.

19. The system of claim 14, wherein the rule definition further indicates a rule table including a plurality of different clean operations and at least one parameter indicating at least one rule from the rule table to apply, wherein the rules in the rule table define clean operations to perform, wherein the means for performing the clean operation applies the at least one rule from the rule table indicated by the parameter to each record that matches one entry in the look-up column.

20. The system of claim 19, wherein the rule table includes columns of different rule definitions, and wherein the parameter indicates at least one column in the rule table providing the at least one rule to apply to each record.

21. A system for performing a clean operation on an input table having an input data table name and at least one input table column including a plurality of entries, comprising:

means for receiving at least one rule definition, wherein each rule definition indicates a look-up column including a plurality of entries and a clean operation, wherein each rule definition is associated with one rule table including find criteria and replacement value

means for determining, for each record in the input table column, whether the record matches one entry the look-up column; and

means for performing the clean operation indicated in the rule definition on each record that matches one entry in the look-up column processing a rule table column for each rule definition, wherein the columns in the rule table include the find criteria and replacement value for the rule definition.

22. A system for performing a clean operation on an input table having an input data table name and at least one input table column including a plurality of entries, comprising:

means for receiving at least one rule definition, wherein each rule definition indicates a look-up column including a plurality of entries and a clean operation and includes a find criteria and a replacement value;

means for determining, for each record in the input table column, whether the record matches one entry in the look-up column; and

means for performing the clean operation indicated in the rule definition on each record that matches one entry in the look-up column by;

(i) determining whether each record that matches one entry in the look-up table matches the find criteria; and

(ii) inserting the replacement value into each record in the input data column that matches the find criteria, wherein subsequent applications of additional rule definitions applied to the same record operate on replacement values inserted in the record during previously applied rule definitions.

23. The system of claim 22, wherein the input data column for a first and second applied rule definitions is the same input data column, further comprising:

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

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

24. The system of claim 22, wherein the rule definition comprises a type of rule that is a member of the set of rules consisting of:

find and replace, discretization, and numeric clip, wherein at least two rule definitions are comprised of different rule types.

25. The system of claim 22, 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.

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

27. An article of manufacture for performing a clean operation on an input table in a database having an input data table name and at least one input table column including a plurality of records, the article of manufacture comprising computer usable media including at least one computer program embedded therein that causes the computer to perform:

receiving at least one rule definition, wherein the at least one rule definition indicates a look-up column including a plurality of entries and a clean operation, wherein the at least one rule definition is associated with at least one rule table column including a find criteria and replacement value;

for each record in the input table column, determining whether the record matches one entry in the look-up column; and

performing the clean operation indicated in the rule definition on each record that matches one entry in the look-up column by processing the rule table column for the at least one rule definition, wherein the rule table columns include the find criteria and replacement value for the rule definition.

28. The article of manufacture of claim 27, further comprising writing information on each record that does not match one entry in the look-up column into a log record.

29. The article of manufacture of claim 28, further comprising writing each record that does not match one entry in the look-up column to the log record.

30. The article of manufacture of claim 27, wherein each clean operation indicates a match condition and replacement operation, and wherein the clean operation performs the replacement operation on the record if the record satisfies the match condition.

31. The article of manufacture of claim 27, wherein the input table is included in a database on a server, wherein the rule definition is received from a client-side stored procedure program, and wherein a server-side stored procedure executing in the server receives the tile definition from the client-side stored procedure, determines whether the records in the input table match one entry in the look-up column, and performs the clean operation on the database in the server.

32. The article of manufacture of claim 27, wherein the rule definition further indicates a rule table including a plurality of different clean operations and at least one parameter indicating at least one rule from the rule table to apply, wherein the rules in the rule table define clean operations to perform, wherein performing the clean operation comprises applying the at least one rule from the rule table indicated by the parameter to each record that matches one entry in the look-up column.

33. The article of manufacture of claim 32, wherein the rule table includes columns of differ rule definitions, and wherein the parameter indicates at least one column in the rule table providing the at least one rule to apply to each record.

34. An article of manufacture for performing a clean operation on an input table in a database having an input data table name and at least one input table column including a plurality of records, the article of manufacture comprising computer usable media including at least one computer program embedded therein that causes the computer to perform:

receiving at least one rule definition, wherein each rule definition indicates a look-up column including a plurality of entries and a clean operation and includes a find criteria and a replacement value;

for each record in the input table column, determining whether the record matches one entry in the look-up column; and

performing the clean operation indicated in the rule definition on each record that matches one entry in the look-up column by;

(i) determining whether each record that matches one entry in the look-up table matches the find criteria; and

(ii) inserting the replacement value into each record in the input data column that matches the find criteria, wherein subsequent applications of additional rule definitions applied to the same record operate on replacement values inserted in the record during previously applied rule definitions.

35. The article of manufacture of claim 34, wherein the input data column for a fist 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.

36. The article of manufacture of claim 34, wherein the rule definition comprises a type of rule that is a member of the set of rules consisting of:

find and replace, discretization, and numeric clip, wherein at least two rule definitions are comprised of different rule types.

37. The article of manufacture of claim 34, 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.

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

39. An article of manufacture for performing a clean operation on input table in a database having an input data table name and at least one input table column including a plurality of records, the article of manufacture comprising computer usable media including at least one computer program embedded therein that causes the computer to perform:

receiving at least one rule definition, wherein each rule definition indicates a look-up column including a plurality of entries and a clean operation, wherein each rule definition is associated with one rule table including find criteria and replacement value

for each record in the input table column, determining whether the record matches one entry in the look-up column; and

performing the clean operation indicated in the rule definition on each record that matches one entry in the look-up column by processing a rule table column for each rule definition, wherein the columns in the rule table include the find criteria and replacement value for the rule definition.