Manipulating hive variable

Question

I would like to subtract a number from the hive variable passed. For example:

SET hiveconf:window_size = 12
SELECT id , max(marks) OVER(ORDER BY Date_time ROWS BETWEEN ${hiveconf:window_size}-1 PRECEDING AND CURRENT ROW) from Students;

But ${hiveconf:window_size}-1 in window function is giving error.

Can anyone provide any suggestions on this.

See https://community.hortonworks.com/articles/60309/working-with-variables-in-hive-hive-shell-and-beel.html — Harold, Aug 14 '18 at 12:20

score 1 · Answer 1 · answered Aug 14 '18 at 12:39

1

It does not like inline calculation of ROWS BETWEEN boundary. Subtract 1 before executing query.

This will work:

SET hiveconf:window_size=11;
SELECT id , max(marks) OVER(ORDER BY Date_time ROWS BETWEEN ${hiveconf:window_size} PRECEDING AND CURRENT ROW) from Students
;

Alternatively you can calculate it in the shell and pass to the Hive script as a variable. See here how to pass a variable from the shell: https://stackoverflow.com/a/37821218/2700344

answered Aug 14 '18 at 12:39

leftjoin

36,950
8
57
116

Can we use a different hive variable to subtract 1 from the actual hive variable. Eg: hiveconf:sub_one = ${hiveconf:window_size}-1 – Ravi Kiran Gururaja Aug 15 '18 at 10:10
Then pass hiveconf:sub_one in ROWS BETWEEN boundary. – Ravi Kiran Gururaja Aug 15 '18 at 10:10
1

@RaviKiran No. Hive does not calculate variables and sustitutes them as is. So, hiveconf:sub_one in this case will be resolved to the same `${hiveconf:window_size}-1` – leftjoin Aug 15 '18 at 11:03

Manipulating hive variable

1 Answers1