Представим себе картину из идеального мира данных, в котором всё стабильно, изменений нет и на горизонте не предвидятся. Аналитик полностью согласовал с заказчиком требования к витрине, спроектировал решение и передал в разработку. Разработчики внедрили витрину в продуктивный контур, пользователи счастливы, всё работает корректно — сопровождение разработчиков и аналитиков не требуется. Представили?
Но, как мы знаем, «IT» и «изменения» — синонимы, поэтому в идеальном мире, как гром среди ясного неба, появляются новые требования: разработать инструмент для регулярного добавления в витрину данных новых атрибутов, на текущий момент в неизвестном количестве.
Сразу отмечу, что решения и оценки, о которых пойдёт речь, подбирались для работы с большими данными на стеке технологий Apache Hadoop, где для обработки данных использовали фреймворк Apache Spark, СУБД — Apache Hive для анализа данных, оркестратор — AIRFLOW, данные хранятся в колоночном формате Parquet.
Ключевыми сущностями проекта являются витрины данных, причём больших данных. Таблицы содержат сотни гигабайтов, а атрибутивный состав таблиц достигает 150 штук.
В SQL изменение существующих записей и структуры таблиц в БД является фундаментальной операцией. Однако при работе с большими данными в Hadoop операция по изменению записей (update) отсутствует, для изменения ранее записанных данных приходится прилагать усилия, перезаписывая полностью файлы Parquet: сначала Spark читает в dataframe (select) и изменяет данные (filter/union/...), потом либо записывает во временную таблицу (write), либо кеширует dataframe (cache), и в конце перезаписывает таблицы или партиции (insertInto с параметром overwite. То есть, вместо одной операции update выполняется пять.
Для изменения структуры таблицы в Hadoop чаще всего выполняются аналогичные действия, только после шага write/сache таблица перезаписывается вместе со схемой ( saveAsTable с параметром overwite). Этот метод подходит для небольших таблиц. А когда объёмы достигают сотни гигабайтов, перезапись становится ресурсозатратной и приходится задумываться о более оптимальном варианте изменения структуры таблицы.
Я рассмотрю методы расширения атрибутивного состава таблиц БД, а также дам оценку методов, исходя из особенной, сложившейся в нашем проекте архитектуры БД и технологического стека.
Решение 1. Добавление атрибутов методом EAV (Entity-Attribute-Value)
Модель EAV предназначена для хранения данных, когда структура БД заранее плохо известна и невозможно определить конечное количество сущностей и атрибутов таблиц. В рамках модели добавление новых атрибутов или сущностей не влечёт за собой изменение структуры основных таблиц, но через идентификаторы необходимо сохранять ссылочную целостность каждой сущности.
В проекте уже была создана табличная структура с фиксированными атрибутами. Сущности EAV — attribute и value — объединены в одной таблице, поэтому необходимо было модифицировать классический EAV‑метод. Также учитывалось, что в Hive операция join является ресурсозатратной и замедляет выполнение запросов, особенно при работе с большими объёмами данных. Размер таблиц достигал сотен гигабайтов, что требовало сократить количество взаимосвязей между ними.
Мы разработали единую динамическую таблицу, объединяющую все три сущности классической модели EAV. Состав таблицы включал в себя 8 атрибутов:
№ | Атрибут | Тип атрибута | Наименование |
1 | data_id | string | ИД среза данных |
2 | id | string | ИД записи |
3 | object_name | string | Наименование сущности — таблицы |
4 | attr_name | string | Наименование атрибута |
5 | attr_string | string | Значение атрибута формата строки |
6 | attr_int | integer | Значение атрибута формата целого числа |
7 | attr_decimal | decimal(36,6) | Значение атрибута формата нецелого числа |
8 | attr_date | date | Значение атрибута формата даты |
При этом в динамическую таблицу записывались только новые атрибуты, запись «старых» продолжалась в ранее созданные таблицы.
В таблице соблюдались требования, согласно которым атрибуты должны иметь четыре возможных типа данных. При записи данных значения атрибута складывались в поле соответствующего ему типа attr_string, attr_int, attr_decimal или attr_date, и при чтении атрибут в dataframe создавался в соответствии с типом поля, из которого был взят.
При создании полноценной линейной таблицы динамическая таблица транспонировалась и объединялась (join) с ранее созданной таблицей по идентификаторам data_id и id. Обе таблицы были партиционированы по атрибуту data_id.
Запросы строились через партиции. Объединение (join) происходило именно по партициям, что значительно сокращало время выполнения операций. Перед объединением использовалась фильтрация динамической таблицы по object_name, что позволяло исключить данные сущностей, не участвующих в объединении. Также для оптимизации количества данных динамической таблицы атрибуты с null‑значениями не записывались.
Основные преимущества решения:
Данные хранятся в едином месте.
«Старые» таблицы не разрастаются, остаются в текущем виде.
При добавлении новых сущностей структура динамической таблицы не изменяется.
Динамическая таблица может хранить неограниченное количество сущностей.
Основные недостатки решения:
Разрастание динамической таблицы по формуле (количество добавленных записей в основной таблице * количество новых атрибутов).
Необходимость транспонировать динамическую таблицу для чтения данных, и при этом схема таблицы должна быть всегда актуальна.
Необходимость транспонировать данные в вертикальную структуру для записи в динамическую таблицу, схему также необходимо учитывать.
Для пользовательской работы с таблицей необходимо создавать view.
Решение 2. Добавление атрибута типа JSON в таблицы
Формат JSON не поддерживается в Hive, поэтому атрибут создается с типом данных string, но по структуре напоминает JSON. В Hive тип данных string не имеет ограничения по количеству символов, при этом, максимальный объём данных поля string составляет 2 Гб (2 147 483 647 байтов).
Добавление атрибута типа JSON в DDL таблиц происходит единожды функцией alter_table через метаданные Hive. При обращении к ранее записанным данным атрибут JSON заполняется значением null.
jsonНовые атрибуты в json_attr151 записывались таким образом:
[
{
"attr_name": "id",
"attr_value": "1",
"attr_type": "string"
},
{
"attr_name": "cost",
"attr_value": "100",
"attr_type": "int"
}
…
]Value в json записываются в унифицированном формате string, и уже после чтения преобразуются в формат, указанный в attr_type. Наименование атрибута содержит поле attr_name, а значение атрибута — поле attr_value.
Такой формат хранения универсальный и высокопроизводительный, нет необходимости использовать дополнительные соединения таблиц (join). Для чтения и записи новых атрибутов достаточно использовать JSON‑схему, содержащую актуальный набор дополнительных атрибутов, хранящихся в json.
Основные преимущества решения:
Поле типа
jsonобладает большой вместимостью и не требует больших ресурсов хранения.Хранение в
jsonатрибутов с одинаковыми значениями сжимает таблицы и оптимизирует ресурсы кластера.Отсутствуют дополнительные соединения таблиц.
В структуру таблиц добавляется только один полноценный атрибут.
Основные недостатки решения:
Необходимо поддерживать актуальную JSON‑схему для чтения и записи новых атрибутов.
Сложность в алгоритмах работы с JSON.
Для пользовательской работы с таблицей необходимо создавать view.
Появление атрибутов с уникальными значениями негативно влияет на сжатие данных.
Решение 3. Добавление атрибутов командой alter table cascade
В Hive команда alter table cascade используется для изменения схемы таблицы, например добавления нового столбца, изменения имени столбца, переименования таблицы и т. д. Изменения затрагивают метаданные таблицы, при этом сама структура данных в HDFS остаётся неизменной. Во время чтения или записи расхождение данных и метаданных таблицы приведёт к ошибке. С такими ограничениями операции по удалению и переименованию атрибутов становятся ресурсозатратными, вслед за изменением метаданных таблицы требуется полностью переписать данные под новую структуру. Перезапись сотен гигабайтов требует больших вычислительных ресурсов кластера и занимает продолжительное время, поэтому мы решили только добавлять атрибуты в БД. После добавления новых атрибутов в метаданные, во время чтения таблицы ранее записанные строки заполняются null‑значениями и не вызывают ошибок.
В Hive нет жёсткого ограничения на количество атрибутов в таблице, однако архитектурные паттерны допускают максимально доступное расширение до 5000 полей.
alter table cascade через метаданные Hive (атрибут attr151)Для последующих вычислений с помощью Spark необходимо произвести аналогичную операцию по добавлению атрибутов, потому что Spark не видит метаданных Hive и завершает расчеты с ошибкой.
Основные преимущества решения:
Новые атрибуты можно добавлять вручную, без использования алгоритмов.
Можно добавлять большое количество атрибутов без значительной потери производительности.
Чтение и запись в таблицу осуществляются простыми функциями select и insert.
Таблица имеет линейную структуру, удобную для работы пользователей.
Не требуется создавать view.
Основные недостатки решения: таблица увеличивается в ширину — становится визуально сложно читать данные.
Тестирование решений
Для анализа увеличения объёмов данных таблицы от добавления новых атрибутов мы создали таблицу table, данные в которой по содержанию и структуре аналогичны промышленным. Состав таблицы — 630 тыс. записей и 122 атрибута, объём — 126 Мб (одна партиция). Расширили таблицу сотней атрибутов по двум сценариям: с уникальными значениями и с повторяющимися значениями.
EAV
Как показано на рисунке 2, дополнительно создавали динамическую таблицу dynamic_table. Идентификатор Data_id в обеих таблицах имел одно значение, обозначающее срез данных. Идентификатор ID был уникальным, то есть всего было 630 тыс. уникальных записей.
В первом сценарии уникальные значения записывались в attr_string для всех 100 новых атрибутов. Размер таблицы составил примерно 363 мегабайта. Для каждого нового атрибута в динамической таблице создавалось 650 тыс. записей. В результате в таблице набралось 65 млн записей.
Для реализации второго сценария мы случайным образом отобрали сто атрибутов из таблицы table. Они включали в себя уникальные идентификаторы, продуктовые разметки и пользовательскую информацию, то есть полное разнообразие атрибутов, ожидаемое при реальном расширении атрибутивного состава. Новые атрибуты распределили по соответствующим их типам данных атрибутам динамической таблицы attr_string, attr_int, attr_decimal и attr_date. Записи с null‑значениями исключали из таблицы, и в результате размер её составил примерно 213 мегабайтов.
JSON
Командой alter table cascade добавили поле типа json и наполнили его сотней новых атрибутов.
В первом сценарии размер таблицы увеличился примерно на 160 килобайтов.
Во втором сценарии размер таблицы увеличился примерно на 58 мегабайтов.
Для реализации сценариев мы алгоритмически записали сто новых атрибутов строкой в поле json. Value json в соответствии с примером заполняли наименованием атрибута — attr_name, значением атрибута — attr_value, и типом данных атрибута — attr_type.
В первом сценарии произошло сжатие данных формата Parquet, колонки с одинаковыми значениями схлопнулись и сократили объём затраченных ресурсов в 370 раз в по сравнению со вторым сценарием.
Alter table
В таблицу table добавили сто атрибутов методом alter table. Для начала заполнили их уникальными значениями по первому сценарию. Размер таблицы увеличился на 529 Мб новых данных.
Для реализации второго сценария создали view основной таблицы, случайным образом отобрали сто атрибутов и объединили (join) по уникальному идентификатору с основной таблицей. Эти новые атрибуты содержали уникальные идентификаторы, продуктовые разметки и пользовательскую информацию, то есть полное разнообразие атрибутов, ожидаемое при реальном расширении атрибутивного состава. В результате, с учётом сжатия Parquet, размер таблицы увеличился на 101 мегабайт.
Результаты увеличения объема данных в таблицах:
EAV | JSON | Alter table cascade | |
Повторяющиеся значения | 213 Мб | 160 Кб | 101 Мб |
Уникальные значения | 363 Мб | 58 Мб | 529 Мб |
Тестирование метода alter table cascade показало среднюю ресурсозатратность на хранение данных, эффект сжатия формата Parquet оптимизировал объём данных примерно в 5 раз.
Самую высокую ресурсозатратность на хранения данных показал метод EAV, динамическая таблица разрослась до 65 млн записей. Формат Parquet сжал атрибуты Deal_ID (650 тыс. уникальных записей), ID, object_name (1 уникальная строка), attr_name (сто уникальных строк) в обоих сценариях. Сжатие атрибутов attr_string, attr_int, attr_decimal и attr_date произошло только во втором сценарии, за счёт повторяющихся значений объём хранения данных уменьшился в 1,7 раза.
Метод json показал самую низкую ресурсозатратность на хранение данных. Также это оказался наиболее оптимальный формат для хранения атрибутов с повторяющимися значениями, например, справочной информации или продуктовой разметки. Однако из-за расширения количества атрибутов вероятность появления уникальных строк в методе json выше, чем в классическом методе alter table. Такая динамика негативно повлияет на степень сжатия данных.
Оценка
Для оценки наиболее подходящего метода мы отобрали метрики, учитывающие ключевые особенности проекта:
Оптимизация объема хранимых данных. Ресурсы кластера никогда не бывают безграничны, поэтому необходимо было заранее учесть, какой объём данных может занять добавление, например, ста атрибутов, и при этом рассмотреть возможность оптимизации ресурсов.
Сложность чтения данных. Операции чтения, даже с учётом партиционирования, требуют больших вычислительных ресурсов. Усложнение алгоритмов могло привести к увеличению времени пользовательских расчётов.
Сложность записи данных. Данные записывались в таблицы в виде партиций методом прямого insert. Усложнение алгоритмов, аналогично чтению, увеличивало время пользовательских расчётов.
Необходимость создания визуализации типа external table. Таблицы необходимо было использовать в горизонтальном формате для решения аналитических задач.
Сложность работы пользователей с таблицами. При сильном усложнении структуры таблиц появляется необходимость готовить скрипты, выделять время на обучение и поддержку пользователей.
Разрастание таблицы. У Hive нет проблем с хранением нескольких тысяч атрибутов, однако время работы с такой таблицей увеличивается, и при недостатке ресурсов появляются ошибки хранения данных в оперативной памяти.
Алгоритмическая сложность. На таблицах проходили разные расчёты со своими техническими особенностями, поэтому требовалось выбрать решения с возможностью унификации алгоритмов.
Оценивали командой по трёхбалльной шкале:
Оценка | Балл |
Лёгкое/минимальное | 3 |
Среднее | 2 |
Сложное/большое | 1 |
Публично обсуждали со всеми участниками команды и голосованием присвоили метрикам качественную оценку каждого метода. Эту оценку перевели в количественный критерий:
Метод/метрика | Объём хранимых данных | Чтение данных | Запись данных | Создание визуализации | Работа с таблицей | Разрастание DDL таблицы | Алгоритмическая сложность | Результат оценки |
Добавление атрибута типа | Минимальное | Среднее | Среднее | Среднее | Среднее | Среднее | Среднее | 15 |
EAV (Entity-Attribute-Value) | Большое | Сложное | Сложное | Среднее | Сложное | Минимальное | Сложное | 10 |
Добавление командой | Среднее | Легкое | Легкое | Легкое | Легкое | Большое | Легкое | 18 |
Выводы
Результаты исследования показали, что каждый из подходов имеет свои достоинства и недостатки, и допустимо было решить поставленную задачу любым подходом. Однако наиболее оптимальное решение для текущего проекта — использование метода alter table cascade для таблиц с фиксированным количеством атрибутов. Как отмечалось, метод лёгкий в реализации и полностью учитывает потребности пользователей в использовании витрин данных. А какой метод подошёл бы для вашего проекта?
