Поиск географических местоположений для ipv4 и ipv6 с использованием BigQuery и MaxMind GeoIP
Я пытаюсь сопоставить пользователей с их географическим положением, используя их IP-адреса.
IP-адреса представляют собой смесь ipv4, ipv6 и некоторых строк с недопустимыми записями.
Я использую GeoLite2-City-Blocks-IPv4 (знаю, что мне придется использовать другой файл для ipv6, если кто-нибудь знает, какой из них правильный, очень признателен) и GeoLite2-City-Locations-en. Таким образом, в одном файле есть блоки IP и их коды местоположения, а в другом — фактические местоположения этих кодов.
Я использовал инструкции из этой статьи Фелипе Хоффа https://towardsdatascience.com/geolocation-with-bigquery-de-identify-76-million-ip-addresses-in-20-seconds-e9e652480bd2 для сопоставления моих IP-адресов с IP-адреса в файле блоков.
Проблема в том, что я получаю сообщение об ошибке при попытке использовать функции NET.SAFE_IP_FROM_STRING(ip_address) и NET.IP_NET_MASK(4,mask). Ошибка:
Побитовый бинарный оператор для BYTES требует одинаковой длины входных данных. Получил 16 байтов слева и 4 байта справа.
Поэтому я попытался убедиться, что IP-адреса действительны, прежде чем передавать их функции с помощью регулярного выражения. Это работает для ipv4. Теперь я выяснил, что регулярное выражение также соответствует ipv6, и, судя по моим проверкам, оно кажется точным. Однако я все еще получаю сообщение об ошибке. Я не могу понять, почему и как исправить мой запрос, чтобы получить правильный результат.
См. весь запрос ниже:
(
SELECT
*,
NET.SAFE_IP_FROM_STRING(ip_address) & NET.IP_NET_MASK(4,
mask) network_bin
FROM (
SELECT
* EXCEPT (is_valid)
FROM (
SELECT
*,
CASE
WHEN (REGEXP_CONTAINS(ip_address, r'\A[a-f0-9]{1,4}(:[a-f0-9]{1,4}){7}\z') OR (NOT REGEXP_CONTAINS(ip_address, r'\A(.*?[a-f0-9](:|\z)){8}') AND REGEXP_CONTAINS(ip_address, r'\A([a-f0-9]{1,4}(:[a-f0-9]{1,4}){0,6})?::([a-f0-9]{1,4}(:[a-f0-9]{1,4}){0,6})?\z')) OR REGEXP_CONTAINS(ip_address, r'\A[a-f0-9]{1,4}(:[a-f0-9]{1,4}){5}:(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9]?[0-9])(\.(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9]?[0-9])){3}\z') OR (NOT REGEXP_CONTAINS(ip_address, r'\A(.*?[a-f0-9]:){6}') AND REGEXP_CONTAINS(ip_address, r'\A([a-f0-9]{1,4}(:[a-f0-9]{1,4}){0,4})?::([a-f0-9]{1,4}:){0,5}(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9]?[0-9])(\.(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9]?[0-9])){3}\z')) ) THEN TRUE
WHEN REGEXP_CONTAINS(ip_address, r"^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$") THEN TRUE
ELSE
FALSE
END
AS is_valid
FROM (
SELECT
user,
ip_address,
date
FROM
`project.dataset.table`)
WHERE
is_valid IS TRUE),
UNNEST(GENERATE_ARRAY(9,32)) mask)
Ответы 2
Ответ на вопрос был здесь: https://stackoverflow.com/a/65403033/12675934
Вкратце: проблема в том, что NET.SAFE_IP_FROM_STRING возвращает 4 байта для IPv4 и 16 байтов для IPv6. Итак, & NET.IP_NET_MASK(4, маска) подходит для IPv4, но для IPv6 вам нужно использовать & NET.IP_NET_MASK(16, маска).
Там тот же ответ, что и на этот вопрос Как повысить производительность запроса GeoIP в BigQuery?
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/
Вы можете следовать этому руководству, чтобы обновить и подготовить набор данных https://hodo.dev/posts/post37-gcp-bigquery-geoip/.