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

5.1 查询近一分钟写入操作的信息
SELECT
query_id,
event_time,
written_rows,
memory_usage,
query_duration_ms,
substring(exception,60,50) exception
FROM system.query_log
WHERE (event_date = today())
and event_time >= now() - INTERVAL 1 MINUTE
and query like ‘insert%event_production(%’
ORDER BY event_time DESC;

5.2 查询merge操作占用的内存
SELECT
database,
table,
elapsed,
memory_usage,
rows_read,
rows_written,
merge_type,
num_parts
FROM system.merges

select sum(memory_usage) 　FROM system.merges;

5.3 根据正则表达式查询事件
select DISTINCT(extractAll(query,‘event = (.*?)(group|and|\)| )’)) from
(
select query from system.query_log
where query_start_time between ‘2023-09-30 00:00:00’ and ‘2024-09-30 00:30:00’
and query like ‘%event_production%’ and query like ‘%select%’ and query like ‘%event =%’
)

5.3 根据正则表达式导出事件
insert into heaven_eye_event_log.event_query_event(event)
select DISTINCT(extractAll(query,‘event = (.*?)(group|and|\)| )’)) from
(
select query from system.query_log
where query_start_time between ‘2023-09-30 00:00:00’ and ‘2024-09-30 00:30:00’
and query like ‘%event_production%’ and query like ‘%select%’ and query like ‘%event =%’
)

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

9、查看clickhouse执行阻塞的sql
select query_id,memory_usage,elapsed,query from system.processes;

10、show processlist;
11、insert 。。。 select
insert into heaven_eye_event_log.event_query_event
select DISTINCT(extractAll(query,‘event = (.*?)(group|and|\)| )’)) from
(
select query from system.query_log
where query_start_time between ‘2023-10-09 00:00:00’ and ‘2024-10-10 00:30:00’
and query like ‘%event_production%’ and query like ‘%select%’ and query like ‘%event =%’
)
12、按条件删除数据

ALTER TABLE heaven_eye_event_log.event_production_merge delete where event=‘$AppViewScreen’ and dt=‘20231201’;

13、按分区删除数据
ALTER TABLE heaven_eye_event_log.event_production_merge DROP PARTITION ‘20231201’;

14、设置单个查询的 执行最大时间和占用最大内存
max_execution_time = 5000,max_memory_usage = 30000000000;

– 创建最终存储表
create table heaven_eye_event_log.event_production_bitmap (
user_id Int64,
dt String
) engine=AggregatingMergeTree()
partition by dt
order by (user_id);

INSERT INTO heaven_eye_event_log.event_production_bitmap
(user_id, dt)
select user_id, dt from heaven_eye_event_log.event_production ep
where dt BETWEEN ‘20241230’ and ‘20241230’
group by user_id, dt settings max_execution_time = 5000,max_memory_usage = 50000000000;

– 创建雾化视图
CREATE MATERIALIZED VIEW IF NOT EXISTS heaven_eye_event_log.event_production_materialized
to heaven_eye_event_log.event_production_bitmap as
select user_id, dt from heaven_eye_event_log.event_production ep
group by user_id, dt;

15、Clickhouse aes 加密
select hex(encrypt(‘aes-128-cbc’,userName ,‘1234567887456321’)) AS userName from heaven_eye_event_log.app_start_log asl