# ClickHouse本地表设计
#### 规则
- 单表（分布式表）的记录数不要超过万亿，对于万亿以上表的查询，性能较差，且集群维护难度变大。单表（本地表）不超过百亿。
- 表的设计都要考虑到数据的生命周期管理，需要进行TTL表属性设置或定期老化清理表分区数据。
- 单表的字段建议不要超过5000列。 因为当一次插入的数据大小超过"min_bytes_for_wide_part"（默认值10485760），ClickHouse写入会按每列1 MB（Nullable类型2MB）来预申请内存，容易出现内存超限的错误：
  ```
  Received exception from server (version 22.3.4):
  Code:241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 1048591 bytes), maximum: 9.31 GiB
  ```
  可以通过调大"min_bytes_for_wide_part"来规避。
  
 
#### 参考案例
- MergeTree引擎在建表的时候支持列字段和表级的TTL。 当列字段中的值过期时，ClickHouse会将其替换成数据类型的默认值。如果分区内，某一列的所有值均已过期，则ClickHouse会从文件系统中删除这个分区目录下的列文件。当表内的数据过期时，ClickHouse会删除所有对应的行。
  在列上配置TTL：
  ```
  CREATE TABLE default.t_column_ttl ON CLUSTER default_cluster
  (
  `did` Int32,
  `app_id` Int32,
  `region` Int32,
  `pt_d` Date,
  `create_time` DateTime,
  `product_desc1` String TTL create_time + toIntervalSecond(10),
  `product_desc2` String TTL create_time + toIntervalMonth(10),
  `product_desc3` String TTL create_time + toIntervalHour(10)
  )
  ENGINE = MergeTree()
  PARTITION BY toYYYYMMDD(pt_d)
  ORDER BY (app_id, region);
  ```
  在表上配置TTL：
  ```
  CREATE TABLE default.t_table_ttl ON CLUSTER default_cluster
  (
  `did` Int32,
  `app_id` Int32,
  `region` Int32,
  `pt_d` Date,
  `create_time` Datetime
  )
  ENGINE = MergeTree()
  PARTITION BY toYYYYMMDD(pt_d)
  ORDER BY (app_id, region)
  TTL create_time + toIntervalMonth(12);
  ```
  TTL详细使用见官网链接：
  <https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-ttl>
  
- 通过外部系统管理数据的生命周期，定时清理过期数据。 清理数据SQL命令示例：
  **DROP TABLE default.table_with_non_default_policy ON CLUSTER default_cluster NO DELAY;** #删除表，on cluster执行不成功，单节点执行。
  **ALTER TABLE default.table_with_non_default_policy ON CLUSTER default_cluster DROP PARTITION 201901;** #删除分区，on cluster执行不成功，单节点执行。
  本地表建表参考：
  ```
  CREATE TABLE default.my_table_local ON CLUSTER default_cluster
  (
  `did`      Int32,
  `app_id`   Int32,
  `region`    Int32,
  `pt_d`     Date
  )
  ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/my_table_local', '{replica}')
  PARTITION BY toYYYYMMDD(pt_d)
  PRIMARY KEY(app_id)
  ORDER BY (app_id, region)
  SETTINGS index_granularity = 8192;
  ```
  - 表引擎选择： ReplicatedMergeTree：支持副本特性的MergeTree引擎，也是最常用的表引擎，其他表引擎参考使用场景介绍进行选择。
    
  
  - ZooKeeper上的表元数据信息存储路径"/clickhouse/tables/{shard}/default/my_table_local"： {shard}是分片名称，{replica}是分片中的副本编号，这几个宏变量直接写即可，建表时不需要替换为常量值。
    default：表示创建的表名放到哪个数据库下面，在创建表时需要根据实际情况进行替换。
    
  
  - on cluster：创建的集群 由于ClickHouse会将DDL on cluster任务在ZooKeeper上进行排队执行，在集群负载繁忙时会导致DDL卡死或执行慢，建议不加on cluster登录到每个节点单节点执行，详见[ClickHouse DDL规范](https://support.huaweicloud.com/devg-rule-mrs/mrs_07_450220.html)。
    
  
  
  
  - no delay：立刻生效 在删除表或修改表语法中加上no delay，表示立即删除，否则会等8分钟以后进行删除，如果未加no delay语法，删除表后需要立即创建同名的表名可能会遇到错误，创建不成功。
    
  
  
  
  - order by：排序字段 查询时最常使用且过滤性最高的字段作为排序字段。依次按照访问频度从高到低、维度基数从小到大来排。排序字段不宜太多，建议不超过4个，否则merge的压力会较大。排序字段不允许为NULL，如果存在NULL值，需要做数据转换。
    
  
  - primary key：主键字段 创建主键索引，值为排序字段的前导列，否则不允许创建表，为访问频率最高的字段创建索引，提升查询性能，查询时会通过索引数据快速地找到数据文件中的数据块所在位置信息。
    
  
  - partition by：分区字段 分区键不允许为NULL，如果字段中有NULL值，需要做数据转换处理。
    
  
  - 表级别的参数配置： index_granularity：稀疏索引粒度配置，默认是8192，一般不需要修改。
    
  
  
  建表定义，参考链接：
  <https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/>