title: Spark SQL performance - JOIN on value BETWEEN min and max
body: <p>I have two files in which I store:</p>
  
  <ol>
  <li>an IP range - country lookup </li>
  <li>a list of requests coming from different IPs</li>
  </ol>
  
  <p>The IPs are stored as integers (using inet_aton()). </p>
  
  <p>I tried using Spark SQL to join these pieces of data by loading both files into dataframes and registering them as temp tables.</p>
  
  <pre><code>GeoLocTable - ipstart, ipend, ...additional Geo location data
  Recordstable - INET_ATON, ...3 more fields
  </code></pre>
  
  <p>I tried using Spark SQL to join these pieces of data using a SQL statement like so -</p>
  
  <pre><code>"select a.*, b.* from Recordstable a left join GeoLocTable b on a.INET_ATON between b.ipstart and b.ipend"
  </code></pre>
  
  <p>There are about 850K records in RecordsTable and about 2.5M records in GeoLocTable. The join as it exists runs for about 2 hours with about 20 executors. </p>
  
  <p>I have tried caching and broadcasting the GeoLocTable but it does not really seem to help. I have bumped up spark.sql.autoBroadcastJoinThreshold=300000000 and  spark.sql.shuffle.partitions=600.</p>
  
  <p>Spark UI shows a BroadcastNestedLoopJoin being performed. Is this the best I should be expecting? I tried searching for conditions where this type of join would be performed but the documentation seems sparse. </p>
  
  <p>PS - I am using PySpark to work with Spark.</p>