ClickHouse 常用语句

一、常用操作

1、建数据库

可以通过jdbc方式访问，DBeaver软件选择ck引擎，8123端口。也可直接客户端方式访问ck

create database bdg_inf on cluster default_cluster;

2、建表分为本地数据表，和查询表。

（1）、本地表-数据表，on cluster ch_cluster语法标识分布式DDL，即执行一次就可以在集群所有实例上创建同样的本地表。

CREATE TABLE if not exists bdg_inf.dws_compass_test_local ON CLUSTER default_cluster
(
    `event_id` String COMMENT '事件id',
    `biz_type` Int16 COMMENT '业务线',
    `client_type` Int16 COMMENT '客户端类型',
    `event_type` Int16 COMMENT '事件类型',
    `sdk_version` String COMMENT 'sdk版本',
    `distinct_id` String COMMENT '唯一id',
    `client_name` String COMMENT '客户端名称',
    `create_time` Int64 COMMENT '日志清洗时间',
    `dt` String COMMENT '分区字段'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/bdg_inf.dws_compass_test_local', '{replica}')
PARTITION BY (dt)
ORDER BY (event_id, sdk_version)
TTL parseDateTimeBestEffort(dt) + toIntervalMonth(3)
SETTINGS index_granularity = 8192

（2） 查询表--分布式表，本身不存储数据

CREATE TABLE bdg_inf.dws_compass_test ON CLUSTER default_cluster
(
   `event_id` String COMMENT '事件id',
    `biz_type` Int16 COMMENT '业务线',
    `client_type` Int16 COMMENT '客户端类型',
    `event_type` Int16 COMMENT '事件类型',
    `sdk_version` String COMMENT 'sdk版本',
    `distinct_id` String COMMENT '唯一id',
    `client_name` String COMMENT '客户端名称',
    `create_time` Int64 COMMENT '日志清洗时间',
    `dt` String COMMENT '分区字段'
)
ENGINE = Distributed('default_cluster', 'bdg_inf', 'dws_compass_test_local', rand())

3、添加字段（只有MergeTree系列、Merge、Distributed表引擎支持alter操作）+++

alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster  add column if not exists score Float32 default 8.8 after create_time;

alter table bdg_inf.dws_compass_test ON CLUSTER default_cluster  add column if not exists score Float32 default 8.8 after create_time;

4、删除字段+++

alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster drop column if exists score;

alter table bdg_inf.dws_compass_test ON CLUSTER default_cluster drop column if exists score;

5、分布式分区表新增数据+++

 insert into bdg_inf.dws_compass_test (event_id, biz_type , client_type , event_type,sdk_version,distinct_id,client_name,create_time,dt) values ('123123112233', 8, 3, 4,'2.4.1','bjhl297001001421b9e9','ios',1653480770280,20220525);

6、分布式表更新数据+++

alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster update client_name = 'ios1' where event_id = '123123112233';

7、分布式表删除数据+++

ALTER TABLE bdg_inf.dws_compass_test_local ON CLUSTER default_cluster DELETE WHERE event_id = '123123112233';

8、查看分区信息+++

select database, table, partition, partition_id, name, path from system.parts where database = 'bdg_inf' and table = 'dws_compass_test_local';

9、删除partition+++

alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster drop partition '20220526'

10、复制分区数据，做测试数据用

insert into bdg_inf.dws_compass_test (event_id, biz_type , client_type , event_type,sdk_version,distinct_id,client_name,create_time,dt)
select event_id, biz_type , client_type , event_type,sdk_version,distinct_id,client_name,create_time,toFixedString('20220523', 8) AS sss from bdg_inf.dws_compass_test where dt='20220526'

11、修改字段类型（修改前后的字段数据类型需要兼容）

alter table alter_table_test ON CLUSTER default_cluster  modify column if exists score Float64 default 0.0;

12、添加或修改字段备注

alter table alter_table_test ON CLUSTER default_cluster  comment column if exists score '分数';

13、重命名或移动表？？？

rename table default.alter_table_test  to default.alter_table_rename_test ON CLUSTER default_cluster;

14、清空数据

truncate table if exists default.alter_table_rename_test ON CLUSTER default_cluster;

18、把某一列变为默认值

alter table partition_table_test clear column name in partition 'Shanghai';

19、卸载和装载partition

alter table partition_table_test detach partition 'Shanghai';

alter table partition_table_test attach partition 'Shanghai';

20、csv方式导入数据

clickhouse-client -h 1.1.1.12 -format_csv_delimiter=',' -q 'insert into test_db.aa_all FORMAT CSV ' < /root/aa_all_data.csv

二、运维

1、查看集群情况

select * from system.clusters;

2、资源查看--查看表数据大小

SELECT column,
　　any(type),
　　sum(column_data_compressed_bytes) AS compressed,
　　sum(column_data_uncompressed_bytes) AS uncompressed,
　　sum(rows)
FROM system.parts_columns
WHERE database = 'bdg_inf'
　　and table = 'dws_compass_test_local'
　　AND active
GROUP BY column
ORDER BY column ASC

3、查看各库表指标(GB显示)：大小，行数，日期，落盘数据大小，压缩前，压缩后大小

select
　　database,
　　table,
　　formatReadableSize(size) as size,
　　formatReadableSize(bytes_on_disk) as bytes_on_disk,
　　formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
　　formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
　　compress_rate,
　　rows,
　　days,
　　formatReadableSize(avgDaySize) as avgDaySize
from
　(
　　　select
　　　　　　database,
　　　　　　table,
　　　　　　sum(bytes) as size,
　　　　　　sum(rows) as rows,
　　　　　　min(min_date) as min_date,
　　　　　　max(max_date) as max_date,
　　　　　　sum(bytes_on_disk) as bytes_on_disk,
　　　　　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　　　　　sum(data_compressed_bytes) as data_compressed_bytes,
　　　　　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　　　　　max_date - min_date as days,
　　　　　　size / (max_date - min_date) as avgDaySize
　　　　from system.parts
　　　　where active 
　　　　　　and database = 'bdg_inf'
　　　　　　and table = 'dws_compass_test_local'
　　　　group by
　　　　　　database,
　　　　　　table
)

4、查看各库表指标(字节显示)：大小，行数，日期，落盘数据大小，压缩前，压缩后大小

select database,
　　table,
　　sum(bytes) as size,
　　sum(rows) as rows,
　　min(min_date) as min_date,
　　max(max_date) as max_date,
　　sum(bytes_on_disk) as bytes_on_disk,
　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　sum(data_compressed_bytes) as data_compressed_bytes,
　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　max_date - min_date as days,
　　size / (max_date - min_date) as avgDaySize
from system.parts
where active
　　and database = 'bdg_inf'
　　and table = 'dws_compass_test_local'
　　group by database, table

5、查看库表容量，压缩率

select database,
　　table,
　　sum(bytes) as size,
　　sum(rows) as rows,
　　min(min_date) as min_date,
　　max(max_date) as max_date,
　　sum(bytes_on_disk) as bytes_on_disk,
　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　sum(data_compressed_bytes) as data_compressed_bytes,
　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　max_date - min_date as days,
　　size / (max_date - min_date) as avgDaySize
from system.parts
where active
　　and database = 'bdg_inf'
　　and table = 'dws_compass_test_local'
　　group by database, table