title: How do you create merge_asof functionality in PySpark?
body: <p>Table <code>A</code> has many columns with a date column, Table <code>B</code> has a datetime and a value. The data in both tables are generated sporadically with no regular interval. Table <code>A</code> is small, table <code>B</code> is massive.</p>
  <p>I need to join <code>B</code> to <code>A</code> under the condition that a given element <code>a</code> of <code>A.datetime</code> corresponds to</p>
  <pre><code>B[B['datetime'] &lt;= a]]['datetime'].max()
  </code></pre>
  <p>There are a couple ways to do this, but I would like the most efficient way.</p>
  <h2>Option 1</h2>
  <p>Broadcast the small dataset as a Pandas DataFrame. Set up a Spark UDF that creates a pandas DataFrame for each row merges with the large dataset using <code>merge_asof</code>.</p>
  <h2>Option 2</h2>
  <p>Use the broadcast join functionality of Spark SQL: set up a theta join on the following condition</p>
  <pre><code>B['datetime'] &lt;= A['datetime']
  </code></pre>
  <p>Then eliminate all the superfluous rows.</p>
  <p>Option B seems pretty terrible... but please let me know if the first way is efficient or if there is another way.</p>
  <p><strong>EDIT:</strong> Here is the sample input and expected output:</p>
  <pre><code>A =
  +---------+----------+
  | Column1 | Datetime |
  +---------+----------+
  |    A    |2019-02-03|
  |    B    |2019-03-14|
  +---------+----------+
  
  B =
  +---------+----------+
  |   Key   | Datetime |
  +---------+----------+
  |    0    |2019-01-01|
  |    1    |2019-01-15|
  |    2    |2019-02-01|
  |    3    |2019-02-15|
  |    4    |2019-03-01|
  |    5    |2019-03-15|
  +---------+----------+
  
  custom_join(A,B) =
  +---------+----------+
  | Column1 |   Key    |
  +---------+----------+
  |    A    |     2    |
  |    B    |     4    |
  +---------+----------+
  </code></pre>