Queries

1. Create a non partioned table to store the data

create external table Unm_Dup_Parti_Trail (EmployeeID Int,FirstName String,Designation  String,Salary Int,Department String) row format delimited fields terminated by "," location '/user/sreeveni/HiveTrail';

2. Create Partitioned hive table

create  table Unm_Parti_Trail (EmployeeID Int,FirstName String,Designation  String,Salary Int) PARTITIONED BY (Department String) row format delimited fields terminated by ",";

hive> desc Unm_Parti_Trail;
OK
employeeid          	int                 	None
firstname           	string              	None
designation         	string              	None
salary              	int                 	None
department          	string              	None

# Partition Information
# col_name            	data_type           	comment

department          	string              	None
Time taken: 0.07 seconds, Fetched: 10 row(s)

3. Insert data into Partitioned table, by using select clause

INSERT INTO TABLE Unm_Parti_Trail PARTITION(department='A')
SELECT EmployeeID, FirstName,Designation,Salary FROM Unm_Dup_Parti_Trail WHERE department='A';

INSERT OVERWRITE TABLE Unm_Parti_Trail PARTITION (department='B')
SELECT EmployeeID, FirstName,Designation,Salary FROM Unm_Dup_Parti_Trail WHERE department='B';

INSERT OVERWRITE TABLE Unm_Parti_Trail PARTITION (department='C')
SELECT EmployeeID, FirstName,Designation,Salary FROM Unm_Dup_Parti_Trail WHERE department='C';

4. Updating salary of Steven with EmployeeID 19 to 50000.
INSERT OVERWRITE TABLE Unm_Parti_Trail PARTITION (Department = 'A') SELECT employeeid,firstname,designation, CASE WHEN employeeid=19 THEN 50000 ELSE salary END AS salary FROM Unm_Parti_Trail;

After updating the entries get repeated

hive> select *from Unm_Parti_Trail;
OK
1	Anne	Admin	50000	A
3	Janet	Sales	60000	A
6	Margaret	Tech	12000	A
9	Nancy	Admin	50000	A
10	Andrew	Manager	40000	A
13	Robert	Manager	40000	A
14	Laura	Engineer	45000	A
19	Steven	Engineer	50000	A
20	Michael	Ceo	100000	A
2	Gokul	Admin	50000	A
7	Nirmal	Tech	12000	A
8	jinju	Engineer	45000	A
11	Arun	Manager	40000	A
12	Harish	Sales	60000	A
15	Anju	Ceo	100000	A
16	Aarathi	Manager	40000	A
17	Parvathy	Engineer	45000	A
18	Gopika	Admin	50000	A
4	Hari	Admin	50000	A
5	Sanker	Admin	50000	A
2	Gokul	Admin	50000	B
7	Nirmal	Tech	12000	B
8	jinju	Engineer	45000	B
11	Arun	Manager	40000	B
12	Harish	Sales	60000	B
15	Anju	Ceo	100000	B
16	Aarathi	Manager	40000	B
17	Parvathy	Engineer	45000	B
18	Gopika	Admin	50000	B
4	Hari	Admin	50000	C
5	Sanker	Admin	50000	C
Time taken: 0.25 seconds, Fetched: 31 row(s)