BigQuery中非IPv4地址转国家代码方法及SQL报错求助

报错原因 #

错误提示NET.IPV4_TO_INT64() encountered a non-IPv4 address. Expected 4 bytes but got 16说明你的context_ip字段中混合了IPv4和IPv6地址。NET.IPV4_TO_INT64仅能处理4字节的IPv4地址，碰到16字节的IPv6时就会触发报错。

解决方案 #

方案1：仅处理IPv4地址（过滤非IPv4记录）

如果只需要转换IPv4地址，直接在查询中过滤掉非IPv4的记录即可避免报错：

SELECT
  a.user_id,
  b.country_code
FROM (
  SELECT
    user_id,
    context_ip,
    NET.IPV4_TO_INT64(NET.IP_FROM_STRING(context_ip)) AS clientIpNum,
    TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(context_ip))/(256*256)) AS classB
  FROM `product_table`
  -- 过滤仅保留IPv4地址
  WHERE BYTE_LENGTH(NET.IP_FROM_STRING(context_ip)) = 4
) a
LEFT JOIN `fh-bigquery.geocode.geolite_city_bq_b2b` b
  ON a.classB = b.classB
  AND a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum

方案2：同时处理IPv4和IPv6地址

如果需要兼容两种IP类型，分别处理后关联对应的GeoLite地理表，再合并结果：

WITH ip_processed AS (
  SELECT
    user_id,
    context_ip,
    -- 转换IPv4为INT64
    CASE WHEN BYTE_LENGTH(NET.IP_FROM_STRING(context_ip)) = 4 THEN
      NET.IPV4_TO_INT64(NET.IP_FROM_STRING(context_ip))
    ELSE NULL END AS clientIpNum_v4,
    -- 转换IPv6为INT64
    CASE WHEN BYTE_LENGTH(NET.IP_FROM_STRING(context_ip)) = 16 THEN
      NET.IPV6_TO_INT64(NET.IP_FROM_STRING(context_ip))
    ELSE NULL END AS clientIpNum_v6,
    -- 计算IPv4的classB用于关联
    CASE WHEN BYTE_LENGTH(NET.IP_FROM_STRING(context_ip)) = 4 THEN
      TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(context_ip))/(256*256))
    ELSE NULL END AS classB
  FROM `product_table`
  -- 可选：过滤掉无效IP地址
  WHERE REGEXP_CONTAINS(context_ip, r'^(([0-9]{1,3}\.){3}[0-9]{1,3})|([0-9a-fA-F:.]+)$')
)
SELECT
  p.user_id,
  p.context_ip,
  -- 优先取IPv4的国家代码，无结果则取IPv6的
  COALESCE(v4.country_code, v6.country_code) AS country_code
FROM ip_processed p
-- 关联IPv4地理表
LEFT JOIN `fh-bigquery.geocode.geolite_city_bq_b2b` v4
  ON p.classB = v4.classB
  AND p.clientIpNum_v4 BETWEEN v4.startIpNum AND v4.endIpNum
-- 关联IPv6地理表
LEFT JOIN `fh-bigquery.geocode.geolite_city_ipv6_bq_b2b` v6
  ON p.clientIpNum_v6 BETWEEN v6.startIpNum AND v6.endIpNum

关键说明 #

• 用BYTE_LENGTH(NET.IP_FROM_STRING(context_ip))判断IP类型：4字节为IPv4，16字节为IPv6
• GeoLite库分别提供了IPv4和IPv6的映射表，需对应关联
• COALESCE用于合并两种IP的转换结果，保证每条记录能拿到可用的国家代码

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