Extensible extract, transform and load (ETL) framework

• US 10,089,371 B2
• Filed: 12/29/2015
• Issued: 10/02/2018
• Est. Priority Date: 12/29/2015
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Belyy, Andrey, Dash, Ajit
• Primary Examiner(s): Spieler, William
• Application Number:
  US14/983,467
• Publication Number:
  US 20170185661A1
• Time in Patent Office: 1,008 Days
• CPC Class Codes: G06F 16/116 Details of conversion of file system types or formats
• CPC Class Codes: G06F 16/119 Details of migration of file systems migration mechanisms in storage systems G06F3/0647
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: H04L 67/06 specially adapted for file transfer, e.g. file transfer protocol [FTP]
• CPC Class Codes: H05K 999/99 dummy group

Abstract

Described herein is an extensible extract, transform and load (ETL) framework. In accordance with one aspect of the framework, a file location object and a corresponding file format object are retrieved from a repository. The file location object stores file location parameters that specify a remote file system directory and a local file system directory, while the file format object stores location configuration parameters that specify a data file and the file location object. The data file may be transferred from the remote file system directory to the local file system directory according to the file location parameters and the location configuration parameters. An extract, transform and load (ETL) job may then be performed on the data file to generate an output file.

Claims

1. A system, comprising:

a remote server including a remote file system directory with at least one data file; and

a local server communicatively coupled to the remote server, including

a non-transitory memory device that stores computer-readable program code, a repository and a local file system directory, and

a processor in communication with the memory device, the processor being operative with the computer-readable program code to

receive, via a user interface, one or more user configurations of a file location object, wherein the file location object stores file location parameters that specify the remote file system directory and the local file system directory,

generate the file location object based on the one or more user configurations and store the file location object in the repository,

retrieve the file location object and a corresponding file format object from the repository, wherein the file format object stores location configuration parameters that specify a data file and the file location object,

transfer the data file from the remote file system directory to the local file system directory by using the file location parameters and the location configuration parameters, and

perform an extract, transform and load (ETL) job on the data file according to parameters in the file format object to generate an output file.

2. The system of claim 1 wherein the remote server comprises a secure host.

3. The system of claim 1 wherein the remote server comprises an unsecure host.

4. The system of claim 1 wherein the file location parameters comprise a file access protocol type.

5. The system of claim 1 wherein the file location parameters comprise a file access authentication type and authentication data.

6. A method, comprising:

retrieving, by a processor device, a file location object and a corresponding file format object from a repository, wherein the file location object stores file location parameters that specify a remote file system directory and a local file system directory, wherein the file format object stores location configuration parameters that specify a data file and the file location object;

transferring, by the processor device, the data file from the remote file system directory to the local file system directory according to the file location parameters and the location configuration parameters; and

performing, by the processor device, an extract, transform and load (ETL) job on the data file to generate an output file.

7. The method of claim 6 wherein the file location parameters are defined by non-variable values.

8. The method of claim 6 wherein the file location parameters comprise a file access protocol type.

9. The method of claim 8 wherein the file access protocol type comprises file transfer protocol (FTP), Secure File access protocol (SFTP), Secure Copy Protocol (SCP), LOCAL, Hadoop File access protocol (HFTP), FTP over Secure Sockets Layer (FTPS) file access protocols, or a combination thereof.

10. The method of claim 6 wherein the file location parameters comprise file access authentication type and authentication data.

11. The method of claim 6 wherein the file location parameters comprise connection retry count and connection retry interval.

12. The method of claim 6 further comprises:

displaying one or more user interface screens arranged to enable a user to input one or more user configurations of the file location object; and

generating the file location object based on the one or more user configurations and storing the file location object in the repository.

13. The method of claim 12 further comprises receiving multiple user configurations corresponding to different operating systems or different file access protocols.

14. The method of claim 12 wherein displaying the one or more user interface screens comprises displaying a first set of user configurable location parameters in response to a user selection of a file access protocol type.

15. The method of claim 12 wherein displaying the one or more user interface screens comprises displaying a second set of user configurable location parameters in response to a user selection of an authorization type.

16. The method of claim 6 further comprises displaying a user interface screen arranged to enable a user to associate the file format object with the file location object.

17. The method of claim 16 wherein displaying the user interface screen further comprises displaying the user interface screen arranged to further enable the user to configure the file format object.

18. A non-transitory computer-readable medium having stored thereon program code, the program code executable by a computer to perform steps comprising:

retrieving, by a processor device, a file location object and a corresponding file format object from a repository, wherein the file location object stores file location parameters that specify a remote file system directory and a local file system directory, wherein the file format object stores location configuration parameters that specify a data file and the file location object;

transferring, by the processor device, the data file from the remote file system directory to the local file system directory according to the file location parameters and the location configuration parameters; and

performing, by the processor device, an extract, transform and load (ETL) job on the data file to generate an output file.

19. The non-transitory computer-readable medium of claim 18 wherein the file location parameters comprise a file access protocol type.

20. The non-transitory computer-readable medium of claim 18 wherein the file location parameters comprise a file access authentication type and authentication data.