Данная статья о том, что в системе ClickHouse использование одной плоской таблицы (включая денормализованные таблицы) вместо нескольких таблиц, объединённых с помощью JOIN-операций, значительно повышает скорость выполнения запросов.
Сразу отмечу, что использование JOIN-ов в ClickHouse это плохая практика (информация для тех, кто не дочитает до конца статьи и будет применять в работе множество JOIN-ов).
Мои данные расположены на HDFS. Они нормализованы и состоят из трех таблиц.
Я использовал Apache Spark в Jupyter Notebook для подключения к Impala, чтобы модифицировать данные, которые затем были загружены в ClickHouse.
Шаги, для загрузки данных из Impala в Jupyter Notebook, а затем вставил их в ClickHouse:
1. Установка нужных библиотек.
2. Импорт библиотек в среду Jupyter Notebook.
3. Подключение к Impala и получение данных.
4. Создание DataFrame с данными для загрузки в ClickHouse.
5. Установление соединения с ClickHouse.
6. Создание таблиц в ClickHouse (если они еще не существуют).
7. Передача данных в ClickHouse с помощью API PySpark, осуществляя запись из DataFrame через JDBC.
Немного расскажу о конфигурации ClickHouse в зоне промышленной эксплуатации, чтобы было почему я так создавал таблицы. Из системной таблицы system.clusters можно увидеть настройки кластера.
У нас один кластер clickhouse: shard_num от 1 до 3, replica_num имеет значения от 1 до 2. То есть таблицы, которые создаются внутри кластера, будут исключительно реплицируемыми (на каждом из узлов данные полностью одинаковые).
Поэтому для нашего ClickHouse я создавал реплицируемую таблицу, чтобы она записывалась на всех узлах нашего кластера.
К реплицируемой создавал дистрибутивную таблицу с аналогичной структурой и другим названием. Дистрибутивная таблица не содержит данных, она объединит данные таблиц со всех шардов кластера.
Созданы таблицы в ClickHouse и в них залиты данные:
a_catalog_sales, для нее дистрибутивная a_catalog_sales_distributed
a_customer, для нее дистрибутивная a_customer_distributed
a_customer_address, для нее дистрибутивная a_customer_address_distributed.
a_practice (плоская таблица, столбцы собраны из трех таблиц Catalog_Sales, Customer, Customer_Address и убраны дубликаты столбцов), для нее дистрибутивная a_practice_distributed.
Выполним запросы для получения одинакового результата как к трем таблицам, объединённым с помощью JOIN-операций, так и к одной плоской таблице.
Время выполнения запросов зафиксировано в таблице для дальнейшего сравнения.
Тест | Время выполнения запроса, секунд | Коэффициент увеличения | Условия | |
Три таблицы с JOIN | Одна плоская таблица | |||
Тест 1. Найти численность клиентов и сумму покупки с датой рождения в диапазоне между 1940 и 1960 годами | 14,33 | 1,14 | 12,57 | SELECT sum(cs_list_price),uniqExact(c_customer_id) |
Тест 2. К Тесту 1 добавим еще условие, клиенты проживающих в Oakland | 14,64 | 0,15 | 97,60 | SELECT sum(cs_list_price),uniqExact(c_customer_id) |
Тест 3. К Тесту 2 добавим, вывести количество покупок | 14,59 | 0,11 | 132,64 | SELECT sum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number) |
Тест 4. К Тесту 2 добавим, условие клиенты с именем Anna | 18,26 | 0,09 | 202,89 | SELECT sum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number) |
Тест 5. К Тексту 4 добавим, группировку по номеру накладной | 18,68 | 0,06 | 311,33 | SELECT cs_order_number,sum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number) |
Тест 6. К Тексту 5 добавим, группировку по дате рождения | 18,45 | 0,03 | 615,00 | SELECT cs_order_number,c_birth_year,c_birth_yearsum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number) |
Обращение к таблице с плоской структурой хранения данных показало значительно более быстрое выполнение запросов (от 12,57 до 615 раз) по сравнению с таблицами, объединёнными с помощью JOIN-операций.
Причины почему JOIN-операции лучше не использовать:
Кластерный первичный ключ: В ClickHouse первичный ключ является кластерным, и данные упорядочены по этому ключу. Это позволяет эффективно осуществлять чтение диапазонов данных, независимо от общего объема таблицы. Таким образом, поиск нужных значений выполняется быстрее, так как ClickHouse может использовать сортировку для оптимизации доступа к данным.
Сложность сжатия: При выполнении JOIN-операций количество данных, подлежащих обработке, может увеличиваться, так как система должна извлекать и обрабатывать данные из нескольких таблиц. Это может помешать эффективному использованию механизмов сжатия, так как данные могут быть извлечены в неупорядоченном виде.
Переусложнение запросов: JOIN-операции могут значительно усложнить структуру SQL-запросов, что затрудняет их понимание, поддержку и отладку. Более простые и понятные запросы повышают производительность работы разработчиков и аналитиков.
Практическим путем мы убедились, что использование одной большой таблицы позволяет значительно сократить время выполнения запросов.
Практическим путем мы убедились, что использование одной большой таблицы позволяет значительно сократить время выполнения запросов.
ВЫВОД. Правильно использовать одну таблицу с плоской структурой.
Избавьтесь от старых стереотипов, все будет хорошо.