ClickHouse REST API(HTTP接口)及Engine引擎的使用

ClickHouse REST API(HTTP接口)及Engine引擎的使用

1.访问接口

ClickHouse 自己的 clickhouse-client 使用的是“原生”的 TCP 连接来完成与服务

端的交互，而在应用中用它的话，它有实现一个 HTTP 的访问接口，把 SQL 语句通过

HTTP 发送到服务端，就可以得到响应数据了（其实不用担心效率问题，数仓场景下，这

种传输成本相较于大数据量下的聚合计算挑战，直接就忽略吧）。

默认配置下， HTTP 的服务是在 8123 端口上的，直接访问的话，可以得到一个 ok 的

响应。（如果要外部访问，记得把配置中的 listen_host 加一个 0.0.0.0 ）。

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

echo 'CREATE TABLE t (a UInt8) ENGINE = Memory' | POST

'1xx.1x.0.x:8123/'

echo 'insert into t (a) values (10)' | POST '1xx.1x.0.x:8123/'

GET "1xx.1x.0.x:8123?query=select * from t"

echo 'drop table t' | POST ':8123/'

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

用户名密码什么的。

database ，数据库

user ， 登录用户

password ， 登录密码

/reference_#Settings 其它配置项

username="default"

password=""

ip=":8123/"

tablename="xxtest"

#create table with crul

#echo 'create table'+ $tablename +'with crul' +$username:$password@$ip/

echo 'CREATE TABLE '$tablename'(dt Date,dp String,id String, name String)

ENGINE = Memory' | curl $username:$password@$ip -d @-

用户名和密码，也可以通过 X-ClickHouse-User 和 X-ClickHouse-Key 这两个头来

设置与传递。

2. 查询语言

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

2.1. CREATE TABLE

建表语句除了基本形式外，还有两个扩展形式。这是基本形式，如果引擎支持索引的

话，索引可以在 ENGINE 的地方额外设置。其中ENGINE字段后面解析

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name

(

name1 [type1] [DEFAULT | MATERIALIZED | ALIAS expr1],

name2 [type2] [DEFAULT | MATERIALIZED | ALIAS expr2],

...

) ENGINE = engine

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

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

[ENGINE = engine]

第二种形式是“建表并填充”，表字段会自动根据 SELECT 的返回内容设置，并且，返

回内容会作为新表内容填充进去。

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

SELECT ...

2.2. 默认值

默认值 的处理方面， ClickHouse 中，默认值总是有的，如果没有显示式指定的话，

会按字段类型处理：NULLs 是不支持的。

数字类型， 0 。

字符串，空字符串。

数组，空数组。

日期， 0000-00-00 。

时间， 0000-00-00 00:00:00 。

同时，在字段类型方面，如果没有明确指定字段类型，但是指定了默认值，则默认值

表达式的返回值类型，作为字段类型。如果即指定了字段类型，也指定了默认值表达式，

那么对开默认值表达式的结果，相当于会有一个类型转换。

2.3. 物化列

指定 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;

2.4. 表达式列

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. 引擎

引擎就是在创建表时，最后的那个 ENGINE 选项指定的东西，这部分我觉得算

是 ClickHouse 最精华的部分了，它很多针对数据仓库场景的设计与优化，是基于特定的

引擎实现的，特别是 MergeTree 这一类引擎。

3.1. TinyLog

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

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

都会坏掉。

所以，它的应用场景，基本上就是那种只写一次，然后就是只读的场景。同时，它也

不适用于处理量大的数据，官方推荐，使用这种引擎的表最多 100 万行的数据。

因为这种引擎的实现非常简单，所以当你有很多很多的小表数据要处理时，使用它是

比较合适的，最基本的，它在磁盘上的文件量很少，读一列数据只需要打开一个文件就好

了。

在 a 产品中，这种引擎用于小批量的中间数据处理上。

create table t (a UInt16, b String) ENGINE = TinyLog;

insert into t (a, b) values (1, 'abc');

上面创建一张表 t ，它有 2 个字段，然后插入了一条数据。

之后，我们在保存数据的目录（默认在 /var/lib/clickhouse/data/default/t）可以看

到这样的目录结构：

├──

├──

└──

和 是压缩过的对应的列的数据， 中记录了每个 *.bin 文件的大

小：

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

3.2. Log

这种引擎跟 TinyLog 基本一致，它的改进点，是加了一个 __ 文件，里面

记录了每个数据块的偏移，这种做的一个用处，就是可以准确地切分读的范围，从而使用

并发读取成为可能。

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

Log 不支持索引，同时因为有一个 __ 的冗余数据，所以在写入数据时，一

旦出现问题，这个表就废了。

同 TinyLog 差不多，它适用的场景也是那种写一次之后，后面就是只读的场景，临时

数据用它保存也可以。

3.3. Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。

可以并行读，读写互斥锁的时间也非常短。

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

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不

太大（上限大概 1 亿行）的场景。

系统运行时也会在 外部数据条件 ， GLOBAL IN 等机制中用到它。

3.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 ':8123'

select 中， _table 这个列，是因为使用了 Merge 多出来的一个的一个 虚拟列 ，它

表示原始数据的来源表，它不会出现在 show table 的结果当中，同时， select * 不会包

含它。

3.5. Distributed

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

务器能力，当然，机器地址的配置依赖配置文件中的信息。

与 Merge 类似， Distributed 也是通过一个逻辑表，去访问各个物理表，设置引擎时

的样子是：

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

其中各个参数说明如下：

remote

remote_group 是配置文件（默认在 /etc/clickhouse-server/ ）中

的 remote_servers 一节的配置信息。

database 是各服务器中的库名。

table 是表名。

sharding_key 是一个 寻址表达式 ，可以是一个列名，也可以是像 rand() 之类的函数

调用，它与 remote_servers 中的 weight 共同作用，决定在 写 时往哪个 shard 写。

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

1

false

172.17.0.3

9000

2

false

172.17.0.4

9000

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

shard 是固定标签。

weight 是权重，前面说的 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 机器上，在 中配置好 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;

注意，逻辑表中的写入操作是异步的，会先缓存在本机的文件系统上，并且，对于物

理表的不可访问状态，并没有严格控制，所以写入失败丢数据的情况是可能发生的。

3.6. Null

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

但是注意，虽然数据本身不会被存储，但是结构上的和数据格式上的约束还是跟普通

表一样是存在的，同时，你也可以在这个引擎上创建视图。

3.7. Buffer

Buffer 引擎，像是 Memory 存储的一个上层应用似的（磁盘上也是没有相应目录的）。

它的行为是一个缓冲区，写入的数据先被放在缓冲区，达到一个阈值后，这些数据会自动

被写到指定的另一个表中。

同时，说它像 Memory 的另一个原因，是它也跟 Memory 一样，有很多的限制，比

如没有索引什么的。

Buffer 是接在其它表前面的一层，对它的读操作，也会自动应用到后面表，但是因为

前面说到的限制的原因，一般我们读数据，就直接从源表读就好了，缓冲区的这点数据延

迟，只要配置得当，影响不大的。

Buffer 后面也可以不接任何表，这样的话，当数据达到阈值，就会被丢弃掉。

使用时，先把源表，再建buffer表建好：

create table t (gmt Date, id UInt16, name String, point UInt16)

ENGINE=MergeTree(gmt, (id, name), 10);

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 和 table 不用多说了，就是指源表，这里除了字符串常量，也可以使用变量

的。

num_layers 是类似“分区”的概念，每个分区的后面的 min / max 是独立计算的，

官方推荐的值是 16 。

min / max 这组配置荐，就是设置阈值的，分别是 时间（秒），行数，空间（字节）。

阈值的规则，是“所有的 min 条件都满足， 或 至少一个 max 条件满足”。如果按

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

条件是：20秒，或 10 条数据，或有 10K 。

关于 Buffer 的其它一些点：

如果一次写入的数据太大或太多，超过了 max 条件，则会直接写入源表。

删源表或改源表的时候，建议 Buffer 表删了重建。

“友好重启”时， Buffer 数据会先落到源表，“暴力重启”， Buffer 表中的数据会丢

失。

即使使用了 Buffer ，多次的小数据写入，对比一次大数据写入，也 慢得多 （几千行

与百万行的差距）

3.8. Set

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

建了一个set集合辅助查找。

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

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

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

1 这个量，这里会自动处理成 UInt8 ，与 scope 的列定义的 UInt16 不匹配。这时，

只能手动显式做一个类型转换了。

Set 引擎表，是全内存运行的，但是相关数据会落到磁盘上保存，启动时会加载到内

存中。所以，意外中断或暴力重启，是可能产生数据丢失问题的。

3.9. Join

跟 Set 类似，用在 JOIN 的右边。

3.10. MergeTree

这个引擎是 ClickHouse 的重头戏，它支持一个日期和一组主键的两层式索引，还可

以实时更新数据。同时，索引的粒度可以自定义，外加直接支持采样功能。

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

的“变种”。使用这个引擎的形式如下：

MergeTree(EventDate, (CounterID, EventDate), 8192)

MergeTree(EventDate,

intHash32(UserID)), 8192)

intHash32(UserID), (CounterID, EventDate,

EventDate 一个日期的列名。

intHash32(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-04-03', 5, 'zys', 11)

insert into t(gmt, id, name, point) values ('2017-06-01', 4, 'abc', 10);

在插入了三条数据之后，在 /var/lib/clickhouse/data/default/t 下可以看到这样的结

构：

├── 20170401_20170401_2_2_0

│ ├──

│ ├──

│ ├──

│ ├──

│ ├──

│ ├──

│ ├──

│ ├──

│ ├──

│ ├──

│ └──

├── 20170403_20170403_6_6_0

│ └── ...

├── 20170601_20170601_4_4_0

│ └── ...

└── detached

最外层的目录，是根据日期列的范围，作了切分的。目前看来，三条数据，并没有使

系统执行 merge 操作（还是有三个目录），后面使用更多的数据看看表现。

最外层的目录，除了开头像是日期范围，后面的数字，可能与主键有关。

最外层还有一个 detached ，不知道干什么的。

目录内， 应该就是主键组索引了。

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

保存一下块偏移量。

简单三条数据，有很多东西还是看不出来的。（在等了不知道多少时间后，或者手动使

用 optimize table t 触发 merge 行为，三个目录会被合成两个目录，变

成 20170401_20170403_2_6_1 和 20170601_20170601_4_4_0 了）

3.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);

ReplacingMergeTree(EventDate, (OrderID, EventDate, BannerID, ...), 8192, ver)

像上面一样，“版本列”允许的类型是， 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 │

└────────────┴────┴──────┴───────┘

3.12. SummingMergeTree

ReplacingMergeTree 是替换数据， SummingMergeTree 就是在 merge 阶段把数

据加起来了，当然，哪些列要加（一般是针对可加的指标）可以配置，不可加的列，会取

一个最先出现的值。建表如下：

create table t (gmt Date, name String, a UInt16, b UInt16)

ENGINE=SummingMergeTree(gmt, (gmt, name), 8192, (a))

SummingMergeTree(EventDate, (OrderID, EventDate, BannerID, ...), 8192,

(Shows, Clicks, Cost, ...))

插入数据后，优化，然后查询：

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);

可以发现 2017-07-11 b 6 8这一行，a的值被累加了，b的值取最先出现的值。

┌────────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 │

└────────────┴──────┴───┴───┘

这个引擎要注意的一个地方是，可加列不能是主键中的列，并且如果某行数据可加列

都是 null ，则这行会被删除。

3.13. AggregatingMergeTree

3.14. CollapsingMergeTree