ClickHouse本地表设计

因为当一次插入的数据大小超过“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”来规避。

当列字段中的值过期时，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);

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; #删除表

ALTER TABLE default.table_with_non_default_policy ON CLUSTER default_cluster drop partition 201901; #删除分区

本地表建表参考：

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”：

  {cluster}表示集群名称，{shard}是分片名称，{replica}是分片中的副本编号，这几个宏变量直接写即可，建表时不需要替换为常量值。

  default：表示创建的表名放到哪个数据库下面，在创建表时需要根据实际情况进行替换。

• on cluster：创建的集群

  建表会创建到集群中所有节点上，否则需要自己手动一个个节点去创建，一个个节点创建过程比较繁琐，创建比较慢；如果在集群中部分节点未创建表，在查询时会遇到无表信息的错误提示。

• no delay：立刻生效

  在删除表或修改表语法中加上no delay，表示立即删除，否则会等8分钟以后进行删除，如果未加no delay语法，删除表后需要立即创建同名的表名可能会遇到错误，创建不成功。

• order by：排序字段

  查询时最常使用且过滤性最高的字段作为排序字段。依次按照访问频度从高到低、维度基数从小到大来排。排序字段不宜太多，建议不超过4个，否则merge的压力会较大。排序字段不允许为null，如果存在null值，需要做数据转换。

• primary key：主键字段

  创建主键索引，值为排序字段的前导列，否则不允许创建表，为访问频率最高的字段创建索引，提升查询性能，查询时会通过索引数据快速的找到数据文件中的数据块所在位置信息。

• partition by：分区字段

  分区键不允许为null，如果字段中有null值，需要做数据转换处理。

• 表级别的参数配置：

  index_granularity：稀疏索引粒度配置，默认是8192，一般不需要修改。

建表定义，参考链接：

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/