Using hive variable in regex

Question

I'd like to extract texts based on some key words from Hive database.

It works if my script looks like,

SELECT text
FROM table
WHERE text RLIKE '.?(aaa|bbb|ccc).?';

But in order to make the script more readable, I'd like to set the key words as a variable in hive. But the script fails if it looks like,

SET hivevar:KeyWords='.?(aaa|bbb|ccc).?';

SELECT text
FROM table
WHERE text RLIKE ${hivevar:KeyWords};

Any idea? Thanks.

Fu

Just FYI: The '.?(aaa|bbb|ccc).?' is actually the same as 'aaa|bbb|ccc' in this case, you are using it with RLIKE. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 13, 2017 at 19:07
Try set KeyWords='aaa|bbb|ccc' and then RLIKE ${hiveconf:KeyWords}. What is your Hive version? The hivevar variables can be used with the 0.8.0 version and higher. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 13, 2017 at 19:10
Still get error information like 'FAILED: ParseException line 16:31 cannot recognize input near 'RLIKE' '$' '(' in expression specification' — violetefu
– violetefu, Commented Jun 13, 2017 at 19:19
I have 2 ideas. (1) Please share the real table name (obviously not table) and column name (probably not text) (2) check the value of set hive.variable.substitute — David דודו Markovitz
– David דודו Markovitz, Commented Jun 13, 2017 at 19:32

marc_s · Accepted Answer · 2017-07-17 03:57:05Z

1

I use hivevar for variable substitution:

set myVar='.?(aaa|bbb|ccc).?';
select * from <your table> where <your column> RLIKE ${myVar};

edit:fixing brain fart...

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 13, 2017 at 20:43

Andrew

8,8933 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1