Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Variable Substitution


Copy link to this message
-
Variable Substitution
Using CDH3u3 (Hive 0.7.1), it appears that variable substitution becomes
disabled when I use a variable in a query that hasn't been defined.

For instance, using the following script:

set var2=2013-02-01;
set var3=2013-02-10;

SELECT clndr_dt FROM calendar WHERE clndr_dt LIKE "${hiveconf:var1}" OR
(clndr_dt >= "${hiveconf:var2}" AND clndr_dt <= "${hiveconf:var3}") LIMIT 1;
I would expect the results job configuration to list the hive.query.string
as:

SELECT clndr_dt FROM calendar WHERE clndr_dt LIKE "${hiveconf:var1}" OR
(clndr_dt >= "2013-02-01" AND clndr_dt <= "2013-02-10") LIMIT 1;
(or with ${hiveconf:var1} removed, leaving an empty string).

Instead, it prints the query as-is (with the variable placeholders).  If I
set var1=2012-01-01, it properly substitutes the variables, but it only
returns the '2012-01-01' record (not the 2013 records).

SELECT clndr_dt FROM calendar WHERE clndr_dt LIKE "2012-01-01" OR (
clndr_dt >= "2013-02-01" AND clndr_dt <= "2013-02-10" ) LIMIT 1

I was originally planning to use this for partition pruning, but it doesn't
appear to be the cause as the calendar table is not partitioned.

Is there something that I've overlooked?

Thanks!
+
Dean Wampler 2013-03-06, 21:30
+
Edward Capriolo 2013-03-06, 21:41
+
Matt Tucker 2013-03-06, 21:50
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB