如何在BigQuery中通过SQL获取客户IP地址的地理位置信息？

当然可以直接用BigQuery的内置函数实现IP地址到地理位置的解析，无需额外导入第三方数据。以下是具体实现方案：

基础实现（获取国家、城市等核心信息） #

假设你的表名为customer_data，存储IP地址的字段名为client_ip，可通过NET.SAFE_IP_FROM_STRING将IP字符串转换为BigQuery支持的IP数值类型，再结合GEOIP2_CITY函数提取完整地理信息：

SELECT
  client_ip,
  -- 提取国家信息
  geoip.city.country.name AS country_name,
  geoip.city.country.iso_code AS country_code,
  -- 提取地区/州信息（取第一个层级的行政区）
  geoip.city.subdivisions[OFFSET(0)].name AS region_name,
  geoip.city.subdivisions[OFFSET(0)].iso_code AS region_code,
  -- 提取城市信息
  geoip.city.city.name AS city_name,
  -- 提取经纬度
  geoip.city.location.latitude AS latitude,
  geoip.city.location.longitude AS longitude
FROM
  `your-project.your-dataset.customer_data`,
  -- 调用GEOIP2_CITY函数解析IP
  UNNEST([GEOIP2_CITY(NET.SAFE_IP_FROM_STRING(client_ip))]) AS geoip
WHERE
  client_ip IS NOT NULL
  -- 过滤格式无效的IP
  AND NET.SAFE_IP_FROM_STRING(client_ip) IS NOT NULL

简化场景（仅获取国家信息） #

如果只需要国家层级的地理数据，可使用更轻量的GEOIP_COUNTRY函数：

SELECT
  client_ip,
  geoip.country.name AS country_name,
  geoip.country.iso_code AS country_code
FROM
  `your-project.your-dataset.customer_data`,
  UNNEST([GEOIP_COUNTRY(NET.SAFE_IP_FROM_STRING(client_ip))]) AS geoip
WHERE
  client_ip IS NOT NULL
  AND NET.SAFE_IP_FROM_STRING(client_ip) IS NOT NULL

注意事项 #

• NET.SAFE_IP_FROM_STRING自动兼容IPv4和IPv6地址，无效格式的IP会返回NULL，可用于数据清洗。
• 地理数据基于MaxMind的GeoIP2数据库，BigQuery会定期更新该库，但无法自定义修改数据内容。
• 私有IP（如192.168.x.x、10.x.x.x）或未被收录的IP，解析结果会返回NULL，建议在查询中过滤或标记这类记录。

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