更新数据
ALTER table ads_sales_item_shop_di_replica ON CLUSTER ck_cluster1 update item_no='' where item_no is null;
ALTER table ads_sales_item_shop_di_replica ON CLUSTER ck_cluster1 update store_shop_code='' where store_shop_code is null;
ALTER table ads_sales_item_shop_di_replica ON CLUSTER ck_cluster1 update unify_goods_code='' where unify_goods_code is null;


重命名表
RENAME TABLE table_A TO table_A_bak, table_B TO table_B_bak;



查看所有表
select *
FROM system.tables t WHERE database ='ads' AND engine <>'Distributed' ORDER by total_rows DESC 


select *
FROM system.tables t WHERE database ='ads'
AND engine <>'Distributed'
and name not like '%del%'
and name not like '%20%'
ORDER by total_rows DESC



自动清理query_log，query_thread_log，trace_log

ALTER TABLE system.query_log on cluster ck_cluster1 MODIFY TTL event_date + INTERVAL 15 DAY

ALTER TABLE system.query_thread_log on cluster ck_cluster1 MODIFY TTL event_date + INTERVAL 15 DAY

ALTER TABLE system.trace_log on cluster ck_cluster1 MODIFY TTL event_date + INTERVAL 15 DAY

立即清理
alter table system.query_thread_log_0 drop partition '202105'
分区名可以用下语句查询
select * from system.parts p where table = '表名'

查看parts  
select * from system.parts  where table = 'abs_activity_item_info_day_replica'
select * from system.parts where active = 0
当前慢查询
SELECT * FROM system.processes limit 100

耗时大于60秒
kill query where elapsed >= 60

clickhouse不能创建等执行操作时（每个节点都要执行）
select * from system.mutations where is_done = 0;
kill mutation ON CLUSTER ck_cluster1 
 where database='ads' and table='ads_jd_erp_sale_outstock_replica' 


进入zk清理任务
deleteall /clickhouse/distributed_ddl/query-0000011932
查询阻塞的任务
select * from system.distributed_ddl_queue where status != 'Finished'


查看表大小
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
GROUP BY table
order by sum(data_compressed_bytes) desc


修改字段名
ALTER TABLE visits RENAME COLUMN webBrowser TO browser
分布式集群下用分布式DDL修改字段名
ALTER TABLE visits on cluster shipin_cluster RENAME COLUMN webBrowser TO browser


新增字段
 alter table ads_itemprice_sales_section_replica ON CLUSTER ck_cluster1 add column sort_mark Nullable(int)
 alter table ads_itemprice_sales_section ON CLUSTER ck_cluster1 add column sort_mark Nullable(int)