ClickHouse 常用语句

ClickHouse 常用语句

查看文件个数
select partition ,count(1) from system.parts where table=‘exception_event’ group by partition order by partition desc

select database,table,partition,count(1) from system.parts group by database,partition,table order by count(1) desc

查询查询日志
select query_duration_ms,query,address from system.query_log
where query_start_time between ‘2022-11-17 00:00:00’ and ‘2022-11-17 00:30:00’ and query like ‘%limit 100000%’

查询每分钟，请求时间>10秒钟的，sql执行数量：
select count(1),formatDateTime(query_start_time,‘%Y-%m-%d %H:%M’) from system.query_log
where query_start_time between ‘2023-04-25 10:00:00’ and ‘2023-04-25 17:00:00’
and query_duration_ms>10000
group by formatDateTime(query_start_time,‘%Y-%m-%d %H:%M’)
order by formatDateTime(query_start_time,‘%Y-%m-%d %H:%M’) desc;

查询查询日志
select query_duration_ms,query,address,query_start_time,type,result_rows,exception,exception_code,stack_trace from system.query_log where query_start_time between ‘2023-04-25 15:16:00’ and ‘2023-04-25 15:16:59’ and query_duration_ms>100000 order by query_start_time;

1.查看数据库容量、行数、压缩率
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;

2.查看数据表容量、行数、压缩率
–在此查询一张临时表的信息
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
3.查看数据表分区信息
–查看测试表在19年12月的分区信息
SELECT
partition 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 (database IN (‘default’)) AND (table IN (‘temp_1’)) AND (partition LIKE ‘2019-12-%’)
GROUP BY partition
ORDER BY partition ASC
4.查看数据表字段的信息
SELECT
column AS 字段名,
any(type) AS 类型,
formatReadableSize(sum(column_data_uncompressed_bytes)) AS 原始大小,
formatReadableSize(sum(column_data_compressed_bytes)) AS 压缩大小,
sum(rows) AS 行数
FROM system.parts_columns
WHERE (database = ‘default’) AND (table = ‘temp_1’)
GROUP BY column
ORDER BY column ASC

5、查询一段时间内的查询历史
select event_time,read_rows,result_rows ,memory_usage ,query_duration_ms,query from system.query_log q
where event_time>=‘2023-01-04 10:00:00’ and event_time<‘2023-01-04 10:01:00’ and databases =[‘heaven_eye_event_log’]
and type=‘QueryFinish’ and upper(query) like ‘INSERT%’
order by event_time

6、编辑数据
ALTER TABLE heaven_eye_event_log.app_crash_log UPDATE appVersion = ‘6.5.1’ WHERE eventId in (‘b7ff4c79d8bc562918f492186a344b01’)

表结构修改（alter）
1、添加字段：
alter table 表名 add column 字段名 字段类型 after 已有字段
alter table 表名 add column 字段名 字段类型

2、删除字段：
alter table 表名 drop column 字段名 字段类型 after 已有字段
alter table 表名 drop column 字段名

3、修改字段：
alter table 表名modify column 字段名 字段类型

4、增加注释：
alter table 表名comment 字段名 ‘注释’

5、移动表|重命名：
rename table a to b
rename table a to newdb.a
rename table a to newdb.b

6、查看写入异常
SELECT
query_id,
event_time,
written_rows,
memory_usage,
query_duration_ms,
substring(exception, 60, 50) AS exception
FROM system.query_log
WHERE (event_date = today()) AND (event_time >= (now() - toIntervalMinute(2))) AND (query LIKE ‘insert%event_production(%’)
ORDER BY event_time DESC；

7、监测内存占用
SELECT
query_id,
event_time,
read_rows,
read_bytes,
query_duration_ms,
memory_usage
FROM system.query_log
WHERE (event_date = today()) AND (event_time > ‘2024-08-20 10:40:00’)
ORDER BY memory_usage DESC
LIMIT 20；

8、查看Clickhouse日志
tail -f /mysqldata/clickhouse-server/log/clickhouse-server.log