How to use hive variable substitution rightly

Question

When I'm using variable substitution in hive, I meet some errors, but I need your help.

My code:

set hievar:b='on t1.id=t2.id where t2.id is null';
select * from t_old as t1 full outer join t_new as t2 ${b};

when I run this code in hive shell, it give me some error about ${b}.

I also try this:

set hivevar:c='select * from t_old as t1 full outer join t_new as t2 on t1.id=t2.id where t2.id is null';
${c};

It gives me the same error.

Possible duplicate of [How to set variables in HIVE scripts](https://stackoverflow.com/questions/12464636/how-to-set-variables-in-hive-scripts) — Gaurang Shah, Mar 20 '19 at 17:10

leftjoin · Answer 1 · 2019-03-20T12:06:04.773

Fix hivevar namespace name (in your code it is hievar) and remove quotes, because they are also passed as is in Hive.

Example:

set hivevar:b=where 1=1; --without quotes
select 1 ${hivevar:b}; --you can do without hivevar: as in your example

Result:

OK
1
Time taken: 0.129 seconds, Fetched: 1 row(s)

Second example:

hive> set hivevar:c=select 1 where 1=1;
hive> ${c};
OK
1
Time taken: 0.491 seconds, Fetched: 1 row(s)

1 Answers1