Managing extract, transform and load systems
An approach to implement an extract, transform and load system, a queue is provided for holding units of data between extraction and transformation. When units of data suffer a rate limit error in the load phase, they are requeued so they can be resubmitted for transformation. The contents of the queue are monitored and, if too many requeued units of data are detected in the queue, then this is taken as an indicator of an unacceptable number of rate limit errors and active pacing management is triggered. A retry schedule is defined for the requeued units of data. Extraction is temporarily halted to allow the requeued units of data to be retransformed without more units of data queuing up. Then, after the suspension is lifted, a pacing delay is inserted between subsequent extract events to avoid the same load phase bottleneck recurring.
1. A computer-implemented method for extracting, transforming and loading data between clients, servers and storage locations, the computer-implemented method comprising:
extracting, by one or more processors, units of data from a source system;
queueing, by the one or more processors, the units of data to a queue for processing;
processing, by the one or more processors, the units of data from the queue, based on transforming the units of data and loading the units of data to a target system, wherein the units of data are subject to a rate limit error from the loading and are requeued in the queue for reprocessing;
monitoring the queue and detecting if there are more than a threshold number of requeued units of data in the queue; and
responsive to more than a threshold number of requeued units of data in the queue, imposing, by the one or more processors, at least one restriction on the extracting and applying a retry schedule for reprocessing the requeued units of data.
2. The computer-implemented method of claim 1 , wherein the retry schedule is set to avoid rate limit errors occurring again in the loading after the units of data have been reprocessed.
3. The computer-implemented method of claim 2 , wherein the at least one extract restriction comprises imposing a pause on extraction for an amount of time set according to an expected amount of time needed to reprocess the requeued units of data based on the retry schedule.
4. The computer-implemented method of claim 1 , wherein the at least one extract restriction comprises introducing a pacing limitation to limit how many units of data are extracted per unit time.
5. The computer-implemented method of claim 4 , wherein the at least one extract restriction further comprises reducing a size of the units of data.
6. The computer-implemented method of claim 1 , wherein the threshold number is based on the number of requeued units of data compared to a total number of units of data in the queue.
7. The computer-implemented method of claim 1 , wherein the loading rate limit errors are received from the target system.
8. The computer-implemented method of claim 1 , wherein a loading rate limit error occurs when more than a threshold number of transformed units of data are loaded.
9. The computer-implemented method of claim 1 , wherein the at least one restriction is varied based on changes in the number of requeued units of data in the queue.
10. The computer-implemented method of claim 1 , wherein the monitoring further comprises at least one history attribute of units of data held in the queue and to delete units of data based on their history attributes.
11. The computer-implemented method of claim 10 , wherein the at least one history attribute is an age of a data unit, wherein a unit of data is deleted if it has an age greater than a predetermined maximum age limit.
12. The computer-implemented method of claim 10 , wherein at least one history attribute is a value indicating how many times a unit of data has been processed by the processing engine, wherein a unit of data is deleted if it has been processed more than a predetermined maximum permitted number of times.
13. A computer system for extracting, transforming and loading data between clients, servers and storage locations, the computer system comprising:
one or more computer processors;
one or more computer readable storage media;
a source system operable to supply units of data in an extract phase; and
program instructions stored on the one or more computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising:
program instructions to extract units of data from a source system;
program instructions to queue the units of data to a queue for processing;
program instructions to process the units of data from the queue, based on transforming the units of data and loading the units of data to a target system, wherein the units of data are subject to a rate limit error from the loading and are requeued in the queue for reprocessing;
program instructions to monitor the queue and detect if there are more than a threshold number of requeued units of data in the queue; and
responsive to more than a threshold number of requeued units of data in the queue, program instructions to impose at least one restriction on the extracting and applying a retry schedule for reprocessing the requeued units of data.
14. A computer-implemented method for extracting, transforming and loading data between clients, servers and storage locations, the computer-implemented method comprising:
extracting, by one or more processors, units of data from a source system and placing them in a queue;
processing, by the one or more processors, units of data from the queue and transforming the units of data; and
loading the units of data to a target system, wherein the units of data that are subject to a rate limit error in the loading further comprise monitoring the queue to detect requeued units of data therein and, if there are more than a threshold number of requeued units of data in the queue, imposing, by the one or more processors, at least one restriction on the extracting and applying a retry schedule for reperforming the transforming on the requeued units of data.
15. The computer-implemented method of claim 14 , wherein the retry schedule is set to avoid rate limit errors occurring again in the loading after the units of data have been reprocessed in the transforming.
16. The computer-implemented method of claim 15 , wherein the at least one extract restriction includes imposing a pause on extracting for an amount of time set according to an expected amount of time needed to reprocess the requeued units of data based on the retry schedule.
17. The computer-implemented method of claim 14 , wherein the at least one extract restriction includes introducing a pacing limitation to limit how many units of data are extracted per unit time.
18. The computer-implemented method of claim 17 , wherein the at least one extract restriction further includes reducing size of the units of data.
19. The computer-implemented method of claim 14 , wherein the threshold number is based on how many requeued units of data there are compared with a total number of units of data held in the queue.
20. The computer-implemented method of claim 14 , wherein the loading rate limit errors are received from the target system.