Hive Variables in Where Clause

Question

SELECT * FROM Tablename WHERE 1=1 (AND COL1=VALUE1 AND COL2=VALUE2) --->$(VAR)

SELECT * FROM Tablename WHERE 1=1 $(VAR)

This is possible in Hive?

Answer 1

Yes, it is possible.

set hivevar:var1='and col1=10 and col2=10';

! echo "select * from table where 1=1 ${hivevar:var1}";
select ${hivevar:var1}


[prjai@lnx0689 prvys]$ hive -f test.hql
"select * from table where 1=1 'and col1=10 and col2=10'"
OK
and col1=10 and col2=10
Time taken: 2.152 seconds, Fetched: 1 row(s)

For more details on how to use hivevar and hiveconf, refer https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VariableSubstitution

What is the difference between -hivevar and -hiveconf?

Answer 2

Set hive variable variable first.

 set hivevar:queryPortion='and col1=value1 and col2=value2';

After that just retrieve it in your query

 select * from table where 1=1 ${queryPortion}