Hive - joins

[#1]

Hi,

I have two table with 4 million and 200 million records each, both in ORC format.

Assuming the smaller table does not fit in memory, is SMB join the best way to go about it, if I have to join and match these tables?

If there are newer partitions every day adding to the bigger table, what optimizations can we do on this?

Thanks in advance.

[#2]

@kerra

Apart from SMB I would try to go with bucketing. Bucketing should be on both the tables and columns used for bucketing is chosen in such a way that it can be used during joins. Data Skew should also be considered. If there are data skews then hive tables has to created using skew. Also check if the size of file created in each partition directory atleast has more than block size. If not you may either have to change the partition technique or should use concatenate to merge small files in hive.

Check these links

http://henning.kropponline.de/2016/10/09/hive-join-strategies/

https://www.facebook.com/notes/facebook-engineering/join-optimization-in-apache-hive/470667928919/

https://www.msrcosmos.com/blog/how-to-optimize-joins-in-hive/

Above links are for generalized join optimization in hive. Hope it Helps!!