How do I set a variable in an Impala query using HUE?

Question

I need to add parameters in several locations in a long query. I want to use parameters because I need to run the query multiple times with different values substituted in. This is very cumbersome because I need to replace the text in all locations whenever I need to change my filter criteria. Is there a way to set a variable in Impala via HUE?

Initial research indicates that this is possible when working with the impala-shell but for HUE, I've found nothing.

Disquisition

Answer 1

You can set variables in Impala via HUE as you can see in the following picture

Off

This works and it also accommodates multiple values (e.g. - Monopoly','Scrabble) for use with the 'in' operator. Thank you. – Disquisition

It was useful and you can upvote, I would appreciate it, thanks. – Off

Line 2 SET game; for me results in ParseExceptopm: Syntax error in line 1:undefined: SET game ^ Encountered: EOF Expected: = CAUSED BY: Exception: Syntax error. If I try to set a value, e.g. SET game=10;, I get Invalid query option: game. Is there some special setting in Hue or Impala needed for this SET syntax to work? – Sourdine

Hi @RyanJendoubi You can remove 'SET game' The variable is set when you type: '${var_name}' in the editor in the WHERE clause. For instance: SELECT field1, field2 FROM table_name WHERE field3 = '${var_name}'; and you can set the var value below the query as you can see in the picture example. I hope this will solve your doubts. – Off

I have removed the former picture. It was confusing. – Off

Answer 2

You can also use this script. Here type of variables int and date. Also can define a default value.

SELECT id, fullname, birthdate 
FROM db.scientists
WHERE id <= ${id=2} 
AND to_date(birthdate) BETWEEN '${mindate=1900-01-01}' AND '${maxdate=2000-01-01}'   

Lawlor