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 matshyeq@gmail.com designates
 209.85.223.180 as permitted sender)
MIME-Version: 1.0
In-Reply-To: <2B3C195A-7622-4309-879C-3079869E0346@icloud.com>
References: 
 <CAONr5=uXSEWYDwM9zOi2Ts0u6TRmXJc2kDoLmbC8ygUxXmjVKQ@mail.gmail.com>
 <2B3C195A-7622-4309-879C-3079869E0346@icloud.com>
From: matshyeq <matshyeq@gmail.com>
Date: Thu, 29 Jan 2015 14:13:12 +0000
Message-ID: 
 <CAONr5=saiL+BCd1quOEoK+4ZZq=0o3YOUxvZv1Z-5_Jey_vySQ@mail.gmail.com>
Subject: Re: Partitioned table and Bucket Map Join
To: user <user@hive.apache.org>
Content-Type: multipart/alternative; boundary=485b397dd1c1cb40c6050dcb1705

--485b397dd1c1cb40c6050dcb1705
Content-Type: text/plain; charset=UTF-8

My hunch is while partitioning is in fact very similar to bucketing
(actually superior as you have some control over what file data goes to)
the hive optimizer only applies bucket joins if your tables are bucketed so
your join condition
   t1.bucketed_column = t2.bucketed_column
triggers the bucketed map join
but
   t1.partitioned_column = t2.partitioned_column
doesn't.
I'm hoping someone with deeper Hive knowledge would be able to confirm this.

Thank you,
Kind Regards
~Maciek

On Thu, Jan 29, 2015 at 1:51 PM, murali parimi <
muralikrishna.parimi@icloud.com> wrote:

> I faced the same situation where two tables with 3 billion records on each
> side and partitioned, sorted on same key. Set the following parameters in
> the hive query assuming the join will happen in the map phase.
>
> set
> hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
> set hive.optimize.bucketmapjoin=true;
> set hive.optimize.bucketmapjoin.sortedmerge=true;
> set hive.enforce.sorting=true;
>
> I am using hive version 13 and the storage format is Orc. One of the table
> is small in size but I haven't checked whether irfan fit in the cache as we
> have huge memory. But the map sided join didn't happen. What could be the
> reason?
>
> Sent from my iPhone
>
> > On Jan 29, 2015, at 7:38 AM, matshyeq <matshyeq@gmail.com> wrote:
> >
> > I do have two tables partitioned on the same criteria.
> > Could I still take advantage of Bucket Map Join or better, Sort Merge
> Bucket Map Join?
> > How?
> >
> > ~Maciek
>

--485b397dd1c1cb40c6050dcb1705
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>My hunch is while partitioning is in fact very simila=
r to bucketing (actually superior as you have some control over what file d=
ata goes to) the hive optimizer only applies bucket joins if your tables ar=
e bucketed so your join condition</div><div>=C2=A0 =C2=A0t1.bucketed_column=
 =3D t2.bucketed_column</div><div>triggers the bucketed map join</div><div>=
but</div><div>=C2=A0 =C2=A0t1.partitioned_column =3D t2.partitioned_column<=
/div><div>doesn&#39;t.</div><div>I&#39;m hoping someone with deeper Hive kn=
owledge would be able to confirm this.</div></div><div class=3D"gmail_extra=
"><br clear=3D"all"><div><div class=3D"gmail_signature"><div dir=3D"ltr"><d=
iv><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div style=
=3D"font-family:arial"><font color=3D"#007481">Thank you,</font></div><div =
style=3D"font-family:arial"><font color=3D"#007481">Kind Regards=C2=A0</fon=
t></div><div style=3D"font-family:arial"><font color=3D"#007481">~Maciek</f=
ont></div></div></div></div></div></div></div></div></div></div>
<br><div class=3D"gmail_quote">On Thu, Jan 29, 2015 at 1:51 PM, murali pari=
mi <span dir=3D"ltr">&lt;<a href=3D"mailto:muralikrishna.parimi@icloud.com"=
 target=3D"_blank">muralikrishna.parimi@icloud.com</a>&gt;</span> wrote:<br=
><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1=
px #ccc solid;padding-left:1ex">I faced the same situation where two tables=
 with 3 billion records on each side and partitioned, sorted on same key. S=
et the following parameters in the hive query assuming the join will happen=
 in the map phase.<br>
<br>
set hive.input.format=3Dorg.apache.hadoop.hive.ql.io.BucketizedHiveInputFor=
mat;<br>
set hive.optimize.bucketmapjoin=3Dtrue;<br>
set hive.optimize.bucketmapjoin.sortedmerge=3Dtrue;<br>
set hive.enforce.sorting=3Dtrue;<br>
<br>
I am using hive version 13 and the storage format is Orc. One of the table =
is small in size but I haven&#39;t checked whether irfan fit in the cache a=
s we have huge memory. But the map sided join didn&#39;t happen. What could=
 be the reason?<br>
<br>
Sent from my iPhone<br>
<div class=3D"HOEnZb"><div class=3D"h5"><br>
&gt; On Jan 29, 2015, at 7:38 AM, matshyeq &lt;<a href=3D"mailto:matshyeq@g=
mail.com">matshyeq@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; I do have two tables partitioned on the same criteria.<br>
&gt; Could I still take advantage of Bucket Map Join or better, Sort Merge =
Bucket Map Join?<br>
&gt; How?<br>
&gt;<br>
&gt; ~Maciek<br>
</div></div></blockquote></div><br></div>

--485b397dd1c1cb40c6050dcb1705--