WHERE clauses

A WHERE clause is used to filter the result set by using predicate operators and logical operators with the help of the following:

• List of predicate operators
• List of logical operators
• List of functions

Here is an example of using the WHERE clause:

select * from OnlineRetail where Description='WHITE METAL LANTERN' limit 5;

The following is the hive console showing the query execution:

The following query shows us how to use the group by clause:

select Description, count(*) from OnlineRetail group by Description limit 5;

The following is the hive console showing the query execution:

The following query is an example of using the group by clause and specify conditions to filter the results obtained with the help of the having clause:

select Description, count(*) as cnt from OnlineRetail group by Description having cnt> 100 limit 5;

The following is the hive console showing the query execution:

The following query is another example of using the group by clause, filtering the result with the having clause and sorting our result using the order by clause, here using DESC:

select Description, count(*) as cnt from OnlineRetail group by Description having cnt> 100 order by cnt DESC limit 5;

The following is the hive console showing the query execution: