Whew, thanks everyone! I think wrapping quotes around that did it.
Nicole, I was going to attempt that as a last resort. But the actual query is
much longer and it would be extremely undesirable to do so.
Regards,
Saurabh
----------------------------------------
> From: nicole.ge...@memorylane.com
> To: user@hive.apache.org
> Subject: Re: Passing date as hive configuration variable
> Date: Thu, 10 May 2012 18:39:10 +0000
>
> 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
> >
> >
>
