HiveQL Select – Order By Query

In last Apache Hive Tutorial, we study Hive Group By Query in detail, in this tutorial we study to retrieve the details based on one column and sort the result set by ascending or descending order we use the HiveQL Select – Order By query/ clause with “SELECT” statement in Hive query.

However, we need to know Hive Order By query syntax properly to use it. So, in this Hive Order By Clause article, we will learn the whole concept of HiveQL Select- Order By Query. But before HiveQL Select Order By query let’s learn brief introduction to Hive Query.

Further, we will learn example of Hive Order By query, JDBC Program with an output of Hive Order By query.

What is Hive Query

Basically, for the ETL purpose on top of Hadoop file system Hive offers SQL type querying language. However, to be more specific, to work with tables, databases, queries in Hive Hive Query language (HiveQL) offers SQL type environment.

In addition,  to perform different type data manipulations and querying it is possible to have a different type of Clauses associated with Hive. Also, provides better connectivity with different nodes outside the environment. Moreover, Hive offers JDBC connectivity as well.

Moreover, there are following features Hive queries offers. Such as:

• At first, data modeling. For example, a creation of databases, tables, etc.
• Also, ETL functionalities. For example, Extraction, Transformation, and Loading data into tables
• Moreover, it offers Joins to merge different data tables
• However, for ease of code, it offers user-specific custom scripts.
• Also, provides a faster-querying tool on top of Hadoop

HiveQL Select – Order By Query

Basically, HiveQL Select Order By Query syntax is as same as the syntax of ORDER BY Query in SQL language. Moreover, in Hive queries, we use Order by clause with Hive Select Statement. However, that guarantees total ordering of data.

Also, for grouping particular column values mentioned in Order by clause use columns on Hive tables. In addition, the order by clause the query will selects and display results in ascending or descending order the particular column values, for whatever the column name we are defining.

i. Order By Query Syntax

However, here is the syntax of the ORDER BY Clause:

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];

ii. Example of Order By Clause

Further, to understand well let’s suppose employee table as given below. Basically, it includes the fields named Id, Name, Salary, Designation, and Dept. Moreover, to retrieve the employee details in Order by Query using Department name, generate a query.
Tablea.1 Order By Query

However, using the above scenario the following query retrieves the employee details:
hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;

Also, we get to see the following response to the successful execution of the query:

Table.2 Order By Clause

iii. JDBC Program

Basically, to apply for Order By clause here is the JDBC program 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 HiveQLOrderBy
  {
     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 * FROM employee ORDER BY DEPT;");
            System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");   
            while (res.next())
              {
                      System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4) + " " + res.getString(5));
              }    
            con.close();
         }
  }

Moreover, by Using the following commands to compile and execute this program. Also, save the program in a file named HiveQLOrderBy.java.
$ javac HiveQLOrderBy.java
$ java HiveQLOrderBy

iv. Order By Query – Output

Table.3 Order By Clause – Output

Conclusion

As a result, we have seen what is HiveQL Select – Order by Query/ Order by Clause. Moreover, we will study example as well as syntax of Order By Query with JDBC Program. Still, if you have any query, feel free to ask in the comment section.