title: Spark 2: How to filter multiple ranges from a dataframe
body: <p>I have a huge dataframe from which I need to filter rows corresponding to multiple ranges. I have tried following two approaches and both of them are extremely slow because the dataframe is too huge and is backed by hbase(using <a href="https://github.com/hortonworks-spark/shc" rel="nofollow noreferrer">shc</a> library).</p>
  
  <ol>
  <li><p>Chain multiple filter conditions.
  where ((key >= range1.start &amp;&amp; key &lt;=range1.end) || (key >= range2.start &amp;&amp; key &lt;=range2.end) || ... )</p></li>
  <li><p>Create a scanRanges dataframe from the ranges list, then create temp views for both data and scans</p>
  
  <p>allRecords.createOrReplaceTempView("data")
     scanRanges.createOrReplaceTempView("scans")</p>
  
  <p>Now I run this query </p>
  
  <p>sqlContext.sql("select * from data, scans where data.col0 between scans._1 and scans._2").show(20)</p></li>
  </ol>
  
  <p>I have even tried broadcasting scan table but that doesn't help. I need to avoid join completely and be able to filter the records to avoid scanning them on hbase. Is it possible?</p>
  
  <p>E.g.:
  Data table (truncated)</p>
  
  <pre><code>+------+-----+----+----+----+----+----+--------------+----+
  |  col0| col1|col2|col3|col4|col5|col6|          col7|col8|
  +------+-----+----+----+----+----+----+--------------+----+
  |row000| true| 0.0| 0.0|   0|   0|   0| String0 extra|   0|
  |row001|false| 1.0| 1.0|   1|   1|   1| String1 extra|   1|
  |row002| true| 2.0| 2.0|   2|   2|   2| String2 extra|   2|
  |row003|false| 3.0| 3.0|   3|   3|   3| String3 extra|   3|
  </code></pre>
  
  <p>Scan table</p>
  
  <pre><code>+------+------+
  |_1    |_2    |
  +------+------+
  |row020|row024|
  |row030|row034|
  |row040|row044|
  |row046|row049|
  +------+------+
  </code></pre>
  
  <p>Result (truncated)</p>
  
  <pre><code>+------+-----+----+----+----+----+----+--------------+----+
  |col0  |col1 |col2|col3|col4|col5|col6|col7          |col8|
  +------+-----+----+----+----+----+----+--------------+----+
  |row043|false|43.0|43.0|43  |43  |43  |String43 extra|43  |
  |row042|true |42.0|42.0|42  |42  |42  |String42 extra|42  |
  |row044|true |44.0|44.0|44  |44  |44  |String44 extra|44  |
  |row030|true |30.0|30.0|30  |30  |30  |String30 extra|30  |
  |row048|true |48.0|48.0|48  |48  |48  |String48 extra|48  |
  |row024|true |24.0|24.0|24  |24  |24  |String24 extra|24  |
  </code></pre>