10. WRITE QUERIES TO SORT AND AGGREGATE THE DATA IN A TABLE USING HIVEQL

Procedure & Queries:
Step 1: Start Hadoop Services
start-dfs.sh
start-yarn.sh
Step 2: Start Hive
hive
Step 3: Create Database
CREATE DATABASE CompanyDB;
Step 4: Use Database
USE CompanyDB;
Step 5: Create Employee Table
CREATE TABLE Employee
(
EmpID INT,
Name STRING,
Department STRING,
Salary INT,
Age INT,
City STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
Step 6: Create Input File content (employee.txt)
101,Karthick,IT,65000,28,Coimbatore
102,Priya,HR,45000,30,Chennai
103,Rahul,Sales,55000,29,Madurai
104,Anitha,IT,72000,35,Salem
105,Vijay,Finance,60000,31,Trichy
106,Riya,HR,47000,27,Erode
Step 7: Load Data into Hive Table
LOAD DATA LOCAL INPATH '/home/hadoop/employee.txt'
INTO TABLE Employee;
Step 8: Display Table
SELECT * FROM Employee;
Sample Output (Display Table):
EmpID | Name | Department | Salary | Age | City
101 | Karthick | IT | 65000 | 28 | Coimbatore
102 | Priya | HR | 45000 | 30 | Chennai
103 | Rahul | Sales | 55000 | 29 | Madurai
104 | Anitha | IT | 72000 | 35 | Salem
105 | Vijay | Finance | 60000 | 31 | Trichy
106 | Riya | HR | 47000 | 27 | Erode
SORTING QUERIES:
1. Sort by Employee Name
Query:
SELECT * FROM Employee ORDER BY Name ASC;
Output:
Anitha
Karthick
Priya
Rahul
Riya
Vijay
2. Sort by Salary
Query:
SELECT * FROM Employee ORDER BY Salary DESC;
Output:
Anitha 72000
Karthick 65000
Vijay 60000
Rahul 55000
Riya 47000
Priya 45000
3. Sort by Department
Query:
SELECT * FROM Employee ORDER BY Department;
Output:
Finance
HR
HR
IT
IT
Sales
4. Sort by Age
Query:
SELECT * FROM Employee ORDER BY Age;
Output:
Riya 27
Karthick 28
Rahul 29
Priya 30
Vijay 31
Anitha 35
5. Sort by City
Query:
SELECT * FROM Employee ORDER BY City;
Output:
Chennai
Coimbatore
Erode
Madurai
Salem
Trichy
6. SORT BY
Query:
SELECT * FROM Employee SORT BY Salary DESC;
Output:
72000
65000
60000
55000
47000
45000
7. DISTRIBUTE BY
Query:
SELECT * FROM Employee DISTRIBUTE BY Department;
Output:
Finance
HR
HR
IT
IT
Sales
8. CLUSTER BY
Query:
SELECT * FROM Employee CLUSTER BY Department;
Output:
Finance
HR
HR
IT
IT
Sales
