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: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type;
  b=REiMMCcpldfnfpMNrSvdpGFFhvLUJYoEfXUA0X/9Aw5Hb4YRIGkispZTI/LLKSfJPYTXd2Dx6CjEFr389Ctz8twI+pDGYcPcgrlAoHHS/snDbz1MAKc0AVwjWrbV0auL5KK9tylWthJWKC65s5t2xPeKSqbJyk5QU330zpaWCPA=;
References: <361d713f.f14f.131bbb95b6f.Coremail.hadoop_wu@163.com>
Message-ID: <1313115917.52679.YahooMailNeo@web110503.mail.gq1.yahoo.com>
Date: Thu, 11 Aug 2011 19:25:17 -0700 (PDT)
From: Ayon Sinha <ayonsinha@yahoo.com>
Reply-To: Ayon Sinha <ayonsinha@yahoo.com>
Subject: Re: multiple tables join with only one hug table.
To: "user@hive.apache.org" <user@hive.apache.org>
In-Reply-To: <361d713f.f14f.131bbb95b6f.Coremail.hadoop_wu@163.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1098535769-1313115917=:52679"

--0-1098535769-1313115917=:52679
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

The Mapjoin hint syntax help optimize by loading the smaller tables specifi=
ed in the Mapjoin hint into memory. Then every small table is in memory of =
each mapper.=0A=A0=0A-Ayon=0ASee My Photos on Flickr=0AAlso check out my Bl=
og for answers to commonly asked questions.=0A=0A=0A=0A____________________=
____________=0AFrom: "Daniel,Wu" <hadoop_wu@163.com>=0ATo: hive <user@hive.=
apache.org>=0ASent: Thursday, August 11, 2011 7:01 PM=0ASubject: multiple t=
ables join with only one hug table.=0A=0A=0Aif the retailer fact table is s=
ale_fact with 10B rows, and join with 3 small tables: stores (10K), product=
s(10K), period (1K). What's the best join solution?=0A=0AIn oracle, it can =
first build hash for stores, and hash for products, and hash for stores. Th=
en probe using the fact table, if the row matched in stores, that row can g=
o up further to map with products by hashing check, if pass, then go up fur=
ther to try to match period. In this way, the sale_fact only needs to be sc=
anned once which save lots of disk IO.=A0 Is this doable in hive, if doable=
, what hint need to use?
--0-1098535769-1313115917=:52679
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"color:#000; background-color:#fff; font-family:ar=
ial, helvetica, sans-serif;font-size:10pt"><div><span>The Mapjoin hint synt=
ax help optimize by loading the smaller tables specified in the Mapjoin hin=
t into memory. Then every small table is in memory of each mapper.</span></=
div><div>&nbsp;</div><div>-Ayon<br><a rel=3D"nofollow" target=3D"_blank" hr=
ef=3D"http://www.flickr.com/photos/ayonsinha/">See My Photos on Flickr</a><=
br><a rel=3D"nofollow" target=3D"_blank" href=3D"http://dailyadvisor.blogsp=
ot.com">Also check out my Blog for answers to commonly asked questions.</a>=
<br><br></div><div style=3D"font-size: 10pt; font-family: arial, helvetica,=
 sans-serif; "><div style=3D"font-size: 12pt; font-family: 'times new roman=
', 'new york', times, serif; "><font size=3D"2" face=3D"Arial"><hr size=3D"=
1"><b><span style=3D"font-weight:bold;">From:</span></b> "Daniel,Wu" &lt;ha=
doop_wu@163.com&gt;<br><b><span style=3D"font-weight: bold;">To:</span></b>=
 hive
 &lt;user@hive.apache.org&gt;<br><b><span style=3D"font-weight: bold;">Sent=
:</span></b> Thursday, August 11, 2011 7:01 PM<br><b><span style=3D"font-we=
ight: bold;">Subject:</span></b> multiple tables join with only one hug tab=
le.<br></font><br><div id=3D"yiv807054678"><div style=3D"line-height: 1.7; =
color: rgb(0, 0, 0); font-size: 14px; font-family: arial; ">if the retailer=
 fact table is sale_fact with 10B rows, and join with 3 small tables: store=
s (10K), products(10K), period (1K). What's the best join solution?<br><br>=
In oracle, it can first build hash for stores, and hash for products, and h=
ash for stores. Then probe using the fact table, if the row matched in stor=
es, that row can go up further to map with products by hashing check, if pa=
ss, then go up further to try to match period. In this way, the sale_fact o=
nly needs to be scanned once which save lots of disk IO.&nbsp; Is this doab=
le in hive, if doable, what hint need to use?<br></div><br><br><span
 title=3D"neteasefooter"><span id=3D"yiv807054678netease_mail_footer"></spa=
n></span></div><br><br></div></div></div></body></html>
--0-1098535769-1313115917=:52679--