hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tucker, Matt" <>
Subject RE: Passing date as hive configuration variable
Date Thu, 10 May 2012 18:34:09 GMT
You'll want to wrap ${hiveconf:ref_date} in quotes, so that's it's passed as a string in the

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


-----Original Message-----
From: Saurabh S [] 
Sent: Thursday, May 10, 2012 2:06 PM
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:

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


P.S. I'm using this as reference:


View raw message