Clickhouse 业务使用手册

[night1008]

数据写入流程

1. 写入本地表
2. 查询分布式表

---

插入 JSON 文件内容

文件路径：/var/lib/clickhouse/user_files/data.json

INSERT INTO your_table_local SELECT * FROM file('data.json', 'JSONEachRow');

插入 csv.gz 文件内容

clickhouse-client --query="INSERT INTO my_table FORMAT CSVWithNames" < data.csv.gz

---

指定周任意起始日

select arrayJoin([0,1,2,3,4,5,6]) as start_day, 
  toDateTime('2025-04-10') as t,
  toDayOfWeek(t),
  if(start_day <= toDayOfWeek(t), 
     date_add(DAY, -((toDayOfWeek(t) - start_day) % 7), t),
     date_add(DAY, -7-((toDayOfWeek(t) - start_day) % 7), t)
  ) AS week_start_day;

---

时间戳转时间

select `#time`, timestamp_add(fromUnixTimestamp64Milli(`#time`), interval coalesce(`#zone_offset`, 8) * 60 minute) from events where `#event` = '#device_login' and `#dt` = '2025-03-01' limit 100;

---

变更语句是否执行完

select hostname(),* from clusterAllReplicas('default','system.mutations') where is_done = 0;

---

加快字段删除速度

-- 每次变更之前执行
alter table demo.users_local modify setting number_of_free_entries_in_pool_to_execute_mutation = 1;

-- 变更完成后执行
alter table demo.users_local reset setting number_of_free_entries_in_pool_to_execute_mutation;

---

清理数据库表

truncate table demo.events_local;
truncate table demo_global.users_local;
truncate table demo_global.devices_local;

---

根据 query 查询 clickhouse 日志

select * from cluster('all-sharded', 'system', 'query_log') where query_id  = '4efbbb9b-b853-44ca-84b2-95ff066895af';

---

数据导出成CSV

https://clickhouse.com/docs/interfaces/formats

SELECT * FROM events
SETTINGS join_use_nulls=1, allow_experimental_analyzer=0
INTO OUTFILE '新玩家首日首场战场数据.csv.gz' TRUNCATE
FORMAT CSVWithNames;

---

数据去重验证

select `_part`, `#dt`, `#event`, `#log_id`, count(1), groupArray(hostname()) from events where `#dt` = '2024-07-28' group by 1, 2, 3, 4 order by 5 desc limit 100;

---

查询表大小

SELECT database,  table, formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

---

删除分区数据(本地表)

DELETE FROM demo.events_local in PARTITION '2026-07-27' 
where `#dt` = '2026-07-27' and `#log_id` = '00102298-5e31-42ec-b810-36c6c2a90d6a'
SETTINGS lightweight_deletes_sync = 2, mutations_sync = 1;

---

慢查询日志定位

SELECT
    event_time,
    query_duration_ms,
    memory_usage,
    read_rows,
    read_bytes,
    query
FROM system.query_log
WHERE
    type = 'QueryFinish'
    AND event_time >= now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC LIMIT 100;

---

先删后加字段

SET alter_sync = 2, allow_experimental_database_replicated = 1;
ALTER TABLE demo.events DROP COLUMN IF EXISTS `name`;
ALTER TABLE demo.events_local DROP COLUMN IF EXISTS `name`;
ALTER TABLE demo.events_local ADD COLUMN IF NOT  EXISTS `name` Nullable(String) COMMENT '名称';
ALTER TABLE demo.events ADD COLUMN IF NOT  EXISTS `name` Nullable(String) COMMENT '名称';

---

查看 shard 分片数量

select cluster,shard_num,replica_num
from system.clusters 
where name = 'demo';

---

整个表聚合查校验值

with  tbl as (select 1 as a, 2 as b,number from numbers(10) order by number desc)
SELECT hex(groupBitXor(cityHash64(tuple(*)))) as cksum
from tbl;
-- B4B3E09AC69CEBE7

---

查询 sleep 超过 3 秒

SELECT sleep(12) AS s, 1 AS id SETTINGS function_sleep_max_microseconds_per_block = 60000000