What is an optimized way of joining large tables in Spark SQL
<p>I have a need of joining tables using Spark SQL or Dataframe API. Need to know what would be optimized way of achieving it.</p>

<p>Scenario is:</p>

<ol>
<li>All data is present in Hive in ORC format (Base Dataframe and Reference files).</li>
<li>I need to join one Base file (Dataframe) read from Hive with 11-13 other reference file to create a big in-memory structure (400 columns) (around 1 TB in size)</li>
</ol>

<p>What can be best approach to achieve this? Please share your experience if some one has encounter similar problem.</p>