Hive query stuck at 99%

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)

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

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.