CODEWITHSUNDEEP
hivehiveql
Ravi Kiran G
Ravi Kiran G

Reputation: 467

Manipulating hive variable

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.

Upvotes: 1

Views: 178

Answers (1)

leftjoin
leftjoin

Reputation: 38325

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

Upvotes: 1

Related Questions