I am assuming that you know how to write shell jobs to import data files into HDFS and write Sqoop jobs to import data directly from relational data sources. Once you have written your shell or Sqoop jobs to pull data from sources into the Enterprise HDFS platform, you can schedule the jobs by using Oozie. Not just shell or Sqoop, you can also schedule MapReduce, Java, Pig, and Hive scripts on Oozie.

Oozie can run workflows for dependent jobs and has two main components:

As the trade, position, and risk data will be received from a number of source systems, we should not expect data providers to learn about it. So, the extraction responsibility will almost certainly fall within the receiving enterprise HDFS system's scope.

Here are a few basic steps to configure your jobs on Oozie, once you have completed the shell and Sqoop scripts to import data into HDFS:

ETL is a very generic term used for extraction, transformation, and loading of data. There are tools that perform ETL in a very easy-to-maintain graphical form—no hand coding of data integration modules, connects to almost any data source, transforms using drag and drop, and schedules jobs.

For large banks that have invested significantly in those ETL tools, they will stick to them, as long as it provides value. In the last few years, most of the leading ETL tools have developed adapters to Hadoop, which makes it a lot easier to move the data in or out of Hadoop.

Most of these tools allow existing developers to continue building new Hadoop solutions using their existing ETL skills. The best thing about it is no complicated code or scripts in Java, Python, or even Pig, or Hive.

There are new entrants in the ETL tool market with Hadoop integration—each with a rich user interface based on drag-and-drop jobs and workflows. They sometimes claim to have a better performance than the MapReduce code.

Top ETL products are (in no particular order and by no means complete):

According to the Gartner 2014 report on data integration tools, Informatica is the leader of the ETL tool market space. It is widely used within the financial sector and I will touch base this tool as an example to import and export data out of the Risk HDFS platform.

Please visit http://www.informatica.com for more details.

With the assumption that other ETL tools have similar functionalities, I will use Informatica Powercenter (PC) 9.5.1 and Power Exchange for Hadoop as a base to explain how ETL tools can be used to automate data ingestion.

Raw trade, position, and risk data files go through basic cleanup and transformations (trimming of extra spaces, consistent letter case, numeric and date formats) and can be loaded on the Risk HDFS platform, using Informatica.

The key steps are:

For this project, I will use ETL tools to move the data in and out of Hadoop. With an existing ETL team, it makes the enterprise adoption of Hadoop a bit easier, as existing IT data providers may be reluctant to learn Hadoop or write Sqoop or shell scripts.

However, if parts of your data transformation involve less data crunching, a mixed approach of using both ETL tools and Hadoop tools (Hive, Pig, and MapReduce) is also a good idea.

The latest benchmark tests prove that Hive performs marginally better than Pig for very large data sets and is inconclusive for smaller data sets. But Pig is more popular and widely used compared to Hive, because it is a procedural language and slightly more flexible in the dynamic use of variables.

I will discuss different options to transform the incoming data into more useful data using Hive, Pig, or Java MapReduce. A detailed solution is not the purpose of this book – it is more about providing the readers with enough details to choose the correct direction. So, I will touch base on all the options with brief technical details and code templates.

We will use Hive for the transformation and to store the intermediate and final results, which will be used later by downstream risk and regulatory managers.

We will still follow a standard data-warehouse-type pattern—staging database, intermediate database, and results database. We can either create three Hive databases or just one database. However, the table names always need to have a good naming convention, such as attaching the prefix stg_ for stage, int_ for intermediate, and <business purpose>_ for the final output tables.

If the same Hive installation is shared across multiple teams, I would recommend limiting the number of databases, but it is completely up to the team developing the solution. In our example, we will create only one Hive database, using the following command:

CREATE DATABASE riskandregulatorywh COMMENT 'Risk and regulatory enterprise warehouse'

USE riskandregulatorywh;
CREATE EXTERNAL TABLE STG_SYSTEM1_TRADE_DETAILS (
BUSINESSAREA STRING COMMENT 'optional comments',
TRADEID STRING,
BOOKNAME STRING,
TRADEPRODUCTTYPE STRING, 
TRADEPRODSUBTYPE STRING,
BOOKINGCOUNTERPARTYID STRING,
LEGALENTITYID STRING,
MATURITYDATE STRING,
TRADEDATE STRING,
................
................
TRADELEVELSTRESSEDRWA_USD FLOAT,
TRADELEVELCVSUNHEDGEDRWA_USD FLOAT,
TRADELEVELCVSHEDGEDRWA_USD FLOAT,
TOTALRWA_USD FLOAT,
NETIR01_USD FLOAT,
PV_USD FLOAT
)
PARTITIONED BY (VALUATIONDATE INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';

ALTER TABLE STG_SYSTEM1_TRADE_DETAILS ADD PARTITION (VALUATIONDATE=<configured_business_date> LOCATION = '<default_hdfs_directory>/data/input/<Trading_system1>/TRADE/20150702/Trade_details.txt'

DROP TABLE IF EXISTS BUSINESS_HIERARCHY;
CREATE EXTERNAL TABLE BUSINESS_HIERARCHY 
(
BUSINESSAREA STRING,
SUBPRODUCTNAME STRING,
..............
LEVEL6NAME STRING,
LEVEL7NAME STRING,
BOOKNAME STRING
)
ROW FORMA DELIMITED FIELDS TERMINATED BY ',' LOCATION = '<default_hdfs_directory>/data/input/<Reference_system1>/REF/<configured_business_date>/Business_Hierarchy.txt'

DROP TABLE IF EXISTS VW_TRADE_DENORMALIZED;
CREATE VIEW VW_TRADE_DENORMALIZED 
AS
SELECT 
STG_TD.TRADEID ,
STG_TD.BOOKNAME ,
STG_TD.TRADEPRODUCTTYPE , 
STG_TD.TRADEPRODSUBTYPE ,
STG_TD.BOOKINGCOUNTERPARTYID ,
STG_TD.LEGALENTITYID ,
STG_TD.MATURITYDATE ,
STG_TD.TRADEDATE ,
................
................
STG_TD.TRADELEVELSTRESSEDRWA_USD,
STG_TD.TRADELEVELCVSUNHEDGEDRWA_USD,
STG_TD.TRADELEVELCVSHEDGEDRWA_USD,
STG_TD.TOTALRWA_USD,
STG_TD.NETIR01_USD,
STG_TD.PV_USD,
BH.BUSINESSAREA,
BH.SUBPRODUCTNAME,
BH.LEVEL6NAME,
BH.LEVEL7NAME
FROM STG_SYSTEM1_TRADE_DETAILS STG_TD
LEFT OUTER JOIN BUSINESS_HIERARCHY BH
ON STG_TD.BUSINESSAREA = BH.BUSINESSAREA;

CREATE TABLE TRADE_DFNORMALIZED .....; --We will leave this to readers imagination, as the structure will be same as above view
INSERT OVEWRITE TABLE TRADE_DFNORMALIZED
SELECT 
STG_TD.TRADEID ,
STG_TD.BOOKNAME ,
STG_TD.TRADEPRODUCTTYPE , 
STG_TD.TRADEPRODSUBTYPE ,
STG_TD.BOOKINGCOUNTERPARTYID ,
STG_TD.LEGALENTITYID ,
STG_TD.MATURITYDATE ,
STG_TD.TRADEDATE ,
................
................
STG_TD.TRADELEVELSTRESSEDRWA_USD,
STG_TD.TRADELEVELCVSUNHEDGEDRWA_USD,
STG_TD.TRADELEVELCVSHEDGEDRWA_USD,
STG_TD.TOTALRWA_USD,
STG_TD.NETIR01_USD,
STG_TD.PV_USD,
BH.BUSINESSAREA,
BH.SUBPRODUCTNAME,
BH.LEVEL6NAME,
BH.LEVEL7NAME
FROM STG_SYSTEM1_TRADE_DETAILS STG_TD
LEFT OUTER JOIN BUSINESS_HIERARCHY BH
ON STG_TD.BUSINESSAREA = BH.BUSINESSAREA;

We will use Pig for the transformation and to store intermediate and final results, which can be used later by downstream risk and regulatory managers.

We will again follow the standard data-warehouse-type pattern—read the data from the staging directories, transform the data, and load results into intermediate and final output results directories.

If the same Pig installation is shared across multiple teams, I recommend using dedicated project directories for the scripts and intermediate or final transformed data.

Pig scripts can be stored at <default_application_directory>/EnterpriseHadoopPlatform/script/<business_transformation>.pig and the intermediate/final results can be stored on HDFS at <default_hdfs_directory>/data/output/<system name>/<subject area>/<business date>/<file_name>.

STG_SYSTEM1_TRADE_DETAILS = LOAD '<default_hdfs_directory>/data/input/<Trading_system1>/TRADE/{$BUSINESS_DATE}'
USING PigStorage('|') AS
AS
BUSINESSAREA,
TRADEID ,
BOOKNAME ,
TRADEPRODUCTTYPE , 
TRADEPRODSUBTYPE ,
BOOKINGCOUNTERPARTYID ,
LEGALENTITYID ,
MATURITYDATE ,
TRADEDATE ,
................
................
TRADELEVELSTRESSEDRWA_USD ,
TRADELEVELCVSUNHEDGEDRWA_USD ,
TRADELEVELCVSHEDGEDRWA_USD ,
TOTALRWA_USD ,
NETIR01_USD ,
PV_USD 
);
DESCRIBE STG_SYSTEM1_TRADE_DETAILS;

BUSINESS_HIERARCHY = LOAD '<default_hdfs_directory>/data/input/<Reference_system1>/REF/{$BUSINESS_DATE}'
USING PigStorage(',') AS
(
BUSINESSAREA,
SUBPRODUCTNAME,
..............
LEVEL6NAME,
LEVEL7NAME,
BOOKNAME
);
DESCRIBE BUSINESS_HIERARCHY;

TRADE_DENORMALIZED = JOIN STG_SYSTEM1_TRADE_DETAILS BY BUSINESSAREA LEFT OUTER, BUSINESS_HIERARCHY BY BUSINESSAREA;

STORE TRADE_DENORMALIZED INTO <default_hdfs_directory>/data/output/<Trading_system1>/TRADE/{$BUSINESS_DATE}'

We can solve the majority of data transformation problems using Pig and Hive, with significantly lower code lines compared to Java MapReduce programs. For example, a simple word count program takes 63 code lines in Java, compared to 8 code lines in Hive. Why on earth would anyone would like to code MapReduce in Java? Well, there are a few reasons:

Let's take an example of creating a Java MapReduce job that we will use in the next chapter to build a fraud prediction model. Writing start-to-end working Java code is beyond the scope of this book, but we will create a template that can easily be filled up by a programmer.

A credit or debit card transaction generally has at least the following attributes:

We will create a Markov Chain Model that can detect abnormal or suspicious transactions:

Traditional BI tools, such as Cognos, work on the principle of aggregating data centrally into a denormalized relational data warehouse. Many of them have now developed connectors to Hive using ODBC or JDBC connections, which is some relief.

But they still fall short, compared to the new breed of BI tools such as QlikView, Tableau, Spotfire, and Revolution R, also known as big data analytics or data visualization tools. In addition to traditional BI functions, they also:

Traditional BI tools are still not able to cope up with large volumes of data. So, I advise summarizing the results on Hive and then connecting BI tools for visual representations.

Once the data has been transformed and is on Hive, dashboards and reports can be developed using BI tools.

I will demonstrate with screenshots how QlikView can connect to Hive by using an ODBC connection. If required, please visit http://www.qlik.com/ for more details.