Search code examples
hivehiveql

How to select rows with one of two column values can't be null?

For example:

hive> select mid, tag1, tag2, dt from message_use_tags where dt="20211107" and (tag1 != '' or tag2 != 'NULL') limit 50;

I want to specify:

  1. date = '20211107'

  2. tag1 and tag2 can't be both empty string at the same time. One of them can be empty. However, the following doesn't achieve the purpose:

    tag1 != '' or tag2 != 'NULL'

In the result set, there are rows where both tag1 and tags are empty strings. How to modify the statement to get the results desired?

Solution 
  • hive> select mid, tag1, tag2, dt from message_use_tags where dt="20211107" and (tag1 != '' or tag2 != 'NULL') and (tag1 != '' and tag2 != '') limit 50;