Управление данными

INSERT, UPDATE, DELETE, мутации, TTL, сжатие, кодировки, оптимизация партиций

#Результат урока

Вы спроектируете пакетную загрузку с повторными попытками и регулированием потока при перегрузке, а также выберете безопасный механизм изменения и удаления данных.

#INSERT — вставка данных

#Пакетная вставка

-- Эффективная пакетная вставка
INSERT INTO events (event_time, user_id, event_type, value)
VALUES 
    (now(), 1, 'click', 1.0),
    (now(), 2, 'view', 2.0),
    (now(), 3, 'purchase', 100.0);

Рекомендации:

Для синхронных вставок начинайте с 1 000 строк; часто эффективны пакеты 10 000–100 000 строк
Подбирайте размер по задержке, памяти клиента и пропускной способности, а не по универсальному максимуму
Не создавайте по одной части на строку: контролируйте частоту вставок и число активных частей

#Асинхронные вставки

Начиная с ClickHouse 26.3 LTS асинхронные вставки включены по умолчанию: сервер объединяет совместимые мелкие запросы перед созданием частей. Явные настройки полезны в примере и при поддержке более ранних версий:

INSERT INTO events
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (now(), 42, 'view', 1.0);

С wait_for_async_insert = 1 клиент получает успех после сброса буфера и видит ошибку записи. Режим без ожидания подтверждает приём раньше и требует явно согласованного окна потери данных.

Наблюдайте за буфером, а не считайте его «бесплатным Kafka»:

SELECT
    event_time,
    query,
    status,
    rows,
    bytes,
    flush_time,
    exception
FROM system.asynchronous_insert_log
ORDER BY event_time DESC
LIMIT 20;

Асинхронная вставка решает пакетирование на сервере, но не определяет бизнес-идемпотентность. Клиент всё равно должен понимать, как повторить неоднозначно завершившийся запрос и как обнаружить логический дубль.

#Вставка из SELECT

-- Вставка результатов запроса
INSERT INTO events_daily
SELECT 
    toDate(event_time) AS date,
    event_type,
    count() AS events
FROM events
GROUP BY date, event_type;

-- Вставка с распределённой таблицы на локальную
INSERT INTO events_local
SELECT * FROM events_all
WHERE event_date = '2026-03-01';

#Вставка из файла

# Из CSV файла
clickhouse-client --query "INSERT INTO events FORMAT CSV" < events.csv

# Из TSV файла
clickhouse-client --query "INSERT INTO events FORMAT TabSeparated" < events.tsv

# Из JSON
clickhouse-client --query "INSERT INTO events FORMAT JSONEachRow" < events.json

# С сжатием gzip
gzip -dc events.csv.gz | clickhouse-client --query "INSERT INTO events FORMAT CSV"

#Форматы данных

-- Текстовые форматы
FORMAT TabSeparated      -- Табуляция
FORMAT CSV               -- CSV
FORMAT TSVWithNames      -- TSV с заголовком

-- Бинарные форматы
FORMAT Native            -- Бинарный ClickHouse
FORMAT RowBinary         -- Бинарный по строкам

-- JSON форматы
FORMAT JSONEachRow       -- JSON по строкам
FORMAT JSON              -- JSON массив
FORMAT JSONCompactEachRow-- JSON массивы по строкам

#UPDATE и DELETE — мутации

#ALTER UPDATE

-- Обновление по условию
ALTER TABLE users 
UPDATE is_active = 0, updated_at = now()
WHERE last_login < '2025-01-01';

-- Обновление нескольких колонок
ALTER TABLE orders
UPDATE 
    status = 'cancelled',
    cancelled_at = now(),
    cancel_reason = 'customer_request'
WHERE order_id IN (1, 2, 3);

Важно:

UPDATE выполняется асинхронно фоном
Не блокирует таблицу для чтения/записи
Может выполняться долго для больших таблиц

#ALTER DELETE

-- Удаление по условию
ALTER TABLE events 
DELETE WHERE event_time < '2025-01-01';

-- Удаление конкретных записей
ALTER TABLE users 
DELETE WHERE user_id IN (1, 2, 3);

Важно:

DELETE не удаляет данные немедленно
Создаётся новая версия частей без удалённых строк
Старые части удаляются фоном после merge

#Мониторинг мутаций

-- Статус мутаций
SELECT 
    database,
    table,
    mutation_id,
    command,
    create_time,
    block_numbers.partition_id,
    block_numbers.number
FROM system.mutations
WHERE table = 'users'
ORDER BY create_time DESC;

-- Прогресс мутации
SELECT 
    mutation_id,
    command,
    is_done,
    latest_failed_part,
    latest_fail_time,
    latest_fail_reason
FROM system.mutations
WHERE table = 'users';

Поля:

is_done = 1 — мутация завершена
latest_fail_reason — причина ошибки если есть

#Отмена мутации

-- Отмена незавершённой мутации
ALTER TABLE users KILL MUTATION WHERE mutation_id = 'mutation_id';

-- Отмена по ID
ALTER TABLE users KILL MUTATION '20260310_123456_789';

#TTL — время жизни данных

#Базовый TTL

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    value      Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 1 YEAR;  -- Удаление через год

#TTL с действиями

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    value      Decimal(10, 2),
    raw_data   String
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id)
TTL 
    event_time + INTERVAL 1 MONTH DELETE,           -- Удалить через месяц
    event_time + INTERVAL 7 DAY TO VOLUME 'cold',   -- Переместить на холодный диск
    event_time + INTERVAL 3 DAY TO DISK 'archive';  -- Архивировать

-- TTL для конкретных колонок
TTL 
    event_time + INTERVAL 1 YEAR,
    raw_data + INTERVAL 1 MONTH DELETE;  -- Только raw_data

#TTL с агрегацией

CREATE TABLE metrics
(
    timestamp DateTime,
    host      String,
    metric    String,
    value     Float64
)
ENGINE = MergeTree()
ORDER BY (timestamp, host, metric)
TTL 
    timestamp + INTERVAL 1 DAY 
    GROUP BY host, metric
    SET value = avg(value);  -- Агрегация через день

Важно:

TTL выполняется фоном при merge частей
Не гарантирует немедленное удаление
Можно выполнить вручную: ALTER TABLE ... MATERIALIZE TTL

#Мониторинг TTL

-- Части с истекающим TTL
SELECT 
    table,
    partition,
    name,
    min_date,
    max_date,
    bytes_on_disk
FROM system.parts
WHERE table = 'events'
  AND active = 1
ORDER BY min_date;

-- История TTL операций
SELECT 
    event_date,
    query,
    result
FROM system.query_log
WHERE query LIKE '%TTL%'
  AND event_date = today();

#Сжатие данных

#Кодеки сжатия

CREATE TABLE compressed_data
(
    id            UInt64,
    -- LZ4 (по умолчанию, быстрое)
    data_lz4      String CODEC(LZ4),
    
    -- ZSTD (лучшее сжатие, медленнее)
    data_zstd     String CODEC(ZSTD(3)),  -- Уровень 1-9
    
    -- Delta для возрастающих значений
    timestamp     UInt64 CODEC(Delta, ZSTD(1)),
    
    -- Dictionary для строк с малым числом значений
    country       String CODEC(ZSTD(1)),
    
    -- None для уже сжатых данных
    compressed    String CODEC(NONE)
)
ENGINE = MergeTree()
ORDER BY id;

#Типы кодеков

Кодек	Описание	Когда использовать
LZ4	Быстрое сжатие по умолчанию	Общие случаи
ZSTD(n)	Лучшее сжатие, уровень 1-9	Архивные данные, холодное хранение
Delta	Дельта-кодирование	Возрастающие значения (ID, timestamp)
Gorilla	XOR-кодирование для float	Временные ряды с плавными изменениями
T64	64-битное сжатие	Целые числа с малым диапазоном
NONE	Без сжатия	Уже сжатые данные

#Пример: эффективное сжатие

CREATE TABLE optimized_metrics
(
    timestamp   DateTime CODEC(Delta(4), ZSTD(1)),
    host_id     UInt32 CODEC(ZSTD(1)),
    metric_name LowCardinality(String) CODEC(ZSTD(1)),
    value       Float64 CODEC(Gorilla),
    tags        String CODEC(LZ4)
)
ENGINE = MergeTree()
ORDER BY (timestamp, host_id, metric_name);

#ALTER TABLE — изменение структуры

#Добавление колонки

-- Добавить колонку в конец
ALTER TABLE events ADD COLUMN country String;

-- Добавить колонку после другой
ALTER TABLE events ADD COLUMN city String AFTER country;

-- Добавить колонку в начало
ALTER TABLE events ADD COLUMN event_id UInt64 FIRST;

-- Добавить колонку с выражением по умолчанию
ALTER TABLE events ADD COLUMN 
    event_date Date DEFAULT toDate(event_time);

#Удаление колонки

ALTER TABLE events DROP COLUMN temp_column;

#Изменение типа колонки

-- Изменение типа (может быть дорогой операцией)
ALTER TABLE events MODIFY COLUMN value Decimal(12, 4);

-- Изменение выражения по умолчанию
ALTER TABLE events MODIFY COLUMN 
    event_date Date DEFAULT toDate(event_time);

-- Изменение кодека сжатия
ALTER TABLE events MODIFY COLUMN 
    data String CODEC(ZSTD(5));

#Изменение движка таблицы

-- Изменение настроек движка
ALTER TABLE events MODIFY SETTING 
    index_granularity = 4096;

-- Изменение TTL
ALTER TABLE events MODIFY TTL 
    event_time + INTERVAL 6 MONTH;

#Оптимизация партиций

#OPTIMIZE TABLE

-- Слияние всех частей в партиции
OPTIMIZE TABLE events;

-- Слияние конкретной партиции
OPTIMIZE TABLE events PARTITION ('202603');

-- Принудительное слияние всех частей (включая уже слитые)
OPTIMIZE TABLE events FINAL;

-- Слияние с дедупликацией (для ReplacingMergeTree)
OPTIMIZE TABLE events FINAL;

Важно:

FINAL может быть дорогим; не делайте его постоянной частью рабочего запроса без измерений
ClickHouse автоматически выполняет merge фоном
Использовать OPTIMIZE только при необходимости

#Detach/Attach партиции

-- Отсоединить партицию (данные остаются на диске)
ALTER TABLE events DETACH PARTITION ('202501');

-- Присоединить партицию обратно
ALTER TABLE events ATTACH PARTITION ('202501');

Применение:

Временное исключение данных из запросов
Перемещение партиций между таблицами

#Drop партиции

-- Удалить партицию (быстрее чем DELETE)
ALTER TABLE events DROP PARTITION ('202501');

-- Удалить несколько партиций
ALTER TABLE events DROP PARTITION ('202501');
ALTER TABLE events DROP PARTITION ('202502');

Преимущества перед DELETE:

Мгновенное удаление
Не создаёт мутацию
Освобождает место сразу

#Freeze партиции (бэкап)

-- Создать snapshot партиции
ALTER TABLE events FREEZE PARTITION ('202603');

-- Snapshot хранится в /var/lib/clickhouse/shadow/

#Транзакции (экспериментально)

-- Начать транзакцию (экспериментальная функция)
BEGIN TRANSACTION;

INSERT INTO events VALUES (...);
INSERT INTO users VALUES (...);

-- Закоммитить
COMMIT;

-- Или откатить
ROLLBACK;

Ограничения:

Только для ReplicatedMergeTree
Требует включения в конфигурации
Не все операции поддерживаются

#Рабочие правила

#1. Пакетная вставка

-- Плохо: много мелких вставок
for row in rows:
    INSERT INTO events VALUES (...);

-- Хорошо: одна пакетная вставка
INSERT INTO events VALUES (...), (...), ...;

#2. Использование TTL

-- Автоматическое удаление старых данных
CREATE TABLE events
ENGINE = MergeTree()
ORDER BY event_time
TTL event_time + INTERVAL 1 YEAR;

#3. Мутации для массовых изменений

-- Для массовых UPDATE/DELETE использовать ALTER
ALTER TABLE users UPDATE is_active = 0 WHERE ...;

-- Не использовать UPDATE в цикле

#4. Drop партиции вместо DELETE

-- Быстрое удаление старых данных
ALTER TABLE events DROP PARTITION ('202501');

-- Вместо:
ALTER TABLE events DELETE WHERE event_time < '2025-02-01';

#5. Мониторинг мутаций

-- Проверка зависших мутаций
SELECT 
    table,
    command,
    create_time,
    is_done
FROM system.mutations
WHERE is_done = 0
  AND create_time < now() - INTERVAL 1 HOUR;

Кодек

Описание

Когда использовать

LZ4

Быстрое сжатие по умолчанию

Общие случаи

ZSTD(n)

Лучшее сжатие, уровень 1-9

Архивные данные, холодное хранение

Delta

Дельта-кодирование

Возрастающие значения (ID, timestamp)

Gorilla

XOR-кодирование для float

Временные ряды с плавными изменениями

T64

64-битное сжатие

Целые числа с малым диапазоном

NONE

Без сжатия

Уже сжатые данные