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

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

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

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

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

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

Перед созданием таблицы просмотрите исходный файл с помощью табличной функции 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.

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

• 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, добавляя репликацию и поддержку общего хранилища. Поведение и интерфейс запросов остаются прежними.

Выполните вставку всего набора данных напрямую из табличной функции 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 миллионов строк.

Здесь можно увидеть внутренние компоненты 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 гарантирует, что вы видите только текущие, слитые части, а не более старые части, ожидающие очистки.

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

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.