Загрузка и использование MMDB в Clickhouse

Загрузка и использование MMDB в Clickhouse
Photo by Pawel Czerwinski / Unsplash

Прошлый способ использования GeoIP данных показал, что он не очень удобен. Поэтому я зарылся в проблему и нашел более простой и действенный способ, как загрузить данные из базы MaxMind (но лучше использовать бесплатные аналоги типа IPInfo) в Clickhouse.

Для этого мы берем отсюда релиз аплоадера и запускаем его вот так:

./mmdb-to-clickhouse -dsn clickhouse://admin:admin@localhost:9000 -drop -mmdb ./ipinfo_lite.mmdb -name ipinfo_mmdb -reload -test

Далее в ВАШЕЙ базе надо создать функции-обертки:

CREATE FUNCTION get_country_mmdb AS (ip_string) ->
    multiIf(
        ip_string IS NULL, 'Null',
        ip_string = '', 'Empty',
        NOT isIPv4String(ip_string), 'Invalid',
        dictGetOrDefault('default.ipinfo_mmdb_val', 'country',
            dictGetOrDefault('default.ipinfo_mmdb_net', 'pointer', toIPv6(ip_string), 0), 'Unknown')
    );

CREATE FUNCTION get_country_code_mmdb AS (ip_string) ->
    multiIf(
        ip_string IS NULL, 'Null',
        ip_string = '', 'Empty',
        NOT isIPv4String(ip_string), 'Invalid',
        dictGetOrDefault('default.ipinfo_mmdb_val', 'country_code',
            dictGetOrDefault('default.ipinfo_mmdb_net', 'pointer', toIPv6(ip_string), 0), 'Unknown')
    );

Остальные функции можете сделать по аналогии.

Использовать функции можно вот так:

SELECT get_country_code_mmdb('8.8.8.8') as country;
SELECT get_country_mmdb('8.8.8.8') as country;

Более полный пример использования:

SELECT
    toDate(timestamp) AS day,
    get_country_code_mmdb(toString(events.`event_data.ip_address`)) as country,
    count(*) AS count
FROM events
WHERE event_name = 'Account: Signed Up'
  AND events.`event_data.ip_address` IS NOT NULL
GROUP BY day, country
ORDER BY day, country;

Обновлять данные можно той же командой, что и загрузка данных.

Read more

Настройка локального SMTP-релея с Docker

Настройка локального SMTP-релея с Docker

При разработке приложений или настройке домашнего сервера часто возникает необходимость отправлять электронные письма. Вместо настройки полноценного почтового сервера можно использовать SMTP-релей, который будет пересылать письма через внешний почтовый сервис. В этой статье рассмотрим, как настроить локальный SMTP-релей с помощью Docker. Что такое SMTP-релей SMTP-релей — это промежуточный почтовый сервер, который принимает

Охота на цифрового шпиона: как астроном стал первым кибердетективом

Охота на цифрового шпиона: как астроном стал первым кибердетективом

История о том, как одна бухгалтерская ошибка привела к раскрытию международной шпионской сети и заложила основы современной кибербезопасности В августе 1986 года астроном Клиффорд Столл работал системным администратором в Лаборатории Лоуренса Беркли в Калифорнии. Его обычный день был нарушен странным сообщением: компьютерная система показывала ошибку в 75 центов. Кто-то создал

Когда технологии слежки обращаются против государства: хакер наркокартеля против ФБР

Когда технологии слежки обращаются против государства: хакер наркокартеля против ФБР

В эпоху повсеместного наблюдения границы между охотником и жертвой становятся размытыми. Недавний отчет Министерства юстиции США раскрыл тревожный случай, когда сложные технологии слежки были обращены против самих правоохранительных органов с летальными последствиями. Цифровая охота на информаторов Согласно 47-страничному отчету Генерального инспектора Министерства юстиции, хакер, связанный с преступной группировкой Хоакина "