When dealing with skewed data and large datasets, requires a combination of techniques.
I name few. The one I am highlighting are extra suggestions on what you did not try yet.:
- Dynamic partition pruning: Use broadcast join only for non-skewed keys. For skewed keys, partition data to ensure better distribution.
- Salting with a variation: Using a secondary key in addition to the main_key to generate a more evenly distributed join.
- Explicit bucketing
- Filter irrelevant data before joining
- Broadcast joins for specific conditions
so on and so forth ...