CLICKHOUSE — различия между версиями
Root (обсуждение | вклад) |
Root (обсуждение | вклад) |
||
(не показано 35 промежуточных версий этого же участника) | |||
Строка 5: | Строка 5: | ||
DESCRIBE TABLE stat - вывести описание столбцов таблицы | DESCRIBE TABLE stat - вывести описание столбцов таблицы | ||
+ | |||
+ | SELECT * FROM system.parts - все партиции для всех таблиц | ||
+ | SELECT partition, name, table FROM system.parts WHERE active - более лаконичная форма запроса исключающая неактивные части | ||
Строка 12: | Строка 15: | ||
Бекап таблицы в Native формате | Бекап таблицы в Native формате | ||
− | |||
− | |||
− | |||
Dump of metadata | Dump of metadata | ||
− | clickhouse-client -h 127.0.0.1 --database=<font color=blue>DATABASE</font> --query="SHOW CREATE TABLE <font color=blue>TABLE</font>" --format=TabSeparatedRaw > meta.sql | + | clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password --database=<font color=blue>DATABASE</font> --query="SHOW CREATE TABLE <font color=blue>TABLE</font>" --format=TabSeparatedRaw > meta.sql |
+ | Dump of data | ||
+ | clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password --query="SELECT * FROM <font color=blue>DATABASE.TABLE</font> FORMAT Native" > dump.native | ||
+ | |||
Restore of metadata | Restore of metadata | ||
− | clickhouse-client -- | + | clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password < meta.sql |
Restore of data | Restore of data | ||
− | clickhouse-client -- | + | clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password --query="INSERT INTO <font color=blue>DATABASE.TABLE</font> FORMAT Native" < dump.native |
− | |||
https://stackoverflow.com/questions/57005443/clickhouse-how-to-take-incremental-backup-of-clickhouse-db-for-both-partitoned | https://stackoverflow.com/questions/57005443/clickhouse-how-to-take-incremental-backup-of-clickhouse-db-for-both-partitoned | ||
Строка 32: | Строка 34: | ||
− | + | Удаление партиции.<br> | |
− | + | Сначала находим нужную партицию | |
− | + | SELECT * FROM system.parts LIMIT <font color=blue>1</font> FORMAT Vertical; | |
− | + | ||
− | + | ||
− | + | Перемещаем заданную партицию в директорию detached. Сервер не будет знать об этой партиции до тех пор, пока не будет выполнен запрос ATTACH. | |
− | + | ALTER TABLE <font color=blue>DATABASE.TABLE</font> DETACH PARTITION '<font color=blue>PARTITION</font>' | |
− | + | ||
− | + | ||
− | + | Удаляем партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут(SETTINGS allow_drop_detached=1 если в конфиге запрещено удалять) | |
− | + | ALTER TABLE <font color=blue>DATABASE.TABLE</font> DROP PARTITION '<font color=blue>PARTITION</font>' | |
− | + | ||
− | + | ||
− | + | Удаляет из detached кусок или все куски, принадлежащие партиции. | |
− | + | ALTER TABLE <font color=blue>DATABASE.TABLE</font> DROP DETACHED PARTITION '<font color=blue>PARTITION</font>' SETTINGS allow_drop_detached=1; | |
− | + | https://stackoverflow.com/questions/64382732/can-i-delete-the-detached-folder-on-clickhouse-data<br> | |
− | + | https://stackoverflow.com/questions/61950278/how-to-enable-allow-drop-detached-in-system-settings | |
− | + | ||
− | + | ||
− | + | Размер таблиц в базе | |
− | + | USE <font color=blue>DATABASE</font> | |
− | + | ||
− | + | 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 -u root --ask-password --query="SELECT * FROM buymedia_biz.statistic where event_date between '2023-03-01' and '2023-03-31' FORMAT Native" | pigz > /srv/storage/01-r1tb4/buymedia_biz--statistic--202303.native.gz | ||
− | |||
https://clickhouse.com/docs/ru/sql-reference/<br> | https://clickhouse.com/docs/ru/sql-reference/<br> | ||
− | https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0 | + | https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015<br> |
− | https:// | + | |
+ | Backup<br> | ||
+ | https://stupin.su/wiki/clickhouse_move_db/<br> | ||
+ | https://habr.com/ru/post/569282/<br> | ||
+ | https://it-lux.ru/clickhouse-backup-and-recovery/<br> | ||
+ | https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0<br> | ||
+ | |||
+ | MergeTree<br> | ||
+ | https://clickhouse.com/docs/ru/engines/table-engines/mergetree-family/mergetree<br> | ||
+ | https://programming.vip/docs/clickhouse-multi-disk-storage.html<br> | ||
+ | https://russianblogs.com/article/87522205920/ |
Текущая версия на 11:15, 25 декабря 2023
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 -u USER --ask-password --database=DATABASE --query="SHOW CREATE TABLE TABLE" --format=TabSeparatedRaw > meta.sql Dump of data clickhouse-client -h 127.0.0.1 -u USER --ask-password --query="SELECT * FROM DATABASE.TABLE FORMAT Native" > dump.native Restore of metadata clickhouse-client -h 127.0.0.1 -u USER --ask-password < meta.sql Restore of data clickhouse-client -h 127.0.0.1 -u USER --ask-password --query="INSERT INTO DATABASE.TABLE FORMAT Native" < dump.native
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;
Перемещаем заданную партицию в директорию detached. Сервер не будет знать об этой партиции до тех пор, пока не будет выполнен запрос ATTACH.
ALTER TABLE DATABASE.TABLE DETACH PARTITION 'PARTITION'
Удаляем партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут(SETTINGS allow_drop_detached=1 если в конфиге запрещено удалять)
ALTER TABLE DATABASE.TABLE DROP PARTITION 'PARTITION'
Удаляет из detached кусок или все куски, принадлежащие партиции.
ALTER TABLE DATABASE.TABLE DROP DETACHED PARTITION 'PARTITION' SETTINGS allow_drop_detached=1;
https://stackoverflow.com/questions/64382732/can-i-delete-the-detached-folder-on-clickhouse-data
https://stackoverflow.com/questions/61950278/how-to-enable-allow-drop-detached-in-system-settings
Размер таблиц в базе
USE DATABASE 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 -u root --ask-password --query="SELECT * FROM buymedia_biz.statistic where event_date between '2023-03-01' and '2023-03-31' FORMAT Native" | pigz > /srv/storage/01-r1tb4/buymedia_biz--statistic--202303.native.gz
https://clickhouse.com/docs/ru/sql-reference/
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015
Backup
https://stupin.su/wiki/clickhouse_move_db/
https://habr.com/ru/post/569282/
https://it-lux.ru/clickhouse-backup-and-recovery/
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0
MergeTree
https://clickhouse.com/docs/ru/engines/table-engines/mergetree-family/mergetree
https://programming.vip/docs/clickhouse-multi-disk-storage.html
https://russianblogs.com/article/87522205920/