ADVANTAGES OF COLUMN DATA STORAGE IN DBMS FOR ANALYTICAL INFORMATION SYSTEMS, DATA WAREHOUSES AND DATA MARTS

Nikita Korneev

Undergraduate, Federal State Autonomous Educational Institution of Higher Education "Siberian federal university", Russia, Krasnoyarsk

Аннотация. Один из первых этапов создания любой информационной системы является выбор СУБД для реализации функции хранения и обработки данных. В зависимости от типа решаемых задач, структур хранимых данных, их количества и внутренних взаимосвязей, выбор может сильно колебаться. От выбора платформы СУБД, типа хранения и внутренней организации хранимых данных зависит не только быстродействие программного кода проекта, но и дальнейшие возможности масштабирования, применяемые технические средства и серверные комплексы. Это напрямую связано с возможностями реализации и конечной стоимостью проектов.

Abstract. One of the first steps in creating any information system is the choice of a DBMS to implement the function of storing and processing data. Depending on the type of tasks being solved, the structures of the stored data, their number and internal relationships, the choice can vary greatly. The choice of the DBMS platform, the type of storage and the internal organization of the stored data determine not only the speed of the program code of the project, but also further scalability, the technical means and server complexes used. This is directly related to the implementation possibilities and the final cost of projects.

Ключевые слова: СУБД; Хранилища данных; Витрины данных; Аналитические информационные системы; Колоночное хранение.

Keywords: DBMS; Data Warehouse; Data Marts; Analytical Information Systems; Column Storage.

1.Введение

Рыночные отношения на современном этапе развития отличаются бурным ростом цифровых технологий во всех сферах бизнеса и общественных отношений. Еще 20-30 лет назад на создание и внедрение новых технологий и производств уходили годы, а сейчас на внедрение нового уходят считанные месяцы: от идеи до массовых продаж по всему миру. Глобальные социальные сети, технологические гиганты и финансовые институты способствуют этим процессам и человеческий фактор становится одной из ключевых проблем в этой гонке на выживание. Тысячи компаний и технологических стартапов появляются на рынке для того, чтобы дать что-то новое миру и исчезнуть по причине жёсткой конкуренции на этом рынке.

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

Создание внутренних современных аналитических систем для поддержки управленческого учёта в компании – это обязательный процесс для любого крупного бизнеса, кроме того, достаточно важно получить этот инструмент как можно раньше, чтобы сделать управление наиболее эффективным на этапах становления бизнеса или отдельных его направлений.

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

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

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

2.Современное положение на рынке бизнес-аналитики

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

Реальные данные по архитектуре существующих систем чаще всего публикуются только в документации к системе, либо в аналитических источниках в сети Интернет.

Компания Gartner [1], одна из ведущих консалтинговых и исследовательских компаний мира, опубликовала собственное схематичное представление о составе и иерархии систем построения аналитики бизнеса – analytical stack, которое часто называют «аналитическая пирамида».

Рисунок 1. Аналитическая пирамида

В основании пирамиды находятся транзакционные системы – это класс систем, предназначенный для отражения текущих операций бизнеса, например, системы бухгалтерского или налогового учёта, учётные системы управления персоналом и мероприятиями, логистики и учёта материально-технических ресурсов. Ключевая характеристика данного типа систем состоит в том, что они собирают наиболее полную объективную информацию по происходящим в компании производственным бизнес-процессам.

У крупных компаний для этих целей используются ERP-системы корпоративного уровня: «1С: ERP», «Business Central by Navicon», «SAP», «Галактика ERP», «Lexema-ERP» [2].

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

Системы управления базами данных, используемые в системах, относятся к реляционным СУБД верхнего, наиболее развитого, технологического сегмента: Oracle Database, Microsoft SQL Server, IBM DB2 Database, PostgreSQL Pro, Pervasive PSQL. Все перечисленные СУБД имеют высокие показатели в части надёжности, отказоустойчивости и масштабируемости (кластеризация), предназначены для корпоративного рынка и больших высоконагруженных систем.

Для малого и среднего бизнеса такие решения не по карману, поэтому там чаще используются отдельные транзакционные системы для разных функций, такие как: «Инфо-Бухгалтер», «1С-Бухгалтерия», «БЭСТ-Бухгалтерия», «Парус-Бухгалтерия». Подобные системы есть у крупных российских производителей ПО для Материального учёта, Управления кадрами, Бюджетирование и т.д. В указанных системах используются менее мощные локальные файловые СУБД DBASE, либо малые выделенные сервера: Microsoft SQL Server, Oracle Database.

Нижний транзакционный уровень обработки информации традиционно обозначают OLTP – On-Line Transaction Processing – Обработка транзакций в режиме реального времени. На этом уровне у систем тоже есть небольшие возможности для построения аналитических отчётов, однако эти возможности сильно ограничены. По мере роста количества данных в транзакционных системах существенно падает скорость формирования аналитических отчётов и создаёт большую нагрузку на СУБД. Кроме этого, для построения аналитических систем часто недостаточно данных из транзакционных системы, требуются внешние источники информации. По этой и по некоторым другим причинам в профессиональном сообществе не рекомендуется использовать транзакционные СУБД и системы для построения аналитик.

В аналитической пирамиде слева и справа стрелками обозначены переносы данных с нижестоящих уровней на вышестоящие. Чаще всего эти данные переносятся в агрегированном виде, предварительно готовом для построения аналитики. Стрелка слева «BYPASS» означает, что OLAP-системы и аналитические приложения могут получать данные непосредственно с транзакционного уровня, минуя хранилища и витрины данных.

Для малого и среднего бизнеса задачи построения аналитических информационных систем во многом сходны с задачами крупного бизнеса, поскольку крупный бизнес использует в работе как малые аналитические системы, так и системы бизнес-аналитики корпоративного уровня, чтобы обеспечивать аналитику для крупных корпоративных систем. Широко используются возможности Microsoft Excel, поскольку платформа обеспечивает как OLAP-хранилище малого размера и построение аналитики, так и уровень представления данных для внешних аналитических систем, например: SAP BI.

Из бесплатного аналитического ПО можно выделить следующие часто используемые системы: Tableau (сайт: www.tableau.com), Microsoft Power BI (существует бесплатная версия), QlikView Personal Edition. Среди платных систем наиболее распространёнными являются: Oracle Business Intelligence, Microsoft Power BI, IBM Cognos Analytics, SAS Visual Analytics, SAP BusinessObjects BI Suite. Системы приведены не столько для описания их возможностей и относительных преимуществ, сколько для выявления тенденций, которые прослеживаются в применении СУБД для эффективной обработки аналитической информации, многомерных кубов данных, витрин данных.

Крупные компании, создатели аналитических систем, такие как SAP, Microsoft, Oracle и IBM используют в качестве хранилищ собственные СУБД: SAP HANA (High performance ANalytics Appliance), Microsoft SQL Server, Oracle Database, DB2 On Cloud. Небольшие компании не имеют такой возможности, поэтому используют адаптеры для подключения к внешним СУБД, что даёт широкие возможности выбора.

Кроме указанных выше имеется множество специализированных СУБД, не являющихся реляционными в классическом понимании, однако успешно себя зарекомендовавших в разработке аналитических систем малого и среднего, а некоторые и глобального уровня: PostgreSQL, HBase ( СУБД от Apache Hadoop), Cassandra ( проект СУБД от Facebook, переданный как OpenSource фонду Apache), Hipertable, CouchDB, Sybase IQ, KDB+, LucidDB [3], MariaDB (клон СУБД MySQL). Более подробно можно посмотреть актуальный перечень аналитических СУБД в следующем источнике [4].

3.Колоночное хранение данных

Внешне кажется, что нет разницы между СУБД транзакционного уровня и СУБД для аналитических систем, однако это не совсем так. Если даже наименование СУБД совпадает, то используются скорее всего аналитические возможности. Дело в том, что крупные производители СУБД существенным образом доработали собственные продукты и теперь кроме реляционной модели данных, те же самые СУБД поддерживают и аналитические возможности и прежде всего – это возможность колоночного хранения данных.

Обычные реляционные СУБД основаны на том, что данные хранятся на диске построчно в виде кортежей полей, как показано на рисунке ниже.

Рисунок 2. Построчное хранение данных

На рисунке показано стрелочками направление увеличения адресации хранимых данных при считывании, конец одной строки совпадает с началом следующей и так дале до конца таблицы. Этот способ записи удобен для построчного считывания всех полей таблицы. Реляционная модель данных нормализует данные таким образом, что по ключевым полям однозначно определяются все остальные атрибуты – не ключевые поля записи. Поэтому в рамках реляционной модели удобнее всего адресовать строки, строить бинарные индексы для поиска строк по значению индексного выражения – в любом случае происходит построчное считывание.

Однако предположим, что для работы наших запросов к СУБД требуется обращение только к полям с номерами 5, 6 и 7 – выделены серым, а остальные поля не нужны. При каждом считывании строк с этими полями СУБД вынуждена будет отбрасывать все поля, кроме 5,6 и 7. Но  остальных полей бывает много, а считывание из файлового хранилища СУБД происходит блоками, включающими в себя все поля. Таким образом считывая 10Мб из СУБД, полезной информации может оказаться не более 1Мб, а иногда и меньше. Это снижает эффективность работы СУБД на подобных запросах, и она может не превышать 5-10% от расчётной мощности, что ведёт к нерациональному использованию оборудования, повышенным потребностям к скорости закупаемого серверного оборудования – то есть к дополнительной стоимости проекта.

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

Для решения проблемы строкового хранения данных в СУБД был реализован механизм колоночного хранения.

Рисунок 3. Колоночное хранение данных

При таком способе хранения столбцы таблиц хранятся отдельно и могут считываться по направлению стрелок. Запись данных в такие таблицы производится больше, чем с строчном варианте, однако такие таблицы имеют ряд существенных преимуществ как раз для аналитических вычислений. Если мы сделаем запрос к СУБД чтобы выбрать только колонки 5, 6 и 7, то в отличие от варианта описанного выше мы имеем возможность считать целиком все 3 поля поочерёдно и при считывании не возникнет ненужной информации – считается ровно то, что было запрошено. Это говорит о высокой эффективности СУБД с колоночным чтением для подобных запросов.

В отличие от традиционных реляционных СУБД основная нагрузка и соответственно оптимизация работы аналитических СУБД в расчёте на непредвиденное чтение больших объемов исторических данных по отдельным показателям. Одной из первых реализаций колоночной СУБД, оптимизированной под такую нагрузку, была разработана в 1993-95 годах является Sybase IQ от компании Expressway Technologies [5].

В среднем выигрыш в скорости выполнения запросов от смены механизма хранения данных может сильно варьироваться и очень зависит от размеров таблиц в СУБД. Дело в том, что для того, чтобы считать одно поле целиком из всей таблицы при строковом хранении придётся считать всю таблицу и выбрать из неё только одно поле. Когда речь идёт о таблице в 10 записей, задача не представляется сложной или затратной по времени и ресурсам сервера. Если же таблица содержит 100 млн записей, задача становится совсем не простой, а главное неэффективность строкового хранения на такой операции станет более чем очевидна.

Существует ещё один немаловажный фактор, способствующий наиболее оптимальной обработке колоночных данных, это однородность данных, хранимых в одной колонке, что позволяет эффективно использовать архивацию данных колонки при хранении в СУБД как In-memory, так и обычном дисковом хранении. Это существенно увеличивает скорость считывания данных с дисковых устройств и экономит оперативную память сервера СУБД. Такая возможность при строчном хранении отсутствует [6].

При это вполне очевидно, что колоночные СУБД не обязательно поддерживают все возможности реляционных. Ориентировка аналитических СУБД на создание хранилищ и витрин данных большого объёма накладывает существенные ограничения на функции обеспечения структурированности и целостности хранимых данных. Целостность данных обычно проверяется на уровне ПО аналитической информационной системы или вручную. С точки зрения ведения разработки, колоночные СУБД преимущественно поддерживают стандарт SQL-99.

Чаще всего в таких системах медленнее работают операции записи данных в СУБД, но таких операций из общего потока операций обычно очень мало – менее 1% и это не сказывается на общей скорости и доступности данных на чтение. Это очень важный показатель, поскольку объемы хранилищ данных могут быть чрезвычайно большими — есть примеры по 300-500ТБ и даже случаи с >1ПБ данных.

Чтение и обработка данных из аналитических систем производится большими блоками данных, и чтобы при этом не страдала скорость выборки, обработка производится параллельно из различных частей СУБД, участвующих в построении аналитики. Эта технология получила название MPP – massive parallel processing. В отдельных случаях анализу подвергается вся накопленная информация в аналитической СУБД и такие задачи вполне выполнимы и эффективно решаются благодаря современным технологиям оптимизации хранилищ данных.

Практически все платформы аналитических СУБД поддерживают кластеризацию и распределённое хранение данных, что позволяет создавать хранилища большого размера с географически распределёнными серверами и данными, которые функционируют как единое целое. Благодаря технологии MPP возможно горизонтальное масштабирование до сотен узлов, что существенно увеличивает производительность создаваемого хранилища и позволяет работать с BigData. Также поддерживаются технологии вертикального и горизонтального партицирования, шардинга и репликации.

Важным классом аналитических СУБД являются системы управления базами данных in-memory [7]. Данная технология позволяет позволяет хранить и обрабатывать все данные в оперативной памяти, тем самым убрать операции ввода/вывода, которые отнимают большую часть времени при операциях над данными у традиционных СУБД. Скорость чтения в СУБД in-memory возрастает в 4 раза, а скорость записи в 420 раз. Наиболее яркими представителями СУБД, использующими данную технологию, являются SAP HANA, VoltDB. Также в последние годы эту технологию стали поддерживать и СУБД, традиционно работавшие с дисковым хранилищем СУБД – MemSQL,  SQLite. К аналитическим СУБД in-memory относятся SAP HANA и MemSQL.

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

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

4. Базовые рекомендации по выбору СУБД

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

Прежде всего, необходимо определить масштаб создаваемой системы. Для этого необходимо произвести расчёт объёмов хранимых данных с учётом скорости роста хранилища – поступления данных с транзакционного уровня и внешних систем. Если объемы хранимых данных менее 100Мб и годовой прирост данных менее 10Мб, вполне возможно для их обработки не требуется аналитическая СУБД даже малого размера. Можно обойтись обычной транзакционной СУБД MySQL или MS SQL Server, либо для реализации можно использовать аналитические СУБД малого класса MariaDB или LucidDB (в случае, если не требуется поддержка транзакционных возможностей).

 В случае, если объёмы СУБД превышают 100Гб и выше, для построения аналитической информационной системы необходимо использовать колоночные СУБД среднего и корпоративного уровня: PostgreSQL, HBase, Cassandra, Hipertable, CouchDB, Sybase IQ, SAP HANA, KDB+, IBM DB2, Oracle Database. Использование обычных реляционных СУБД без аналитических расширений и колоночного хранения данных при таких объёмах неэффективно.

Вторым немаловажным показателем для определения выбора СУБД служит максимальное время отклика аналитической системы при расчёте каждого из показателей, а также критичность выполнения данного показателя при последовательных выборках. Реляционные СУБД могут обеспечивать хорошее время отклика только на небольших объемах выборки. Например, выдавать за 1 секунду аналитический отчёт, который обрабатывает порядка 1 млн записей. Если же речь идёт об обработке более 1 млн записей со сложной аналитикой и сроках выдачи аналитики в пределах 0,5 сек и менее, то рекомендуется применять именно колоночные СУБД. Для увеличения скорости работы потребуется использовать возможности кластеризации и технологии MPP – горизонтальное масштабирование.

В случае нехватки расчётного быстродействия аналитической системы или объёмов хранилища, есть возможность обратить внимание на аналитические системы с технологией in-memory. За счёт отсутствия операций ввода/вывода быстродействие таких систем в разы превосходит аналоги. Для этого потребуется покупка серверов с большим объёмом оперативной памяти – более 8Tb и выше, а также приобретение SAP HANA в качестве аналитической СУБД. Сервера SAP HANA оптимизированы под обработку десятков миллиардов записей в режиме online, имеют множество внедрений по всему миру, активно развиваются и хорошо документированы. Покупку подобных комплексов может себе позволить только средний и крупный бизнес.

Для малого бизнеса возможно использование бюджетного варианта на MemSQL и в данном случае это не означает серьёзных ограничений по скорости работы или возможностям построить полноценную аналитическую информационную систему.

Когда создаётся аналитическая система, часто требуется рассматривать варианты её развития на годы вперёд. Объём хранимых данных обычно только увеличивается, в среднем на 10-20% в год, необходимо определить рамки роста СУБД и механизмы архивации данных.

Кроме того, по мере развития аналитической системы появляются новые пожелания заказчика в части разработки дополнительных аналитических приложений, витрин данных и т.д., что ещё более увеличивает размеры хранилища в перспективе.

Это означает, что на раннем этапе важно предусмотреть запас объёмов хранилища данных и возможности горизонтального и вертикального масштабирования. Не все СУБД одинаково легко масштабируются, необходимо выбрать наиболее подходящий вариант в соответствии с потребностями бизнеса и написать в план проекта.

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

5. Заключение

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

Одной из ключевых целей данной работы является выработка рекомендаций для осуществления обоснованного выбора СУБД для аналитической системы с акцентом на колоночное хранение данных, как наиболее эффективного, но не единственного механизма хранения данных. Именно колоночные СУБД позволяют увеличить скорость обработки данных в десятки раз, а также оптимизировать объёмы хранимых данных в СУБД за счёт использования компрессии колонок. На ограниченных объёмах СУБД особенно эффективны хранилища с технологией In-memory.

В работе были рассмотрены различные аспекты и технологии, влияющие на выбор того или иного класса СУБД, даны соответствующие рекомендации по модели хранения данных.

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