Використання словників ClickHouse для швидкого пошуку даних по IP-мережах

19.11.2025

ClickHouse дозволяє створювати в оперативній пам'яті сервера словники, які забезпечують швидкий доступ до даних без додаткових JOIN-ів. Один з поширених сценаріїв — визначення країни за IP-адресою на основі таблиці з CIDR-мережами.

Вихідна таблиця

Припустимо, у нас є таблиця geo_ip, в якій кожен рядок містить IP-мережу у форматі CIDR та відповідну їй країну:

CREATE TABLE geo_ip (
  cidr String,
  country String
)
Engine = ReplacingMergeTree()
ORDER BY (cidr);

INSERT INTO geo_ip ('185.39.224.0/24', 'UA');

Створення словника

Для того щоб швидко визначати країну за IP-адресою, створимо словник geo_dict:

CREATE DICTIONARY geo_dict
(
	cidr String,
	country String
)
PRIMARY KEY cidr
SOURCE (clickhouse(database 'db_name' table 'geo_ip' user 'user_login' password 'user_password'))
LAYOUT (ip_trie)
LIFETIME (3600);

При створенні словника робота з таблицею відбувається як з віддаленим джерелом даних, тому обов'язково при створенні словника вказуйте в полі SOURCE логін і пароль для підключення до таблиці.

Кілька важливих деталей:

Джерело даних

При створенні словника ClickHouse звертається до таблиці як до зовнішнього джерела. Тому обов'язково вказуються параметри підключенняuser, password), навіть якщо таблиця знаходиться в тій же базі.

2. LAYOUT = ip_trie

Формат зберігання ip_trie оптимізований спеціально для пошуку IP-адрес у мережах, заданих через CIDR. Він дозволяє ефективно знаходити найдовше збіг за префіксом і визначати, до якої мережі входить IP.

3. LIFETIME

Параметр LIFETIME (3600) вказує частоту оновлення даних словника — раз на годину.

Використання словника в запитах

Після створення словника можна виконувати швидкий пошук країни за IP в таблиці логів:

SELECT 
  ip,
  dictGet('geo_dict', ('country'), tuple(ip))
FROM nginx_log

Функція dictGet повертає значення стовпчика country для мережі, якій відповідає вказана IP-адреса.

# Блог # ClickHouse

Зміст