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 логин и пароль для подключения к таблице. 

Несколько важных деталей:

1. Источник данных

При создании словаря 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-адрес.