saurabh7777777/openstack-apachebfsi repository overview

. The following components are part of the docker image 1) Minimal Ubuntu 16.04 2) Open ssh 3) Hadoop 4) Hive 5) Sqoop 6) Metabase (Visualization) 7) JDK 1.8 8) Postgresql (Meta store for hive) 9) Zookeeper

docker 1) docker pull saurabh7777777/openstack-apachebfsi 2) docker run -d -P --name hadoop saurabh7777777/openstack-apachebfsi 3) docker image user name is hadoop and password is hadoop

Move the files to HDFS

hdfs dfs -copyFromLocal /tmp/Bankholidays.csv /tmp/bankholidays.csv hdfs dfs -copyFromLocal /tmp/ATM_Raw_Data.csv /tmp/ATM_Raw_Data.csv

Check if files are loaded into HDFS

hdfs dfs -ls /tmp

Got to hive

hive

Create and Use database (say atm_data)

Create database atm_data; Use atm_data;

Create 2 tables atm raw data and holiday list

Create Table atm_raw_data(id int, atm_id string, aggr_period_from string, aggr_period_till string, tot_wd int, tot_wd_our_bank_card int, tot_wd_other_bank_card int, sum_tran_amt int, sum_tran_amt_our_bank_card int, sum_tran_amt_other_bank_card int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE tblproperties ("skip.header.line.count"="1");

Create table bank_holidays(Tdate date,Wday string,FesReligion string,Holiday string,Holiday_seq string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE tblproperties ("skip.header.line.count"="1");

Load Data into both the files

LOAD DATA INPATH '/tmp/ATM_Raw_Data.csv' OVERWRITE INTO TABLE atm_raw_data; LOAD DATA INPATH '/tmp/bankholidays.csv' OVERWRITE INTO TABLE bank_holidays;

Check count and Select limit 10

Select count(id) from ATM_Raw_Data; Select * from ATM_Raw_Data limit 10; Select count(Tdate) from bank_holidays; Select * from bank_holidays limit 10;

Create daily aggregate for the ATM Raw DATA Step 1 : Create the Daily aggregate table

Create Table atm_data(atm_id string, tdate date, tot_wd int, tot_wd_our_bank_card int, tot_wd_other_bank_card int, sum_tran_amt int, sum_tran_amt_our_bank_card int, sum_tran_amt_other_bank_card int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE tblproperties ("skip.header.line.count"="1");

Step 2 : Insert Aggreagte data in above table

Insert into atm_data select atm_id,substr(aggr_period_from,1,10), sum(tot_wd),sum(tot_wd_our_bank_card), sum(tot_wd_other_bank_card), sum(sum_tran_amt), sum(sum_tran_amt_our_bank_card), sum(sum_tran_amt_other_bank_card) from atm_raw_data group by substr(aggr_period_from,1,10),atm_id;

Check count and do a select with limit

Select count() from atm_data;* Select * from atm_data limit 10;

Join the Daily Aggreagte and Holiday table to a new table

Create table atm_trans as select a.atm_id, a.tdate , a.tot_wd , a.tot_wd_our_bank_card, a.tot_wd_other_bank_card, a.sum_tran_amt, a.sum_tran_amt_our_bank_card, a.sum_tran_amt_other_bank_card, bh.Wday, bh.FesReligion, bh.Holiday ,bh.Holiday_seq FROM atm_data a LEFT JOIN bank_holidays bh ON a.tdate = bh.Tdate ;

Check count and select with limit 10

Select count(atm_id) from atm_trans; Select * from atm_trans limit 10;

Delete the rows with null columns and check count and select with limit 10

Create table atm_txns as select * from atm_trans where year(Tdate)>2010; Select count(atm_id) from atm_txns; Select * from atm_txns limit 10;

Drop unnecessary tables

Drop table atm_raw_data; Drop table bank_holidays; Drop table atm_trans; Show databases;

Convert the text tables to ORC files with partition Step 0 : Setting for partition

set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; set hive.mapred.mode=nonstrict; set map.reduce.tasks = 12; set hive.enforce.bucketing = true;

Step 1 : Create a ORC Table

CREATE EXTERNAL TABLE IF NOT EXISTS ATM_DATA_ORC(atm_id string, tdate date, TMONTH INT,tot_wd int, tot_wd_our_bank_card int, tot_wd_other_bank_card int, sum_tran_amt int, sum_tran_amt_our_bank_card int, sum_tran_amt_other_bank_card int, wday string, fesreligion string , holiday string, holiday_seq string) PARTITIONED BY (TYEAR INT) CLUSTERED BY (TMONTH) INTO 12 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS ORC TBLPROPERTIES ("orc.compress.size"="1024");

Step 2 : Insert the data from text to ORC

FROM atm_txns A INSERT INTO atm_data_orc PARTITION(TYEAR) SELECT A.atm_id, A.tdate, MONTH(FROM_UNIXTIME( UNIX_TIMESTAMP(A.tdate))) TMONTH, A.tot_wd, A.tot_wd_our_bank_card, A.tot_wd_other_bank_card, A.sum_tran_amt, A.sum_tran_amt_our_bank_card, A.sum_tran_amt_other_bank_card, A.wday, A.fesreligion, A.holiday, A.holiday_seq, YEAR( FROM_UNIXTIME(UNIX_TIMESTAMP(A.tdate))) TYEAR;

Step 3 : Verify the data with count and select with limit

Select count(atm_id) from atm_data_orc; Select * from atm_data_orc limit 10;

Analyze the PARTITION

analyze table atm_data_orc partition(tyear) compute statistics;

Create Data Mart (from analytics) Step 1 : Create Tables

Create table avg_seq(seq string, taverage double)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

Create table amout_withdrawn(Transaction_Date date,Total_amount_Withdrawn INT, Amount_withdrawn_CUB_Card INT, Amount_withdrawn_Other_Card INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

Create table weekday_avg(weekday string, taverage double)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

Step 2: Intsert Data into the data mart

Insert into table weekday_avg select ucase(wday) ,round(avg(sum_tran_amt),0) as average from atm_data_orc group by ucase(wday) order by average;

Insert into table avg_seq select holiday_seq ,round(avg(sum_tran_amt),0) as average from atm_data_orc group by holiday_seq order by average;

Insert into amout_withdrawn select Tdate,sum_tran_amt, sum_tran_amt_our_bank_card, sum_tran_amt_other_bank_card from atm_data_orc;

Push the Data Mart into Postgres Table for visualization Create the required tables

Create table weekday_avg(weekday varchar, taverage float); Create table avg_seq(seq varchar, average double); Create table amount_withdrawn(Transaction_Date date,Total_amount_Withdrawn INT, Amount_withdrawn_CUB_Card INT, Amount_withdrawn_Other_Card INT);

Sqoop the Data from Hive Data Mart to Postgres

Sqoop export --connect jdbc:postgresql://localhost/datamart --username biuser --password welcome --table avg_seq --direct --input-fields-terminated-by ',' --export-dir /user/hive/warehouse/cub_atm.db/avg_seq

Sqoop export --connect jdbc:postgresql://localhost/datamart --username biuser --password welcome --table weekday_avg --direct --input-fields-terminated-by ',' --export-dir /user/hive/warehouse/cub_atm.db/weekday_avg

Sqoop export --connect jdbc:postgresql://localhost/datamart --username biuser --password welcome --table amount_withdrawn --direct --input-fields-terminated-by ',' --export-dir /user/hive/warehouse/cub_atm.db/amout_withdrawn

Verify the data in Postgres

Select count(weekday) from weekday_avg; Select count(seq) from avg_seq; Select count(Transaction_Date) from amount_withdrawn;

Start Metabase jar

cd /tmp java -jar metabase.jar