BigQuery中将IPv4、IPv6转换为数值以匹配IP起止范围的方法咨询

IPv6为128位地址，超出BigQuery原生INT64类型的64位最大存储范围，因此没有直接对应IPv4NET.IPV4_TO_INT64的单值转换函数，可选择以下两种落地方式实现需求：

方案1：直接使用原生字节类型做区间校验（无需自定义转换逻辑，性能最优） #

BigQuery的BYTES类型本身支持大小比较，刚好匹配IP区间校验的需求，无需手动做数值转换，同时天然兼容IPv4/IPv6两种地址格式：

-- 通用IP区间校验函数，自动适配IPv4、IPv6
CREATE TEMP FUNCTION is_ip_in_range(ip STRING, range_start STRING, range_end STRING) AS (
  NET.IP_TO_BYTES(ip) BETWEEN NET.IP_TO_BYTES(range_start) AND NET.IP_TO_BYTES(range_end)
);

-- 功能测试示例
SELECT 
  is_ip_in_range('240e:3a0:1000::1', '240e:3a0::', '240e:3a0:ffff:ffff:ffff:ffff:ffff:ffff') AS ipv6_test_result,
  is_ip_in_range('192.168.1.10', '192.168.1.0', '192.168.1.255') AS ipv4_test_result

该方案调用全原生函数，执行性能最高，适配绝大多数IP校验场景。

方案2：转换为高低位双INT64数值（匹配转数值存储的需求） #

如果业务要求必须将IP地址存储为数值格式，可把128位IPv6拆分为高64位、低64位两个INT64字段存储，转换和校验逻辑如下：

-- IPv6转高低位INT64结构体
CREATE TEMP FUNCTION ipv6_to_int64_pair(ip STRING) AS ((
  SELECT AS STRUCT
    CAST(CONCAT('0x', TO_HEX(SUBSTR(ip_bytes, 1, 8))) AS INT64) AS high,
    CAST(CONCAT('0x', TO_HEX(SUBSTR(ip_bytes, 9, 8))) AS INT64) AS low
  FROM (SELECT NET.IP_TO_BYTES(ip) AS ip_bytes)
  WHERE BYTE_LENGTH(ip_bytes) = 16
));

-- IPv6区间校验函数
CREATE TEMP FUNCTION is_ipv6_in_range(ip STRING, range_start STRING, range_end STRING) AS (
  (ipv6_to_int64_pair(ip).high > ipv6_to_int64_pair(range_start).high)
  OR (ipv6_to_int64_pair(ip).high = ipv6_to_int64_pair(range_start).high AND ipv6_to_int64_pair(ip).low >= ipv6_to_int64_pair(range_start).low)
  AND 
  (ipv6_to_int64_pair(ip).high < ipv6_to_int64_pair(range_end).high)
  OR (ipv6_to_int64_pair(ip).high = ipv6_to_int64_pair(range_end).high AND ipv6_to_int64_pair(ip).low <= ipv6_to_int64_pair(range_end).low)
);

高低位双字段的排序逻辑和IP本身的排序完全一致，可直接用于范围查询、排序等操作。

内容的提问来源于stack exchange，提问作者Davout