clickhouse 利用docker安装流程 clickhouse怎么用

转载

definitely 2025-08-18 10:18:13

1. 简介与安装

ClickHouse 是俄罗斯的 Yandex https://www.yandex.com/ （跟 Google 一样做搜索的）开源的一套针对数据仓库场景的多维数据存储与检索工具，它通过针对性的设计力图解决海量多维度数据的查询性能问题。

安装 ClickHouse 在 Ubuntu 下是比较简单的，因为官方直接提供了源， https://clickhouse.yandex/reference_en.html#System%20requirements 直接 apt-get就可以了。（我没有自己编译安装过）

安装完之后，会自动添加一个名为 clickhouse

sudo service clickhouse-server start

也可以手动启动：

clickhouse-server --config-file=/etc/clickhouse-server/config.xml

服务端启动之后，就可以使用官方的客户端工具 clickhouse-client 连接上去看看了，之后的交互，类似 MySQL

2. 访问接口

ClickHouse 自己的 clickhouse-client默认配置下， HTTP 的服务是在 8123 端口上的，直接访问的话，可以得到一个 ok 的响应。（如果要外部访问，记得把配置中的 listen_host 加一个 0.0.0.0

HTTP 服务，查询的话， GET 或 POST 都可以，修改和创建，只能用 POST

GET "http://172.17.0.2:8123?query=select 2"

echo 'CREATE TABLE t (a UInt8) ENGINE = Memory' | POST 'http://172.17.0.2:8123/'
echo 'insert into t (a) values (10)' | POST 'http://172.17.0.2:8123/'
GET "http://172.17.0.2:8123?query=select * from t"
echo 'drop table t' | POST 'http://172.17.0.2:8123/'

访问地址中，可以通过请求参数，或者头，来指定一些环境配置项，比如 databasedatabaseuserpassword

https://clickhouse.yandex/reference_en.html#Settings 其它配置项

另外，对于用户名和密码，也可以通过 X-ClickHouse-User 和 X-ClickHouse-Key

3. 查询语言

ClickHouse 中有两种类型的解析器， full parser 和 data format parser ，前者是一个完整的 SQL 解析器，后者是一个高性能的流解析器。当语句被发到 ClickHouse 时，默认配置下前 1 MB 字节的数据会使用 full parser 来处理，剩下的数据就交给 data format parser 了，所以，像 insert

语法细节，整体上跟 MySQL 是一样的，当然， ClickHouse

比如，对于别名 Synonyms ， ClickHouse

select ((select 1) as n), n

这种语句，在 ClickHouse

3.1. CREATE TABLE

建表语句除了基本形式外，还有两个扩展形式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name
(
 name1 [type1] [DEFAULT | MATERIALIZED | ALIAS expr1],
 name2 [type2] [DEFAULT | MATERIALIZED | ALIAS expr2],
 ...
) ENGINE = engine

这是基本形式，如果引擎支持索引的话，索引可以在 ENGINE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name AS [db2.]name2 [ENGINE = engine]

第一种扩展形式，可以创建一个跟指定表完全一样的表，但是可以更换不同的引擎。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name ENGINE = engine AS SELECT ...

这种形式是“建表并填充”，表字段会自动根据 SELECT

试一下：

create table t (id UInt16, name String) ENGINE = Memory;
insert into t(id, name) values (1, 'abc'), (2, 'xxx');

各种引擎后面会专门介绍，这里就用 Memory

create table t2 as t;
insert into t2(id, name) values (1, 'abc'), (2, 'xxx');

create table t3 ENGINE = Memory as select * from t;

3.1.1. 默认值

默认值的处理方面， ClickHouse

数字类型，

0

字符串，空字符串。
数组，空数组。
日期，

0000-00-00

时间，

0000-00-00 00:00:00NULLs

同时，在字段类型方面，如果没有明确指定字段类型，但是指定了默认值，则默认值表达式的返回值类型，作为字段类型。如果即指定了字段类型，也指定了默认值表达式，那么对开默认值表达式的结果，相当于会有一个类型转换。

3.1.2. 物化列

指定 MATERIALIZED 表达式，即将一个列作为物化列处理了，这意味着这个列的值不能从 insert 语句获取，只能是自己计算出来的。同时，物化列也不会出现在 select *

create table t (a MATERIALIZED (b+1), b UInt16) ENGINE = Memory;
insert into t(b) values (1);
select * from t;
select a, b from t;

3.1.3. 表达式列

ALIAS 表达式列某方面跟物化列相同，就是它的值不能从 insert 语句获取。不同的是，物化列是会真正保存数据（这样查询时不需要再计算），而表达式列

create table t (a ALIAS (b+1), b UInt16) ENGINE = Memory;
insert into t(b) values (1);
select * from t;
select a, b from t;

3.2. SELECT

这个 ClickHouse 中独特的地方涉及其它的机制，所以 SELECT

4. 引擎

引擎就是在创建表时，最后的那个 ENGINE 选项指定的东西，这部分我觉得算是 ClickHouse 最精华的部分了，它很多针对数据仓库场景的设计与优化，是基于特定的引擎实现的，特别是 MergeTree

4.1. TinyLog

最简单的一种引擎，每一列保存为一个文件，里面的内容是压缩过的，不支持索引。

这种引擎没有并发控制，所以，当你需要在读，又在写时，读会出错。并发写，内容都会坏掉。

所以，它的应用场景，基本上就是那种只写一次，然后就是只读的场景。同时，它也不适用于处理量大的数据，官方推荐，使用这种引擎的表最多 100 万行的数据。

因为这种引擎的实现非常简单，所以当你有很多很多的小表数据要处理时，使用它是比较合适的，最基本的，它在磁盘上的文件量很少，读一列数据只需要打开一个文件就好了。

在 Yandex.Metrica

create table t (a UInt16, b String) ENGINE = TinyLog;
insert into t (a, b) values (1, 'abc');

上面创建一张表 t之后，我们在保存数据的目录（默认在 /var/lib/clickhouse/data/default/t）可以看到这样的目录结构：

├── a.bin ├── b.bin └── sizes.json

a.bin 和 b.bin 是压缩过的对应的列的数据， sizes.json 中记录了每个 *.bin

{"yandex":{"a%2Ebin":{"size":"28"},"b%2Ebin":{"size":"30"}}}

4.2. Log

这种引擎跟 TinyLog 基本一致，它的改进点，是加了一个 __marks.mrk

但是，它是不能支持并发写的，一个写操作会阻塞其它读写操作。

Log 不支持索引，同时因为有一个 __marks.mrk

同 TinyLog

4.3. Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。可以并行读，读写互斥锁的时间也非常短。

不支持索引，简单查询下有非常非常高的性能表现。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。

系统运行时也会在外部数据条件， GLOBAL IN

（TODO，数据空间占用与内存占用的大概量）

4.4. Merge

一个工具引擎，本身不保存数据，只用于把指定库中的指定多个表链在一起。这样，读取操作可以并发执行，同时也可以利用原表的索引，但是，此引擎不支持写操作。

指定引擎的同时，需要指定要链接的库及表，库名可以使用一个表达式，表名可以使用正则表达式指定。

create t1 (id UInt16, name String) ENGINE=TinyLog;
create t2 (id UInt16, name String) ENGINE=TinyLog;
create t3 (id UInt16, name String) ENGINE=TinyLog;

insert into t1(id, name) values (1, 'first');
insert into t2(id, name) values (2, 'xxxx');
insert into t3(id, name) values (12, 'i am in t3');

create table t (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t');

上面先建了 t1 ， t2 ， t3 ，三个表，然后用 Merge 引擎的 t

这样，查询的时候，就能同时取到三个表的数据了：

echo 'select _table,* from t order by id desc'|POST 'http://172.17.0.2:8123'

select 中， _table 这个列，是因为使用了 Merge 多出来的一个的一个虚拟列，它表示原始数据的来源表，它不会出现在 show table 的结果当中，同时， select *

4.5. Distributed

前面说的 Merge 可以看成是单机版的 Distributed ，而真正的 Distributed

与 Merge 类似， Distributed

Distributed(remote_group, database, table [, sharding_key])

其中：

remote_group 是配置文件（默认在

/etc/clickhouse-server/config.xml

）中的

remote_servers

database
table
sharding_key 是一个寻址表达式，可以是一个列名，也可以是像

rand()

之类的函数调用，它与

remote_servers

中的

weight

共同作用，决定在写时往哪个 shard

下面的重点，就是配置文件中的 remote_servers

<remote_servers>
    <log>
        <shard>
            <weight>1</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>172.17.0.3</host>
                <port>9000</port>
            </replica>
        </shard>

        <shard>
            <weight>2</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>172.17.0.4</host>
                <port>9000</port>
            </replica>
        </shard>
    </log>
</remote_servers>

log

是某个 shard 组的名字，就是上面的 remote_group

shardweight

是权重，前面说的 sharding_key 与这个有关。简单来说，上面的配置，理论上来看，第一个 shard “被选中”的概率是

1 / 1 + 2

，第二个是

2 / 1 + 2

，这很容易理解。但是， sharding_key 的工作情况，是按实际数字的“命中区间”算的，即第一个的区间是

[0, 1)

的周期，第二个区间是

[1, 1+2)

的周期。比如把 sharding_key 设置成

id

，当

id=0

id=3

时，一定是写入到第一个 shard 中，如果把 sharding_key 设置成

rand()

，那系统会对应地自己作一般化转换吧，这种时候就是一种概率场景了。

internal_replication

是定义针对多个 replica 时的写入行为的。如果为

false

，则会往所有的 replica 中写入数据，但是并不保证数据写入的一致性，所以这种情况时间一长，各 replica 的数据很可能出现差异。如果为

true

，则只会往第一个可写的 replica

replica

就是定义各个冗余副本的，选项有

host

port

user

password

看一个实际的例子，我们先在 B 和 C 两台机器上创建好“物理表”：

create table t(id UInt16, name String) ENGINE=TinyLog;

然后两台机器可以随便 insert

insert into t(id, name) values (0, 'hahaha');
insert into t(id, name) values (2, 'xxxx');

之后，再在 A 机器上，在 config.xml 中配置好 remote_servers

create table t(id UInt16, name String) ENGINE=Distributed(log, default, t, id);

然后，针对这个逻辑表，就可以直接随便 insert

insert into t(id, name) values (0, 'main');
insert into t(id, name) values (1, 'main');
insert into t(id, name) values (2, 'main');

最后，针对逻辑表的查询，其实是没什么特殊之处了， ClickHouse

select name,sum(id),count(id) from t group by name;

clickhouse 利用docker安装流程 clickhouse怎么用_默认值

注意，逻辑表中的写入操作是异步的，会先缓存在本机的文件系统上，并且，对于物理表的不可访问状态，并没有严格控制，所以写入失败丢数据的情况是可能发生的。

4.6. Null

空引擎，写入的任何数据都会被忽略，读取的结果一定是空。

但是注意，虽然数据本身不会被存储，但是结构上的和数据格式上的约束还是跟普通表一样是存在的，同时，你也可以在这个引擎上创建视图。

4.7. Buffer

Buffer 引擎，像是 Memory

同时，说它像 Memory 的另一个原因，是它也跟 Memory

Buffer

使用时，先把源表建好：

create table t (gmt  Date, id UInt16, name String, point UInt16) ENGINE=MergeTree(gmt, (id, name), 10);

然后建 Buffer

create table t_buffer as t ENGINE=Buffer(default, t, 16, 3, 20, 2, 10, 1, 10000)

看起来参数有点多：

Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

database

tablenum_layers

是类似“分区”的概念，每个分区的后面的

min / max

是独立计算的，官方推荐的值是

16min / max

阈值的规则，是“所有的 min 条件都满足，或

如果按上面我们的建表来说，所有的 min 条件就是：过了 3秒，2条数据，1 Byte。一个 max 条件是：20秒，或 10 条数据，或有 10K 。

insert into t (gmt, id, name, point) values ('2017-07-10', 1, 'a', 20)
insert into t_buffer (gmt, id, name, point) values ('2017-07-10', 1, 'b', 10)
select * from t
select '------'
select * from t_buffer

上面的查询，可以在 t 中查出 1 条数据，从 t_buffer 中查出 2 条数据（它会自己从 t过了 20 秒后（至少一个 max 条件），就可以从 t

关于 Buffer

如果一次写入的数据太大或太多，超过了 max 条件，则会直接写入源表。
删源表或改源表的时候，建议 Buffer
“友好重启”时， Buffer 数据会先落到源表，“暴力重启”， Buffer
即使使用了 Buffer ，多次的小数据写入，对比一次大数据写入，也慢得多

4.8. Set

Set 这个引擎有点特殊，因为它只用在 IN 操作符右侧，你不能对它 select

create table scope(id UInt16, name String) ENGINE=Set;

创建了 scope 表之后，是不能： select * from scope

insert into scope(id, name) values (1, 'hello');

在 IN 右侧使用 scope

select 1 where (1, 'hello') in scope;

也会报错，原因是 1 这个量，这里会自动处理成 UInt8 ，与 scope 的列定义的 UInt16

select 1 where (toUInt16(1), 'hello') in scope;

Set

4.9. Join

跟 Set 类似，用在 JOIN

TODO

4.10. MergeTree

这个引擎是 ClickHouse

而且，以这个引擎为基础，后面几种引擎都是在其基础之上附加某种特定功能而实现的“变种”。

使用这个引擎的形式如下：

MergeTree(EventDate, (CounterID, EventDate), 8192)
MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

EventDateintHash32(UserID)(CounterID, EventDate)8123

细节稍后再看，先建表看看文件系统那层大概是个什么样：

create table t (gmt  Date, id UInt16, name String, point UInt16) ENGINE=MergeTree(gmt, (id, name), 10);

先不管采样机制，创建一个四列的表。然后插入几条数据：

insert into t(gmt, id, name, point) values ('2017-04-01', 1, 'zys', 10);
insert into t(gmt, id, name, point) values ('2017-06-01', 4, 'abc', 10);
insert into t(gmt, id, name, point) values ('2017-04-03', 5, 'zys', 11);

这里注意一下，日期的格式，好像必须是 yyyy-mm-dd在插入了三条数据之后，在 /var/lib/clickhouse/data/default/t

├── 20170401_20170401_2_2_0
│   ├── checksums.txt
│   ├── columns.txt
│   ├── gmt.bin
│   ├── gmt.mrk
│   ├── id.bin
│   ├── id.mrk
│   ├── name.bin
│   ├── name.mrk
│   ├── point.bin
│   ├── point.mrk
│   └── primary.idx
├── 20170403_20170403_6_6_0
│   └── ...
├── 20170601_20170601_4_4_0
│   └── ...
└── detached

从上面看的话：

最外层的目录，是根据日期列的范围，作了切分的。目前看来，三条数据，并没有使系统执行 merge
最外层的目录，除了开头像是日期范围，后面的数字，可能与主键有关。
最外层还有一个

detached

primary.idx

目录内其它的文件，看起来跟 Log 引擎的差不多，就是按列保存，额外的

mrk

简单三条数据，有很多东西还是看不出来的。

（在等了不知道多少时间后，或者手动使用 optimize table t 触发 merge 行为，三个目录会被合成两个目录，变成 20170401_20170403_2_6_1 和 20170601_20170601_4_4_0

4.11. ReplacingMergeTree

这个引擎是在 MergeTree

相比 MergeTree ， ReplacingMergeTree 在最后加一个“版本列”，它跟时间列配合一起，用以区分哪条数据是“新的”，并把旧的丢掉（这个过程是在 merge 时处理，不是数据写入时就处理了的，平时重复的数据还是保存着的，并且查也是跟平常一样会查出来的，所以在 SQL 上排序过滤 Limit

create table t (gmt  Date, id UInt16, name String, point UInt16) ENGINE=ReplacingMergeTree(gmt, (name), 10, point);

像上面一样，“版本列”允许的类型是， UInt 一族的整数，或 Date 或 DateTime

insert into t (gmt, id, name, point) values ('2017-07-10', 1, 'a', 20);
insert into t (gmt, id, name, point) values ('2017-07-10', 1, 'a', 30);
insert into t (gmt, id, name, point) values ('2017-07-11', 1, 'a', 20);
insert into t (gmt, id, name, point) values ('2017-07-11', 1, 'a', 30);
insert into t (gmt, id, name, point) values ('2017-07-11', 1, 'a', 10);

插入这些数据，用 optimize table t 手动触发一下 merge

select * from t

结果就只有一条：

┌────────gmt─┬─id─┬─name─┬─point─┐
│ 2017-07-11 │  1 │ a    │    30 │
└────────────┴────┴──────┴───────┘

4.12. SummingMergeTree

ReplacingMergeTree 是替换数据， SummingMergeTree 就是在 merge

建表：

create table t (gmt Date, name String, a UInt16, b UInt16) ENGINE=SummingMergeTree(gmt, (gmt, name), 8192, (a))

插入数据：

insert into t (gmt, name, a, b) values ('2017-07-10', 'a', 1, 2);
insert into t (gmt, name, a, b) values ('2017-07-10', 'b', 2, 1);
insert into t (gmt, name, a, b) values ('2017-07-11', 'b', 3, 8);
insert into t (gmt, name, a, b) values ('2017-07-11', 'b', 3, 8);
insert into t (gmt, name, a, b) values ('2017-07-11', 'a', 3, 1);
insert into t (gmt, name, a, b) values ('2017-07-12', 'c', 1, 3);

OPTIMIZE TABLE

┌────────gmt─┬─name─┬─a─┬─b─┐
│ 2017-07-10 │ a    │ 1 │ 2 │
│ 2017-07-10 │ b    │ 2 │ 1 │
│ 2017-07-11 │ a    │ 3 │ 1 │
│ 2017-07-11 │ b    │ 6 │ 8 │
│ 2017-07-12 │ c    │ 1 │ 3 │
└────────────┴──────┴───┴───┘

11, b 的 a 列，相加了， b 列取了一个 8这个引擎要注意的一个地方是，可加列不能是主键中的列，并且如果某行数据可加列都是 null

4.13. AggregatingMergeTree

AggregatingMergeTree 是在 MergeTree 基础之上，针对聚合函数结果，作增量计算优化的一个设计，（ clickhouse 中说的是状态，我个人猜，应该就是为增量计算保存的一些中间数据）。它会在 merge

在讲具体的用法之前，要先讲明白两个问题，一是聚合数据的预计算，二是进一步，聚合数据的增量计算的情况。

聚合数据的预计算，实现上，算是一种“空间换时间”的权衡，并且是以减少维度为代价的。

假设原始有三个维度，一个需要 count

	D1	D2	D3	M1
甲	a	1	1
甲	a	1	1
甲	b	2	1
乙	b	3	1
丙	b	2	1
丙	c	1	1
丁	c	2	1
丁	a	1	1

我们可以通过减少一个维度的方式，来以 count 函数聚合一次 M ，减少维度要达到目的，结果的行数应该要减少的。以上面数据来说，如果我们把 D1 去掉，按 D2 和 D3

D2	D3	count(M1)
a	1	3
b	2	2
b	3	1
c	1	1
c	2	1

count(M1)

通过这一步，我们从原来的三个维度，减少到两个维度，数据从 8 行减少到 5 行。当然，剩下的两个维度，在实际使用中，还是可以自由控制的了。

在实际中，如果是记录网站访问之类的数据，原始数据中一般都有一个“用户ID”的维度，但是在输出数据时，是不会精确到人的，那么这就是一个可以去掉的维度，至于去掉这个维度之后，数据行数能减少多少，实际上跟减少的那个维度的数据是完全没有关系的，只跟剩下的维度有关，剩下的维度值越集中，数据行数就越少。不过，去掉“用户ID”之后，好像我们就没有办法计算像 UV 这样的数据了。

接着说去掉一个维度后的情况，前面说当去掉“用户ID”之后，剩下的数据其实我们是没有办法再计算 UV 这样的指标了的，那么在之前，我们可以就把相关的指标算好。比如我们在前面的数据上加一个 UV 指标：

	D2	D3	count(M1)	UV by D2 and D3
a	1	3	2
b	2	2	2
b	3	1	1
c	1	1	1
c	2	1	1

现在又有新的问题了，现在虽然还剩下 2 个维度，但是像 UV 这类数据即不可加，也不是复合指标，对于这个指标而言，维度不能再改变了。（从上方的数据中，你无法再按 D2 这一个维度聚合 UV，因为 D1 已经没了），如果只看 D2

	D2	UV by D2
a	2
b	3
c	2

AggregatingMergeTree

clickhouse 中，对于聚合函数的实现，实现上是有三套的，除了普通的 sum, uniq 这些，应用于 AggregatingMergeTree 上的，还有 sumState , uniqState ，及 sumMerge ， uniqMerge 这两组，而一个 AggregatingMergeTree 的表，里面的聚合函数，只能使用 sumState 这一组，对应于，查询时，只能使用 sumMerge 这一组。（ sumState另外，对于 AggregatingMergeTree 引擎的表，不能使用普通的 INSERT 去添加数据，那怎么办？一方面可以用 INSERT SELECT我们还是按上面的例子，先创建一个 t

create table t(gmt Date, D1 String, D2 String, D3 String, M1 UInt16) ENGINE=MergeTree(gmt, (gmt, D1, D2, D3), 8192)

原始数据放进去：

insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '甲', 'a', '1', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '甲', 'a', '1', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '甲', 'b', '2', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '乙', 'b', '3', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '丙', 'b', '2', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '丙', 'c', '1', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '丁', 'c', '2', 1);
insert into t (gmt, D1, D2, D3, M1) values ('2017-07-10', '丁', 'a', '1', 1);

按 D2 和 D3 聚合 count(M1)

select D2, D3, count(M1) from t group by D2, D3;

只按 D2

select D2, uniq(D1) from t group by D2;

这些都没有什么特殊的地方。

接下来，我们创建一个物化视图，使用 AggregatingMergeTree ，把 D1 去掉（把前面的 t

create materialized view t_view
ENGINE = AggregatingMergeTree(gmt, (D2, D3), 8192)
as
select D2, D3, uniqState(D1) as uv
from t group by D2, D3;

在重新填充数据后，直接查 t_view

select * from t_view

可以看到这样的输出：

┌────────gmt─┬─D2─┬─D3─┬─uv──────┐
│ 2017-07-10 │ c  │ 2  │ \0????  │
└────────────┴────┴────┴─────────┘
┌────────gmt─┬─D2─┬─D3─┬─uv─────────┐
│ 2017-07-10 │ a  │ 1  │ \0???      │
│ 2017-07-10 │ b  │ 2  │ \0???????  │
│ 2017-07-10 │ b  │ 3  │ \0????     │
└────────────┴────┴────┴────────────┘
┌────────gmt─┬─D2─┬─D3─┬─uv──────┐
│ 2017-07-10 │ a  │ 1  │ \0????  │
└────────────┴────┴────┴─────────┘
┌────────gmt─┬─D2─┬─D3─┬─uv──────┐
│ 2017-07-10 │ c  │ 1  │ \?????  │
└────────────┴────┴────┴─────────┘

OPTIMIZE TABLE t_view

┌────────gmt─┬─D2─┬─D3─┬─uv─────────┐
│ 2017-07-10 │ a  │ 1  │ \0???????  │
│ 2017-07-10 │ b  │ 2  │ \0???????  │
│ 2017-07-10 │ b  │ 3  │ \0???????  │
│ 2017-07-10 │ c  │ 1  │ \0???????  │
│ 2017-07-10 │ c  │ 2  │ \0???????  │
└────────────┴────┴────┴────────────┘

我们要查 D2 的 uv

select D2, uniqMerge(uv) from t_view group by D2 order by D2;

输出：

┌─D2─┬─uniqMerge(uv)─┐
│ a  │             2 │
│ b  │             3 │
│ c  │             2 │
└────┴───────────────┘

酷吧。 t_view 中的 uv 列保存的是源表中 D1 列的聚合状态，对于 uniq 的实现，简单地，状态中可以记录已经找到的 row_id ，已经有的参数值的集合，这里参数是 D1，还有当前结果值，这样，下次查的时候，就可以从 row_id

说得更细一点，原始数据：

	row_id	D1	D2	D3	M1
0	甲	a	1	1
1	甲	a	1	1
2	甲	b	2	1
3	乙	b	3	1
4	丙	b	2	1
5	丙	c	1	1
6	丁	c	2	1
7	丁	a	1	1

t_view

gmt	D2	D3	uv
2017-07-10	a	1	row_id:7 value:2 set:{甲,丁}
2017-07-10	b	2	row_id:7 value:2 set:{甲,丙}
2017-07-10	b	3	row_id:7 value:1 set:{乙}
2017-07-10	c	1	row_id:7 value:1 set:{丙}
2017-07-10	c	2	row_id:7 value:1 set:{丁}

这样，源表中后面有新的数据进去，更新 t_view再考虑从 t_view 中只取子维度的情况，比如前面的只取 D2 维度的结果，对于 uniq 来说就更简单了， D2 的值对应的 uv 状态中，集合做并集就可以得到正确结果了。比如取 b 的 uniq ，就是 {甲,丙} 并 {乙}可以看出，这种方式，对于不同的聚合函数处理上是会有不同，但是即使是对 uv 这类算是最麻烦的聚合计算， uniqState

4.14. CollapsingMergeTree

这个引擎，是专门为 OLAP 场景下，一种“变通”存数做法而设计的，要搞明白它，以及什么场景下用它，为什么用它，需要先行了解一些背景。

首先，在 clickhouse 中，数据是不能改，更不能删的，其实在好多数仓的基础设施中都是这样。前面为了数据的“删除”，还专门有一个 ReplacingMergeTree

试想，假设每隔 10 秒时间，你都能获取到一个当前在线人数的数据，把这些数据一条一条存下，大概就是这样：

时间点	在线人数
10	123
20	101
30	98
40	88
50	180

现在问你，“当前有多少人在线？”，这么简单的问题，怎么回答？

在这种存数机制下，“当前在线人数”显然是不能把在线人数这一列聚合起来取数的嘛。也许，能想到的是，“取最大的时间”的那一行，即先 order by 再 limit 1

时间点	频道	在线人数
10	a	123
10	b	29
10	c	290
20	a	101
20	b	181
20	c	31
30	a	98
30	b	18
30	c	56
40	a	88
40	b	9
40	c	145

这时，如果想看每个频道的当前在线人数，查询就不像之前那么好写了，硬上的话，你可能需要套子查询。好了，我们目的不是讨论 SQL 语句怎么写。

回到开始的数据：

时间点	在线人数
10	123
20	101
30	98
40	88
50	180

如果我们的数据，是在关心一个最终的状态，或者说最新的状态的话，考虑在业务型数据库中的作法，我们会不断地更新确定的一条数据， OLAP 环境我们不能改数据，但是，我们可以通过“运算”的方式，去抹掉旧数据的影响，把旧数据“减”去即可，比如：

	符号	时间点	在线人数
+	10	123
-	10	123
+	20	101

当我们在添加 20 时间点的数据前，首先把之前一条数据“减”去，以这种“以加代删”的增量方式，达到保存最新状态的目的。

当然，起初的数据存储，我们可以以 +1 和 -1

	sign	gmt	name	point
+1	10	a	123
+1	10	b	29
+1	10	c	290
-1	10	a	123
+1	20	a	101
-1	10	b	29
+1	20	b	181
-1	10	c	290
+1	20	c	31

如果想看每个频道的当前在线人数：

select name, sum(point * sign) from t group by name;

就可以得到正确结果了：

┌─name─┬─sum(multiply(point, sign))─┐
│ b    │                        181 │
│ c    │                         31 │
│ a    │                        101 │
└──────┴────────────────────────────┘

神奇。考虑数据可能有错误的情况（-1 和 +1 不匹配），我们可以添加一个 having

insert into t (sign, gmt, name, point) values (-1, '2017-07-11', 'd', 10),

再按原来的 SQL 查，结果是：

┌─name─┬─sum(multiply(point, sign))─┐
│ b    │                        181 │
│ c    │                         31 │
│ d    │                        -10 │
│ a    │                        101 │
└──────┴────────────────────────────┘

加一个 having

select name, sum(point * sign) from t group by name having sum(sign) > 0;

就可以得到正确的数据了：

┌─name─┬─sum(multiply(point, sign))─┐
│ b    │                        181 │
│ c    │                         31 │
│ a    │                        101 │
└──────┴────────────────────────────┘

这种增量方式更大的好处，是它与指标本身的性质无关的，不管是否是可加指标，或者是像 UV 这种的去重指标，都可以处理。

相较于其它一些变通的处理方式，比如对于可加指标，我们可以通过“差值”存储，来使最后的 sum聚合正确工作，但是对于不可加指标就无能为力了。

上面的东西如果都明白了，我们也就很容易理解 CollapsingMergeTree

“以加代删”的增量存储方式，带来了聚合计算方便的好处，代价却是存储空间的翻倍，并且，对于只关心最新状态的场景，中间数据都是无用的。 CollapsingMergeTree 引擎的作用，就是针对主键，来帮你维护这些数据，它会在 merge

前面的数据，如果我们存在 MergeTree 引擎的表中，那么通过 select * from t

┌─sign─┬────────gmt─┬─name─┬─point─┐
│    1 │ 2017-07-10 │ a    │   123 │
│   -1 │ 2017-07-10 │ a    │   123 │
│    1 │ 2017-07-10 │ b    │    29 │
│   -1 │ 2017-07-10 │ b    │    29 │
│    1 │ 2017-07-10 │ c    │   290 │
│   -1 │ 2017-07-10 │ c    │   290 │
│    1 │ 2017-07-11 │ a    │   101 │
│    1 │ 2017-07-11 │ b    │   181 │
│    1 │ 2017-07-11 │ c    │    31 │
│   -1 │ 2017-07-11 │ d    │    10 │
└──────┴────────────┴──────┴───────┘

如果换作 CollapsingMergeTree

┌─sign─┬────────gmt─┬─name─┬─point─┐
│    1 │ 2017-07-11 │ a    │   101 │
│    1 │ 2017-07-11 │ b    │   181 │
│    1 │ 2017-07-11 │ c    │    31 │
│   -1 │ 2017-07-11 │ d    │    10 │
└──────┴────────────┴──────┴───────┘

CollapsingMergeTree 在创建时与 MergeTree 基本一样，除了最后多了一个参数，需要指定 Sign 位（必须是 Int8

create table t(sign Int8, gmt Date, name String, point UInt16) ENGINE=CollapsingMergeTree(gmt, (gmt, name), 8192, sign);

讲明白了 CollapsingMergeTree 可能有人会问，如果只是要“最新状态”，用 ReplacingMergeTree

这里，即使不论对“日期维度”的特殊处理（ ReplacingMergeTree 不会对日期维度做特殊处理，但是 CollapsingMergeTree 看起来是最会保留最新的），更重要的，是要搞明白，我们面对的数据的形态，不一定是 merge 操作后的“完美”形态，也可能是没有 merge 的中间形态，所以，即使你知道最后的结果对于每个主键只有一条数据，那也只是 merge 操作后的结果，你查数据时，聚合函数还是得用的，当你查询那一刻，可能还有很多数据没有做 merge

明白了一点，不难了解，对于 ReplacingMergeTree 来说，在这个场景下跟 MergeTree 其实没有太多区别的，如果不要 sign ，那么结果就是日期维度在那里，你仍然不能以通用方式聚合到最新状态数据。如果要 sign ，当它是主键的一部分时，结果就跟 MergeTree 一样了，多存很多数据。而当它不是主键的一部分，那旧的 sign 会丢失，就跟没有 sign 的 MergeTree 一样，不能以通用方式聚合到最新状态数据。结论就是， ReplacingMergeTree 的应用场景本来就跟 CollapsingMergeTreeReplacingMergeTree 的应用，大概都是一些 order by limit 1 这种。而 CollapsingMergeTree 则真的是 group by

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：es2015学习网址推荐

下一篇：python 扣子API

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯