Aug
10
HiveQL – Select-Group By ”; Previous Next This chapter explains the details of GROUP BY clause in a SELECT statement. The GROUP BY clause is used to group all the records in a result set using a particular collection column. It is used to query a group of records. Syntax The syntax of GROUP BY clause is as follows: SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING having_condition] [ORDER BY col_list]] [LIMIT number]; Example Let us take an example of SELECT…GROUP BY clause. Assume employee table as given below, with Id, Name, Salary, Designation, and Dept fields. Generate a query to retrieve the number of employees in each department. +——+————–+————-+——————-+——–+ | ID | Name | Salary | Designation | Dept | +——+————–+————-+——————-+——–+ |1201 | Gopal | 45000 | Technical manager | TP | |1202 | Manisha | 45000 | Proofreader | PR | |1203 | Masthanvali | 40000 | Technical writer | TP | |1204 | Krian | 45000 | Proofreader | PR | |1205 | Kranthi | 30000 | Op Admin | Admin | +——+————–+————-+——————-+——–+ The following query retrieves the employee details using the above scenario. hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT; On successful execution of the query, you get to see the following response: +——+————–+ | Dept | Count(*) | +——+————–+ |Admin | 1 | |PR | 2 | |TP | 3 | +——+————–+ JDBC Program Given below is the JDBC program to apply the Group By clause for the given example. import java.sql.SQLException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.DriverManager; public class HiveQLGroupBy { private static String driverName = “org.apache.hadoop.hive.jdbc.HiveDriver”; public static void main(String[] args) throws SQLException { // Register driver and create driver instance Class.forName(driverName); // get connection Connection con = DriverManager. getConnection(“jdbc:hive://localhost:10000/userdb”, “”, “”); // create statement Statement stmt = con.createStatement(); // execute statement Resultset res = stmt.executeQuery(“SELECT Dept,count(*) ” + “FROM employee GROUP BY DEPT; ”); System.out.println(” Dept t count(*)”); while (res.next()) { System.out.println(res.getString(1) + ” ” + res.getInt(2)); } con.close(); } } Save the program in a file named HiveQLGroupBy.java. Use the following commands to compile and execute this program. $ javac HiveQLGroupBy.java $ java HiveQLGroupBy Output: Dept Count(*) Admin 1 PR 2 TP 3 Print Page Previous Next Advertisements ”;