title: find the closest time between two tables in spark
body: <p>I am using pyspark and I have two dataframes like this:</p>
  
  <pre><code>user         time          bus
   A    2016/07/18 12:00:00   1
   B    2016/07/19 12:00:00   2
   C    2016/07/20 12:00:00   3
  
  bus          time          stop
   1    2016/07/18 11:59:40   sA
   1    2016/07/18 11:59:50   sB
   1    2016/07/18 12:00:05   sC
   2    2016/07/19 11:59:40   sB
   2    2016/07/19 12:00:10   sC
   3    2016/07/20 11:59:55   sD
   3    2016/07/20 12:00:10   sE
  </code></pre>
  
  <p>Now I want to know at which stop the user reports according to the bus number and the closest time in the second table. </p>
  
  <p>For example, in table 1, user A reports at 2016/07/18 12:00:00 and he is on bus No.1, and according to the second table, there are three records of bus No.1, but the closest time is 2016/07/18 12:00:05(the third record), so the user is in sC now.</p>
  
  <p>The desired output should be like this:</p>
  
  <pre><code>user         time          bus  stop
   A    2016/07/18 12:00:00   1    sC
   B    2016/07/19 12:00:00   2    sC
   C    2016/07/20 12:00:00   3    sD
  </code></pre>
  
  <p>I have transferred the time into timestamp so that the only problem is to find the closest timestamp where the bus number is eqaul.</p>
  
  <p>Because I'm not familiar with sql right now, I tried to use map function to find the closest time and its stop, which means I have to use sqlContext.sql in the map function, and spark dosen't seem to allow this:</p>
  
  <blockquote>
    <p>Exception: It appears that you are attempting to reference SparkContext from a broadcast variable, action, or transformation. SparkContext can only be used on the driver, not in code that it run on workers. For more information, see SPARK-5063.</p>
  </blockquote>
  
  <p>So how can I write a sql query to get the right output?</p>