【ClickHouse-Sql】ClickHouse 常用 Sql（创建集群表、删除分区、条件删除、随机分群、修改表名、计算数据存储量等）

1）建库

create database bigdata_user on cluster perftest_3shards_1replicas;

2）建表

ClickHouse 有本地表，集群表的概念，本地表就是每台节点的表（存储整个集群数据的一部分），集群表可以看成是一个视图。

（1）本地表（local）

create table ods_visit_user_local on cluster perftest_3shards_1replicas(
    start_time DateTime,
    username String,
    user_agent String,
    user_ip String,
    visit_url String
) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(start_time) order by start_time;

（2）集群表（cluster）

create table ods_visit_user on cluster perftest_3shards_1replicas(
    start_time DateTime,
    username String,
    user_agent String,
    user_ip String,
    visit_url String
) ENGINE = Distributed(perftest_3shards_1replicas, 'bigdata_user', ods_visit_user_local, rand());

（3）分区表

create table ods_visit_user_local on cluster perftest_3shards_1replicas(
    start_time DateTime,
    username String,
    user_agent String,
    user_ip String,
    visit_url String
) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(start_time) 
partition by start_time 
order by start_time;

3）删除表、清空表

drop table if exists ods_visit_user_local on cluster perftest_3shards_1replicas;
drop table if exists ods_visit_user on cluster perftest_3shards_1replicas;
truncate table ods_visit_user_local on cluster perftest_3shards_1replicas;

4）删除数据

（1）删分区

alter table ods_visit_user.ods_visit_user_local  on cluster perftest_3shards_1replicas  drop partition '***';

（2）条件删除

alter table ods_visit_user.ods_visit_user_local on cluster perftest_3shards_1replicas
delete where length(event_st_dt)>19 
and event_id ='***';

5）随机分群

SELECT 
    userID,
    rand() % 5 AS dataset
FROM 
    userTable
ORDER BY 
    dataset;

6）改表名

RENAME TABLE  ods_visit_user.ods_visit_user_local to ods_visit_user.ods_visit_user_local2;

7）计算数据存储量

SELECT 
    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
WHERE database = 'ods_visit_user';