CODEWITHSUNDEEP
sqlgoogle-bigqueryipv6
Sunil
Sunil

Reputation: 111

Converting IPv6 to integer in BigQuery standard SQL

I tired converting IP address to integer. It worked for IPv4 but while doing IPv6 i got error saying

NET.IPV4_TO_INT64() encountered a non-IPv4 address. Expected 4 bytes but got 16

How to convert IP address both IPv4 & IPv6?

Upvotes: 0

Views: 1371

Answers (1)

rmesteves
rmesteves

Reputation: 4085

If you need to convert a IPV6 to a decimal value, you can use a JavaScript UDF just like below. Please have in mind that the numeric value resulting from this operation needs to be represented as a STRING because its too big to fit into a BigQuery number type

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 "2001:0db8:0:0:8d3:0:0:0" ip, ipv6_to_number("2001:0db8:0:0:8d3:0:0:0") number

Result:

Row ip                         number   
1   2001:0db8:0:0:8d3:0:0:0    42540766411282592857539836924043198464

Upvotes: 2

Related Questions