CODEWITHSUNDEEP
hadooppivothivehiveql
Mujahid Basha Syed
Mujahid Basha Syed

Reputation: 51

How to PIVOT data in Hive

How to achive PIVOT functionality in HiveQL. Following is SQL Query and please help me in achieving this in Hive:

 CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
 GO
 -- Inserting Data into Table
 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',2)
 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','SODA',6)
 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','MILK',1)
 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','BEER',12)
 INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','MILK',3)
 INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','BEER',24)
 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',3)
 GO

 -- Pivot Table ordered by PRODUCT
 SELECT PRODUCT, FRED, KATE FROM (
 SELECT CUST, PRODUCT, QTY
 FROM Product) up
 PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
 ORDER BY PRODUCT

--Desired Output: Cust Product QTY KATE VEG 2 KATE SODA 6 KATE MILK 1 KATE BEER 12 FRED MILK 3 FRED BEER 24 KATE VEG 3

Upvotes: 3

Views: 12805

Answers (2)

John Meagher
John Meagher

Reputation: 24708

Hive does not support pivoting in that way. If there are a small number of values to pivot over the results can be done with "if" or "case" statements in the query. This approach will not work for an unbounded or unknown set of products or customers.

There is a Jira issue requesting PIVOT support.

Upvotes: 3

AvkashChauhan
AvkashChauhan

Reputation: 20556

What you really need is to store your content in text file first as comma (or any other choice) as below:.

Kate,Veg,2 Kate,Soda,6 Kate, Milk,1 Kate,Beer,12 Fred,Milk,3 Fred,Beer,24 Kate,Veg,3

After the in the Hive shell create a table as below:

create table Products(Cust string, Product string, Qty int) row format \ 
delimited fields terminated by ',' lines terminated by '\n';

After that (assume you file is stored locally as /user/avkash/prodlist.txt you can load data to your table as below:

load data inpath '/user/avkash/prodlist.txt' overwrite into table Products;

Verify the table and records as below:

describe Products;
select * from Products;

After that you can run your other hive query on Hive shell to achieve your objective.

Upvotes: -2

Related Questions