Создайте первую таблицу MergeTree

Узнайте, как работает основной движок таблиц в ClickHouse: создайте таблицу MergeTree, загрузите данные о ценах на недвижимость в Великобритании и посмотрите, как части и слияния влияют на хранение данных и производительность запросов.

Все руководства по быстрому старту

Real-time аналитикаХранилище данныхОбсервабилитиAI/MLCloudOss

Предварительные требования

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

Что вы создадите

В этом кратком руководстве вы создадите таблицу MergeTree для хранения записей о продаже жилой недвижимости в Великобритании с 1995 года. Вы спроектируете схему с подходящими типами столбцов, выберете подходящие ORDER BY и PARTITION BY, загрузите данные напрямую из S3, а затем выполните запрос к system.parts, чтобы увидеть, как ClickHouse физически организует данные на диске. В итоге вы поймёте, почему движок MergeTree лежит в основе почти всех таблиц ClickHouse и как решения о сортировке и партиционировании напрямую влияют на производительность запросов.

1. Разберитесь, как работает MergeTree

   Прежде чем писать SQL, полезно понять, чем MergeTree отличается от традиционной таблицы базы данных.

   Когда вы вставляете данные в таблицу MergeTree, ClickHouse не записывает строки по одной. Вместо этого он записывает часть данных — небольшой, отсортированный и сжатый фрагмент строк — напрямую на диск. Затем ClickHouse со временем в фоновом режиме сливает эти части. Отсюда и название: merge + tree.

   Каждая часть данных сортируется по выражению ORDER BY таблицы. Этот порядок сортировки становится индексом первичного ключа, что позволяет ClickHouse пропускать большие блоки данных, которые не нужно читать при выполнении запроса (это называется отсечением данных). Чем лучше столбцы в ORDER BY подходят для ваших самых частых запросов, тем меньше данных читает ClickHouse.

   То, как MergeTree организует данные, определяется тремя секциями:

   Секция	Что она делает
   ORDER BY	Физически сортирует данные внутри каждой части. Определяет первичный ключ. Обязательно.
   PARTITION BY	Разделяет данные на отдельные партиции, обычно по диапазону дат. Части из разных партиций никогда не сливаются, что позволяет быстро отсекать партиции.
   PRIMARY KEY	По умолчанию совпадает с ORDER BY, если вы явно не зададите более короткий префикс. На его основе строится разреженный индекс.

   Теперь вы должны уметь объяснить взаимосвязь между частями данных, первичным ключом и производительностью запросов в таблице MergeTree.

2. Предпросмотр исходных данных

   Перед созданием таблицы просмотрите исходный файл с помощью табличной функции s3. Это позволяет выполнять запросы к S3 напрямую, не записывая данные в ClickHouse.

   Выполните следующее в SQL-консоли:

   DESCRIBE s3(
   'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
   );
   

   Обратите внимание, что почти каждый столбец определяется как Nullable(String). ClickHouse читает необработанный CSV, поэтому не знает реальные типы данных — это вы исправите на следующем шаге, когда будете проектировать схему таблицы.

   Просмотрите несколько строк:

   SELECT *
   FROM s3(
   'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
   )
   LIMIT 5;
   

   Набор данных содержит сведения о продажах жилой недвижимости в Англии и Уэльсе, зарегистрированных в HM Land Registry, включая id транзакции, price продажи, date, type недвижимости, поля адреса и географические идентификаторы. Вы также заметите два последних столбца (column15, column16), которые пусты, — их можно игнорировать.

   Убедитесь в этом: должны отображаться строки со столбцами id, price, date, postcode, type, town и county.

3. Спроектируйте и создайте таблицу MergeTree

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

   • LowCardinality(String) используется для столбцов с небольшим числом уникальных значений (почтовые индексы, названия городов, названия графств). Этот тип внутренне использует кодирование с использованием словаря, что значительно уменьшает объём хранимых данных и повышает производительность группировки и фильтрации по этим столбцам.
   • Enum8 кодирует столбцы type и duration как небольшие целые числа на диске, сохраняя при этом понятные человеку строковые обозначения в запросах. В исходном CSV используются однобуквенные коды, поэтому мы сопоставим их при вставке.
   • PARTITION BY toYYYYMM(date) создаёт по одной партиции на каждый календарный месяц, что позволяет ClickHouse пропускать целые месяцы, когда условие WHERE фильтрует по date.
   • ORDER BY (postcode, addr1, addr2) сортирует данные так, чтобы обеспечить быстрый поиск по адресу объекта недвижимости — это наиболее естественный способ доступа к этому набору данных.

   CREATE TABLE uk_price_paid
   (
   price      UInt32,
   date       Date,
   postcode   LowCardinality(String),
   type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
   is_new     UInt8,
   duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
   addr1      String,
   addr2      String,
   street     LowCardinality(String),
   locality   LowCardinality(String),
   town       LowCardinality(String),
   district   LowCardinality(String),
   county     LowCardinality(String)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (postcode, addr1, addr2);
   

   Убедитесь, что таблица создана, выполнив:

   SHOW CREATE TABLE uk_price_paid;
   

   Дважды щёлкните по ячейке результата, чтобы просмотреть полный вывод. Обратите внимание: хотя вы указали ENGINE = MergeTree, ClickHouse Cloud создал таблицу с SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}'). Это ожидаемо: Cloud автоматически преобразует MergeTree в SharedMergeTree, добавляя репликацию и поддержку общего хранилища. Поведение и интерфейс запросов остаются прежними.

4. Загрузите данные из S3

   Выполните вставку всего набора данных напрямую из табличной функции s3(). ClickHouse потоково читает сжатый файл из S3 и записывает его в вашу таблицу в виде отсортированных частей.

   INSERT INTO uk_price_paid
   SELECT
       toUInt32(price),
       date,
       postcode,
       transform(type, ['T', 'S', 'D', 'F', 'O'],
           ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
       if(is_new = 'Y', 1, 0) AS is_new,
       transform(duration, ['F', 'L', 'U'],
           ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
       addr1,
       addr2,
       street,
       locality,
       town,
       district,
       county
   FROM s3(
   'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
   );
   

   Поскольку исходный CSV хранит всё в виде строк с однобуквенными кодами (например, T для terraced, F для freehold, Y/N для new-build), мы используем transform, чтобы преобразовать их в понятные значения, а toUInt32/if — чтобы привести числовые столбцы к нужному типу. Столбцы id, column15 и column16 исключены, так как они нам не нужны.

   Это займет одну-две минуты в зависимости от размера вашего сервиса. После завершения проверьте количество строк:

   SELECT formatReadableQuantity(count())
   FROM uk_price_paid;
   

   Вы должны увидеть, что загружено примерно 30 миллионов строк.

5. Изучите части с помощью system.parts

   Здесь можно увидеть внутренние компоненты MergeTree. Таблица system.parts отслеживает все части данных на диске во всех таблицах MergeTree вашего сервиса.

   SELECT
   partition,
   name,
   rows,
   bytes_on_disk,
   marks
   FROM system.parts
   WHERE table = 'uk_price_paid'
   AND active = true
   ORDER BY partition
   LIMIT 20;
   

   Каждая строка представляет одну активную часть данных. Обратите внимание:

   • partition — значение YYYYMM, полученное из выражения PARTITION BY. Данные за каждый месяц хранятся отдельно.
   • name — в имени части закодированы партиция, диапазон номеров блоков и уровень слияния (например, 199501_1_4_2 означает партицию 199501, блоки 1–4 и два слияния).
   • marks — количество гранул индекса. По умолчанию каждая гранула охватывает 8 192 строки, а индекс первичного ключа хранит одну запись на гранулу. Именно этот разреженный индекс остается в памяти и позволяет быстро пропускать ненужные данные.
   • bytes_on_disk — ClickHouse по умолчанию сжимает каждую часть по столбцам с помощью LZ4. Сравните это значение с исходным размером, чтобы оценить коэффициент сжатия.

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

   SELECT
   count()          AS parts,
   sum(rows)        AS total_rows,
   formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table = 'uk_price_paid'
   AND active = true;
   

   Если вы снова выполните этот запрос спустя некоторое время, то можете заметить, что количество частей уменьшилось. Это результат работы слияния в MergeTree: ClickHouse постоянно в фоновом режиме объединяет более мелкие части в более крупные, сокращая их общее количество. Фильтр active = true гарантирует, что вы видите только текущие, слитые части, а не более старые части, ожидающие очистки.

6. Выполните запрос к данным и посмотрите, как ведёт себя первичный ключ

   Теперь выполните несколько реальных аналитических запросов. Сначала найдите самые дорогие продажи за всё время:

   SELECT
   addr1,
   addr2,
   town,
   county,
   price,
   date
   FROM uk_price_paid
   ORDER BY price DESC
   LIMIT 5;
   

   Проверьте статистику запроса в SQL-консоли — обратите внимание, что были прочитаны все 30,033,199 строк. Поскольку price не входит в ключ ORDER BY, ClickHouse не может использовать первичный индекс для пропуска данных и вынужден выполнять полное сканирование таблицы.

   Затем найдите среднюю цену продажи по округам:

   SELECT
   county,
   round(avg(price)) AS avg_price,
   count()           AS sales
   FROM uk_price_paid
   GROUP BY county
   ORDER BY avg_price DESC;
   

   Снова считываются все 30,033,199 строк — county не входит в ORDER BY или PARTITION BY, поэтому ClickHouse сканирует всю таблицу.

   Теперь выполните запрос, который сочетает агрегацию с ORDER BY. Поскольку данные отсортированы по (postcode, addr1, addr2), фильтрация по префиксу почтового индекса позволяет ClickHouse пропустить бо́льшую часть таблицы. Здесь мы находим среднюю цену продажи по годам для объектов недвижимости в зоне почтового индекса SW1A:

   SELECT
   toYear(date) AS year,
   round(avg(price)) AS avg_price,
   count() AS sales,
   min(price) AS cheapest,
   max(price) AS most_expensive
   FROM uk_price_paid
   WHERE postcode LIKE 'SW1A%'
   GROUP BY year
   ORDER BY year DESC;
   

   Проверяйте статистику запросов в SQL-консоли после каждого запроса. Агрегация с фильтром по postcode должна читать лишь небольшую долю строк таблицы, показывая, как работает индекс первичного ключа. Сравните это с предыдущими запросами, которые сканируют таблицу гораздо шире, — разница показывает, почему так важно правильно выбрать ORDER BY.

Следующие шаги

В этом руководстве быстрого старта вы с нуля создали таблицу MergeTree, загрузили 30 миллионов записей о продаже недвижимости в Великобритании из S3, разобрались, как ClickHouse организует данные в отсортированные части и партиции, и выполнили запросы, показывающие возможности индекса по первичному ключу.

Движок MergeTree — это основа. Отсюда можно перейти к изучению специализированных движков, построенных поверх него, или узнать, как materialized views развивают этот подход дальше.

Далее рекомендуем следующие руководства быстрого старта:

• Введение в materialized views

Или углубитесь в справочную документацию:

• Справочник по движку MergeTree
• Справочник по system.parts
• Выбор подходящих типов столбцов

Все руководства по быстрому старту