Clickhouse入门

docker pull yandex/clickhouse-server:21.3.20.1

docker run --rm -d --name=clickhouse-server \
--ulimit nofile=262144:262144 \
-p 8123:8123 -p 9009:9009 -p 9090:9000 \
yandex/clickhouse-server:21.3.20.1

docker exec -it da07c054ce26 bash
clickhouse-client 
show databases  默认带2个数据库（system、default）

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

SELECT query_id, user, address, query  FROM system.processes ORDER BY 1;

kill query where query_id='xxxx';

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

SELECT database, formatReadableSize(sum(bytes_on_disk)) 
on_disk FROM system.parts GROUP BY database;

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

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;

SELECT user, client_hostname AS host, client_name AS client, formatDateTime(query_start_time, '%T') AS started, query_duration_ms / 1000 AS sec, 
round(memory_usage / 1048576) AS MEM_MB, 
result_rows AS RES_CNT, 
result_bytes / 1048576 AS RES_MB, read_rows AS R_CNT, round(read_bytes / 1048576) AS R_MB, 
written_rows AS W_CNT, round(written_bytes / 1048576) AS W_MB, query
FROM system.query_log WHERE type = 2
ORDER BY query_duration_ms DESC LIMIT 10

SELECT table,
formatReadableSize(sum(data_compressed_bytes)) AS tc,
formatReadableSize(sum(data_uncompressed_bytes)) AS tu,
round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100,2) AS ratio
FROM system.columns
WHERE database = 'system' and table = 'metric_log'
GROUP BY table ORDER BY sum(data_compressed_bytes) ASC

SELECT database,table,partition,partition_id,name,path FROM 
system.parts
WHERE table = 'metric_log';