hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <richin.j...@nokia.com>
Subject RE: Converting rows into dynamic colums in Hive
Date Wed, 08 Aug 2012 19:56:54 GMT
Thanks Ashish, that gives an idea.
But I am not sure about the outer select loop, I have to know all the values in Beta column
beforehand to do a max on each value.
Is there a better way?

Richin

From: ext Ashish Thusoo [mailto:athusoo@qubole.com]
Sent: Tuesday, August 07, 2012 5:05 PM
To: user@hive.apache.org
Subject: Re: Converting rows into dynamic colums in Hive

you should be able to do this in hive using a group by on alpha and then using a combination
of the max and if statement... something on the following lines

select alpha, max(abc), max(pqr), ...
(
  select alpha, if (beta == 'abc', Gamma, NULL) as abc, if (beta == 'pqr', Gamma, NUL) as
pqr, ....
  from table
)
group by alpha

something on those lines...

Ashish
On Tue, Aug 7, 2012 at 1:57 PM, <richin.jain@nokia.com<mailto:richin.jain@nokia.com>>
wrote:
Hi All,

One of my Query output looks like-

Alpha                    Beta                       Gamma
123                         xyz                         1.0
123                         abc                         0.5
123                         pqr                         1.3
123                         def                         2.1
456                         xyz                         0.1
456                         abc                         0.6
456                         pqr                         1.9
456                         def                         3.2
456                         lmn                        1.1
456                         sdf                          1.2

I want the output for the data visualization purpose to look like (basically taking rows from
one table and making it column in another table)

Alpha                    xyz         abc         pqr         def         lmn        sdf
123                         1.0          0.5          1.3          2.1
456                         0.1          0.6          1.9          3.2          1.1      
   1.2

Question - Can it be done in Hive? If not, any suggestions.

Thanks,
Richin




Mime
View raw message