Для чего нужны колоночные СУБД и как мы используем ClickHouse в наших сервисах

Быстрая обработка аналитических запросов — главное преимущество колоночных СУБД. Рассказываем, какие задачи это позволяет решать онлайн-проектам и делимся примерами на наших сервисах. 

Каких типов бывают системы управления данными?

Диджитализация привела не только к появлению новых возможностей для онлайн-проектов и пользователей, но и к необходимости обрабатывать большое количество данных. Для решения задач Big Data, Data Science, Machine Learning, IoT и множества других направлений используются различные системы управления базами данных (СУБД). 

Типы СУБД

• Реляционные: MySQL, MSSQL, PostrgeSQL. Используются при частом изменении данных и при выдаче небольшого количества записей за одну операцию. Поддерживают транзакции.
• Key-value (ключ-значение): Redis, DynamoDB и Aerospike. Используются в основном для кеша и логирования.
• Документно-ориентированные: MongoDB, CouchDB и Amazon DocumentDB. Используются для хранения неструктурированных данных в виде документов.
• Колоночные: Vertica, Google Big Query, ClickHouse. Эффективны при выполнении аналитических запросов, создании отчетов, чтении и сжатии данных.
• Графовые: Neo4j, Amazon Neptune. Используются для хранения данных со сложными взаимосвязями, например в социальных сетях и рекомендательных системах.
• Поисковые: Elasticsearch, Apache Solr и Amazon CloudSearch. Оптимизированы для быстрого поиска файлов, например для текстового контента при работе со статьями и блогами.

Выбор БД зависит от типа и объема данных, задач и возможностей проекта, а также других критериев. В этой статье подробнее рассмотрим особенности колоночных СУБД и ClickHouse в частности.

Особенности колоночных СУБД

Универсальные СУБД медленно обрабатывают сложные аналитические запросы, особенно если БД хранит информацию в строках, а количество записей достигает критического объема. При создании отчетов традиционная реляционная СУБД считывает большое количество связанной информации, до 90% данных которой могут быть ненужными для конкретной задачи.

Колоночные СУБД хранят данные в столбцах. И это позволяет существенно ускорить аналитические команды, создание отчетов, чтение и запись. При выполнении запроса в колоночной БД можно задействовать только необходимые для конкретного сценария данные: в каждом столбце хранится однотипная информация. Поэтому при чтении ClickHouse работает в 100—1000 раз быстрее традиционных СУБД.

Колоночные СУБД активно используются для онлайн-обработки аналитических запросов (OLAP), которым свойственно следующее:

• преобладание команд для чтения информации;
• добавление данных массивами от 1000 строк; 
• вставка новых строк без изменений текущих;
• считывание в сервисах множества строк БД и лишь немногих столбцов.

В наших сервисах для быстрой обработки данных, например при выводе метрик, мы используем ClickHouse — колоночную СУБД на базе SQL.

Плюсы ClickHouse:

• Решение с открытым исходным кодом.
• Считывание только нужных столбцов, эффективное сжатие однотипных данных и ускоренная обработка запросов.
• Быстрый доступ к конкретным значениям или диапазонам.
• Оптимизация издержек на контроль и эффективное использование CPU.
• Эффективное сжатие.
• Быстрая вставка: 100 тысяч строк из логов можно вставить в ClickHouse за 2 секунды. 

Минусы ClickHouse:

• Ограничения в поддержке JOIN-запросов.
• Отсутствие поддержки транзакций.
• Медленная одиночная вставка.
• Рекомендованная частота вставки — раз в секунду.

При работе с ClickHouse желательно заранее продумать структуру БД и будущие запросы. И лучше указать порядок сортировки данных при создании таблиц: например, по id, дате или полю с кодом страны. Обычно схема таблиц не меняется после создания, а если нужно ее изменить — это будет медленно и сложно.

ClickHouse — СУБД для записи и чтения. Отказ от редактирования и удаления — это компромисс, который и обеспечивает высокую скорость выполнения запросов.

Так как каждая колонка содержит одну и ту же категорию информации становится проще и выборка данных (select). Данные каждого столбца хранятся независимо от других. За счет этого при запросах считывается только несколько колонок, информация из которых и требуется по запросу. Например, из 100 столбцов система обратится только к 5 и быстро выполнит запрос.

Хранение данных в столбцах также повышает эффективность сжатия. При этом можно использовать оптимизированные кодеки сжатия, например для числовых столбцов и текстовой информации.

Взаимодействие с ClickHouse

Работа с данными востребована во множестве направлений digital, IT и других отраслях. Поэтому колоночные СУБД и ClickHouse в частности можно использовать в разных сферах:

• аналитике онлайн-проектов и мобильных приложений;
• рекламных сетях и онлайн-торгах;
• телекоммуникации;
• электронной коммерции;
• информационной безопасности;
• бизнес-аналитике;
• онлайн-играх;
• интернете вещей.

Поддерживаемые библиотеки: PHP, Go, Swift, NodeJs, Perl, Ruby, Rust, R, Java, Scala, Kotlin, C#, Elixir, Nim, Haskell.

Колоночные СУБД оптимизированы для выполнения агрегационных операций (например, SUM, AVG, COUNT) на больших наборах данных, что делает их наиболее подходящими для аналитики, отчетности и других задач.

Примеры использования

• логи (с авто-удалением: TTL);
• исторические данные (IP вчера, позавчера, год назад);
• визиты (кто, откуда);
• биллинг (конкретизация стоимости).

Сжатие данных

Эффективнее проводить сжатие в одной колонке однообразных данных, чем разнообразных. Например, столбец с ячейками country_code1, country_coude2... будет обработан проще и быстрее, чем набор из ID, IP, кода страны и других данных.

• Методы: для чисел, даты, строк.
• Преимущества: на каждую колонку можно указать свой метод.
• Недостатки: высокая степень сжатия потребляет ресурсы.

Пример реализации: Таблицу на 114 Гб можно сжать до 3.65 ГБ.

В колоночных базах данных проблему дублирования структуры решает сжатие.
А пустота в колонках не занимает места. Можно использовать таблицу в таблице, чтобы не выполнять лишние JOIN-запросы и еще больше повысить скорость выборки.

Лайфхаки для оптимизации:

• nested table (таблица в таблице);
• агрегатные функции: arraySum(arrayMap(x -> (x.2), arrayFilter(..)));
• индексы, представления: materialized view;
• буферизированная таблица: CREATE TABLE .. ENGINE = Buffer(..);
• ускорение MySQL: CREATE TABLE (..) ENGINE = MySQL('host:port');

ClickHouse проще наполнять сразу тысячами и даже миллионами строк. Одиночная вставка может обрабатываться медленно. Поэтому можно накапливать данные в отдельном кеше (или в буферизированной таблице) и затем добавлять их за один раз.

Как мы используем ClickHouse в наших сервисах

Возможности ClickHouse пригодились для обработки данных в облачных продуктах и кибербезопасности.

• hoster Cloud — облачная платформа с оплатой по факту потребления и множеством решений для задач клиентов. Используем ClickHouse для расчета поминутной тарификации (биллинга) виртуальных серверов.
• hoster Guard — платформа безопасности для защиты веб-приложений от взломов, утечек данных и отказа в обслуживании. При помощи колоночной СУБД составляем логи трафика, доступов и ошибок.
• S3 Object Storage — облачный сервис для хранения данных, совместимый с Amazon S3. Анализируем использование хранилища и расчета тарификации.

Пример отображения метрик из личного кабинета hoster Guard

Объемы данных продолжают расти и для их обработки нужно выбирать подходящие инструменты. А еще можно воспользоваться нашими облачными решениями. Так вы обеспечите своим проектам высокую надежность и гибкое управление ресурсами с оплатой по факту потребления.