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
————————————————
版权声明:本文为CSDN博主「佛系小李哥」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/lhpbird/article/details/122616706