=Paper=
{{Paper
|id=Vol-2062/paper9
|storemode=property
|title=Next-generation ETL Framework to Address the Challenges Posed by Big Data
|pdfUrl=https://ceur-ws.org/Vol-2062/paper09.pdf
|volume=Vol-2062
|authors=Syed Muhammad Fawad Ali
|dblpUrl=https://dblp.org/rec/conf/dolap/Ali18
}}
==Next-generation ETL Framework to Address the Challenges Posed by Big Data==
https://ceur-ws.org/Vol-2062/paper09.pdf
Next-generation ETL Framework to address the challenges
posed by Big Data
Syed Muhammad Fawad Ali
Poznan University of Technology
Poznan Poland
trivago N.V.
Leipzig Germany
fawadali.ali@gmail.com
ABSTRACT smaller data sets. However, Big Data demands new and advanced
The specific features of Big Data i.e., variety, volume, and velocity computations, as an example from the data cleansing side, the
call for special measures to create ETL data pipelines and data messy and noisy nature of Big Data requires new types of cleans-
warehouses. A rapidly growing need for analyzing Big Data calls ing operators, such as outlier detection or de-duplication that
for novel architectures for warehousing the data, such as data specifically fit the ever changing characteristics of the data. The
lakes or polystores. In both of the architectures, ETL processes same applies to the data analytics side where we find a zoo of algo-
serve similar purposes as in traditional data warehouse archi- rithms such as classification, regression, clustering, collaborative
tectures. Except the fact that the data to process has multitude filtering, and many more.
of formats and the relationships between data are often very We carried out an extensive study on the current practices,
complex. Furthermore, most of the times data transformations short-comings, limitations, and open issues of existing ETL method-
are required on-the-fly that have to be executed and completed ologies and tools [1]. According to this study, the still open issues
in near real-time. For these reasons designing and optimizing on ETL development become much more difficult to solve in the
ETL workflows for Big Data is much more difficult than for tra- field of Big Data. Therefore, in this paper, we propose a next-
ditional data. In this paper, we focus on the ETL aspect of Big generation extendable ETL framework in order to address the
Data and propose an extendable ETL workflow that addresses challenges caused by Big Data. The proposed framework is based
the aforementioned challenges posed by Big Data. on the outcome of our aforementioned study.
In Section 2 we present our motivation for the new ETL frame-
work. We then introduce and explain the proposed extendable
1 INTRODUCTION ETL framework in Section 3. In Section 4 we discuss the related
Since early 2000s, the volume of produced, collected, and stock- work in the same field. Section 5 contains the conclusion and the
piled digital data has been continuously growing exponentially. future work.
It is expected that by 2020 there will be more than 16 Zettabytes
(16 Trillion GB) of useful data.This Big Data provides great oppor- 2 MOTIVATION
tunities and harnessing that leads to great benefits in science and As mentioned in Section 1, we carried out an intensive study [1]
business. Thus, having the right technological basis to exploit on the existing methods for designing, implementing, and opti-
the potential of Big Data, it is essential for most organizations to mizing of ETL workflows. We analyzed several techniques w.r.t
gain competitive advantage or even survive in today’s world. their pros, cons, and challenges in the context of metrics such
Big data itself requires a great scientific contribution to deal as: autonomous behavior, support for quality metrics, and sup-
with it. That is, most of such data is not easily accessible or can be port for ETL activities as user-defined functions. Following is
processed by existing technologies and this imposes to academics the summary of conclusions on open research and technological
a lot of challenges to be solved and questions to be answered. issues in the field of ETL:
For example, traditional ETL or data integration tools work well
with clean and consistent data and are not capable to efficiently (1) The support for semi-structured and unstructured data
deal with the variety of Big Data. Although there are already nu- is very limited. Whereas, the variety of data format espe-
merous Big Data, IoT, and analytics solutions that enable people cially the unstructured and raw data is growing rapidly.
to obtain valuable insights from vast amount of data, such solu- Therefore, there is a need to extend the support for pro-
tions are still in their early stages of development[8]. Therefore, cessing an unstructured data in an ETL workflow along
there is a need for developing new and advanced methods and with other data formats (e.g., video, audio, binary).
technologies to extract, transform, load, analyze, and visualize (2) There is a lack or no support for user-defined functions
such data in order to obtain valuable insights from it. (UDFs) as ETL activities. Whereas, the volume and vari-
This paper focuses on the ETL aspect of Big Data. Traditional ety of Big Data require custom functionality in order to
ETL frameworks, methods, and built-in operators provided by the perform complex and intensive computations. The rea-
existing ETL tools are now obsolete in case of Big Data due to its son being, traditional DWs are not optimized enough to
volume, variety, and velocity. The existing ETL tools and frame- store huge volume and variety of data, therefore novel
works were designed for creating a traditional Data Warehouse data warehousing architectures like data lake [12] or a
(DW), which efficiently supports light-weight computations on polystore [3] are introduced. These DWs support different
kind of data formats, which eventually lead to complex
© 2018 Copyright held by the owner/author(s). Published in the Workshop ETL workflows to populate such DWs. Designing ETL
Proceedings of the EDBT/ICDT 2018 Joint Conference (March 26, 2018, Vienna, workflows for the Big Data is a challenging task because
Austria) on CEUR-WS.org (ISSN 1613-0073). Distribution of this paper is permitted
under the terms of the Creative Commons license CC-by-nc-nd 4.0. traditional ETL operators are not suitable for processing
Big Data and such tasks have to be implemented by UDFs. A UDF is a software program written in any programming,
Therefore, there is a need to consolidate and fully sup- scripting, or procedural language. It allows the ETL developer to
port UDFs in an ETL workflow along with traditional ETL extend the functionality of an ETL tool that is outside the scope
operators. of the already provided built-in ETL operators. For example, the
(3) Only a few methods emphasized on the issues of efficient, messy and noisy nature of Big Data demands new types of cleans-
reliable, and improved execution of an ETL workflow. ing operators, such as outlier detection or de-duplication that
Whereas, today’s need of real-time availability of data specifically fit the ever-changing characteristics of the data. The
requires efficient ETL workflows that can quickly process same applies to the data analytics side where we find a zoo of
and analyze huge amount of data. Therefore, to improve algorithms such as classification, regression, clustering, collabo-
the execution performance of an entire ETL workflow, rative filtering, and many more. A UDF can be used to implement
techniques based on task parallelism, data parallelism, and aforementioned operators in a Big Data setup or to perform ag-
a combination of both for traditional ETL operators as gregations or any kind of run-time intensive computations on a
well as UDFs are required. data that may be necessary before loading into a data warehouse.
(4) Most of the design methods require ETL developers to A UDFs component contains a library of Parallel Algorithmic
extensively provide input during the modeling and design Skeletons (PASs) or parallelizable code templates. These PASs are
phase of an ETL workflow, thus it can be error prone, time designed to be executed in a distributed environment, (e.g., a
consuming, and inefficient. Hence, there is a need for an template for MapReduce or Spark to be executed in Hadoop).
ETL framework that shall reduce the work of the ETL The UDFs component requires a basic knowledge of distributed
developer from a design and performance optimization computing and parallelization aspects from the ETL developer.
perspective. The framework should provide recommen- Figure 2 shows the working of UDFs Component. The com-
dations on: (1) an efficient design for an ETL workflow ponent provides the already parallelizable code for the list of
according to the business requirements, (2) how and when commonly used Big Data operators (case-based PASs) to the ETL
to improve the performance of an ETL workflow without developer (e.g., sentiment analysis, de-duplication of rows, out-
conceding other quality metrics. lier detection) and a list of generic PASs (e.g., worker-farm model,
The consequence of the aforementioned observation is that divide and conquer, branch and bound, systolic, MapReduce). The
designing and optimizing ETL workflows for Big Data is much ETL developer either chooses case based PAS or a generic PAS
more difficult than for traditional data and is much needed at based on his/her requirements.
this point in time.
3 THE EXTENDABLE ETL FRAMEWORK
On the basis of conclusions discussed in Section 2, we present an
extendable theoretical ETL Framework. A three-layered architec-
ture of the ETL Framework is shown in Figure 1.
Figure 2: Extendable UDFs Component Architecture
Figure 1: The overall architecture of the ETL Framework As shown in Figure 2, a generic input to UDFs component
is depicted as [{usercode, case based PAS}, {(input format, output
The bottom layer is an ETL Workflow Designer, which may be format)}, {max execution time constraint}, {distributed machine
any standard open source ETL tool for designing ETL workflows. specifications}]. For example, in case of case-based reasoning the
This layer communicates with the middle layer, which is extend- ETL developer only has to provide the input and output data
able and consists of the four components: (1) a UDFs Component, formats {(input format, output format)}, execution time constraint
(2) a Recommender, (3) a Cost Model, and (4) a Monitoring Agent, to run the ETL workflow (e.g., the ETL job must complete exe-
described in detail in the following sub-sections. cution with in ’x’ number of hours {max execution time}), and
The top layer in the architecture is the Distributed Framework. distributed machine specifications {distributed machine specifica-
Its task is to execute parallel codes of UDFs in a distributed envi- tions}, if known. In case of generic PAS, the ETL developer has
ronment, in order to improve the overall execution performance to provide the basic program for the chosen PAS {usercode}, an
of an ETL workflow. execution time constraint to run the ETL workflow {max exe-
cution time}, and distributed machine specifications {distributed
3.1 A UDFs Component machine specifications}. That is, for the MapReduce paradigm as
The idea behind introducing this component is to assist the ETL a PAS, only Map and Reduce functions would be required. The
developer in writing a parallelizable UDF by separating paral- MapReduce configurations (i.e., partitioning parameters, number
lelization concerns from the code. of nodes) will be provided by the UDFs component. The Code
Generator then generates the configuration and a paralellizable • report errors - e.g., task or workflow failures and the pos-
code based on the ETL developer’s input to the component about sible reasons.
the distributed machine specifications, time constraints on the • schedule executions - e.g., execution time of ETL work-
completion of the ETL workflow, and by the recommendation of flows and creating a dependency chart for ETL tasks and
the Recommender component in the proposed ETL framework. workflows.
The specific configurations provided by this component are very • gather various performance statistics - execution time of
critical to achieve the right degree of parallelism. each ETL activity w.r.t rows processed per second, execu-
Once the configurations are generated, the code provided by tion time of the entire ETL workflow w.r.t rows processed
the ETL developer and the distributed environment configura- per second, memory consumption by each ETL activity.
tions will be executed in Distributed Framework. The computed This is a standard component of any ETL engine. However, we
results are then returned to the ETL workflow for the next steps would store all of the aforementioned collected information in an
in the workflow. ETL framework repository to be later utilized by Recommender
and Cost Model in order to make recommendations to the ETL
3.2 A Recommender developer and to generate optimal ETL workflows.
A Recommender includes an extendable set of machine learning
algorithms to optimize a given ETL workflow (based on metadata 4 RELATED WORK
collected during past ETL executions) and to generate a more
There does not exist much research work in literature on ETL
efficient version of the workflow. Metadata may be collected with
frameworks specifically for Big Data besides some cloud based
the help of Monitoring Agent, where it collects various perfor-
distributed frameworks (e.g., Amazon Web Services1 stack, Google
mance statistics of different ETL workflows and provide them
Cloud Platform2 , and Microsoft Azure3 ). These cloud based dis-
to a Recommender. Since, there are a few algorithms that can
tributed platforms provide several products that help in creating
be applied to optimizing a workflow (e.g., Dependency Graph
Big Data ETL data pipelines and solutions. However, the provided
approach) [10, 11], Scheduling Strategies [7], the ETL developer
products are not fully autonomous as well as does not provide
would then be able to experiment with alternative algorithms
recommendations to the ETL developer for creating optimized
and compare their optimization outcomes.
data pipelines at run-time.
Recommender component also helps the ETL developer to
In research, there exists a few stand alone methods, data ware-
choose the best possible PAS from a UDFs component based on
housing architecture, and utilities for the extraction and trans-
the developer’s input (c.f. Section 3.1) to the Recommender. To
formation phases in an ETL workflow for Big data.
provide the optimal PAS to a UDFs component, it uses the Cost
In [2], the authors proposed a method to semantically extract
Model component.
the data from a variety of data sources e.g., text, video, email,
audio in an ETL workflow. The discussed approach is focused
3.3 A Cost Model on extracting the data and does not cover the compute-intensive
The algorithms used by Recommender need cost models. A Rec- transformation phase required for the 3Vs Big Data. Furthermore,
ommender can choose the appropriate cost model from a library to define the semantics of data it requires a human expert to
of cost models in order to make optimal decisions based on the define ontology, which is a tedious and a time consuming task.
ETL developer’s input to it. A data warehousing architecture for Big Data is discussed
The library of cost models may include cost models for mone- in [9]. The proposed architecture uses HDFS for data storage,
tary cost, performance cost, and both cost and execution perfor- Talend Open Studio for ETL transformations, and Hive as a data
mance optimization. Since most of the Big Data ETL workflows warehouse. However, the work presented did not mention the
or UDFs for Big Data are executed in a cloud or a distributed difficulties to tackle the 3Vs of Big Data. In our paper, we ad-
framework, there would be cost models to evaluate the perfor- dressed the issues like complex ETL workflows due to 3Vs of
mance of workflows in a cloud computing environment [5, 6] Big Data and how to solve the issues of compute-intensive UDFs.
and also to determine the best possible configuration of virtual Finally we also provided a fully automated framework to create
machines both in terms of execution time and monetary cost ETL workflows.
[13].
Since the Recommender uses the Cost Model component to 5 CONCLUSION
provide the optimal PAS to a UDFs component, the cost model
In this paper, we presented an extendable ETL framework in
would be able to select the optimal PAS based on the Multiple
order to address the challenges posed by Big Data. We proposed
Choice Knapsack Problem (MCKP) [4]. For example, suppose an
this ETL framework on the basis of limitations and shortcom-
ETL workflow consists of n different computationally intensive
ings in the currently existing ETL methodologies and tools. We
UDFs and UDFs component may generate m parallel variants of
proposed a UDF’s Component to address the the issue of no or
each UDF, there are mn combinations of code variants. Therefore,
minimal support for UDFs and their optimization in currently
finding an optimal UDF may be mapped to MCKP.
existing ETL frameworks, which is an integral part to develop
ETL transformations for Big Data. Furthermore, we proposed
3.4 A Monitoring Agent
recommendation module that utilizes a library of cost models
Monitoring Agent allows to: and retrieves information from a monitoring agent in order to
• monitor ETL workflow executions - e.g., number of input provide recommendations to the ETL developer. The monitoring
rows, number of output rows, execution time of each step,
number of rows processed per second. 1 https://aws.amazon.com
• identify performance bottlenecks - e.g., which tasks are be- 2 https://cloud.google.com/products
ing delayed or aborted, which tasks need to be optimized. 3 https://azure.microsoft.com
agent module is proposed to assist the recommendation module
as well as an end-to-end monitoring of ETL workflows.
We believe that the proposed ETL framework is a step forward
towards a fully automated ETL framework to help the ETL devel-
opers optimize ETL tasks and an overall ETL workflow for Big
Data with the help of recommendations, montoring agent, and
UDFs provided by the tool.
Currently we are working on the first steps towards building a
complete ETL Framework i.e., (1) a UDFs Component - to provide
the library of reusable parallel algorithmic skeletons for the ETL
developer and (2) Cost Model - to generate the most efficient
execution plan for an ETL workflow.
ACKNOWLEDGMENTS
The research of Syed Ali has been funded by the European Com-
mission through the Erasmus Mundus Joint Doctorate "Informa-
tion Technologies for Business Intelligence Doctoral College"
(IT4BI-DC) and trivago N.V.
REFERENCES
[1] S. M. F. Ali and R. Wrembel. From conceptual design to performance opti-
mization of ETL workflows: current state of research and open problems. The
VLDB Journal, pages 1–25, 2017.
[2] S. K. Bansal. Towards a semantic extract-transform-load (ETL) framework
for big data integration. In Proceedings of International Congress on Big Data,
pages 522–529. IEEE, 2014.
[3] J. Duggan, A. J. Elmore, M. Stonebraker, M. Balazinska, B. Howe, J. Kepner,
S. Madden, D. Maier, T. Mattson, and S. Zdonik. The BigDAWG Polystore
System. SIGMOD Record, pages 11–16, 2015.
[4] T. Ibaraki, T. Hasegawa, K. Teranaka, and J. Iwase. The multiple choice
knapsack problem. Journal of Operations Research Society Japan, pages 59–94,
1978.
[5] A. Iosup, S. Ostermann, M. N. Yigitbasi, R. Prodan, T. Fahringer, and D. Epema.
Performance analysis of cloud computing services for many-tasks scientific
computing. Transactions on Parallel and Distributed systems, pages 931–945,
2011.
[6] K. R. Jackson, L. Ramakrishnan, K. Muriki, S. Canon, S. Cholia, J. Shalf, H. J.
Wasserman, and N. J. Wright. Performance analysis of high performance
computing applications on the amazon web services cloud. In International
Conference on Cloud Computing Technology and Science, pages 159–168. IEEE,
2010.
[7] A. Karagiannis, P. Vassiliadis, and A. Simitsis. Scheduling strategies for effi-
cient ETL execution. Information Systems, pages 927–945, 2013.
[8] M. Marjani, F. Nasaruddin, A. Gani, A. Karim, I. A. T. Hashem, A. Siddiqa,
and I. Yaqoob. Big IoT data analytics: Architecture, opportunities, and open
research challenges. IEEE Access, pages 5247–5261, 2017.
[9] B. Martinho and M. Y. Santos. An architecture for data warehousing in big
data environments. In Proceedings of Research and Practical Issues of Enterprise
Information Systems, pages 237–250. Springer, 2016.
[10] A. Simitsis, P. Vassiliadis, and T. Sellis. State-space optimization of ETL
workflows. IEEE Transactions on Knowledge and Data Engineering (TKDE),
pages 1404–1419, 2005.
[11] A. Simitsis, K. Wilkinson, U. Dayal, and M. Castellanos. Optimizing ETL
workflows for fault-tolerance. In Proceedings of IEEE International Conference
on Data Engineering (ICDE), 2010.
[12] I. Terrizzano, P. Schwarz, M. Roth, and J. E. Colino. Data Wrangling: The
Challenging Journey from the Wild to the Lake. In Proceedings of Conference
on Innovative Data Systems Research (CIDR), 2015.
[13] V. Viana, D. De Oliveira, and M. Mattoso. Towards a cost model for scheduling
scientific workflows activities in cloud environments. In Proceedings of IEEE
World Congress on Services, pages 216–219, 2011.