Modular execution and management of extract, transform, load (ETL) processes
The present disclosure describes a method and apparatus for the modularization, management, and maintenance of extract, transform, and load (ETL) operations. Aspects of the disclosure may provide for a management module from which to initialize operations for each stage of an ETL operation, manage configuration parameters for each stage of an ETL operation, and coordinate order of execution of each stage in an ETL operation. Further aspects described may include methods for validating configuration parameters before executing an ETL operation, logging status updates during an ETL operation, and logging completion times of each stage within an ETL operation. Further aspects described may also include interfacing with a third-party data processing or ETL framework to realize specific advantages of the third-party framework for an ETL operation while maintaining the modularization and management benefits described in the disclosure.
1 . A computer-implemented method comprising:
receiving, by a computing device, a request to transfer information from a plurality of databases to a data warehouse;
initializing, based on a plurality of configuration parameters, a management module configured to coordinate execution of a plurality of stages of an extract, transform, load (ETL) operation from the plurality of databases to the data warehouse;
initializing, by the management module, a plurality of execution modules, wherein an execution module is associated with each stage of the plurality of stages, and wherein initializing an execution module further comprises;
determining, by the execution module, whether a subset of the plurality of configuration parameters for the execution module is valid, wherein:
based on the subset of the plurality of configuration having an invalid configuration parameter, causing an indication of invalid configuration parameters to be displayed,
receiving a subset of valid configuration parameters, and
initializing the execution module based on the subset of valid configuration parameters;
reading, using a first execution module, data, in a first format, from a first database of the plurality of databases;
updating, by the management module and based on a determination that the data has been read from the first database, a stage of the ETL operation indicating that the data has been read;
transforming, using a second execution module and based on the stage of the ETL operation indicating that the data has been read, the data to a second format;
updating, by the management module and based on a determination that the data has been transformed, the stage of the ETL operation to indicate that the data has been transformed;
writing, using a third execution module and based on the stage of the ETL operation indicating that the data has been transformed, the data to the data warehouse;
updating, by the management module and based on a determination that the data has been written to the data warehouse, the stage of the ETL operation to indicate completion of the ETL operation; and
causing, by the management module and based on the stage of the ETL operation indicating completion of the ETL operation, an indication that the information has been transferred from the plurality of databases to the data warehouse to be displayed.
2 . The method of claim 1 , further comprising:
determining, by the first execution module, an initialization time of a read operation;
determining, by the first execution module, a completion time of the read operation; and
causing, based on a determination that an execution time of the read operation exceeded a threshold, an error to be displayed.
3 . The method of claim 1 , wherein updating the stage of the ETL operation further comprises:
causing, after each execution module completes execution of a stage of the ETL operation, a status of the ETL operation to be displayed in a log entry.
4 . The method of claim 1 , wherein the plurality of configuration parameters comprises a JavaScript Object Notation (JSON) dictionary.
5 . The method of claim 1 , wherein the execution module associated with each stage of the plurality of stages comprises a third-party program.
6 . The method of claim 1 , wherein completion of each stage of the plurality of stages comprises:
passing, to the management module, a temporary data structure comprising updates completed by the execution module associated with each stage of the plurality of stages.
7 . The method of claim 1 , wherein initializing the first execution module further comprises:
incorporating, into the first execution module, a first subset of configuration parameters from the plurality of configuration parameters, wherein the first subset of configuration parameters comprises one or more of the first data format, a location for the plurality of databases, or a first schema for a format of a first temporary data structure.
8 . The method of claim 1 , wherein reading using the first execution module further comprises:
reading the data from the first database;
writing the data into a first temporary data structure; and
providing, to the management module, the first temporary data structure.
9 . A computing device, comprising:
one or more processors; and
memory storing instructions that, when executed by the one or more processors, cause the computing device to:
receive, by a computing device, a request to transfer information from a plurality of databases to a data warehouse;
initialize, based on a plurality of configuration parameters, a management module configured to coordinate execution of a plurality of stages of an extract, transform, load (ETL) operation from the plurality of databases to the data warehouse;
initialize, by the management module, a plurality of execution modules, wherein an execution module is associated with each stage of the plurality of stages, and wherein initializing an execution module further comprises:
determining, by the execution module, whether a subset of the plurality of configuration parameters for the execution module is valid, wherein:
based on the subset of the plurality of configuration having an invalid configuration parameter, causing an indication of invalid configuration parameters to be displayed,
receiving a subset of valid configuration parameters, and
initializing the execution module based on the subset of valid configuration parameters;
read, using a first execution module, data, in a first format, from a first database of the plurality of databases;
update, by the management module and based on a determination that the data has been read from the first database, a stage of the ETL operation indicating that the database has been read;
transform, using a second execution module, the data to a second format based on the stage of the ETL operation indicating that the database been read;
update, by the management module and based on a determination that the data has been transformed, the stage of the ETL operation to indicate that the data has been transformed;
write, using a third execution module and based on the stage of the ETL operation indicating that the data has been transformed, the data to the data warehouse;
update, by the management module and based on a determination that the data has been written to the data warehouse, the stage of the ETL operation to indicate completion of the ETL operation; and
cause, by the management module and based on the stage of the ETL operation indicating completion of the ETL operation, an indication that the information has been transferred from the plurality of databases to the data warehouse to be displayed.
10 . The computing device of claim 9 , wherein the instructions, when executed by the one or more processors, cause the computing device to initialize the second execution module by:
incorporating, into the second execution module, a second subset of configuration parameters from the plurality of configuration parameters, wherein the second subset of configuration variables comprises at least one of a first temporary data structure, a second temporary data structure, or an executable statement to modify the data in the first temporary data structure.
11 . The computing device of claim 10 , wherein the instructions, when executed by the one or more processors, cause the computing device to transform the data by:
executing the executable statement to modify the data in the first temporary data structure.
12 . The computing device of claim 11 , wherein the executable statement comprises a Structured Query Language (SQL) statement.
13 . The computing device of claim 9 , wherein the instructions, when executed by the one or more processors, further cause the computing device to:
determine, by the first execution module, an initialization time of a read operation;
determine, by the first execution module, a completion time of the read operation; and
cause, based on a determination that an execution time of the read operation exceeded a threshold, an error to be displayed.
14 . The computing device of claim 9 , wherein the plurality of configuration parameters comprises a JavaScript Object Notation (JSON) dictionary.
15 . One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause a computing device to perform steps comprising:
receiving, by a computing device, a request to transfer information from a plurality of databases to a data warehouse;
initializing, based on a plurality of configuration parameters, a management module configured to coordinate execution of a plurality of stages of an extract, transform, load (ETL) operation from the plurality of databases to the data warehouse;
initializing, by the management module, a plurality of execution modules, wherein an execution module is associated with each stage of the plurality of stages, and wherein initializing an execution module further comprises:
determining, by the execution module, whether a subset of the plurality of configuration parameters for the execution module is valid, wherein:
based on the subset of the plurality of configuration having an invalid configuration parameter, causing an indication of invalid configuration parameters to be displayed,
receiving a subset of valid configuration parameters, and
initializing the execution module based on the subset of valid configuration parameters;
reading, using a first execution module, data, in a first format, from a first database of the plurality of databases;
updating, by the management module and based on a determination that the data has been read from the first database, a stage of the ETL operation indicating that the data has been read;
transforming, using a second execution module and based on the stage of the ETL operation indicating that the data has been read, the data to a second format;
updating, by the management module and based on a determination that the data has been transformed, the stage of the ETL operation to indicate that the data has been transformed;
writing, using a third execution module and based on the stage of the ETL operation indicating that the data has been transformed, the data to the data warehouse;
updating, by the management module and based on a determination that the data has been written to the data warehouse, the stage of the ETL operation to indicate completion of the ETL operation; and
causing, by the management module and based on the stage of the ETL operation indicating completion of the ETL operation, an indication that the information has been transferred from the plurality of databases to the data warehouse to be displayed.
16 . The one or more non-transitory computer readable-media of claim 15 , wherein the instructions, when executed by the one or more processors, cause the computing device to initialize the third execution module by:
incorporating, into the third execution module, a third subset of configuration parameters from the plurality of configuration parameters, wherein the third subset of configuration parameters comprises at least one of a plurality of output locations, an output file format, or a partition configuration setting.
17 . The one or more non-transitory computer-readable media of claim 16 , wherein the instructions, when executed by the one or more processors, cause the computing device to write the data to the data warehouse by:
determining, using the partition configuration setting, a destination within the plurality of output locations; and
writing the data to the destination.
18 . The one or more non-transitory computer readable-media of claim 15 , wherein the instructions, when executed by the one or more processors, further cause the computing device to perform steps comprising:
determining, by the first execution module, an initialization time of a read operation;
determining, by the first execution module, a completion time of the read operation; and
causing, based on a determination that an execution time of the read operation exceeded a threshold, an error to be displayed.
19 . The one or more non-transitory computer readable-media of claim 15 , wherein the plurality of configuration parameters comprises a JavaScript Object Notation (JSON) dictionary.
20 . The one or more non-transitory computer readable-media of claim 15 , wherein the instructions, when executed by the one or more processors, further cause the computing device to perform steps comprising:
passing, to the management module, a temporary data structure comprising updates completed by the execution module associated with each stage of the plurality of stages.