How do we drop partitions in hive with regex. Is it possible?

Question

I am trying to run the following

alter table historical_data drop partition (my_date not rlike '[A-Za-z]');

Which gives me an Exception

org.apache.hadoop.hive.ql.parse.ParseException: line 2:69 mismatched input 'not' expecting set null in drop partition statement

I couldn't find anything similar. I did see one answer on some question in SO but it doesn't work.

Any help is appreciated.

Answer 1

Regexp is not supported unfortunately.

You can use all these comparators < > <= >= <> = != maybe it will help. See usage in this answer: https://stackoverflow.com/a/56646879/2700344

See also this jira Extend ALTER TABLE DROP PARTITION syntax to use all comparators

Also one more jira not implemented yet: Extend ALTER TABLE DROP PARTITION syntax to use multiple conditions

Impala supports LIKE in drop partition:

alter table historical_data drop partition (year < 1995, last_name like 'A%');

Created this Jira for adding regexp, please vote in the Jira if you need it.