hive command error Expression Not In Group By Key

Question

I have a HDFS table A written in the following format

user  product
U1       101
U1       102
U1       103
U2       101
U2       104
U3       102

...............

describe A;
>> user   string
   product int

Now if I want to aggregate users so that products by the same user are grouped together, how should I write the hive command?

select user, product from A group by user;

error: line 1:14 Expression Not In Group By Key product

Answer 1

You can use collect_set(col) function in hive for aggregating products by user name.

Use below command :

select user,collect_set(product) from A group by user;

You will get output like below :

U1      [102,103,101]
U2      [101,104]
U3      [102]

Please refer Hive Documentation for collect_set() for more information.