SQL-запросы к Clickhouse в онлайн-песочнице: практический пример

Big Data, Большие данные, SQL, обработка данных, ClickHouse

Насколько быстро ClickHouse выполняет SQL-запросы: тестирование СУБД в открытой онлайн-песочнице. Примеры запросов и время их выполнения.

Работа с онлайн-песочницей Clickhouse: выполнение SQL-запросов

Будучи реляционной аналитической СУБД, ClickHouse позволяет обрабатывать гигабайты данных в реальном времени. Архитектурные особенности, благодаря которым реализуется такая скорость, мы недавно разбирали здесь. Чтобы оценить это на практике, поработаем в открытой онлайн-песочнице, которая доступна по URL-адресу https://play.clickhouse.com/play.

Веб-интерфейс онлайн-песочницы ClickHouse
Веб-интерфейс онлайн-песочницы ClickHouse

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

  • HTTP, который очень прост и подходит для использования напрямую;
  • Native TCP, который имеет меньше накладных расходов благодаря прямому обращению сразу на транспортном уровне. Нативный протокол используется в клиенте командной строки, для взаимодействия между серверами во время обработки распределенных запросов, а также в других программах на C++.
  • gRPC, который использует HTTP/2 в качестве транспорта и тоже работает очень быстро.

На практике разработчик обычно не обращается к ClickHouse напрямую, а использует соответствующие инструмент, например, поддерживаемые Яндексом консольный клиент, драйверы JDBC и ODBC или клиентскую библиотеку C++. Также можно применять сторонние библиотеки и GUI-интерфейсы, например, Tabix, HomeOps, LightHouse, Redash, Grafana, DBM и пр.

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

Сперва посмотрим список баз данных с помощью команды

show databases
Перечень баз данных
Перечень баз данных

Далее этой же командой запросим список таблиц:

show tables
Перечень таблиц
Перечень таблиц

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

show grants
Доступные разрешения
Доступные разрешения

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

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

show privileges
Все привилегии
Все привилегии

Работая под пользователем с именем play, возьмем в качестве объекта исследования базу данных с названием git_clickhouse. Посмотрим перечень ее таблиц с помощью команды

show tables from git_clickhouse
Просмотр таблиц в базе данных
Просмотр таблиц в базе данных

Результат запроса показывает, что в рассматриваемой базе данных всего 2 таблицы. Посмотрим, какие столбцы есть в таблице line_changes, используя команду

show columns from git_clickhouse.line_changes
Просмотр столбцов в таблице БД ClickHouse
Просмотр столбцов в таблице БД ClickHouse

Результат выполнения запроса показывает, что в этой таблице 37 столбцов, и столбец с названием time является первичным ключом.

Хотя ClickHouse не поддерживает ANSI SQL, эта СУБД позволяет обращаться к данным, используя SQL-подобный язык, включающие многие популярные SQL-операторы, как SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, подзапросы в разделах FROM, IN, JOIN, оконные функции и скалярные подзапросы.

Получим данные из таблицы line_changes, выполнив SELECT-запрос всех столбцов:

select * from git_clickhouse.line_changes
Вывод значений всех столбцов в таблице БД
Вывод значений всех столбцов в таблице БД

Запрос, прочитавший более 16 тысяч строк на 3,76 МБ, выполнился за 0,014 секунд.

Теперь посмотрим, как ClickHouse работает с соединениями таблиц. Для этого соединим таблицы line_changes и file_changes из базы данных git_clickhouse по ключу commit_hash, который есть в них обеих. В выводе будем возвращать строку, путь, автора и время с фильтрацией по автору:

SELECT line, path, author, time from git_clickhouse.line_changes
JOIN git_clickhouse.file_changes ON git_clickhouse.file_changes.commit_hash= git_clickhouse.line_changes.commit_hash
WHERE author='s-kat'
JOIN в ClickHouse
Соединение таблиц с фильтрацией

Запрос, прочитавший более 2 млн строк на 116,60 МБ, выполнился менее чем за 0,05 секунд.

Теперь выполним агрегацию по строке, подсчитав количество строк с фильтрацией по автору и сортировкой по убыванию:

SELECT line, COUNT(line) as quantity FROM git_clickhouse.line_changes
JOIN git_clickhouse.file_changes ON git_clickhouse.file_changes.commit_hash= git_clickhouse.line_changes.commit_hash
WHERE author='s-kat'
GROUP BY line
ORDER BY quantity DESC
агрегаты в ClickHouse
Соединение таблиц с агрегацией и фильтрацией

Выполнение запроса, прочитавшего более 7 млн строк на 409,17 МБ, заняло чуть более 2,5 секунд.

Усложним этот запрос, добавив оператор HAVING для фильтрации по группе строк:

SELECT line, COUNT(line) as quantity FROM git_clickhouse.line_changes
JOIN git_clickhouse.file_changes ON git_clickhouse.file_changes.commit_hash= git_clickhouse.line_changes.commit_hash
WHERE author='s-kat'
GROUP BY line
HAVING quantity BETWEEN 1000 AND 100000
ORDER BY quantity DESC
Group By и Having в ClickHouse
Group By и Having в ClickHouse

Выполнение этого запроса, прочитавшего, как и в предыдущем случае более 7 млн строк на 409,17 МБ, заняло уже чуть больше времени — 2,775 секунд из-за добавления дополнительной фильтрации с оператором HAVING.

Посмотрим план выполнения этого SQL-запроса, подставив вперед оператор EXPLAIN:

EXPLAIN SELECT line, COUNT(line) as quantity FROM git_clickhouse.line_changes
JOIN git_clickhouse.file_changes ON git_clickhouse.file_changes.commit_hash= git_clickhouse.line_changes.commit_hash
WHERE author='s-kat'
GROUP BY line
HAVING quantity BETWEEN 1000 AND 100000
ORDER BY quantity DESC
План выполнения SQL-запроса в Clickhouse
План выполнения SQL-запроса в Clickhouse

Отображение плана из 11 строк на 574 байта выполнилось очень быстро, заняв 0,004 секунды.

Проведенные эксперименты показывают, что ClickHouse действительно работает довольно быстро, однако скорость выполнения запросов все равно обратно пропорциональна количеству читаемых (сканируемых) данных. Впрочем, ClickHouse может обрабатывать простые запросы по несколько десятков ГБ в секунду. Поэтому эта СУБД отлично подходит для построения DWH, критичного к малейшим задержкам, обеспечивая аналитику больших данных в реальном времени. В новой статье я рассказываю про другую онлайн-песочницу ClickHouse, которая поддерживает DDL-запросы.

Освойте администрирование и эксплуатацию ClickHouse для аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:

Я даю свое согласие на обработку персональных данных и соглашаюсь с политикой конфиденциальности.

Источники

  1. https://play.clickhouse.com/play
  2. https://clickhouse.com/docs/ru/interfaces
Поиск по сайту