[Из песочницы] Операционные vs аналитические базы: колоночное vs построчное хранение данных
Базы данных можно реализовать с помощью Excel, GSheet или при помощи больших ORM систем. В своей практике бизнес-аналитика я сталкивался с разными решениями. А поскольку в бизнес-анализ я пришёл из финансов и аудита, то каждый раз встречая новую систему задавался вопросами — чем все они отличаются друг от друга и какие задачи решают? Некоторые ответы нашёл. В этой статье будет рассмотрено два основных назначения баз данных:
1 — учёт операций,
2 — анализ данных
Первый тип задач решают OLTP системы: от On Line Transaction Processing. Второй тип решают OLAP системы: от On Line Analytical Processing
OLTP
Модель хранения данных в OLTP можно сравнить с записями в телефонной книге. Строка в таблице представлена в виде индекса и соответствующих этому индексу данных: (indexN, data). Поэтому такую таблицу нельзя называть таблицей. Это скорее обычная книга, с пронумерованными строками. Если в книгу нужно записать новую операцию — добавляем строку, присваиваем индекс и закрываем книгу. Из книги торчат ярлыки по которым можно быстро O (log n), находить нужную строку и делать CRUD.
Для целей учёта операций это дружелюбное отображение. Но оно недружелюбно для анализа данных, в котором нам важны не строки сами по себе, но вычисления на основе содержимого этих строк. И если делать аналитический запрос на основе содержимого строк, т.е. по не индексированным полям, то такие запросы будут работать медленнее.
Индексировать все записи, как известно, не вариант. Хоть книга становится похожей на таблицу, поскольку атрибуты становятся доступны для быстрого поиска, но при этом существенно замедляется создание новых и обновление имеющихся строк. Поскольку эти операции будут требовать пересортировки всего массива.
Компромисс между OLAP и OLTP
В решениях 1С компромисс реализован следующим образом. События при записи в базу пишутся сразу в несколько мест. В одном месте записи имеют мало индексов и оптимизированы под OLTP нагрузки, в другом месте записи индексируются по всем полям и адаптированы для OLAP нагрузок. Такие таблицы называются регистрами накоплений и регистрами сведений. Поскольку запись в несколько мест кратно увеличивает занимаемое пространство, то для экономии в регистры попадают не все атрибуты транзакции, а только те, которые считаются важными для данного раздела аналитического учёта. Подобный компромисс называется ROLAP моделью, т.е. реляционно-аналитическим отображением.
OLAP
В SAP, немецком аналоге 1С пошли дальше. Реляционную OLTP модель в этом ПО можно реплицировать в OLAP модель. В SAP HANA реализована колоночная структура хранения. Это значит, что «таблицы» хранятся там не в виде набора строк, а в виде набора колонок.
Аналогичная схема хранения реализована в таких решениях как Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.
Отличие колоночного хранения от построчного
Если в построчной структуре данные хранятся в виде «горизонтальных» кортежей, каждый из которых является транзакцией:
period, product, department
(Q1, SKU1, 1)
(Q1, SKU2, 1)
(Q1, SKU1, 1)
...
(Q2, SKU1, 1)
(Q2, SKU1, 1)
(Q3, SKU1, 1)
(Q3, SKU1, 1)
...
То в колоночной такие данные хранятся «вертикально»:
(Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...)
(SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...)
(1,1,1, ... 1,1,1,1, ...)
Повторы можно оптимизировать, условно так:
period = (Q1, {start: 0, count: n}, Q2, {start: n+1; count: m}, ...)
product = (SKU1, {start: 0, count: 1}, SKU2, {start: 1; count: 1}, SKU1, {start: 2; count: m}, ...)
department = (1,{start:0, count:m}...)
Если же есть колонка для которой такая оптимизация не сократит изначальный объём, то данные хранятся в первоначальном виде.
Движок колоночной таблицы сам выбирает последовательность сортировки колонок, но если вы знаете свои данные и отсортируете их вручную, то часто это увеличивает компрессию и облегчает аналитические нагрузки. У меня сжатие отдельных таблиц превышало 300 раз. На практике такая структура хранения данных:
- позволяет сжимать данные до уровня когда они помещаются в RAM, т.е. делают доступными in-memory вычисления, которые не сопоставимы по скорости с запросами к реляционным БД
- задаёт свои правила для построения модели данных, уже не требуя такой нормализации как в OLTP
- задаёт свою семантику для построения аналитических выражений.
Специфика выражений подробно описана:
здесь — для Google BigQuery.
здесь — для Microsoft DAX.
BI как инфраструктура колоночных баз
BI это решения обслуживающие аналитические нагрузки. И они делают жизнь намного проще если выстроены поверх колоночных баз данных. Это может быть самодельная связка ClickHouse-Grafana-Python или связка стека Google: Bigquery-Data Studio-Dataprep-Dataflow или же монолитный Power BI.
Многомерные кубы являются другой OLAP альтернативой колоночной схемы хранения. Но для меня выражения MDX, если сравнивать их с SQL в BQ или языком DAX — избыточны и сложны.
