How to transpose or convert columns to rows in Hive? - Big Data In Real World

How to transpose or convert columns to rows in Hive?

How to read and write XML files with Spark?
March 30, 2023
How to check size of a bucket in S3?
April 6, 2023
How to read and write XML files with Spark?
March 30, 2023
How to check size of a bucket in S3?
April 6, 2023

Let’s say we have a table name  employee_multiple_depts and each employee in the table is mapped to 3 departments – dept1, dept2 and dept3.

select * from employee_multiple_depts;
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--+
| employee_multiple_depts.ename  | employee_multiple_depts.dept1  | employee_multiple_depts.dept2  | employee_multiple_depts.dept3  |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--+
| Jerry                          | 10                             | 20                             | 30                             |
| Tom                            | 60                             | 70                             | 80                             |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--+

What we need is to transpose or convert each department into a row for each employee.

Solution

We will first create an array with dept1, dept2 and dept3. We then explode the array. explode is an User Defined Table generating function (UDTF) which converts a row into multiple rows.

Finally we use LATERAL VIEW. LATERAL VIEW joins resulting output exploded rows to the input rows from employee_multiple_depts providing the below output.

Check out this post if you need more information on LATERAL VIEW

SELECT ename, dept
FROM employee_multiple_depts 
LATERAL VIEW explode(array(dept1, dept2, dept3)) dept_exp as dept;

+--------+-------+--+
| ename  | dept  |
+--------+-------+--+
| Jerry  | 10    |
| Jerry  | 20    |
| Jerry  | 30    |
| Tom    | 60    |
| Tom    | 70    |
| Tom    | 80    |
+--------+-------+--+
6 rows selected (0.164 seconds)
Big Data In Real World
Big Data In Real World
We are a group of Big Data engineers who are passionate about Big Data and related Big Data technologies. We have designed, developed, deployed and maintained Big Data applications ranging from batch to real time streaming big data platforms. We have seen a wide range of real world big data problems, implemented some innovative and complex (or simple, depending on how you look at it) solutions.

1 Comment

  1. […] The Big Data in Real World team does a bit of data reshaping: […]

How to transpose or convert columns to rows in Hive?
This website uses cookies to improve your experience. By using this website you agree to our Data Protection Policy.

Hadoop In Real World is now Big Data In Real World!

X