What is ETL?
Before we go discussing about the hadoop's support
for ETL, let me explain briefly about ETL.
ETL is a short form of Extract, Transform & Load to a data warehouse. As a first step of ETL, the data from various
form of data sources are EXTRACTed. Then the extracted data is TRANSFORMed into proper format so that
any business analysis can be performed on the data. Finally the transformed data is LOAD into a target database (A data
warehouse).
ETL software primarily compile data from
multiple applications (systems), those are developed and supported by software
vendors or hosted on separate computer hardware. The disparate systems
containing the original data are frequently managed and operated by different
employees. For example, a University Management System may combine data from courses,
students, examinations, staffing, staff payrolls, accounting software,
etc. And if an university is spread
across various geographical locations / countries, then all these data need to
be integrated.
Stages of ETL:
The first part of an ETL process involves
extracting the data from the source system(s). In many cases this represents
the most important aspect of ETL, since extracting data correctly sets the
stage for the success of subsequent processes and one has to spend quality time
on deciding the data sets that need to be extracted. Brainstorming group discussions would be of
great use here.
In the data transformation stage, a series of
rules or functions are applied to the extracted data in order to prepare it for
loading into the end target. Some data does not require any transformation at
all; such data is known as "direct move" or "pass through"
data.
The load phase loads the data into the end
target that may be a simple delimited flat file or a data warehouse. Depending on the
requirements of the organization, this process varies widely. Some data
warehouses may overwrite existing information with cumulative information;
updating extracted data is frequently done on a daily, weekly, or monthly
basis. Other data warehouses (or even other parts of the same data warehouse)
may add new data in a historical form at regular intervals—for example, hourly.
To understand this, consider a data warehouse that is required to maintain
sales records of the last year. This data warehouse overwrites any data older
than a year with newer data. The final
data, ie., the data LOADed to a data warehouse is the one that could be used
for any BI reports, management reports, analytical reports based on which an
organization can decide/plan for their future business focus. So, some discussions with the senior
management, marketing team, other stakeholders would be desired to load the
required data.
Increased data volume - Missing ETL timelines?
In the
mid-to-late 1990s, the data sizes started to grow beyond the ability of ETL
tools to finish the transformation work within allocated time (the ETL window)
— that is, they had difficulties in executing the TRANSFORM tasks
fast enough.
Most organizations with traditional data platforms—typically
relational database management systems (RDBMS) coupled to enterprise data
warehouses (EDW) using ETL tools—find that their legacy infrastructure is
either technically incapable or financially impractical for storing and
analyzing big data.
Matters were further complicated by human
error. For example, if it is discovered that part of the ETL didn't complete
properly during the previous run, or if any logical errors observed in the user
defined functions (UDF) if any, the transformations have to be redone. This
problem often takes weeks to be corrected, as the ETL tool has to redo all that
past work while continuing to do the work for new daily data as it arrives. In
summary, ETL transformation engines became a significant bottleneck.
Apache Hadoop
Rapidly ingesting, storing, and processing big
data requires a cost effective infrastructure that can scale with the amount of
data and the scope of analysis. As the industries struggled with SLAs, a new
solution evolved: Apache Hadoop, a distributed data storage and processing
system built from the ground up to be massively scalable (thousands of servers)
using industry-standard hardware. Furthermore, Hadoop was built to be very
flexible in terms of accepting data of any type, regardless of structure.
Apache Hadoop is an open source distributed
software platform for storing and processing data. Written in Java, it runs on
a cluster of industry-standard servers configured with direct-attached
storage. Using Hadoop, you can store
petabytes of data reliably on tens of thousands of servers while scaling
performance cost-effectively by merely adding inexpensive nodes to the cluster.
Central to the scalability of Apache Hadoop is
the distributed processing framework known as MapReduce. MapReduce helps
programmers solve data-parallel problems for which the data set can be
sub-divided into small parts and processed independently.
MapReduce is an important advance because it
allows ordinary developers, not just those skilled in high-performance computing,
to use parallel programming constructs without worrying about the complex
details of intra-cluster communication, task monitoring, and failure handling.
MapReduce simplifies all that.
The system splits the input data-set into
multiple chunks, each of which is assigned a map task that can process the data
in parallel. Each map task reads the
input as a set of (key, value) pairs and produces a transformed set of (key,
value) pairs as the output. The framework shuffles and sorts outputs of the map
tasks, sending the intermediate (key, value) pairs to the reduce tasks, which
group them into final results. MapReduce uses JobTracker and TaskTracker mechanisms
to schedule tasks, monitor them, and restart any that fail.
The Apache Hadoop platform also includes the
Hadoop Distributed File System (HDFS), which is designed for scalability and
fault tolerance. HDFS stores large files
by dividing them into blocks (usually 64 or 128 MB) and replicating the blocks
on three or more servers. HDFS provides
APIs for MapReduce applications to read and write data in parallel. Capacity
and performance can be scaled by adding Data Nodes, and a single NameNode
mechanism manages data placement and monitors server availability. HDFS
clusters in production use today reliably hold petabytes of data on thousands
of nodes.
Besides MapReduce & HDFS, Apache hadoop has
many components such as Apache Flume, Apache Sqoop, Pig, Hive etc. some of them
are useful for the ETL process.
Apache Flume* is a distributed system for collecting,
aggregating, and moving large amounts of data from multiple sources into HDFS
or another central data store.
Apache Sqoop* is a tool for transferring data
between Hadoop and relational databases. You can use Sqoop to import data from
a MySQL or Oracle database into HDFS, run MapReduce on the data, and then
export the data back into an RDBMS. Sqoop automates these processes, using
MapReduce to import and export the data in parallel with fault-tolerance.
Apache Hive* and Apache Pig* are programming
languages that simplify development of applications employing the MapReduce
framework. HiveQL is a dialect of SQL and supports a subset of the syntax.
Although slow, Hive is being actively enhanced by the developer community to
enable low-latency queries on Apache HBase* and HDFS. Pig Latin is a procedural
programming language that provides high-level abstractions for MapReduce. You
can extend it with User Defined Functions written in Java, Python, and other
languages.
Hadoop's
support
Now
that Hadoop has been commercially available in the market for years, hundreds
of organizations are moving the TRANSFORM function from their databases to
Hadoop. This movement achieves a number of key benefits:
1.
Hadoop can perform TRANSFORM much
more effectively than RDBMSs. Besides the performance benefits it is
also very fault tolerant and elastic. If you have a nine-hour transformation
job running on 20 servers, and at the eighth hour four of these servers go
down, the job will still finish — you will not need to rerun it from scratch.
If you discover a human error in your ETL logic and need to rerun TRANSFORM for
the last three months, you can temporarily add a few nodes to the cluster to
get extra processing speed, and then decommission those nodes after the ETL
catch-up is done.
2.
When the TRANSFORM moves to Hadoop, the RDBMS'es
are free to focus on doing the Q really well. It would be interesting to see
how much faster these systems get once voluminous unstructured data is moved
out of them.
Using Hadoop in this way, the organization
gains an additional ability to store and access data that they “might” need,
data that may never be loaded into the data warehouse. For example, data
scientists might want to use the large amounts of source data from social
media, web logs, or third- party stores stored on Hadoop, to enhance new
analytic models that drive research and discovery. They can store this data
cost effectively in Hadoop, and retrieve it as needed (using Hive or other
analytic tools native to the platform), without affecting the EDW environment.
Please note that I have give some views on how
hadoop can be utilised for ETL / Dataware housing related activities, and I am
not saying hadoop is out & out replacement for an ETL or and RDBMS, since
they may have their own strengths. Its
the responsibility of the clients who can choose the best implementation based
on analysis.
Thanks.
