规则 写本地表，查询分布式表，提升写入和查询性能，保证写入和查询的数据一致性。 只有在去重诉求的场景下，可以使用分布式表插入，通过sharding key将要去重的数据转发到同一个shard，便于后续去重查询。 外部模块保证数据导入的幂等性。 ClickHouse不支持数据写入的事务保证。通过外部导入数据模块控制数据的幂等性，比如某个批次的数据导入异常，则drop对应的分区数据或清理掉导入的数据后，重新导入该分区或批次数据。 大批量少频次的写入。 ClickHouse的每次数据插入，都会生成一到多个part文件，如果data part过多, merge压力会变大，甚至出现各种异常影响数据插入。建议每个批次5k到100k行，写入字段不能太多，太多字段情况下要减少写入行数，以降低对写入节点的内存和CPU压力，每秒不超过1次插入。 多副本并行导入。 有大数据的导入场景，建议将数据提前拆分成多份，在一个shard内的多个副本同时导入，以分摊一个节点导入数据的压力，同时能提升数据入库的性能，缩短入库时间。 常见错误： Too many parts(304). Merges are processing significantly slower than inserts 原因分析：MergeTree的merge的速度跟不上目录生成的速度，数据目录越来越多就会抛出这个异常。

建议 一次只插入一个分区内的数据 如果数据属于不同的分区，则每次插入，不同分区的数据会独立生成part文件，导致part总数量膨胀，建议一批插入的数据属于同一个分区。 写入速率 单节点写入速度为50~200MB/S，如果写入的数据每行为1Kb，那么写入的速度为50，000到200，000行每秒，如果行数据容量更小，那么写入速度将更高，如果写入性能不够，可以使用多个副本同时写入，同一时间每个副本写入的数据保持均衡。 慎用分布式表批量插入 写分布式表，数据会分发到集群的所有本地表，每个本地表插入的数据量是总插入量的1/N，batch size可能比较小，导致data part过多，merge压力变大，甚至出现异常影响数据插入； 数据的一致性问题：数据先在分布式表写入节点的主机落盘，然后数据被异步地发送到本地表所在主机进行存储，中间没有一致性的校验，如果分布式表写入数据的主机出现宕机，会存在数据丢失风险； 对于数据写分布式表和数据写本地表相比，分布式表数据写入性能也会变慢，单批次分布式表写，写入节点的磁盘和网络IO会成为性能瓶颈点。 分布式表转发给各个shard成功与否，插入数据的客户端无法感知，转发失败的数据会不断重试转发，消耗CPU。 大批量数据导入要分时、分节点、扩容 如果数据盘为SATA盘，当大批量数据集中插入时候，会抢占磁盘，使得磁盘长时间处于繁忙状态，影响其他alter类操作的效率。 尽量避免批量导数据的SQL并发执行，会给磁盘和ClickHouse并发能力带来冲击。 Kafka数据入库 不建议建ClickHouse kafka表引擎，进行数据同步到ClickHouse中，当前CK的kafka引擎有会导致kafka引擎数据入库产生性能等诸多问题，通过用户使用经验，需要应用侧自己写kafka的数据消费，攒批写入ClickHouse，提升ClickHouse的入库性能。 使用分区替换或增加的方式写入数据 为避免目标表写入脏数据导致的删改，先将数据写入临时表，再从临时表写入目标表。 操作步骤如下： 创建一张与目标表table_dest结构、分区键、排序键、主键、存储策略、引擎都一致的临时表table_source。 先把数据写到临时表，一次只写入一个分区的数据，检查临时表的数据准确无误。 使用以下SQL查看目标表的分区： SELECT partition AS `partition`,sum(rows) AS `count` FROM system.parts WHERE active AND database=='数据库名' AND table=='表名' GROUP BY partition ORDER BY partition ASC; 如果目标表存在该分区，将分区替换到目标表，到集群的每个节点上执行如下语法： ALTER TABLE table_dest REPLACE PARTITION partition_expr FROM table_source; 如果目标表不存在该分区，将分区增加到目标表，到集群的每个节点上执行如下语法： ALTER TABLE table_dest REPLACE PARTITION tuple() partition_expr FROM table_source;

内容介绍 本文主要描述ClickHouse数据管理全生命周期过程中，数据库规划、建模设计、开发、调优、运维的规则建议和指导。 通过这些约束和建议，指导开发者在ClickHouse数据库开发使用过程中能够最大化发挥数据库的优势，保障ClickHouse数据库高性能、稳定可靠运行。用户可更专注于上层业务，释放数据更大的价值。 表1 ClickHouse设计规范说明 项目 描述 数据库规划 集群业务规划、容量规划、数据分布。 数据库设计 Database设计、宽表设计、分布式表设计、本地表设计、分区设计、索引设计、物化视图设计。 数据库开发 简单查询、聚合查询、join查询、数据增/删/改等SQL开发。 数据库调优 调优思路、参数调优、系统调优、SQL改写调优。 数据库运维 监控、告警、日志、系统表/视图。

日志路径 ClickHouse相关日志的默认存储路径为：“${BIGDATA_LOG_HOME}/clickhouse”。 ClickHouseServer运行相关日志：“/var/log/Bigdata/clickhouse/clickhouseServer/ *.log”。 ClickHouseBalancer运行日志：“/var/log/Bigdata/clickhouse/balance/*.log”。 ClickHouseServer审计日志：“/var/log/Bigdata/audit/clickhouse/clickhouse-server-audit.log”。 ClickHouse数据迁移日志：“/var/log/Bigdata/clickhouse/migration/${task_name}/clickhouse-copier_{timestamp}_{processId}/copier.log”。

规则 大批量少频次的插入。 内容要求：ClickHouse的每次数据插入都会生成一到多个part文件，如果data part过多则会导致merge压力变大，甚至出现服务异常影响数据插入。建议一次插入10万行，每秒不超过1次插入。 一次只插入一个分区内的数据。 内容要求：如果数据属于不同的分区，则每次插入，不同分区的数据会独立生成part文件,导致part总数量膨胀。甚至写入报错“Merges are processing significantly slower than inserts“。一批次写入的数据，对应的分区数太多。ClickHouse建表之后insert batch时，会对不同的分区创建一个目录。如果一个batch里面的数据对应了过多的分区，那么一次insert就会生成较多的分区目录，后台merge线程处理速度跟不上分区增加的速度，社区规格是每秒不超过一个数据目录。 具体的操作：确认一个batch的数据对应了多少个分区，insert的时候，尽量保证一个batch包含的分区数是1。 慎用delete、update操作。 内容要求：建议使用CollapsingMergeTree、VersionedCollapsingMergeTree引擎或根据分区批量清理。 ClickHouse需要写本地表。 内容要求：连接balancer写入报错Request Entity Too Large。这是由于Nginx对http请求体大小有限制，而一次写入的数据量超过了这个限制。 规避：修改Nginx配置项client_max_body_size为一个较大的值。 解决：写本地表，不要通过balancer写入数据。

操作步骤 先获取clickhouse-example样例代码工程。 代码获取地址：https://github.com/huaweicloud/huaweicloud-mrs-example/blob/mrs-3.1.2/src/clickhouse-examples/。 在样例工程“conf”目录下有一个“clickhouse-example.proerties”配置文件，其中各项的配置的作用如下所示： #连接节点或Balancer的ip列表，ip之间用逗号隔开 loadBalancerIPList= #是否需要开启ssl,如果取值为true，则loadBalancerHttpsPort必填 sslUsed=true #端口号 loadBalancerHttpPort= loadBalancerHttpsPort= #ClickHouse安全模式开关，安全模式集群时该参数固定为true。 CLICKHOUSE_SECURITY_ENABLED=true #连接的用户名 user= #连接的用户的密码 password= #集群名称 clusterName= #数据库名称 databaseName= #表名称 tableName= #一个批次写入的条数 batchRows=10000 #写入数据的总批次 batchNum=10 #ip:port。安全模式下https端口，普通模式下http端口 clickhouse_dataSource_ip_list= #ip:tcp port native_dataSource_ip_list=ip:port,ip:port,ip:port 在Demo.java有三种连接JDBC的样例：节点的JDBC连接、banlancer的JDBC连接和tcp端口的banlancer的JDBC连接。 Demo提供了createDatabase、createTable、insertData和queryData的样例。

ClickHouse参数调优实践 表1 ClickHouse参数调优汇总 参数名 参数描述 默认值 建议值 是否需要重启生效 max_memory_usage_for_all_queries 单台服务器上所有查询的内存使用量，默认没有限制。建议根据机器的总内存，预留一部分空间，防止内存不够导致服务或者机器宕机。 0 机器总内存的80% 否 max_memory_usage 单个查询在单台服务器的能使用的最大内存。 10G 50GB 否（新版本可通过多租户方式配置） max_bytes_before_external_group_by 确定了在GROUP BY中启动将临时数据转存到磁盘上的内存阈值。默认值为0表示这项功能将被禁用。一般：设置为max_memory_usage/2。 0 25GB 否 max_execution_time 单次查询耗时的最长时间，单位为秒。默认没有限制。 0 300 否 max_threads 执行请求的最大线程数。默认情况下是按照机器CPU核数自动确定的。单并发情况下线程数越大越好（该值要小于CPU核数），多并发情况建议设置为CPU核数/2的值。 CPU核数/2 64 否 max_result_rows 限制返回结果行数，默认为0不限制。 0 100000 否 distributed_product_mode 默认SQL中的子查询不允许使用分布式表，修改为local表示将子查询中对分布式表的查询转换为对应的本地表。 deny 根据场景定： deny/local/global/allow 否 background_pool_size 后台用于merge的线程池大小。 16 64 否 log_queries system.query_log表的开关。默认值为0，不存在该表。修改为1，系统会自动创建system.query_log表，并记录每次query的日志信息。 0 1 否 skip_unavailable_shards 当通过分布式表查询时，遇到无效的shard是否跳过。默认值为0表示不跳过，抛异常。设置值为1表示跳过无效shard。 0 建议使用默认值。异常时，调整为1，提供有损服务。 否 max_bytes_before_external_sort 如果没有足够的内存，可以使用该参数来设置外部排序（在磁盘中创建一些临时文件）。默认为0表示禁用外部排序功能，当内存不够时直接抛错，设置了该值order by可以正常完成，但是速度非常慢。 0 25GB 否 keep_alive_timeout 服务端与客户端保持长连接的时长，单位为秒。 10 600 否 max_concurrent_queries 最大支持的查询并发。 100 150 否 session_timeout_ms Clickhouse服务和ZooKeeper保持的会话时长，超过该时间ZooKeeper还收不到Clickhouse的心跳信息，会将与Clickhouse的session断开。 3000 120000 否 父主题： ClickHouse数据库调优

通过表属性修改方式创建projection 在创建好projection后还可以对projection进行修改，具体语句如下： ALTER TABLE test_projection_table ADD PROJECTION projection_3( SELECT type, level GROUP BY type, level )

projection定义 CREATE TABLE test_projection_table( level String, type String, name String, city String, time DateTime64, PROJECTION projection_1( SELECT level, count() GROUP BY level ), PROJECTION projection_2( SELECT type, count() GROUP BY type ) ) ENGINE = MergeTree() ORDER BY (name, level, type)

Projection的使用 如下SQL查询的时候会走表达式： SELECT type, count() FROM test_projection_table WHERE type = 'A' GROUP BY type; 而如下SQL不会走projection，因为city不在projection的定义中。 SELECT city, count() FROM test_projection_table WHERE type = 'A' GROUP BY city; 具体可以通过explain查看执行计划，如果出现ReadFromStorage (MergeTree(with projection)) ，表示命中projection。

表引擎选择建议 自助报表分析、行为数据分析，在不涉及重复数据聚合的情况下，建议使用ReplicatedMergeTree表引擎。 涉及到物化视图等聚合函数的场景，建议使用ReplicatedAggregatingMergeTree表引擎。 经常有数据去重或有update修改数据的场景下，建议使用ReplacingMergeTree表引擎，配合使用argMax函数获取最新数据。 表1 应用场景列表 引擎名称 应用场景 MergeTree ClickHouse中最重要的引擎，基于分区键（partitioning key）的数据分区分块存储、前缀稀疏索引（order by和primary key）。 ReplacingMergeTree 相对于MergeTree，它会用最新的数据覆盖具有相同主键的重复项。 删除老数据的操作是在分区异步merge的时候进行处理，只有同一个分区的数据才会被去重，分区间及shard间重复数据不会被去重，所以应用侧想要获取到最新数据，需要配合argMax函数一起使用。 SummingMergeTree 当合并SummingMergeTree表的数据片段时，ClickHouse会把所有具有相同主键的行进行汇总，将同一主键的行替换为包含sum后的一行记录。 如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。 AggregatingMergeTree 该引擎继承自MergeTree，并改变了数据片段的合并逻辑。 ClickHouse会将一个数据片段内所有具有相同主键（准确的说是排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。可以使用AggregatingMergeTree表引擎来做增量数据的聚合统计，包括物化视图的数据聚合。 CollapsingMergeTree 在创建时与MergeTree基本一样，除了最后多了一个参数，需要指定Sign位（必须是Int8类型）。 CollapsingMergeTree会异步地删除（折叠）除了特定列Sign1和-1值以外的所有字段的值重复的行。 VersionedCollapsingMergeTree 是CollapsingMergeTree的升级，使用不同的collapsing算法，该算法允许使用多个线程以任何顺序插入数据。 Replicated*MergeTree 只有Replicated*MergeTree系列引擎是上面介绍的引擎的多副本版本，为了提升数据和服务的可靠性，建议使用副本引擎： ReplicatedMergeTree ReplicatedSummingMergeTree ReplicatedReplacingMergeTree ReplicatedAggregatingMergeTree ReplicatedCollapsingMergeTree ReplicatedVersionedCollapsingMergeTree ReplicatedGraphiteMergeTree

ClickHouse集群业务规划 集群规模 建议单集群不超过256节点规模。 集群负载 对于不同业务负载的业务，需要分开集群部署，便于不同负载的业务进行资源隔离。 集群并发 由于ClickHouse单个SQL会最大化使用每个主机上的CPU/内存/IO资源，对于复杂SQL查询（复杂聚合、复杂join计算）能够支持50~100并发，对于简单的SQL查询，支持100~200左右查询。 如果集群有混合负载（要求极致性能的点查/范围查询和有大数据量聚合及join查询），建议将不同类型的负载拆分到不同集群；对于集群规划有远远超过100个并发业务系统，也需要设计将业务分摊到不同的集群。 父主题： ClickHouse集群规划

使用说明 分布式表名称：default.my_table_dis。 本地表名称：default.my_table_local。 通过“AS”关联分布式表和本地表，保证分布式表的字段定义跟本地表一致。 分布式表引擎的参数说明： default_cluster：集群名称。 default：本地表所在库名。 my_table_local：本地表名。 rand()：可选参数，分片键（sharding key），可以是表中一列的原始数据（如did），也可以是函数调用的结果。 如轮训方式：rand()，表示在写入数据时直接将数据插入到分布式表，分布式表引擎会按轮训算法将数据发送到各个分片。 该键是写分布式表保证数据均匀分布在各分片的唯一方式。

创建本地复制表和分布式表 使用ReplicatedMergeTree引擎创建复制表。 详细的语法说明请参考：https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/replication/#creating-replicated-tables。 例如，在default_cluster_1集群节点上和default数据库下创建表名为test的ReplicatedMergeTree表： CREATE TABLE default.test ON CLUSTER default_cluster_1 ( `EventDate` DateTime, `id` UInt64 ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id; 参数说明如下： ON CLUSTER语法表示分布式DDL，即执行一次就可在集群所有实例上创建同样的本地表。 default_cluster_1为查看ClickHouse服务cluster等环境参数信息中查询到的cluster集群标识符。 ReplicatedMergeTree引擎族接收两个参数： ZooKeeper中该表相关数据的存储路径。 该路径必须在/clickhouse目录下，否则后续可能因为ZooKeeper配额不够导致数据插入失败。 为了避免不同表在ZooKeeper上数据冲突，目录格式必须按照如下规范填写： /clickhouse/tables/{shard}/default/test，其中/clickhouse/tables/{shard}为固定值，default为数据库名，test为创建的表名。 副本名称，一般用{replica}即可。 CREATE TABLE default.test ON CLUSTER default_cluster_1 ( `EventDate` DateTime, `id` UInt64 ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐ │ node-group-1tXED0002 │ 9000 │ 0 │ │ 5 │ 3 │ │ node-group-1tXED0003 │ 9000 │ 0 │ │ 4 │ 3 │ │ node-master1dOnG │ 9000 │ 0 │ │ 3 │ 3 │ └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘ ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐ │ node-master3QsRI │ 9000 │ 0 │ │ 2 │ 0 │ │ node-group-1tXED0001 │ 9000 │ 0 │ │ 1 │ 0 │ │ node-master2OXQS │ 9000 │ 0 │ │ 0 │ 0 │ └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘ 6 rows in set. Elapsed: 0.189 sec. 使用Distributed引擎创建分布式表。 例如，以下将在default_cluster_1集群节点上和default数据库下创建名为test_all 的Distributed表： CREATE TABLE default.test_all ON CLUSTER default_cluster_1 ( `EventDate` DateTime, `id` UInt64 ) ENGINE = Distributed(default_cluster_1, default, test, rand()); CREATE TABLE default.test_all ON CLUSTER default_cluster_1 ( `EventDate` DateTime, `id` UInt64 ) ENGINE = Distributed(default_cluster_1, default, test, rand()) ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐ │ node-group-1tXED0002 │ 9000 │ 0 │ │ 5 │ 0 │ │ node-master3QsRI │ 9000 │ 0 │ │ 4 │ 0 │ │ node-group-1tXED0003 │ 9000 │ 0 │ │ 3 │ 0 │ │ node-group-1tXED0001 │ 9000 │ 0 │ │ 2 │ 0 │ │ node-master1dOnG │ 9000 │ 0 │ │ 1 │ 0 │ │ node-master2OXQS │ 9000 │ 0 │ │ 0 │ 0 │ └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘ 6 rows in set. Elapsed: 0.115 sec. Distributed引擎需要以下几个参数： default_cluster_1为查看ClickHouse服务cluster等环境参数信息中查询到的cluster集群标识符。 default本地表所在的数据库名称。 test为本地表名称。 （可选的）分片键（sharding key） 该键与config.xml中配置的分片权重（weight）一同决定写入分布式表时的路由，即数据最终落到哪个物理表上。它可以是表中一列的原始数据（如site_id），也可以是函数调用的结果，如上面的SQL语句采用了随机值rand()。注意该键要尽量保证数据均匀分布，另外一个常用的操作是采用区分度较高的列的哈希值，如intHash64(user_id)。

常见问题 执行连接ClickHouse组件客户端命令后，登录报错“Connection refused”。 请检查当前集群是否为定制端口（在创建集群时将“组件端口”参数选择为“定制”），如果为定制端口，则需要将连接ClickHouse组件客户端命令中所使用的端口替换为下表中的“定制默认端口”。 配置参数 开源默认端口 定制默认端口 端口说明 interserver_http_port 9009 9009 用于在ClickHouse server间通信的http端口。 interserver_https_port 9010 9010 用于在ClickHouse server间通信的https端口。 http_port 8123 8123 用于通过http连接到ClickHouse server的端口。 https_port 8443 8443 用于通过https连接到ClickHouse server的端口。 tcp_port 9000 9000 用于客户端通过TCP连接到ClickHouse server的端口。 tcp_port_secure 9440 9440 用于客户端通过TCP SSL连接到ClickHouse server的端口。 lb_tcp_port 21424 21424 ClickHouseBalancer监听的tcp端口号。 lb_http_port 21425 21425 ClickHouseBalancer监听的http端口号。 lb_https_port 21426 21426 ClickHouseBalancer监听的https端口号。 lb_tcp_secure_port 21428 21428 ClickHouseBalancer监听的tcp ssl端口号。