Mistake in the group using the hive

Question

I use the following code and get the error below

select d.searchpack,d.context, d.day,d,txnid,d.config, c.sgtype from ds3resultstats d join context_header c on (d.context=c.contextid) where (d.day>='2012-11-15' and d.day<='2012-11-25' and c.sgtype='Tickler' and d.config like '%people%') GROUP BY d.context limit 10; FAILED: Error in semantic analysis: line 1:7 Expression Not In Group By Key d 

I assume that I am using the group incorrectly

Answer 1

when you use group by , you cannot select another additional field. You can only select the group key using the aggregate function.

See the hive group for more information.

Related questions .

Code example:

 select d.context,count(*) from ds3resultstats ... group by d.context 

or a group by multiplying fields.

 select d.context, d.field2, count(*) from ds3resultstats ... group by d.context, d.field2 

Answer 2

It is expected that all columns will be added using the group. Even I came across the same problem, but I managed to find work on these issues. you can use collect_set with the column name to get the result. For example select d.searchpack,collect_set(d.context) from sampletable group by d.searchpack;