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