Прошел еще один месяц, а это значит, что пришло время для нового релиза!
Релиз ClickHouse 26.10 содержит 104 новые функции 🎃, 225 оптимизаций производительности 🍂 и 739 исправления ошибок 🦉.
В этом выпуске реализована композиция функций для более читаемого SQL, более гибкие условия JOIN и новые способы работы с текстом и файлами SQLite.
Мы также добавили ускоренные запросы GROUP BY, сброс на диск для подзапросов IN и экспериментальный кэш столбцов, который избавляет от повторного чтения при декомпрессии и десериализации одних и тех же данных.
Особое приветствие всем новым участникам в версии 26.10! Рост сообщества ClickHouse вызывает искреннюю благодарность, и мы всегда ценим вклад, который делает ClickHouse таким популярным.
Ниже приведены имена новых участников:
ARON, Aarav, Abdullah Kaya, Akanshaak, Aleh Strakachuk, Aleksandr Efimov, Alexey Sitnikov, Andrea Debernardi, Andrew Wilkins, Angelica Ismailos, Armin Braun, Arvind Kumar, Ashish Choubey, Azarudeen Shariff A, Azhar Izzannada E, Bentsi Leviav, Chester, Craig Ringer, David Rolfe, Denis Voytyuk, Dimitri König, Frank Denis, Fábio Neves, GON, Gheorghe Pucea, Ilias, Ilias Aberkane, Javier G. Sogo, Jay Pokale, Jaysurya Ray, Jonathan Ackerman, Knut-Aage-Hofseth, Krithika Balagurunathan, LeeTeng2001, LyJacky, Maksim Andreev, Mike Lewis, Mishalski, Mohammed Sohail, Nick Akey, Oleg Milanov, Prince Maurya, RJ, Ray Winkelman, Rion Bora, Sam Wolfberg, Shahzeb Ali, Thomas Sileghem, Tom Alard, Unamed, Uwez Khan, Vadim Lopatin, Vector, Vitaly Pryakhin, Vlad, VrtxOmega, Zach Rippas, ZainnQureshii, ajay tomar, amogiska, antonioscaramuzzino, arc1999, artemkulyk, awss, azharizz, bentsileviav, bisht2050, clickgapai, comino, dhruvtaneja-ch, dosedaf, erezrokah, gonappuccino, jacky, linjiayu1025-collab, namtran1812, seva-potapov, sunliqiang, superdiaodiao, tianhui-fu, tridao, turbolytics, turbolytics.io, ultrabig, vector524
Подсказка: если вам интересно, как мы формируем этот список… here.
Вы также можете посмотреть слайды из презентации.
Если вы использовали функции высшего порядка в ClickHouse, такие как arrayMap и arrayFilter, то знаете, что передаваемая лямбда-функция может быстро превратиться в цепочку вложенных вызовов функций. ClickHouse 26.10 добавляет оператор композиции функций, который позволяет создавать конвейер функций, объединенных с помощью оператора |, что делает такие лямбды более читаемыми.
Давайте посмотрим, как сравниваются эти два стиля при извлечении названий сайтов из URL-адресов. Мы собираемся перевести URL в нижний регистр, удалить префикс www, взять первый значимый поддомен, а затем перевести его в верхний регистр.
Оба столбца дают одинаковый результат, но составной вариант читается скорее как список шагов. Каждую функцию здесь можно вызвать, передав только само значение, поэтому нам не нужно было указывать, куда именно оно должно направиться.
Чтобы передать дополнительные аргументы функции, используйте _ для обозначения места, куда попадает значение из конвейера. Например, substring(_, 1, 3) берет первые три символа переданной строки:
Функции высшего порядка также могут принимать несколько массивов одинаковой длины, обрабатывая их поэлементно. В этом случае используйте _1 для элемента из первого массива, _2 для соответствующего элемента из второго и так далее. Давайте вычислим итоговые суммы по строкам для заказа, умножив каждую цену на ее количество, а затем отформатируем результаты в виде цен:
Заполнители (плейсхолдеры) также работают сами по себе, без |, как более короткий способ написания лямбда-функции. Например, _1 * _2 означает то же самое, что и (price, quantity) -> price * quantity, как показано в следующем примере:
Под капотом ClickHouse превращает конвейер в обычную лямбду во время анализа запроса. Это можно увидеть, выполнив EXPLAIN SYNTAX с параметром run_query_tree_passes = 1, который показывает запрос после его переписывания анализатором:
Цепочка с | превратилась в лямбду с тем же вложенным выражением, с которого мы начинали, поэтому оба стиля работают абсолютно одинаково, и вы можете использовать тот, который вам больше нравится.
Квоты позволяют ограничить объем работы пользователя за заданный интервал времени. В ClickHouse 26.10 добавлен счетчик written_rows, позволяющий ограничивать объем вставки по количеству строк, записываемых пользователем.
Давайте создадим таблицу и пользователя для вставки, который может записывать не более 100 000 строк суммарно не более чем за 250 операций вставки в минуту:
Первая вставка из 60 000 строк проходит без каких-либо проблем:
Но если мы выполним ту же вставку повторно в течение той же минуты, мы превысим лимит:
Отклоненная вставка не записывает никаких данных, поэтому таблица по-прежнему содержит 60 000 строк. Как и в случае с другими квотами, вы можете посмотреть, насколько каждый пользователь близок к своим лимитам, в системной таблице system.quotas_usage:
Этот релиз 26.10 также приносит несколько новых функций для работы с массивами и картами (maps).
arrayMinIndex и arrayMaxIndex возвращают позицию наименьшего и наибольшего элемента (опционально после применения лямбды к каждому из них). arrayRemoveAt удаляет элемент в заданной позиции, причем отрицательные позиции отсчитываются с конца.
Давайте посмотрим, что каждая из них возвращает для небольших массивов:
Индексы массивов в ClickHouse начинаются с 1, поэтому наименьшее значение 2, находящееся на первой позиции в массиве, имеет индекс 1
Индексы массивов в ClickHouse начинаются с 1, поэтому наименьшее значение 2, находящееся на первой позиции в массиве, имеет индекс 1
Затем мы можем передать результаты напрямую в другой массив для поиска соответствующего значения. В следующем примере мы находим самый холодный и самый теплый дни недели, а затем исключаем пятницу из списка:
Для карт (maps) функция mapRemove удаляет ключ, mapEntries возвращает пары ключ-значение в виде массива кортежей, а mapContainsKeyValue проверяет, содержит ли карта определенную пару ключ-значение:
mapContainsKeyValue также может использовать текстовые индексы, что значительно ускоряет фильтрацию по меткам при наличии большого количества строк.
Наконец, комбинатор -Map больше не требует столбца типа Map. Вы можете передать ключ в качестве последнего аргумента, что упрощает агрегацию по ключу непосредственно из строк:
В ClickHouse деление одного целого числа на другое всегда возвращало число с плавающей точкой, поэтому 7 / 2 дает 3.5. PostgreSQL, SQL Server и SQLite вместо этого усекают результат до 3.
Мнение Алексея по поводу обсуждения релиза заключалось в том, что ClickHouse прав, а все остальные неправы, но если вы переносите запросы из другой базы данных и вам нужно, чтобы они были «неправы» точно так же, теперь для этого есть специальная настройка. Вот поведение по умолчанию:
А если вам ближе подход PostgreSQL, вы можете включить sql_compatible_division_for_integers:
Результат усекается в сторону нуля и сохраняет целочисленный тип, в то время как деление на число с плавающей точкой по-прежнему дает float. При включенной настройке деление на ноль вызывает ошибку, а не возвращает inf:
Настройка отключена по умолчанию. Представления (views) и другие сохраненные определения сохраняют то поведение деления, с которым они были созданы, поэтому ее включение не изменит результаты ваших существующих представлений.
Когда мы используем IN с подзапросом, ClickHouse строит множество значений, возвращаемых этим подзапросом, и проверяет по нему строки внешнего запроса. Большое множество может потреблять много памяти. В ClickHouse 26.10 добавлена возможность сбрасывать это множество на диск (spill to disk), что позволяет завершить запрос с меньшим лимитом памяти.
Давайте попробуем это на датасете цен на недвижимость в Великобритании (UK property prices dataset), который содержит данные о продажах недвижимости в Англии и Уэльсе с 1995 года. Мы хотим посчитать количество продаж в 2024 году по адресам, для которых также зафиксирована продажа до 2024 года. Мы будем сопоставлять данные по почтовому индексу (postcode) и двум адресным полям, addr1 и addr2, которые идентифицируют здание и, при наличии, квартиру. Это сопоставление по адресу, а не по постоянному идентификатору объекта недвижимости, и мы считаем именно продажи, а не уникальные объекты.
Сначала зададим запросу лимит памяти 4 GiB и оставим сброс на диск отключенным:
Подзапрос считывает 29,3 миллиона ранних продаж, чтобы построить множество адресов. В ClickHouse 26.10 этот запрос использовал 1,96 GiB пиковой памяти. Посмотрим, что произойдет, если уменьшить лимит памяти до 512 MiB:
ClickHouse исчерпал память. Теперь мы можем включить сброс на диск, настроив параметр max_bytes_before_external_set. Давайте используем порог в 128 MiB, сохранив общий лимит памяти для запроса на уровне 512 MiB:
На этот раз пиковое использование памяти составило 203 MiB. Это выше нашего порога сброса в 128 MiB, который указывает ClickHouse, когда начинать сброс на диск, а не задает жесткий лимит памяти. Запросу также нужна память для чтения и обработки данных, поэтому мы установили порог сброса ниже общего лимита в 512 MiB.
Если мы посмотрим на события профилирования (profile events), нажав клавишу пробела во время выполнения запроса, то увидим, что ClickHouse сбросил одно множество на диск и записал четыре куска (parts):
В следующей таблице обобщены различные конфигурации, которые мы использовали:
В ClickHouse 26.10 улучшена группировка по нескольким колонкам LowCardinality. Теперь он может сериализовать ключи группировки напрямую из их словарей и кэшировать поиск состояний агрегатов для повторяющихся комбинаций ключей.
Давайте снова используем нашу таблицу uk_price_paid, чтобы посчитать количество продаж и вычислить среднюю цену для каждой комбинации графства (county), района (district), города (town) и внешнего почтового индекса (outward postcode). Все четыре колонки группировки имеют тип LowCardinality(String):
Мы используем FORMAT Null, чтобы выполнить запрос без вывода 15 409 строк результата, чтобы вывод в терминал не влиял на сравнение.
Выполним запрос трижды на ClickHouse 26.9:
А теперь трижды на ClickHouse 26.10, используя те же данные и структуру таблицы. Обе версии используют по умолчанию 12 потоков на моей машине:
Если взять лучшее время для каждой версии, запрос занял 255 миллисекунд на 26.9 и 171 миллисекунду на 26.10, то есть версия 26.10 работает чуть более чем на 30% быстрее.
В ClickHouse 25.12 была представлена динамическая фильтрация для запросов ORDER BY … LIMIT, использующая текущую границу Top-K во избежание избыточного чтения колонок и пропуска гранул с помощью индексов minmax. ClickHouse 26.10 распространяет этот подход на запросы GROUP BY и добавляет пропуск гранул по первичному индексу.
Давайте снова используем данные о недвижимости в Великобритании, чтобы найти десять самых свежих дат с зарегистрированными продажами, а также количество продаж и их среднюю цену. Мы создадим отдельную таблицу, содержащую две используемые в запросе колонки, отсортированные по дате. Такая структура позволит ClickHouse использовать оба механизма: отфильтровывать более старые продажи до чтения их цен и использовать первичный индекс для пропуска гранул, все даты в которых старше граничной.
Это скопирует все 30 млн продаж и скомпонует (merge) новую таблицу в единый кусок (part). Мы выполним запрос на ClickHouse 26.10, чтобы посмотреть, как выглядит результат:
Запрос возвращает следующие десять дат:
Время протестировать запрос с оптимизацией и без нее с помощью clickhouse-local. Замеры времени взяты из вывода CLI --time, а количество прочитанных данных и пиковая память — из --print-profile-events:
Изменение enable_group_by_top_k_dynamic_filtering на 1 дает следующий результат:
Из таблицы результатов видно, что с включенной фильтрацией ClickHouse считывает примерно на 94% меньше строк. Прогоны с оптимизацией пропустили 3529, 3521 и 3529 гранул соответственно, тогда как без нее ни одна гранула пропущена не была. Точный объем работы варьируется, поскольку параллельные потоки агрегации находят и публикуют свои граничные значения.
Мы можем подтвердить пропуск целых гранул с помощью события профилирования TopKGranulesSkippedByPrimaryKey. С параметрами --print-profile-events --profile-events-delay-ms=-1 CLI выводит итоговые значения событий. Для первого прогона с включенной оптимизацией релевантные значения составили:
В ClickHouse 26.10 появился экспериментальный кэш колонок (columns cache), который хранит десериализованные данные колонок в памяти, избавляя последующие запросы от их повторной разжатия и десериализации.
Давайте снова используем датасет недвижимости Великобритании, чтобы посчитать продажи, где первое адресное поле содержит HOUSE. Мы создадим копию, содержащую колонки price и address, сохранив исходный порядок сортировки:
Кэш в настоящее время поддерживает формат wide кусков MergeTree (wide MergeTree parts). Установка обоих порогов размера в 0 принудительно задает этот формат.
Сначала выполним запрос с отключенным кэшем колонок:
После одного тестового прогона (без замеров) для прогрева кэша файловой системы мы выполним этот запрос трижды, используя sorted-columns-cache:off-1, sorted-columns-cache:off-2 и sorted-columns-cache:off-3 в качестве комментариев в логе.
Затем мы изменим use_columns_cache на 1 и выполним его еще три раза, используя комментарии sorted-columns-cache:on-1, sorted-columns-cache:on-2 и sorted-columns-cache:on-3. Первый прогон с включенным кэшем заполняет кэш колонок, а следующие два повторно используют его.
При включенном кэшировании запрос кэширует addr1. Он не читает, например, price, поэтому эта колонка не кэшируется. Но если бы мы добавили условие на price, любые данные, прочитанные из этой колонки, также закэшировались бы.
При включенном логировании запросов мы можем получить данные о времени выполнения и счетчиках кэша из system.query_log с помощью следующего запроса:
При отключенном кэше колонок запрос занимает 32–34 миллисекунды. Значение cacheHits равно 0, потому что ClickHouse не обращается к кэшу.
Прогон, во время которого кэш заполняется, занимает 37 миллисекунд, а следующие два прогона, использующие кэш, занимают по 12 миллисекунд каждый, при этом все 3718 гранул addr1 считываются из кэша колонок.
Значение SelectedRows остается прежним: ClickHouse по-прежнему вычисляет фильтр для всех 30,45 миллиона строк, но может повторно использовать декодированные адресные данные. Кэш хранит данные колонок, а не результат запроса, поэтому другие запросы, читающие те же данные, также могут получить выгоду.
В ClickHouse 26.10 добавлена экспериментальная поддержка LATERAL JOIN, которая позволяет подзапросу ссылаться на колонки таблицы, указанной перед ним. Мы можем включить ее, установив allow_experimental_lateral_join = 1.
Мы используем датасет IMDb из наших примеров соединений (joins), чтобы найти три фильма с самым высоким рейтингом для каждого из трех актеров. Таблица actors содержит их имена, movies — информацию о фильмах и рейтинги, а roles связывает актеров с фильмами, в которых они снимались.
Строка 12 является ключевой: WHERE r.actor_id = a.id. Значение a.id поступает из actors AS a во внешнем запросе. Конструкция LATERAL позволяет нашему вложенному запросу использовать это значение, что означает поиск трех лучших фильмов для данного актера. LIMIT 3 применяется для каждого актера, а не выбирает три фильма по всему набору данных.
Для каждого выбранного актера вложенный запрос находит его роли, соединяет их с данными о фильмах и исключает фильмы без рейтинга. Инструкция GROUP BY удаляет дубликаты фильмов, если у актера имеется несколько записей о ролях. Затем выполняется сортировка по рейтингу с использованием года и идентификатора фильма для разрешения ничьих, и возвращаются первые три результата.
Мы получаем девять строк: по три фильма для каждого актера.
ClickHouse 26.10 также поддерживает соединения (JOIN) с произвольными условиями ON, включая те, которые не содержат условие равенства между таблицами. Давайте используем это для категоризации веб-трафика с помощью шаблонов URL.
Сначала мы создадим таблицу, содержащую количество просмотров страниц:
Мы сохраним правила категоризации в отдельной таблице. Подстановочный знак % соответствует любой последовательности символов:
Теперь мы можем объединить таблицы с помощью LIKE, чтобы сопоставить каждый путь с категорией:
Имейте в виду, что гибкость произвольных соединений может обходиться дорого: соединение вложенными блочными циклами проверяет каждую пару строк, поэтому оно может работать так же медленно, как декартово произведение (cross join), и используется планировщиком запросов в крайнем случае.
ClickHouse 26.10 также добавляет формат ввода Freeform, который определяет столбцы и их типы из текста с однородной структурой.
Давайте посмотрим, как это работает, передав вывод команды ls -l в clickhouse-local через пайп. Переданный на вход текст будет доступен в виде таблицы и к нему можно будет делать запросы так же, как и к любой другой таблице. Следующий запрос описывает вывод команды ls -l:
Из этого вывода мы видим, что ls -l выводит 10 столбцов, которые названы от c0 до c9. Мы можем запросить таблицу, чтобы вернуть все столбцы с помощью следующего запроса:
Давайте дадим столбцам понятные имена, отформатируем размеры файлов и объединим поля даты в метку времени. В этом выводе не указан год, поэтому мы будем использовать текущий год для этих недавних файлов:
ClickHouse уже поддерживает запросы к базам данных SQLite через движок баз данных, движок таблиц и табличную функцию. Версия 26.10 добавляет SQLite в качестве формата ввода и вывода, позволяя экспортировать результаты запросов напрямую в файл базы данных SQLite, готовый для открытия с помощью инструментов SQLite или передачи другим пользователям.
Давайте повторно используем нашу таблицу фильмов IMDb для подсчета фильмов и расчета их среднего рейтинга за каждый год 1990-х годов:
ClickHouse использует формат SQLite из-за расширения .sqlite у выходного файла, но мы также можем явно указать формат вывода, задав FORMAT SQLite после имени файла. Мы записываем десять результатов в таблицу с именем films_by_year.
Мы можем открыть файл с помощью командной строки sqlite3 и запросить его:
ClickHouse также может прочитать файл обратно, определив имена и типы столбцов:
ClickHouse 26.10 может проверять строковые фильтры, такие как LIKE, startsWith и endsWith, во время десериализации столбца, избегая копирования несоответствующих строк в память. Эта оптимизация включена по умолчанию благодаря параметру apply_string_filters_during_scan.
Давайте попробуем это на наборе данных отзывов Amazon. Мы загрузим файлы Parquet за 2010 и 2011 годы из нашего примера с форматом Freeform в таблицу MergeTree, получив 9,98 млн отзывов:
Мы хотим посчитать отзывы, содержащие слово disappointed. Сначала давайте отключим оптимизацию, а также кэш столбцов, кэш запросов и кэш условий запросов:
Мы запустили это в ClickHouse 26.10 с шестьюдесятью потоками по умолчанию, после объединения таблицы в одну широкую (Wide) часть. После одного предварительного запуска с каждым набором настроек мы чередовали три замеряемых запуска с отключенной и включенной оптимизацией. Приведенные ниже показатели времени и пикового потребления памяти взяты из system.query_log.
При apply_string_filters_during_scan = 0:
Изменение apply_string_filters_during_scan на 1 дает следующий результат:
Каждый запуск возвращает те же 293 115 совпадений. Время выполнения схоже, но пиковое потребление памяти снижается примерно со 129 МБ до 2,54 МБ, то есть примерно на 98%.
