Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Date: Sun, 12 Apr 2015 06:19:12 +0000 (UTC)
From: "sanjiv singh (JIRA)" <jira@apache.org>
To: dev@hive.apache.org
Message-ID: <JIRA.12820365.1428819533000.65600.1428819552104@Atlassian.JIRA>
In-Reply-To: <JIRA.12820365.1428819533000@Atlassian.JIRA>
References: <JIRA.12820365.1428819533000@Atlassian.JIRA>
 <JIRA.12820365.1428819533455@arcas>
Subject: [jira] [Created] (HIVE-10310) Support GROUPING() in HIVE
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

sanjiv singh created HIVE-10310:
-----------------------------------

             Summary: Support GROUPING() in HIVE
                 Key: HIVE-10310
                 URL: https://issues.apache.org/jira/browse/HIVE-10310
             Project: Hive
          Issue Type: Wish
          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 verived 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 parser level.
2) GROUPING() aggregate function to hive(recommended)


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