How to use nested variable substitution in a hive query

user_15291440 0
Viewed 238

I've tried to use nested variables directly in a hive query. I'm using Hive and Dbeaver.

My goal is to loop through a list of conditions and save the results in numbered tables (condition_1 | tbl_1, condition_2, tbl_2, etc) Here's an example:

@set condition_1 = col1 in (1,10,11) and col2 in (1000,10000)
@set condition_2 = col1 in (2,20,22) and col2 in (2000,20000)
@Set ctrl= 1

create table tbl_${ctrl}
select ${ctrl} as id, * from my_table where ${condition_${ctrl}}

When I run the select statement, it fails at where statement and resolves only ctrl variable and I get this error message:

SQL Error [40000] [42000]: Error while compiling statement: FAILED: ParseException line 22:6 cannot recognize input near '$' '{' 'condition_1' in expression specification

I think hive is ignoring the last closing curly bracket. Any help would be appreciated!

I read the Language Manual Variable Substitution but it only show this:

set a=1;
set b=a;
set c=${hiveconf:${hiveconf:b}};
set c;
--uses nested variables.
dbeaver hiveql hive variables
1 Answers

I think hive is ignoring the last closing curly bracket.

Because dollar sign needs to be placed before curly bracket.

Do it like in manual and it will work. Specify hiveconf namespace and do not forget about dollar sign before curly bracket: ${hiveconf:condition_${hiveconf:ctrl}}

This example works fine:

set condition_1 = col1 in (1,10,11) and col2 in (1000,10000);
set condition_2 = col1 in (2,20,22) and col2 in (2000,20000);
Set ctrl= 1;

with Table1 as( 
SELECT stack (2,
1, 1000,
2, 2000
) AS  (col1, col2)
)

select ${hiveconf:ctrl} as id, t.* from Table1 t where ${hiveconf:condition_${hiveconf:ctrl}}
user_27003440
Related