代码之家  ›  专栏  ›  技术社区  ›  Flair JFMoya

如何在BigQuery标准SQL中将IP地址转换为地理位置?

  •  3
  • Flair JFMoya  · 技术社区  · 9 年前

    所以我读过 https://cloudplatform.googleblog.com/2014/03/geoip-geolocation-with-google-bigquery.html

    #standardSQL 这样做的方式。到目前为止,我在转换PARSE\u IP和NTH()时遇到了很多挑战,因为迁移文档中建议的更改有局限性。

    从 PARSE_IP(contributor_ip) 到 NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(contributor_ip))

    从 NTH(1, latitude) lat latitude[SAFE_ORDINAL(1)]

    我可能还没有遇到更多的迁移问题。有人知道如何在BigQuery标准SQL中将IP地址转换为地理位置吗?

    P、 我将如何从地理位置确定时区?

    #legacySQL
    SELECT
      COUNT(*) c,
      city,
      countryLabel,
      NTH(1, latitude) lat,
      NTH(1, longitude) lng
    FROM (
      SELECT
        INTEGER(PARSE_IP(contributor_ip)) AS clientIpNum,
        INTEGER(PARSE_IP(contributor_ip)/(256*256)) AS classB
      FROM
        [publicdata:samples.wikipedia]
      WHERE
        contributor_ip IS NOT NULL ) AS a
    JOIN EACH
      [fh-bigquery:geocode.geolite_city_bq_b2b] AS b
    ON
      a.classB = b.classB
    WHERE
      a.clientIpNum BETWEEN b.startIpNum
      AND b.endIpNum
      AND city != ''
    GROUP BY
      city,
      countryLabel
    ORDER BY
      1 DESC
    

    和

    SELECT
      COUNT(*) c,
      city,
      countryLabel,
      ANY_VALUE(latitude) lat,
      ANY_VALUE(longitude) lng
    FROM (
      SELECT
        CASE
          WHEN BYTE_LENGTH(contributor_ip) < 16 THEN SAFE_CAST(NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(contributor_ip)) AS INT64)
          ELSE NULL
        END AS clientIpNum,
        CASE
          WHEN BYTE_LENGTH(contributor_ip) < 16 THEN SAFE_CAST(NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(contributor_ip)) / (256*256) AS INT64) 
          ELSE NULL
        END AS classB
      FROM
        `publicdata.samples.wikipedia`
      WHERE
        contributor_ip IS NOT NULL ) AS a
    JOIN
      `fh-bigquery.geocode.geolite_city_bq_b2b` AS b
    ON
      a.classB = b.classB
    WHERE
      a.clientIpNum BETWEEN b.startIpNum
      AND b.endIpNum
      AND city != ''
    GROUP BY
      city,
      countryLabel
    ORDER BY
      1 DESC
    

    edit2:我似乎通过不正确地抛出浮动来解决问题。目前,标准SQL返回41815行,而不是传统SQL中的56347行,这可能是由于标准SQL缺少从IPv6到int的转换,但可能是由于其他原因。此外,传统SQL查询的性能要好得多,运行时间约为10秒,而不是标准SQL的整分钟。

    2 回复  |  直到 4 年前
        1
  •  2
  •   Ramtin M. Seraj    8 年前

    根据 https://gist.github.com/matsukaz/a145c2553a0faa59e32ad7c25e6a92f7

    #standardSQL
    SELECT
      id,
      IFNULL(city, 'Other') AS city,
      IFNULL(countryLabel, 'Other') AS countryLabel,
      latitude,
      longitude
    FROM (
      SELECT
        id,
        NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip)) AS clientIpNum,
        TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip))/(256*256)) AS classB
      FROM
        `<project>.<dataset>.log` ) AS a
    LEFT OUTER JOIN
      `fh-bigquery.geocode.geolite_city_bq_b2b` AS b
    ON
      a.classB = b.classB
      AND a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum
    ORDER BY
      id ASC
    
        2
  •  0
  •   Gabriel Hodoroaga    4 年前

    这个问题的答案对于ipv6地址无效。

    遵循此处描述的方法 https://medium.com/@hoffa/geolocation-with-bigquery-de-identify-76-million-ip-addresses-in-20-seconds-e9e652480bd2

    WITH test_data AS (
        SELECT '2a02:2f0c:570c:fe00:1db7:21c4:21fa:f89' AS ip UNION ALL 
        SELECT '79.114.150.111' AS ip
    )
    -- replace the input_data with your data
    , ipv4 AS (
        SELECT DISTINCT ip, NET.SAFE_IP_FROM_STRING(ip) AS ip_bytes
        FROM test_data 
        WHERE BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 4
    ), ipv4d AS (
        SELECT ip, city_name, country_name, latitude, longitude
        FROM (
            SELECT ip, ip_bytes & NET.IP_NET_MASK(4, mask) network_bin, mask
            FROM ipv4, UNNEST(GENERATE_ARRAY(8,32)) mask
        )
        JOIN `demo_bq_dataset.geoip_city_v4`
        USING (network_bin, mask)
    ), ipv6 AS (
        SELECT DISTINCT ip, NET.SAFE_IP_FROM_STRING(ip) AS ip_bytes
        FROM test_data 
        WHERE BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 16
    ), ipv6d AS (
        SELECT ip, city_name, country_name, latitude, longitude
        FROM (
            SELECT  ip, ip_bytes & NET.IP_NET_MASK(16, mask) network_bin, mask
            FROM ipv6, UNNEST(GENERATE_ARRAY(19,64)) mask
        )
        JOIN `demo_bq_dataset.geoip_city_v6`  
        USING (network_bin, mask)
    )
    SELECT * FROM ipv4d
    UNION ALL 
    SELECT * FROM ipv6d
    

    geoip_city_v4 geoip_city_v6 您需要从下载geoip数据库 https://maxmind.com/

    hodo.dev/posts/post-37-gcp-bigquery-geoip .

    推荐文章