Как стать автором
Обновить

В ClickHouse одна плоская таблица лучше, чем несколько соединенных таблиц

Время на прочтение4 мин
Количество просмотров9.5K

Данная статья о том, что в системе 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)
WHERE c_birth_year BETWEEN 1940 AND 1960

Тест 2. К Тесту 1 добавим еще условие, клиенты проживающих в  Oakland

14,64

0,15

97,60

SELECT sum(cs_list_price),uniqExact(c_customer_id)
WHERE c_birth_year BETWEEN 1940 AND 1960 and ca_city='Oakland'

Тест 3. К Тесту 2 добавим, вывести количество покупок

14,59

0,11

132,64

SELECT sum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number)
WHERE c_birth_year BETWEEN 1940 AND 1960 AND ca_city='Oakland'

Тест 4. К Тесту 2 добавим, условие клиенты с именем Anna

18,26

0,09

202,89

SELECT sum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number)
WHERE c_birth_year BETWEEN 1940 AND 1960 AND ca_city='Oakland' and c_first_name='Anna'

Тест 5. К Тексту 4 добавим, группировку по номеру накладной

18,68

0,06

311,33

SELECT cs_order_number,sum(cs_list_price),uniqExact(c_customer_id),uniqExact(cs_order_number)
WHERE c_birth_year BETWEEN 1940 AND 1960 and ca_city='Oakland' AND c_first_name='Anna'
GROUP BY 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)
WHERE c_birth_year BETWEEN 1940 AND 1960 AND ca_city='Oakland' AND c_first_name='Anna'
GROUP BY cs_order_number,c_birth_year

Обращение к таблице с плоской структурой хранения данных показало значительно более быстрое выполнение запросов (от 12,57 до 615 раз) по сравнению с таблицами, объединёнными с помощью JOIN-операций.

Причины почему  JOIN-операции лучше не использовать:

  • Кластерный первичный ключ: В ClickHouse первичный ключ является кластерным, и данные упорядочены по этому ключу. Это позволяет эффективно осуществлять чтение диапазонов данных, независимо от общего объема таблицы. Таким образом, поиск нужных значений выполняется быстрее, так как ClickHouse может использовать сортировку для оптимизации доступа к данным.

  • Сложность сжатия: При выполнении JOIN-операций количество данных, подлежащих обработке, может увеличиваться, так как система должна извлекать и обрабатывать данные из нескольких таблиц. Это может помешать эффективному использованию механизмов сжатия, так как данные могут быть извлечены в неупорядоченном виде.

  • Переусложнение запросов: JOIN-операции могут значительно усложнить структуру SQL-запросов, что затрудняет их понимание, поддержку и отладку. Более простые и понятные запросы повышают производительность работы разработчиков и аналитиков.

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

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

ВЫВОД. Правильно использовать одну таблицу с плоской структурой.

Избавьтесь от старых стереотипов, все будет хорошо.

Теги:
Хабы:
Всего голосов 11: ↑7 и ↓4+3
Комментарии16

Публикации

Работа

Data Scientist
55 вакансий

Ближайшие события