Run Hive Script with variable - problem with passing variable

Question

I'm using DBVisualiser to run the following script (Expecting to pass variable in query and retrieve data based on where condition)

set Id='1';

select * from MyTable where account_id = '${hivevar:Id}' limit 5

Unfortunatelly, when I run this script I see that query which is executed is as follows:

set Id='1';

select * from mytable where account_id = '${hivevar:Id}' limit 5

But when I run query with hardcoded value

select * from mytable where account_id = '1' limit 5

then I get expected dataset. I would apprecaie if anyone could help me to learn what I do wrong.

Thanks in advance.

In DbVisualizer you need to use another syntax: `select * from mytable where account_id = '${id||1' limit 5;`. Read more in: http://confluence.dbvis.com/display/UG/Using+DbVisualizer+Variables — roger, May 19 '21 at 03:54

score 0 · Answer 1 · answered May 15 '21 at 16:45

Variables which were set without namespace are hiveconf variables, not hivevar. Though you can specify the namespace explicitly.

Try this:

set Id=1; --No need to quote here if it is quoted in the select
-- use hiveconf
select * from MyTable where account_id = '${hiveconf:Id}' limit 5;

Or this:

--specify the namespace
set hivevar:Id=1;
select * from MyTable where account_id = '${hivevar:Id}' limit 5;

1 Answers1