Строковые и URL-функции

ClickHouse предоставляет специализированные строковые функции, выходящие далеко за рамки стандартного SQL. Мультипаттерный поиск позволяет искать десятки ключевых слов за один проход по столбцу. Нечёткое сопоставление находит похожие строки без точного совпадения. URL-парсинг разбирает адреса на компоненты со скоростью SIMD-инструкций.

Все эти функции работают поверх столбцового хранения: ClickHouse последовательно сканирует один строковый столбец, обрабатывая данные батчами с использованием SSE/AVX инструкций.

Строковый поиск: multiSearchAny и multiSearchAllPositions

multiSearchAny: мультипаттерный поиск

-- Поиск логов, содержащих любое из ключевых слов
SELECT timestamp, message
FROM logs
WHERE multiSearchAny(message, ['error', 'timeout', 'connection refused', 'OOM'])
ORDER BY timestamp DESC
LIMIT 100;

multiSearchAny(haystack, [needles]) возвращает 1, если хотя бы одна из “игл” найдена в строке. Внутренняя реализация использует алгоритм Aho-Corasick: все паттерны компилируются в конечный автомат, и строка сканируется один раз вместо N раз для N паттернов.

Ограничение: максимум 28 игл в массиве. При превышении — ошибка. Это связано с размером внутреннего битового вектора (32-bit, 4 бита зарезервированы).

-- Регистронезависимый поиск (суффикс -CaseInsensitive)
SELECT count(*)
FROM logs
WHERE multiSearchAnyCaseInsensitive(message, ['error', 'warn', 'fatal']);

multiSearchAllPositions: позиции всех совпадений

-- Позиции каждого паттерна в строке (0 = не найден)
SELECT
    message,
    multiSearchAllPositions(message, ['error', 'timeout', 'OOM']) AS positions
FROM logs
WHERE multiSearchAny(message, ['error', 'timeout', 'OOM']);
-- positions = [15, 0, 42] означает: 'error' на позиции 15, 'timeout' не найден, 'OOM' на позиции 42

Возвращает массив позиций для каждого паттерна. Позиция 0 означает отсутствие совпадения. Полезно для классификации логов: какие именно ключевые слова найдены.

Регулярные выражения: extractAll и extract

-- Извлечь все email-адреса из текста
SELECT extractAll(body, '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}') AS emails
FROM messages;

-- Извлечь первый IPv4-адрес из лога
SELECT extract(message, '\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}') AS ip
FROM logs;

extractAll(haystack, pattern) возвращает массив всех совпадений regex в строке. extract(haystack, pattern) — только первое совпадение.

Нечёткое сопоставление: ngramDistance

-- Найти товары, похожие на "iPhone 15 Pro"
SELECT
    product_name,
    ngramDistance(product_name, 'iPhone 15 Pro') AS distance
FROM products
WHERE ngramDistance(product_name, 'iPhone 15 Pro') < 0.5
ORDER BY distance ASC
LIMIT 10;

ngramDistance(s1, s2) вычисляет расстояние между двумя строками на основе 4-грамм (подстроки длиной 4 символа):

• 0.0 — строки идентичны (множества 4-грамм полностью совпадают)
• 1.0 — строки полностью различны (нет общих 4-грамм)
• Промежуточные значения — степень различия

Алгоритм: разбивает обе строки на 4-граммы, вычисляет долю несовпадающих. Для “iPhone” 4-граммы: iPho, Phon, hone.

-- Регистронезависимый вариант
SELECT ngramDistanceCaseInsensitive('clickhouse', 'ClickHouse');
-- Результат: 0.0 (идентичны после нормализации)

-- ngramSearch: асимметричная версия (доля needle-грамм в haystack)
SELECT ngramSearch('ClickHouse is a fast database', 'clickhouse');
-- Высокое значение: все 4-граммы 'clickhouse' найдены в haystack

URL-парсинг: SIMD-оптимизированные функции

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

Базовые URL-функции

SELECT
    domain('https://docs.clickhouse.com/en/sql-reference/functions') AS dom,
    topLevelDomain('https://docs.clickhouse.com/en/sql-reference/functions') AS tld,
    path('https://docs.clickhouse.com/en/sql-reference/functions') AS p,
    protocol('https://docs.clickhouse.com/en/sql-reference/functions') AS proto;
-- dom = 'docs.clickhouse.com'
-- tld = 'com'
-- p = '/en/sql-reference/functions'
-- proto = 'https'

queryString и extractURLParameter

-- Полная строка запроса
SELECT queryString('https://shop.com/search?q=laptop&sort=price&page=2');
-- Результат: 'q=laptop&sort=price&page=2'

-- Конкретный параметр
SELECT extractURLParameter('https://shop.com/search?q=laptop&sort=price', 'q');
-- Результат: 'laptop'

SELECT extractURLParameter('https://shop.com/search?q=laptop&sort=price', 'sort');
-- Результат: 'price'

Практика: аналитика кликстрима с URL-парсингом

-- Топ-10 доменов по количеству переходов
SELECT
    domain(page_url) AS site,
    count() AS views
FROM clickstream
GROUP BY site
ORDER BY views DESC
LIMIT 10;

-- Извлечение UTM-параметров для маркетинговой аналитики
SELECT
    extractURLParameter(page_url, 'utm_source') AS source,
    extractURLParameter(page_url, 'utm_medium') AS medium,
    extractURLParameter(page_url, 'utm_campaign') AS campaign,
    count() AS visits,
    uniq(user_id) AS unique_users
FROM clickstream
WHERE extractURLParameter(page_url, 'utm_source') != ''
GROUP BY source, medium, campaign
ORDER BY visits DESC;

Мультипаттерный поиск логов

-- Классификация ошибок в логах
SELECT
    multiSearchFirstIndex(message,
        ['connection refused', 'timeout', 'OOM', 'disk full', 'permission denied']
    ) AS error_class,
    count() AS occurrences
FROM logs
WHERE level = 'ERROR'
GROUP BY error_class
ORDER BY occurrences DESC;
-- error_class: 1 = connection refused, 2 = timeout, 3 = OOM, 4 = disk full, 5 = permission denied

multiSearchFirstIndex возвращает индекс первого совпавшего паттерна (1-based). Позволяет классифицировать строки по категориям за один проход.

Нечёткий поиск товаров

-- Fuzzy matching: пользователь ввёл "aphone 15" вместо "iPhone 15"
SELECT product_name, price
FROM products
WHERE ngramDistanceCaseInsensitive(product_name, 'aphone 15') < 0.4
ORDER BY ngramDistanceCaseInsensitive(product_name, 'aphone 15') ASC
LIMIT 5;

Ключевые выводы

1. multiSearchAny — мультипаттерный поиск до 28 паттернов за один проход (Aho-Corasick). Значительно быстрее цепочки LIKE ... OR LIKE ....
2. ngramDistance — нечёткое сопоставление на основе 4-грамм. 0.0 = идентичны, 1.0 = полностью различны. Подходит для fuzzy search товаров и исправления опечаток.
3. URL-функции (domain, path, queryString, extractURLParameter) реализованы с SIMD-оптимизацией и работают значительно быстрее regex-парсинга.
4. extractAll/extract — regex для сложных паттернов (email, IP-адреса). Используйте, когда нужны захватывающие группы или паттерны, невыразимые через multiSearch.
5. multiSearchFirstIndex — классификация строк по категориям за один проход. Возвращает индекс первого совпавшего паттерна.