2

While Hive doesn't supports multi-value LIKE queries which are supported in SQL : ex.

SELECT * FROM user_table WHERE first_name LIKE ANY ( 'root~%' , 'user~%' );

We can convert it into equivalent HIVE queries as : 

SELECT * FROM user_table WHERE first_name LIKE 'root~%' OR first_name LIKE 'user~%'

Does anyone know an equivalent solution that Hive does support in case sub-query is used with LIKE ? Have a look at below example : 

SELECT * FROM user_table WHERE first_name LIKE ANY ( SELECT expr FROM exprTable);

As It doesn't have values in expression, I can't use same approach for generating multiple LIKE expression separated with OR / AND operator. Initially I thought to write HIVE UDF for it ? Can you please help me supporting such expression and finding HIVE equivalent ?

Sanjiv
  • 1,795
  • 1
  • 29
  • 45
  • 1
    The questions are 1 year apart so maybe the solution was not available when it was posted but I think this is the good solution: http://stackoverflow.com/questions/33626645/hive-query-with-multiple-like-operators – Picarus Apr 06 '17 at 00:35

4 Answers4

1

You can use Hive's RLIKE relational operator as shown below,

SELECT * FROM user_table WHERE first_name RLIKE 'root~|user~|admin~';

Hope this helps!

Sakthivel
  • 576
  • 8
  • 15
0

This is a case involving theta joins in Hive. There is a wiki page for this and a jira request. Please go through the details here on this page: https://cwiki.apache.org/confluence/display/Hive/Theta+Join

Your case is similar to the Side-Table Similarity case given on the page.

Amar
  • 3,825
  • 1
  • 23
  • 26
0

You need to convert the expr values into a map and then use regular expression to find the like. Alternatively you can also use union all with all the like expressions in separate SQL - the query might become tedious so you can programatically generate it.

user3406675
  • 148
  • 9
0

What about this using EXISTS 

SELECT * FROM user_table WHERE EXISTS ( SELECT * FROM exprTable WHERE first_name LIKE expr );
Sanjiv
  • 1,795
  • 1
  • 29
  • 45