Shulou(Shulou.com)05/31 Report--

How to realize the and not in clause, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Hive example explains the implementation of in and not in clauses

Currently, hive does not support syntax that contains query clauses in in or not in, so it can only be implemented through left join.

Suppose you have a login table login (the same day's login record, only one uid), and a user registry regusers (the same day's registered user, with only one uid field), both tables contain a field, uid.

In query

If you want to query the registered users who logged in on the same day, you need to use in to query. The hive sql is as follows:

Select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null

If the login table and regusers table are partitioned by day, and the field is dt, then query the registered users who logged in on January 1, 2013, and the hive sql is as follows:

Select login.uid from login day_login left outer join (select uid from regusers where dt='20130101') day_reguserson day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null

Not in query

If you want to query the regular users who logged in on the same day (it is assumed that users who are not registered on the same day are regular users), you need to use not in to query. The hive sql is as follows:

Select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null

If login table and regusers table are partitioned by day, and the field is dt, then query the regular users who logged in on January 1, 2013, and the hive sql is as follows:

Select login.uid from login day_login left outer join (select uid from regusers where dt='20130101') day_reguserson day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null

The answer to the question about how to realize and not in clause is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.