Regex in Hive QL (RLIKE)
Looks good to me.

Your specification, "at least one group in second format appears", is stated in the positive, yet your not rlike is stated in the negative. You might consider phrasing it as where groups rlike '(^|#)[A-Z][A-Z](#|$)'.

You asked about performance. These queries will certainly have to read all rows, which costs more CPU cycles than evaluating the regexes. You might consider adding derived boolean column(s) that indicate whether "format1" and "format2" groups are present. Then you could index on such a column.