Automated data warehouse migration

Patent metadata

Field	Value
Publication number	US-9430505-B2
Application number	US-201113088556-A
Country	US
Kind code	B2
Filing date	Apr 18, 2011
Priority date	Apr 18, 2011
Publication date	Aug 30, 2016
Grant date	Aug 30, 2016

Abstract

Systems, apparatus, computer-readable storage media, and methods are disclosed for allowing analysis, migration, and validation of data from a source environment (such as an RDBMS system) to a target environment (such as a data warehouse (DW) appliance). In one example, a method comprises analyzing a source database, a source ETL environment, a target database, and a target ETL environment to produce configuration data, the configuration data being used for generating a mapping of the source database to a target database in the target database environment, a mapping of the source DDL code to target DDL code in the target database environment, and a mapping of source ETL code to target ETL code for the target database environment, and migrating at least one table from the source database, at least a portion of the source DDL code, and at least a portion of the source ETL code to the target database environment, where the migrating is based at least in part on the mapping generated using the configuration data.

First claim

We claim:

1. A method of migrating data from a source database environment to a target database environment, the source database environment including at least one source database, the method comprising:
with a computer, analyzing the source database environment and the target database environment, wherein the analyzing comprises extracting a validation criteria list, the validation criteria list comprising a plurality of data validation test cases;
generating based on the analyzing, a conversion inventory of the source database environment and a configuration data file identifying at least one mapping code and/or script the configuration data file for generating a mapping for converting at least one table in the source database to a format compliant with a target database in the target database environment, the conversion inventory comprising extraction, transformation, and loading (ETL) code from the source database environment;
generating a target-compliant mapping based on the at least one mapping code and/or script identified by the configuration data file and at least one ETL process characteristic associated with one or both of the source database environment and the target database environment, wherein the at least one process characteristic is stored in an ETL repository, and wherein the generating the target-compliant mapping comprises mapping one or more validation structured query language (SQL) statements based on the extracted validation criteria list; and
with the computer, migrating the at least one table from the conversion inventory of the source database to the target database environment to produce migrated data in the target database environment, wherein the migrating is performed based at least in part on the target-compliant mapping.

2. The method of claim 1 , wherein the source database environment comprises a relational database management system and the target database environment comprises a data warehouse appliance.

3. The method of claim 1 , further comprising searching the source database environment for structured query language (SQL) statements and based on the SQL statements and the target-compliant mapping, generating SQL statements compliant with the target database environment.

4. The method of claim 1 , wherein:
the analyzing comprises extracting one or more column lists from the source database environment;
the generating the target-compliant mapping comprises generating one or more structured query language (SQL) statements based on the extracted column lists and generating an SQL script with translation functions and conversion functions based on the generated SQL statements; and
the migrating comprises executing the SQL script in the target database environment.

5. The method of claim 1 , further comprising:
persisting output of a target database environment script in a computer-readable storage device to produce persisted output; and
based on the persisted output, repeating the migrating, wherein the at least one table is selected to be migrated to the target database environment based on the persisted output.

6. The method of claim 1 , further comprising validating the migrated data using one or more sets of computer-executable instructions, the computer-executable instructions being generated based at least in part on the configuration data file or the target-compliant mapping.

7. The method of claim 1 , wherein:
the analyzing comprises extracting at least one or more of the following from the source database environment to produce extracted data: a column list, data associated with a column list, or a date range;
the generating the target-compliant mapping comprises mapping one or more validation structured query language (SQL) statements based on the extracted data; and
the migrating comprises executing at least a portion of the validation SQL statements in the target database environment.

8. The method of claim 1 , further comprising storing at least a portion of the migrated data in a computer-readable storage medium.

9. The method of claim 1 , wherein the target-compliant mapping includes mappings for converting at least one or more of the following to a form compliant with the target environment: database (DB)-specific functions, custom structured query language (SQL) usage patterns, custom target loads, DB-specific join syntax, reserved syntax, DB constraints, datatypes, or data definition language (DDL) code.

10. The method according to claim 1 , wherein the analyzing comprises:
invoking using the configuration data file, a database analysis script; and
searching using the database analysis script, the source database environment for one or more of datatype usage patterns, index usage patterns, partition usage patterns, database constraint patterns, and source SQL data.

11. A computer-readable storage device comprising computer-executable instructions stored therein for causing the computer to perform a method of migrating data from a source database environment to a target database environment, the source database environment including at least one source database, the method comprising:
analyzing the source database environment and the target database environment to extract a validation criteria list and produce based on the analyzing, a conversion inventory of the source database environment and a configuration data file identifying at least one mapping code and/or script, the configuration data file for generating one or more mappings for data in the conversion inventory of the source database environment to the target database environment, wherein:
the conversion inventory comprises extraction, transformation, and loading (ETL) code from the source database environment;
the generating the one or more mappings comprises mapping one or more validation structured query language (SQL) statements based on the extracted validation criteria list; and

migrating at least a portion of the data to the target database environment, wherein the migrating is performed based at least in part on the mappings generated using the at least one mapping code and/or script identified by the produced configuration data file, wherein the migrating produces migrated data in the target database environment.

12. The computer-readable storage device of claim 11 , wherein the computer-executable instructions comprise code for:
extracting at least one or more of the following from the source database environment to produce extracted data: a column list, data associated with a column list, or a date range;
the generating the target-compliant mapping comprises mapping one or more validation structured query language (SQL) statements based on the extracted data; and
the migrating comprises executing at least a portion of the validation SQL statements in the target database environment.

13. The computer-readable storage device of claim 11 , wherein the computer-executable instructions comprise code for:
analyzing data stored in a source database in the source environment; and
migrating data from the source database to a target database in the target environment.

14. The computer-readable storage device of claim 11 , wherein the computer-executable instructions comprise code for:
analyzing Extraction, Transformation, and Loading (ETL) data stored in the source environment; and
migrating ETL data from the source environment to the target environment.

15. The computer-readable storage device of claim 11 , wherein the computer-executable instructions comprise code for:
validating at least a portion of the migrated data.

16. The computer-readable st

Assignees

• Padmanabhan Rajan
• Patki Asha Uday
• Infosys Ltd

Inventors

• Padmanabhan Rajan
• Patki Asha Uday

Classifications

• G06F16/214 Primary

  Database migration support · CPC title

• G06F17/303 Primary

  Physics · mapped topic