Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of dolik.rce@gmail.com
 designates 209.85.160.176 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CAN7ydrNr0j+18Msf1amaAUHPvmMgT60p4nfq6jTq48H5CUeiFA@mail.gmail.com>
References: 
 <CAJ2-wbpteHfNhPUjHOb5Ly9aY9xmxLzgM1BU4X3veJmSbHk_hQ@mail.gmail.com>
	<CAJ2-wbrtZ6Cn3GPhgXBnWPW6ysWNArUhyksPRuqnKwVEkj9Jog@mail.gmail.com>
	<CAN7ydrOM5aLde_k62fui65-=eHpwPnLTQ6Ofuzdf6B4CUfgHhg@mail.gmail.com>
	<CAJ2-wbr6r=XPDsC4SvLmFwtqFxCjuAEnjhmsoDhdwhxSLpZrkA@mail.gmail.com>
	<CAN7ydrNr0j+18Msf1amaAUHPvmMgT60p4nfq6jTq48H5CUeiFA@mail.gmail.com>
Date: Tue, 5 Jun 2012 14:47:48 +0200
Message-ID: 
 <CAN7ydrO77g+emtGpLfWJ0y-jMz6QPXj91WcaSUQEdZ_0s9EW1A@mail.gmail.com>
Subject: Re: Multi-GroupBy-Insert optimization
From: =?UTF-8?B?SmFuIERvbGluw6Fy?= <dolik.rce@gmail.com>
To: user@hive.apache.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi Shan,

If you happen to have a lot of repeated data (in the most general
grouping), you might get some speedup by little pre-aggregation. The
following code should produce the same results as the example in your
first post:

>From (
  SELECT a, b , c, count(*) AS cnt
  FROM X
  group by a,b,c
 ) t
INSERT OVERWRITE LOCAL DIRECTORY 'output/y1'
SELECT a, b , c, cnt
INSERT OVERWRITE LOCAL DIRECTORY 'output/y2'
SELECT  a ,  SUM(cnt)
group by a
INSERT OVERWRITE LOCAL DIRECTORY 'output/y3'
SELECT b,  SUM(cnt)
group by b
...

The trick is that there there will be one more job that will first
reduce the number of records that are used in the following jobs. They
will only have to read one line for each distinct triplet a,b,c. Note
that this will only help if the number of distinct combinations is
relatively low compared to the total amount of data. In other cases it
might make no difference or even make the calculation longer.

Hope that helps... I can't think about anything else that could help you.

Jan

On 6/5/12, Jan Dolin=C3=A1r <dolik.rce@gmail.com> wrote:
> On 6/4/12, shan s <mysub987@gmail.com> wrote:
>> Thanks for the explanation Jan.
>> If I understand correctly, the input will be read one single time and
>> will
>> be preprocessed in some form,  and this intermediate data is used for
>> subsequent group-by..
>> Not sure if my scenario will help this single step, since group-by varie=
s
>> across vast entities.
>
> Yes, that is that is correct. The very simplest use case is when you
> only scan a part of table. But if you are interested in all the data,
> it is not going to help you much.
>
>> If I were to implement group-by,manually, generally  we could club them
>> together in single program. Can I do better with hive, with some
>> hints/optimizations?
>> Or  is there a possibility that Pig might perform better in this case.(
>> Assuming Pig would probably handle this in a single job?)
>
> In some cases it might be able to outsmart the hive optimizer and
> write the mapreduce job directly in java in such way that it might
> perform better. In most cases though, it is probably not worth the
> trouble. You might easily end up in situation where buying more
> machines is cheaper than developing the low level solutions that might
> or might not be slightly faster... I'm not familiar with Pig or any
> other tools that might be of use in your situation.
>
> Jan
>