Book Image

Apache Hive Essentials

By : Dayong Du
Book Image

Apache Hive Essentials

By: Dayong Du

Overview of this book

Table of Contents (17 chapters)
Apache Hive Essentials
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

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     |
+--------+
3 rows selected (87.298 seconds)