clickhouse jdbc报错：Too many partitions for single INSERT block (more than 100)

最新推荐文章于 2025-03-20 14:32:47 发布

原创最新推荐文章于 2025-03-20 14:32:47 发布 · 4.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#大数据

olap 专栏收录该内容

5 篇文章

订阅专栏

本文解决了一个在使用ClickHouse时常见的问题：在通过JDBC插入数据时遇到“Too many partitions for single INSERT block”错误。问题源于使用了包含超过100个分区值的字段作为分区键。文章详细解释了ClickHouse对分区数量的限制及其原因，并提出了调整分区策略以避免此问题的方法。

通过clickhouse官方jdbc写入时报错：Too many partitions for single INSERT block (more than 100)
解决：
调整了建表时PARTITION BY分区字段，因为是从hive里读的，所以直接用了hive分区的值: PARTITION BY dt，之前用的add_time字段做分区：toYYYYMMDD(toDateTime(add_time, 'Asia/Shanghai'))，这个字段在同一批次写入里包括大于100个分区值，clickhouse认为这样会存在性能问题，抛出异常：

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 20.2.1.2183 (official build))