教你如何在clickhouse上建表

一、简介

在 ClickHouse 中，表引擎是表的核心组成部分，决定了表的存储方式、数据分布、索引机制以及查询性能。ClickHouse 提供了多种表引擎，每种引擎适用于不同的场景。

• 表引擎决定了如何存储表的数据。包括： 

• • 数据的存储方式和位置，写到哪里以及从哪里读取数据
  • 支持哪些查询以及如何支持
  • 并发数据访问
  • 索引的使用（如果存在）
  • 是否可以执行多线程请求
  • 数据复制参数

表引擎是 ClickHouse 的核心特性之一，它定义了数据的存储和查询方式。常见的表引擎分为以下几类：见第二章

二、表引擎

2.1 MergeTree 系列

MergeTree 系列是 ClickHouse 最常用的表引擎，适用于大规模数据分析。它支持高效的数据插入、更新和查询。这里只举例了几个常用引擎，更多可以参考官网：

MergeTree Engine Family | ClickHouse Docs

**MergeTree**

- 基础引擎，支持按主键排序和分区。

- 适用于时间序列数据、日志数据等。

- 示例：

ENGINE

ENGINE — Name and parameters of the engine. ENGINE = MergeTree(). The MergeTree engine has no parameters.

CREATE TABLE merge_tree on cluster ck_cluster(
        id UInt32,
        timestamp DateTime,
        value Float64
    ) ENGINE = MergeTree()
    PARTITION BY toYYYYMM(timestamp)
    ORDER BY (id, timestamp);

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [[NOT] NULL] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr1] [COMMENT ...] [CODEC(codec1)] [STATISTICS(stat1)] [TTL expr1] [PRIMARY KEY] [SETTINGS (name = value, ...)],
    name2 [type2] [[NOT] NULL] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr2] [COMMENT ...] [CODEC(codec2)] [STATISTICS(stat2)] [TTL expr2] [PRIMARY KEY] [SETTINGS (name = value, ...)],
    ...
    INDEX index_name1 expr1 TYPE type1(...) [GRANULARITY value1],
    INDEX index_name2 expr2 TYPE type2(...) [GRANULARITY value2],
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name = value, ...]

**ReplacedMergeTree**

- 在 MergeTree 的基础上，支持副本

适用于按分区整体更新的场景。临时表建议使用下

ReplacedMergeTree 主要用于替换整个分区的数据，它会在插入新分区数据时删除旧分区数据。

- 示例：

CREATE TABLE replaced_merge_tree on cluster ck_cluster(
        id UInt32,
        timestamp DateTime,
        value Float64
    ) ENGINE = ReplacedMergeTree('/clickhouse/tables/{shard}/{database}/replaced_merge_tree', '{replica}')
    PARTITION BY toYYYYMM(timestamp)
    ORDER BY (id, timestamp);

**ReplacingMergeTree**

- 在 MergeTree 的基础上，支持去重（根据排序键去重）。主要用于数据去重，通过 FINAL 语句或后台合并机制删除重复数据。

- 适用于需要去重的场景。不能保证数据在插入时去重，只能在合并时去重。

• ReplacingMergeTree 是通过 order by 参数指定的字段作为唯一约束进行去重的
• 去重只能在同一分区的内部进行，不能执行跨分区的去重
• 允许定义一个 version 列（通常是时间戳或自增 ID），在合并时保留最大版本的数据。如果没有定义 version 列，则在合并时会随机保留一条记录。
• 去重的时机：a) 在 clickhouse 新版本中同一批次的数据插入时会进行去重；b) 分区进行自动或手动合并操作时会进行去重
• 去重后数据的保留：a) 当指定了版本字段时，会将版本字段值最大的那条数据保留，若版本字段最大值数据有多条则保留顺序为最后插入的那条数据；b) 没有指定版本字段时则保留顺序为最后插入的那条数据

- 示例：

CREATE TABLE replacing_merge_tree on cluster ck_cluster(
        id UInt32,
        timestamp DateTime,
        value Float64
    ) ENGINE = ReplacingMergeTree('/clickhouse/tables/{shard}/{database}/replacing_merge_tree', '{replica}')
    PARTITION BY toYYYYMM(timestamp)
    ORDER BY (id, timestamp);
    

**ReplicatedReplacingMergeTree**

ReplicatedReplacingMergeTree 是 ReplacingMergeTree 的分布式版本，支持数据复制和高可用。

特点：

• 在 ReplacingMergeTree 的基础上，支持数据复制。
• 适用于分布式集群环境

CREATE TABLE replicated_replacing_merge_tree on cluster ck_cluster (
    id UInt32,
    value String,
    timestamp DateTime
) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/{database}/replicated_replacing_merge_tree', '{replica}')
ORDER BY id;

**SummingMergeTree**

- 在 MergeTree 的基础上，支持按排序键聚合数据。

- 适用于需要预聚合的场景。

- 示例：

CREATE TABLE summing_merge_tree on cluster ck_cluster (
        id UInt32,
        timestamp DateTime,
        value Float64
    ) ENGINE = SummingMergeTree('/clickhouse/tables/{shard}/{database}/summing_merge_tree', '{replica}')
    PARTITION BY toYYYYMM(timestamp)
    ORDER BY (id, timestamp);

2.2 Distributed

- 分布式表引擎，用于跨多个节点分布数据。

在理解这个分布式表引擎前，需要理解两个概念（分片和副本）

2.2.1 分片与副本

数据分片是将数据进行横向切分，这是一种在面对海量数据的场景下，解决存储和查询瓶颈的有效手段，是一种分治思想的体现。ClickHouse支持分片，而分片则依赖集群。每个集群由1到多个分片组成，而每个分片则对应了ClickHouse的1个服务节点。分片的数量上限取决于节点数量（1个分片只能对应1个服务节点）。

ClickHouse拥有高度自动化的分片功能。ClickHouse提供了本地表 （Local Table，可以简单理解为有路径的表）与分布式表（Distributed Table）的概念。一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。

分片是指包含部分数据的服务器，要读取所有的数据，必须访问所有的分片。

副本是指存储分片备份数据的服务器，要读取所有的数据，访问任意副本上的数据即可。

• shard：

分片，一个clickhouse集群可以分多个分片，每个分片可以存储数据，这里分片可以理解为clickhouse机器中的每个节点。这里可以配置一个或者任意多个分片，在每个分片中可以配置一个或任意多个副本，不同分片可配置不同数量的副本。如果只是配置一个分片，这种情况下查询操作应该称为远程查询，而不是分布式查询。

• replica：

每个分片的副本，默认每个分片配置了一个副本。也可以配置多个。如果配置了副本，读取操作可以从每个分片里选择一个可用的副本。如果副本不可用，会依次选择下个副本进行连接。该机制利于系统的可用性。

副本只支持以下表引擎中:

• ReplicatedMergeTree
• ReplicatedSummingMergeTree
• ReplicatedReplacingMergeTree
• ReplicatedAggregatingMergeTree
• ReplicatedCollapsingMergeTree
• ReplicatedVersionedCollapsingMergeTree
• ReplicatedGraphiteMergeTree

Tip

Adding Replicated is optional in ClickHouse Cloud, as all of the tables are replicated.

2.2.2 Distributed 分布式表

分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。

Distributed 分布式引擎可以理解为ES集群中的 ClientNode

读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。

我们可以引申理解它就相当于关系型数据库中的视图概念。

示例：ENGINE = Distributed(<集群名称>, <库名>, <表名>[, sharding_key])

与分布式表对应的是本地表，

也就是上面的<表名>参数，查询分布式表的时候，ClickHouse会自动查询所有分片，然后将结果汇总后返回。

向分布式表插入数据

ClickHouse会根据分片权重将数据分散插入到各个分片中

默认情况下，每个分片中所有副本都会写入数据

或者通过参数internal_replication配置每个分片只写入其中一个副本，使用复制表(ReplicateMergeTree)管理数据的副本。<internal_replication>true</internal_replication>

- 示例：

CREATE TABLE example_distributed on cluster ck_cluster(
        id UInt32,