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

女爷i 2022-05-24 13:46 496阅读 0赞

# 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 'http://1xx.1x.0.x:8123/'
    echo 'insert into t (a) values (10)' | POST 'http://1xx.1x.0.x:8123/'
    GET "http://1xx.1x.0.x:8123?query=select * from t"
    echo 'drop table t' | POST 'http://1xx.1x.xx.xx:8123/'

访问地址中，可以通过请求参数，或者头，来指定一些环境配置项，比如 `database` ，用户名密码什么的。

*  database ，数据库
 *  user ， 登录用户
 *  password ， 登录密码
 *  https://clickhouse.yandex/reference\_en.html\#Settings 其它配置项

username="default"
    password=""
    ip="172.16.xxx.xx: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 万行的数据。

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

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

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）可以看到这样的目录结构：

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

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

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

## 3.2. Log ##

这种引擎跟 TinyLog 基本一致，它的改进点，是加了一个 `__marks.mrk` 文件，里面记录了每个数据块的偏移，这种做的一个用处，就是可以准确地切分读的范围，从而使用**并发读取**成为可能。

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

Log 不支持索引，同时因为有一个 `__marks.mrk` 的冗余数据，所以在写入数据时，一旦出现问题，这个表就废了。

同 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 'http://1xx.xx.0.xx: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/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 的值。
 *  `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 机器上，在 `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;

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

## 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), (CounterID, EventDate, intHash32(UserID)), 8192)

*  `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
    │   ├── 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` 了）

## 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 ##