How can I convert an IP address to an integer in BigQuery?

Question

My primary goal is to filter out a range of IP addresses in IPv4 format by converting them to integers. So, for example, I'd like to filter an IP range from 192.168.1.1 to 192.168.1.100, but I first need to convert them to integers: 3232235777 to 3232235876.

Is there a way to do this in BigQuery (standard SQL)? I've looked at the BigQuery documentation but my combination of the NET.IP_FROM_STRING() and NET.IPV4_TO_INT64() functions keeps throwing this error: No matching signature for function NET.IPV4_TO_INT64 for argument types: STRING.

SELECT addr_str, ip_from_string, FORMAT("0x%X",
  NET.IPV4_TO_INT64(ip_from_string)) AS ipv4_to_int64
FROM
(
SELECT addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST(['192.168.1.1','192.168.1.100']) AS addr_str
)

score 2 · Answer 1 · answered Mar 09 '17 at 18:12

2

NET.IPV4_TO_INT64 function expects binary (BYTES) as an input, but you "accidentally" cast ip_from_string to string by applying FORMAT function.

Just remove it!

answered Mar 09 '17 at 18:12

Mikhail Berlyant

117,385
6
77
139

Thanks as always, Mikhail! – dnaeye Mar 09 '17 at 18:59

score 1 · Accepted Answer · answered Mar 09 '17 at 18:06

1

Why are you calling FORMAT on the result of NET.IP_FROM_STRING? NET.IP_FROM_STRING returns the IP address as bytes, and using that you can convert to an INT64 using NET.IPV4_TO_INT64, for example.

#standardSQL
SELECT
  addr_str,
  ip_from_string,
  NET.IPV4_TO_INT64(ip_from_string) AS ipv4_to_int64
FROM (
  SELECT
    addr_str,
    NET.IP_FROM_STRING(addr_str) AS ip_from_string
  FROM UNNEST(['192.168.1.1','192.168.1.100']) AS addr_str
);

answered Mar 09 '17 at 18:06

Elliott Brossard

26,036
2
41
74

Thanks, Elliot. I called mindlessly called the `FORMAT` function simply because that's what was in the Google documentation haha. – dnaeye Mar 09 '17 at 18:59

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

This answer covers the math of converting an IPv4 to an integer. Are there mathematical operators available to you in BigQuery?

edited May 23 '17 at 11:53

Community

1
1

answered Mar 09 '17 at 17:57

Elijah

12,636
9
54
88

How can I convert an IP address to an integer in BigQuery?

3 Answers3