title: How to improve performance of GeoIP query in BigQuery?
body: <p>I have loaded my application logs in BigQuery and I need to calculate country based on IP address from those logs.</p>
  
  <p>I have written a join query between my table and a GeoIP mapping table that I downloaded from <a href="http://dev.maxmind.com/geoip/legacy/geolite/" rel="noreferrer">MaxMind</a>.</p>
  
  <p>An ideal query would be <code>OUTER JOIN</code> with range filter, however <code>BQ</code> supports only <code>=</code> in join conditions. 
  So the query does an <code>INNER JOIN</code> and handles missing values in each side of the <code>JOIN</code>.</p>
  
  <p>I have amended my original query so it could run on the Wikipedia public data set.</p>
  
  <p>Can someone please help me make this run faster?</p>
  
  <pre><code>SELECT id, client_ip, client_ip_code, B.Country_Name as Country_Name
  
  FROM
      (SELECT id, contributor_ip as client_ip, INTEGER(PARSE_IP(contributor_ip)) AS client_ip_code, 1 AS One
      FROM [publicdata:samples.wikipedia] Limit 1000) AS A1
  
  JOIN 
      (SELECT From_IP_Code, To_IP_Code, Country_Name, 1 AS One
      FROM
  
          -- 3 IP sets: 1.valid ranges, 2.Gaps, 3. Gap at the end of the set
          -- all Ranges of valid IPs:
          (SELECT From_IP_Code, To_IP_Code, Country_Name FROM [QA_DATASET.GeoIP])
  
          -- Missing rages lower from From_IP 
          ,(SELECT
              PriorRangeEndIP + 1 From_IP_Code, 
              From_IP_Code - 1 AS To_IP_Code, 
              'NA' AS Country_Name
          FROM
  
              -- use of LAG function to find prior valid range
              (SELECT 
                  From_IP_Code, 
                  To_IP_Code, Country_Name, 
                  LAG(To_IP_Code, 1, INTEGER(0)) 
                  OVER(ORDER BY From_IP_Code asc) PriorRangeEndIP                 
              FROM [QA_DATASET.GeoIP]) A
  
              -- If gap from prior valid range is &gt; 1 than its a gap to fill
              WHERE From_IP_Code &gt; PriorRangeEndIP + 1)
  
          -- Missing rages higher tan Max To_IP
          ,(SELECT MAX(To_IP_Code) + 1 as From_IP_Code, INTEGER(4311810304) as To_IP_Code, 'NA' AS Country_Name
          FROM [QA_DATASET.GeoIP])
      ) AS B
  ON A1.ONE = B.ONE    -- fake join condition to overcome allowed use of only = in joins
  
  -- Join condition where valid IP exists on left
  WHERE
      A1.client_ip_code &gt;= B.From_IP_Code
      AND A1.client_ip_code &lt;= B.To_IP_Code
      OR (A1.client_ip_code IS NULL 
      AND B.From_IP_Code = 1)    -- where there is no valid IP on left contributor_ip
  </code></pre>