CODEWITHSUNDEEP
sqlhadoophivemapreducehiveql
user2895589
user2895589

Reputation: 1030

Hive query stuck at 99%

I am inserting records using left joining in Hive.When I set limit 1 query works but for all records query get stuck at 99% reduce job.

Below query works

   Insert overwrite table tablename select a.id , b.name from a left join b on a.id = b.id limit 1;

But this does not

    Insert overwrite table tablename select table1.id , table2.name from table1 left join table2 on table1.id = table2.id;

I have increased number of reducers but still it doesn't work.

Upvotes: 9

Views: 21022

Answers (6)

RobertF
RobertF

Reputation: 904

Make sure you don't have rows with duplicate id values in one of your data tables!

I recently encountered the same issue with a left join's map-reduce process getting stuck on 99% in Hue.

After a little snooping I discovered the root of my problem: there were rows with duplicate member_id matching variables in one of my tables. Left joining all of the duplicate member_ids would have created a new table containing hundreds of millions of rows, consuming more than my allotted memory on our company's Hadoop server.

Upvotes: 2

Ignacio Alorre
Ignacio Alorre

Reputation: 7615

I faced the same problem with a left outer join similar to:

select bt.*, sm.newparam from
big_table bt
left outer join
small_table st
on bt.ident = sm.ident 
and bt.cate - sm.cate

I made an analysis based on the already given answers and I saw two of the given problems:

Left table was more than 100x bigger than the right table

select count(*) from big_table   -- returned 130M
select count(*) from small_table -- returned 1.3M

I also detected that one of the join variable was rather skewed in the right table:

select count(*), cate 
from small_table 
group by cate 

-- returned
-- A    70K
-- B   1.1M
-- C   120K

I tried most of the solutions given in other answers plus some extra parameters I found here Without success.:

set hive.optimize.skewjoin=true;
set hive.skewjoin.key=500000;
set hive.skewjoin.mapjoin.map.tasks=10000;
set hive.skewjoin.mapjoin.min.split=33554432;

Lastly I found out that the left table had a really high % of null values for the join columns: bt.ident and bt.cate

So I tried one last thing, which finally worked for me: to split the left table depending on bt.ident and bt.cate being null or not, to later make a union all with both branches:

select * from
(select bt.*, sm.newparam from
select * from big_table bt where ident is not null or cate is not null
left outer join
small_table st
on bt.ident = sm.ident 
and bt.cate - sm.cate
    union all
select *, null as newparam from big_table nbt where ident is null and cate is null) combined

Upvotes: 0

Syntax
Syntax

Reputation: 294

If your query is getting stuck at 99% check out following options -

  • Data skewness, if you have skewed data it might possible 1 reducer is doing all the work
  • Duplicates keys on both side - If you have many duplicate join keys on both side your output might explode and query might get stuck
  • One of your table is small try to use map join or if possible SMB join which is a huge performance gain over reduce side join
  • Go to resource manager log and see amount of data job is accessing and writing.

Upvotes: 3

Ravi Ranjan
Ravi Ranjan

Reputation: 62

use these configuration and try hive> set mapreduce.map.memory.mb=9000; hive> set mapreduce.map.java.opts=-Xmx7200m; hive> set mapreduce.reduce.memory.mb=9000; hive> set mapreduce.reduce.java.opts=-Xmx7200m

Upvotes: 0

BushMinusZero
BushMinusZero

Reputation: 1292

Here are a few Hive optimizations that might help the query optimizer and reduce overhead of data sent across the wire.

set hive.exec.parallel=true;
set mapred.compress.map.output=true;
set mapred.output.compress=true;
set hive.exec.compress.output=true;
set hive.exec.parallel=true;
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;

However, I think there's a greater chance that the underlying problem is key in the join. For a full description of skew and possible work arounds see this https://cwiki.apache.org/confluence/display/Hive/Skewed+Join+Optimization

You also mentioned that table1 is much smaller than table2. You might try a map-side join depending on your hardware constraints. (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins)

Upvotes: 5

Amar
Amar

Reputation: 3845

Hive automatically does some optimizations when it comes to joins and loads one side of the join to memory if it fits the requirements. However in some cases these jobs get stuck at 99% and never really finish.

I have faced this multiple times and the way I have avoided this by explicitly specifying some settings to hive. Try with the settings below and see if it works for you.

  1. hive.auto.convert.join=false
  2. mapred.compress.map.output=true
  3. hive.exec.parallel=true

Upvotes: 3

Related Questions