Is there a Hive equivalent of SQL "not like"

Question

While Hive supports positive like queries: ex.

select * from table_name where column_name like 'root~%';

Hive Does not support negative like queries: ex.

select * from table_name where column_name not like 'root~%';

Does anyone know an equivalent solution that Hive does support?

score 54 · Answer 1 · edited Aug 25 '11 at 04:23

54

Try this:

Where Not (Col_Name like '%whatever%')

also works with rlike:

Where Not (Col_Name rlike '.*whatever.*')

edited Aug 25 '11 at 04:23

CoolBeans

20,654
10
86
101

answered Apr 14 '11 at 20:56

HAL9000

541
3
2

score 8 · Answer 2 · answered Oct 29 '14 at 13:52

8

NOT LIKE have been supported in HIVE version 0.8.0, check at JIRA.

https://issues.apache.org/jira/browse/HIVE-1740

answered Oct 29 '14 at 13:52

Sanjiv

1,795
1
29
45

score 6 · Answer 3 · answered Jun 19 '14 at 08:41

6

In SQL:

select * from table_name where column_name not like '%something%';

In Hive:

select * from table_name where not (column_name like '%something%');

answered Jun 19 '14 at 08:41

minhas23

9,291
3
58
40

score 3 · Accepted Answer · edited Apr 03 '12 at 19:59

3

Check out https://cwiki.apache.org/confluence/display/Hive/LanguageManual if you haven't. I reference it all the time when I'm writing queries for hive.

I haven't done anything where I'm trying to match part of a word, but you might check out RLIKE (in this section https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#Relational_Operators)

This is probably a bit of a hack job, but you could do a sub query where you check if it matches the positive value and do a CASE (http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF#Conditional_Functions) to have a known value for the main query to check against to see if it matches or not.

Another option is to write a UDF which does the checking.

I'm just brainstorming while sitting at home with no access to Hive, so I may be missing something obvious. :)

Hope that helps in some fashion or another. \^_^/

EDIT: Adding in additional method from my comment below.

For your provided example colName RLIKE '[^r][^o][^o][^t]~\w' That may not be the optimal REGEX, but something to look into instead of sub-queries

edited Apr 03 '12 at 19:59

thefroatgt

896
2
12
19

answered Apr 12 '11 at 00:31

QuinnG

6,346
2
39
47

it looks like CASE will do what I need it to, but Im having trouble with the syntax. Does it go in the Select clause or the Where clause? This attempt failed: 'create table tmp_table as CASE WHEN a.username like 'twitter~%' THEN '' ELSE select sum(file_size) as storage, sum(bandwidth) as bandwidth from tmp_sites_compare1 a;' But it doesnt make sense to me for it to be in the where clause... Thoughts? – CMaury Apr 12 '11 at 18:11
@CMaury: The `CASE` is in the `SELECT` clause. `SELECT CASE WHEN ... THEN ... END AS colName FROM...` – QuinnG Apr 12 '11 at 19:00
1

@CMaury: I started playing around with `RLIKE` for something at my work, and while it's not going to be optimal for all situations you can use the regex 'not' `^`. For your provided example `colName RLIKE '[^r][^o][^o][^t]~\\w'` That may not be the optimal REGEX, but something to look into instead of sub-queries – QuinnG Apr 13 '11 at 04:03
Haha. yea, that is even better than sub-queries... Thanks for all the help. Accepting your answer. – CMaury Apr 13 '11 at 20:31
1

To do a negative regex lookahead you can do `(?!root)` but note that if you are doing that in bash you will need to escape the `!` like `\!` so bash doesn't think it is an event. so `hive -e "SELECT * FROM table_name WHERE colName RLIKE '^(?\!root~).*$'"` should work exactly like the not like in other DBs – Kyle Kochis May 18 '11 at 14:51

score 1 · Answer 5 · answered Jul 08 '15 at 15:00

1

Using regexp_extract works as well:

select * from table_name where regexp_extract(my_column, ('myword'), 0) = ''

answered Jul 08 '15 at 15:00

Reid Minto

181
5

score 0 · Answer 6 · edited Apr 24 '13 at 12:00

0

Actually, you can make it like this:

select * from table_name where not column_name like 'root~%';

edited Apr 24 '13 at 12:00

von v.

16,868
4
60
84

answered Apr 24 '13 at 11:43

zorro

1

score 0 · Answer 7 · edited Feb 16 '17 at 19:55

0

In impala you can use != for not like:

columnname != value

edited Feb 16 '17 at 19:55

FelixSFD

6,052
10
43
117

answered Feb 16 '17 at 19:49

DrSD

151
2
12

score 0 · Answer 8 · answered Jul 27 '21 at 05:57

as @Sanjiv answered

hive has support not like

0: hive> select * from dwtmp.load_test;
+--------------------+----------------------+
| load_test.item_id  | load_test.item_name  |
+--------------------+----------------------+
| 18282782           | NW                   |
| 1929SEGH2          | BSTN                 |
| 172u8562           | PLA                  |
| 121232             | JHK                  |
| 3443453            | AG                   |
| 198WS238           | AGS                  |
+--------------------+----------------------+
6 rows selected (0.224 seconds)

0: hive> select * from dwtmp.load_test where item_name like '%ST%';
+--------------------+----------------------+
| load_test.item_id  | load_test.item_name  |
+--------------------+----------------------+
| 1929SEGH2          | BSTN                 |
+--------------------+----------------------+
1 row selected (0.271 seconds)

0: hive> select * from dwtmp.load_test where item_name not like '%ST%';
+--------------------+----------------------+
| load_test.item_id  | load_test.item_name  |
+--------------------+----------------------+
| 18282782           | NW                   |
| 172u8562           | PLA                  |
| 121232             | JHK                  |
| 3443453            | AG                   |
| 198WS238           | AGS                  |
+--------------------+----------------------+
5 rows selected (0.247 seconds)

Is there a Hive equivalent of SQL "not like"

8 Answers8