clickhouse建表优化

本地表创建样例:

CREATE TABLE database.table_branch  ON CLUSTER 'CLUSTER '
(
     timevalue  DateTime COMMENT '时间',
     vprovince  Nullable(String) COMMENT '省份',
     vcity  Nullable(String) COMMENT '地市',
     msisdn  String COMMENT '号码'     rat  Nullable(String) COMMENT '接入网',
     cell_id  Nullable(String) COMMENT '小区',
     cell_name  Nullable(String) COMMENT '小区名称',
     longitude  Nullable(Float64) COMMENT '经度',
     latitude  Nullable(Float64) COMMENT '纬度',
     prov_id  String COMMENT '省份id',
     INDEX ix_longitude longitude TYPE minmax GRANULARITY 4,
     INDEX ix_latitude latitude TYPE minmax GRANULARITY 4,
     INDEX ix_cell_id cell_id TYPE minmax GRANULARITY 4

)
ENGINE = ReplicatedMergeTree('/aaaa/database/table_branch/{shard}/aaaa/database/table_branch', '{replica}') 
PARTITION BY (toYYYYMMDD(timevalue), prov_id,vcity) 
ORDER BY (cell_id,longitude, latitude,msisdn) 
TTL timevalue + toIntervalYear(1) SETTINGS storage_policy = 'allData', allow_nullable_key = 1, index_granularity = 8192

 分布式表创建语句

CREATE TABLE database.table  ON CLUSTER 'CLUSTER '
(
      timevalue  DateTime COMMENT '时间',
     vprovince  Nullable(String) COMMENT '省份',
     vcity  Nullable(String) COMMENT '地市',
     msisdn  String COMMENT '号码'     rat  Nullable(String) COMMENT '接入网',
     cell_id  Nullable(String) COMMENT '小区',
     cell_name  Nullable(String) COMMENT '小区名称',
     longitude  Nullable(Float64) COMMENT '经度',
     latitude  Nullable(Float64) COMMENT '纬度',
     prov_id  String COMMENT '省份id'
)
ENGINE = Distributed('CLUSTER ', 'database', 'table_branch  ', rand())

建表关键字解释

ENGINE ck建表引擎在如果要最后查询的是分布式表那么引擎使用Distributed

PARTITION BY  一般使用时间作为分区 分区可用使用函数 

ORDER BY  排序列

TTL 数据保留时间

allow_nullable_key =1 设置允许索引为空

index_granularity  设置索引疏密

实际操作中遇到问题:

(1). 重建表的时候 用ON CLUSTER  删掉了表但是重建报错

ck建表分为本地表和分布式表  clickhouse是去中心化的 内部通过Zookeper进行通信,所以在修改表的时候需要一段时间广播到其他节点,所有重建会报错,需要等待

(2).当修改了字段后重建表等待很久还是报错,并且查看其他节点发现表已经被删除

即使其他节点被广播到了之后,zk存放的信息还没改变,新的建表格式和原有的冲突所有还是会报错.

解决办法:修改ReplicatedMergeTree 中的路径将新的信息存放到另一个路径,即能快速建表,或者手动删除路径文件

优化参数:

SETTINGS index_granularity = 8192, 
use_minimalistic_part_header_in_zookeeper = 1,
 merge_with_ttl_timeout = 3600, 
min_bytes_for_wide_part = 100000000, 
min_rows_for_wide_part = 400000, 
min_bytes_for_compact_part = 1000000, 
min_rows_for_compact_part = 10000, 
in_memory_parts_enable_wal = 0,
 write_ahead_log_max_bytes = 10000000, 
allow_nullable_key = 1