Всем привет! На проекте развернул ClickHouse В таблице порядка 25 полей и

400млн записей и количество растет (прирост порядка 270т записей в сутки)
Все бы хорошо, но данные за последний месяц-полтора могут обновляться и обновлений может быть много, что делает апдейт по одной записи невозможным

Что я сделал:
Движок у таблицы сделал ReplacingMergeTree и делаю периодический инсерт (по крону) большими пачками как раз за последние полтора месяца. В таблице появляются дубли и в конце инсерта 50млн записей я делаю OPTIMIZE TABLE table_name FINAL DEDUPLICATE; что удаляет все дубликаты и оставляет последнюю версию по первичному ключу (id)

В чем проблема:
Во время выполнения этого скрипта, мне в запросах на выборку приходится использовать модификатор FINAL для того, чтобы в выборку не попадали дубли (FINAL берет последнюю версию дублей по первичному ключу), а это приводит к тому, что происходит перебор всей таблицы и запрос выполняется порядка 30сек, что на малых выборках - неприлично долго (без FINAL отрабатывает порядка 0.3сек), а на очень больших выборках в целом приемлемо, но все равно очень долго. Я пробовал индексы накидывать - но индексы при модификаторе FINAL не используются.

Сейчас решил добавить партицирование и попробовать, будет ли отрабатывать только по нужным партициям, а не по всей таблице (партиции будут по полю Дата создания)

Подскажите, может, у кого-то был похожий кейс и как лучше сделать? Может, партиции не самое хрошее решение и можно как-то иначе решить проблему?

Привет. Слегка антипаттерн использования КХ, НО у меня подобный же кейс. Могу посоветовать некое инкрементальное обновление данных по расписанию. Например: добавляете столбец с временем вставки записи и раз в промежуток времени смотрите что за записи пришли, скажем пришло 100000 новых записей с ID, делаете запрос с логикой - удалить те записи у которых ID из этой выборки и время вставки меньше. В отдельной табличке/файле отмечаете после этого удаления что обработали данные в окне "от" и "до". В следующий проход планировщика обработки записей берете новое временное окно с самой максимальной даты "до" и максимальную дату (или можете поставить фиксированный интервал, чтобы примерно регулировать количество обрабатываемых ID) из основной таблицы. Далее снова повторяете шаги по удалению неактуальных ID. При этом часть записей смержится в фоне за счёт движка, помните об этом.

А чтобы в запросах на выборку данные попадали всегда актуальные вставки данных делайте с флагом неактуальности - то есть записи в БД есть, но до прохождения обработки они условно неактуальны и в выборках не видны, и после обработки меняйте флаг update'ом на актуальный.

ну вот в фоне как раз не очень удобно будет, потому что нужны все время актуальные данные вариант с удалением перед вставкой тоже уже думал Проблема еще в том, что одна запись может обновляться по нескольку раз за месяц-полтора

я как понимаю, в ClickHouse и придумали ReplacingMergeTree, чтобы вот такие системы с флагами родом из транзакционных баз не городить

Я не спорю, я лишь предложил вариант как и консистентность получить не по принципу "в конечном итоге" а в момент времени, и производительностью БД воспользоваться. Сразу обозначил что считаю данный кейс нетипичным и не оптимальным для КХ.

наши дата инженеры используют такой же подход, партиционированная таблица с replacing engine когда надо перелить / проапдейтить данные, заливают дубликаты и запускают optimize (его можно запускать и на отдельных партициях, чтобы всю таблицу не перемердживать) запросы с final и ключом партиционирования в where работают быстро

Посмотрите в сторону argMax

Добрый день! Года два назад так же стояли на распутье, выбирали подход. В итоге от ReplacingMergeTree отказались, т.к. вот эти две фразы из документации "Слияние происходят в фоне в неизвестный момент времени, на который вы не можете ориентироваться. Некоторая часть данных может остаться необработанной." нас ни как не устраивала. Мы провели некоторые тесты: FINAL ощутимо тормозил. OPTMIZE вообще не подошел, нам данные нужно заливать и обновлять каждые 10 минут, а операция OPTIMIZE выходила далеко за рамки этих 10 мин. Попытка получать актуальные данные из VIEW в которой все значимые столбцы обернуты argMax так же не подошла, ощутимо тормозила. Возможно без вью было бы приемлемо, но мы не захотели каждый раз для получения данных писать argMax. В итоге сделали обновление через ALTER TABLE UPDATE. На текущий момент в таблице более 2 Млрд записей, около 100 колонок. Каждый день приходит новых примерно 3 Млн. записей (т.е. каждые 10 минут примерно 20К записей). Обновляется примерно 2-3% (т.е. примерно 600 записей). Обновляем правда не все колонки, где то примерно половину. На обновление уходит примерно 3 минуты. Было замечено что время на ALTER TABLE UPDATE не сильно коррелирует от объема данных, т.е. обновление 1000 строк и 10000 может занимать примерно одно и то же время. А вот от кол-ва столбцов сильно зависит, чем их меньше в операции, тем быстрее. Такой подход не самый оптимальный. Я все жду когда же наконец из документации исчезнет фраза про слияние неизвестно когда, тогда вернусь к идее с ReplacingMergeTree, т.к. INSERT происходит действительно быстро. Я понимаю что если при INSERT будет сразу мерж, то он уже не будет таким быстрым, но мне лучше медленный INSERT и консистентные данные, чем быстрый INSERT и дубликаты.

Понял, спасибо за ответ, был очень полезен

у вас получается около 600 строк апдейтится за 10 мин? а апдейт делаете по одной строчке или пачкой?

Нет, 600 строк за 3 минуты. Это цикличность ETL процесса 10 минут. За это время приходит 20К строк на INSERT и 600 на UPDATE. UPDATE делаем пачкой. Конструкция вот такая: ALTER TABLE <имя таблицы> UPDATE <имя поля> = joinGet('<таблица-словарь>', '<имя поля>', <ключевое поле>) WHERE <ключевое поле> IN (SELECT <ключевое поле> FROM <буферная таблица с 10-ой порцией данных>) Таблица словарь содержит тот же набор данных что и <буферная таблица с 10-ти минутной порцией данных>

Спасибо за совет! Пристально взгляну в сторону Lightwheght DELETE. А можно как то обеспечить выполнение Lightwheght DELETE и последующий INSERT в одной транзакции?

А если сделать что-то типа цепочки Engine=Null -> Matview в которой сохранять только последний (хранить типа argMaxState)

Возможно рабочий вариант. Вот только места понадобиться в 2 раза больше. Не что что бы это сильно критично, сейчас мои 2 Млрд строк умещаются в 300 Гб.

Engine=Null не хранит данные, а только для инсерта - а из него уже в Matview. Ну короче надо экспериментировать

Значит я не правильно понял. Честно не знал про движок Engine=Null. Я подумал что в одной таблице держим все изменения (этакая история изменений), а в Matview агрегируем до последнего состояния.

День добрый По поводу вчерашнего нашего обсуждения - нашел решение, которое удовлетворило В общем, разбил на партиции по месяцам, получилось в каждой партиции около 10млн записей всего апдейт делаю через INSERT и после всех нужных инсертов делаю OPTIMIZE. Хотя сейчас он в ручном режиме мне не особо нужен, так как FINAL по партициям заработал оч хорошо Он теперь пробегает не по всей таблице, а только по тем партициям, которые подходят под запрос (у меня в каждом запросе есть условие по датам) Так что, если вдруг вам подойдет подобное решение - можете тестировать

Спасибо! Боюсь нам не подойдет. Если бы мы грузили данные раз в сутки, в каком то технологическом окне, то тогда да, можно и OPTIMIZE по партиции. Но дело в том, что данные могут измениться не только за последний месяц, но и более ранние и ни каких разумных ограничений нет, и грузить надо каждые 10 минут. Правда такие "более ранние" обновления скорее редкость. Наверное это можно как то обыграть. Тут пользователь под ником @Boris (Altinity) писал, что за последнее время FINAL хорошо оптимизировали. Буду смотреть, пробовать.

ну как раз как грузить то, за последние 2 месяца или более ранние - в целом то не имеет значения можно ж вставлять любые данные а потом просто либо ждать, пока в фоне дубли почистятся, либо самому раз в какое-то время делать OPTIMIZE, ну а FINAL по партициям работает быстро, поэтому и на дубли в целом то можно и не обращать внимание