Clickhouse_Table_Engine_总结

  bigdata
  32 minutes read (About 4769 words)

Clickhouse_Table_Engine_总结

Clickhouse表引擎决定了：

  • 数据如何存储，如何读取
  • 支持何种查询
  • 并发数据访问能力
  • 索引的使用
  • 是否支持多线程请求执行
  • 数据如何同步

当读取数据时, 引擎只需要抽取必要的列簇. 然而，在一些场景下,引擎可以半处理数据

对于大多数场合下，应该使用 MergeTree家族 引擎

以下包括官方介绍的17种表引擎的介绍：

1. TinyLog引擎

  1. TinyLog 是最简单的表引擎, 它将数据保存到磁盘. 每一列都以单独压缩文件形式保存. 当写入数据时, 数据追加到文件的末尾

  2. 并发数据访问不限制任何形式

    同时读写，读操作将报错
    同时写，数据损坏

  3. 典型使用场景：一次写入，多次读取的应用场景。此引擎适用于相对较小的表（建议最多100W行）。 如果您有许多小表，则使用此表引擎是有意义的，因为它比Log引擎更简单（需要打开的文件更少）。 当您拥有大量小表时，这种情况可能会导致生产效率低下。

  4. TinyLog 引擎 不支持索引

在Yandex.Metrica中，TinyLog表用于数据的小批量处理的中间表

2. Log引擎

  1. 类似TinyLog，不同于TinyLog在于：标记 “marks”的小文件同时保留在列存储文件中. 这些标记写到每个数据块中 ，标记中包含偏移量，可以知道从哪开始读文件，跳过特定的行数. 此机制使得能够支持多线程并行读取

  2. 并发读取，写入时阻塞读取，如果写入失败，表将会被破坏

  3. Log 引擎不支持索引

Log引擎适用于临时数据，一次写入表以及测试或演示目的

3. Memory引擎

  1. 内存表引擎保存在内存中, 数据处于未压缩状态. 数据保存格式与读取数据的格式相同
  2. 并发数据访问是同步的. 无锁访问
  3. 读写数据操作互相不受影响. 但数据索引不支持
  4. 因为无磁盘读写, 压缩/解压缩, 和序列化/反序列化操作，因此单个SQL语句查询可达到 10 GB/秒（高效率，多数情况下，效率能等同于Merge Tree)
  5. 当重启服务器后, 数据会在表中清空
  6. 它可用于小数据量(1亿条左右)的高速读取数据场景或用于测试环境.

内存引擎也可用于外部数据的临时表查询和实现 GLOBAL IN 操作

4. Buffers引擎

将数据写入到内存中，周期性刷新数据到另外的表中。在读取操作的过程中，数据从 Buffer 和另外的表中同时读取。

  1. 周期性刷新数据到下级表，可以设置 时间/条数/size，满足就进行刷新
  2. Buffer表不支持索引，完全扫描大缓存区的数据可能会很慢（下级表使用自己的索引）
  3. 如果Buffer表中的列集与下级表中的列集不匹配，则会插入两个表中存在的列的子集
  4. 如果Buffer表和下级表中的某列在类型上不匹配，则会输出异常，并清除缓存
  5. 如果对应的下级表不存在，同样会输出异常，并清除缓存
  6. 如果需要执行Alert，建议先删除buffer表，则Alert下级表，然后再次创建Buffer表
  7. 如果服务器异常重启，缓冲区中的数据将丢失
  8. PREWHERE，FINAL和SAMPLE对缓冲表不起作用，这些条件直接传递到下级表，不用于Buffer中的数据
  9. 当添加数据到Buffer时，如果一个缓冲区被锁，这时读操作可能会出现延迟
  10. 写入到Buffer中的数据，最终可能会以不同的顺序被刷新不同的块中，因此，Buffer表很难用于正确写入CollapsingMergeTree
  11. 对于目标表是复制表，Buffer 表中的数据会丢失复制表的相关特性，在使用过程中容易出问题

只建议在极少数情况下使用Buffer表

  • 当在一个单位时间内从大量服务器接收到太多INSERT并且在插入之前无法缓冲数据时使用缓冲表，这意味着INSERT不能足够快地运行
  • 请注意，一次插入一行数据是没有意义的，即使对于Buffer表也是如此。这只会产生每秒几千行的速度，而插入更大的数据块每秒可以产生超过一百万行

5. 外部数据

ClickHouse允许向服务器发送处理查询所需的数据以及SELECT查询。 此数据放在临时表中（请参阅“临时表”一节），可以在查询中使用（例如，在IN运算符中）。

如果需要使用大量外部数据运行多个查询，请不要使用此功能，最好提前将数据上传到数据库

可以使用命令行客户端（在非交互模式下）或使用HTTP接口上载外部数据。

6. Merge Tree 引擎

Merge Tree系列引擎，是Clickhouse 最强大的引擎

主要特性：

  1. 按主键排序存储
  2. 支持稀疏索引，便于更快查找数据
  3. 支持分区，在使用分区的某些操作时，Clickhouse有自动机制确保更佳的查询性能
  4. 支持数据复制，见ReplicatedMergeTree家族引擎
  5. 支持数据抽样

数据存储

  1. 表由按主键排序的数据部分组成。
  2. 插入数据时，会创建单独的数据块（part），每个数据部分按主键排序按字典顺序排序；例如，如果主键是（CounterID，Date），则part中的数据先按CounterID排序，再按Date排序。
  3. 属于不同分区的数据被分割在不同的块中（part），为了更高效的存储，Clickhouse会合并不同的part。不同分区中的数据块不会进行合并，合并机制不保证具有相同主键的所有行都在同一数据部分中
  4. 对于每个数据部分，ClickHouse创建一个索引文件，其中包含每个索引行的主键值（“mark”）。索引行号被定义为n * index_granularity。最大值n等于将总行数除以index_granularity的整数部分。对于每列，“标记”也写入与主键相同的索引行。这些“标记”允许您直接在列中查找数据。

MergeTree引擎的适合场景：单个大型表，并且以小块的形式不断向其中添加数据

对于主键和索引在查询中的高效原理

回忆两个clickhouse的特性：

  1. 数据按照主键排序
  2. 稀疏索引

举例：主键(CounterID, Date)，排序后的数据及生成的索引类似于：

1
2
3
4
5
6
Whole data:     [-------------------------------------------------------------------------]
CounterID:      [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date:           [1111111222222233331233211111222222333211111112122222223111112223311122333]
Marks:           |      |      |      |      |      |      |      |      |      |      |
                a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1    i,3    l,3
Marks numbers:   0      1      2      3      4      5      6      7      8      9      10

当数据查询为：

  • CounterID in ('a', 'h'), the server reads the data in the ranges of marks [0, 3) and [6, 8)
  • CounterID IN ('a', 'h') AND Date = 3, the server reads the data in the ranges of marks [1, 3) and [7, 8).

上面的示例表明，使用索引总是比完全扫描更有效。

稀疏索引

  • 稀疏索引允许读取额外的数据，当读取单个主键范围时，可以从每个数据块中额外读取 index_granularity * 2行，在index_granularity=8192时，Clickhouse并不会有性能问题
  • 稀疏索引能够支撑处理非常大的数据表行，因为索引存储在RAM中
  • Clickhouse并不要求唯一主键。支持重复插入相同主键的数据

如何选择主键

主键中的列数没有明确限制，可以通过增减主键中的列，以达到：

  1. 提高索引性能
    • 如果主键是（a，b），则在满足以下条件时添加另一列c将提高性能：
      • 在列c上存在条件的查询。
      • 具有相同（a，b）值的长数据范围（比index_granularity长几倍）是常见的。 换句话说，添加另一列时，您可以跳过很长的数据范围。
  2. 提高数据压缩率.
    • ClickHouse按主键对数据进行排序，因此一致性越高，压缩越好。
  3. 在CollapsingMergeTree和SummingMergeTree引擎中合并数据部件时提供其他逻辑

多列主键会对插入性能和内存消耗产生负面影响，但主键中的额外列在进行select查询时不会影响ClickHouse性能。

选择与排序键不同的主键

  1. 可以指定与排序键（用于对数据部分中的行进行排序的表达式）不同的主键（表达式，其值被写入每个标记的索引文件中）。在这种情况下，主键表达式元组必须是排序键表达式元组的前缀
  2. 排序键的ALTER是一个轻量级操作，因为当一个新列同时添加到表和排序键时，数据部分不需要更改（它们仍然按新的排序键表达式排序）
  3. 使用SummingMergeTree和AggregatingMergeTree表引擎时，此功能很有用。在使用这些引擎的常见情况下，该表有两种类型的列：维度和度量。典型查询使用任意GROUP BY聚合度量列的值并按维度过滤。由于SummingMergeTree和AggregatingMergeTree使用相同的排序键值聚合行，因此很自然地向其添加所有维度。因此，键表达式包含一长串列，并且必须使用新添加的维度频繁更新此列表。在这种情况下，在主键中只留下几列可以提供有效的范围扫描并将剩余的维列添加到排序键元组是有意义的。

在查询中使用索引和分区

对于SELECT查询，ClickHouse会判断是否可以使用索引：

对于在主键或者分区键的列，如果

  1. WHERE / PREWHERE子句具有表示相等或不等式比较操作的表达式（作为连接元素）
  2. 或者在列操作或表达式上具有固定前缀的IN或LIKE
  3. 或某些部分重复的函数
  4. 或存在逻辑关系的表达式。

因此，Clickhouse可以利用索引机制在主键的一个或多个范围上进行快速高效查询。

在下面的示例中，针对特定跟踪代码运行时查询会很快; 对于特定标签和日期范围; 对于特定的标签和日期; 对于具有日期范围的多个标签，依此类推。

示例：

让我们看看配置如下的引擎： 

1
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192

查询sql：

1
2
3
SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse将使用主键索引来筛选合适数据，使用月分区键来确定在日期范围内的分区。

从上面的示例sql可以看出，复杂表达式也是可以支持索引的，clickhouse从表中扫描数据是有规则的，使用索引不会比全表scan慢

下面的查询sql示例，将不能使用SQL

1
SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

要在运行查询时检查ClickHouse是否可以使用索引，请使用设置force_index_by_date和force_primary_key。

按月分区的关键是只允许读取包含适当范围日期的数据块。 在这种情况下，数据块可能包含许多日期（最多整月）的数据。 在块中，数据按主键排序，主键可能不包含日期作为第一列。 因此，使用仅具有未指定主键前缀的日期条件的查询将导致读取的数据多于单个日期。

对于并发：

  • 使用多版本控制方案，支持并发表访问。 换句话说，当同时读取和更新表时，从查询时当前的一组part读取数据。 没有冗长的锁。 插入不会妨碍读取操作。
  • 从表中读取会自动并行化。

7. Distributed 引擎

  1. 分布式引擎本身不存储数据，但允许从服务器上执行分布式查询
  2. 被写⼊入，做转发
  3. 查询，作为中间件， 聚合后返回给⽤用户

Clusters配置如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<remote_servers>
    <logs>
        <shard>
            <!-- Optional. Shard weight when writing data. Default: 1. -->
            <weight>1</weight>
            <!-- Optional. Whether to write data to just one of the replicas. Default: false (write data to all replicas). -->
            <internal_replication>false</internal_replication>
            <replica>
                <host>example01-01-1</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>example01-01-2</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <weight>2</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>example01-02-1</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>example01-02-2</host>
                <secure>1</secure>
                <port>9440</port>
            </replica>
        </shard>
    </logs>
</remote_servers>

其他详细见Clickhouse_Distributed_引擎一文

8. Dictionary 引擎

Clickhouse 支持从其他数据源引入数据作为字典使用，字典完全存储在内存中，不同的存储方式支持100W-1000W左右的数据，详细见Clickhouse_Dictionaries_xxx系列文章

9. Merge

  1. 不同于MergeTree引擎，也不属于MergeTree家族引擎
  2. Merge engine 本身也不存储数据
  3. 允许同时从任意数量的其他表读取数据，读取过程自动并行化。
  4. 不支持写入表
  5. 读取时，如果存在，则使用实际读取的表的索引。
  6. 使用Merge引擎的典型场景是使用大量TinyLog表，就像使用单个表一样。

Merge引擎接受参数：数据库名称和表的正则表达式。

1
2
#hits库，以WatchLog 开头的表
Merge(hits, '^WatchLog')

通过正则选择要合并的表时，不会选择Merge表本身，这是为了避免无限循环。注意，多个Merge表间，可能会出现无休止地尝试读取彼此的数据。

Merge表引擎示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE WatchLog_old(date Date, UserId Int64, EventType String, Cnt UInt64) 
ENGINE=MergeTree(date, (UserId, EventType), 8192);
INSERT INTO WatchLog_old VALUES ('2018-01-01', 1, 'hit', 3);

CREATE TABLE WatchLog_new(date Date, UserId Int64, EventType String, Cnt UInt64) 
ENGINE=MergeTree PARTITION BY date ORDER BY (UserId, EventType) SETTINGS index_granularity=8192;
INSERT INTO WatchLog_new VALUES ('2018-01-02', 2, 'hit', 3);

CREATE TABLE WatchLog as WatchLog_old ENGINE=Merge(currentDatabase(), '^WatchLog');

SELECT *
FROM WatchLog

┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │      1 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │      2 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘

10. 其他特殊引擎

10.1 File

数据源是特定格式（TabSeparated, Native, etc.）的文件
The data source is a file that stores data in one of the supported input formats (TabSeparated, Native, etc.).

应用场景举例:

  • 数据从Clickhouse导出到文件
  • 数据格式转换
  • 通过export - 修改文件 - import 的方式来实现 Update

10.2 Null

写入Null表时，将忽略数据。 从Null表读取时，响应为空。

但是，您可以在Null表上创建实例化视图。 因此写入表中的数据将最终出现在视图中。

10.3 Set

始终在RAM中的数据集。 它适用于IN运算符的右侧（请参见“IN运算符”部分）。

可以使用INSERT在表中插入数据。 新元素将添加到数据集中，而重复项将被忽略。 但是你不能从表中执行SELECT。 检索数据的唯一方法是在IN运算符的右半部分使用它。

数据始终位于RAM中。 对于INSERT，插入数据块也会写入磁盘上的表目录。 启动服务器时，此数据将加载到RAM。 换句话说，重新启动后，数据仍然存在。

对于非正常的服务器重启，磁盘上的数据块可能会丢失或损坏。 如果出现数据文件损坏，则需要手动删除包含损坏数据的文件。

10.4 Join

为JOIN 操作 预处理的数据结构，始终位于RAM中。

1
Join(ANY|ALL, LEFT|INNER, k1[, k2, ...])

参数：

  • ANY | ALL - 严格;
  • LEFT | INNER - 类型。 这些参数设置不带引号，并且必须与表将用于的JOIN匹配。
  • k1，k2，…是USING子句中将要进行连接的关键列。

该表不能用于GLOBAL JOIN。

您可以使用INSERT向表中添加数据，类似于Set引擎。 对于任何情况，将忽略重复键的数据。 对于ALL，它将被计算在内。 您无法直接从表中执行SELECT。 检索数据的唯一方法是将其用作JOIN的“右手”表。

在磁盘上存储数据与Set引擎相同。

10.5 URL

类似于File引擎。只不过数据源是通过访问URL地址获取

示例：

1.创建url表引擎：

1
2
CREATE TABLE url_engine_table (word String, value UInt64)
ENGINE=URL('http://127.0.0.1:12345/', CSV)

2.模拟http服务：

1
2
3
4
5
6
7
8
9
10
11
12
13
from http.server import BaseHTTPRequestHandler, HTTPServer

class CSVHTTPServer(BaseHTTPRequestHandler):
    def do_GET(self):
        self.send_response(200)
        self.send_header('Content-type', 'text/csv')
        self.end_headers()

        self.wfile.write(bytes('Hello,1\nWorld,2\n', "utf-8"))

if __name__ == "__main__":
    server_address = ('127.0.0.1', 12345)
    HTTPServer(server_address, CSVHTTPServer).serve_forever()
1
python3 server.py

3.查询：

1
SELECT * FROM url_engine_table
  • 读写并行
  • 不支持：
    • ALTER and SELECT…SAMPLE operations.
    • Indexes
    • Replication

10.6 View 视图

用于实现视图（有关更多信息，请参阅CREATE VIEW查询）。 它不存储数据，但仅存储指定的SELECT查询。 从表中读取时，它会运行此查询（并从查询中删除所有不必要的列）。

执行查询时等同于：

1
SELECT a, b, c FROM (SELECT ...)

10.7 MaterializedView：物化视图

相对于 View：

  • View视图是虚拟表，不存储数据
  • MaterializedView存储相应Select查询转换的数据

Select查询转换可以包含DISTINCT, GROUP BY, ORDER BY, LIMIT…

注意的是：相应的转化是在每次插入数据块上独立执行的，例如Group By，只针对插入数据的单个数据包的数据聚合，数据不会进一步汇总。例外情况是使用独立执行数据聚合的ENGINE，例如SummingMergeTree。

  , 
数据可视化_企业级BI工具Superset简介
Clickhouse Dictionaries 使用样例
  Follow
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×