Hive skew mitigation for left join

user_14357657 0
Viewed 297

I have a classic skew problem impacting performance of a left outer join (left table is "big", right table is "small"). The skewed keys are primarily NULL (by a long way) and secondarily "keyX".

I've tried a few different things:

  1. adding a join predicate "IS NOT NULL" on the skewed key doesn't seem to have any noticeable impact. and besides i have "keyX" to deal with
  2. i've had mixed results using hive.optimize.skewjoin
  3. the "key salting" technique referenced in a few articles i found works great (3x - 4x faster)! but i'm mindful of adding complexity to the query and it does require modifying each problem query, educating various other engineers etc
  4. i just noticed a very promising feature where you can specify skew in the metastore and have hive use that to generate a skew optimized execution plan. I'd love to test this before i fall back on option 3, but i can't seem to get NULL into the list of Skewed Values. It will accept this:

alter table T skewed by (skewed_key) on ('keyX');

but not this:

alter table T skewed by (skewed_key) on ('keyX',NULL);

Any ideas what's wrong with this syntax? Or does this feature not accept NULL Skew Values?

I'm open to other solutions to the skew problem in general also :)

hive-metastore skew hiveql hive join
1 Answers

My 5 cents: I do not intend to completely solve the skew problem in this post. Just providing some info about skewed NULL join key and how I solved it, hope it will be useful

Yes, "key salting" works, everything else seems not stable or does not work at all.

In case when the skewed key is NULL and all NULL records are being distributed to the same single reducer, you can solve it by transforming NULL key to random key values which are not in the allowed range so they will not be joined but distributed evenly to many reducers.

Something like ike this:

LEFT JOIN  asset_dim p ON NVL(f.asset_id, -9999999+RAND()*1000.0)%1000) = p.asset_id

This approach relies on the fact that you never have negative keys in the range – 9999999 … - 9998000

For not-NULL skew keys such approach is dangerous especially if you are running on EMR with spot nodes, which can be lost at the run time. Re-running containers will produce different RAND and the same records will be passed to different reducers and this will result in partial data duplication or loss, but it works fine for this particular case with NULL, which is not being joined.

Also for "KeyX" explicit splitting join + UNION ALL works fine, but the solution is a bit cumbersome:

(select * from t1 where key!='KeyX') left join t2 on t1.key=t2.key --everything else
UNION ALL
(select * from t1 where key=='KeyX') left join t2 on t1.key=t2.key --skewed
user_27003440
Related