hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ferdinand Xu (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-10310) Support GROUPING() and GROUP_ID() in HIVE
Date Mon, 13 Apr 2015 06:00:24 GMT

    [ https://issues.apache.org/jira/browse/HIVE-10310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14491949#comment-14491949
] 

Ferdinand Xu commented on HIVE-10310:
-------------------------------------

Hi [~sanjiv singh], is this what you want? https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation,+Cube,+Grouping+and+Rollup

> Support GROUPING() and GROUP_ID() in HIVE
> -----------------------------------------
>
>                 Key: HIVE-10310
>                 URL: https://issues.apache.org/jira/browse/HIVE-10310
>             Project: Hive
>          Issue Type: New Feature
>          Components: Parser, SQL
>            Reporter: sanjiv singh
>            Priority: Minor
>
> I have lots of queries using GROUPING() function. failing on hive , just because GROUPING()
not supported in hive. See the Query below;  
> SELECT fact_1_id,
>        fact_2_id,
>        GROUPING(fact_1_id) AS f1g, 
>        GROUPING(fact_2_id) AS f2g
> FROM   dimension_tab
> GROUP BY CUBE (fact_1_id, fact_2_id)
> ORDER BY fact_1_id, fact_2_id;
> In order to run in HIVE all such queries, It  need to be transformed to HIVE syntax.
See below transformed query, compatible to hive. Equivalent have been derived using Case statement
.   
> SELECT fact_1_id,
>        fact_2_id,
>        (case when (GROUPING__ID & 1) = 0 then 1 else 0 end) as f1g,
>        (case when (GROUPING__ID & 2) = 0 then 1 else 0 end) as f2g
> FROM   dimension_tab
> GROUP BY fact_1_id, fact_2_id WITH CUBE
> ORDER BY fact_1_id, fact_2_id;
> It would be great if GROUPING() implemented in hive. I see two ways to do it
> 1) Handle it at parser level.
> 2) GROUPING() aggregate function to hive(recommended)



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message