How to join big dataframes in Spark SQL? (best practices, stability, performance)
<p>I'm getting the same error than <a href="https://stackoverflow.com/questions/28901123/org-apache-spark-shuffle-metadatafetchfailedexception-missing-an-output-locatio">Missing an output location for shuffle</a> when joining big dataframes in Spark SQL. The recommendation there is to set <strong>MEMORY_AND_DISK</strong> and/or <strong>spark.shuffle.memoryFraction 0</strong>. However, spark.shuffle.memoryFraction is deprecated in Spark >= 1.6.0 and setting MEMORY_AND_DISK shouldn't help if I'm not caching any RDD or Dataframe, right? Also I'm getting lots of other WARN logs and task retries that lead me to think that the job is not stable. </p>

<p>Therefore, my question is: </p>

<ul>
<li><strong>What are best practices to join huge dataframes in Spark SQL >= 1.6.0?</strong></li>
</ul>

<p>More specific questions are:</p>

<ul>
<li>How to tune <strong>number of executors</strong> and <strong>spark.sql.shuffle.partitions</strong> to achieve better stability/performance?</li>
<li>How to find the right balance between <strong>level of parallelism</strong> (num of executors/cores) and <strong>number of partitions</strong>? I've found that increasing the num of executors is not always the solution as it may generate <strong>I/O reading time out</strong> exceptions because of network traffic.</li>
<li>Is there any other relevant parameter to be tuned for this purpose?</li>
<li>My understanding is that joining data stored as <strong>ORC</strong> or <strong>Parquet</strong> offers better performance than text or Avro for join operations. Is there a significant difference between Parquet and ORC?</li>
<li>Is there an advantage of <strong>SQLContext</strong> vs <strong>HiveContext</strong> regarding stability/performance for join operations?</li>
<li>Is there a difference regarding performance/stability when the dataframes involved in the join are previously <strong>registerTempTable()</strong> or <strong>saveAsTable()</strong>?</li>
</ul>

<p>So far I'm using <a href="https://stackoverflow.com/a/37849488/2482894">this is answer</a> and <a href="https://www.safaribooksonline.com/library/view/high-performance-spark/9781491943199/ch04.html" rel="noreferrer">this chapter</a> as a starting point. And there are a few more stackoverflow pages related to this subject. Yet I haven't found a comprehensive answer to this popular issue.</p>

<p>Thanks in advance. </p>