Ответ 1
2019, значительно улучшенный ответ:
#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
Исправлена версия этого ответа по адресу: http://googlecloudplatform.blogspot.com/2014/03/geoip-geolocation-with-google-bigquery.html.
Позвольте мне привести в порядок исходный запрос:
SELECT
id,
client_ip,
client_ip_code,
B.Country_Name AS Country_Name
FROM (
SELECT
id,
contributor_ip AS client_ip,
INTEGER(PARSE_IP(contributor_ip)) AS client_ip_code,
1 AS One
FROM
[publicdata:samples.wikipedia]
WHERE contributor_ip IS NOT NULL
LIMIT
1000
) AS A1
LEFT JOIN
(
SELECT
From_IP_Code,
To_IP_Code,
Country_Name,
1 AS One
FROM
--3 IP sets: 1.valid ranges, 2.Gaps, 3. Gap at the END of the set
(
SELECT
From_IP_Code,
To_IP_Code,
Country_Name
FROM
[playscape-proj:GeoIP.GeoIP]) -- all Ranges ov valid IPs
,
(
SELECT
PriorRangeEndIP+1 From_IP_Code,
From_IP_Code-1 AS To_IP_Code,
'NA' AS Country_Name -- Missing rages lower FROM From_IP
from(
SELECT
From_IP_Code,
To_IP_Code,
Country_Name
,
LAG(To_IP_Code,
1,
INTEGER(0)) OVER(
ORDER BY
From_IP_Code ASC) PriorRangeEndIP --use of LAG function to find prior valid range
FROM
[playscape-proj:GeoIP.GeoIP])A
WHERE
From_IP_Code>PriorRangeEndIP+1) -- If gap FROM prior valid range IS >1 than its a gap to fill
,
(
SELECT
MAX(To_IP_Code)+1 AS From_IP_Code,
INTEGER (4311810304) AS To_IP_Code,
'NA' AS Country_Name -- Missing rages higher tan Max To_IP
FROM
[playscape-proj:GeoIP.GeoIP])
) AS B
ON A1.ONE=B.ONE --fake JOIN condition to overcome allowed use of = only IN joins
WHERE
A1.client_ip_code>=B.From_IP_Code
AND A1.client_ip_code<=B.To_IP_Code -- JOIN condition WHERE valid IP exists ON left
OR (A1.client_ip_code IS NULL
AND B.From_IP_Code=1 ) -- WHERE there IS no valid IP ON left contributor_ip;
Это длинный запрос! (и очень интересный). Работает за 14 секунд. Как мы можем оптимизировать это?
Некоторые трюки, которые я нашел:
- Пропустить NULL. Если в журнале нет IP-адреса, не пытайтесь сопоставить его.
- Уменьшите комбинации. Вместо того, чтобы присоединяться к каждой записи левой стороны с каждой записью правой стороны, как насчет объединения только записей 39.xxx на левой стороне с записями 39.xxx на правой стороне. Есть только несколько (3 или 4) правил, которые охватывают несколько диапазонов. Было бы легко добавить пару правил в таблицу геолита, чтобы добавить правила для устранения этих пробелов.
Итак, я меняюсь:
-
1 AS OnetoINTEGER(PARSE_IP(contributor_ip)/(256*256*256)) AS One(дважды). - Добавление 'WHERE contributor_ip IS NULL'.
И теперь он работает за 3 секунды! 5% ips не могли быть локализованы, вероятно, с помощью описанных пробелов (легко исправить).
Теперь, как насчет перехода от LIMIT 1000 к LIMIT 300000. Сколько времени это займет?
37 секунд! Гораздо лучше, чем описано 25 минут. Если вы хотите подняться еще выше, я бы предложил превратить правую таблицу в статическую - поскольку после вычисления она вообще не меняется, это просто расширение основных правил. Тогда вы можете использовать JOIN EACH.
SELECT
id,
client_ip,
client_ip_code,
B.Country_Name AS Country_Name
FROM (
SELECT
id,
contributor_ip AS client_ip,
INTEGER(PARSE_IP(contributor_ip)) AS client_ip_code,
INTEGER(PARSE_IP(contributor_ip)/(256*256*256)) AS One
FROM
[publicdata:samples.wikipedia]
WHERE contributor_ip IS NOT NULL
LIMIT
300000
) AS A1
JOIN
(
SELECT
From_IP_Code,
To_IP_Code,
Country_Name,
INTEGER(From_IP_Code/(256*256*256)) AS One
FROM
--3 IP sets: 1.valid ranges, 2.Gaps, 3. Gap at the END of the set
(
SELECT
From_IP_Code,
To_IP_Code,
Country_Name
FROM
[playscape-proj:GeoIP.GeoIP]) -- all Ranges ov valid IPs
,
(
SELECT
PriorRangeEndIP+1 From_IP_Code,
From_IP_Code-1 AS To_IP_Code,
'NA' AS Country_Name -- Missing rages lower FROM From_IP
from(
SELECT
From_IP_Code,
To_IP_Code,
Country_Name
,
LAG(To_IP_Code,
1,
INTEGER(0)) OVER(
ORDER BY
From_IP_Code ASC) PriorRangeEndIP --use of LAG function to find prior valid range
FROM
[playscape-proj:GeoIP.GeoIP])A
WHERE
From_IP_Code>PriorRangeEndIP+1) -- If gap FROM prior valid range IS >1 than its a gap to fill
,
(
SELECT
MAX(To_IP_Code)+1 AS From_IP_Code,
INTEGER (4311810304) AS To_IP_Code,
'NA' AS Country_Name -- Missing rages higher tan Max To_IP
FROM
[playscape-proj:GeoIP.GeoIP])
) AS B
ON A1.ONE=B.ONE --fake JOIN condition to overcome allowed use of = only IN joins
WHERE
A1.client_ip_code>=B.From_IP_Code
AND A1.client_ip_code<=B.To_IP_Code -- JOIN condition WHERE valid IP exists ON left
OR (A1.client_ip_code IS NULL
AND B.From_IP_Code=1 ) -- WHERE there IS no valid IP ON left contributor_ip;