Code: 252, e.displayText() = DB::Exception: Too many partitions for single INSERT block (more than 100). The limit is controlled by 'max_partitions_per_insert_block' setting. Large number of partitions is a common misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data manipulation (DROP PARTITION, etc). (version 21.8.2.1)
解决方法: 默认是100
set global on cluster default max_partitions_per_insert_block=1000
是因为一次写入的数据,超过100个分区,所以报错。
我们是按照天分区, 开发一次写入了一年的数据,365天 365个分区。
调大了参数,解决这个问题。
和本次无的命令记录一下, 可以强制刷新临时表。
system flush distributed ck_xx_dense.xxx_record_hh
ClickHouse每次写入都会生成一个data part,如果每次写入一条或者少量的数据,那会造成ClickHouse内部有大量的data part(会给merge和查询造成很大的负担)。为了防止出现大量的data part,ClickHouse内部做了很多限制。发生报错的原因是max_partitions_per_insert_block参数用来限制单个插入的Block,数据插入太频繁,ClickHouse不支持频繁插入操作。
https://help.aliyun.com/document_detail/427057.html