Another option is to do it all in the shell:
#/bin/sh
somedate=$(date -d '2 day ago' +"%Y-%m-%d")
echo "$somedate"
script="
select count(*)
from myschema.mytable
where local_dt > $somedate
"
echo "$script"
hive -e $script > output.dat
On 5/10/12 11:34 AM, "Tucker, Matt" <matt.tuc...@disney.com> wrote:
>You'll want to wrap ${hiveconf:ref_date} in quotes, so that's it's passed
>as a string in the query.
>
>SELECT "${hiveconf:ref_date}" FROM dummytable LIMIT 1;
>
>Matt Tucker
>Associate eBusiness Analyst
>Walt Disney Parks and Resorts Online
>Ph: 407-566-2545
>Tie: 8-296-2545
>
>CONFIDENTIAL
>
>-----Original Message-----
>From: Saurabh S [mailto:saurab...@live.com]
>Sent: Thursday, May 10, 2012 2:06 PM
>To: user@hive.apache.org
>Subject: Passing date as hive configuration variable
>
>
>I'm having a hard time passing a date as a hive environment variable.
>
>The setting is this: The table I'm querying is partitioned on a date
>column, say, local_dt. I wish to query on last two days' worth of data.
>Unfortunately there seems to be no way of getting the current date
>without either scanning the entire table on all local dates
>(current_timestamp()) or writing a custom UDF. So, I'm trying to pass it
>as a parameter within shell.
>
>Here is my test_query.sql:
>
>
>select count(*)
>from myschema.mytable
>where local_dt > ${hiveconf:ref_date}
>;
>
>
>and here is the driver shell script:
>
>
>#/bin/sh
>somedate=$(date -d '2 day ago' +"%Y-%m-%d") echo $somedate hive -hiveconf
>ref_date=$somedate -f test_query.sql > output.dat
>
>
>The problem is that Hive is performing subtractions in the date format.
>echo $somedate produces "2012-05-08" and "select ${hiveconf:ref_date}
>from dummytable limit 1" produces "1999".
>
>I noticed that there is an option to "set
>hive.variable.substitute=false;", but in that case, hive throws the
>following error:
>FAILED: Parse Error: line 3:7 cannot recognize input near '$' '{'
>'hiveconf' in select clause
>
>Regards,
>Saurabh
>
>P.S. I'm using this as reference:
>http://hive.apache.org/docs/r0.9.0/language_manual/var_substitution.html
>
>
