Below is my shell script from which I am trying to invoke few Hive SQL queries and the below shell script works fine.
Problem Statement:-
If you see my first `hive -e` block in the below shell script which contains a very long Hive SQL query. Sometimes that Hive SQL query gets failed due to certain reasons and when it gets failed it moved to second hive sql query after sleeping for 120 seconds which I don't want. Is there any way if my first `hive SQL query` gets failed due to any reasons, it should get stopped automatically at that point without executing any other hive sql query after the first one. I should be notified in an email of this error. I am running SunOS.
bash-3.00$ uname -a
SunOS lvsaishdc3in0001 5.10 Generic_142901-02 i86pc i386 i86pc
Below is my shell script.
#!/bin/bash
HADOOP_HOME=/home/hadoop/latest
export HADOOP_HOME
JAVA_HOME=/usr/jdk/latest
export JAVA_HOME
HIVE_OPTS="$HIVE_OPTS -hiveconf mapred.job.queue.name=hdmi-technology"
export HIVE_OPTS
DATE_YEST_FORMAT1=`perl -e 'use POSIX qw(strftime); print strftime "%Y-%m-%d",localtime(time()- 3600*504);'`
echo $DATE_YEST_FORMAT1
DATE_YEST_FORMAT2=`perl -e 'use POSIX qw(strftime); print strftime "%Y%m%d",localtime(time()- 3600*504);'`
echo $DATE_YEST_FORMAT2
hive -e "
set mapred.job.queue.name=hdmi-technology;
set mapred.output.compress=true;
set mapred.output.compression.type=BLOCK;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.LzoCodec;
add jar UserDefinedFunction.jar;
create temporary function rank as 'com.ebay.hive.udf.Rank';
insert overwrite table lip_data_quality partition (dt='$DATE_YEST_FORMAT2')
SELECT table2.buyer_id
,table2.count2 as count2
,coalesce(table1.error, cast(0 AS BIGINT)) AS count1
FROM (
SELECT buyer_id
,count(1) AS count2
FROM (
SELECT t1 [0] AS buyer_id
,t1 [1] AS item_id
,created_time
FROM (
SELECT split(ckey, '\\\\|') AS t1
,created_time
FROM (
SELECT CONCAT (
buyer_id
,'|'
,item_id
) AS ckey
,created_time
FROM dw_checkout_trans
WHERE to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) AS BIGINT))) = '$DATE_YEST_FORMAT1' distribute BY ckey sort BY ckey
,created_time DESC
) a
WHERE rank(ckey) < 1
) X
ORDER BY buyer_id
,created_time DESC
) a
WHERE rank(buyer_id) < 10
GROUP BY buyer_id
) table2
LEFT OUTER JOIN (
SELECT TT1.BUYER_ID
,SUM(CASE
WHEN ABS(cast((TT2.prod_and_ts_split / 1000) - UNIX_TIMESTAMP(TT1.created_time) AS DOUBLE)) <= 900
THEN 0
ELSE 1
END) AS ERROR
FROM (
SELECT buyer_id
,item_id
,rank(buyer_id)
,created_time
FROM (
SELECT t1 [0] AS buyer_id
,t1 [1] AS item_id
,created_time
FROM (
SELECT split(ckey, '\\\\|') AS t1
,created_time
FROM (
SELECT CONCAT (
buyer_id
,'|'
,item_id
) AS ckey
,created_time
FROM dw_checkout_trans
WHERE to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) AS BIGINT))) = '$DATE_YEST_FORMAT1' distribute BY ckey sort BY ckey
,created_time DESC
) a
WHERE rank(ckey) < 1
) X
ORDER BY buyer_id
,created_time DESC
) a
WHERE rank(buyer_id) < 10
) TT1
LEFT OUTER JOIN (
SELECT t1 [0] AS uid
,t1 [1] AS product_id
,prod_and_ts_split
FROM (
SELECT split(ckey, '\\\\|') AS t1
,prod_and_ts_split
FROM (
SELECT CONCAT (
uid
,'|'
,product_id
) AS ckey
,prod_and_ts_split
FROM (
SELECT uid
,product_id
,prod_and_ts_split
FROM (
SELECT *
FROM (
SELECT uid
,prod_and_ts.product_id AS product_id
,prod_and_ts.timestamps AS timestamps
FROM pds_attribute_data_realtime LATERAL VIEW explode(last_items_purchased) exploded_table AS prod_and_ts
) prod_and_ts
) tt2 LATERAL VIEW explode(split(timestamps, '#')) exploded_table2 AS prod_and_ts_split
WHERE to_date(from_unixtime(cast(prod_and_ts_split / 1000 AS BIGINT))) = '$DATE_YEST_FORMAT1'
GROUP BY uid
,product_id
,prod_and_ts_split
ORDER BY uid
,prod_and_ts_split DESC
) X
distribute BY ckey sort BY ckey
,prod_and_ts_split DESC
) a
WHERE rank(ckey) < 1
) YY
ORDER BY uid
,prod_and_ts_split DESC
) TT2 ON (
TT1.item_id = TT2.product_id
AND TT1.BUYER_ID = TT2.uid
)
GROUP BY TT1.BUYER_ID
) table1 ON table1.buyer_id = table2.buyer_id
ORDER BY table2.buyer_id;"
sleep 120
QUERY1=`hive -e "
set mapred.job.queue.name=hdmi-technology;
SELECT SUM(total_items_purchased), SUM(total_items_missingormismatch) from lip_data_quality where dt='$DATE_YEST_FORMAT2';"`
QUERY2=`hive -e "
set mapred.job.queue.name=hdmi-technology;
SELECT 100 * SUM(total_items_missingormismatch*1.0) / SUM(total_items_purchased) FROM lip_data_quality where dt='$DATE_YEST_FORMAT2';"`
rm /tmp/chart.html
TEMP=`mktemp -t chart.html`
NOERROR=100-$QUERY2
ERROR=$QUERY2
cat > $TEMP <<EOF
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
// Load the Visualization API and the piechart package.
google.load('visualization', '1.0', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);
// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {
// Create the data table.
var data = new google.visualization.DataTable();
data.addColumn('string', 'Title');
data.addColumn('number', 'Value');
data.addRows([
['No Error Percentage', $NOERROR],
['Error Percentage', $ERROR]
]);
// Set chart options
var options = {'title':'LIP Data Quality Report',
'width':700,
'height':600};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<!--Div that will hold the pie chart-->
<div id="chart_div" style="width:900px; height: 800px;"></div>
</body>
</html>
EOF
mailx -s "LIP Data Quality Report for $DATE_YEST_FORMAT1" -r email_person@sample_email.com <<EOF
Data Successfully loaded into LIP_DATA_QUALITY table
Total Items Purchased: `echo $QUERY1 | awk '{print $1}'`
Total Items MissingorMismatch: `echo $QUERY1 | awk '{print $2}'`
Error Percentage: $QUERY2
********* Before Making Assumptions, please read the below Notes***********************
Above Percentage could be mainly because of below reasons:-
1) Multiple USER's sharing the same browser and export is done basis on LAST_LOGGED_IN_USER.
2) Either the Export Issue.
3) Thirdly, ITEM was not captured properly in PDS.
`uuencode /tmp/chart.html percentage_graph.html`
EOF