ClickHouse предоставляет большой набор функций для работы с IP-адресами, что позволяет хранить и обрабатывать сетевые данные прямо в базе, без необходимости выгружать их во внешние скрипты.

IPv4 и IPv6 в одной колонке 

Если IPv4 и IPv6 хранятся в разных колонках — никаких проблем не возникает: каждая группа функций работает со “своим” типом данных. Но в реальных задачах (например, при хранении логов nginx) часто требуется помещать IPv4 и IPv6 в одну колонку. В этом случае обычно используют тип IPv6, куда IPv4 записываются в виде IPv4-mapped адресов (::ffff:x.x.x.x). Здесь и появляется нюанс: функции для IPv4 и IPv6 различаются, и вызов нужной функции приходится выбирать по условию.

Как избавиться от префикса ::ffff: у IPv4-адресов

При хранении IPv4 в колонке типа IPv6 они автоматически получают префикс ::ffff:. Если нужно вывести «чистый» IPv4-адрес, необходимо либо преобразовать его в тип IPv4, либо убрать префикс строковыми функциями. Поэтому в запросах обычно добавляют условие, определяющее тип адреса:

SELECT 
  if(
    startsWith(toString(ip), '::ffff:'), 
    toString(toIPv4(ip)), 
    toString(ip)
  )
FROM nginx_log;

Вызов функций для обработки IP-адресов

ClickHouse использует строгую типизацию, схожую с C++. Поэтому при работе с IP необходимо явно указывать преобразования.

Например, функция IPv4CIDRToRange применяется только к данным типа IPv4. Если данные хранятся в виде IPv6, но фактически представляют собой IPv4-mapped адреса, их сначала нужно привести к IPv4:

SELECT
  if(
    startsWith(toString(ip), '::ffff:'),
    toString(IPv4CIDRToRange(toIPv4(ip), netbits).1),
    toString(IPv6CIDRToRange(ip, netbits).1)
  ) AS network
FROM ngnx_log;

Формат возвращаемых данных

Функции IPv4CIDRToRange и IPv6CIDRToRange возвращают tuple — массив с заранее определёнными типами. Чтобы получить конкретный элемент, используется точечная нотация:

IPv6CIDRToRange(ip, netbits).1

(например, .1 — это первый элемент массива).

Проблема с разными типами и приведение к строке

Важно учитывать, что типы внутри возвращаемых массивов различаются: IPv6CIDRToRange возвращает IPv6, а IPv4CIDRToRange — IPv4. Если попытаться объединить их без приведения типов, ClickHouse выдаст ошибку:

Code: 386. DB::Exception: There is no supertype for types IPv6, IPv4 because some of them are numbers and some of them are not

Чтобы избежать ошибки, необходимо привести значения к единому типу — обычно к строковому:

toString(...)

Так можно корректно объединить результаты обеих функций в одном запросе.

Поиск IP адреса в списке сетей

Для поиска IP адреса в списке сетей используется функция isIPAddressInRange:

SELECT name
FROM search_engines
WHERE isIPAddressInRange('8.8.8.8', cidr)

В поле cidr хранятся String записи вида "8.8.8.0/24"