ClickHouse系列--常用sql语句

//删除本地表
DROP table ti.java4al_base on cluster ck;
//删除分布式表
DROP table ti.java4al_base_all on cluster ck;

-- 添加字段 本地表
ALTER table ti.java4al_base  ON CLUSTER ck add column IF NOT EXISTS 
`challengerType` Nullable(String) COMMENT '是否是挑战者' AFTER `isChallenger`;

-- 添加字段 分布式表
ALTER table ti.java4al_base_all  ON CLUSTER ck add column IF NOT EXISTS 
`challengerType` Nullable(String) COMMENT '是否是挑战者' AFTER `isChallenger`;

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) "压缩率"
from system.parts
    group by table;	

查询分区信息
select * from system.parts p where table = 'activity_base_local'
删除指定分区数据
alter table java4all.activity_base_local drop partition ('2021-04-07');

clickhouse-client -u java4all --password 123456 --port 9000

ALTER TABLE java4all.aaa on cluster ck ADD INDEX IF NOT EXISTS index_eventType eventType TYPE set(20) GRANULARITY 5294;