You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by "Ratner, Alan S (IS)" <Al...@ngc.com> on 2012/11/01 19:27:18 UTC

Group By Concatenation

Sorry to ask what is probably a very naïve Hive question but here goes:

I have a table as follows:
Col1   Col2
K1       V1
K1       V1
K2       V1
K3       V1
K1       V2
K1       V3
K2       V2

Now I have managed to SELECT Col1,COUNT(DISTINCT Col2) FROM ... BY COL1; to obtain
K1       3
K2       2
K3       1

But what I want is a concatenated list of all the distinct Col2 values for each Col1 key.
K1       V1       V2       V3
K2       V1       V2
K3       V1

This is something absolutely trivial in MR but I cannot seem to find anything in Hive that will do this for me.  Do I have to write a UDF to accomplish this?


Alan


Re: Group By Concatenation

Posted by Edward Capriolo <ed...@gmail.com>.
Collect_set() is built into hive. If you want a version that does not
de-duplicate look here.
https://github.com/edwardcapriolo/hive-collect

Caution both of these functions can produce out of memory if the
results are later then a mapper can store in memory.

On Thu, Nov 1, 2012 at 2:27 PM, Ratner, Alan S (IS) <Al...@ngc.com> wrote:
> Sorry to ask what is probably a very naïve Hive question but here goes:
>
>
>
> I have a table as follows:
>
> Col1   Col2
>
> K1       V1
>
> K1       V1
>
> K2       V1
>
> K3       V1
>
> K1       V2
>
> K1       V3
>
> K2       V2
>
>
>
> Now I have managed to SELECT Col1,COUNT(DISTINCT Col2) FROM … BY COL1; to
> obtain
>
> K1       3
>
> K2       2
>
> K3       1
>
>
>
> But what I want is a concatenated list of all the distinct Col2 values for
> each Col1 key.
>
> K1       V1       V2       V3
>
> K2       V1       V2
>
> K3       V1
>
>
>
> This is something absolutely trivial in MR but I cannot seem to find
> anything in Hive that will do this for me.  Do I have to write a UDF to
> accomplish this?
>
>
>
>
>
> Alan
>
>