hive:select all column exclude two

Question

How to select all column exclude columns 'date' and 'cst_id' in hive?

Answer 1

Try below -

For single session - set hive.support.quoted.identifiers=none;

select `(date|cst_id)?+.+` from myTable.

refer quotedIdentifiers for more information on hive select using regular expressions

Answer 2

The accepted answer and the example regex on the hive manual page is flawed. If you want to exclude two columns day and day_hour, (day|day_hour)?+.+ will still match day_hour column. That's because regex engine is eager on |. Although changing the order to (day_hour|day)?+.+ can solve this issue, the better method is using negative lookahead, (?!(day|day_hour)$).+.