Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive, mail # user - how to combine some rows into 1 row in hive

zuohua zhang 2013-06-22, 22:36
Edward Capriolo 2013-06-22, 23:48
Copy link to this message
Re: how to combine some rows into 1 row in hive
Michael Malak 2013-06-23, 00:05
Or, the single-language (HiveQL) alternative might be (i.e. I haven't tested it):
select f1,
       if(max(if(f3='P',f4,null)) is null,0,max(if(f3='P',f4,null))) pf4,       if(max(if(f3='P',f5,null)) is null,0,max(if(f3='P',f5,null))) pf5,
       if(max(if(f3='N',f4,null)) is null,0,max(if(f3='N',f4,null))) nf4,
       if(max(if(f3='N',f5,null)) is null,0,max(if(f3='N',f5,null))) nf5
from   mytable
group by f1, f2;

 From: Edward Capriolo <[EMAIL PROTECTED]>
Sent: Saturday, June 22, 2013 7:48 PM
Subject: Re: how to combine some rows into 1 row in hive
Using hives streaming feature is a nice option for this as it is a fairly natural way to work with entire rows and return multiple columns

select transform a,b,c,d  using /bin/pipeprogram as a, b ,c ,d

You an also write a UDTF user defined Table function as well because this can return more then one column.

On Sat, Jun 22, 2013 at 6:36 PM, zuohua zhang <[EMAIL PROTECTED]> wrote:

I have the following table:
>f1 f2 f3 f4 f5
>a1 a2 P x1 x2
>a1 a2 N x3 x4
>a1 a3 N x5 x6
>a4 a6 P x7 x8
>i want to convert to below:
>f1 f2 pf4 pf5 nf4 nf5
>a1 a2 x1 x2 x3 x4
>a1 a3 0 0 x5 x6
>a4 a6 x7 x8 0 0
>basically, when f3="P", I want f4 f5 to be moved to pf4 pf5
>when f3="N", i want f4 f5 to be moved to nf4 nf5
>when there is no "P" or "N" rows for the record, I want to fill in zeros in the corresponding fields.
>how to do it?
zuohua zhang 2013-06-23, 00:38
Stephen Sprague 2013-06-23, 19:20
Michael Malak 2013-06-23, 21:40
zuohua zhang 2013-06-22, 23:51