hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Furcy Pin <>
Subject Re: Efficient Equality Joins of Large Tables
Date Mon, 09 Jun 2014 17:34:50 GMT
Hi Mark,

I'm not sure if I understand what your trying to do correctly, do you know
the reference id on which you want to do the join beforehand?
Or is one of your tables small?
Or are they all big with a small intersection?

I you haven't yet, I would suggest you to have a look at MapJoin:
Mapjoins allow hive to perform a join on the map side by putting the small
table in cache.
(depending on your hive version, this might or might not be enabled by

Depending on your use case, you might have to pre-filter some tables, for
that you can use sub-queries like:

) T1

Unlike a standard sql that would simplifies the query, I believe this will
force Hive to perform the sub-select with a first mapreduce, and apply the
join in the second
(as a mapjoin if the result of the subquery is small enough).

Hope this helps,


2014-06-09 18:48 GMT+02:00 Mark Desnoyer <>:

> Hi,
> I was wondering if there was a way in Hive to trigger it to perform an
> efficient equality join on large tables? Specifically, I have two or more
> tables where the joined key is relatively rare in each table. A good
> example would be an AdClick scenario where you would have two tables, one
> for ad loads and one for ad clicks, where there is a reference id to
> connect a click to a specific ad load and that's the key to join on.
> In the MapReduce framework, this join could be done efficiently by using
> the reference id as the key from the map output and then the join would be
> done in the reducer since there are a small number of rows associated with
> each reference id. However, hive implements join by buffering all but the
> last tables in the reducer, and then streams the last table through. This
> works great if the first tables are relatively small, but if the tables are
> large and the associated rows are rare, it's very inefficient.
> So, can hive perform the join the first way I described? If so, any idea
> how I trigger it? Or do I have to write my own MR job?
> Cheers,
> Mark

View raw message