[03] Работа с SQL в ClickHouse - rklepov/OTUS-ClickHouse-2025-03 GitHub Wiki
При выполнении первой части задания (меню) использовалась языковая модель DeepSeek.
1. Создание БД
CREATE DATABASE IF NOT EXISTS restaurant_db
ENGINE = Atomic
Ok.
USE restaurant_db
Connecting to database restaurant at localhost:9000 as user default.
Connected to ClickHouse server version 25.3.2.
2. Создание таблицы
CREATE OR REPLACE TABLE menu
(
`dish_id` UInt32 NOT NULL COMMENT 'Уникальный идентификатор блюда',
`dish_name` String NOT NULL COMMENT 'Название блюда',
`category` LowCardinality(String) NOT NULL COMMENT 'Категория блюда (основное, десерт и т.д.)',
`price` Decimal(10, 2) NOT NULL COMMENT 'Цена блюда в рублях',
`description` String NULL COMMENT 'Описание блюда и ингредиентов (может отсутствовать)'
)
ENGINE = MergeTree
ORDER BY dish_id
COMMENT 'Меню ресторана с основными блюдами и их характеристиками'
Ok.
SELECT
name,
type,
comment
FROM system.columns
WHERE (database = 'restaurant_db') AND (`table` = 'menu')
┌─name────────┬─type───────────────────┬─comment─────────────────────────────────────────────┐
1. │ dish_id │ UInt32 │ Уникальный идентификатор блюда │
2. │ dish_name │ String │ Название блюда │
3. │ category │ LowCardinality(String) │ Категория блюда (основное, десерт и т.д.) │
4. │ price │ Decimal(10, 2) │ Цена блюда в рублях │
5. │ description │ Nullable(String) │ Описание блюда и ингредиентов (может отсутствовать) │
└─────────────┴────────────────────────┴─────────────────────────────────────────────────────┘
3. CRUD операции
Вставка данных
INSERT INTO menu VALUES
(1, 'Стейк Рибай', 'Горячие блюда', 1250.00, 'Говяжий стейк с соусом Демиглас'),
(2, 'Салат Цезарь', 'Салаты', 450.00, 'Салат с курицей, крутонами и соусом Цезарь'),
(3, 'Тыквенный суп-пюре', 'Супы', 350.00, 'Нежный суп-пюре с тыквой и сливками'),
(4, 'Лосось на гриле', 'Горячие блюда', 890.00, 'Филе лосося с лимонным соусом'),
(5, 'Брускетта с томатами', 'Закуски', 290.00, 'Тосты с помидорами, базиликом и чесноком'),
(6, 'Тирамису', 'Десерты', 390.00, 'Классический итальянский десерт'),
(7, 'Паста Карбонара', 'Паста', 550.00, 'Спагетти с беконом, яйцом и пармезаном'),
(8, 'Вегетарианская пицца', 'Пицца', 680.00, 'Пицца с овощами и моцареллой'),
(9, 'Мохито', 'Напитки', 350.00, 'Освежающий коктейль с лаймом и мятой'),
(10, 'Чизкейк', 'Десерты', 420.00, 'Классический чизкейк с ягодным соусом'),
(11, 'Секретное блюдо', 'Горячие блюда', 999.00, NULL); -- Пример с NULL в описании
Запрос данных
Количество и средняя цена блюд по категориям.
SELECT
category,
count(*) AS cntByCategory,
round(avg(price), 2) AS avgPrice
FROM menu
GROUP BY category
ORDER BY
cntByCategory DESC,
category ASC
┌─category──────┬─cntByCategory─┬─avgPrice─┐
1. │ Горячие блюда │ 3 │ 1046.33 │
2. │ Десерты │ 2 │ 405 │
3. │ Закуски │ 1 │ 290 │
4. │ Напитки │ 1 │ 350 │
5. │ Паста │ 1 │ 550 │
6. │ Пицца │ 1 │ 680 │
7. │ Салаты │ 1 │ 450 │
8. │ Супы │ 1 │ 350 │
└───────────────┴───────────────┴──────────┘
Изменение данных
Повысим цену на горячие блюда на 10% и повторим предыдущий запрос.
ALTER TABLE menu
(UPDATE price = price * 1.1 WHERE category IN ['Горячие блюда'])
Ok.
SELECT
category,
count(*) AS cntByCategory,
round(avg(price), 2) AS avgPrice
FROM menu
GROUP BY category
ORDER BY
cntByCategory DESC,
category ASC
┌─category──────┬─cntByCategory─┬─avgPrice─┐
1. │ Горячие блюда │ 3 │ 1150.97 │
2. │ Десерты │ 2 │ 405 │
3. │ Закуски │ 1 │ 290 │
4. │ Напитки │ 1 │ 350 │
5. │ Паста │ 1 │ 550 │
6. │ Пицца │ 1 │ 680 │
7. │ Салаты │ 1 │ 450 │
8. │ Супы │ 1 │ 350 │
└───────────────┴───────────────┴──────────┘
Удаление данных
Удаляем десерты дороже 400р.
ALTER TABLE restaurant_db.menu
(DELETE WHERE (category = 'Десерты') AND (price > 400.))
Ok.
Проверяем состояние мутаций
SELECT
`table`,
mutation_id,
create_time,
substr(command, 1, 7) AS cmd,
is_done
FROM system.mutations
WHERE (`table` = 'menu') AND (database = 'restaurant_db')
┌─table─┬─mutation_id────┬─────────create_time─┬─cmd─────┬─is_done─┐
1. │ menu │ mutation_2.txt │ 2025-04-26 09:58:04 │ (UPDATE │ 1 │
2. │ menu │ mutation_3.txt │ 2025-04-26 10:10:31 │ (DELETE │ 1 │
└───────┴────────────────┴─────────────────────┴─────────┴─────────┘
4. Изменение состава полей
Добавление новых
ALTER TABLE menu
ADD COLUMN `calories` Nullable(UInt32) COMMENT 'Калорийность блюда в ккал',
ADD COLUMN `is_vegetarian` Bool DEFAULT false COMMENT 'Флаг вегетарианского блюда (true/false)',
ADD COLUMN `preparation_time` UInt16 DEFAULT 10 COMMENT 'Время приготовления в минутах'
Ok.
SELECT
name,
type,
comment,
default_expression
FROM system.columns
WHERE (database = 'restaurant_db') AND (`table` = 'menu')
┌─name─────────────┬─type───────────────────┬─comment─────────────────────────────────────────────┬─default_expression─┐
1. │ dish_id │ UInt32 │ Уникальный идентификатор блюда │ │
2. │ dish_name │ String │ Название блюда │ │
3. │ category │ LowCardinality(String) │ Категория блюда (основное, десерт и т.д.) │ │
4. │ price │ Decimal(10, 2) │ Цена блюда в рублях │ │
5. │ description │ Nullable(String) │ Описание блюда и ингредиентов (может отсутствовать) │ │
6. │ calories │ Nullable(UInt32) │ Калорийность блюда в ккал │ │
7. │ is_vegetarian │ Bool │ Флаг вегетарианского блюда (true/false) │ false │
8. │ preparation_time │ UInt16 │ Время приготовления в минутах │ 10 │
└──────────────────┴────────────────────────┴─────────────────────────────────────────────────────┴────────────────────┘
Удаление существующих
ALTER TABLE restaurant_db.menu
(DROP COLUMN is_vegetarian),
(DROP COLUMN description)
Ok.
SELECT
name,
type,
comment,
default_expression
FROM system.columns
WHERE (database = 'restaurant_db') AND (`table` = 'menu')
┌─name─────────────┬─type───────────────────┬─comment───────────────────────────────────┬─default_expression─┐
1. │ dish_id │ UInt32 │ Уникальный идентификатор блюда │ │
2. │ dish_name │ String │ Название блюда │ │
3. │ category │ LowCardinality(String) │ Категория блюда (основное, десерт и т.д.) │ │
4. │ price │ Decimal(10, 2) │ Цена блюда в рублях │ │
5. │ calories │ Nullable(UInt32) │ Калорийность блюда в ккал │ │
6. │ preparation_time │ UInt16 │ Время приготовления в минутах │ 10 │
└──────────────────┴────────────────────────┴───────────────────────────────────────────┴────────────────────┘
5. Выборка из Sample dataset
SELECT
pickup_date,
cab_type,
passenger_count,
trip_distance,
fare_amount,
pickup_ct2010,
dropoff_ct2010
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz', 'TabSeparatedWithNames')
LIMIT 10
┌─pickup_date─┬─cab_type─┬─passenger_count─┬─trip_distance──────┬─fare_amount─┬─pickup_ct2010─┬─dropoff_ct2010─┐
1. │ 2015-07-07 │ yellow │ 2 │ 2.59 │ 14.5 │ Manhattan │ Manhattan │
2. │ 2015-07-07 │ yellow │ 1 │ 2.4 │ 9 │ Queens │ Queens │
3. │ 2015-07-07 │ yellow │ 1 │ 5.13 │ 20 │ Manhattan │ Manhattan │
4. │ 2015-07-07 │ yellow │ 1 │ 1.2 │ 7 │ Manhattan │ Manhattan │
5. │ 2015-07-08 │ yellow │ 5 │ 2.17 │ 8.5 │ Manhattan │ Manhattan │
6. │ 2015-07-08 │ yellow │ 2 │ 5.4 │ 20.5 │ Manhattan │ Brooklyn │
7. │ 2015-07-08 │ yellow │ 1 │ 1.6600000000000001 │ 8.5 │ Manhattan │ Manhattan │
8. │ 2015-07-08 │ yellow │ 1 │ 1.6600000000000001 │ 8 │ Manhattan │ Manhattan │
9. │ 2015-07-08 │ yellow │ 1 │ 1.1 │ 9.5 │ Manhattan │ Manhattan │
10. │ 2015-07-08 │ yellow │ 1 │ 5.14 │ 22.5 │ Brooklyn │ Manhattan │
└─────────────┴──────────┴─────────────────┴────────────────────┴─────────────┴───────────────┴────────────────┘
10 rows in set. Elapsed: 0.926 sec.
6. Материализация таблицы
Здесь в целом следуем инструкциям со страницы New York Taxi Data, однако для дальнейшей практики с партициями сразу делаем таблицу партицированной (по месяцу поездки). Также для большей наглядности отключаем таймаут на удаление неактивных партов (old_parts_lifetime).
CREATE DATABASE nyc_taxi;
CREATE OR REPLACE TABLE nyc_taxi.trips_small
(
`trip_id` UInt32,
`pickup_datetime` DateTime,
`dropoff_datetime` DateTime,
`pickup_longitude` Nullable(Float64),
`pickup_latitude` Nullable(Float64),
`dropoff_longitude` Nullable(Float64),
`dropoff_latitude` Nullable(Float64),
`passenger_count` UInt8,
`trip_distance` Float32,
`fare_amount` Float32,
`extra` Float32,
`tip_amount` Float32,
`tolls_amount` Float32,
`total_amount` Float32,
`payment_type` Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
`pickup_ntaname` LowCardinality(String),
`dropoff_ntaname` LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_datetime)
PRIMARY KEY (pickup_datetime, dropoff_datetime)
SETTINGS old_parts_lifetime = 0;
INSERT INTO nyc_taxi.trips_small
SELECT
trip_id,
pickup_datetime,
dropoff_datetime,
pickup_longitude,
pickup_latitude,
dropoff_longitude,
dropoff_latitude,
passenger_count,
trip_distance,
fare_amount,
extra,
tip_amount,
tolls_amount,
total_amount,
payment_type,
pickup_ntaname,
dropoff_ntaname
FROM s3(
'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz',
'TabSeparatedWithNames'
);
7. Практика с партициями
Начальное состояние
Состояние партиций таблицы сразу после загрузки из s3:
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name─────────┬───rows─┬─size──────┬─active─┐
1. │ trips_small │ 201507 │ 201507_1_1_0 │ 258211 │ 10.51 MiB │ 1 │
2. │ trips_small │ 201507 │ 201507_4_4_0 │ 258275 │ 10.51 MiB │ 1 │
3. │ trips_small │ 201507 │ 201507_7_7_0 │ 61957 │ 2.60 MiB │ 1 │
4. │ trips_small │ 201508 │ 201508_2_2_0 │ 557407 │ 22.45 MiB │ 1 │
5. │ trips_small │ 201508 │ 201508_5_5_0 │ 556737 │ 22.41 MiB │ 1 │
6. │ trips_small │ 201508 │ 201508_8_8_0 │ 556491 │ 22.41 MiB │ 1 │
7. │ trips_small │ 201509 │ 201509_3_3_0 │ 250451 │ 10.20 MiB │ 1 │
8. │ trips_small │ 201509 │ 201509_6_6_0 │ 250229 │ 10.20 MiB │ 1 │
9. │ trips_small │ 201509 │ 201509_9_9_0 │ 250559 │ 10.21 MiB │ 1 │
└─────────────┴───────────┴──────────────┴────────┴───────────┴────────┘
Вручную инициируем слияние:
OPTIMIZE TABLE nyc_taxi.trips_small FINAL
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name─────────┬────rows─┬─size──────┬─active─┐
1. │ trips_small │ 201507 │ 201507_1_7_1 │ 578443 │ 23.22 MiB │ 1 │
2. │ trips_small │ 201508 │ 201508_2_8_1 │ 1670635 │ 65.27 MiB │ 1 │
3. │ trips_small │ 201509 │ 201509_3_9_1 │ 751239 │ 30.04 MiB │ 1 │
└─────────────┴───────────┴──────────────┴─────────┴───────────┴────────┘
Отключение партиции
Проверяем общее количество записей по месяцам до отсоединения партиции:
WITH toYYYYMM(pickup_datetime) AS month
SELECT
toString(month),
count(*) AS monthlyRides
FROM nyc_taxi.trips_small
GROUP BY month
ORDER BY month ASC
┌─toString(month)─┬─monthlyRides─┐
1. │ 201507 │ 578443 │
2. │ 201508 │ 1670635 │
3. │ 201509 │ 751239 │
└─────────────────┴──────────────┘
Отсоединяем партицию за август 2015г. и повторяем запрос:
ALTER TABLE nyc_taxi.trips_small
(DETACH PARTITION 201508)
Ok.
WITH toYYYYMM(pickup_datetime) AS month
SELECT
toString(month),
count(*) AS monthlyRides
FROM nyc_taxi.trips_small
GROUP BY month
ORDER BY month ASC
┌─toString(month)─┬─monthlyRides─┐
1. │ 201507 │ 578443 │
2. │ 201509 │ 751239 │
└─────────────────┴──────────────┘
Дополнительно подтверждаем, что партиция 201508 действительно была отсоединена:
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name─────────┬───rows─┬─size──────┬─active─┐
1. │ trips_small │ 201507 │ 201507_1_7_1 │ 578443 │ 23.22 MiB │ 1 │
2. │ trips_small │ 201509 │ 201509_3_9_1 │ 751239 │ 30.04 MiB │ 1 │
└─────────────┴───────────┴──────────────┴────────┴───────────┴────────┘
Подключение партиции
Создаём временную таблицу:
CREATE OR REPLACE TABLE nyc_taxi.tmp_trips AS nyc_taxi.trips_small
Ok.
Переносим отсоединённую партицию во временную таблицу перемещая директорию на диске (через shell):
root@3329e3f61ff3:~# mv \
/var/lib/clickhouse/data/nyc_taxi/trips_small/detached/201508_2_8_1/ \
/var/lib/clickhouse/data/nyc_taxi/tmp_trips/detached/
Подключаем партицию во временную таблицу:
ALTER TABLE nyc_taxi.tmp_trips
(ATTACH PARTITION '201508')
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name─────────┬────rows─┬─size──────┬─active─┐
1. │ tmp_trips │ 201508 │ 201508_1_1_0 │ 1670635 │ 65.27 MiB │ 1 │
2. │ trips_small │ 201507 │ 201507_1_7_1 │ 578443 │ 23.22 MiB │ 1 │
3. │ trips_small │ 201509 │ 201509_3_9_1 │ 751239 │ 30.04 MiB │ 1 │
└─────────────┴───────────┴──────────────┴─────────┴───────────┴────────┘
Изменяем данные во временной таблице. Удаляем поездки в/из аэропорт(а):
ALTER TABLE nyc_taxi.tmp_trips
(DELETE WHERE (pickup_ntaname IN ['Airport']) OR (dropoff_ntaname IN ['Airport']))
Ok.
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name───────────┬────rows─┬─size──────┬─active─┐
1. │ tmp_trips │ 201508 │ 201508_1_1_0_2 │ 1551802 │ 60.31 MiB │ 1 │
2. │ trips_small │ 201507 │ 201507_1_7_1 │ 578443 │ 23.22 MiB │ 1 │
3. │ trips_small │ 201509 │ 201509_3_9_1 │ 751239 │ 30.04 MiB │ 1 │
└─────────────┴───────────┴────────────────┴─────────┴───────────┴────────┘
Переносим изменённые данные из партиции временной таблицы обратно в основную таблицу (через архив):
root@3329e3f61ff3:~# tar -czvf updated_trips_201508.tgz \
-C /var/lib/clickhouse/data/nyc_taxi/tmp_trips/ 201508_3_3_0
201508_3_3_0/
201508_3_3_0/primary.cidx
201508_3_3_0/trip_id.bin
201508_3_3_0/trip_id.cmrk2
. . . . .
201508_3_3_0/metadata_version.txt
root@3329e3f61ff3:~# tar -xzvf updated_trips_201508.tgz \
-C /var/lib/clickhouse/data/nyc_taxi/trips_small/detached/
201508_3_3_0/
201508_3_3_0/primary.cidx
201508_3_3_0/trip_id.bin
201508_3_3_0/trip_id.cmrk2
. . . . .
201508_3_3_0/metadata_version.txt
Подключаем изменённую партицию к основной таблице:
ALTER TABLE nyc_taxi.trips_small
(ATTACH PARTITION '201508')
Ok.
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name───────────┬────rows─┬─size──────┬─active─┐
1. │ tmp_trips │ 201508 │ 201508_3_3_0 │ 1551802 │ 60.31 MiB │ 1 │
2. │ trips_small │ 201507 │ 201507_1_7_1 │ 578443 │ 23.22 MiB │ 1 │
3. │ trips_small │ 201508 │ 201508_10_10_0 │ 1551802 │ 60.31 MiB │ 1 │
4. │ trips_small │ 201509 │ 201509_3_9_1 │ 751239 │ 30.04 MiB │ 1 │
└─────────────┴───────────┴────────────────┴─────────┴───────────┴────────┘
Удаляем партицию временной таблицы:
ALTER TABLE nyc_taxi.tmp_trips
(DROP PARTITION '201508')
Ok.
SELECT
`table`,
partition,
name,
rows,
formatReadableSize(bytes_on_disk) AS size,
active
FROM system.parts
WHERE database = 'nyc_taxi'
┌─table───────┬─partition─┬─name───────────┬────rows─┬─size──────┬─active─┐
1. │ trips_small │ 201507 │ 201507_1_7_1 │ 578443 │ 23.22 MiB │ 1 │
2. │ trips_small │ 201508 │ 201508_10_10_0 │ 1551802 │ 60.31 MiB │ 1 │
3. │ trips_small │ 201509 │ 201509_3_9_1 │ 751239 │ 30.04 MiB │ 1 │
└─────────────┴───────────┴────────────────┴─────────┴───────────┴────────┘
Как итог данные в партиции 201508 основной таблицы были изменены:
WITH toYYYYMM(pickup_datetime) AS month
SELECT
toString(month),
count(*) AS monthlyRides
FROM nyc_taxi.trips_small
GROUP BY month
ORDER BY month ASC
┌─toString(month)─┬─monthlyRides─┐
1. │ 201507 │ 578443 │
2. │ 201508 │ 1551802 │
3. │ 201509 │ 751239 │
└─────────────────┴──────────────┘