i0isJNjA

1######################3 CREATE STATEMENT ####################
2
3create table bridger_result_hist(
4cstone_feed_key bigint,
5rslt_id int,
6clnt_id int,
7run_id string,
8ent_acct_id string,
9div_id string,
10user_id string,
11user_nm string,
12sta_id string,
13sta_creat_user_nm string,
14sta_creat_ts string
15)
16ROW FORMAT DELIMITED
17FIELDS TERMINATED BY ','
18LINES TERMINATED BY '\n';
19----------------------------------
20
21create table bridger_result(
22cstone_feed_key bigint,
23rslt_id int,
24clnt_id int,
25run_id string,
26ent_acct_id string,
27div_id string,
28user_id string,
29user_nm string,
30sta_id string,
31sta_creat_user_nm string,
32sta_creat_ts string
33)
34ROW FORMAT DELIMITED
35FIELDS TERMINATED BY ','
36LINES TERMINATED BY '\n';
37
38---------------------------------
39
40create table bridger_result_status(
41cstone_feed_key bigint,
42rslt_id int,
43clnt_id int,
44sta_id string,
45sta_creat_user_nm string,
46sta_creat_ts string
47)
48ROW FORMAT DELIMITED
49FIELDS TERMINATED BY ','
50LINES TERMINATED BY '\n';
51
52--------------------------------
53
54create table bridger_audit_action (
55cstone_feed_key bigint,
56rec_id bigint,
57clnt_id bigint,
58area_tx string,
59act_tx string
60)
61ROW FORMAT DELIMITED
62FIELDS TERMINATED BY ','
63LINES TERMINATED BY '\n';
64
65---------------------------------
66
67###################### INSERT DATA ####################
68
69LOAD DATA LOCAL INPATH '/idn/home/ishar23/bridger_hive/dataset/bridger_result_hist'
70into table bridger_result_hist;
71
72LOAD DATA LOCAL INPATH '/idn/home/ishar23/bridger_hive/dataset/bridger_result'
73into table bridger_result;
74
75LOAD DATA LOCAL INPATH '/idn/home/ishar23/bridger_hive/dataset/bridger_result_status'
76into table bridger_result_status;
77
78LOAD DATA LOCAL INPATH '/idn/home/ishar23/bridger_hive/dataset/bridger_audit_action'
79into table bridger_audit_action;
80
81---------------------------------
82
83     Step1 - join Result and result status   ----->  t_bridger_result_status_join   / drop table / #################  add case condition + Load append condition max feed key // add indicator
84
85create table t_bridger_result_status_join 
86as
87select br.cstone_feed_key as cstone_feed_key,br.rslt_id as rslt_id,br.clnt_id as clnt_id,br.run_id as run_id,br.ent_acct_id as ent_acct_id,br.div_id as div_id,
88br.user_id as user_id,br.user_nm as user_nm,
89CASE WHEN bs.rslt_id IS NOT NULL and bs.clnt_id IS NOT NULL THEN bs.sta_id ELSE br.sta_id END as sta_id,
90CASE WHEN bs.rslt_id IS NOT NULL and bs.clnt_id IS NOT NULL THEN bs.sta_creat_user_nm ELSE br.sta_creat_user_nm END as sta_creat_user_nm,
91CASE WHEN bs.rslt_id IS NOT NULL and bs.clnt_id IS NOT NULL THEN bs.sta_creat_ts ELSE br.sta_creat_ts END as sta_creat_ts,
92CASE WHEN bs.rslt_id IS NULL and bs.sta_creat_user_nm IS NULL and bs.sta_creat_ts IS NULL THEN 'R' ELSE 'S' END AS indicator
93 from bridger_result br LEFT JOIN bridger_result_status bs
94on br.rslt_id=bs.rslt_id and br.clnt_id=bs.clnt_id
95where br.cstone_feed_key IN (select max(cstone_feed_key) from bridger_result group by cstone_feed_key);
96
97    Step2 - Load Incremental along with History data  -------> 	t_bridger_result_load    ##### Remember to DROP Tables ##############################
98	
99CREATE TABLE t_bridger_result_load
100AS
101SELECT cstone_feed_key,rslt_id,clnt_id,run_id,ent_acct_id,div_id,user_id,user_nm,sta_id,sta_creat_user_nm,sta_creat_ts,'O' as indicator
102 FROM bridger_result_hist UNION ALL select * from t_bridger_result_status_join;
103
104Rejected
105INSERT INTO TABLE bridger_result_hist
106SELECT * from t_bridger_result_status_join;
107
108
109select * from 
110
111drop table if exists t_bridger_result_status_join;
112
113    Step3 - Forward Snapshot ------->  t_bridger_result_snapshot  
114	
115	
116when a.indicator = NULL          ---------->   b.all
117when a.indicator = 'R'           ---------->   a.(7) & b.(3)
118whrn a.indicator = 'S'           ---------->   a.all
119
120CREATE TABLE t_bridger_result_snapshot
121AS
122select *
123from t_bridger_result_load h
124JOIN (
125select rslt_id,clnt_id,max(cstone_feed_key) as max_feed_key from t_bridger_result_load where sta_id IS NOT NULL group by rslt_id,clnt_id) t
126on h.rslt_id = t.rslt_id and h.clnt_id = t.clnt_id where h.cstone_feed_key=t.max_feed_key;
127
128	Step4 Delete using audit action ------------->        #########3 MAP SIDE JOIN
129
130select * from bridger_result_snapshot rs LEFT JOIN bridger_audit_action aa 
131ON rs.rslt_id = aa.rec_id and rs.clnt_id = aa.clnt_id on aa.<  > <> 'DELETE'
132
133
134----------------------------------------------------------------------------------------------
135
136select h.*
137from t_bridger_result_load h
138JOIN t_bridger_result_load t 
139on h.rslt_id = t.rslt_id and h.clnt_id = t.clnt_id;