go - 从bigquery开放式数据库中查询IP地理位置数据:geolite2

Question

我可以使用什么查询从the bigquery open database: geolite2获取IP长度> = 1的数组的地理位置数据。该数组在golang程序中定义。
我得到的错误是:IP未定义为[11:77]

type IPValue struct {
    IP string
}

func main()  {
    ctx := context.Background()
    client, err := bigquery.NewClient(ctx, "{{project}}")
    if err != nil {
        panic(err)
    }

    q := client.Query(`
        WITH source_of_ip_addresses AS (
            SELECT @IPS as IPs
        )
        SELECT city_name
        FROM (
            SELECT ip, city_name, c, latitude, longitude, geoname_id
            FROM (
                SELECT *, NET.SAFE_IP_FROM_STRING(IP) & NET.IP_NET_MASK(4, mask) network_bin
                FROM source_of_ip_addresses, UNNEST(GENERATE_ARRAY(9,32)) mask
                WHERE BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(IP)) = 4
            )
            JOIN ` + "`fh-bigquery.geocode.201806_geolite2_city_ipv4_locs`" + `
            USING (network_bin, mask)
        )
    `)
    q.DisableQueryCache = false
    q.Parameters = []bigquery.QueryParameter {
        {
            Name: "IPS",
            Value: []IPValue{
                {
                    IP: "75.x.y.z",
                },
            },
        },
    }
    runErr := runAndRead(ctx, q)
    log.Println(runErr)
}

Answer 1

退房:

https://towardsdatascience.com/geolocation-with-bigquery-de-identify-76-million-ip-addresses-in-20-seconds-e9e652480bd2

查询:

#standardSQL
# replace with your source of IP addresses
# here I'm using the same Wikipedia set from the previous article
WITH source_of_ip_addresses AS (
  SELECT REGEXP_REPLACE(contributor_ip, 'xxx', '0')  ip, COUNT(*) c
  FROM `publicdata.samples.wikipedia`
  WHERE contributor_ip IS NOT null  
  GROUP BY 1
)
SELECT country_name, SUM(c) c
FROM (
  SELECT ip, country_name, c
  FROM (
    SELECT *, NET.SAFE_IP_FROM_STRING(ip) & NET.IP_NET_MASK(4, mask) network_bin
    FROM source_of_ip_addresses, UNNEST(GENERATE_ARRAY(9,32)) mask
    WHERE BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 4
  )
  JOIN `fh-bigquery.geocode.201806_geolite2_city_ipv4_locs`  
  USING (network_bin, mask)
)
GROUP BY 1
ORDER BY 2 DESC
Query complete (20.9 seconds elapsed, 1.14 GB processed)