a GROUP BY that is not fully grouping

[Donald Matthews <dr...@gmail.com>]

After upgrading someone's Spark/Hive program from Spark 1.5.2 to Spark
1.6.2, I've run into a GROUP BY that does not work reliably in the newer
version: the GROUP BY results are not always fully aggregated. Instead, I
get lots of duplicate + triplicate sets of group values. Seems like a Hive
bug to me, but before I create a Jira ticket I thought I should check with
the list.

Input:  A single table with 24 fields that I want to group on, and a few
other fields that I want to aggregate.

Statement: similar to
SELECT a,b,c, ..., x, count(y) as yc, sum(z1) as z1s, sum(z2) as z2s
FROM inputTable
GROUP BY a,b,c, ..., x

Checking the data for one sample run, I see that the input table has about
1.1M rows, with 18157 unique combinations of those 24 grouped values.

Expected output: A table of 18157 rows.

Observed output: A table of 28006 rows. Looking just at unique combinations
of those grouped fields, I see that while 10125 rows are unique as
expected, there are 6215 duplicate rows and 1817 triplicate rows.

This is not 100% repeatable. That is, I'll see the issue repeatedly one
day, but the next day with the same input data the GROUP BY will work
correctly. Anyway, for what it's worth I have captured parquets of the
input + output at a time when this issue kicked in.

For now it seems that I have a workaround: if I presort the input table by
the grouped fields, the GROUP BY works correctly. But of course I shouldn't
have to do that.

I was unable to find any recent reports of this sort of thing in the Hive
Jira or on this list. Which is odd. Does this misbehaviour seem familiar to
anyone?

/drm

Re: a GROUP BY that is not fully grouping

[Stephen Sprague <sp...@gmail.com>]

ha!  kinda shows how the tech stack boundaries now are getting blurred,
eh?  well at least for us amateurs! :o

On Thu, Nov 3, 2016 at 5:00 AM, Donald Matthews <dr...@gmail.com> wrote:

> |    Spark calls its SQL part HiveContext, but it is not related to this
> list
>
> Oof, I didn't realize that. Thanks for letting me know.
>

Re: a GROUP BY that is not fully grouping

[Donald Matthews <dr...@gmail.com>]

|    Spark calls its SQL part HiveContext, but it is not related to this
list

Oof, I didn't realize that. Thanks for letting me know.

Re: a GROUP BY that is not fully grouping

[Gopal Vijayaraghavan <go...@apache.org>]

> Attached (assuming attachments actually work on this list) are three explains: 
..
> TungstenAggregate(key=[artid#5,artorsec#0,page#11,geo#12,

The explain plan shows you are not using Hive-on-Spark, but SparkSQL.

> If you'd like any other info, or if you'd like me to test with other versions of Spark or whatever, please let me know.

This bug needs to be reported on the Spark lists or JIRA (Spark calls its SQL part HiveContext, but it is not related to this list).

You can also check their existing bugs before reporting it - https://issues.apache.org/jira/browse/SPARK

Cheers,
Gopal

Re: a GROUP BY that is not fully grouping

[Donald Matthews <dr...@gmail.com>]

Thanks for your prompt response.

Attached (assuming attachments actually work on this list) are three
explains:
1. original (with incorrect GROUP BY result)
2. with hive.app.aggr=false (no change, still getting incorrect result),
3. back to the default hive.app.aggr value, with my workaround of
presorting the inputs (result is correctly grouped)

If you'd like any other info, or if you'd like me to test with other
versions of Spark or whatever, please let me know.

/drm

On Tue, Nov 1, 2016 at 4:51 PM, Gopal Vijayaraghavan <go...@apache.org>
wrote:

>
> >  I've run into a GROUP BY that does not work reliably in the newer
> version: the GROUP BY results are not always fully aggregated. Instead, I
> get lots of duplicate + triplicate sets of group values. Seems like a Hive
> bug to me
>
> That does sound like a bug, but this information is not enough to
> determine what's wrong.
>
> > This is not 100% repeatable.
>
> The output of "explain <query>" would be useful and along with it, I have
> put in a non-deterministic flush in Hive (for Tez) which might be messing
> with Spark.
>
> Can you run repros with - set hive.map.aggr=false;?
>
> Cheers,
> Gopal
>
>
>

Re: a GROUP BY that is not fully grouping

[Gopal Vijayaraghavan <go...@apache.org>]

>  I've run into a GROUP BY that does not work reliably in the newer version: the GROUP BY results are not always fully aggregated. Instead, I get lots of duplicate + triplicate sets of group values. Seems like a Hive bug to me

That does sound like a bug, but this information is not enough to determine what's wrong.

> This is not 100% repeatable. 

The output of "explain <query>" would be useful and along with it, I have put in a non-deterministic flush in Hive (for Tez) which might be messing with Spark.

Can you run repros with - set hive.map.aggr=false;?

Cheers,
Gopal