HiveQL Select – Order By Query

1. Order By Query – Objective

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.

Hive Order By Query | Order By Cause

Hive Order By Query | Order By Cause

2. 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
Hadoop Quiz

Get the most demanding skills of IT Industry - Learn Hadoop

3. 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.
Let’s discussComparison between Hive Internal Tables vs External Tables

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

How to install Apache Hive on Ubuntu

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

ID NameSalaryDesignation Dept
1201Ross45000Technical managerTP
1202Rachel45000 Proofreader PR
1203Chandler40000Technical writerTP
1204Mike40000HR AdminHR
1205Phoebe30000Op AdminAdmin

However, using the above scenario the following query retrieves the employee details:
hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;
Let’s Learn to Develop Data Models in Hive in detail
Also, we get to see the following response to the successful execution of the query:
Table.2 Order By Clause

ID NameSalaryDesignation Dept
1205Phoebe30000Op AdminAdmin
1204Mike40000HR AdminHR
1202Rachel45000ProofreaderPr
1201Ross45000Technical managerTP
1203Chandler40000Technical writerTP

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
Let’s know about How to Install Java in Windows & Linux 

iv. Order By Query – Output

Table.3 Order By Clause – Output

ID NameSalaryDesignation Dept
1205Phoebe30000Op AdminAdmin
1204Mike40000HR AdminHR
1202Rachel45000ProofreaderPr
1201Ross45000Technical managerTP
1203Chandler40000Technical writerTP
1204Mike40000HR AdminHR
This was all about HiveQL Select – Hive Order By Query Tutorial. Hope you like our explanation of Hive Order By Clause.

4. 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.
Related Topic- Difference between Hive Partitioning vs Bucketing
For reference 

1 Response

  1. Vipul nanavati says:

    Order by clause call reducer or only map will be called in hive ?

Leave a Reply

Your email address will not be published. Required fields are marked *