google-bigquerygeoip

How to get From & To Ip Address from CIDR BigQuery


BigQuery provides updated geoip2 public dataset here [bigquery-publicdata -> geolite2 -> ipv4_city_blocks] which contains network column with IPv4 CIDR values.

How do I convert the CIDR values in the network column via BigQuery SQL (and not via a utility outside BigQuery) into start & end ip-address values so that I can find if an IP address is within a range or no? Would be helpful if you can provide the query to obtain the range ips for a CIDR value in the table.


Solution

  • Below is for BigQuery Standard SQL

    #standardSQL
    CREATE TEMP FUNCTION cidrToRange(CIDR STRING)
    RETURNS STRUCT<start_IP STRING, end_IP STRING>
    LANGUAGE js AS """
      var beg = CIDR.substr(CIDR,CIDR.indexOf('/'));
      var end = beg;
      var off = (1<<(32-parseInt(CIDR.substr(CIDR.indexOf('/')+1))))-1; 
      var sub = beg.split('.').map(function(a){return parseInt(a)});
      var buf = new ArrayBuffer(4); 
      var i32 = new Uint32Array(buf);
      i32[0]  = (sub[0]<<24) + (sub[1]<<16) + (sub[2]<<8) + (sub[3]) + off;
      var end = Array.apply([],new Uint8Array(buf)).reverse().join('.');
      return {start_IP: beg, end_IP: end};
    """; 
    SELECT network, IP_range.*
    FROM `bigquery-public-data.geolite2.ipv4_city_blocks`,
    UNNEST([cidrToRange(network)]) IP_range   
    

    It took about 60 sec to process all 3,037,858 rows with result like below

    enter image description here