74. Databricks | Pyspark | Interview Question: Sort-Merge Join (SMJ)

Azure Databricks Learning: Sort Merge Join
==========================================

What is sort-merge join in Spark?

Sort-merge join is one of the internal joining mechanism used by spark to join multiple dataframes. It is important to understand th internal working mechanism to understand the performance of spark program.

This is also one of the widely asked interview question

#SortMergeJoin, #SparkSortMerge, #SparkInternalJoin, #BroadcastJoin, #ShuffleHashJoin,#DatabricksSortMergeJoin ,#DatabricksRealtime, #SparkRealTime, #DatabricksInterviewQuestion, #DatabricksInterview, #SparkInterviewQuestion, #SparkInterview, #PysparkInterviewQuestion, #PysparkInterview, #BigdataInterviewQuestion, #BigdataInterviewQuestion, #BigDataInterview, #PysparkPerformanceTuning, #PysparkPerformanceOptimization, #PysparkPerformance, #PysparkOptimization, #PysparkTuning, #DatabricksTutorial, #AzureDatabricks, #Databricks, #Pyspark, #Spark, #AzureDatabricks, #AzureADF, #Databricks, #LearnPyspark, #LearnDataBRicks, #DataBricksTutorial, #azuredatabricks, #notebook, #Databricksforbeginners

[omprakashreddy]

You are here to make our lives simple. Thank you so much !!

[moviestime]

No one can explain better than this..Thanks raja for your efforts and time.

[vineethreddy.s]

Say if we have deptid 111 in emp table a million times and deptid 111 in dept table over 500k times.
During the shuffle spark would create 200 partitions. So deptid 111 of emptable may split across 20 partitions and deptid 111 of depttable may split across 10 partitions and if the sort and merge is performed on these partitions, then this would result in partial join. How does spark handle it internally?

[suresh.suthar.]

hats of to you sir g ur explanation is next level.

[JimRohn-uc]

Is this the same as the Sort-Merge-Bucket (SMB) join?

[venkatasai]

Good explanation Raja. Few questions 1)Does number of partitions determined by number of cores in the cluster or input split size for example s3 bucket 128MB 2)what happens if the partition size greater than the executor size . Does it spill to the disk ? Is that impacts the performance ?

[prabhatgupta]

Sir i have seen multiple join strategies are there . I could find in ur playlist.

[mohitupadhayay]

Is this why we use BROADCAST join? Because normal joins are expensive?

[olegcentury]

Hello!
1 executor unit is not 1 worker node unit? Maybe this worker node 1 is rack or little cluster? Or maybe this executors is actually containers (cores) on 1 executor (worker)?

[pavankumarveesam]

But in the third stage its not completed right lets say there is one more filter operation on the data frame it will still be in that stage only but if the data frame encounters a shuffle operation like join there will be another stage correct ?