US20060195488A1 - Method, system, program and data structure for cleaning a database table - Google Patents
Method, system, program and data structure for cleaning a database table Download PDFInfo
- Publication number
- US20060195488A1 US20060195488A1 US11/382,889 US38288906A US2006195488A1 US 20060195488 A1 US20060195488 A1 US 20060195488A1 US 38288906 A US38288906 A US 38288906A US 2006195488 A1 US2006195488 A1 US 2006195488A1
- Authority
- US
- United States
- Prior art keywords
- input data
- rule
- rule definition
- find criteria
- criteria
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Abandoned
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99933—Query processing, i.e. searching
- Y10S707/99934—Query formulation, input preparation, or translation
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99933—Query processing, i.e. searching
- Y10S707/99936—Pattern matching access
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99941—Database schema or data structure
- Y10S707/99942—Manipulating data structure, e.g. compression, compaction, compilation
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99941—Database schema or data structure
- Y10S707/99943—Generating database or data structure, e.g. via user interface
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99941—Database schema or data structure
- Y10S707/99944—Object-oriented database structure
- Y10S707/99945—Object-oriented database structure processing
Definitions
- the present invention relates to a method, system, program, and data structure for cleaning a database table and, in particular, for performing clean operations on columns in the database table.
- Data records in a computer database are maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records which comprise the rows.
- a data warehouse is a large scale database including millions or billions of records defining business or other types of transactions or activities. Data warehouses contain a wide variety of data that present a coherent picture of business or organizational conditions over time.
- Various data analysis and mining tools are provided with the data warehouse to allow users to effectively analyze, manage and access large-scale databases to support management decision making. Data mining is the process of extracting valid and previously unknown information from large databases and using it to make crucial business decisions. In many real-world domains such as marketing analysis, financial analysis, fraud detection, etc, information extraction requires the cooperative use of several data mining operations and techniques.
- Transformations vary from conversions of one type of data to another, e.g., converting nominal values into numeric ones so that they can be processed by a neural network, to definition of new attributes, i.e., derived attributes. New attributes are defined either by applying mathematical or logical operators on the values of one or more database attributes.
- the transformed data is stored in a target database where it may then be mined using one or more techniques to extract the desired type of information necessary to make the organizational decisions. Further details of data mining are described in the International Business Machines Corporation (IBM) publication entitled “White Paper: Data Mining Solutions” (IBM Copyright, 1996)
- Data transformation refers to the process of filtering, merging, decoding, and translating source data to create validated data for the data warehouse and data mining tools. For example, a numeric regional code might be replaced with the name of the region. Data transformations and cleansing is used when data is inconsistent or incompatible between sources. In such case, some level of data cleansing is needed to ensure data consistency and accuracy. Some of the current techniques for transforming and cleansing data include the use of an SQL WHERE clause to limit the rows extracted from the source table. Further, formulas and expressions specified in the column definition window and constants and tokens are used to eliminate and modify data.
- Previous versions of IBM Visual Warehouse included programs to allow users to perform numerous functions on the source data. For instance, if one database table has revenue data in U.S. dollars and another data table stores revenue data in foreign currency denominations, then the foreign revenue data must be cleansed before both sets of data can be analyzed together. Transformation operations may be performed using application programs external to the database program that process and transform tables of data records. Further details of data warehousing and data transforms, are described in the IBM publications “Managing Visual Warehouse, Version 3.1,” IBM document no. GC26-8822-01 (IBM Copyright, January, 1998), which is incorporated herein by reference in its entirety.
- preferred embodiments disclose 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.
- 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.
- At least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria.
- each rule definition is associated with one rule table including the find criteria and replacement value.
- a rule table column parameter is provided for each rule definition indicating the columns in the rule table including the find criteria and replacement value for that rule definition.
- two rule definitions may have the same rule table.
- the rule table column parameters indicate different columns in the same rule table including the find criteria and replacement value for each rule definition.
- a separate rule table may include the find criteria and replacement value for different rule definitions.
- a rule definition may include multiple find criteria and a corresponding replacement value for each find criteria.
- the step of searching the input data column comprises applying each of the multiple find criteria to one field until a match occurs or none of the multiple find criteria are found to match the field content.
- the replacement value corresponding to the find criteria is inserted in the field having the matching content.
- the rule definition may define a find and replace rule, a discretization rule or a numeric clip rule.
- Different rule definitions may define different rule types.
- the rule definitions may be communicated from one computer system, such as a client, to a computer system including the input data table, such as a database server.
- the rule definitions are then executed against the input table on the database server including the input tables.
- Preferred embodiments provide a data command structure including one or more rule definitions for performing different operations on the data in an input data table.
- the preferred embodiments provide a command structure that accommodates multiple types of clean operations to be performed on an input data table before the input data table is written to the output table.
- preferred embodiments allow a client to transfer clean commands including to the database server including the database for execution on the database server. This reduces network traffic as the database tables subject to the clean operation do not have to be transferred between the database server and the client constructing the clean commands.
- the rule definitions are maintained in rule tables in the server. This further reduces network traffic as the clean command need only specify the location of rules to apply and does not have to provide tables of rules.
- FIG. 1 illustrates a computing environment in which preferred embodiments are implemented
- FIG. 2 illustrates the parameters used in a transform command to clean input tables in accordance with preferred embodiments of the present invention
- FIGS. 3 a , 3 b , 4 , and 5 illustrate examples of a rule table to clean data in accordance with preferred embodiments of the present invention
- FIGS. 6 a , 6 b , 6 c , 6 d , and 6 e illustrate logic to clean an input data table in accordance with preferred embodiments of the present invention
- FIG. 7 illustrates an example of an input data table
- FIGS. 8 a , 8 b , 8 c , and 8 d illustrate examples of rule table to apply to clean columns in the input data table in FIG. 7 in accordance with preferred embodiments of the present invention.
- FIG. 1 illustrates a computing environment 2 in which preferred embodiments are implemented.
- the environment 2 includes a server 4 and client 6 .
- the server 4 and client 6 would include an operating system, such as MICROSOFT WINDOWS 98 and WINDOWS NT, AIX, OS/390, OS/400, OS/2, and SUN SOLARIS,** and may be comprised of any suitable server and client architecture known in the art.
- the server 4 and client 6 include a database program 8 a and 8 b , wherein 8 a comprises the server 4 side of the database program and 8 b comprises the client 6 side.
- the server 4 and client 6 may communicate via any communication means known in the art, such as a telephone line, dedicated cable or network line, etc, using any protocol known in the art including TCP/IP network (e.g., an Intranet, the Internet), LAN, Ethernet, WAN, System Area Network (SAN), Token Ring, etc.
- TCP/IP network e.g., an Intranet, the Internet
- LAN Local Area Network
- WAN Wide Area Network
- SAN System Area Network
- Token Ring etc.
- **Microsoft, Windows, Windows NT are registered trademarks and Microsoft SQL Server is a trademark of Microsoft Corporation
- DB2, AIX, OS/390, OS/400, and OS/2 are registered trademarks of IBM
- Oracle8 is a trademark of Oracle Corporation
- Solaris is a trademark of Sun Microsystem, Inc.
- the client/server database programs 8 a, b may be comprised of any client/server database program known in the art, such as DB2, Oracle Corporation's ORACLE 8, Microsoft SQL Server,** etc.
- the database programs 8 a and 8 b are used to access operations and perform operations with respect to information maintained in one or more databases 10 .
- the database(s) 10 would consist of multiple tables having rows and columns of data, e.g., tables 14 and 18 . Further details of the architecture and operation of a database program are described in the IBM publications “DB2 for OS/390: Administration Guide, Version 5” IBM document no. SC26-8957-01 (Copyright IBM.
- the clean transform program is implemented using the IBM stored procedure database program structure.
- a stored procedure is a block of procedural constructs and embedded SQL statements, i.e., an application program, that is stored in a database and can be called by name.
- Stored procedures allow an application program to execute in two parts. One part runs on the client and the other on the server. This allows one client call to produce several accesses of the database from the application program executing on the system, i.e., server including the database.
- Stored procedures are particularly useful to process a large number of database records, e.g., millions to billions of records, without having to transfer data between the server 4 and client 6 .
- the client stored procedure passes input information to the server stored procedure which then, executing within the database program 8 including the database 10 , processes numerous database records according to such client input information.
- the server stored procedure program is initiated by the client, and during execution the client cannot communicate with the stored procedure executing in the server. Further details of stored procedures are described in the publication “A Complete Guide to DB2 Universal Database,” “A Complete Guide to DB2 Universal Database,” which was incorporated by reference above.
- the clean transform of the preferred embodiments is implemented as a stored procedure application program 12 in the server 4 .
- the clean transform stored procedure 12 receives as input a name of an input table 14 in the database 10 , and transform rules 16 from the client 6 specifying the clean operations to perform on the data in the named input table 14 .
- the results of the clean operations performed by the clean transform stored procedure 12 in response to the transform rules 16 are generated into the output table 18 .
- the transformed, i.e., cleaned input table is written to the database 10 to overwrite the previous version of the input table.
- the client side of the clean transform stored procedure 20 generates the transform rules 16 that specify the clean operations to perform and initiate execution of the clean transform stored procedure 12 .
- the rules specified by the client side 20 are capable of implementing the following clean operations at the server 4 : replace values in selected data columns that are missing, nonvalid, or inconsistent with appropriate substitute values; remove unsuitable data rows; clip numeric values; perform numeric discretization; remove excess white space from text; copy columns from the source table to the target table.
- the client generated rules 16 can instruct the clean transform stored procedure 12 to ignore case and white spaces when locating strings and permit a tolerance value for numeric data.
- the client side 8 b can pass parameters to the clean transform stored procedure as “host variables” or embedded in a CALL statement. In either case, the parameters or transform rules 16 must be specified to control the operation of the clean transform 12 .
- a graphical user interface (GUI) at the client 6 may be used to enter various input parameters to control a clean operation.
- GUI graphical user interface
- the client side 20 would generate an application program interface (API) call to the clean transform stored procedure 12 including the parameters shown in FIG. 2 to initiate and control the operation of the clean transform stored procedure 12 .
- the clean parameter field 50 shown in FIG. 2 includes an input table name 52 , output table name 54 , log table name 56 , run ID 58 , and replacement rule definition(s) 60 .
- the input table name 52 specifies the input table 14 table including the data subject to the clean operations and the output table name 54 , which is optional, specifies the output table 18 where the cleaned data from the input table 14 is placed.
- the log table name 56 indicates the name of a log file into which warnings and exceptions that occur during execution of the clean transform stored procedure 12 are stored.
- the run ID 58 appears in the log file and is used to identify the transform operations for which the log entry was made.
- the replacement rule definition(s) 60 identifies an input column from the input table 14 and an output column in the output table 18 , and one or more rules to use when processing the input columns.
- the specified field of parameters 50 defines the transform rules 16 the client side of the clean transform stored procedure 20 presents to the clean transform stored procedure 12 in the server 4 .
- the replacement rule definitions 60 parameter comprises one or more definitions, such that each definition 80 includes sub-parameters 82 - 100 .
- the rule definitions parameter 60 may specify multiple distinct rule definitions 80 , each describing particular operations to perform on specified columns in the input table. Below is a description of the sub-parameters included in each definition 80 a user may specify in the rule definitions parameter 60 :
- a rule table may include columns for different rule definitions.
- the rule table is maintained in the database program 8 a for direct access by the clean transform 12 .
- the rule table may include one find column used in two rule definitions and different columns for the replacement values for the two rule definitions having the same find value. Thus, both rule definitions search on the same criteria, but provide different replacement values.
- the rule table may have different columns for the find criteria for two rule definitions, but the same replacement value for the different columns including the find criteria.
- the rule table may include multiple search criteria for a single rule definition and a corresponding search value for each of the search criteria. In this way, multiple search criteria could be applied to the same input column as part of one rule definition. Upon finding the first find value matching the field content, the replacement value corresponding to that matching find value would be inserted into the matching field.
- multiple rules in different columns of the same rule table or in different rule tables may sequentially operate against the same input data column. If more then one set of rules is to be applied to an input data column, a definition 80 must be specified for each rule.
- the order of the definitions listed in the replacement rule definitions 60 parameter indicates the order in which the rules are applied to the input data column. In this way, values in the input data column may be modified in sequence and processed according to clean operations specified in different rule definitions.
- the processing of subsequent rule definitions on the same column will depend on any replacement values inserted in the field during the application of a previous rule definition, not the original content in the input data column.
- rule definitions are applied to the copy of the input data table in memory as it is being processed. The find operation with respect to a field will apply to any previously inserted replacement value inserted into the field. If this inserted replacement value matches the search criteria of the subsequent rule definition, then another replacement value will be inserted into the field.
- the rule sub-parameter 90 indicates the type of rule indicated in the table identified in the rule table name sub-parameter 86 .
- Possible values for rule type could include find and replace, discretize, numeric clip or any other find and replace type operations known in the art.
- a find and replace locates a field in the input data column matching the find value. This find value is specified in one column of the rule table. The column including the find rule is provided in the first rule table column identified in sub-parameter 92 . This rule table column would further identify a second column in the rule table including a replacement value. If the find operation produces a match on the find value, then the replacement value is inserted in the field having the content matching the find value.
- the specification of the value to find in the rule table column must match the type of data in the input data column, e.g., only numbers are allowed in numeric “find” columns, pattern strings are allowed for character columns. Further for either numeric or character data, the database null value can be used as a find or replacement value.
- FIGS. 3 a and 3 b illustrate a possible format for rule tables for a find and replacement rule type.
- FIG. 3 a illustrates a two column find and replace rule table for an input data column including character values. The find pattern is what is searched for in the input data column and the replacement text value is what is inserted into the field matching the find pattern.
- FIG. 3 b illustrates a similar two column find and replace rule table for an input column having numerical values.
- the rule 90 indicates a discretization type, then there is both an upper and lower bound for the find value.
- the find operation can specify to search for matching values in the input data column that are between the upper and lower bounds, outside of the upper and lower bounds, less than the lower bound, or greater than the upper bounds.
- Another column in the rule table would specify a replacement operation to perform. If the upper and lower boundaries specified character strings, then SQL rules would be used to determine whether strings in the input data column satisfy the search criteria.
- FIG. 4 illustrates a table format for discretization for character and numeric data types. The replacement value is inserted into every field in the input data column having a value between the upper and lower bounds.
- Placement of the NULL indicator in the upper or lower bounds can affect whether the find seeks all fields in the input data column greater or less than one of the bounds.
- the discussion below with respect to FIGS. 6 b and 6 c explains how a NULL value in one of the find values affects the search criteria.
- the find operation finds fields in the input data column that are less than, equal to or greater than specified values.
- FIG. 5 shows that discretization includes an upper and lower values for both the find and replace. Any values in the input data column less than the lower bound and greater than the upper bound are replaced with the lower and upper replacement value, respectively.
- the use of the NULL value in one of the find values can affect the find operation, as discussed below with respect to FIGS. 6 d and 6 e.
- the database NULL value can be used as the find or replacement value.
- the NULL value indicates the absence of information.
- the database NULL value is only allowed for a pattern find value or replacement value if the database input column allows for NULL values, i.e., the input data column was not defined with a NOT NULL clause.
- the database NULL value may also be used to determine how to consider lower and upper bound values in the discretization and numeric clip operations, as described below.
- FIGS. 6 a, b, c, d , and e illustrate logic implemented in the clean transform stored procedure 12 (clean transform 12 ) to clean input data columns according to the clean parameters 50 including definitions 80 in the replacement rule definition(s) 60 parameter.
- These parameters 50 and sub-parameters 80 comprise the transform rules 16 .
- a user at the client 6 would specify certain clean operations to perform and the client side of the clean transform 20 would generate an API function call including the clean parameters 50 and definitions 80 from information the user entered in the GUI.
- Control begins at block 200 with the clean transform 12 receiving the clean parameters 50 and a set of sub-parameters 80 for each rule definition in an API function call.
- the clean transform 12 accesses (at block 202 ) the input table 14 from the database 10 indicated in the input data table name 52 .
- the clean transform 12 executes a loop to perform operations in FIGS. 6 a, b, c , beginning at block 204 .
- the clean transform 12 accesses (at block 206 ) the input data column from the input table 14 indicated in the input data column name 82 parameter for the rule definition and accesses (at block 208 ) the rule table in the server 4 indicated in the rule table name parameter 86 and input columns in the rule table indicated in the rule table columns parameter 92 for the rule definition.
- the clean transform 12 determines (at block 210 ) whether the rule table sort-key column name is non-empty. If so, the clean transform 12 sorts (at block 210 ) the rules in the rule columns for the rule definition according to the order specified in the sort-key column in the rule table. As discussed, a sort-key column may be provided if a rule table includes multiple rules to apply for the rule definition.
- the clean transform 12 determines (at block 214 ) whether the rule parameter 90 indicates a find and replace clean operation.
- the clean transform 12 determines (at block 216 ) one or more find values or patterns (if the data type is a character) from the first column in the rule table indicated in the rule table columns parameter 92 .
- the clean transform 12 inserts (at block 218 ) the replacement value/pattern in the column in the rule table having the same column number as the second column in the rule table columns 92 sub-parameter if one of the find values or patterns matches the field content. Note that if there are multiple find values/patterns in the rule table, then there is a different corresponding replacement value for each separate find/value pattern.
- the replacement value corresponding to the first matching find value is inserted in the field.
- the clean transform then returns (at block 220 ) to block 204 to process the next rule definition 80 in the rule definition parameters 60 .
- a rule escape character is indicated in sub-parameter 96
- the clean transform 12 searches for a character matching the character following the escape character, which may be a character that usually has specific search meaning, such as a percent (%), underscore ( ), comma (,) or semicolon (;).
- the row clean indicator sub-parameter 94 is set to YES, then any field, i.e., row, in the input data column matching the find value/pattern is not copied to the output table in the current rule definition being applied, or any further rule definitions that may apply to the field or row.
- numeric tolerance sub-parameter 100 If a numeric tolerance sub-parameter 100 is provided, than number fields in the input data column will return a match if the field value is equal to the find value within a range specified by the numeric tolerance. Further, if the ignore white space indicator sub-parameter 98 is YES, then white spaces are removed from the field when determining whether the field matches the find value/parameter; otherwise, white spaces are considered characters.
- the clean transform 12 determines (at block 242 ) the upper bound, lower bound and replacement values from the accessed rule table.
- the upper, lower, and replacement columns to access from the rule table maintained in the database program 8 a are indicated in the first, second, and third columns in the rule table columns sub-parameter 92 , respectively.
- the clean transform 12 determines (at block 244 ) whether the upper and lower bounds are both NULL. If so, the clean transform 12 does the replacement (at block 246 ) in every field in the current input data column. Otherwise, the clean transform 12 begins a loop at block 248 to perform for every field in the input data column.
- the clean transform 12 executes a nested loop between blocks 249 and 274 to consider each rule in the rule table, when there are multiple rules. For each rule, the clean transform 12 determines (at block 250 ) whether the accessed field in the input data column is NULL. If so, the clean transform 12 determines (at block 252 ) whether either the upper or lower bounds are NULL. If so, the clean transform 12 inserts (at block 254 ) the replacement value in the rule table into the field; otherwise, no replacement value is inserted (at block 256 ). From blocks 254 or 256 , control transfers to block 258 where the clean transform 12 performs another iteration of the loop beginning at block 248 for the next field (row) in the input data column.
- the clean transform 12 determines (at block 260 ) whether both the upper and lower bounds are NULL. If so, the clean transform 12 inserts the replacement value into the field and proceeds to block 258 to consider any further rows in the input data column. Otherwise, the clean transform 12 determines (at block 264 ) whether only the upper bound is NULL. If so, the clean transform 12 inserts (at block 266 ) the replacement value into the current field if the field in the input data column is greater than the lower bound minus the numeric tolerance for numeric data types where a numeric tolerance is specified.
- numeric tolerance is provided in parameter 100 or the input column is a character type, then there is no consideration of numeric tolerance when performing the find operation. If only the lower bound is NULL (at block 268 ), then the clean transform 12 inserts (at block 270 ) the replacement value into the current field if the field in the input data column is less than the upper bound plus any numeric tolerance for numeric data types where a numeric tolerance is specified. If neither the upper nor lower bound are NULL, then the clean transform 12 inserts (at block 272 ) the replacement value in the field if the field value is less than or equal to the upper bound plus any provided numeric tolerance AND less than or equal to the lower bound minus any provided numeric tolerance.
- the clean operation 12 After applying one rule in the rule table at blocks 254 , 256 , 262 , 266 , 270 or 272 , the clean operation 12 then considers any further rules at block 274 for the current row in the input data table. After finding a match or considering all rules in the rule table for a given field, the clean transform proceeds (at block 258 ) to consider the next row (field) in the input data column.
- the clean transform proceeds (at block 276 ) to block 204 to execute the next rule definition against a specified input data column.
- the clean transform determines (at block 302 ) the upper bound, lower bound, and replacement value in the columns of the rule table indicated in the rule table columns parameter 92 .
- the clean transform determines (at block 302 ) whether both upper and lower bounds are NULL. If so, the clean transform 12 skips (at block 304 ) the input data column, and inserts no replacement values into any row. Otherwise, the clean transform 12 begins a loop at block 306 to process each row in the input data column.
- the clean transform 12 begins a nested loop at block 307 to consider each rule in the rule table, if there are multiple rules.
- the clean transform 12 determines (at block 308 ) whether the field content is NULL. If so, the clean transform 12 does not insert (at block 310 ) the replacement value for any into the field and proceeds (at block 312 ) to consider the next row in the column until all rows are processed. If only the upper bound is null (at block 314 ), then the clean transform 12 inserts the lower replacement value in the field (at block 316 ) if the field value is less than the lower bound minus any numeric tolerance indicated in sub-parameter 100 . If only the lower bound is NULL (at block 318 ), then the clean transform 12 inserts the upper replacement value in the field (at block 320 ) if the field value is greater than the upper bound plus any numeric tolerance.
- the clean transform 12 inserts (at block 322 ) the lower replacement value if the field is less than or equal to the lower bound minus any numeric tolerance OR inserts the upper replacement value if the field is greater than or equal to the upper bound plus any tolerance.
- the clean transform 12 then proceeds (at block 323 ) back to block 307 to consider the next rule in the rule table, if there are further rules.
- the clean transform 12 proceeds (at block 312 ) back to block 36 to consider the next field (row) in the input data column.
- the clean transform 12 proceeds (at block 324 ) to block 204 to consider any further rule definitions in the replacement rule definition parameter 60 .
- the clean transform 12 determines (at block 326 ) whether the output data table name 54 specifies an output data table 18 in the database 10 to receive any cleaned or modified input data columns. If so, then the clean transform 12 writes (at block 328 ) the input data columns, including replaced and cleaned fields, to the specified output data table 19 . Otherwise, the modified and processed input data columns are written (at block 330 ) to the input table 14 .
- Preferred embodiments provide a command data structure to control a stored procedure program to clean columns of data in an input table the database 10 .
- the rules to clean the tables may be provided in a rule table data structure stored in the server 4 that provides one or more clean rules for different columns and in different sequences. This allows a single instance of execution of the clean program to perform numerous types of operations on the columns in the input table.
- Preferred embodiments thus provide a flexible and programmable data structure and program to provide fine grained control of clean operations.
- the client 6 does not increase network traffic because the client does not transfer the tables or the rule tables to clean between the database server 4 .
- the client 6 merely provides a command data structure including various parameters and rules to a stored procedure 12 that executes in the server 4 to perform the clean operations within the database program 8 on the server 4 .
- Such savings in network traffic can be significant when very large database tables, including millions or billions, of records are cleaned.
- FIGS. 7 and 8 a, b, c, d illustrate an example of how rule tables may be applied to clean an input data table.
- FIG. 7 illustrates an input data table to be cleaned including columns concerning sales information: city, indicating the sale location; product name; product category; dollar sales; and unit sales.
- FIGS. 8 a, b, c , and d illustrate different rule tables to apply to columns in the input table in FIG. 7 .
- FIG. 8 a illustrates a find and replace rule to locate certain strings in the City column that begin and end with “%”. The first percent in the find value represents any string of zero or more characters. An escape character of “/” is indicated in parameter 96 so the search criteria includes strings that start and end with the percent sign (%). The replace value is on the right.
- FIG. 8 a illustrates a rule table including multiple rules having search criteria to apply against each field in a column, e.g., the CITY column, of the input data table.
- FIG. 8 a would remove the abbreviations in the CITY input data column and replace them with a complete city name for the replacement value corresponding to the find value matching the field in the City column.
- FIG. 8 b provides a numeric clip rule table.
- values less than 100 are considered invalid and will be rounded up to 100, and any sales number greater than 15,000 is considered NULL.
- the rule table in FIG. 8 d will be used to flag rows with a NULL value to avoid in calculations.
- the rule table in FIG. 8 b processes each field in the Unit Sales data column and replaces values less than 100 with 100 and replaces values greater than 15,000 with NULL.
- a numeric tolerance may be specified to apply the rule to values “close enough” to the lower or upper bound.
- FIG. 8 c is a discretization rule table used to replace the code values in the Product Category column of the table in FIG. 7 with meaningful descriptive terms. Code values within certain string ranges are a particular type of product. For instance, a Category code in the range from “HA” to “JZ” is a hardware product.
- the application of the rule table in FIG. 8 c determines which rule has a lower and upper bound that includes the Product Category field and applies the corresponding Replace Value for the rule having the matching find bounds.
- the Upper Bound in the last row of the rule table in FIG. 8 c includes a NULL value. This means the replacement value is inserted in any field in the Product Category column having a string value greater than “RX.”
- FIG. 8 c further provides a sort column, which would be included in the sort-key column name parameter 88 , indicating the order in which to sort the rows in the rule table before applying the rule table to the input column
- FIG. 8 d is a find and replace table used to clean up the already processed data in the input table in FIG. 7 .
- a rule definition including the rule table in FIG. 8 d and having the row clean indicator parameter 94 set to YES would eliminate from further processing and from the final revised table any row in the input table ( FIG. 7 ) having a product name of NULL. If the compress white space indicator is set in the parameters with the rule definition including FIG. 8 d applied to the Product Name column, then any white space will be removed from the product name, such as the spaces between “Pick Axe” in the first data row of the input table in FIG. 7 .
- the preferred embodiments may be implemented as a method, apparatus or article of manufacture using standard programming and/or engineering techniques to produce software, firmware, hardware, or any combination thereof.
- article of manufacture (or alternatively, “computer program product”) as used herein is intended to encompass one or more computer programs and data files accessible from one or more computer-readable devices, carriers, or media, such as a magnetic storage media, “floppy disk,” CD-ROM, a file server providing access to the programs via a network transmission line, holographic unit, etc.
- a magnetic storage media such as a magnetic storage media, “floppy disk,” CD-ROM, a file server providing access to the programs via a network transmission line, holographic unit, etc.
- Preferred embodiments were described with respect to three rule types, find and replace, discretization, and numeric clip.
- other types of rules may be provided and included in the command data structure of the preferred embodiments to perform different types of clean operations known in the art.
- the clean transform program was executed in a stored procedure type program, such as that used in the IBM DB2 database system.
- a stored procedure type program such as that used in the IBM DB2 database system.
- different types of application programs, other than stored procedure programs may be executed in the server 4 or even the client 6 to perform clean operations in accordance with the command data structures of the preferred embodiments.
- the input table and output table were included in a database in the server in which the clean transform program is executing.
- the rule, input, and output tables may be distributed at different storage locations at different network devices.
- a client constructed the clean operation command and communicated such commands to the database server.
- the clean operation command of the preferred embodiments may be executed on the machine used to construct the command.
- preferred embodiments disclose 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 two 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.
- 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.
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. At least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria.
Description
- This application is a divisional application of and claims the benefit of “METHOD, SYSTEM, PROGRAM, AND DATA STRUCTURE FOR CLEANING A DATABASE TABLE”, having application Ser. No. 09/399,694, filed Sep. 21, 1999, the disclosure of which is incorporated herein by reference in its entirety.
- This application is related to the following commonly-assigned patents and co-pending patent applications, all of which are filed on the same date herewith, and which are incorporated herein by reference in their entirety:
-
- “Method, System, Program, And Data Structure for Transforming Database Tables,” to Mark A. Cesare, Tom R. Christopher, Julie A. Jerves, Richard H. Mandel III, and having attorney docket number ST9-99-034, application Ser. No. 09/400,507, and U.S. Pat. No. 6,920,443, issue don Jul. 19, 2005;
- “Method, System, Program, And Data Structure for Pivoting Columns in a Database Table,” to Mark A. Cesare, Julie A. Jerves, and Richard H. Mandel III, and having attorney docket number ST9-99-035, application Ser. No. 09/400,057, and U.S. Pat. No. 6,604,095, issued on Aug. 5, 2003;
- “Method, System, and Program for Inverting Columns in a Database Table,” to Mark A. Cesare, Julie A. Jerves, and Richard H. Mandel III, and having attorney docket no. ST9-99-038, application Ser. No. 09/400,690, and U.S. Pat. No. 6,748,389, issued on Jun. 8, 2004; and
- “Method, System, Program, And Data Structure For Cleaning a Database Table Using a Look-up Table,” Mark A. Cesare, Julie A. Jerves, and Richard H. Mandel III, and having attorney docket no. ST9-99-036, application Ser. No. 09/401,006, and U.S. Pat. No. 6,965,888, issued Nov. 15, 2005.
- 1. Field of the Invention
- The present invention relates to a method, system, program, and data structure for cleaning a database table and, in particular, for performing clean operations on columns in the database table.
- 2. Description of the Related Art
- Data records in a computer database are maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records which comprise the rows. A data warehouse is a large scale database including millions or billions of records defining business or other types of transactions or activities. Data warehouses contain a wide variety of data that present a coherent picture of business or organizational conditions over time. Various data analysis and mining tools are provided with the data warehouse to allow users to effectively analyze, manage and access large-scale databases to support management decision making. Data mining is the process of extracting valid and previously unknown information from large databases and using it to make crucial business decisions. In many real-world domains such as marketing analysis, financial analysis, fraud detection, etc, information extraction requires the cooperative use of several data mining operations and techniques.
- Once the desired database tables have been selected and the data to be mined has been identified, transformations on the data may be necessary. Transformations vary from conversions of one type of data to another, e.g., converting nominal values into numeric ones so that they can be processed by a neural network, to definition of new attributes, i.e., derived attributes. New attributes are defined either by applying mathematical or logical operators on the values of one or more database attributes. The transformed data is stored in a target database where it may then be mined using one or more techniques to extract the desired type of information necessary to make the organizational decisions. Further details of data mining are described in the International Business Machines Corporation (IBM) publication entitled “White Paper: Data Mining Solutions” (IBM Copyright, 1996)
- Data transformation refers to the process of filtering, merging, decoding, and translating source data to create validated data for the data warehouse and data mining tools. For example, a numeric regional code might be replaced with the name of the region. Data transformations and cleansing is used when data is inconsistent or incompatible between sources. In such case, some level of data cleansing is needed to ensure data consistency and accuracy. Some of the current techniques for transforming and cleansing data include the use of an SQL WHERE clause to limit the rows extracted from the source table. Further, formulas and expressions specified in the column definition window and constants and tokens are used to eliminate and modify data.
- Previous versions of IBM Visual Warehouse included programs to allow users to perform numerous functions on the source data. For instance, if one database table has revenue data in U.S. dollars and another data table stores revenue data in foreign currency denominations, then the foreign revenue data must be cleansed before both sets of data can be analyzed together. Transformation operations may be performed using application programs external to the database program that process and transform tables of data records. Further details of data warehousing and data transforms, are described in the IBM publications “Managing Visual Warehouse, Version 3.1,” IBM document no. GC26-8822-01 (IBM Copyright, January, 1998), which is incorporated herein by reference in its entirety.
- Notwithstanding current programs for cleansing data, there is a need in the art to provide users greater control over operations to clean input data.
- To overcome the limitations in the prior art described above, preferred embodiments disclose 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. At least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria.
- In further embodiments, each rule definition is associated with one rule table including the find criteria and replacement value. In such case, a rule table column parameter is provided for each rule definition indicating the columns in the rule table including the find criteria and replacement value for that rule definition. In certain embodiments, two rule definitions may have the same rule table. In such case, the rule table column parameters indicate different columns in the same rule table including the find criteria and replacement value for each rule definition. In still further embodiments, a separate rule table may include the find criteria and replacement value for different rule definitions.
- Still further, a rule definition may include multiple find criteria and a corresponding replacement value for each find criteria. In such case, the step of searching the input data column comprises applying each of the multiple find criteria to one field until a match occurs or none of the multiple find criteria are found to match the field content. When a match is found, the replacement value corresponding to the find criteria is inserted in the field having the matching content.
- In preferred embodiments, the rule definition may define a find and replace rule, a discretization rule or a numeric clip rule. Different rule definitions may define different rule types.
- In preferred embodiments, the rule definitions may be communicated from one computer system, such as a client, to a computer system including the input data table, such as a database server. The rule definitions are then executed against the input table on the database server including the input tables.
- Preferred embodiments provide a data command structure including one or more rule definitions for performing different operations on the data in an input data table. The preferred embodiments provide a command structure that accommodates multiple types of clean operations to be performed on an input data table before the input data table is written to the output table. Further, preferred embodiments allow a client to transfer clean commands including to the database server including the database for execution on the database server. This reduces network traffic as the database tables subject to the clean operation do not have to be transferred between the database server and the client constructing the clean commands. Further, in preferred embodiments, the rule definitions are maintained in rule tables in the server. This further reduces network traffic as the clean command need only specify the location of rules to apply and does not have to provide tables of rules.
- Referring now to the drawings in which like reference numbers represent corresponding parts throughout:
-
FIG. 1 illustrates a computing environment in which preferred embodiments are implemented; -
FIG. 2 illustrates the parameters used in a transform command to clean input tables in accordance with preferred embodiments of the present invention; -
FIGS. 3 a, 3 b, 4, and 5 illustrate examples of a rule table to clean data in accordance with preferred embodiments of the present invention; -
FIGS. 6 a, 6 b, 6 c, 6 d, and 6 e illustrate logic to clean an input data table in accordance with preferred embodiments of the present invention; -
FIG. 7 illustrates an example of an input data table; and -
FIGS. 8 a, 8 b, 8 c, and 8 d illustrate examples of rule table to apply to clean columns in the input data table inFIG. 7 in accordance with preferred embodiments of the present invention. - In the following description, reference is made to the accompanying drawings which form a part hereof and which illustrate several embodiments of the present invention. It is understood that other embodiments may be utilized and structural and operational changes may be made without departing from the scope of the present invention.
-
FIG. 1 illustrates a computing environment 2 in which preferred embodiments are implemented. The environment 2 includes aserver 4 andclient 6. Theserver 4 andclient 6 would include an operating system, such asMICROSOFT WINDOWS 98 and WINDOWS NT, AIX, OS/390, OS/400, OS/2, and SUN SOLARIS,** and may be comprised of any suitable server and client architecture known in the art. Theserver 4 andclient 6 include a database program 8 a and 8 b, wherein 8 a comprises theserver 4 side of the database program and 8 b comprises theclient 6 side. Theserver 4 andclient 6 may communicate via any communication means known in the art, such as a telephone line, dedicated cable or network line, etc, using any protocol known in the art including TCP/IP network (e.g., an Intranet, the Internet), LAN, Ethernet, WAN, System Area Network (SAN), Token Ring, etc. Alternatively, there may be separate and different networks between theservers 4 andclient 6.
**Microsoft, Windows, Windows NT are registered trademarks and Microsoft SQL Server is a trademark of Microsoft Corporation; DB2, AIX, OS/390, OS/400, and OS/2 are registered trademarks of IBM; and Oracle8 is a trademark of Oracle Corporation; and Solaris is a trademark of Sun Microsystem, Inc.
- The client/server database programs 8 a, b, may be comprised of any client/server database program known in the art, such as DB2, Oracle Corporation's ORACLE 8, Microsoft SQL Server,** etc. The database programs 8 a and 8 b are used to access operations and perform operations with respect to information maintained in one or
more databases 10. The database(s) 10 would consist of multiple tables having rows and columns of data, e.g., tables 14 and 18. Further details of the architecture and operation of a database program are described in the IBM publications “DB2 for OS/390: Administration Guide,Version 5” IBM document no. SC26-8957-01 (Copyright IBM. Corp., June, 1997) and “A Complete Guide to DB2 Universal Database,” by Don Chamberlin (1998), which publications are incorporated herein by reference in its entirety.
**Microsoft, Windows, Windows NT are registered trademarks and Microsoft SQL Server is a trademark of Microsoft Corporation; DB2, AIX, OS/390, OS/400, and OS/2 are registered trademarks of IBM; and Oracle8 is a trademark of Oracle Corporation; and Solaris is a trademark of Sun Microsystem, Inc.
- In preferred embodiments, the clean transform program is implemented using the IBM stored procedure database program structure. A stored procedure is a block of procedural constructs and embedded SQL statements, i.e., an application program, that is stored in a database and can be called by name. Stored procedures allow an application program to execute in two parts. One part runs on the client and the other on the server. This allows one client call to produce several accesses of the database from the application program executing on the system, i.e., server including the database. Stored procedures are particularly useful to process a large number of database records, e.g., millions to billions of records, without having to transfer data between the
server 4 andclient 6. The client stored procedure passes input information to the server stored procedure which then, executing within the database program 8 including thedatabase 10, processes numerous database records according to such client input information. The server stored procedure program is initiated by the client, and during execution the client cannot communicate with the stored procedure executing in the server. Further details of stored procedures are described in the publication “A Complete Guide to DB2 Universal Database,” “A Complete Guide to DB2 Universal Database,” which was incorporated by reference above. - The clean transform of the preferred embodiments is implemented as a stored
procedure application program 12 in theserver 4. The clean transform storedprocedure 12 receives as input a name of an input table 14 in thedatabase 10, and transformrules 16 from theclient 6 specifying the clean operations to perform on the data in the named input table 14. The results of the clean operations performed by the clean transform storedprocedure 12 in response to the transform rules 16 are generated into the output table 18. Alternatively, the transformed, i.e., cleaned input table is written to thedatabase 10 to overwrite the previous version of the input table. - The client side of the clean transform stored
procedure 20 generates the transform rules 16 that specify the clean operations to perform and initiate execution of the clean transform storedprocedure 12. The rules specified by theclient side 20 are capable of implementing the following clean operations at the server 4: replace values in selected data columns that are missing, nonvalid, or inconsistent with appropriate substitute values; remove unsuitable data rows; clip numeric values; perform numeric discretization; remove excess white space from text; copy columns from the source table to the target table. Further, the client generatedrules 16 can instruct the clean transform storedprocedure 12 to ignore case and white spaces when locating strings and permit a tolerance value for numeric data. - In preferred embodiments, the client side 8 b can pass parameters to the clean transform stored procedure as “host variables” or embedded in a CALL statement. In either case, the parameters or transform
rules 16 must be specified to control the operation of theclean transform 12. - A graphical user interface (GUI) at the
client 6 may be used to enter various input parameters to control a clean operation. In response to such user input, theclient side 20 would generate an application program interface (API) call to the clean transform storedprocedure 12 including the parameters shown inFIG. 2 to initiate and control the operation of the clean transform storedprocedure 12. Theclean parameter field 50 shown inFIG. 2 includes aninput table name 52,output table name 54,log table name 56,run ID 58, and replacement rule definition(s) 60. - The
input table name 52 specifies the input table 14 table including the data subject to the clean operations and theoutput table name 54, which is optional, specifies the output table 18 where the cleaned data from the input table 14 is placed. Thelog table name 56 indicates the name of a log file into which warnings and exceptions that occur during execution of the clean transform storedprocedure 12 are stored. Therun ID 58 appears in the log file and is used to identify the transform operations for which the log entry was made. The replacement rule definition(s) 60 identifies an input column from the input table 14 and an output column in the output table 18, and one or more rules to use when processing the input columns. The specified field ofparameters 50 defines the transform rules 16 the client side of the clean transform storedprocedure 20 presents to the clean transform storedprocedure 12 in theserver 4. - The
replacement rule definitions 60 parameter comprises one or more definitions, such that eachdefinition 80 includes sub-parameters 82-100. Thus, therule definitions parameter 60 may specify multipledistinct rule definitions 80, each describing particular operations to perform on specified columns in the input table. Below is a description of the sub-parameters included in each definition 80 a user may specify in the rule definitions parameter 60: -
- Input
Data Column Name 82—defines an existing column in the table having the InputData Table Name 52 that contains the data to be operated on in accordance with the rules specified in the rule table and other parameters. This parameter is required in thedefinition 80. The input data column must be capable of being modified, so that the clean transform storedprocedure 12 will not update a value which has a constraint, such as for a unique key column or a referential constraint. - Output Data Column Name 84: defines the name of a column in the table having the Output
Data Table Name 54 where cleaned data is placed. This parameter is optional, and the default is the inputdata column name 82. Thus, if no output data column or output table is specified, clean data is placed back into the copy of the input table in memory being processed. - Rule Table Name 86: Provides the name of the table containing the “find and replace” rule patterns. This sub-parameter is optional; if not provided the values for the column are copied to the output table, and are only modified if the option to compress or remove white space is specified. In preferred embodiments, the rule tables are maintained in the
server 4 and are specified in the rule definitions. - Rule Table Sort-Key Column Name 88: When a rule table includes multiple rules to apply to the input data column, this
parameter 88 indicates a sort key-column in the rule table that provides an ordering in which the rules are applied to rows (fields) in the input data column. - Rule 90: This sub-parameter is required if the
rule table name 86 is specified. This sub-parameter defines the type of rule included in the rule table to use when processing the values in the input data column. Further details ofrule 90 are described below. - Rule Table Columns 92: This sub-parameter is specified if the rule table name is specified. The number of columns depend on the rules, examples of which are discussed below. If a rule table has different columns for different rule definitions, i.e., one rule table stores multiple rule definitions, then the
rule table columns 92 parameter indicates those columns in the rule table including the specific rules for one rule definition. - Row Clean Indicator 94: Boolean YES/NO value. If YES is specified and there is a match in the input data column to the “find” condition, then the input value is not copied to the output table and the located matching rows are deleted from the temporary input table. Note that the setting this parameter to YES will affect subsequent attempts to process the transitional input data columns as the matching entries in the input data columns subject to the row
clean indicator 94 are empty. If the value for this sub-parameter is NO, the matching entry in the input data column is copied to the output data column or the input data column. Further, any replacement value in a rule table is ignored and need not be specified when the row clean indicator is used. This sub-parameter is optional, and is ignored unless arule table name 86 is specified. - Rule Escape Character 96: Certain characters have special meaning, such as the percent sign (%), which represents any string of zero or more characters, or the underscore character ( ), which represents a single character. The use of the rule escape character in the search string followed by the special character, means that the query looks for the actual special character following the escape character, and ignores the special meaning.
- White Space Indicator 98: Optional boolean value that indicates whether white spaces are ignored when searching
- Numeric Tolerance 100: Specifies a tolerance value when doing a “find” operation with respect to numeric values. Thus, numbers within the “tolerance” range of the searched upon number will produce a match.
- Input
- A rule table may include columns for different rule definitions. In preferred embodiments the rule table is maintained in the database program 8 a for direct access by the
clean transform 12. In this way, users can utilize predefined clean rules in a rule table in the database program 8 a. The rule table may include one find column used in two rule definitions and different columns for the replacement values for the two rule definitions having the same find value. Thus, both rule definitions search on the same criteria, but provide different replacement values. Alternatively, the rule table may have different columns for the find criteria for two rule definitions, but the same replacement value for the different columns including the find criteria. Still further, the rule table may include multiple search criteria for a single rule definition and a corresponding search value for each of the search criteria. In this way, multiple search criteria could be applied to the same input column as part of one rule definition. Upon finding the first find value matching the field content, the replacement value corresponding to that matching find value would be inserted into the matching field. - Moreover, multiple rules in different columns of the same rule table or in different rule tables may sequentially operate against the same input data column. If more then one set of rules is to be applied to an input data column, a
definition 80 must be specified for each rule. The order of the definitions listed in thereplacement rule definitions 60 parameter indicates the order in which the rules are applied to the input data column. In this way, values in the input data column may be modified in sequence and processed according to clean operations specified in different rule definitions. In preferred embodiments, the processing of subsequent rule definitions on the same column will depend on any replacement values inserted in the field during the application of a previous rule definition, not the original content in the input data column. Thus, rule definitions are applied to the copy of the input data table in memory as it is being processed. The find operation with respect to a field will apply to any previously inserted replacement value inserted into the field. If this inserted replacement value matches the search criteria of the subsequent rule definition, then another replacement value will be inserted into the field. - The
rule sub-parameter 90 indicates the type of rule indicated in the table identified in the ruletable name sub-parameter 86. Possible values for rule type could include find and replace, discretize, numeric clip or any other find and replace type operations known in the art. A find and replace locates a field in the input data column matching the find value. This find value is specified in one column of the rule table. The column including the find rule is provided in the first rule table column identified insub-parameter 92. This rule table column would further identify a second column in the rule table including a replacement value. If the find operation produces a match on the find value, then the replacement value is inserted in the field having the content matching the find value. The specification of the value to find in the rule table column must match the type of data in the input data column, e.g., only numbers are allowed in numeric “find” columns, pattern strings are allowed for character columns. Further for either numeric or character data, the database null value can be used as a find or replacement value. -
FIGS. 3 a and 3 b illustrate a possible format for rule tables for a find and replacement rule type.FIG. 3 a illustrates a two column find and replace rule table for an input data column including character values. The find pattern is what is searched for in the input data column and the replacement text value is what is inserted into the field matching the find pattern.FIG. 3 b illustrates a similar two column find and replace rule table for an input column having numerical values. - If the
rule 90 indicates a discretization type, then there is both an upper and lower bound for the find value. The find operation can specify to search for matching values in the input data column that are between the upper and lower bounds, outside of the upper and lower bounds, less than the lower bound, or greater than the upper bounds. Another column in the rule table would specify a replacement operation to perform. If the upper and lower boundaries specified character strings, then SQL rules would be used to determine whether strings in the input data column satisfy the search criteria.FIG. 4 illustrates a table format for discretization for character and numeric data types. The replacement value is inserted into every field in the input data column having a value between the upper and lower bounds. Placement of the NULL indicator in the upper or lower bounds can affect whether the find seeks all fields in the input data column greater or less than one of the bounds. The discussion below with respect toFIGS. 6 b and 6 c explains how a NULL value in one of the find values affects the search criteria. - If the
rule 90 indicates a numeric clip type, then the find operation finds fields in the input data column that are less than, equal to or greater than specified values.FIG. 5 shows that discretization includes an upper and lower values for both the find and replace. Any values in the input data column less than the lower bound and greater than the upper bound are replaced with the lower and upper replacement value, respectively. As with discretization, the use of the NULL value in one of the find values can affect the find operation, as discussed below with respect toFIGS. 6 d and 6 e. - For find and replacement, discretization, and numeric clip rule types, the database NULL value can be used as the find or replacement value. The NULL value indicates the absence of information. The database NULL value is only allowed for a pattern find value or replacement value if the database input column allows for NULL values, i.e., the input data column was not defined with a NOT NULL clause. The database NULL value may also be used to determine how to consider lower and upper bound values in the discretization and numeric clip operations, as described below.
-
FIGS. 6 a, b, c, d, and e illustrate logic implemented in the clean transform stored procedure 12 (clean transform 12) to clean input data columns according to theclean parameters 50 includingdefinitions 80 in the replacement rule definition(s) 60 parameter. Theseparameters 50 andsub-parameters 80 comprise the transform rules 16. As discussed, a user at theclient 6 would specify certain clean operations to perform and the client side of theclean transform 20 would generate an API function call including theclean parameters 50 anddefinitions 80 from information the user entered in the GUI. Control begins atblock 200 with theclean transform 12 receiving theclean parameters 50 and a set of sub-parameters 80 for each rule definition in an API function call. Theclean transform 12 accesses (at block 202) the input table 14 from thedatabase 10 indicated in the inputdata table name 52. For eachrule definition 80 specified in thereplacement rule definitions 60 parameter, theclean transform 12 executes a loop to perform operations inFIGS. 6 a, b, c, beginning atblock 204. - In processing each rule definition, the
clean transform 12 accesses (at block 206) the input data column from the input table 14 indicated in the inputdata column name 82 parameter for the rule definition and accesses (at block 208) the rule table in theserver 4 indicated in the ruletable name parameter 86 and input columns in the rule table indicated in the ruletable columns parameter 92 for the rule definition. Theclean transform 12 determines (at block 210) whether the rule table sort-key column name is non-empty. If so, theclean transform 12 sorts (at block 210) the rules in the rule columns for the rule definition according to the order specified in the sort-key column in the rule table. As discussed, a sort-key column may be provided if a rule table includes multiple rules to apply for the rule definition. Otherwise, if the sort key-column parameter 88 is empty, the rule columns are sorted in ascending order. Afterblock 212 or the yes branch ofblock 210, theclean transform 12 determines (at block 214) whether therule parameter 90 indicates a find and replace clean operation. - If the clean operation for the current definition is a find and replace, the
clean transform 12 determines (at block 216) one or more find values or patterns (if the data type is a character) from the first column in the rule table indicated in the ruletable columns parameter 92. Theclean transform 12 inserts (at block 218) the replacement value/pattern in the column in the rule table having the same column number as the second column in therule table columns 92 sub-parameter if one of the find values or patterns matches the field content. Note that if there are multiple find values/patterns in the rule table, then there is a different corresponding replacement value for each separate find/value pattern. The replacement value corresponding to the first matching find value is inserted in the field. The clean transform then returns (at block 220) to block 204 to process thenext rule definition 80 in therule definition parameters 60. - During the find and replace searching, and the searching operations for the discretization and numeric clip rule types, other sub-parameters are considered. If a rule escape character is indicated in
sub-parameter 96, theclean transform 12 searches for a character matching the character following the escape character, which may be a character that usually has specific search meaning, such as a percent (%), underscore ( ), comma (,) or semicolon (;). Further, if the rowclean indicator sub-parameter 94 is set to YES, then any field, i.e., row, in the input data column matching the find value/pattern is not copied to the output table in the current rule definition being applied, or any further rule definitions that may apply to the field or row. If anumeric tolerance sub-parameter 100 is provided, than number fields in the input data column will return a match if the field value is equal to the find value within a range specified by the numeric tolerance. Further, if the ignore whitespace indicator sub-parameter 98 is YES, then white spaces are removed from the field when determining whether the field matches the find value/parameter; otherwise, white spaces are considered characters. - If the rule indicated in the
rule sub-parameter 90 is discretization (at block 240), then theclean transform 12 determines (at block 242) the upper bound, lower bound and replacement values from the accessed rule table. The upper, lower, and replacement columns to access from the rule table maintained in the database program 8 a are indicated in the first, second, and third columns in the rule table columns sub-parameter 92, respectively. Theclean transform 12 determines (at block 244) whether the upper and lower bounds are both NULL. If so, theclean transform 12 does the replacement (at block 246) in every field in the current input data column. Otherwise, theclean transform 12 begins a loop atblock 248 to perform for every field in the input data column. In this loop, theclean transform 12 executes a nested loop betweenblocks clean transform 12 determines (at block 250) whether the accessed field in the input data column is NULL. If so, theclean transform 12 determines (at block 252) whether either the upper or lower bounds are NULL. If so, theclean transform 12 inserts (at block 254) the replacement value in the rule table into the field; otherwise, no replacement value is inserted (at block 256). Fromblocks 254 or 256, control transfers to block 258 where theclean transform 12 performs another iteration of the loop beginning atblock 248 for the next field (row) in the input data column. - If the field in the input data columns is not NULL (from the NO branch at block 250), then the
clean transform 12 determines (at block 260) whether both the upper and lower bounds are NULL. If so, theclean transform 12 inserts the replacement value into the field and proceeds to block 258 to consider any further rows in the input data column. Otherwise, theclean transform 12 determines (at block 264) whether only the upper bound is NULL. If so, theclean transform 12 inserts (at block 266) the replacement value into the current field if the field in the input data column is greater than the lower bound minus the numeric tolerance for numeric data types where a numeric tolerance is specified. If no numeric tolerance is provided inparameter 100 or the input column is a character type, then there is no consideration of numeric tolerance when performing the find operation. If only the lower bound is NULL (at block 268), then theclean transform 12 inserts (at block 270) the replacement value into the current field if the field in the input data column is less than the upper bound plus any numeric tolerance for numeric data types where a numeric tolerance is specified. If neither the upper nor lower bound are NULL, then theclean transform 12 inserts (at block 272) the replacement value in the field if the field value is less than or equal to the upper bound plus any provided numeric tolerance AND less than or equal to the lower bound minus any provided numeric tolerance. After applying one rule in the rule table atblocks 254, 256, 262, 266, 270 or 272, theclean operation 12 then considers any further rules atblock 274 for the current row in the input data table. After finding a match or considering all rules in the rule table for a given field, the clean transform proceeds (at block 258) to consider the next row (field) in the input data column. - After all rows in the input data column are considered from
blocks - If the rule indicated in the
rule parameter 90 is a numeric clip (atblock 300 inFIG. 6 d), then the clean transform determines (at block 302) the upper bound, lower bound, and replacement value in the columns of the rule table indicated in the ruletable columns parameter 92. The clean transform then determines (at block 302) whether both upper and lower bounds are NULL. If so, theclean transform 12 skips (at block 304) the input data column, and inserts no replacement values into any row. Otherwise, theclean transform 12 begins a loop atblock 306 to process each row in the input data column. Theclean transform 12 begins a nested loop atblock 307 to consider each rule in the rule table, if there are multiple rules. Theclean transform 12 determines (at block 308) whether the field content is NULL. If so, theclean transform 12 does not insert (at block 310) the replacement value for any into the field and proceeds (at block 312) to consider the next row in the column until all rows are processed. If only the upper bound is null (at block 314), then theclean transform 12 inserts the lower replacement value in the field (at block 316) if the field value is less than the lower bound minus any numeric tolerance indicated insub-parameter 100. If only the lower bound is NULL (at block 318), then theclean transform 12 inserts the upper replacement value in the field (at block 320) if the field value is greater than the upper bound plus any numeric tolerance. If neither the lower nor upper bound are NULL, then theclean transform 12 inserts (at block 322) the lower replacement value if the field is less than or equal to the lower bound minus any numeric tolerance OR inserts the upper replacement value if the field is greater than or equal to the upper bound plus any tolerance. After applying one rule against one field atblocks clean transform 12 then proceeds (at block 323) back to block 307 to consider the next rule in the rule table, if there are further rules. After applying all rules in a rule table to one field (row), theclean transform 12 proceeds (at block 312) back to block 36 to consider the next field (row) in the input data column. - After processing all rules in the rule table and rows in the input data column from
block 312 or block 324, theclean transform 12 proceeds (at block 324) to block 204 to consider any further rule definitions in the replacementrule definition parameter 60. After thelast rule definition 80 in the replacementrule definitions parameter 60 is processed atblocks clean transform 12 then determines (at block 326) whether the outputdata table name 54 specifies an output data table 18 in thedatabase 10 to receive any cleaned or modified input data columns. If so, then theclean transform 12 writes (at block 328) the input data columns, including replaced and cleaned fields, to the specified output data table 19. Otherwise, the modified and processed input data columns are written (at block 330) to the input table 14. - Preferred embodiments provide a command data structure to control a stored procedure program to clean columns of data in an input table the
database 10. The rules to clean the tables may be provided in a rule table data structure stored in theserver 4 that provides one or more clean rules for different columns and in different sequences. This allows a single instance of execution of the clean program to perform numerous types of operations on the columns in the input table. Preferred embodiments thus provide a flexible and programmable data structure and program to provide fine grained control of clean operations. Further, with preferred embodiments, theclient 6 does not increase network traffic because the client does not transfer the tables or the rule tables to clean between thedatabase server 4. Instead, theclient 6 merely provides a command data structure including various parameters and rules to a storedprocedure 12 that executes in theserver 4 to perform the clean operations within the database program 8 on theserver 4. Such savings in network traffic can be significant when very large database tables, including millions or billions, of records are cleaned. -
FIGS. 7 and 8 a, b, c, d illustrate an example of how rule tables may be applied to clean an input data table.FIG. 7 illustrates an input data table to be cleaned including columns concerning sales information: city, indicating the sale location; product name; product category; dollar sales; and unit sales.FIGS. 8 a, b, c, and d illustrate different rule tables to apply to columns in the input table inFIG. 7 .FIG. 8 a illustrates a find and replace rule to locate certain strings in the City column that begin and end with “%”. The first percent in the find value represents any string of zero or more characters. An escape character of “/” is indicated inparameter 96 so the search criteria includes strings that start and end with the percent sign (%). The replace value is on the right. Thus,FIG. 8 a illustrates a rule table including multiple rules having search criteria to apply against each field in a column, e.g., the CITY column, of the input data table.FIG. 8 a would remove the abbreviations in the CITY input data column and replace them with a complete city name for the replacement value corresponding to the find value matching the field in the City column. -
FIG. 8 b provides a numeric clip rule table. In the Unit Sales column, values less than 100 are considered invalid and will be rounded up to 100, and any sales number greater than 15,000 is considered NULL. Later, the rule table inFIG. 8 d will be used to flag rows with a NULL value to avoid in calculations. Thus, the rule table inFIG. 8 b processes each field in the Unit Sales data column and replaces values less than 100 with 100 and replaces values greater than 15,000 with NULL. A numeric tolerance may be specified to apply the rule to values “close enough” to the lower or upper bound. -
FIG. 8 c is a discretization rule table used to replace the code values in the Product Category column of the table inFIG. 7 with meaningful descriptive terms. Code values within certain string ranges are a particular type of product. For instance, a Category code in the range from “HA” to “JZ” is a hardware product. The application of the rule table inFIG. 8 c determines which rule has a lower and upper bound that includes the Product Category field and applies the corresponding Replace Value for the rule having the matching find bounds. The Upper Bound in the last row of the rule table inFIG. 8 c includes a NULL value. This means the replacement value is inserted in any field in the Product Category column having a string value greater than “RX.”FIG. 8 c further provides a sort column, which would be included in the sort-keycolumn name parameter 88, indicating the order in which to sort the rows in the rule table before applying the rule table to the input column -
FIG. 8 d is a find and replace table used to clean up the already processed data in the input table inFIG. 7 . A rule definition including the rule table inFIG. 8 d and having the rowclean indicator parameter 94 set to YES would eliminate from further processing and from the final revised table any row in the input table (FIG. 7 ) having a product name of NULL. If the compress white space indicator is set in the parameters with the rule definition includingFIG. 8 d applied to the Product Name column, then any white space will be removed from the product name, such as the spaces between “Pick Axe” in the first data row of the input table inFIG. 7 . - This concludes the description of the preferred embodiments of the invention. The following describes some alternative embodiments for accomplishing the present invention.
- The preferred embodiments may be implemented as a method, apparatus or article of manufacture using standard programming and/or engineering techniques to produce software, firmware, hardware, or any combination thereof. The term “article of manufacture” (or alternatively, “computer program product”) as used herein is intended to encompass one or more computer programs and data files accessible from one or more computer-readable devices, carriers, or media, such as a magnetic storage media, “floppy disk,” CD-ROM, a file server providing access to the programs via a network transmission line, holographic unit, etc. Of course, those skilled in the art will recognize that many modifications may be made to this configuration without departing from the scope of the present invention.
- Preferred embodiments were described with respect to specific data structures, such as a rule table having columns of rules, and an arrangement of parameters to provide a vehicle for transferring commands to the clean transform stored procedure program. However, those skilled in the art will recognize that modifications may be made to the architecture of the data structures used to convey multiple clean rules and still remain within the scope of the present invention.
- Preferred embodiments were described with respect to three rule types, find and replace, discretization, and numeric clip. In further embodiments, other types of rules may be provided and included in the command data structure of the preferred embodiments to perform different types of clean operations known in the art.
- In preferred embodiments, the clean transform program was executed in a stored procedure type program, such as that used in the IBM DB2 database system. However, in further embodiments, different types of application programs, other than stored procedure programs, may be executed in the
server 4 or even theclient 6 to perform clean operations in accordance with the command data structures of the preferred embodiments. - In preferred embodiments, the input table and output table were included in a database in the server in which the clean transform program is executing. In alternative embodiments, the rule, input, and output tables may be distributed at different storage locations at different network devices.
- In preferred embodiments, a client constructed the clean operation command and communicated such commands to the database server. In alternative embodiments, the clean operation command of the preferred embodiments may be executed on the machine used to construct the command.
- In summary, preferred embodiments disclose 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 two 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.
- The foregoing description of the preferred embodiments of the invention has been presented for the purposes of illustration and description. It is not intended to be exhaustive or to limit the invention to the precise form disclosed. Many modifications and variations are possible in light of the above teaching. It is intended that the scope of the invention be limited not by this detailed description, but rather by the claims appended hereto. The above specification, examples and data provide a complete description of the manufacture and use of the composition of the invention. Since many embodiments of the invention can be made without departing from the spirit and scope of the invention, the invention resides in the claims hereinafter appended.
Claims (16)
1. A computer implemented method for performing a clean operation on an input table having an input table name, comprising:
receiving at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, and an input data column in the input table;
searching, for each rule definition, the input data column for any fields that match the find criteria;
determining whether the rule definition specifies an output table; and
in response to determining that the rule definition does not specify an output table, directly inserting, for each rule definition, 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,
wherein at least one of said rule definition 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, wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein searching comprises applying the multiple find criteria to each field in the order specified in the sort column.
2. The method of claim 1 , wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein searching comprises searching for any fields that have values within the upper and lower bounds.
3. The method of claim 2 , wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.
4. The method of claim 1 , wherein each rule definition includes 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.
5. A computer implemented system for performing a clean operation on an input table having an input data table name, comprising;
means for receiving at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, and an input data column in the input table;
means for searching, for each rule definition, the input data column for any fields that match the find criteria;
means for determining whether the rule definition specifies an output table; and
means for, in response to determining that the rule definition does not specify an output table, directly inserting, for each rule definition, 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;
wherein at least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria, wherein the means for 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, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein the means for searching comprises applying the multiple find criteria to each field in the order specified in the sort column.
6. The system of claim 5 , wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein searching comprises searching for any fields that have values within the upper and lower bounds.
7. The system of claim 6 , wherein the means for searching comprises searching for any fields that have values outside of one of the upper and lower bounds.
8. The system of claim 5 , wherein each rule definition includes a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising means for removing any row including a field matching the search criteria from the input table when the row clean flag is set.
9. An article of manufacture for use performing a clean operation on an input table in a database having an input data table name, 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 find criteria, a replacement value, and an input data column in the input table;
searching, for each rule definition, the input data column for any fields that match the find criteria;
determining whether the rule definition specifies an output table; and
in response to determining that the rule definition does not specify an output table, directly inserting, for each rule definition, 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;
wherein at least one of said rule definition 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, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein searching comprises applying the multiple find criteria to each field in the order specified in the sort column.
10. The article of manufacture of claim 9 , wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein searching comprises searching for any fields that have values within the upper and lower bounds.
11. The article of manufacture of claim 10 , wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.
12. The article of manufacture of claim 9 , wherein each rule definition includes 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.
13. A memory device including a command data structure, comprising:
an input data table name parameter indicating the input table subject to the clean operation; and
at least one rule definition, wherein each rule definition includes a find criteria, a replacement value, and an input data column in the input table, wherein, for each rule definition, the input data column is searched for any fields that match the find criteria, wherein, for each rule definition, it is determined whether the rule definition specifies an output table, and in response to determining that the rule definition does not specify an output table, the replacement value is directly inserted in the fields in the input data column that match the find criteria, and 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, wherein at least one of said rule definition further includes multiple find criteria and a corresponding replacement value for each find criteria, wherein the input data column is searched by 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 replacement value corresponding to the matching find criteria is inserted into the field, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein the multiple find criteria are applied to each field in the input data column in the order specified in the sort column.
14. The memory device of claim 13 , wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein the input data column is searched for any fields that have values within the upper and lower bounds.
15. The memory device of claim 14 , wherein the input data column is searched for any fields that have values outside of one of the upper and lower bounds.
16. The memory device of claim 13 , wherein each rule definition includes a row clean flag, and wherein at least one rule definition has the row clean flag set, and wherein any row including a field matching the search criteria is removed from the input table when the row clean flag is set.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US11/382,889 US20060195488A1 (en) | 1999-09-21 | 2006-05-11 | Method, system, program and data structure for cleaning a database table |
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US09/399,694 US7120638B1 (en) | 1999-09-21 | 1999-09-21 | Method, system, program, and data structure for cleaning a database table |
US11/382,889 US20060195488A1 (en) | 1999-09-21 | 2006-05-11 | Method, system, program and data structure for cleaning a database table |
Related Parent Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US09/399,694 Division US7120638B1 (en) | 1999-09-21 | 1999-09-21 | Method, system, program, and data structure for cleaning a database table |
Publications (1)
Publication Number | Publication Date |
---|---|
US20060195488A1 true US20060195488A1 (en) | 2006-08-31 |
Family
ID=35376467
Family Applications (4)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US09/399,694 Expired - Fee Related US7120638B1 (en) | 1999-09-21 | 1999-09-21 | Method, system, program, and data structure for cleaning a database table |
US11/190,250 Expired - Fee Related US7249151B2 (en) | 1999-09-21 | 2005-07-26 | Method, system, program, and data structure for cleaning a database table |
US11/382,894 Abandoned US20060195489A1 (en) | 1999-09-21 | 2006-05-11 | Method, system, program and data structure for cleaning a database table |
US11/382,889 Abandoned US20060195488A1 (en) | 1999-09-21 | 2006-05-11 | Method, system, program and data structure for cleaning a database table |
Family Applications Before (3)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US09/399,694 Expired - Fee Related US7120638B1 (en) | 1999-09-21 | 1999-09-21 | Method, system, program, and data structure for cleaning a database table |
US11/190,250 Expired - Fee Related US7249151B2 (en) | 1999-09-21 | 2005-07-26 | Method, system, program, and data structure for cleaning a database table |
US11/382,894 Abandoned US20060195489A1 (en) | 1999-09-21 | 2006-05-11 | Method, system, program and data structure for cleaning a database table |
Country Status (1)
Country | Link |
---|---|
US (4) | US7120638B1 (en) |
Cited By (4)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20050262121A1 (en) * | 1999-09-21 | 2005-11-24 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table |
US20090157774A1 (en) * | 2007-12-18 | 2009-06-18 | International Business Machines Corporation | Character pattern-based file storage tool |
CN109977110A (en) * | 2019-04-28 | 2019-07-05 | 杭州数梦工场科技有限公司 | Data cleaning method, device and equipment |
US11263218B2 (en) * | 2016-04-26 | 2022-03-01 | Equifax Inc. | Global matching system |
Families Citing this family (25)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US7970621B2 (en) * | 2002-10-18 | 2011-06-28 | Cerner Innovation, Inc. | Automated order entry system and method |
US20040181512A1 (en) * | 2003-03-11 | 2004-09-16 | Lockheed Martin Corporation | System for dynamically building extended dictionaries for a data cleansing application |
US20040181550A1 (en) * | 2003-03-13 | 2004-09-16 | Ville Warsta | System and method for efficient adaptation of multimedia message content |
US7587396B2 (en) * | 2004-11-24 | 2009-09-08 | Oracle International Corporation | Encoding data to be sorted |
US7725468B2 (en) * | 2005-04-08 | 2010-05-25 | Oracle International Corporation | Improving efficiency in processing queries directed to static data sets |
US7634495B2 (en) * | 2005-05-06 | 2009-12-15 | Microsoft Corporation | System of multi-level defaults in transaction entries |
US7860841B2 (en) * | 2005-09-09 | 2010-12-28 | Sap Ag | Method and apparatus to support mass changes to business objects in an integrated computer system |
US9195695B2 (en) * | 2006-09-15 | 2015-11-24 | Ibm International Group B.V. | Technique for compressing columns of data |
US7516128B2 (en) | 2006-11-14 | 2009-04-07 | International Business Machines Corporation | Method for cleansing sequence-based data at query time |
US8719233B2 (en) * | 2008-06-24 | 2014-05-06 | Emc Corporation | Generic method and apparatus for database sanitizing |
CA2744881C (en) | 2008-12-02 | 2020-03-10 | Ab Initio Technology Llc | Mapping instances of a dataset within a data management system |
US8825695B2 (en) * | 2009-09-16 | 2014-09-02 | Ab Initio Technology Llc | Mapping dataset elements |
US20120089604A1 (en) * | 2010-10-08 | 2012-04-12 | Jocelyn Siu Luan Hamilton | Computer-Implemented Systems And Methods For Matching Records Using Matchcodes With Scores |
US9977659B2 (en) | 2010-10-25 | 2018-05-22 | Ab Initio Technology Llc | Managing data set objects |
KR101872748B1 (en) | 2011-01-14 | 2018-06-29 | 아브 이니티오 테크놀로지 엘엘시 | Managing changes to collections of data |
US10489360B2 (en) * | 2012-10-17 | 2019-11-26 | Ab Initio Technology Llc | Specifying and applying rules to data |
WO2016011442A1 (en) | 2014-07-18 | 2016-01-21 | Ab Initio Technology Llc | Managing lineage information |
US9626393B2 (en) | 2014-09-10 | 2017-04-18 | Ab Initio Technology Llc | Conditional validation rules |
CN104572867A (en) * | 2014-12-18 | 2015-04-29 | 上海新炬网络信息技术有限公司 | Data processing method based on Oracle |
US11907316B2 (en) * | 2016-08-15 | 2024-02-20 | Richard S. Brown | Processor-implemented method, computing system and computer program for invoking a search |
US11860855B1 (en) * | 2017-06-23 | 2024-01-02 | Amazon Technologies, Inc. | Storage service supporting data transformations |
CN108829782B (en) * | 2018-05-31 | 2023-08-25 | 平安科技(深圳)有限公司 | Data table cleaning method, server and computer readable storage medium |
CN111694824A (en) * | 2020-05-25 | 2020-09-22 | 智强通达科技(北京)有限公司 | Method for mapping and cleaning oil data chain |
CN113392099A (en) * | 2021-07-01 | 2021-09-14 | 苏州维众数据技术有限公司 | Automatic data cleaning method |
CN116450653B (en) * | 2023-06-09 | 2023-08-25 | 浙江大学 | Completion method and device for supply chain data loss |
Citations (52)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US4876733A (en) * | 1986-10-31 | 1989-10-24 | International Business Machines Corporation | Method for performing morphic transformations on image data in a general purpose computer |
US4930071A (en) * | 1987-06-19 | 1990-05-29 | Intellicorp, Inc. | Method for integrating a knowledge-based system with an arbitrary database system |
US5113342A (en) * | 1989-04-26 | 1992-05-12 | International Business Machines Corporation | Computer method for executing transformation rules |
US5196924A (en) * | 1991-07-22 | 1993-03-23 | International Business Machines, Corporation | Look-up table based gamma and inverse gamma correction for high-resolution frame buffers |
US5226158A (en) * | 1989-05-24 | 1993-07-06 | International Business Machines Corporation | Method and apparatus for maintaining referential integrity within a relational database |
US5282147A (en) * | 1991-08-02 | 1994-01-25 | International Business Machines Corporation | Method and apparatus for optimizing a logic network |
US5299304A (en) * | 1991-04-16 | 1994-03-29 | International Business Machines Corporation | Method and apparatus for identifying multiple stage document format transformations |
US5321606A (en) * | 1987-05-19 | 1994-06-14 | Hitachi, Ltd. | Data transforming method using externally provided transformation rules |
US5321797A (en) * | 1990-06-11 | 1994-06-14 | Eastman Kodak Company | Apparatus and method for performing coordinate transformation employing stored values and interpolation |
US5367675A (en) * | 1991-12-13 | 1994-11-22 | International Business Machines Corporation | Computer automated system and method for optimizing the processing of a query in a relational database system by merging subqueries with the query |
US5546576A (en) * | 1995-02-17 | 1996-08-13 | International Business Machines Corporation | Query optimizer system that detects and prevents mutating table violations of database integrity in a query before execution plan generation |
US5548754A (en) * | 1995-02-07 | 1996-08-20 | International Business Machines Corporation | Optimization of SQL queries using early-out join transformations |
US5548749A (en) * | 1993-10-29 | 1996-08-20 | Wall Data Incorporated | Semantic orbject modeling system for creating relational database schemas |
US5548755A (en) * | 1995-02-17 | 1996-08-20 | International Business Machines Corporation | System for optimizing correlated SQL queries in a relational database using magic decorrelation |
US5560005A (en) * | 1994-02-25 | 1996-09-24 | Actamed Corp. | Methods and systems for object-based relational distributed databases |
US5584024A (en) * | 1994-03-24 | 1996-12-10 | Software Ag | Interactive database query system and method for prohibiting the selection of semantically incorrect query parameters |
US5588150A (en) * | 1994-09-29 | 1996-12-24 | International Business Machines Corporation | Push down optimization in a distributed, multi-database system |
US5590324A (en) * | 1995-02-07 | 1996-12-31 | International Business Machines Corporation | Optimization of SQL queries using universal quantifiers, set intersection, and max/min aggregation in the presence of nullable columns |
US5594899A (en) * | 1989-09-01 | 1997-01-14 | Amdahl Corporation | Operating system and data base having an access structure formed by a plurality of tables |
US5598559A (en) * | 1994-07-01 | 1997-01-28 | Hewlett-Packard Company | Method and apparatus for optimizing queries having group-by operators |
US5615361A (en) * | 1995-02-07 | 1997-03-25 | International Business Machines Corporation | Exploitation of uniqueness properties using a 1-tuple condition for the optimization of SQL queries |
US5664172A (en) * | 1994-07-19 | 1997-09-02 | Oracle Corporation | Range-based query optimizer |
US5687362A (en) * | 1995-01-30 | 1997-11-11 | International Business Machines Corporation | Enumerating projections in SQL queries containing outer and full outer joins in the presence of inner joins |
US5694591A (en) * | 1995-05-02 | 1997-12-02 | Hewlett Packard Company | Reducing query response time using tree balancing |
US5701454A (en) * | 1995-01-17 | 1997-12-23 | International Business Machines Corporation | Simplification of SQL queries using generalized inference propagation and generalized transitive closure |
US5724570A (en) * | 1995-06-07 | 1998-03-03 | Tandem Computers Incorporated | Method and apparatus for a complete SQL subquery elimination process |
US5737600A (en) * | 1994-09-12 | 1998-04-07 | International Business Machines Corporation | Method and system for log management in a coupled data processing system |
US5737592A (en) * | 1995-06-19 | 1998-04-07 | International Business Machines Corporation | Accessing a relational database over the Internet using macro language files |
US5742806A (en) * | 1994-01-31 | 1998-04-21 | Sun Microsystems, Inc. | Apparatus and method for decomposing database queries for database management system including multiprocessor digital data processing system |
US5765167A (en) * | 1994-12-26 | 1998-06-09 | Casio Computer Co., Ltd. | Data file update processing apparatus |
US5873075A (en) * | 1997-06-30 | 1999-02-16 | International Business Machines Corporation | Synchronization of SQL actions in a relational database system |
US5903753A (en) * | 1995-08-18 | 1999-05-11 | International Business Machines Corporation | Name space registry with backward compatibility for older applications |
US5905982A (en) * | 1997-04-04 | 1999-05-18 | International Business Machines Corporation | Handling null values in SQL queries over object-oriented data |
US5953534A (en) * | 1997-12-23 | 1999-09-14 | University Of Washington | Environment manipulation for executing modified executable and dynamically-loaded library files |
US5987474A (en) * | 1995-08-04 | 1999-11-16 | Aircraft Technical Publishers | Computer aided maintenance and repair information system for equipment subject to regulatory compliance |
US6014670A (en) * | 1997-11-07 | 2000-01-11 | Informatica Corporation | Apparatus and method for performing data transformations in data warehousing |
US6014656A (en) * | 1996-06-21 | 2000-01-11 | Oracle Corporation | Using overlapping partitions of data for query optimization |
US6122644A (en) * | 1998-07-01 | 2000-09-19 | Microsoft Corporation | System for halloween protection in a database system |
US6178421B1 (en) * | 1998-06-05 | 2001-01-23 | International Business Machines Corporation | Method of performing parallel cleanup of segments of a lock structure |
US6185552B1 (en) * | 1998-03-19 | 2001-02-06 | 3Com Corporation | Method and apparatus using a binary search engine for searching and maintaining a distributed data structure |
US6266663B1 (en) * | 1997-07-10 | 2001-07-24 | International Business Machines Corporation | User-defined search using index exploitation |
US6282540B1 (en) * | 1999-02-26 | 2001-08-28 | Vicinity Corporation | Method and apparatus for efficient proximity searching |
US6298342B1 (en) * | 1998-03-16 | 2001-10-02 | Microsoft Corporation | Electronic database operations for perspective transformations on relational tables using pivot and unpivot columns |
US6308177B1 (en) * | 1996-10-25 | 2001-10-23 | Vijaya S. Israni | System and method for use and storage of geographic data on physical media |
US6370524B1 (en) * | 1999-04-02 | 2002-04-09 | Oracle Corp. | System and method for processing queries having an inner query block containing a grouping operator |
US6370522B1 (en) * | 1999-03-18 | 2002-04-09 | Oracle Corporation | Method and mechanism for extending native optimization in a database system |
US6523040B1 (en) * | 1999-06-24 | 2003-02-18 | Ibm Corporation | Method and apparatus for dynamic and flexible table summarization |
US6604095B1 (en) * | 1999-09-21 | 2003-08-05 | International Business Machines Corporation | Method, system, program, and data structure for pivoting columns in a database table |
US6748389B1 (en) * | 1999-09-21 | 2004-06-08 | International Business Machines Corporation | Method, system, and program for inverting columns in a database table |
US6920443B1 (en) * | 1999-09-21 | 2005-07-19 | International Business Machines, Corporation | Method, system, program, and data structure for transforming database tables |
US6965888B1 (en) * | 1999-09-21 | 2005-11-15 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table using a look-up table |
US20050262121A1 (en) * | 1999-09-21 | 2005-11-24 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table |
Family Cites Families (5)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
JPH07182179A (en) | 1993-12-24 | 1995-07-21 | Fuji Xerox Co Ltd | Object oriented data base managing device |
JPH07244603A (en) | 1994-03-03 | 1995-09-19 | Nippon Telegr & Teleph Corp <Ntt> | Data base reconstructing method |
JPH08241330A (en) | 1995-03-07 | 1996-09-17 | Nippon Telegr & Teleph Corp <Ntt> | Data base reconstituting method |
WO1996036003A1 (en) | 1995-05-10 | 1996-11-14 | Minnesota Mining And Manufacturing Company | Method for transforming and storing data for search and display and a searching system utilized therewith |
JPH09179882A (en) | 1995-12-27 | 1997-07-11 | Fujitsu Ltd | Data base access system by cpu |
-
1999
- 1999-09-21 US US09/399,694 patent/US7120638B1/en not_active Expired - Fee Related
-
2005
- 2005-07-26 US US11/190,250 patent/US7249151B2/en not_active Expired - Fee Related
-
2006
- 2006-05-11 US US11/382,894 patent/US20060195489A1/en not_active Abandoned
- 2006-05-11 US US11/382,889 patent/US20060195488A1/en not_active Abandoned
Patent Citations (58)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US4876733A (en) * | 1986-10-31 | 1989-10-24 | International Business Machines Corporation | Method for performing morphic transformations on image data in a general purpose computer |
US5321606A (en) * | 1987-05-19 | 1994-06-14 | Hitachi, Ltd. | Data transforming method using externally provided transformation rules |
US4930071A (en) * | 1987-06-19 | 1990-05-29 | Intellicorp, Inc. | Method for integrating a knowledge-based system with an arbitrary database system |
US5113342A (en) * | 1989-04-26 | 1992-05-12 | International Business Machines Corporation | Computer method for executing transformation rules |
US5226158A (en) * | 1989-05-24 | 1993-07-06 | International Business Machines Corporation | Method and apparatus for maintaining referential integrity within a relational database |
US5596752A (en) * | 1989-09-01 | 1997-01-21 | Amdahl Corporation | System for creating, editing, displaying, and executing rules-based programming language rules having action part subsets for both true and false evaluation of the conditional part |
US5594899A (en) * | 1989-09-01 | 1997-01-14 | Amdahl Corporation | Operating system and data base having an access structure formed by a plurality of tables |
US5321797A (en) * | 1990-06-11 | 1994-06-14 | Eastman Kodak Company | Apparatus and method for performing coordinate transformation employing stored values and interpolation |
US5299304A (en) * | 1991-04-16 | 1994-03-29 | International Business Machines Corporation | Method and apparatus for identifying multiple stage document format transformations |
US5196924A (en) * | 1991-07-22 | 1993-03-23 | International Business Machines, Corporation | Look-up table based gamma and inverse gamma correction for high-resolution frame buffers |
US5282147A (en) * | 1991-08-02 | 1994-01-25 | International Business Machines Corporation | Method and apparatus for optimizing a logic network |
US5367675A (en) * | 1991-12-13 | 1994-11-22 | International Business Machines Corporation | Computer automated system and method for optimizing the processing of a query in a relational database system by merging subqueries with the query |
US5548749A (en) * | 1993-10-29 | 1996-08-20 | Wall Data Incorporated | Semantic orbject modeling system for creating relational database schemas |
US5742806A (en) * | 1994-01-31 | 1998-04-21 | Sun Microsystems, Inc. | Apparatus and method for decomposing database queries for database management system including multiprocessor digital data processing system |
US5560005A (en) * | 1994-02-25 | 1996-09-24 | Actamed Corp. | Methods and systems for object-based relational distributed databases |
US5724575A (en) * | 1994-02-25 | 1998-03-03 | Actamed Corp. | Method and system for object-based relational distributed databases |
US5584024A (en) * | 1994-03-24 | 1996-12-10 | Software Ag | Interactive database query system and method for prohibiting the selection of semantically incorrect query parameters |
US5598559A (en) * | 1994-07-01 | 1997-01-28 | Hewlett-Packard Company | Method and apparatus for optimizing queries having group-by operators |
US5664172A (en) * | 1994-07-19 | 1997-09-02 | Oracle Corporation | Range-based query optimizer |
US5737600A (en) * | 1994-09-12 | 1998-04-07 | International Business Machines Corporation | Method and system for log management in a coupled data processing system |
US5588150A (en) * | 1994-09-29 | 1996-12-24 | International Business Machines Corporation | Push down optimization in a distributed, multi-database system |
US5590321A (en) * | 1994-09-29 | 1996-12-31 | International Business Machines Corporation | Push down optimization in a distributed, multi-database system |
US5765167A (en) * | 1994-12-26 | 1998-06-09 | Casio Computer Co., Ltd. | Data file update processing apparatus |
US5701454A (en) * | 1995-01-17 | 1997-12-23 | International Business Machines Corporation | Simplification of SQL queries using generalized inference propagation and generalized transitive closure |
US5687362A (en) * | 1995-01-30 | 1997-11-11 | International Business Machines Corporation | Enumerating projections in SQL queries containing outer and full outer joins in the presence of inner joins |
US5590324A (en) * | 1995-02-07 | 1996-12-31 | International Business Machines Corporation | Optimization of SQL queries using universal quantifiers, set intersection, and max/min aggregation in the presence of nullable columns |
US5548758A (en) * | 1995-02-07 | 1996-08-20 | International Business Machines Corporation | Optimization of SQL queries using early-out join transformations of column-bound relational tables |
US5548754A (en) * | 1995-02-07 | 1996-08-20 | International Business Machines Corporation | Optimization of SQL queries using early-out join transformations |
US5615361A (en) * | 1995-02-07 | 1997-03-25 | International Business Machines Corporation | Exploitation of uniqueness properties using a 1-tuple condition for the optimization of SQL queries |
US5548755A (en) * | 1995-02-17 | 1996-08-20 | International Business Machines Corporation | System for optimizing correlated SQL queries in a relational database using magic decorrelation |
US5546576A (en) * | 1995-02-17 | 1996-08-13 | International Business Machines Corporation | Query optimizer system that detects and prevents mutating table violations of database integrity in a query before execution plan generation |
US5694591A (en) * | 1995-05-02 | 1997-12-02 | Hewlett Packard Company | Reducing query response time using tree balancing |
US5724570A (en) * | 1995-06-07 | 1998-03-03 | Tandem Computers Incorporated | Method and apparatus for a complete SQL subquery elimination process |
US5737592A (en) * | 1995-06-19 | 1998-04-07 | International Business Machines Corporation | Accessing a relational database over the Internet using macro language files |
US5987474A (en) * | 1995-08-04 | 1999-11-16 | Aircraft Technical Publishers | Computer aided maintenance and repair information system for equipment subject to regulatory compliance |
US5903753A (en) * | 1995-08-18 | 1999-05-11 | International Business Machines Corporation | Name space registry with backward compatibility for older applications |
US6014656A (en) * | 1996-06-21 | 2000-01-11 | Oracle Corporation | Using overlapping partitions of data for query optimization |
US6308177B1 (en) * | 1996-10-25 | 2001-10-23 | Vijaya S. Israni | System and method for use and storage of geographic data on physical media |
US5905982A (en) * | 1997-04-04 | 1999-05-18 | International Business Machines Corporation | Handling null values in SQL queries over object-oriented data |
US5873075A (en) * | 1997-06-30 | 1999-02-16 | International Business Machines Corporation | Synchronization of SQL actions in a relational database system |
US6266663B1 (en) * | 1997-07-10 | 2001-07-24 | International Business Machines Corporation | User-defined search using index exploitation |
US6014670A (en) * | 1997-11-07 | 2000-01-11 | Informatica Corporation | Apparatus and method for performing data transformations in data warehousing |
US5953534A (en) * | 1997-12-23 | 1999-09-14 | University Of Washington | Environment manipulation for executing modified executable and dynamically-loaded library files |
US6298342B1 (en) * | 1998-03-16 | 2001-10-02 | Microsoft Corporation | Electronic database operations for perspective transformations on relational tables using pivot and unpivot columns |
US6185552B1 (en) * | 1998-03-19 | 2001-02-06 | 3Com Corporation | Method and apparatus using a binary search engine for searching and maintaining a distributed data structure |
US6178421B1 (en) * | 1998-06-05 | 2001-01-23 | International Business Machines Corporation | Method of performing parallel cleanup of segments of a lock structure |
US6122644A (en) * | 1998-07-01 | 2000-09-19 | Microsoft Corporation | System for halloween protection in a database system |
US6282540B1 (en) * | 1999-02-26 | 2001-08-28 | Vicinity Corporation | Method and apparatus for efficient proximity searching |
US6370522B1 (en) * | 1999-03-18 | 2002-04-09 | Oracle Corporation | Method and mechanism for extending native optimization in a database system |
US6370524B1 (en) * | 1999-04-02 | 2002-04-09 | Oracle Corp. | System and method for processing queries having an inner query block containing a grouping operator |
US6523040B1 (en) * | 1999-06-24 | 2003-02-18 | Ibm Corporation | Method and apparatus for dynamic and flexible table summarization |
US6604095B1 (en) * | 1999-09-21 | 2003-08-05 | International Business Machines Corporation | Method, system, program, and data structure for pivoting columns in a database table |
US6748389B1 (en) * | 1999-09-21 | 2004-06-08 | International Business Machines Corporation | Method, system, and program for inverting columns in a database table |
US6920443B1 (en) * | 1999-09-21 | 2005-07-19 | International Business Machines, Corporation | Method, system, program, and data structure for transforming database tables |
US6965888B1 (en) * | 1999-09-21 | 2005-11-15 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table using a look-up table |
US20050262121A1 (en) * | 1999-09-21 | 2005-11-24 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table |
US20060195489A1 (en) * | 1999-09-21 | 2006-08-31 | International Business Machines Corporation | Method, system, program and data structure for cleaning a database table |
US7249151B2 (en) * | 1999-09-21 | 2007-07-24 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table |
Cited By (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20050262121A1 (en) * | 1999-09-21 | 2005-11-24 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table |
US20060195489A1 (en) * | 1999-09-21 | 2006-08-31 | International Business Machines Corporation | Method, system, program and data structure for cleaning a database table |
US7249151B2 (en) | 1999-09-21 | 2007-07-24 | International Business Machines Corporation | Method, system, program, and data structure for cleaning a database table |
US20090157774A1 (en) * | 2007-12-18 | 2009-06-18 | International Business Machines Corporation | Character pattern-based file storage tool |
US11263218B2 (en) * | 2016-04-26 | 2022-03-01 | Equifax Inc. | Global matching system |
CN109977110A (en) * | 2019-04-28 | 2019-07-05 | 杭州数梦工场科技有限公司 | Data cleaning method, device and equipment |
Also Published As
Publication number | Publication date |
---|---|
US7249151B2 (en) | 2007-07-24 |
US7120638B1 (en) | 2006-10-10 |
US20060195489A1 (en) | 2006-08-31 |
US20050262121A1 (en) | 2005-11-24 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US7249151B2 (en) | Method, system, program, and data structure for cleaning a database table | |
US6965888B1 (en) | Method, system, program, and data structure for cleaning a database table using a look-up table | |
US10860548B2 (en) | Generating and reusing transformations for evolving schema mapping | |
US6604095B1 (en) | Method, system, program, and data structure for pivoting columns in a database table | |
US6366917B1 (en) | Method of modifying a populated database structure by modifying metadata describing the database structure | |
US5596748A (en) | Functional compensation in a heterogeneous, distributed database environment | |
US5706509A (en) | Application independent record level synchronization | |
CN100541486C (en) | The result that the function of data is used carries out structured index | |
US20020059327A1 (en) | Method and apparatus for generating web pages from templates | |
US6920443B1 (en) | Method, system, program, and data structure for transforming database tables | |
CN101452443B (en) | Recording medium for recording logical structure model creation assistance program, logical structure model creation assistance device and logical structure model creation assistance method | |
US7653663B1 (en) | Guaranteeing the authenticity of the data stored in the archive storage | |
US8756205B2 (en) | System and method for rule-based data object matching | |
US20080201290A1 (en) | Computer-implemented methods, systems, and computer program products for enhanced batch mode processing of a relational database | |
US6748389B1 (en) | Method, system, and program for inverting columns in a database table | |
CN109299219A (en) | Data query method, apparatus, electronic equipment and computer readable storage medium | |
GB2366421A (en) | Ordering relational database operations according to referential integrity constraints | |
US6453324B1 (en) | Method for maintaining a version history of objects in a repository | |
US6484163B1 (en) | Technique for data mining of large scale relational databases using SQL | |
US7559048B1 (en) | System and method for managing objects between projects | |
US20050065947A1 (en) | Thesaurus maintaining system and method | |
US7016906B1 (en) | Data processing method and apparatus employing OLE DB and having dual schema and auto update features | |
CN112948510A (en) | Construction method of knowledge graph in media industry | |
Blanning | Data management and model management: A relational synthesis | |
March et al. | On the selection of efficient record segmentations and backup strategies for large shared databases |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
STCB | Information on status: application discontinuation |
Free format text: ABANDONED -- FAILURE TO PAY ISSUE FEE |