TULgLcnD

1beeline -u "jdbc:hive2://sda1node04.na.pg.com:10000/default;principal=hive/_HOST@BUCCLUSTER.NA.PG.COM"  --showHeader=false --silent=true --outputformat=dsv  --verbose =false  -e "select * from poc_ccts.qs_mps">output_partition.txt
2
3
4stage_csv=output_file.csv
5
6while read row
7do
8
9start_date1=$(echo $row | awk -F "|" '{print $5}')
10start_time=$(echo $start_date1 |  cut -f 2 -d " " )
11start_date=$(echo $start_date1 |  cut -f 1 -d " " )
12D1=`date +%s -d "$start_date"`
13start_time=$(date +%H:%M:%S -d "$start_date1")
14end_date1=$(echo $row | awk -F "|" '{print $6}')
15end_time=$(echo $end_date1 |  cut -f 2 -d " " )
16end_date=$(echo $end_date1 |  cut -f 1 -d " " )
17D2=`date +%s -d "$end_date"`
18
19
20
21ean_cs=$(echo $row | awk -F "|" '{print $1}')
22material=$(echo $row | awk -F "|" '{print$2}')
23material_id=$(echo $row | awk -F "|" '{print$3}')
24count_ean_mat=$(echo $row | awk -F "|" '{print$4}')
25product=$(echo $row | awk -F "|" '{print$7}')
26material_id_extract=$(echo $row | awk -F "|" '{print$8}')
27team=$(echo $row | awk -F "|" '{print$9}')
28_shift=$(echo $row | awk -F "|" '{print$10}')
29_status=$(echo $row | awk -F "|" '{print$11}')
30produced_su=$(echo $row | awk -F "|" '{print$12}')
31produced_pad=$(echo $row | awk -F "|" '{print$13}')
32totalpad=$(echo $row | awk -F "|" '{print$14}')
33runningscrap=$(echo $row | awk -F "|" '{print$15}')
34stopscrap=$(echo $row | awk -F "|" '{print$16}')
35linespeed_tar=$(echo $row | awk -F "|" '{print$17}')
36linespeed_act=$(echo $row | awk -F "|" '{print$18}')
37line_desc=$(echo $row | awk -F "|" '{print$19}')
38plant_desc=$(echo $row | awk -F "|" '{print$20}')
39line_id=$(echo $row | awk -F "|" '{print$21}')
40totalcases=$(echo $row | awk -F "|" '{print$22}')
41prodperbag=$(echo $row | awk -F "|" '{print$23}')
42bagspercase=$(echo $row | awk -F "|" '{print$24}')
43casesperpallet=$(echo $row | awk -F "|" '{print$25}')
44prodperstat=$(echo $row | awk -F "|" '{print$26}')
45p_size=$(echo $row | awk -F "|" '{print$27}')
46run_time=$(echo $row | awk -F "|" '{print$28}')
47unit_id=$(echo $row | awk -F "|" '{print$29}')
48unit_desc=$(echo $row | awk -F "|" '{print$30}')
49plid=$(echo $row | awk -F "|" '{print$31}')
50gleds_transfer_status=$(echo $row | awk -F "|" '{print$32}')
51eops_transfer_status=$(echo $row | awk -F "|" '{print$33}')
52prod_id=$(echo $row | awk -F "|" '{print$34}')
53site=$(echo $row | awk -F "|" '{print$35}')
54production_id=$(echo $row | awk -F "|" '{print$36}')
55
56
57
58        start_epoch=$(date +"%s" -d "$start_date $start_time")  ||  {
59                echo "Error processing start date&time $start_date $start_time" >&2
60                exit 1
61        }
62        end_epoch=$(date +"%s" -d "$end_date $end_time")  ||  {
63                echo "Error processing end date&time $end_date $end_time" >&2
64                exit 1
65        }
66        if [ "$end_epoch" -lt "$start_epoch" ]
67        then
68                echo "End date&time $end_date $end_time is before start date&time $start_date $start_time" >&2
69                # Now what?
70                continue
71        fi
72
73
74
75((diff_sec=D2-D1))
76date_diff=`echo - | awk -v SECS=$diff_sec '{printf "%d",SECS/(60*60*24)}'`
77echo $date_diff
78
79
80delimiter='|'       
81
82                if             [ $date_diff -ne 0 ] ; then 
83        ok_seq=1        # Flag: we are moving forward.
84
85                                current_time="00:00:00"
86                                eod_time="23:59:59"
87                                start_epoch=`echo "$start_date $start_time"`
88                                eod_epoch=`echo "$end_date $current_time"`
89                                default_start_epoch=`echo "$start_date $eod_time"`
90                                default_end_epoch=`echo "$end_date $end_time"`
91
92                                final_result_set=$ean_cs$delimiter$material$delimiter$material_id$delimiter$count_ean_mat$delimiter$start_epoch$delimiter$default_start_epoch$delimiter$product$delimiter$material_id_extract$delimiter$team$delimiter$_shift$delimiter$_status$delimiter$produced_su$delimiter$produced_pad$delimiter$totalpad$delimiter$runningscrap$delimiter$stopscrap$delimiter$linespeed_tar$delimiter$linespeed_act$delimiter$line_desc$delimiter$plant_desc$delimiter$line_id$delimiter$totalcases$delimiter$prodperbag$delimiter$bagspercase$delimiter$casesperpallet$delimiter$prodperstat$delimiter$p_size$delimiter$run_time$delimiter$unit_id$delimiter$unit_desc$delimiter$plid$delimiter$gleds_transfer_status$delimiter$eops_transfer_status$delimiter$prod_id$delimiter$site$delimiter$production_id
93                                echo $final_result_set >> $stage_csv
94                                final_result_set=$ean_cs$delimiter$material$delimiter$material_id$delimiter$count_ean_mat$delimiter$eod_epoch$delimiter$default_end_epoch$delimiter$product$delimiter$material_id_extract$delimiter$team$delimiter$_shift$delimiter$_status$delimiter$produced_su$delimiter$produced_pad$delimiter$totalpad$delimiter$runningscrap$delimiter$stopscrap$delimiter$linespeed_tar$delimiter$linespeed_act$delimiter$line_desc$delimiter$plant_desc$delimiter$line_id$delimiter$totalcases$delimiter$prodperbag$delimiter$bagspercase$delimiter$casesperpallet$delimiter$prodperstat$delimiter$p_size$delimiter$run_time$delimiter$unit_id$delimiter$unit_desc$delimiter$plid$delimiter$gleds_transfer_status$delimiter$eops_transfer_status$delimiter$prod_id$delimiter$site$delimiter$production_id
95                                echo $final_result_set >> $stage_csv
96
97                                let loop_rec=date_diff-1
98                                if [ $loop_rec -ne 0 ]; then
99                                NEXT_DATE=$(date +%Y-%m-%d -d "$start_date")
100        while [ $ok_seq -le $loop_rec ]
101        do
102
103                                                NEXT_DATE=$(date +%Y-%m-%d -d "$NEXT_DATE + 1 day")
104                                                default_start_epoch1=`echo "$NEXT_DATE $current_time"`
105                                                default_end_epoch1=`echo "$NEXT_DATE $eod_time"`
106
107                                                final_result_set=$ean_cs$delimiter$material$delimiter$material_id$delimiter$count_ean_mat$delimiter$default_start_epoch1$delimiter$default_end_epoch1$delimiter$product$delimiter$material_id_extract$delimiter$team$delimiter$_shift$delimiter$_status$delimiter$produced_su$delimiter$produced_pad$delimiter$totalpad$delimiter$runningscrap$delimiter$stopscrap$delimiter$linespeed_tar$delimiter$linespeed_act$delimiter$line_desc$delimiter$plant_desc$delimiter$line_id$delimiter$totalcases$delimiter$prodperbag$delimiter$bagspercase$delimiter$casesperpallet$delimiter$prodperstat$delimiter$p_size$delimiter$run_time$delimiter$unit_id$delimiter$unit_desc$delimiter$plid$delimiter$gleds_transfer_status$delimiter$eops_transfer_status$delimiter$prod_id$delimiter$site$delimiter$production_id
108                                                echo $final_result_set >> $stage_csv
109              let ok_seq=ok_seq+1
110
111        done
112                                fi 
113                else 
114                                final_result_set=$ean_cs$delimiter$material$delimiter$material_id$delimiter$count_ean_mat$delimiter$current_date$delimiter$current_time$delimiter$current_date$delimiter$eod_time$delimiter$product$delimiter$material_id_extract$delimiter$team$delimiter$_shift$delimiter$_status$delimiter$produced_su$delimiter$produced_pad$delimiter$totalpad$delimiter$runningscrap$delimiter$stopscrap$delimiter$linespeed_tar$delimiter$linespeed_act$delimiter$line_desc$delimiter$plant_desc$delimiter$line_id$delimiter$totalcases$delimiter$prodperbag$delimiter$bagspercase$delimiter$casesperpallet$delimiter$prodperstat$delimiter$p_size$delimiter$run_time$delimiter$unit_id$delimiter$unit_desc$delimiter$plid$delimiter$gleds_transfer_status$delimiter$eops_transfer_status$delimiter$prod_id$delimiter$site$delimiter$production_id
115                                echo $final_result_set >> $stage_csv
116                fi
117done < output_partition.txt
118
119
120hdfs dfs -put -f  $stage_csv /poc/poc_CCTS/test_shweta123/$stage_csv 
121beeline -u "jdbc:hive2://sda1node04.na.pg.com:10000/default;principal=hive/_HOST@BUCCLUSTER.NA.PG.COM"  -e "create table IF NOT EXISTS poc_ccts.sd_mps_t1(ean_cs bigint, material bigint, material_id int, count_ean_mat int, starttime timestamp, endtime timestamp, product string, material_id_extract int, team string, shift int, status string, produced_su string, produced_pad int, totalpad int, runningscrap int, stopscrap int, linespeed_tar int, linespeed_act int, line_desc string, plant_desc string, line_id int, totalcases int, prodperbag int, bagspercase int, casesperpallet int, prodperstat int, p_size string, run_time  int, unit_id int,  unit_desc string, plid int, gleds_transfer_status int, eops_transfer_status int, prod_id int, site string, production_id int)row format delimited fields terminated by '|' stored as textfile;" -e  "LOAD DATA INPATH '/poc/poc_CCTS/test_shweta123/$stage_csv' INTO TABLE poc_ccts.sd_mps_t1;"