华为云用户手册

多个Flink作业或者insert into语句写同一张Gauss for MySQL时建议过滤回撤数据 当有多个Flink作业写同一张MySQL表时，其中一个Flink作业发送回撤数据（-D、-U）到目标表删除整行数据，再插入本次更新的数据，导致其他作业写入的字段全部丢失。 优化前SQL： create table source-A(id,user_id)with('connector' = 'kafka');create table source-B(id,org_id)with('connector' = 'kafka');create table sink-A(id,user_id)with('connector' = 'jdbc''url' = 'jdbc:mysql://****','table-name' = 'sink-table');create table sink-B(id,org_id)with('connector' = 'jdbc''url' = 'jdbc:mysql://****','table-name' = 'sink-table');insert into sink-A select id,user_id from source-A;insert into sink-B select id,org_id from source-B; 优化后SQL： create table source-A(id,user_id)with('connector' = 'kafka');create table source-B(id,org_id)with('connector' = 'kafka');create table sink-A(id,user_id)with('connector' = 'jdbc''url' = 'jdbc:mysql://****','table-name' = 'sink-table','filter.record.enabled' = 'true');create table sink-B(id,org_id)with('connector' = 'jdbc''url' = 'jdbc:mysql://****','table-name' = 'sink-table','filter.record.enabled' = 'true');insert into sink-A select id,user_id from source-A;insert into sink-B select id,org_id from source-B;

多表left join时建议lookup join在所有双流join后 多表left join时建议lookup join在所有双流join后，否则下游有left join LATERAL TABLE时会发生乱序。 图3 多表left join 虽然左表已经定义主键，但是经过lookup join后下游left join时无法推断左流主键，导致左流所有历史数据都存储在状态，右流数据到达后会从最新的状态开始依次回撤左流状态中的每一条数据，经过LATERAL TABLE每一条source数据又与lateral table自关联，数据乱序。 查看打印结果可以看到连续多条“-D”消息，并且最后一条数据错误，因此建议lookup join放在双流join后执行。 图4 连续多条“-D”消息 优化前SQL： select... from t1left join t2 FOR SYSTEM_TIME AS OF t1.proctime AS t21 on t21.id = t1.idleft join t3 on t3.id = t1.idleft join LATERAL TABLE(udtf()) AS t4(res1,res2.res3,res4) on true 优化后SQL： select... from t1left join t3 on t3.id = t1.idleft join t2 FOR SYSTEM_TIME AS OF t1.proctime AS t21 on t21.id = t1.idleft join LATERAL TABLE(udtf()) AS t4(res1,res2.res3,res4) on true

使用char数据类型时指定精度或者改用string类型 使用“cast(id as char)”数据类型转换时，结果只截取第一位，导致数据错误。如果转换字段正好是主键字段则会丢失大量数据。 配置“table.exec.legacy-cast-behaviour=ENABLED”也可以解决转换发生错误的问题，但是不建议使用。 在Flink 1.15之前，可以通过将“table.exec.legacy-cast-behaviour”设置为“enabled”来启用旧版本的类型转换行为。但在Flink 1.15及之后版本中，默认情况下该标志被禁用，将导致以下行为： 转换为CHAR/VARCHAR/BINARY/VARBINARY时禁用修剪/填充操作。 CAST操作永远不会失败，而是返回NULL，类似于TRY_CAST，但不会推断正确的类型。 对于某些转换为CHAR/VARCHAR/STRING的格式化操作，结果可能略有不同。 我们不建议使用此标志，并强烈建议新项目保持禁用该标志并使用新的类型转换行为。该标志将在未来的Flink版本中被移除。 优化前SQL： selectcast(id as char) as id,... from t1 优化后SQL： selectcast(id as string) as id,... from t1

聚合函数中case when语法改写成filter语法 在聚合函数中，FILTER是更符合SQL标准用于过滤的语法，并且能获得更多的性能提升。FILTER是用于聚合函数的修饰符，用于限制聚合中使用的值。 【示例】在某些场景下需要从不同维度来统计UV，如Android中的UV，iPhone中的UV，Web中的UV和总UV，这时可能会使用如下CASE WHEN语法。 修改前： SELECTday,COUNT(DISTINCT user_id) AS total_uv,COUNT(DISTINCT CASE WHEN flag IN (android', "iphone'") THEN user_id ELSE NULL END) AS app_uv,COUNT(DISTINCT CASE WHEN flag IN(wap', 'other') THEN user_id ELSE NULL END) AS web_uvFROM TGROUP BY day 修改后： SELECTday,COUNT(DISTINCT user_id) AS total_uv,COUNT(DISTINCT user_id) FILTER (WHERE flag IN ('android', 'iphone')) AS app_uv,COUNT(DISTINCT user_id) FILTER(WHERE flag IN ('wap', 'other'))AS web_uvFROM TGROUP BY day Flink SQL优化器可以识别相同的distinct key上的不同过滤器参数。例如示例中三个COUNT DISTINCT都在user_id列上。Flink可以只使用一个共享状态实例，而不是三个状态实例，以减少状态访问和状态大小，在某些工作负载下可以获得显著的性能提升。

Doris建表规则 在创建Doris表指定分桶buckets时，每个桶的数据大小应保持在100MB~3GB之间，单分区中最大分桶数量不超过5000。 表数据超过5亿条以上必须设置分区分桶策略。 表的分桶列不要设置太多，一般情况下设置1或2个列即可，同时需要兼顾数据分布均匀和查询吞吐均衡。 数据均匀是为了避免某些桶的数据存在倾斜影响数据均衡和查询效率。 查询吞吐利用查询SQL的分桶剪裁优化避免了全桶扫描，以提升查询性能。 分桶列的选取：优先考虑数据较为均匀且常用于查询条件的列作为分桶列。 可使用以下方法分析是否会导致数据倾斜： SELECT a, b, COUNT(*) FROM tab GROUP BY a,b; 命令执行后查看各个分组的数据条数是否相差不大，如果相差超过2/3或1/2，则需要重新选择分桶字段。 2千万以内数据禁止使用动态分区。动态分区会自动创建分区，而小表用户关注不到，会创建出大量不使用的分区分桶。 创建表时，排序键key不能太多，一般建议3~5个；太多key会导致数据写入较慢，影响数据导入性能。 不使用Auto Bucket，需按照已有的数据量来进行分区分桶，能更好的提升导入及查询性能。Auto Bucket会造成Tablet数量过多，最终导致有大量的小文件。 创建表时的副本数必须至少为2，默认是3，禁止使用单副本。 没有聚合函数列的表不应该被创建为AGGREGATE表。 创建主键表时需保持主键的列唯一，不建议将所有列都设置为主键列，且主键表需设置value列。主键表不建议用于数据去重场景。

Doris建表建议 单表物化视图不能超过6个，物化视图不建议嵌套，不建议数据写入时通过物化视图进行重型聚合和Join计算等ETL任务。 对于有大量历史分区数据，但是历史数据比较少，或者数据不均衡，或者数据查询概率较小的情况，可以创建历史分区（比如年分区，月分区），将所有历史数据放到对应分区里。 创建历史分区方式为：FROM ("2000-01-01") TO ("2022-01-01") INTERVAL 1 YEAR 1千万~2亿以内数据为了方便可以不设置分区（Doris内部有一个默认分区），直接用分桶策略即可。 如果分桶字段存在30%以上的数据倾斜，则禁止使用Hash分桶策略，改为使用Random分桶策略，相关命令为： Create table ... DISTRIBUTED BY RANDOM BUCKETS 10 ... 建表时第一个字段一定是最常查询使用的列，默认有前缀索引快速查询能力，选取最常查询且高基数的列作为前缀索引，默认将一行数据的前36个字节作为这行数据的前缀索引（varchar类型的列只能匹配20个字节，并且会匹配不足36个字节截断前缀索引） 。 超过亿级别的数据，如果有模糊匹配或者等值/in条件，可以使用倒排索引（Doris 2.x版本开始支持）或者Bloomfilter。如果是低基数列的正交查询适合使用bitmap索引（bitmap索引的基数在10000~100000之间效果较好）。 建表时需要提前规划将来要使用的字段个数，可以多预留几十个字段，类型包括整型、字符型等。避免将来字段不够使用，需要较高代价临时去添加字段。

Flink流式写Hudi表建议 使用SparkSQL统一建表。 推荐使用Spark异步任务对Hudi表进行Compaction。 表名必须以字母或下划线开头，不能以数字开头。 表名只能包含字母、数字、下划线。 表名长度不能超过128个字符。 表名中不能包含空格和特殊字符，如冒号、分号、斜杠等。 表名不区分大小写，但建议使用小写字母。 Hive保留关键字不能作为表名，如select、from、where等。 父主题： Flink on Hudi开发规范

优化shuffle并行度，提升Spark加工效率 所谓的shuffle并发度如下图所示： 集群默认是200，作业可以单独设置。如果发现瓶颈stage（执行时间长），且分配给当前作业的核数大于当前的并发数，说明并发度不足。通过以下配置优化。 场景 配置项 集群默认值 调整后 Jar作业 spark.default.parallelism 200 按实际作业可用资源2倍设置 SQL作业 spark.sql.shuffle.partitions 200 按实际作业可用资源2倍设置 hudi入库作业 hoodie.upsert.shuffle.parallelism 200 非bucket表使用，按实际作业可用资源2倍设置 动态资源调度情况下（spark.dynamicAllocation.enabled= true）时，资源按照spark.dynamicAllocation.maxExecutors评估。

Spark加工Hudi表时其他参数优化 设置spark.sql.enableToString=false，降低Spark解析复杂SQL时候内存使用，提升解析效率。 设置spark.speculation=false，关闭推测执行，开启该参数会带来额外的cpu消耗，同时Hudi不支持启动该参数，启用该参数写Hudi有概率导致文件损坏。 配置项 集群默认值 调整后 --conf spark.sql.enableToString true false --conf spark.speculation false false

初始化Hudi表时，可以使用BulkInsert方式快速写入数据 示例： set hoodie.combine.before.insert=true; --入库前去重，如果数据没有重复 该参数无需设置。set hoodie.datasource.write.operation = bulk_insert; --指定写入方式为bulk insert方式。set hoodie.bulkinsert.shuffle.parallelism = 4; --指定bulk_insert写入时的并行度，等于写入完成后保存的分区parquet文件数。insert into dsrTable select * from srcTabble

调整Spark调度参数优化OBS场景下Spark调度时延 开启对于OBS存储，可以关闭Spark的本地性进行优化，尽可能提升Spark调度效率 配置项 集群默认值 调整后 --conf spark.locality.wait 3s 0s --conf spark.locality.wait.process 3s 0s --conf spark.locality.wait.node 3s 0s --conf spark.locality.wait.rack 3s 0s

优化Spark Shuffle参数提升Hudi写入效率 开启spark.shuffle.readHostLocalDisk=true，本地磁盘读取shuffle数据，减少网络传输的开销。 开启spark.io.encryption.enabled=false，关闭shuffle过程写加密磁盘，提升shuffle效率。 开启spark.shuffle.service.enabled=true，启动shuffle服务，提升任务shuffle的稳定性。 配置项 集群默认值 调整后 --conf spark.shuffle.readHostLocalDisk false true --conf spark.io.encryption.enabled true false --conf spark.shuffle.service.enabled false true

规则 Hudi表必须执行Archive。 对于Hudi的MOR类型和COW类型的表，都需要开启Archive。 Hudi表在写入数据时会自动判断是否需要执行Archive，因为Archive的开关默认打开(hoodie.archive.automatic默认为true)。 Archive操作并不是每次写数据时都会触发，至少需要满足以下两个条件： Hudi表满足hoodie.keep.max.commits设置的阈值。如果是Flink写hudi至少提交的checkpoint要超过这个阈值；如果是Spark写hudi，写Hudi的次数要超过这个阈值。 Hudi表做过Clean，如果没有做过Clean就不会执行Archive（ MRS 3.3.1-LTS及以后版本，忽略此项条件）。

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

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

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。

数据分布设计 Shard数据分片均匀分布 建议用户的数据均匀分布到集群中的多个shard分片，如图1所示有3个分片。 假如有30 GB数据需要写入到集群中，需要将30 GB数据均匀切分后分别放到shard-1、shard-2和shard-3的3个分片节点中，以充分发挥MPP查询时并行计算能力，避免数据在shard间倾斜计算出现木桶效应，导致SQL查询性能较差。 可通过弹性负载均衡（Elastic Load Balance，简称ELB）访问ClickHouse，来实现数据均匀。 Shard内数据副本高可靠存储 数据写入单shard中的一个副本后，ClickHouse会自动异步将数据同步到其他副本，如图1中的shard-3。 如果将10GB数据导入ClickHouse Node 5节点副本，ClickHouse会自动异步将数据同步到ClickHouse Node 6节点副本，保证shard-3分片数据的高可靠性存储。

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)

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

建议 一次只插入一个分区内的数据 如果数据属于不同的分区，则每次插入，不同分区的数据会独立生成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;

规则 写本地表，查询分布式表，提升写入和查询性能，保证写入和查询的数据一致性。 只有在去重诉求的场景下，可以使用分布式表插入，通过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的速度跟不上目录生成的速度，数据目录越来越多就会抛出这个异常。

ClickHouse系统调优 通过 FusionInsight Manager查看主机上的CPU、内存、I/O和网络资源使用情况，确认这些资源是否已被充分利用，分以下几种情况： 每个节点资源占用都比较均匀 通过观察资源在每个节点都使用比较均匀，说明系统资源使用比较正常，可以先不关注，可以去分析SQL语句是否有进一步优化的余地。 有个别节点资源占用比较高 如果观察到个别节点占用资源较高，需要针对占用资源较高的节点分析，分析当前的SQL语句是什么原因导致部分节点占用比其他节点更多资源，是计算还是数据存储倾斜导致，或者是软件bug导致。 每个节点资源占用都比较高 如果集群所有节点资源占用都比较高，说明集群整体比较忙，需要单独确认需要调优的SQL语句，单独调优。如果SQL也无调优余地，集群资源达到瓶颈，需要通过扩容来提升查询性能，达到调优目标。 父主题： ClickHouse数据库调优

一级索引设计 在建表设计时指定主键字段的建议：按查询时最常使用且过滤性最高的字段作为主键。依次按照访问频度从高到低、维度基数从小到大来排列。数据是按照主键排序存储的，查询的时候，通过主键可以快速筛选数据，合理的主键设计，能够大大减少读取的数据量，提升查询性能。例如所有的分析，都需要指定业务的id，则可以将业务id字段作为主键的第一个字段顺序。 根据业务场景合理设计稀疏索引粒度 ClickHouse的主键索引采用的是稀疏索引存储，稀疏索引的默认采样粒度是8192行，即每8192行取一条记录在索引文件中，实践建议： 索引粒度越小，对于小范围的查询更有效，避免查询资源的浪费； 索引粒度越大，则索引文件越小，索引文件的处理会更快； 超过10亿的表索引粒度可设为16384，其他设为8192或者更小值。

规则 物化视图（Materialized View）显式指定聚合表。 在创建物化视图时，使用TO关键字为物化视图指定数据存储表。 如果不显示指定聚合表，则会创建隐式表.inner.mv1，与物化视图绑定。 用于数据预聚合的物化视图，聚合表使用聚合引擎。 如果不用聚合引擎，则每次数据插入，会对明细表的全量数据重新计算，而不是只处理增量数据。 聚合表中，聚合指标定义成聚合类型（AggregateFunction）。 物化视图的指标列与聚合表中对应字段名称一致，命名规范如下： {aggrateFunction}_{columnName}_state 聚合表创建样例： CREATE TABLE counter_daily_agg ON CLUSTER default_cluster(day DateTime,device UInt32,count UInt64,max_value_state AggregateFunction(max, Float32),min_value_state AggregateFunction(min, Float32),avg_value_state AggregateFunction(avg, Float32))ENGINE = SummingMergeTree()PARTITION BY tuple()ORDER BY (device, day);

二级跳数索引设计 跳数索引使用参考： 使用说明 对于*MergeTree引擎，支持配置跳数索引，即一种数据局部聚合的粗糙索引，对数据块创建索引，选择性的保留一部分原始数据（minmax、set）， 或者是保留计算后的中间数据（bloomfilter）。在查询时，选择忽略加载不会包含结果的数据块，从而达到加速查询的效果。 索引定义 INDEX index_name expr TYPE type（...） GRANULARITY granularity_value Expr：属性表达式，基于字段或者字段的表达式来创建索引； type（...）：支持的索引类型，minmax、set等； Granularity：创建索引的记录粒度。比如index_granularity = 8192，granularity配置为3，则使用8192*3条记录创建一条索引数据。 创建索引样例 CREATE TABLE skip_index_test ON CLUSTER default_cluster(ID String,URL String,Code String,EventTime Date,INDEX a ID TYPE minmax GRANULARITY 5,INDEX b (length(ID) * 8) TYPE set(100) GRANULARITY 5,INDEX c (ID, Code) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5,INDEX d ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5,INDEX e ID TYPE bloom_filter(0.025) GRANULARITY 5) ENGINE = MergeTree()ORDER BY ID ; minmax索引 记录了一段数据范围内的最小和最大极值，其索引的作用类似分区目录的minmax索引，能够快速跳过无用的数据区间。 INDEX a ID TYPE minmax GRANULARITY 5 上述示例中minmax索引会记录这段数据区间内ID字段的极值。极值的计算涉及每5个index_granularity区间中的数据。 set索引 直接记录了声明字段或表达式的取值（唯一值，无重复），其完整形式为set（max_rows），其中max_rows是一个阈值，表示在一个index_granularity内，索引最多记录的数据行数。如果max_rows=0，则表示无限制。 INDEX b (length(ID) * 8) TYPE set(100) GRANULARITY 5 上述示例中set索引会记录数据中ID的长度*8后的取值。其中，每个index_granularity内最多记录100条。 布隆过滤器 bloom_filter索引 为指定的列存储布隆过滤器。 可选的参数false_positive用来指定从布隆过滤器收到错误响应的几率。取值范围是 (0,1)，默认值：0.025。 支持的数据类型：Int*，UInt*，Float*，Enum，Date，DateTime，String，FixedString，Array，LowCardinality，Nullable。 ngrambf_v1索引 记录的是数据短语的布隆表过滤器，只支持String和FixedString数据类型。只能够提升in、notIn、like、equals和notEquals查询的性能，其完整形式为： ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) 这些参数是一个布隆过滤器的标准输入，如果接触过布隆过滤器，应该会对此十分熟悉。 具体的含义如下： n：token长度，依据n的长度将数据切割为token短语。 size_of_bloom_filter_in_bytes：布隆过滤器的大小。 number_of_hash_functions：布隆过滤器中使用Hash函数的个数。 random_seed：Hash函数的随机种子。 tokenbf_v1索引 是ngrambf_v1的变种，同样也是一种布隆过滤器索引。tokenbf_v1除了短语token的处理方法外，其他与ngrambf_v1是完全一样的。tokenbf_v1会自动按照非字符的、数字的字符串分割token。 INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5 索引创建详见官方文档 https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-data_skipping-indexes 建表后再创建索引 ALTER TABLE table_name add INDEX min_max_index (etl_time) TYPE minmax GRANULARITY 3; 删除索引 ALTER TABLE table_name DROP INDEX min_max_index; 单表跳数索引数量 由于索引的创建对数据导入性能有影响，建议单表跳数索引的总数量控制在5个以内。

ClickHouse容量规划设计 为了能够更好的发挥ClickHouse分布式查询能力，在集群规划阶段需要合理设计集群数据分布存储。 当前ClickHouse能力为单机磁盘容量达到80%后会上报告警信息，磁盘容量达90%后集群会处于只读状态。 出现磁盘告警信息后需要考虑是否是容量不足问题，如果是容量不足问题需要尽快考虑集群扩容，提升集群整体容量存储。 ClickHouse节点及容量规划如下： 磁盘规划 由于ClickHouseServer业务数据主要存储在本地磁盘上，数据量可能会随着集群使用时间增长而增长，通常建议ClickHouse数据盘单独挂载，元数据盘共享第一个数据盘目录。 磁盘实际容量 由于磁盘存在1MB = 1024KB或者1000KB的不同算法，一般来说，磁盘实际可用容量 = 磁盘标注容量 * 0.9。 例如磁盘标注容量为1.2 TB，实际容量为1200 * 0.9 = 1080 GB。 计算公式 假设历史数据量为H，每日增量为A，单节点磁盘容量为C，数据保留M天，集群副本数为R，则ClickHouseServer物理节点数计算公式如下： ClickHouseServer物理节点数N = [R * (H + A * M)] / C 父主题： ClickHouse集群规划

Hive JDBC驱动的加载 客户端程序以JDBC的形式连接HiveServer时，需要首先加载Hive的JDBC驱动类org.apache.hive.jdbc.HiveDriver。 故在客户端程序的开始，必须先使用当前类加载器加载该驱动类。 如果classpath下没有相应的jar包，则客户端程序抛出Class Not Found异常并退出。 如下： Class.forName("org.apache.hive.jdbc.HiveDriver").newInstance();

关闭数据库连接 客户端程序在执行完HQL之后，注意关闭数据库连接，以免内存泄露，同时这是一个良好的编程习惯。 需要关闭JDK的两个对象statement和connection。 如下： finally { if (null != statement) { statement.close(); } // 关闭JDBC连接 if (null != connection) { connection.close(); } }