Операционные 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 раз. На практике такая структура хранения данных:


  1. позволяет сжимать данные до уровня когда они помещаются в RAM, т.е. делают доступными in-memory вычисления, которые не сопоставимы по скорости с запросами к реляционным БД
  2. задаёт свои правила для построения модели данных, уже не требуя такой нормализации как в OLTP
  3. задаёт свою семантику для построения аналитических выражений.

Специфика выражений подробно описана:
здесь — для Google BigQuery.
здесь — для Microsoft DAX.


BI как инфраструктура колоночных баз


BI это решения обслуживающие аналитические нагрузки. И они делают жизнь намного проще если выстроены поверх колоночных баз данных. Это может быть самодельная связка ClickHouse-Grafana-Python или связка стека Google: Bigquery-Data Studio-Dataprep-Dataflow или же монолитный Power BI.


Многомерные кубы являются другой OLAP альтернативой колоночной схемы хранения. Но для меня выражения MDX, если сравнивать их с SQL в BQ или языком DAX — избыточны и сложны.

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 2

    0
    Статья интересная, спасибо. Но мало углублённой информации: такое ощущение, что прочитал введение к подробной статье про OLAP или колоночное хранение данных. Было бы здорово, если бы далее шло много подобной информации про одно из направлений, описанных вами:
    — OLAP в целом и подробнее про его отличия от OLTP, больше примеров с пояснениями, к каким плюсам приводит использование хранения данных колонками (раз аналитика присутствует даже в названии, значит дело не только в сжатии),
    — как решаются проблемы (при сжатии, как вы описали, отбирать данные по условиям вроде должно получаться сильно медленее)
    — InMemory: а если не получилось уложиться в память, когда стоит ли хранить данные в колонках, а когда нет
    — может быть расскажете подробнее про конкретные реализации: ROLAP, SAP, Google, PowerBI/кубы (или последние два это одно и тоже?).

    Вы явно имеете опыт, поделитесь :)
    Спасибо.
      0
      Как раз недавно вышел подкаст про базы данных, где подробно рассказывают о том, что такое OLAP и OLTP. Советую всем интересующимся

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое