阿里云文档地址： https://help.aliyun.com/zh/clickhouse/user-guide/tiered-storage-of-hot-data-and-cold-data#section-tn5-sd9-0gx

SELECT 
    *
FROM system.storage_policies
SELECT
  name,
  path,
  formatReadableSize (free_space) AS free,
  formatReadableSize (total_space) AS total,
  formatReadableSize (keep_free_space) AS reserved
FROM
  system.disks;

SELECT
table AS `表名`,
sum(rows) AS `总行数`,
formatReadableSize (sum(data_uncompressed_bytes)) AS `原始大小`,
formatReadableSize (sum(data_compressed_bytes)) AS `压缩大小`,
round(
  (
    sum(data_compressed_bytes) / sum(data_uncompressed_bytes)
  ) * 100,
  0
) AS `压缩率`
FROM
  system.parts
GROUP BY
table

-- 查选一定时间范围内查询耗时大于100毫秒的语句
select left(query, 50) as sql, count() as queryNum, sum(query_duration_ms) as totalTime, totalTime/queryNum as avgTime from system.query_log ql where event_time > toDateTime('2022-10-17 19:28:00') and event_time < toDateTime('2022-10-17 19:32:00') AND query_duration_ms > 100 group by sql order by queryNum desc

-- 查询具体语句
select query from system.query_log where event_time > toDateTime('2022-10-17 19:28:00') and event_time < toDateTime('2022-10-17 19:32:00') and query like '%select * from xxxx%' limit 5;

-- 查询执行最久的前10条
select query,query_duration_ms from system.query_log where event_time > toDateTime('2022-10-17 19:28:00') and event_time < toDateTime('2022-10-17 19:32:00') order by query_duration_ms desc limit 10;

-- 查看表空间全库：

SELECT sum(rows) AS `总行数`, formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`, formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`, round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率` FROM system.parts
-- 查看表空间全库：
SELECT table AS `表名`, sum(rows) AS `总行数`, formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`, formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`, round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率` FROM system.parts WHERE table IN ('temp_1') GROUP BY table

-- 查看空间占用
SELECT name, path, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total, formatReadableSize(keep_free_space) AS reserved FROM system.disks;

-- 检查存储位置
SELECT
  `partition`,
  `name`,
  `database`,
  `table`,
  `disk_name`
FROM
  system.parts
WHERE
  disk_name = 'default'

-- 修改储存
ALTER TABLE default.default_table MODIFY TTL timestamp TO DISK 'default', timestamp + toIntervalMonth(2) TO DISK 'default2', timestamp + toIntervalMonth(4) TO DISK 'default3'


SELECT *
FROM system.processes;

KILL QUERY WHERE `user` = 'default';
KILL QUERY WHERE query_id = 'xxxxxx-xxxx-xxxx-xxxx-xxxx'

clickhouse-client --query="SELECT * FROM database.table WHERE ..." --format CSV > data.csv

clickhouse-client --query="INSERT INTO database.table FORMAT CSV" --input_format_skip_unknown_fields=1 < data.csv