CLICKHOUSE — различия между версиями

Материал из megapuper
Перейти к: навигация, поиск
 
(не показаны 23 промежуточные версии этого же участника)
Строка 5: Строка 5:
  
 
  DESCRIBE TABLE stat - вывести описание столбцов таблицы
 
  DESCRIBE TABLE stat - вывести описание столбцов таблицы
 +
 +
SELECT * FROM system.parts - все партиции для всех таблиц
 +
SELECT partition, name, table FROM system.parts WHERE active - более лаконичная форма запроса исключающая неактивные части
  
  
Строка 13: Строка 16:
 
Бекап таблицы в 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
 
  Dump of data
  clickhouse-client -h 127.0.0.1 --query="SELECT * FROM <font color=blue>DATABASE.TABLE</font> FORMAT Native" > dump.native  
+
  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 --user <font color=blue>USER</font> --ask-password < meta.sql
+
  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 --user <font color=blue>USER</font> --ask-password --query="INSERT INTO <font color=blue>DATABASE.TABLE</font> FORMAT Native" < dump.native
+
  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
  
Строка 31: Строка 34:
  
  
Удаление партиции. Сначала находим нужную, делаем ей детач, затем удаляем
+
Удаление партиции.<br>
  SELECT * FROM system.parts LIMIT 1 FORMAT Vertical;
+
Сначала находим нужную партицию
  ALTER TABLE statistic DETACH PARTITION '202011'
+
  SELECT * FROM system.parts LIMIT <font color=blue>1</font> FORMAT Vertical;
  ALTER TABLE statistic DROP PARTITION '202011'
+
 
 +
 
 +
Перемещаем заданную партицию в директорию 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
 
  SELECT
 
     parts.*,
 
     parts.*,
Строка 73: Строка 91:
  
  
Размеры партиции в базе по месяцам, отсортированные по годам/месяцам
+
Размеры партиций в базе по месяцам, отсортированные по годам/месяцам
 
  SELECT
 
  SELECT
 
     toYear(max_date) AS year,
 
     toYear(max_date) AS year,
Строка 96: Строка 114:
  
  
Размер партиций всех таблиц за промежуток времени
+
Размер партиций всех баз.таблиц за промежуток времени
 
  SELECT
 
  SELECT
 
     database,
 
     database,
Строка 114: Строка 132:
  
  
  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
+
  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://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015<br>
 +
 +
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>
 
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0<br>
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015
+
 
 +
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

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;


Перемещаем заданную партицию в директорию 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/