title: How to set dynamic spark.sql.shuffle.partitions in pyspark?
body: <p>I want to set Spark (V 2.3) configuration <code>spark.sql.shuffle.partitions</code> dynamically and this configuration used in multiple spark applications.</p>
  
  <p><strong>code:</strong></p>
  
  <pre><code>Spark configuration
  ===================
  spark = SparkConf() \
   .setAppName("My App") \ 
   .set('spark.executor.memory', '7g') \
   .set('spark.num.executors', '15') \
   .set('spark.executor.cores', '4') \
   .set('spark.yarn.executor.memoryOverhead', '3098m') \
   .set('spark.sql.shuffle.partitions', '1500') \
   .set('fs.s3.multipart.uploads.enabled', 'true')
  
   empsql = 'Select * From Employee'  #Only 30 records and 40 columns
   df = spark.sql(empsql) ##Spark is configured
   df.coalesce(2).write.mode('overwrite').format("parquet").option("delimiter",'|').save(s3_path, header = True) #coalesce cannot be changed to repartition due to restrictions 
  **Error:** Spark Out of memeory issues
  **Resolved:** By changing the above spark configuration to .set('spark.sql.shuffle.partitions', '2')
  </code></pre>
  
  <p>For the above data frame, it is resolved by changing to .set('spark.sql.shuffle.partitions', '2') and the issue is, it doesn't work for spark applications having over one or more million records and it requires .set('spark.sql.shuffle.partitions', '1500').</p>
  
  <p>How to resolve this issue and how to make it dynamic?</p>