O'Reilly logo

Apache Hive Essentials by Dayong Du

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Aggregation condition – HAVING

Since Hive 0.7.0, HAVING is added to support the conditional filtering of GROUP BY results. By using HAVING, we can avoid using a subquery after GROUP BY. The following is an example:

jdbc:hive2://> SELECT sex_age.age FROM employee 
. . . . . . .> GROUP BY sex_age.age HAVING count(*)<=1;
+--------------+
| sex_age.age  |
+--------------+
| 57           |
| 27           |
| 35           |
+--------------+
3 rows selected (74.376 seconds)

If we do not use HAVING, we can use a subquery for instance as follows:

jdbc:hive2://> SELECT a.age
. . . . . . .> FROM
. . . . . . .> (SELECT count(*) as cnt, sex_age.age 
. . . . . . .> FROM employee GROUP BY sex_age.age
. . . . . . .> ) a WHERE a.cnt<=1;
+--------+
| a.age  |
+--------+
| 57     |
| 27     |
| 35     |
+--------+ ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required