Modularized extraction, transformation, and loading for a database

• US 8,311,974 B2
• Filed: 02/20/2004
• Issued: 11/13/2012
• Est. Priority Date: 02/20/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gong, Yu
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US10/783,779
• Publication Number:
  US 20050187974A1
• Time in Patent Office: 3,189 Days
• Field of Search: 707/104.1, 707/100, 707/10, 707/1, 707/3, 707/7, 707/602
• US Class Current: 707/602
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Techniques exporting data and metadata from a source database environment to a target database environment are provided. The techniques include the steps of analyzing metadata that describes one or more items, the data for which is in a source database, where the one or more items include at least one of a view, a sequence, a dimension, a cube, an ETL mapping, and any database object for which the metadata is stored outside of the source and target databases. The data for each item resides in a data file associated with the source database. The data for each item is incorporated into the target database based on the metadata by providing the target database access to an incorporated data file, where the incorporated data file is the data file or a copy thereof. Techniques are also provided for exporting database data from the source database. The techniques include extracting metadata that describes one or more items, the data for which is in the source database, where the one or more items include at least one of the structures described above. The data for each item resides in a data file associated with the source database. After the exporting, a database server that manages the target database is provided access to an incorporated data file, where the incorporated data file is the data file or a copy thereof.

Claims

1. A computer implemented method comprising:

a source ETL application receiving, from a user, input that selects one or more database objects to be transported from a source database to a target database;

wherein said source database includes source database metadata that describes a structure of database objects of said source database;

wherein said source database metadata identifies a set of tablespaces that store data for the one or more database objects to be transported, and said set of tablespaces is in a format that is understandable by the target database;

wherein said source ETL application includes source ETL metadata, separate from said source database metadata, that describes said structure of said database objects of said source database;

said source ETL application causing generation of a module comprising metadata that describes a structure of said one or more database objects of said source database;

a target ETL application reading said module;

wherein said target database includes target database metadata that describes a structure of database objects of said target database;

wherein said target ETL application includes target ETL metadata, separate from said target database metadata, that describes said structure of said database objects of said target database;

wherein reading said module causes said target ETL application to perform;

modifying said target ETL metadata based on said source ETL metadata read from said module to describe said structure of said one or more database objects of said target database; and

modifying said target database metadata based on said metadata read from said module to describe a structure of said one or more database objects of said source database;

a target database system incorporating a copy of said set of tablespaces that store said data for at least one of said one or more database objects, wherein incorporating said copy of said set of tablespaces includes modifying the target database metadata to define said copy of said set of tablespaces as a set of tablespaces that are used to store said data for said at least one of said one or more database objects,

wherein the method is performed by one or more computing devices.

2. The method of claim 1, further comprising:

in response to a failure occurring during the loading of said database objects within said target database, rolling back all changes made during the loading of the database objects to the target database.

3. The method of claim 1, wherein the selected one or more database objects to be transported from a source database to a target database includes a database object that has metadata stored outside of the source database.

4. The method of claim 1, wherein generation of a module includes analyzing the source database metadata for dependencies.

5. The method of claim 4, wherein analyzing the source database metadata for dependencies includes ensuring proper order of loading of the source database metadata into the target database.

6. The method of claim 1, further comprising:

storing said module in one or more files in a source file system.

7. The method of claim 6, further comprising:

said target ETL application performing;

reading a specification containing information for how to move modules from said source file system to a target file system;

wherein said information comprises a network protocol and the location in the source file system of said one or more files; and

accessing said one or more files in a source file system based on said information.

8. The method of claim 7, wherein the network protocol is one of FTP, HTTP, HTTPS, or rsync.

9. A computer implemented method comprising:

a source external application receiving, from a user, input that selects one or more database objects, wherein said one or more database objects include an internal database object to be transported from a source database to a target database and an external database object to be transported to a target external application;

wherein said source database includes source database metadata that describes a structure of said internal database object of said source database;

wherein said source database metadata identifies a set of tablespaces that store data for the one or more database objects to be transported, and said set of tablespaces is in a format that is understandable by the target database;

wherein said source external application includes source external application metadata, separate from said source database metadata, that describes said one or more database objects;

said source external application causing generation of a module comprising metadata that describes a structure of said one or more database objects;

a target external application reading said module;

wherein said target database includes target database metadata that describes said structure of said internal database object;

wherein said target external application includes target external metadata, separate from said target database metadata, that describes said one or more database objects; and

wherein said reading said module causes said target external application to perform loading said one or more database objects within said target database and said target external application, wherein loading includes;

modifying said target external metadata to describe said one or more database objects; and

modifying said target database metadata to define a copy of said set of tablespaces as a set of tablespaces that are used to store said data for at least one of said one or more database objects;

wherein the method is performed by one or more computing devices.

10. The method of claim 9, wherein generation of a module includes analyzing the source database metadata for dependencies.

11. The method of claim 10, wherein analyzing the source database metadata for dependencies includes ensuring proper order of loading of the source database metadata into the target database.

12. The method of claim 9, further comprising:

storing said module in one or more files in a source file system.

13. The method of claim 12, further comprising:

said target ETL application performing;

reading a specification containing information for how to move modules from said source file system to a target file system; and

wherein said information comprises a network protocol and the location of said one or more files; and

accessing said one or more files in a source file system based on said information.

14. The method of claim 13, wherein the network protocol is one of FTP, HTTP, HTTPS, or rsync.

15. The method of claim 9, further comprising:

in response to a failure occurring during the loading of said database objects within said target database, rolling back all changes made during the loading of the database objects to the target database.

16. The method of claim 9, wherein said one or more database objects to be transported from a source database to a target database includes a database object that has metadata stored outside of the source database.

17. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform:

a source ETL application receiving, from a user, input that selects one or more database objects to be transported from a source database to a target database;

wherein said source database includes source database metadata that describes a structure of database objects of said source database;

wherein said source database metadata identifies a set of tablespaces that store data for the one or more database objects to be transported, and said set of tablespaces is in a format that is understandable by the target database;

wherein said source ETL application includes source ETL metadata, separate from said source database metadata, that describes said structure of said database objects of said source database;

said source ETL application causing generation of a module comprising metadata that describes the structure of said one or more database objects of said source database;

a target ETL application reading said module;

wherein said target database includes target database metadata that describes a structure of database objects of said target database;

wherein said target ETL application includes target ETL metadata, separate from said target database metadata, that describes said structure of said database objects of said target database;

wherein reading said module causes said target ETL application to perform;

modifying said target ETL metadata based on said source ETL metadata read from said module to describe said structure of said one or more database objects of said target database; and

modifying said target database metadata based on said metadata read from said module to describe the structure of said one or more database objects of said source database;

a target database system incorporating a copy of said set of tablespaces that store said data for at least one of said one or more database objects, wherein incorporating said copy of said set of tablespaces includes modifying the target database metadata to define said copy of said set of tablespaces as a set of tablespaces that are used to store said data for said at least one of said one or more database objects.

18. The non-transitory computer-readable storage medium of claim 17,

further comprising instructions which, when executed by one or more processors, causes the one or more processors to perform:

in response to a failure occurring during the loading of said database objects within said target database, rolling back all changes made during the loading of the database objects to the target database.

19. The non-transitory computer-readable storage medium of claim 17,

wherein the selected one or more database objects to be transported from a source database to a target database includes a database object that has metadata stored outside of the source database.

20. The non-transitory computer-readable storage medium of claim 17,

wherein generation of a module includes analyzing the source database metadata for dependencies.

21. The non-transitory computer-readable storage medium of claim 20,

wherein analyzing the source database metadata for dependencies includes ensuring proper order of loading of the source database metadata into the target database.

22. The non-transitory computer-readable storage medium of claim 17,

further comprising instructions which, when executed by one or more processors, causes the one or more processors to perform:

storing said module in one or more files in a source file system.

23. The non-transitory computer-readable storage medium of claim 22,

further comprising instructions which, when executed by one or more processors, causes the one or more processors to perform:

said target ETL application reading a specification containing information for how to move modules from said source file system to a target file system;

wherein said information comprises a network protocol and the location in the source file system of said one or more files; and

said target ETL application accessing said one or more files in a source file system based on said information.

24. The non-transitory computer-readable storage medium of claim 23,

wherein the network protocol is one of FTP, HTTP, HTTPS, or rsync.

25. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform:

a source external application receiving, from a user, input that selects one or more database objects, wherein said one or more database objects include an internal database object to be transported from a source database to a target database and an external database object to be transported to a target external application;

wherein said source database includes source database metadata that describes a structure of said internal database object of said source database;

wherein said source database metadata identifies a set of tablespaces that store data for the one or more database objects to be transported, and said set of tablespaces is in a format that is understandable by the target database;

wherein said source external application includes source external application metadata, separate from said source database metadata, that describes said one or more database objects;

said source external application causing generation of a module comprising metadata that describes a structure of said one or more database objects;

a target external application reading said module;

wherein said target database includes target database metadata that describes said structure of said internal database object;

wherein said target external application includes target external metadata, separate from said target database metadata, that describes said one or more database objects; and

wherein said reading said module causes said target external application to perform loading said one or more database objects within said target database and said target external application, wherein loading includes;

modifying said target external metadata to describe said one or more database objects; and

modifying said target database metadata to define a copy of said set of tablespaces as a set of tablespaces that are used to store said data for at least one of said one or more database objects.

26. The non-transitory computer-readable storage medium of claim 25,

wherein generation of a module includes analyzing the source database metadata for dependencies.

27. The non-transitory computer-readable storage medium of claim 26,

wherein analyzing the source database metadata for dependencies includes ensuring proper order of loading of the source database metadata into the target database.

28. The non-transitory computer-readable storage medium of claim 25,

further comprising instructions which, when executed by one or more processors, causes the one or more processors to perform:

storing said module in one or more files in a source file system.

29. The non-transitory computer-readable storage medium of claim 28,

further comprising instructions which, when executed by one or more processors, causes the one or more processors to perform:

said target ETL application reading a specification containing information for how to move modules from said source file system to a target file system;

wherein said information comprises a network protocol and the location of said one or more files; and

said target ETL application accessing said one or more files in a source file system based on said information.

30. The non-transitory computer-readable storage medium of claim 29,

wherein the network protocol is one of FTP, HTTP, HTTPS, or rsync.

31. The non-transitory computer-readable storage medium of claim 25,

further comprising instructions which, when executed by one or more processors, causes the one or more processors to perform:

in response to a failure occurring during the loading of said database objects within said target database, rolling back all changes made during the loading of the database objects to the target database.

32. The non-transitory computer-readable storage medium of claim 25, wherein said one or more database objects to be transported from a source database to a target database includes a database object that has metadata stored outside of the source database.