Денормализация таблиц, оптимизация SQL-запросов, словари вместо измерений и AggregatingMergeTree-движок с инкрементными матпредставлениями для приема измененных данных из PostgreSQL в ClickHouse.
Оптимизация SQL-запросов
Хотя передача изменений из PostgreSQL в ClickHouse может сопровождаться дублированием или потерями данных, эти проблемы решаемы, о чем мы рассказывали здесь и здесь. Однако, репликация данных из реляционной базы с сильной степенью нормализации в колоночное денормализованное хранилище предполагает изменение структуры данных. При том, что в ClickHouse можно выполнять многотабличные запросы благодаря поддержке операций соединения таблиц (JOIN), это считается не рекомендуемым сценарием. Будучи изначально ориентированным на OLAP-сценарии, ClickHouse оптимизирован для агрегаций больших объемов данных и аналитических запросов. Внутренние механизмы этого колоночного хранилища лучше приспособлены для фильтрации и агрегирования, чем для сложных соединений таблиц.
Чтобы оптимизировать соединение таблиц в ClickHouse, можно оптимизировать SQL-запросы к данным:
- использовать для фильтрации данных подзапросы или общие табличные выражения (CTE, Common Table Expressions) – временные результирующие наборы данных, которые определяются внутри оператора WITH и могут использоваться в последующем основном запросе. CTE не просто упрощают написание сложных запросов и улучшают их читаемость, но и позволяют создавать рекурсивные запросы.
- включить столбцы соединения в ключи сортировки таблицы, которые критически важны в ClickHouse, т.к. именно они определяют, как данные сортируются на диске и индексируются.
- использовать словари вместо отдельных таблиц измерений. Словарь в ClickHouse – это примитивная структура данных ключ-значение, которая полностью или частично хранится в оперативной памяти сервера. Словари работают намного быстрее запросов с JOIN-операторами.
- выбрать наиболее подходящий алгоритм JOIN в зависимости от потребления памяти, производительности и типа соединения. По умолчанию ClickHouse использует прямой или хэш-алгоритм соединения. В качестве альтернативы можно настроить ClickHouse на адаптивный выбор и динамическое изменение алгоритма соединения во время выполнения в зависимости от доступности и использования ресурсов. Например, если нужно сократить потребление памяти, используется алгоритм partial_merge, а когда надо повысить производительность запроса, лучше подойдет алгоритм parallel_hash.Подробнее об этом поговорим в новой статье.
Помимо перечисленных способов оптимизации SQL-запросов, можно денормализовать данные, поскольку структура таблиц в колоночном ClickHouse будет значительно отличаться от нормализованной схемы в PostgreSQL.
Денормализация схемы данных
Денормализация данных направлена на исключение соединений за счет преобразования структуры данных, когда измерения вместо представления в виде отдельных таблиц становятся атрибутами. В нормализованных схемах данные разбиваются на несколько связанных таблиц для минимизации избыточности и обеспечения целостности. Денормализация повторно вводит избыточность путем объединения таблиц, дублирования данных и включения вычисляемых полей в таблицу. Это снижает потребность в сложных соединениях во время запроса и может значительно ускорить операции чтения. Хотя такой прием подходит для приложений с высокими требованиями к быстрому чтению и сложными запросами, он повышает сложность операций записи. Однако, главным недостатком денормализации является удорожание эксплуатации, поскольку любые изменения в данных, дублированных в виде атрибутов в нескольких местах, должны немедленно распространяться для обеспечения целостности.
Обеспечить целостность денормализованных данных можно следующими способами:
- с помощью триггеров и триггерных функций;
- сохранение статистики и связанных строк в родительской строке в виде столбцов и вложенных объектов. Здесь можно использовать сложные типы данных, например, массивы Array(Tuple), JSON или Nested — массив именованных кортежей, где каждая запись представляет объект.
При денормализации отношения «один к одному» с небольшим количеством столбцов их можно добавить в качестве строк с их исходными типами. Но это не очень подходит для больших объектов и невозможно для отношений «один ко многим». В этих случаях можно использовать именованные кортежи, которые позволяют представить связанную структуру в виде набора столбцов.
В целом рекомендуется проводить денормализацию для таблиц, которые изменяются редко или для них допустима задержка доступности данных для аналитических запросов (т.е. данные можно полностью перезагрузить пакетом). При отношении «многие ко многим» следует избегать денормализации, чтобы не обновлять много строк при изменении одной исходной. Также вместо денормализации отношений с высокой кардинальностью лучше использовать массивы или кортежи. Причем делать более 1000 кортежей в массиве не рекомендуются. Можно не денормализовать все столбцы как вложенные объекты, а сделать это только для статистики с использованием материализованных представлений, что мы рассмотрим далее. Наконец, не нужно денормализовать все данные – достаточно сделать это только для тех данных, к которым требуется частый доступ.
Денормализация усложняет прием данных и предполагает выстраивать ETL-конвейер так, чтобы повторно вставлять строку в ClickHouse при изменении исходной строки. Это может потребовать обновления множества строк, что довольно затратно, и может привести к проблемам. Денормализацию можно выполнить в самом ClickHouse либо до загрузки данных в него, например, с помощью, Apache Flink или Spark. Если данные обновляются часто и задержки в их обновлении недопустимы, следует избегать денормализации. Лучше всего денормализация работает, когда таблица предназначена только для добавления или может периодически перезагружаться в пакетном режиме, например, раз в день. Для периодических пакетных загрузок в ClickHouse можно применить:
- обновляемые или инкрементные материализованные представления, чтобы обеспечить атомарное обновление целевой таблицы;
- внешние инструменты преобразований,например dbt или Airflow, чтобы выполнять трансформацию атомарно с новой версией целевой таблицы, которая транзакционно создается, а потом заменяется через команду EXCHANGE.
И снова о пользе представлений в Clickhouse
Как уже было отмечено, представления полезны и при денормализации данных в ClickHouse. Например, с их помощью можно обеспечить сырую денормализацию – простое выравнивание необработанных данных без преобразования. Альтернативой является агрегированная денормализация – агрегация данных по мере их денормализации и сохранение их в материализованном представлении.
Также распространенной стратегией является агрегирование данных и сохранение результатов в отдельных таблицах с использованием обновляемых материализованных представлений для еще более быстрого доступа к результатам за счет гибкости запросов.
Для сырой денормализации можно использовать инкрементные материализованные представления, которые дают два ключевых преимущества по сравнению с обновляемыми материализованными представлениями:
- запрос выполняется только для вновь вставленных строк, а не сканирует всю исходную таблицу, что особенно важно для больших наборов данных;
- инкрементное материализованное представление обновляется в режиме реального времени по мере вставки новых строк в исходную таблицу, тогда как обновляемое материализованное представление обновляется периодически.
Стоит помнить, что дедупликация не может происходить во время вставки, а запросы к целевой таблице по-прежнему требуют ключевого слова FINAL для обработки дубликатов, о чем мы писали здесь и здесь.
Агрегированная денормализация с использованием инкрементального материализованного представления в ClickHouse может быть реализована с помощью специального табличного движка AggregatingMergeTree. Этот движок относится к семейству MergeTree, но по-другому реализует логику слияния частей данных, заменяя все строки с одним и тем же ключом сортировки в пределах одной части данных на одну строку, которая хранит комбинацию состояний агрегатных функций. AggregatingMergeTree-таблицы подходят для инкрементальной агрегации данных, в том числе для агрегированных материализованных представлений. AggregatingMergeTree также смягчает проблему дублирования результатов при простой агрегации входящих данных, связанных с тем, что каждое обновление в PostgreSQL создает новую строку в ClickHouse.
Простая агрегация входящих данных и сохранение результата в целевой таблице приведет к дублированию подсчетов. А движок AggregatingMergeTree движок позволяет хранить агрегированные состояния функций вместо хранения каждой строки отдельно, предотвращая дублирование данных. В качестве ключа агрегации могут выступать первичные ключи в PostgreSQL. В качестве агрегатных функций при использовании движка AggregatingMergeTree в ClickHouse применяются специальные функции состояния (state functions). Эти функции позволяют хранить промежуточные результаты агрегации, что обеспечивает корректное объединение данных при слиянии частей таблицы. Обычно в качестве агрегатных функций состояния используются следующие:
- sumState – сумма значений для агрегирования итогов по определённому ключу;
- countState — количество записей для получения общего количества элементов в группе;
- minState и maxState — минимальное и максимальное значение в группе;
- avgState — среднее значение;
- anyState – любое одно значение из группы, когда надо сохранить примерное значение без необходимости конкретики;
- quantileState — квантиль, например, медиана или другие процентильные значения, что полезно для анализа распределения данных.
При создании материализованных представлений или агрегированных таблиц важно использовать именно функции состояния, а не их финальные эквиваленты. После агрегации состояния можно преобразовать обратно в финальные значения с помощью соответствующих функций, таких как sumMerge, countMerge и т.д. Это обеспечивает эффективную и корректную инкрементальную агрегацию данных без риска их дублирования.
От чего зависит скорость вставки данных в ClickHouse и как ее повысить, читайте в новой статье.
Освойте ClickHouse на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
- https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips-v2
- https://clickhouse.com/docs/data-modeling/denormalization
- https://clickhouse.com/blog/clickhouse-fully-supports-joins-how-to-choose-the-right-algorithm-part5
- https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree
