hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chen Song <chen.song...@gmail.com>
Subject Re: map side join with group by
Date Thu, 13 Dec 2012 19:50:40 GMT
Thanks Nitin. This is all I want to clarify :)

Chen

On Thu, Dec 13, 2012 at 2:30 PM, Nitin Pawar <nitinpawar432@gmail.com>wrote:

> to improve the speed of the job they created map only joins so that all
> the records associated with a key fall to a map .. reducers slows it down.
> If the reducer has to do some more job then they launch another job.
>
> bear in mind, when we say map only join we are absolutely sure that speed
> will increase in case data in one of the tables is in the few hundred MB
> ranges. If this has to do with reduce in hand, the processing logic
> completely changes and it also slows down.
>
> Launching a new job for group by is a neat way to measure how much time
> you spent on just join and another on group by so you can easily see two
> different things.
>
> There is no way you can ask a mapjoin to launch a reducer as it is not
> supposed to do.
>
> If you have such case (may be if you think that it will improve
> performance), please feel free to raise a jira and get it reviewed. if its
> valid I think people will provide more ideas
>
>
> On Fri, Dec 14, 2012 at 12:42 AM, Chen Song <chen.song.82@gmail.com>wrote:
>
>> Nitin
>>
>> Yeah. My original question is that is there a way to force Hive (or
>> rather to say, is it possible) to execute map side join at mapper phase and
>> group by in reduce phase. So instead of launching a map only job (join) and
>> map reduce job (group by), doing it altogether in a single MR job. This is
>> obviously not what Hive does but I am wondering if it is a nice feature to
>> have.
>>
>> The point you made (different keys in join and group by) only matters
>> when it is the time in reduce phase, right? As map side join takes care of
>> join at mapper phase, it sounds to me natural that group by can be done in
>> the reduce phase in the same job. The only hassle that I can think of is
>> that map output have to be resorted (based on group by keys).
>>
>> Chen
>>
>> On Thu, Dec 13, 2012 at 1:42 PM, Nitin Pawar <nitinpawar432@gmail.com>wrote:
>>
>>> chen in mapside join .. there are no reducers .. its MAP ONLY job
>>>
>>>
>>> On Thu, Dec 13, 2012 at 11:54 PM, Chen Song <chen.song.82@gmail.com>wrote:
>>>
>>>> Understood that fact that it is impossible in the same MR job if both
>>>> join and group by are gonna happen in the reduce phase (because the join
>>>> keys and group by keys are different). But for map side join, the joins
>>>> would be complete by the end of the map phase, and outputs should be ready
>>>> to be distributed to reducers based on group by keys.
>>>>
>>>> Chen
>>>>
>>>>
>>>> On Thu, Dec 13, 2012 at 11:04 AM, Nitin Pawar <nitinpawar432@gmail.com>wrote:
>>>>
>>>>> Thats because for the first job the join keys are different and second
>>>>> job group by keys are different, you just cant assume join keys and group
>>>>> keys will be same so they are two different jobs
>>>>>
>>>>>
>>>>> On Thu, Dec 13, 2012 at 8:26 PM, Chen Song <chen.song.82@gmail.com>wrote:
>>>>>
>>>>>> Yeah, my abridged version of query might be a little broken but my
>>>>>> point is that when a query has a map join and group by, even in its
>>>>>> simplified incarnation, it will launch two jobs. I was just wondering
why
>>>>>> map join and group by cannot be accomplished in one MR job.
>>>>>>
>>>>>> Best,
>>>>>> Chen
>>>>>>
>>>>>>
>>>>>> On Thu, Dec 13, 2012 at 12:30 AM, Nitin Pawar <
>>>>>> nitinpawar432@gmail.com> wrote:
>>>>>>
>>>>>>> I think Chen wanted to know why this is two phased query if I
>>>>>>> understood it correctly
>>>>>>>
>>>>>>> When you run a mapside join .. it just performs the join query
..
>>>>>>> after that to execute the group by part it launches the second
job.
>>>>>>> I may be wrong but this is how I saw it whenever I executed group
by
>>>>>>> queries
>>>>>>>
>>>>>>>
>>>>>>> On Thu, Dec 13, 2012 at 7:11 AM, Mark Grover <
>>>>>>> grover.markgrover@gmail.com> wrote:
>>>>>>>
>>>>>>>> Hi Chen,
>>>>>>>> I think we would need some more information.
>>>>>>>>
>>>>>>>> The query is referring to a table called "d" in the MAPJOIN
hint but
>>>>>>>> there is not such table in the query. Moreover, Map joins
only make
>>>>>>>> sense when the right table is the one being "mapped" (in
other
>>>>>>>> words,
>>>>>>>> being kept in memory) in case of a Left Outer Join, similarly
if the
>>>>>>>> left table is the one being "mapped" in case of a Right Outer
Join.
>>>>>>>> Let me know if this is not clear, I'd be happy to offer a
better
>>>>>>>> explanation.
>>>>>>>>
>>>>>>>> In your query, the where clause on a column called "hour",
at this
>>>>>>>> point I am unsure if that's a column of table1 or table2.
If it's
>>>>>>>> column on table1, that predicate would get pushed up (if
you have
>>>>>>>> hive.optimize.ppd property set to true), so it could possibly
be
>>>>>>>> done
>>>>>>>> in 1 MR job (I am not sure if that's presently the case,
you will
>>>>>>>> have
>>>>>>>> to check the explain plan). If however, the where clause
is on a
>>>>>>>> column in the right table (table2 in your example), it can't
be
>>>>>>>> pushed
>>>>>>>> up since a column of the right table can have different values
>>>>>>>> before
>>>>>>>> and after the LEFT OUTER JOIN. Therefore, the where clause
would
>>>>>>>> need
>>>>>>>> to be applied in a separate MR job.
>>>>>>>>
>>>>>>>> This is just my understanding, the full proof answer would
lie in
>>>>>>>> checking out the explain plans and the Semantic Analyzer
code.
>>>>>>>>
>>>>>>>> And for completeness, there is a conditional task (starting
Hive
>>>>>>>> 0.7)
>>>>>>>> that will convert your joins automatically to map joins where
>>>>>>>> applicable. This can be enabled by enabling hive.auto.convert.join
>>>>>>>> property.
>>>>>>>>
>>>>>>>> Mark
>>>>>>>>
>>>>>>>> On Wed, Dec 12, 2012 at 3:32 PM, Chen Song <chen.song.82@gmail.com>
>>>>>>>> wrote:
>>>>>>>> > I have a silly question on how Hive interpretes a simple
query
>>>>>>>> with both map
>>>>>>>> > side join and group by.
>>>>>>>> >
>>>>>>>> > Below query will translate into two jobs, with the 1st
one as a
>>>>>>>> map only job
>>>>>>>> > doing the join and storing the output in a intermediary
location,
>>>>>>>> and the
>>>>>>>> > 2nd one as a map-reduce job taking the output of the
1st job as
>>>>>>>> input and
>>>>>>>> > doing the group by.
>>>>>>>> >
>>>>>>>> > SELECT
>>>>>>>> > /*+ MAPJOIN(d) */
>>>>>>>> > table.a, sum(table2.b)
>>>>>>>> > from table
>>>>>>>> > LEFT OUTER JOIN table2
>>>>>>>> > ON table.id = table2.id
>>>>>>>> > where hour = '2012-12-11 11'
>>>>>>>> > group by table.a
>>>>>>>> >
>>>>>>>> > Why can't this be done within a single map reduce job?
As what I
>>>>>>>> can see
>>>>>>>> > from the query plan is that all 2nd job mapper do is
taking the
>>>>>>>> 1st job's
>>>>>>>> > mapper output.
>>>>>>>> >
>>>>>>>> > --
>>>>>>>> > Chen Song
>>>>>>>> >
>>>>>>>> >
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Nitin Pawar
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Chen Song
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Nitin Pawar
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Chen Song
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Nitin Pawar
>>>
>>
>>
>>
>> --
>> Chen Song
>>
>>
>>
>
>
> --
> Nitin Pawar
>



-- 
Chen Song

Mime
View raw message