hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michal Krawczyk <michal.krawc...@u2i.com>
Subject How to use grouping__id in a query
Date Fri, 25 Sep 2015 13:14:41 GMT
Hi all,

During the migration from Hive 0.11 to 1.0 on Amazon EMR I run to an issue
with grouping__id function. I'd like to use it to filter out NULL values
that didn't come from grouping sets. Here's an example:

We have a simple table with some data:

hive> create table grouping_test (col1 string, col2 string);
hive> insert into grouping_test values (1, 2), (1, 3), (1, null), (null, 2);
hive> select * from grouping_test;
OK
1       2
1       3
1       NULL
NULL    2

hive> select col1, col2, GROUPING__ID, count(*)
from grouping_test
group by col1, col2
grouping sets ((), (col1))
having !(col1 IS NULL AND ((CAST(GROUPING__ID as int) & 1) > 0))

I expect the query above to filter out NULL col1 for the col1 grouping set,
it used to work on Hive 0.11. But on Hive 1.0 it doesn't filter any values
and still returns NULL col1:

NULL    NULL    0       4
NULL    NULL    1       1         <=== this row is expected to be removed
by the having clause
1       NULL    1       3

I tried also a few other conditions on grouping__id in having clause and
none of them seem to work correctly:

select col1, col2, GROUPING__ID, count(*)
from grouping_test
group by col1, col2
grouping sets ((), (col1))
having GROUPING__ID = '1'

This query doesn't return any data.


I also tried to embed it into a subquery, but still no luck. It finally
worked when I saved the output of the main query to a temp table and
filtered out the data using where clause, but this looks like an overkill.

So my question is: How to filter out values using grouping__id in Hive 1.0?

Thanks for your help,
Michal


-- 
Michal Krawczyk
Project Manager / Tech Lead
Union Square Internet Development
http://www.u2i.com/

Mime
View raw message