title: HiveQL - Efficient geoip discovery for big data
body: <p>I am using this hive query  to discover all accounts' country codes. The problem is that the join is exploding the data set to unmanageable sizes, and even the job fails to complete sometimes.</p>
  
  <p><strong>What is a fast and efficient way to accomplish this geoip lookup query? Use of Hive is not mandatory</strong></p>
  
  <pre><code>SELECT /*+ MAPJOIN(geoip) */
    data.account_id, geoip.country_code
  FROM data JOIN geoip
  WHERE data.ip_num BETWEEN geoip.begin_ip_num AND geoip.end_ip_num
  </code></pre>
  
  <p>Hive does not support <code>BETWEEN</code> inside <code>ON</code> clauses. Also, the <code>WHERE</code> filter is only applied after the join is complete. Any tips would be useful too. I am currently trying to solve this using a custom MapReduce job. Anyone have some ideas?</p>
  
  <h3>Info</h3>
  
  <ul>
  <li><code>geoip</code> table is around 1MB in size</li>
  <li>Hadoop cluster only has 12 map slots and 12 reduce slots</li>
  <li>I have tried to bucket the geoip table based on the <code>begin_ip_num</code> first octet (ex: the <code>123</code> of <code>123.0.0.0</code>), and then use the condition in the <code>JOIN</code> clause like <code>FROM data JOIN geoip ON (data.first_octet_bucket=geoip.first_octet_bucket)</code>. This technique does not work since some rows will be left out.</li>
  </ul>