Support Questions
Find answers, ask questions, and share your expertise

failed to detect group by on hive query

Highlighted

failed to detect group by on hive query

while executing a simple hive query with group by getting following error:

FAILED: ParseException line 1:135 Failed to recognize predicate 'group'. Failed rule: 'identifier' in table or column identifier

I restarted my entire cluster by did'nt get success. can somebody help me identifying and resolving the issue.

2 REPLIES 2
Highlighted

Re: failed to detect group by on hive query

Its a parser exception so its your syntax and restarting won't help you. That's a bit of a Windows users way out :-).

Can you post the query? You can also try to remove newlines in the query just to make sure I have seen beeline and hive shell having problems when running a query with newlines in weird places.

Highlighted

Re: failed to detect group by on hive query

Explorer

Hi I am facing the same Error while compiling statement: FAILED: ParseException line 1991:0 Failed to recognize predicate 'GROUP'. Failed rule: 'identifier' in subquery source

this is happening while doing an insert

SELECT user_no 
       ,user_name
       ,ref_date
       , 'xxx' BRAND
           ,nvl(SUM
            (
                CASE
                    WHEN product_name        = 'xx'
                    AND  indicator_name     = 'xx'
                    THEN indicator_value
                    ELSE NULL
                END
            ),0) AS SB_TO
           ,
           nvl(SUM
            (
                CASE
                    WHEN product_name        = 'xx'
                    AND  indicator_name     = 'xx'
                    THEN indicator_value
                    ELSE NULL
                END
            ),0) AS SB_GW
           ,
           nvl(SUM
            (
                CASE
                    WHEN product_name        = 'xx'
                    AND  indicator_name     = 'xx'
                    THEN indicator_value
                    ELSE NULL
                END
            ),0) AS xx       
FROM (SELECT pun.user_no , pun.user_name, c.calendar_date AS ref_date , p.xx, p.xx, p.xx, p.channel,  i.xx, sum(f.xx) AS indicator_value
        FROM tabl1 f
        join tab2 c
          on f.time_key         =  c.time_key
        join tab3 p
          on f.product_key        = p.product_key
        join tab4  i
          on f.indicator_key      = i.indicator_key
        join tab5 pun
          on f.xx       = pun.xx
         GROUP by pun.user_no , pun.user_name, c.calendar_date,  p.xx, p.xx, p.xx, p.xx,  i.xx)  
GROUP BY user_no, user_name, ref_date