title: Spark Dataframe With for Loop: Optimization Technique
body: <p>I trying to implement blow logic.</p>
  
  <pre><code>    1. Taking some records from one table.
      2. based on resultant data I'm using one loop.
      3.then inside loop taking data from other tables in two different dataframe
      4. joining these two dataframes and loading data into 3rd table.
  
      var id_chck1 = s"select distinct id ,id1, id2  from table  WHERE type =  'N';
      val id_chck = hive.executeQuery(id_chck1)
      for (data &lt;- id_chck) {
  
     var id = data(0)
      var id1 = data(1)
      var id2 = data(2)
  
        val values_1 = "select distinct bill, bil_num, id_num,  bill_date,process_date from table l WHERE id2 = '222';
        val values_1_data = hive.executeQuery(values_1)
        for (row &lt;- values_1_data.collect) {
          val bill = row.mkString(",").split(",")(0)
          val bil_num = row.mkString(",").split(",")(1)
          val id_num= row.mkString(",").split(",")(2)
          val bill_date = row.mkString(",").split(",")(3)
  
          var df1 = s"select column name from tablename where id=222"
          val df1_data = hive.executeQuery(df1)
          var df2 = s"s"select column name from tablename2 where id=222""
          val df2_data = hive.executeQuery(df2)
  
        val df3="joining df1 and df2"
          df3.write.format("orc").mode("Append").save("hdfslocation")
        }
        var load1 = s"load data inpath 'hdfslocation' into table tablename"
        val load1_data = hive.executeUpdate(load1)
  </code></pre>
  
  <p>but this process is taking 6+ hrs is there any other way to doing the same thing so it can complete in short time.is there any other way to do the same thing..like rdd or setting some spark hive properties to improve performance.
  I have 5,00,000 records in test1 table.</p>