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

---

Итак, что же такое Hydra? Это аналитическая СУБД с открытым исходным кодом, который вы можете найти на GitHub. Hydra разработана на основе другой очень популярной транзакционной СУБД, PostgreSQL, что позволяет ей быть более универсальной по сравнению с прочими СУБД, созданными для аналитики данных. Но как такое возможно? Фундаментальное различие между аналитической и транзакционной СУБД заключается в том, что первая хранит данные каждого поля в отдельном файле, а вторая — хранит данные нескольких записей в одном файле. С Hydra все немного интереснее.

Создатели Hydra предлагают облачные решения на любой вкус в зависимости от ваших потребностей.

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

Особенности хранения данных

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

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

В одной базе данных могут быть таблицы с разными вариантами хранения данных, каждая под свою задачу.
Например, основная таблица с действиями клиентов может хранить данные построчно и быть обычной PostgreSQL таблицей, а рядом с ней, на том же сервере, может быть такая же таблица с колоночным хранилищем, которая используется аналитиками с их тяжелыми запросами на чтение. А комбинирование различных типов таблиц в одном запросе может поражать воображение!

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

Вот так будет выглядеть скрипт создания стандартной таблицы из PostgreSQL со строковым хранением. Этот же скрипт подойдет и для Hydra.

CREATE TABLE uk_price_paid_row (
    price INT,
    date DATE,
    postcode1 TEXT,
    postcode2 TEXT,
    TYPE SMALLINT,
    is_new SMALLINT,
    duration SMALLINT,
    addr1 TEXT,
    addr2 TEXT,
    street TEXT,
    locality TEXT,
    town TEXT,
    district TEXT,
    county TEXT
);

Для того, чтобы такая таблица стала хранить данные по столбцам, достаточно добавить всего лишь одну строчку в конце скрипта:

CREATE TABLE uk_price_paid_col (
    price INT,
    date DATE,
    postcode1 TEXT,
    postcode2 TEXT,
    TYPE SMALLINT,
    is_new SMALLINT,
    duration SMALLINT,
    addr1 TEXT,
    addr2 TEXT,
    street TEXT,
    locality TEXT,
    town TEXT,
    district TEXT,
    county TEXT
) USING columnar;

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

В англоязычных источниках транзакционные задачи обычно описываются таким термином, как «OLTP нагрузка» (OLTP — аббревиатура, которая расшифровывается как OnLine Transaction Processing). С другой стороны, решение аналитических задач характеризуется OLAP нагрузкой (OnLine Analytical Processing). Зачастую каждая СУБД выбирается под свою нагрузку, но Hydra отличается тем, что предназначена как для OLTP, так и для OLAP нагрузки. Такое смешение двух типов нагрузки породило термин «HTAP нагрузки» (Hybrid Transaction/Analytical Processing) — как раз под такую нагрузку и была разработана Hydra.

Партиционирование

Еще одной очень интересной функцией таблиц Hydra является партиционирование, то есть разделение таблицы на несколько логически независимых друг от друга кусков. Партиционирование — это логическое разбиение всех записей таблицы на группы. Это очень удобный функционал для работы сразу с большим количеством строк.

В Hydra партиционирование концептуально похоже на партиционирование таблицы из PostgreSQL. При создании таблицы необходимо указать поле, по значениям которого будет разделение на партиции, а также тип разделения; подробнее можете узнать в русскоязычной документации к  документации к PostgreSQL. Давайте при создании нашей таблицы из предыдущего примера укажем партиционирование по месяцам продажи дома, тогда наш скрипт будет выглядеть так:

CREATE TABLE uk_price_paid (
    price INT,
    date DATE,
    postcode1 TEXT,
    postcode2 TEXT,
    TYPE SMALLINT,
    is_new SMALLINT,
    duration SMALLINT,
    addr1 TEXT,
    addr2 TEXT,
    street TEXT,
    locality TEXT,
    town TEXT,
    district TEXT,
    county TEXT
) PARTITION BY RANGE(date);

Мы намеренно не указывали в скрипте тип хранения данных таблицы, об этом позже. В одной партиции будут храниться данные за определенный промежуток значений поля date. Нам бы хотелось хранить данные за каждый год в своей партиции. Далее нам необходимо создать каждую партицию вручную, с указанием границ поля date; например, вот так выглядел бы скрипт создания партиции для записей 1995 года:

CREATE TABLE uk_1995 PARTITION OF uk_price_paid 
FOR VALUES FROM ('1995-01-01') TO ('1996-01-01');

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

CREATE TABLE uk_1995 PARTITION OF uk_price_paid 
FOR VALUES FROM ('1995-01-01') TO ('1996-01-01')
USING COLUMNAR;

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

То есть указывать в изначальной таблице способ хранения не нужно, ведь у каждой партиции он будет свой.

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

Обновление и удаление данных

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

Сейчас процесс удаления данных реализован следующим образом: при удалении из таблицы запросом DELETE запись помечается просто удаленной и не будет прочитана при следующем SELECT запросе. Физическое удаление произойдет при вызове запроса на сборку мусора VACUUM FULL, который хорошо известен пользователям PostgreSQL PostgreSQL. Это рабочий способ, но малоэффективный, потому что при вызове сборщика мусора с таким параметром произойдет полная блокировка таблицы.

Процесс обновления записей в таблице при колоночном хранении мало отличается от удаления. Запрос UPDATE помечает каждую обновляемую строку как удаленную (как и при удалении данных запросом DELETE), а новые версии записей просто будут дописаны в конце. По сути, запрос UPDATE — это запрос DELETE, за которым следует запрос INSERT.

Если, например, мы захотим обновить цену во второй записи из такого куска таблицы:

То нам придется удалить строку целиком, а потом вставить такую же, но с новым значением поля price:

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

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

Как выполняются аналитические запросы?

Не только колоночное хранение данных позволяет оптимизировать выполнение аналитических запросов. Для выполнения каждого запроса используются ресурсы сразу же нескольких вычислительных ядер процессора, то есть запрос выполняется параллельно.

Давайте разберемся с этим механизмом на примере вот такого простого запроса к нашей таблице uk_price_paid, которая была создана с колоночным хранением:

SELECT
    DATE_PART('year', date) AS year,
    ROUND(AVG(price)) AS avg_price
FROM
    uk_price_paid
GROUP BY
    year
ORDER BY
    year ASC;

Запрос определяет среднюю стоимость недвижимости за каждый год. Если таблица была предварительно разделена на партиции по году, то агрегатная функция AVG будет рассчитываться одновременно для каждой партиции параллельно.

Данные каждого столбца для вычисления разделяются на векторы, и каждый поток будет обрабатывать свой вектор. Мы обозначили каждый вектор своим цветом (всего на рисунке 6 векторов). Так как таблица была создана с партиционированием по годам, то скорее всего вычисление каждого столбца изначально будет разделено между потоками по партициям. Если в распоряжении Hydra потоков будет больше, чем партиций, то файлы столбцов будут разделены на еще бОльшее число векторов, тем самым увеличивая параллельность выполнения запроса. Такой алгоритм выполнения запроса сильно уменьшает время отклика, а соответственно, увеличивает время выполнения запроса.

Для сравнения: в PostgreSQL такой запрос выполнялся бы в один поток, который бы читал все строки (так как у PostgreSQL строковый тип хранения данных) и делал вычисление среднего арифметического каждого года последовательно.

Источники данных

В Hydra также есть средства для интеграции с другими СУБД, такими как MySQL и PostgreSQL. Эти СУБД могут быть источником данных для таблиц Hydra. Также источником могут служить данные, хранящиеся в S3 и в Google Sheets.

Для каждого способа есть своя инструкция по интеграции, но все они очень сильно похожи друг на друга, так как реализованы при помощи модуля обертки для внешнего источника данных, например, для интеграции с PostgreSQL используется модуль postgres_fdw.

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

Испытание запросами

Давайте наконец посмотрим, на что способна Hydra в деле. Мы потестим ее производительность на тестовых данных о продаже жилья в Великобритании с 1995 года. Структуру таблицы мы приводили ранее. Наша таблица будет поделена на партиции по году, каждая партиция при этом содержит данные по колонкам. Всего в таблице 28 миллионов записей.

Ну и встречайте сами запросы!

1. Посчитать общее число размещенных объявлений за все время.

SELECT COUNT(*)
FROM uk_price_paid;

2. Найти среднюю цену за каждый год.

SELECT
DATE_PART('year', date) AS year,
ROUND(AVG(price)) AS avg_price
FROM uk_price_paid
GROUP BY year
ORDER BY year ASC;

3. Найти среднюю цену за каждый год в Лондоне.

SELECT
DATE_PART('year', date) AS year,
ROUND(AVG(price)) AS avg_price
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year ASC;

4. Топ 100 районов городов по средней цене с 2020 года с более чем 100 объявлений.

SELECT
town,
district,
COUNT(*) AS c,
ROUND(AVG(price)) AS avg_price
FROM uk_price_paid
WHERE date >= '2020-01-01'
GROUP BY town, district
HAVING COUNT(*) >= 100
ORDER BY avg_price DESC
LIMIT 100;

5. Вывести все возможные комбинации типов домов и количество объявлений с ними по убыванию их суммарной цены из объявлений.

SELECT
    t.name AS type,
    d.name AS duration,
    COUNT(*) AS c,
    ROUND(SUM(price)) AS sum_price
FROM uk_price_paid AS upp
INNER JOIN type AS t ON upp.type = t.id
INNER JOIN duration AS d ON upp.duration
= d.id
WHERE date >= '2010-01-01'
GROUP BY t.name, d.name
HAVING COUNT(*) >= 100
ORDER BY sum_price DESC;

6. Вывести информацию об улицах с самым дорогим и самым дешевым жильем.

SELECT
    price,
    date,
    postcode1,
    t.name AS type,
    d.name AS duration,
    street,
    locality,
    town,
    district,
    county
FROM uk_price_paid AS upp
INNER JOIN type AS t ON upp.type = t.id
INNER JOIN duration AS d ON upp.duration = d.id
WHERE street IN (
    (SELECT street
    FROM uk_price_paid
    ORDER BY price DESC
    LIMIT 1)
 
    UNION ALL
 
    (SELECT street
    FROM uk_price_paid
    ORDER BY price ASC
    LIMIT 1)
)
ORDER BY price DESC;

Время выполнения запросов мы представили в таблице ниже:

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

Заключение

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

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