title: Best way to join multiples small tables with a big table in Spark SQL
body: <p>I'm doing a join multiples tables using spark sql. One of the table is very big and the others are small (10-20 records). really I want to replace values in the biggest table using others tables that contain pairs of key-value. </p>
  
  <p>i.e.
  Bigtable: </p>
  
  <pre><code>| Col 1 | Col 2 | Col 3 | Col 4 | ....
  --------------------------------------
  | A1    | B1    | C1    | D1    | ....
  | A2    | B1    | C2    | D2    | ....
  | A1    | B1    | C3    | D2    | ....
  | A2    | B2    | C3    | D1    | ....
  | A1    | B2    | C2    | D1    | ....
  .
  .
  .
  .
  .
  </code></pre>
  
  <p>Table2: </p>
  
  <pre><code>| Col 1 | Col 2 
  ----------------
  | A1    | 1a    
  | A2    | 2a    
  </code></pre>
  
  <p>Table3: </p>
  
  <pre><code>| Col 1 | Col 2 
  ----------------
  | B1    | 1b    
  | B2    | 2b  
  </code></pre>
  
  <p>Table3: </p>
  
  <pre><code>| Col 1 | Col 2 
  ----------------
  | C1    | 1c    
  | C2    | 2c  
  | C3    | 3c
  </code></pre>
  
  <p>Table4: </p>
  
  <pre><code>| Col 1 | Col 2 
  ----------------
  | D1    | 1d    
  | D2    | 2d  
  </code></pre>
  
  <p>Expected table is </p>
  
  <pre><code>| Col 1 | Col 2 | Col 3 | Col 4 | ....
  --------------------------------------
  | 1a    | 1b    | 1c    | 1d    | ....
  | 2a    | 1b    | 2c    | 2d    | ....
  | 1a    | 1b    | 3c    | 2d    | ....
  | 2a    | 2b    | 3c    | 1d    | ....
  | 1a    | 2b    | 2c    | 1d    | ....
  .
  .
  .
  .
  .
  </code></pre>
  
  <p>My question is; <strong>which is best way to join the tables</strong>. (Think that there are 100 or more small tables)
   1) Collecting the small dataframes, to transforming it to maps, broadcasting the maps and transforming the big datataframe in one only step</p>
  
  <pre><code>bigdf.transform(ds.map(row =&gt; (small1.get(row.col1),.....)
  </code></pre>
  
  <p>2) Broadcasting the tables and making join using select method. </p>
  
  <pre><code>spark.sql("
         select * 
         from bigtable
         left join small1 using(id1) 
         left join small2 using(id2)")
  </code></pre>
  
  <p>3) Broadcasting the tables and Concatenate multiples joins </p>
  
  <pre><code>bigtable.join(broadcast(small1), bigtable('col1') ==small1('col1')).join...
  </code></pre>
  
  <p>Thanks in advance</p>