Reply
Explorer
Posts: 9
Registered: ‎11-07-2016

Transpose columns to rows

Hey all,

Our production env use Impala 2.2.0, CDH 5.4.3.

 

Does impala has a function to transpose columns to rows?

 

Currently , in order to do so, I need to perform seperate queries, which filter the specific column, and union them.

Because the source table is huge, this solution is not good at all.

Any ideas?

 

Here's a simple example:

 

-- Source table
SELECT * FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp

 

-- Desired output

SELECT a AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp
UNION all
SELECT b AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp
UNION all
SELECT c AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp
UNION all
SELECT d AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp

 

 

Thanks!

Dror

Posts: 177
Topics: 8
Kudos: 28
Solutions: 19
Registered: ‎07-16-2015

Re: Transpose columns to rows

I don't think Impala has such a feature (but I could be wrong).

 

If I were you, I would try to answer these questions :

- "Why do I need this kind of ouput ?"

- "What do I use it for ?"

- "Can't I acheive my goal with an other output ?"

 

Maybe you will find an other approach more adapted.

 

By the way, I guess something like this would be better (but it will not make a huge difference) :

SELECT a AS col FROM tmp
UNION ALL SELECT b AS col FROM tmp
UNION ALL SELECT c AS col FROM tmp
UNION ALL SELECT d AS col FROM tmp

 

Posts: 519
Topics: 14
Kudos: 92
Solutions: 45
Registered: ‎09-02-2016

Re: Transpose columns to rows

@dsss

 

You can find all the Impala build in functions in the below link. I don't find any option for PIVOT (pls double chk)

https://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_functions.html

 

There are so many ways to transpose row to column/column to row using normal SQL. I would suggest you to follow that and create a UDF (User Defined Function)

https://www.cloudera.com/documentation/enterprise/5-8-x/topics/impala_udf.html

 

Below is the JIRA ticket created with apache to include PIVOT option in Hive, you can see the status & comments. Also some links provided in the comment section to manually transpose the column to row/row to column. 

 

https://issues.apache.org/jira/browse/HIVE-3776

 

Thanks

Kumar

 

Explorer
Posts: 9
Registered: ‎11-07-2016

Re: Transpose columns to rows

Contributor
Posts: 25
Registered: ‎06-13-2017

Re: Transpose columns to rows

you can use DECODE or CASE.

New Contributor
Posts: 5
Registered: ‎07-16-2018

Re: Transpose columns to rows

Not helpful yet, but promising... PIVOT keyword is reserved for future use!
https://www.cloudera.com/documentation/enterprise/6/6.2/topics/impala_reserved_words.html