CODEWITHSUNDEEP
google-bigqueryipv6
user2868835
user2868835

Reputation: 1600

BigQuery Convert IPV4 and IPV6 into a number to allow a match between start and end IP address ranges?

I'm building a BigQuery based data schema that will check whether a source IP address is within a declared IP address start/end range. To handle this, I will converting the source IP address and start/end range values into numbers.

I can see BigQuery NET functions for converting an IPV4 to a number, but I can't find the equivalent for IPV6

Any suggestions?

Upvotes: 1

Views: 1298

Answers (1)

Sandeep Mohanty
Sandeep Mohanty

Reputation: 1552

As per the documentation of BigQuery there is no equivalent of the NET function to convert an IPV6 address to a number. However in BigQuery you can write JavaScript user-defined functions in legacy SQL query syntax for your requirement.

You can refer to this thread for UDF using Javascript. I tried the code and it is converting an IPV6 address to a number.

Code :

CREATE TEMP FUNCTION ipv6_to_number(ip STRING)
RETURNS STRING
LANGUAGE js AS """
var parts = [];
   ip.split(":").forEach(function(it) {
       var bin = parseInt(it, 16).toString(2);
       while (bin.length < 16) {
           bin = "0" + bin;
       }
       parts.push(bin);
   })
   var bin = parts.join("");
   var dec = BigInt("0b"+ bin)
   return dec;

""";
Select "2020:0db9:0:0:8e3:0:0:0" ip, ipv6_to_number("2020:0db9:0:0:8e3:0:0:0") number

Output:

enter image description here

As per your requirement, you want a return type of Integer. I created a logic where the datatype is returned as BigInt.

code :

ip="2020:0db9:0:0:8e3:0:0:0";
const parts=[];
const arr=ip.split(":");
const data=arr.forEach(function(it){
   var bin=parseInt(it,16).toString(2);
   while(bin.length<16){
       bin="0"+bin;
       var bin1=parseInt(bin);

   }
   parts.push(bin1);
   });

var bin1=parts.join("");
var dec=BigInt("0b"+bin1);
console.log(dec);
console.log(typeof dec);

output:

4415686854424n
bigint

When you will use this function in UDF, Bigquery will throw an error :

enter image description here

This is because IPv6 uses a 128-bit address, which is 16 bytes. There is no numeric data type in BigQuery which is large enough to hold a numeric form of IPv6 address. BigQuery supports INT64 data type which is 8 bytes.

You will need to consider using BYTES to hold the IPv6 address or you can use the return type as String in your UDF.

For a compressed IPv6 address:

For a compressed IPv6 address like “2001:0db8::0001” , “2001:4ca0:6fff::” you can refer to the below steps:

Step 1: pre-process the IPv6 address (expand the address from a compressed one)

You can refer to the below code :

ipdeocde.js

ipval="2001:4ca0:6fff::";
var ip = require('ipaddr.js');
var addr = ip.parse(ipval);
addr.toString();
var ipadd =addr.toNormalizedString();
console.log(ipadd);
console.log(typeof ipadd);

output:

2001:4ca0:6fff:0:0:0:0:0
String

Step 2: Running the UDF

After expanding the Ipv6 address you can use the above UDF in BigQuery to convert the IPv6 address to a number.

If the address is already in expanded format you can directly use the UDF in BigQuery and if the address is in compressed format first you need to process it to convert the address to the expanded format and then run the UDF in BigQuery on the expanded IPv6 addresses.

Upvotes: 1

Related Questions