1、慢查詢監控

SELECTuser,formatDateTime(query_start_time, '%Y%m%d %T') AS start_time,query_duration_ms / 1000 AS query_duration_s,query,memory_usage / 1024 / 1024 / 1024 AS memory_usage_g,result_rows ,formatReadableSize(result_bytes) AS result_bytes,read_rows ,read_bytes / 1024 / 1024 /1024 AS read_bytes_g,written_rows ,written_bytes / 1024 / 1024 /1024 AS written_bytes_gFROM system.query_log WHERE type = 2 and query_start_time>=today() ORDER BY query_duration_s DESC LIMIT 10

2、清空查詢日志

ALTER table system.query_log delete WHERE 1=1;

3、查看數據壓縮率

selectsum(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) "壓縮率" from system.parts;

4、查看表大小

SELECTtable,formatReadableSize(sum(data_compressed_bytes)) AS compressed_size ,formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_bytes FROMsystem.parts WHEREactiveAND (table LIKE 'data_%')GROUP BY table order byuncompressed_bytes desc ;

5、 查看當前連接數

SELECT * FROM system.metrics WHERE metric LIKE '%Connection';

6、手動合并分區

OPTIMIZE TABLE table [PARTITION partition] [FINAL]

7、添加或者更新TTL

ALTER TABLE table1 MODIFY TTL toDate(time) + toIntervalDay(10)

8、添加多個列

ALTER TABLE table1 ADD COLUMN longcol1 Int64 AFTER col111, ADD COLUMN longcol2 Int64 AFTER longcol1;

9、卸載裝載分區

ALTER TABLE table1 DETACH PARTITION '1629111600'; ALTER TABLE table1 ATTACH PARTITION '1629111600';

10、查看表資源占用情況

SELECT database,table,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., 2) AS `壓縮率/%` FROM system.parts GROUP BY database,table ORDER BY database ASC

11、個列字段占用空間統計

SELECT database, table, column, any(type), sum(column_data_compressed_bytes) AS compressed, sum(column_data_uncompressed_bytes) AS uncompressed, round(uncompressed / compressed, 2) AS ratio, compressed / sum(rows) AS bpr, sum(rows) FROM system.parts_columns WHERE active AND database != 'system' GROUP BY database, table, column ORDER BY database ASC, table ASC, column ASC

12、查看后臺執行的更新語句

SELECTdatabase,table,mutation_id,command,create_time,parts_to_do_names,parts_to_do,latest_fail_reason FROMsystem.mutations whereis_done <> 1

13、查看正在執行的查詢語句

SELECTquery_id,user,address,elapsed,query FROMsystem.processes ORDER BYquery_id ASC

14、kill指定的查詢語句

KILL QUERY WHERE query_id='query_id';

15、查看集群分布式信息

select * from system.clusters;

16、改名

rename table TABLE1 to TABLE2;

17、更新數據

ALTER TABLE db.test UPDATE name='ccc' WHERE createTime='2020/2/13';

18、系統事件、查詢總次數、insert次數、失敗次數

select * from system.events

19、查看建表ddl

SHOW CREATE TABLE table1

20、查看當前查詢執行列表

show processlist;

21、壓縮率

selectdatabase as `數據庫`,table as `表名`,formatReadableSize(size) as `所占磁盤大小`,formatReadableSize(data_uncompressed_bytes) as `原始大小`,formatReadableSize(data_compressed_bytes) as `壓縮大小`,compress_rate as `壓縮率`,rows as `行數`,days as `存在天數`,formatReadableSize(avgDaySize) as `平均每天的大小` from (selectdatabase,table,sum(bytes) as size,sum(rows) as rows,min(min_date) as min_date,max(max_date) as max_date,sum(data_uncompressed_bytes) as data_uncompressed_bytes,sum(data_compressed_bytes) as data_compressed_bytes,(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,max_date - min_date as days,size / (max_date - min_date) as avgDaySizefrom system.partswhere active and database = 'ds'and table = 'table'group bydatabase,table )

總結

以上是生活随笔為你收集整理的clickhouse常用sql的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。