数据写入流程

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

插入 JSON 文件内容

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

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

指定周任意起始日

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

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 table_local WHERE date = '2025-10-01';

慢查询日志定位

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 '名称';