ClickHouse 表引擎

程序员学习圈

已于 2022-01-18 17:37:50 修改

阅读量1.4k

点赞数

分类专栏： # ClickHouse 文章标签： clickhouse

于 2020-08-11 22:59:44 首次发布

本文链接：https://blog.csdn.net/luomingkui1109/article/details/107946733

版权

ClickHouse 专栏收录该内容

9 篇文章 2 订阅

订阅专栏

4.1 partition by 分区(可选)

4.2 primary key主键(可选)

4.3 order by （必选）

4.4 数据TTL

5. ReplacingMergeTree

0、表引擎是 ClickHouse 的一大特色。可以说，表引擎决定了如何存储表的数据。包括:

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

表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。

ClickHouse的表引擎有很多，下面介绍常用几种，对其他引擎有兴趣的可以去查阅官方文档：表引擎 | ClickHouse文档

1、TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。

// 创建一个TinyLog引擎的表并插入一条数据
:)create table t_tinylog (a UInt16, b String) ENGINE=TinyLog;
:)insert into t_tinylog (a, b) values (1, 'abc');
:)insert into t_tinylog (a, b) values (2, 'def');

// 此时我们到保存数据的目录/var/lib/clickhouse/data/default/t_tinylog中可以看到如下目录结构：
[root@hadoop102 t]# ls
a.bin  b.bin  sizes.json
a.bin 和 b.bin 是压缩过的对应的列的数据，sizes.json 中记录了每个 *.bin 文件的大小：
[root@hadoop102 t]# cat sizes.json 
{"yandex":{"a%2Ebin":{"size":"28"},"b%2Ebin":{"size":"30"}}}

2、Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。

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

3、Merge

Merge 引擎本身不存储数据，但可用于同时从任意多个其他的表中读取数据。读是自动并行的，不支持写入。读取时，那些被真正读取到数据的表的索引会被使用。

Merge 引擎的参数：一个数据库名和一个用于匹配表名的正则表达式。

// 案例：先建t1，t2，t3三个表，然后用 Merge 引擎的 t 表再把它们链接起来。
:)create table t1_tinylog (id UInt16, name String) ENGINE=TinyLog;
:)create table t2_tinylog (id UInt16, name String) ENGINE=TinyLog;
:)create table t3_tinylog (id UInt16, name String) ENGINE=TinyLog;


:)insert into t1_tinylog(id, name) values (1, 'first');
:)insert into t2_tinylog(id, name) values (2, 'second');
:)insert into t3_tinylog(id, name) values (3, 'i am in t3');


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


:) select * from t_merge;
┌─id─┬─name─┐
│  2 │ second │
└────┴──────┘
┌─id─┬─name──┐
│  1 │ first │
└────┴───────┘
┌─id─┬─name───────┐
│ 3     │ i am in t3 │
└────┴────────────┘

4、MergeTree

ClickHouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(*MergeTree) 中的其他引擎，支持索引和分区，地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree，还衍生除了很多小弟，也是非常有特色的引擎。

// 建表语句
create table t_order_mt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime
 ) engine = MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

insert into  t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00');
insert into  t_order_mt values (102,'sku_002',2000.00,'2020-06-01 11:00:00');
insert into  t_order_mt values (102,'sku_004',2500.00,'2020-06-01 12:00:00');
insert into  t_order_mt values (102,'sku_002',2000.00,'2020-06-01 13:00:00');
insert into  t_order_mt values (102,'sku_002',12000.00,'2020-06-01 13:00:00');
insert into  t_order_mt values (102,'sku_002',600.00,'2020-06-02 12:00:00');

// MergeTree其实还有很多参数(绝大多数用默认值即可)，但是三个参数是更加重要的，也涉及了关于MergeTree的很多概念。

4.1 partition by 分区(可选)

作用：学过hive的应该都不陌生，分区的目的主要是降低扫描的范围，优化查询速度。

如果不填：只会使用一个分区。

分区目录： MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

并行：分区后，面对涉及跨分区的查询统计，clickhouse会以分区为单位并行处理。

数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概10-15分钟后），clickhouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中。 optimize table xxxx [final];

4.2 primary key主键(可选)

clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key数据的。

主键的设定主要依据是查询语句中的where 条件。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全包扫描。

index granularity：直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。clickhouse中的MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

稀疏索引：稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

4.3 order by （必选）

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。

order by是MergeTree中唯一一个必填项，甚至比primary key 还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理（比如后面会讲的去重和汇总）。

要求：主键必须是order by字段的前缀字段。

比如order by 字段是 (id,sku_id) 那么主键必须是id 或者(id,sku_id)

4.4 数据TTL

TTL即Time To Live，MergeTree提供了可以管理数据或者列的生命周期的功能。

// 列级别TTL
create table t_order_mt3(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2)  TTL create_time+interval 10 SECOND,
    create_time  Datetime 
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id)

// 插入数据
insert into  t_order_mt3 values(106,'sku_001',1000.00,'2020-08-11 22:17:55');
insert into  t_order_mt3 values(107,'sku_002',2000.00,'2020-08-11 22:17:55');
insert into  t_order_mt3 values(110,'sku_003',600.00,'2020-08-11 22:17:55');

// 表级TTL
下面的这条语句是数据会在create_time 之后10秒丢失:alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。
能够使用的时间周期：SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR

5. ReplacingMergeTree

ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承MergeTree，只是多了一个去重的功能。

尽管MergeTree可以设置主键，但是primary key其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个ReplacingMergeTree。

去重时机：数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。

所以ReplacingMergeTree能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

create table t_order_rmt(
  id UInt32,
  sku_id String,
  total_amount Decimal(16,2) ,
  create_time  Datetime 
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id, sku_id)

// ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认保留最后一条。   

insert into  t_order_rmt values(101,'sku_001',1000.00,'2020-06-01 12:00:00');
insert into  t_order_rmt values(102,'sku_002',2000.00,'2020-06-01 11:00:00');
insert into  t_order_rmt values(102,'sku_002',12000.00,'2020-06-01 13:00:00');
insert into  t_order_rmt values(102,'sku_002',2000.00,'2020-06-01 13:00:00');
insert into  t_order_rmt values(102,'sku_004',2500.00,'2020-06-01 12:00:00');
insert into  t_order_rmt values(102,'sku_002',600.00,'2020-06-02 12:00:00');

// 通过测试得到结论
➢ 实际上是使用 order by 字段作为唯一键
➢ 去重不能跨分区
➢ 只有同一批插入(新版本)或合并分区时才会进行去重 ➢ 认定重复的数据保留，版本字段值最大的
➢ 如果版本字段相同则按插入顺序保留最后一笔

6、SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。 Clickhouse 为了这种场景，提供了一种能够“预聚合”的引擎SummingMergeTree。

6.1 案例演示

（1）创建表

create table t_order_smt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time  Datetime
) engine =SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

（2）插入数据

insert into  t_order_smt values(101,'sku_001',1000.00,'2020-08-01 12:00:00');
insert into  t_order_smt values(102,'sku_002',2000.00,'2020-08-01 11:00:00’);
insert into  t_order_smt values(102,'sku_002',2000.00,'2020-08-01 13:00:00');
insert into  t_order_smt values(102,'sku_002',12000.00,'2020-08-01 13:00:00');
insert into  t_order_smt values(102,'sku_004',2500.00,'2020-08-01 12:00:00');
insert into  t_order_smt values(102,'sku_002',600.00,’2020-08-02 12:00:00');

（3）执行第一次查询

hadoop102 :) select * from t_order_smt;

(4) 手动合并

OPTIMIZE TABLE t_order_smt FINAL;

( 5 ) 再执行一次查询

hadoop102 :) select * from t_order_smt;

6.2 通过结果可以得到以下结论

➢ 以SummingMergeTree()中指定的列作为汇总数据列

➢ 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列

➢ 以 order by 的列为准，作为维度列

➢ 其他的列按插入顺序保留第一行

➢ 不在一个分区的数据不会被聚合

➢ 只有在同一批次插入(新版本)或分片合并时才会进行聚合

6.3 开发建议

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

6.4 问题

能不能直接执行以下 SQL 得到汇总值

elect total_amount from XXX where province_name=’’ and create_date=’xxx’

不行，可能会包含一些还没来得及聚合的临时明细，如果要是获取汇总值，还是需要使用 sum 进行聚合，这样效率会有一定的提高，但本身 ClickHouse 是列式存储的，效率提升有限，不会特别明显。

select sum(total_amount) from province_name=’’ and create_date=‘xxx’

7.Distributed

分布式引擎，本身不存储数据, 但可以在多个服务器上进行分布式查询。读是自动并行的。读取时，远程服务器表的索引会被使用。

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

// 参数解析：
cluster_name  - 服务器配置文件中的集群名,在/etc/metrika.xml中配置的
database – 数据库名
table – 表名
sharding_key – 数据分片键

// 案例演示：
1）在hadoop102，hadoop103，hadoop104上分别创建一个表t
:)create table t_distributed(id UInt16, name String) ENGINE=TinyLog;

2）在三台机器的t表中插入一些数据
insert into t_distributed(id, name) values (1, 'zhangsan');
insert into t_distributed(id, name) values (2, 'lisi');

3）在hadoop102上创建分布式表
:) create table dis_table(id UInt16, name String) ENGINE=Distributed(perftest_3shards_1replicas, default, t_distributed, id);

4）往dis_table中插入数据
:) insert into dis_table select * from t_distributed;

5）查看数据量
:) select count() from dis_table;

┌─count()─┐
│       8 │
└─────────┘
:) select count() from t_distributed

6) 删除多个节点同一张表
hadoop102 :) drop table t_distributed on cluster perftest_3shards_1replicas;
DROP TABLE t_distributed ON CLUSTER perftest_3shards_1replicas
┌─host──────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ hadoop102 │ 9000 │      0 │       │                   2 │                0 │
│ hadoop104 │ 9000 │      0 │       │                   1 │                0 │
│ hadoop103 │ 9000 │      0 │       │                   0 │                0 │
└───────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
3 rows in set. Elapsed: 0.145 sec.

程序员学习圈

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
ClickHouse 表引擎

0、表引擎是 ClickHouse 的一大特色。可以说，表引擎决定了如何存储表的数据。包括:* 数据的存储方式和位置，写到哪里以及从哪里读取数据。* 支持哪些查询以及如何支持。* 并发数据访问。* 索引的使用（如果存在）。* 是否可以执行多线程请求。* 数据复制参数。表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。 ClickHouse的表引擎有很多，下面介绍常用几种，对其他引擎有兴趣的可以去查阅官方文档：https://clickhouse
复制链接

扫一扫