Apparatus, systems and methods for relational database replication and proprietary data transformation

• US 7,555,493 B2
• Filed: 03/08/2005
• Issued: 06/30/2009
• Est. Priority Date: 03/08/2004
• Status: Active Grant

• Current Assignee: Transreplicator Incorporated
• Original Assignee: Transreplicator Incorporated
• Inventors: Goulart, Robert F., Khayter, Mark
• Primary Examiner(s): Vital; Pierre M
• Assistant Examiner(s): LIAO, JASON G
• Application Number:
  US11/075,514
• Publication Number:
  US 20050198074A1
• Time in Patent Office: 1,575 Days
• Field of Search: 707/102, 707/204
• US Class Current: 1/1
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

The present invention provides data replication and transformation. The invention system creates a replica version of a relational database based upon information obtained from a source database. The system populates the replica database with all or a portion of the data from the source database and transforms the data during the extraction process using a standard set of algorithms. This keeps any proprietary information in the source database from reaching the replica database while still retaining that source database'"'"'s referential integrity. The system supports the process of reloading the replica databases interactively or on a scheduled basis running as a batch process. Included is a global network component that coordinates the reload among the various groups of users accessing the replica database and supports the addition of new or enhanced transform algorithms through the present invention web portal, as they become available.

Claims

1. Computer apparatus for creating a destination database from a given source database, comprising:

an extractor for extracting catalog information and data from a given source database having certain data indicated as proprietary data to create a destination database, the source and destination databases being relational databases, and the extractor configured to filter the data extracted from the source database based on configuration values specific to a particular extract process that indicate a schema-level filter, which specifies a size of the destination database as a specific size in megabytes or as a percentage of a size of the source database;

driving tables, which are an optimum number of tables in the source database for which filtering is to be applied to limit the data extracted from the tables, the filtering of the driving tables driving a selection from the remainder of the tables in the source database based on relationships of the driving tables with other tables in the source database, the relationships being represented by a hierarchy of tables;

the extractor configured to (i) build the hierarchy of tables based on physical referential integrity defined by foreign key relationships among the tables and based on logical relationships among the tables, (ii) designate tables representing leaf-level tables in the hierarchy of tables as the driving tables, and (iii) assign to each table a percent filter indicating a percentage of the rows in the table that are to be extracted, the value of the percentage being equal to the percentage of the size of the source database in an event the schema-level filter so specifies or being a calculated percentage that is determined in an event the schema-level filter specifies a specific size in megabytes; and

a data transformer coupled to the extractor for preventing the proprietary data from being propagated from the source database to the destination database during extraction such that the destination database is populated by a referentially intact set of filtered data from the source database that is free of proprietary information by applying to data of a foreign key of one table a same transformation applied to data of a related primary key of another table, the foreign and related primary keys and application of the same transformation to the data thereof being determined through traversal of the hierarchy of tables.

2. Computer apparatus as claimed in claim 1 wherein the extractor utilizes system functions and native database utilities to perform the extraction from the source database and load into the destination database.

3. Computer apparatus as claimed in claim 1 wherein:

the source database is a relational database; and

the extractor is implemented as dynamically generated SQL queries.

4. Computer apparatus as claimed in claim 3 wherein the extractor formats results of the SQL queries into working files and then loads the formatted results from the working files, using native bulk extraction utilities, into the destination database using a bulk loader native to the source database.

5. Computer apparatus as claimed in claim 1 wherein the configuration values indicate

a custom filter, which consists of specified table-level filters for one or more tables.

6. Computer apparatus as claimed in claim 5 wherein

the driving tables are determined as those tables for which filter criteria is specified in the form of any combination ofWHERE clause, to be incorporated into the SQL statement selecting rows from that table;

or a

2. COUNT, indicating the number of rows to be selected from that table;

or a

3. PERCENT, indicating what percent of the rows in the table are to be selected;

or a

4. ALL indicator, specifying all rows will be selected from that table;

or a

5. NONE indicator, specifying no rows will be selected from that table;

or a

6. AS-NEEDED indicator, specifying that only rows related to data in other tables will be selected.

7. Computer apparatus as claimed in claim 1 wherein the extractor employs an algorithm allowing a percentage of information to be extracted from a particular table in the source database and having the distribution of data contained within that extracted subset of data mimic the distribution of data contained within the source database table, by

Creating a logical selection of the set of all unique identifiers in the source table in an ascending order and assigning them a corresponding row number, beginning with one and ending with the number of rows in the source table;

and

Selecting a final result set from that table by using a SQL query that employs the assigned row number to determine if the row should be selected.

8. Computer apparatus as claimed in claim 1 wherein the extractor further multiplies the size of a database using data multiplication logic to create records having unique identifiers in predetermined tables.

9. Computer apparatus as claimed in claim 1 wherein the extractor further builds the set of table hierarchies and associated attributes that define and expedite data extraction.

10. Computer apparatus as claimed in claim 1 wherein the extractor traverses the table hierarchies of the source database in such a manner as to make efficient use of system resources on a server hosting the source database and in such a manner as to expedite a bulk extraction of a referentially intact dataset.

11. Computer apparatus as claimed in claim 1 wherein:

the proprietary data is any of credit/debit card numbers, people'"'"'s names, social security numbers, phone numbers or other columns of information contained within the source database that the owner considers proprietary; and

the data transformer substitutes random generated data for the proprietary data such that the extractor uses the random or formula-based generated data to populate the destination database, based upon the configuration parameters of a specific extract process, that column type in the source database, and the table relationships associated with the source database is maintained.

12. Computer apparatus as claimed in claim 11 wherein the data transformer utilizes functions native to the source database and coding techniques to generate masking data for the extraction of transformed data.

13. Computer apparatus as claimed in claim 1 wherein the data transformer employs standardized data transform algorithms.

14. A computer system for creating a destination database from a given source database, comprising:

a navigator for viewing database information, configuring database level parameters and defining configuration parameters associated with a specific extract process that indicate a schema-level filter, which specifies a size of the destination database as a specific size in megabytes or as a percentage of a size of the source database;

an extraction assembly for carrying out the extract process to extract data from the source database and use said extracted data to populate and produce the destination database, the source and destination databases being relational databases, and the extraction assembly configured to filter the data extracted from the source database based on the configuration parameters;

driving tables, which are an optimum number of tables in the source database for which filtering is to be applied to limit the data extracted from the tables, the filtering of the driving tables driving a selection from the remainder of the tables in the source database based on relationships of the driving tables with other tables in the source database, the relationships being represented by a hierarchy of tables; and

the extraction assembly configured to (i) build the hierarchy of tables based on physical referential integrity defined by foreign key relationships among the tables and based on logical relationships among the tables, (ii) designate tables representing leaf-level tables in the hierarchy of tables as the driving tables, and (iii) assign to each table a percent filter indicating a percentage of the rows in the table that are to be extracted, the value of the percentage being equal to the percentage of the size of the source database in an event the schema-level filter so specifies or being a calculated percentage that is determined in an event the schema-level filter specifies a specific size in megabytes.

15. Computer system as claimed in claim 14 wherein the navigator, which operates across a variety of RDBMS platforms, provides the user with the ability to

a. View database configuration parameters and database objects;

and

b. Define database level configuration parameters, such as connection information, standard data transforms to be applied to all extracts from a specific source database and logical foreign keys, which are used to supplement the existing physical foreign keys for a particular source database; and

c. Define extract process configuration parameters such as a source database, destination database, type of extract, extract filters, and other parameters necessary to define the process for copying a subset or superset of a database from one database server to another.

16. A computer system as claimed in claim 15 wherein the SDTA are applied to each extract process definition, which defines how data is extracted from a specific database, in order to ensure that all data extracted from that database as part of an extract process will incorporate the set of data transformations defined for the database.

17. A computer system as claimed in claim 14 wherein the computer system is an application in a global network, and enables a user to coordinate and schedule the reload of databases amongst various users.

18. A method for creating a destination database from a given source database, comprising the computer implemented steps of:

a. extracting catalog information and data from the given source database to create the destination database, the source and destination databases being relational databases, and the source database having certain data indicated as proprietary data;

filtering the data extracted from the source database based on configuration values specific to a particular extract process that indicate a schema-level filter, which specifies a size of the destination database as a specific size in megabytes or as a percentage of a size of the source database;

automatically selecting driving tables, which are an optimum number of tables in the source database for which filtering is to be applied to limit the data extracted from the tables, the filtering of the driving tables driving a selection from the remainder of the tables in the source database based on relationships of the driving tables with other tables in the source database, the relationships being represented by a hierarchy of tables, and the automatic selection of the driving tables including;

(i) building the hierarchy of tables based on physical referential integrity defined by foreign key relationships among the tables and based on logical relationships among the tables;

(ii) designating tables representing leaf-level tables in the hierarchy of tables as the driving tables; and

(iii) assigning to each table a percent filter indicating a percentage of the rows in the table that are to be extracted, the value of the percentage being equal to the percentage of the size of the source database in an event the schema-level filter so specifies or being a calculated percentage that is determined in an event the schema-level filter specifies a specific size in megabytes; and

b. preventing the proprietary data from being propagated from the source database to the destination database during extraction by applying to data of a foreign key of one table a same transformation applied to data of a related primary key of another table, the foreign and related primary keys and application of the same transformation to the data thereof being determined through traversal of the hierarchy of tables;

c. the steps of extracting and preventing providing the destination database to be populated by a referentially intact set of filtered data free of proprietary information.

19. A method as claimed in claim 18 wherein:

a. the proprietary data may contain any of credit/debit card numbers, people names, social security numbers and phone numbers or other columns of information contained within the source database that the owner considers proprietary; and

b. the step of preventing includes performing a data transformation that substitutes random generated data for the proprietary data such that the destination database is populated with the random generated data instead of the proprietary data and referential integrity of the destination database is maintained.

20. A method as claimed in claim 19 wherein the data transformation employs standardized data transformation algorithms.