clickhouse使用教程

零、clickhouse的优劣1、应用场景2、与redis对比优劣一、安装部署clickhouse二、基础语法clickhouse 设备开机率表 完整记事本教程直接复制运行 库名bench 表名uptime_rate_statistics 注意点前置提醒 1、ClickHouse没有标准deleteDELETE FROM底层也是mutation异步任务不是立刻删除 2、mutationupdate/delete提交后不会马上生效需要查询system.mutations看is_done1才算完成 3、Date类型不能直接like模糊匹配必须包toString()否则查不到数据 4、建表字段id、create_time有DEFAULT默认值insert的时候不用传这两个字段 5、UInt8数字类型存数字0 1 2不要传字符串空字符串会报类型错误 6、SQL语句末尾分号复制到客户端执行多条语句分开执行不要一次性把一大段全部丢进去容易异常 7、KILL MUTATION 用来杀掉卡住的更新删除任务表名数据库名不能写错 8、WITH语法用来生成时间骨架做缺月补空数据看板图表经常用 ---------------------------------------------------------------------- --1、删除旧表清理重建 DROP TABLE IF EXISTS bench.uptime_rate_statistics; CREATE TABLE bench.uptime_rate_statistics ( id UUID DEFAULT generateUUIDv4() COMMENT 主键ID自动生成, type UInt8 COMMENT 统计类型1-日统计2-月统计, stat_date Date COMMENT 统计日期日统计为当日月统计存当月01号, workshop String COMMENT 车间名称, equipment_name String COMMENT 设备名称, uptime_hours Float64 COMMENT 开机时长小时日/月由type决定, uptime_rate Float64 COMMENT 开机率日当日时长/24月累计时长/当月总时长, month_uptime_rate Float64 COMMENT 月开机率日统计行填写月统计行填0, dev_type String COMMENT 设备类型, dev_state UInt8 COMMENT 设备状态 0开机 1关机 2故障, create_time DateTime DEFAULT now() COMMENT 记录创建时间自动生成 ) ENGINE MergeTree PARTITION BY toYYYYMM(stat_date) ORDER BY (type, workshop, equipment_name, stat_date) COMMENT 设备开机率统计表看板数据; 说明 id 插入自动生成uuid不用手动赋值 create_time 插入自动取数据库服务器当前时间insert不必写 type1日统计2月统计 stat_date Date类型格式yyyy‑MM‑dd月统计统一存当月01号例如2026‑07‑01 dev_state UInt8只能存数字0开机1关机2故障不能存空字符串 建完校验表结构命令复制执行看字段是否齐全 DESCRIBE TABLE bench.uptime_rate_statistics; ---------------------------------------------------------------------- --2、按月分组统计今年各月平均开机率只取月统计type2 SELECT stat_date,sum(uptime_rate)/count(uptime_rate) as rate from bench.uptime_rate_statistics where type 2 and stat_date toStartOfYear(now()) group by stat_date; 说明 toStartOfYear(now()) 自动获取今年1月1号不用硬编码年份 group by 规则select中非聚合字段必须全部写在group byclickhouse严格模式漏写直接报错 ---------------------------------------------------------------------- --3、清空整张表全部数据保留表结构 TRUNCATE TABLE bench.uptime_rate_statistics; 说明 truncate 删除全部数据表结构、字段、注释保留高危操作确认表名数据不可回滚 drop table 是直接删掉整张表结构数据两者区分开 ---------------------------------------------------------------------- --4、条件查询指定日期、日统计type1的数据 select * from bench.uptime_rate_statistics where stat_date 2026-08-27 and type 1; 说明type是clickhouse关键字用反引号 包裹避免语法报错 ---------------------------------------------------------------------- --5、删除数据底层mutation异步不是立刻生效 --删除指定月份统计记录 type2 DELETE FROM bench.uptime_rate_statistics WHERE type 2 and stat_date 2026-03-01; --删除2026‑07全部日统计type1数据 DELETE FROM bench.uptime_rate_statistics WHERE toString(stat_date) like 2026-07% and type 1; 重点坑 Date字段做like必须toString(stat_date)转换字符串不加toString查不到任何结果 delete提交后不会立刻看不到数据需要等待mutation后台执行完成 查看mutation任务进度执行这条is_done1代表任务完成 SELECT * FROM system.mutations WHERE databasebench AND tableuptime_rate_statistics; ---------------------------------------------------------------------- --6、错误示例Date类型直接like这种写法查不到数据不要使用 -- select * from bench.uptime_rate_statistics where stat_date 2026-08% and type 1; ---------------------------------------------------------------------- --7、统计最近7天日统计平均开机率返回stat_date日期和rate SELECT stat_date as name,sum(uptime_rate)/count(uptime_rate) as rate from bench.uptime_rate_statistics where type 1 and stat_date today() -7 group by stat_date; 说明today()获取数据库服务器当天日期today()-7代表7天前日期 ---------------------------------------------------------------------- --8、单台设备按月汇总返回json格式供node‑red/前端调用 select sum(uptime_hours) as sumHour,count(*) as sumNum, argMax(dev_type, stat_date) AS dev_type, argMax(dev_state, stat_date) AS dev_state from bench.uptime_rate_statistics where toString(stat_date) like 2026-08% and equipment_name D109 and type 1 FORMAT JSON; 说明 argMax(字段,stat_date)取stat_date最大那条记录对应的dev_type、dev_state拿最新一条属性 FORMAT JSON 输出标准json接口直接读取 ---------------------------------------------------------------------- --9、插入一条月统计示例数据 INSERT INTO bench.uptime_rate_statistics (type,stat_date,workshop,equipment_name,uptime_hours,uptime_rate,month_uptime_rate,dev_type,dev_state) VALUES(2,2026-07-01,一车间,A029,61.89,0.08,0,挤出,1); 说明 idcreate_time不写数据库自动填充 type2月统计month_uptime_rate业务约定填0 dev_state传数字不要传字符串 ---------------------------------------------------------------------- --10、更新数据mutation异步更新 ALTER TABLE bench.uptime_rate_statistics UPDATE uptime_hours 8.18, uptime_rate 0.056, month_uptime_rate 0.82 WHERE type 1 AND stat_date 2026-08-26 AND workshop 一车间 AND equipment_name D033; 说明 clickhouse没有update table set语法统一 ALTER TABLE ... UPDATE where条件尽量多字段防止误更新大批量行提交后后台慢慢跑查system.mutations看完成状态 ---------------------------------------------------------------------- --11、查询指定月份指定设备日统计数据返回明细 select * from bench.uptime_rate_statistics where toString(stat_date) LIKE 2026-08% and equipment_name D027 and type 1; ---------------------------------------------------------------------- --12、修改字段属性修改dev_state默认值和注释 ALTER TABLE bench.uptime_rate_statistics MODIFY COLUMN dev_state UInt8 DEFAULT 0 COMMENT 设备状态; 说明MODIFY COLUMN 修改已有字段类型、默认值、注释 注意老历史数据不会自动更新默认值DEFAULT只针对新插入行生效 ---------------------------------------------------------------------- --13、修复dev_state历史脏数据把空字符串改为数字0注意类型dev_state是UInt8不能存字符串 ALTER TABLE bench.uptime_rate_statistics UPDATE dev_state0 WHERE dev_state; 坑提醒dev_state是UInt8数字类型条件里面字符串匹配一般匹配不到历史脏数据尽量源头不要写入 ---------------------------------------------------------------------- --14、杀掉卡住的mutation任务表名库名写正确 KILL MUTATION WHERE tabledevice_rate_board AND databasebench; 说明mutation任务堆积卡死长时间is_done0的时候使用不要随便执行 ---------------------------------------------------------------------- --15、统计本月日统计总开机时长、总记录条数输出json SELECT sum(uptime_hours) as hours,count(uptime_hours) as num from bench.uptime_rate_statistics where toYYYYMM(stat_date) toYYYYMM(now()) and type 1 FORMAT JSON; 说明toYYYYMM(stat_date) 提取年月数字toYYYYMM(now()) 当前年月做当月筛选不用写死年月字符串 ---------------------------------------------------------------------- --16、查询所有开机率等于0的设备名称去重 select DISTINCT equipment_name from bench.uptime_rate_statistics where type 1 and uptime_rate 0; DISTINCT去重返回不重复设备名 ---------------------------------------------------------------------- --17、新增字段指定放在某个字段后面示例表device_rate_board ALTER TABLE bench.device_rate_board ADD COLUMN mcategory String DEFAULT COMMENT 设备总类 AFTER month_uptime_rate; 说明 AFTER xxx 只改变select *显示顺序磁盘旧数据文件不会重排老记录自动填充DEFAULT 不写AFTER新增字段直接放表最后面 新增完 DESCRIBE TABLE bench.device_rate_board; 确认字段 删除字段备用谨慎执行 -- ALTER TABLE bench.device_rate_board DROP COLUMN mcategory; ---------------------------------------------------------------------- --18、WITH时间骨架SQL今年1月到当前月没有数据月份也返回图表不会缺月份输出JSON WITH month_spine AS ( SELECT toStartOfMonth(toStartOfYear(now()) INTERVAL number MONTH) AS stat_date FROM numbers(toMonth(now()) - 1) ), stat_result AS ( SELECT stat_date, IF(count(uptime_rate) 0, sum(uptime_rate)/count(uptime_rate), NULL) AS rate from bench.uptime_rate_statistics where type 2 and stat_date toStartOfYear(now()) group by stat_date ) SELECT concat(toString(toMonth(s.stat_date)),月) AS name, r.rate AS rate FROM month_spine s LEFT JOIN stat_result r ON s.stat_date r.stat_date ORDER BY s.stat_date FORMAT JSON; 解释 month_spine 生成今年1月到当前月份的全部月份时间骨架 LEFT JOIN 统计结果没有数据月份rate返回null前端图表不会月份断层 concat拼接“1月、2月”用于前端显示 numbers()生成序列函数toMonth(now())‑1生成对应多少个月份 ---------------------------------------------------------------------- --19、按设备类型分组统计开机率空类型统一显示“其他” SELECT IF(TRIM(dev_type) , 其他, IFNULL(dev_type, 其他)) AS dev_type, sum(uptime_rate)/count(uptime_rate) AS rate FROM bench.device_rate_board GROUP BY dev_type; 说明 IFNULL(dev_type,其他)dev_type为null替换为其他 TRIM(dev_type)把全空格、空字符串的dev_type归类为其他 GROUP BY 按设备类型分组计算平均开机率 ---------------------------------------------------------------------- --日常调试备用命令复制直接运行 --查看完整建表语句可以复制备份 SHOW CREATE TABLE bench.uptime_rate_statistics; --查看表字段结构 DESCRIBE TABLE bench.uptime_rate_statistics; --统计表总行数 SELECT count(*) FROM bench.uptime_rate_statistics; --查看分区active1有效分区 SELECT partition,name FROM system.parts WHERE databasebench AND tableuptime_rate_statistics AND active1; --删除指定分区高危删除整个月全部数据 -- ALTER TABLE bench.uptime_rate_statistics DROP PARTITION 202608; --取前10条样例数据 SELECT * FROM bench.uptime_rate_statistics limit 10; --查看mutation任务状态 SELECT * FROM system.mutations WHERE databasebench AND tableuptime_rate_statistics; ---------------------------------------------------------------------- 高频踩坑备忘时隔很久再使用优先看这里 1、type是关键字写查询条件的时候尽量用 type 反引号包裹避免语法报错 2、Date类型做like模糊必须toString(stat_date)不转换查不到数据 3、DELETE / ALTER UPDATE 都是mutation异步任务提交不会立刻生效查system.mutations看is_done1才算完成 4、UInt8数字类型字段不要写入空字符串会报类型错误dev_state只能存数字0、1、2 5、WITH时间骨架查询用于图表解决没有数据月份直接消失的问题 6、insert的时候id、create_time不要传值表有DEFAULT自动生成 7、MODIFY COLUMN 修改字段DEFAULT只对新插入数据生效历史旧数据不会自动更新 8、KILL MUTATION 表名库名千万不要写错会杀掉别的表任务 9、DROP 和 TRUNCATE区分DROP删除整张表结构数据TRUNCATE只清空数据保留表结构 10、FORMAT JSON写在整条SQL的最后不要写到中间位置