Hive query not working for more than 3 partitions

Question

Below is my hive query

'select substr(ltrim(date_ts),0,10) date_ts,
 sum(if(col1 = 'type1', 1, 0)) as type_1,
 sum(if(col1 = 'type2', 1, 0)) as type_2,
 sum(if(col1 = 'type3', 1, 0)) as type_3
 from table1
 GROUP BY substr(ltrim(date_ts),0,10) 
 ORDER BY date_ts;'

My table1(external table) is partitioned as (year string,month string, day string)

Below are my partitions

'year='2010',month='01',day='01'
 year='2010',month='01',day='02'
 year='2010',month='01',day='03'
 year='2010',month='01',day='04''

The query is completely working fine if i run it on 3 or less partitions. And when i add the 4th partition, it just gets stuck at map=92%. Unable to figure out why. And it is working on combination of any 3 partitions. I don't know if anyone has faced this issued before.

I am able to get below output.

' date        | type1  | type2     |type3 |
------------------------------------------
 2011-10-01   |    1   |  0        |  0   |
 2011-10-02   |    1   |  0        |  0   |
 2011-10-03   |    0   |  1        |  1   |'

The moment i add a 4th partition for day four, map is getting stuck at some 90% and it stays on like that even after 1 to 2 hrs.

Expected output

' date        | type1  | type2     |type3 |
------------------------------------------
 2011-10-01   |    1   |  0        |  0   |
 2011-10-02   |    1   |  0        |  0   |
 2011-10-03   |    0   |  1        |  1   |
 2011-10-05   |    0   |  1        |  0   |'

Any suggestions please?

WestCoastProjects WestCoastProjects · Accepted Answer · 2014-05-15T01:19:03

What are the four partitions? Take a look if you end up with

__DEFAULT_PARTITION

which means that you had a null value in the partition column. That leads to skew which leads to .. slowness.

How to do this:

use <your_database>;
show partitions table table1;

Hive query not working for more than 3 partitions

1 Answers