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/
