CLICKHOUSE

SHOW DATABASES - выводит список всех баз данных
SHOW TABLES - выводит список таблиц выбранной базы
SHOW PROCESSLIST - выводит список запросов выполняющихся в выбранной базе
SHOW GRANTS - выводит список привилегий пользователя

DESCRIBE TABLE stat - вывести описание столбцов таблицы

SELECT * FROM system.parts - все партиции для всех таблиц
SELECT partition, name, table FROM system.parts WHERE active - более лаконичная форма запроса исключающая неактивные части

Права админа

https://stackoverflow.com/questions/64166492/how-to-setup-an-admin-account-for-clickhouse

Бекап таблицы в Native формате

Dump of metadata
clickhouse-client -h 127.0.0.1 --database=DATABASE --query="SHOW CREATE TABLE TABLE" --format=TabSeparatedRaw > meta.sql

Dump of data
clickhouse-client -h 127.0.0.1 --query="SELECT * FROM DATABASE.TABLE FORMAT Native" > dump.native 
 
Restore of metadata
clickhouse-client --user USER --ask-password < meta.sql

Restore of data
clickhouse-client --user USER --ask-password --query="INSERT INTO DATABASE.TABLE FORMAT Native" < dump.native

https://stackoverflow.com/questions/57005443/clickhouse-how-to-take-incremental-backup-of-clickhouse-db-for-both-partitoned

SELECT query_id,user,elapsed FROM system.processes; - выборка по id, пользователю и времени выполнения
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90' - принудительно останавливает все запросы с указанным query_id
KILL QUERY WHERE elapsed > 100 - принудительно останавливает все запросы с временем выполнения больше 100

Удаление партиции. Сначала находим нужную, делаем ей детач, затем удаляем

SELECT * FROM system.parts LIMIT 1 FORMAT Vertical;
ALTER TABLE statistic DETACH PARTITION '202011'
ALTER TABLE statistic DROP PARTITION '202011'

Размер таблиц в базе

SELECT
   parts.*,
   columns.compressed_size,
   columns.uncompressed_size,
   columns.ratio
FROM 
(
   SELECT
       table,
       formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
       formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
       sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS ratio
   FROM system.columns
   WHERE database = currentDatabase()
   GROUP BY table
) AS columns
RIGHT JOIN 
(
   SELECT
       table,
       sum(rows) AS rows,
       max(modification_time) AS latest_modification,
       formatReadableSize(sum(bytes)) AS disk_size,
       formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size,
       any(engine) AS engine,
       sum(bytes) AS bytes_size
   FROM system.parts
   WHERE active AND (database = currentDatabase())
   GROUP BY
       database,
       table
) AS parts ON columns.table = parts.table
ORDER BY parts.bytes_size DESC

Размеры партиции в базе по месяцам, отсортированные по годам/месяцам

SELECT
   toYear(max_date) AS year,
   toMonth(max_date) AS month,
   table,
   sum(rows) AS rows,
   max(modification_time) AS latest_modification,
   formatReadableSize(sum(bytes_on_disk)) AS disk_size,
   formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size,
   formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
   formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
   any(engine) AS engine
FROM system.parts
WHERE active AND (database = 'buymedia_biz') AND (table = 'statistic')
GROUP BY
   table,
   year,
   month
ORDER BY
   year ASC,
   month ASC

Размер партиций всех таблиц за промежуток времени

SELECT
   database,
   table,
   sum(rows) AS rows,
   max(modification_time) AS latest_modification,
   formatReadableSize(sum(bytes_on_disk)) AS disk_size,
   formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size,
   formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
   formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
   any(engine) AS engine
FROM system.parts
WHERE active AND ((max_date >= '2021-01-01') AND (max_date <= '2021-12-31'))
GROUP BY
   database,
   table

clickhouse-client -h 127.0.0.1 --query="SELECT * FROM pushads_biz.stat where datetime between '2021-03-01' and '2021-03-31' FORMAT Native" | pigz > /srv/storage/01-r1tb4/pushads_biz--stat--202103.native.gz

https://clickhouse.com/docs/ru/sql-reference/
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015