Pass date from shell script to query

Question

Below is my shell script from which I am running my Hive query. In this I am calculating the Yesterday's date and I am passing that Yesterday's date in the where clause. But that query is not working for me.

#!/bin/bash
DATE_YEST=`TZ=GMT+48 date +%Y%m%d`
echo $DATE_YEST
hive -S -e 'SELECT * FROM PDS_ATTRIBUTE_DATA_REALTIME WHERE dt=$DATE_YEST'

I am running the above script like this-

sh -x test.sh

Is there something wrong with the way I am passing that DATE_YEST date in the where clause?

Answer 1

Try this:

#!/bin/bash
DATE_YEST=`TZ=GMT+48 date +%Y%m%d`
echo $DATE_YEST
echo "SELECT * FROM PDS_ATTRIBUTE_DATA_REALTIME WHERE dt=$DATE_YEST"

Output will be:

20120806
SELECT * FROM PDS_ATTRIBUTE_DATA_REALTIME WHERE dt=20120806

And if you need the quotes around the select, use this:

#!/bin/bash
DATE_YEST=`TZ=GMT+48 date +%Y%m%d`
echo $DATE_YEST
echo "\"SELECT * FROM PDS_ATTRIBUTE_DATA_REALTIME WHERE dt=$DATE_YEST\""

Output will be:

20120806
"SELECT * FROM PDS_ATTRIBUTE_DATA_REALTIME WHERE dt=20120806"

Assuming you want the quotes your script will look like this:

#!/bin/bash
DATE_YEST=`TZ=GMT+48 date +%Y%m%d`
echo $DATE_YEST
hive -S -e "\"SELECT * FROM PDS_ATTRIBUTE_DATA_REALTIME WHERE dt=$DATE_YEST\""