ClickHouse学习笔记之表引擎

最新推荐文章于 2024-03-12 17:26:21 发布
最新推荐文章于 2024-03-12 17:26:21 发布
阅读量486 收藏 1
点赞数
分类专栏： 大数据组件 数据库 文章标签： clickhouse 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37475168/article/details/126675943
版权

文章目录

介绍

表引擎是ClickHouse一大特色，决定了表数据的存储方式，包括：

  • 数据的存储方式和位置；
  • 支持的查询方式，以及如何支持；
  • 并发数据访问；
  • 索引的使用；
  • 多线程请求；
  • 数据复制参数。

表引擎使用时必须显式在创建表时定义该表使用的引擎，以及引擎参数，注意引擎名大小写敏感。

支持的表引擎

TinyLog

以列文件的形式将数据保存在硬盘上，不支持索引，没有并发控制，可用作保存少量数据。

scentos :) create table t_tinylog ( id String, name String) engine=TinyLog;

CREATE TABLE t_tinylog
(
    `id` String,
    `name` String
)
ENGINE = TinyLog

Query id: 1875eb5a-209d-47ad-9d5d-a4a551f80926

Ok.

0 rows in set. Elapsed: 0.002 sec.

Memory

内存引擎，将数据以未压缩的原始形式直接保存到内存中，服务器重启数据丢失，读写不会相互阻塞，不支持索引，再简单查询下有着很高的性能。内存引擎一般用来测试，即性能需求高，且数据量不太大(不超过一亿行)的场景。

MergeTree

ClickHouse中最强大的表引擎就是合并树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') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

order by

必选选项，设定了分区内数据按照哪些字段排序，在不设置主键的情况下，进行去重、汇总等操作时会会依照order by字段进行处理。

主键必须以order by字段为前缀，比如order by字段为(a, b)，那么主键只能是a或者(a, b)

partition by分区

分区的目的是为了降低扫描范围，优化查询速度。如果不填，默认为一个分区。
MergeTree是以列文件+索引文件+表定义文件组成的，如果设定了分区，那么这些文件会保存到不同的分区目录中，且ClickHouse会以分区为单位并行处理跨分区的查询统计。
任何批次的数据写入都会产生一个临时分区，该临时分区不会纳入任何一个已有分区，写入后一段时间，ClickHouse会自行将临时分区合并到已有分区中，我们也可以通过optimize命令手动执行合并：

optimize table table_name final;

上例中，我们的表数据按年月日格式的create_time列分区，我们可以查看一下数据：

scentos :) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: a98c9b62-3053-43e3-8675-66613cd5fed6

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

6 rows in set. Elapsed: 0.003 sec.

再执行一次写入，并立刻查询：

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

INSERT INTO t_order_mt FORMAT Values

Query id: 6c133036-8444-45c4-a1a8-e05e3b1c3492

Ok.

6 rows in set. Elapsed: 0.003 sec.

scentos :) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: 01abdb15-1f4a-48a0-abf2-d36463e7a43f

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

12 rows in set. Elapsed: 0.003 sec.

optimize后，再次查询：

scentos :) optimize table t_order_mt final;

OPTIMIZE TABLE t_order_mt FINAL

Query id: 00061d16-5830-4762-a2ed-c43def2b8fca

Ok.

0 rows in set. Elapsed: 0.003 sec.

scentos :) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: 598a25c3-4c98-459d-94ec-c270de6a25f2

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

12 rows in set. Elapsed: 0.003 sec.

primary key主键

ClickHouse中的主键只提供了数据的一级索引，而非一级约束，因此可以存在主键相同的数据。
主键的设定依据主要是查询语句的where条件，通过查询条件，合并树引擎对主键进行二分查找，定位到对应的索引粒度(index granularity)。避免了全表扫描。
索引粒度指的是稀疏索引中两个相邻索引的的数据间隔，合并树中默认值为8192，如果某列存在大量重复值，导致一个分区几万行才有一个不同的数据时，我们可以将这个值调大，但官方不建议修改。
稀疏索引示例图如下所示：
在这里插入图片描述
可以看到，稀疏索引的好处是通过很少的索引定位更多的数据，但每次定位只能定位到索引粒度的第一行，然后再进行进一步地扫描。

二级索引

二级索引再ClickHouse 20.1.2.4之前是实验性的，需要手动开启，在这个版本之后，默认开启，开启方式为：

set allow_experimental_data_skipping_indices=1;

20.1.2.4之后的版本中，allow_experimental_data_skipping_indices参数已经被删除

我们首先创建测试表：

create table t_order_mt2(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);

其中INDEX a total_amount TYPE minmax GRANULARITY 5表示为列total_amount建立二级索引(一级索引为主键)，类型为minmax(存储该列的最值)，该索引对于对应的一级索引粒度为5。然后插入一些测试数据：

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

此时一级索引有两个：101，102；101只有一个二级索引1000，102中有600和12000两个二级索引，在CenOS7中进行测试：查询>900的数据：

[szc@scentos ~]$  clickhouse-client --send_logs_level=trace <<< 'select
> * from t_order_mt2 where total_amount > toDecimal32(900., 2)';
[scentos] 2021.12.10 19:10:59.278281 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> executeQuery: (from [::1]:42466) select * from t_order_mt2 where total_amount > toDecimal32(900., 2)
[scentos] 2021.12.10 19:10:59.279046 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "total_amount > toDecimal32(900., 2)" moved to PREWHERE
[scentos] 2021.12.10 19:10:59.279487 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Trace> ContextAccess (default): Access granted: SELECT(id, sku_id, total_amount, create_time) ON default.t_order_mt2
[scentos] 2021.12.10 19:10:59.279665 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[scentos] 2021.12.10 19:10:59.280011 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): Key condition: unknown
[scentos] 2021.12.10 19:10:59.280247 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): MinMax index condition: unknown
[scentos] 2021.12.10 19:10:59.281230 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): Index `a` has dropped 1/2 granules.
[scentos] 2021.12.10 19:10:59.281341 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): Selected 2/2 parts by partition key, 1 parts by primary key, 2/2 marks by primary key, 1 marks to read from 1 ranges
[scentos] 2021.12.10 19:10:59.281658 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 20200601_1_1_0, approx. 5 rows starting from 0
[scentos] 2021.12.10 19:10:59.283240 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Information> executeQuery: Read 5 rows, 160.00 B in 0.004803457 sec., 1040 rows/sec., 32.53 KiB/sec.
[scentos] 2021.12.10 19:10:59.283297 [ 55472 ] {ebdb07d8-b6cf-4bf1-8568-a5b80cbeaac2} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
101    sku_001    1000    2020-06-01 12:00:00
102    sku_002    2000    2020-06-01 11:00:00
102    sku_002    2000    2020-06-01 13:00:00
102    sku_002    12000    2020-06-01 13:00:00
102    sku_004    2500    2020-06-01 12:00:00

因为101只有一个二级索引，所以可以通过这一个二级索引决定读取还是排除101一级索引的数据，因此二级索引a会跳过101该一级索引；对于102，因为900在其二级索引[600, 12000]中，从而不能跳过102一级索引，得逐条读取比较，故此，二级索引a跳过了一个“索引粒度”：

Index `a` has dropped 1/2 granules.

查询<200的数据：

[szc@scentos ~]$  clickhouse-client --send_logs_level=trace <<< 'select
* from t_order_mt2 where total_amount < toDecimal32(200., 2)';
[scentos] 2021.12.10 19:22:19.517281 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> executeQuery: (from [::1]:42610) select * from t_order_mt2 where total_amount < toDecimal32(200., 2)
[scentos] 2021.12.10 19:22:19.517679 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "total_amount < toDecimal32(200., 2)" moved to PREWHERE
[scentos] 2021.12.10 19:22:19.517893 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Trace> ContextAccess (default): Access granted: SELECT(id, sku_id, total_amount, create_time) ON default.t_order_mt2
[scentos] 2021.12.10 19:22:19.518014 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[scentos] 2021.12.10 19:22:19.518156 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): Key condition: unknown
[scentos] 2021.12.10 19:22:19.518262 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): MinMax index condition: unknown
[scentos] 2021.12.10 19:22:19.518588 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): Index `a` has dropped 2/2 granules.
[scentos] 2021.12.10 19:22:19.518614 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> default.t_order_mt2 (f8300897-4912-4035-b830-08974912f035) (SelectExecutor): Selected 2/2 parts by partition key, 0 parts by primary key, 2/2 marks by primary key, 0 marks to read from 0 ranges
[scentos] 2021.12.10 19:22:19.519013 [ 55472 ] {e9ede560-a577-49ba-875d-596e9799ecbe} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
[szc@scentos ~]$

类似方才的分析，101和102都被二级索引a跳过：

Index `a` has dropped 2/2 granules.

数据存活时间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);

其中total_amount Decimal(16,2) TTL create_time+interval 10 SECOND指定该(total_amount)列存活时间为10s，即当前时间>某一行数据的create_time列+10s时，该行该列(total_amount)字段清零。
我们先插入数据

scentos :) insert into t_order_mt3 values
           (106,'sku_001',1000.00,'2021-12-10 20:14:50'),
           (107,'sku_002',2000.00,'2021-12-10 22:14:20'),
           (110,'sku_003',600.00,'2021-12-10 12:00:00');

INSERT INTO t_order_mt3 FORMAT Values

Query id: 95e3afc8-322c-4c51-a65b-a1713dc77a34

Ok.

3 rows in set. Elapsed: 0.003 sec.

scentos :) select * from t_order_mt3;

SELECT *
FROM t_order_mt3

Query id: ff04f192-809d-4203-9e7d-e5445e7dcdc1

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 106 │ sku_001 │         1000 │ 2021-12-10 20:14:50 │
│ 107 │ sku_002 │         2000 │ 2021-12-10 22:14:20 │
│ 110 │ sku_003 │            0 │ 2021-12-10 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

3 rows in set. Elapsed: 0.002 sec.

如果没有即时生效，需要对表进行手动合并：

scentos :) optimize table t_order_mt3 final;

OPTIMIZE TABLE t_order_mt3 FINAL

Query id: 661e3c01-0975-4f7e-9fee-c5b0be152511

Ok.

0 rows in set. Elapsed: 0.003 sec.

scentos :) select * from t_order_mt3;

SELECT *
FROM t_order_mt3

Query id: cfb71396-7731-4072-844c-38146804d853

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 106 │ sku_001 │            0 │ 2021-12-10 20:14:50 │
│ 107 │ sku_002 │         2000 │ 2021-12-10 22:14:20 │
│ 110 │ sku_003 │            0 │ 2021-12-10 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

3 rows in set. Elapsed: 0.002 sec.
表级存活时间

表级存活时间是指，超过指定时间的数据行会丢失(而非清零)，所涉及的字段必须是Date或Datetime类型，建议使用分区的日期字段。

比如下面是个例子：清除当前时间时间超过create_time字段10s的数据行：

scentos :) alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

ALTER TABLE t_order_mt3
    MODIFY TTL create_time + toIntervalSecond(10)

Query id: bd8e52a1-070a-4b9a-819e-0755b1a2b85b

Ok.

0 rows in set. Elapsed: 0.040 sec.

scentos :) select * from t_order_mt3;

SELECT *
FROM t_order_mt3

Query id: 2fc4d900-1ef8-4360-94b4-f635d8b33563

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 107 │ sku_002 │         2000 │ 2021-12-10 22:14:20 │
└─────┴─────────┴──────────────┴─────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

可以用的时间周期有：SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR，顾名思义，分别表示秒、分、时、天、周、月、三个月、年。

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

engine =ReplacingMergeTree(create_time)中传入的参数为去重字段，只保留各分区内该字段值最大的数据，如果不填，默认保留各分区最新的数据。
然后插入数据：

insert into t_order_rmt values
           (101,'sku_001',1000.00,'2021-12-10 20:37:00') ,
           (102,'sku_002',2000.00,'2021-12-10 20:35:00'),
           (102,'sku_004',2500.00,'2021-12-10 20:35:00'),
           (102,'sku_002',2000.00,'2020-12-10 20:36:00'),
           (102,'sku_002',12000.00,'2021-12-10 20:33:00'),
           (102,'sku_002',600.00,'2021-12-10 20:36:00');

并进行查询：

scentos :)  select * from t_order_rmt;

SELECT *
FROM t_order_rmt

Query id: 050d0f4d-2eda-4e70-82b7-87f845701095

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2021-12-10 20:37:00 │
│ 102 │ sku_002 │          600 │ 2021-12-10 20:36:00 │
│ 102 │ sku_004 │         2500 │ 2021-12-10 20:35:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │         2000 │ 2020-12-10 20:36:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.024 sec.

再插入数据，把(102,'sku_002',600.00,'2021-12-10 20:36:00')中的600改为1600，立刻查询：

scentos :) insert into t_order_rmt values
           (101,'sku_001',1000.00,'2021-12-10 20:37:00') ,
           (102,'sku_002',2000.00,'2021-12-10 20:35:00'),
           (102,'sku_004',2500.00,'2021-12-10 20:35:00'),
           (102,'sku_002',2000.00,'2020-12-10 20:36:00'),
           (102,'sku_002',12000.00,'2021-12-10 20:33:00'),
           (102,'sku_002',1600.00,'2021-12-10 20:36:00');

INSERT INTO t_order_rmt FORMAT Values

Query id: b5508d64-9feb-4d01-9264-db69e0e8ceb1

Ok.

6 rows in set. Elapsed: 0.003 sec.

scentos :)  select * from t_order_rmt;

SELECT *
FROM t_order_rmt

Query id: 946d4461-c00a-4aef-bddc-09e7aca31be9

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2021-12-10 20:37:00 │
│ 102 │ sku_002 │         1600 │ 2021-12-10 20:36:00 │
│ 102 │ sku_004 │         2500 │ 2021-12-10 20:35:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │         2000 │ 2020-12-10 20:36:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2021-12-10 20:37:00 │
│ 102 │ sku_002 │          600 │ 2021-12-10 20:36:00 │
│ 102 │ sku_004 │         2500 │ 2021-12-10 20:35:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │         2000 │ 2020-12-10 20:36:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

8 rows in set. Elapsed: 0.003 sec.

发现新数据没有被去重，我们手动合并后，再查询，可见数据回到了四行：

scentos :) optimize table t_order_rmt final;

OPTIMIZE TABLE t_order_rmt FINAL

Query id: 4ae5add5-c25a-4834-acd9-99d6ae9c64c5

Ok.

0 rows in set. Elapsed: 0.003 sec.

scentos :)  select * from t_order_rmt;

SELECT *
FROM t_order_rmt

Query id: 6aeded9c-9cdb-4aea-8fbd-66e16adb79d5

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │         2000 │ 2020-12-10 20:36:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2021-12-10 20:37:00 │
│ 102 │ sku_002 │         1600 │ 2021-12-10 20:36:00 │
│ 102 │ sku_004 │         2500 │ 2021-12-10 20:35:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.003 sec.

scentos :)

可以得到以下结论：

  1. 去重合并树使用的是order by字段作为唯一主键；
  2. 去重不能跨分区；
  3. 只有同一批插入数据或合并表时才会去重；
  4. 去重时，保留去重字段值最大的数据；如果去重字段值相同，保留最新插入的数据。

SummingMergeTree

如果只关心数据的汇总聚合，我们可以使用汇总合并树SummingMergeTree，该引擎提供了预聚合的功能，以节省存储空间和查询性能的开销。

示例如下，先创建一张表，使用汇总合并树引擎：

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

然后插入数据：

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

进行查询：

scentos :) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: 3fbe051f-e47e-4693-b2cc-fa395547e059

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │        16000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.002 sec.

scentos :)

先按时间将数据分为6月2号和6月1号两个区，然后对每个分区内order by字段相同的数据的total_amount值进行合并累加。

我们再做一次数据插入和查询，将(102,'sku_002',12000.00,'2020-06-01 13:00:00')中的13:00:00改为23:00:00：

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

INSERT INTO t_order_smt FORMAT Values

Query id: a6a291e0-a44f-41ba-973b-5ab470878d1a

Ok.

6 rows in set. Elapsed: 0.003 sec.

scentos :) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: 5c1d2e64-6b51-4190-b467-130ccb441034

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │        16000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │        16000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

8 rows in set. Elapsed: 0.004 sec.

可见新插入的数据没有被聚合，且(102,'sku_002',12000.00',...)只保留了插入时间最早的一条

102 │ sku_002 │        16000 │ 2020-06-01 11:00:00

然后我们手动合并一下：

scentos :) optimize table t_order_smt final;

OPTIMIZE TABLE t_order_smt FINAL

Query id: 1ab134b2-a680-4005-a36f-724e4be5222a

Ok.

0 rows in set. Elapsed: 0.003 sec.

scentos :) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: 4ba33366-5d47-4e82-9b1a-299567068182

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │         1200 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         2000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │        32000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │         5000 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.020 sec.

因此，我们可以得到以下结论：

  1. 汇总合并树需要指定某一列为合并列，可以是多列，但必须是数字列，默认以所有的非order by的数字列进行合并；
  2. 非合并列按插入顺序保留最早的数据；
  3. 聚合不会跨分区；
  4. 只有同一批次插入的数据或手动合并后，才会进行聚合。

相比聚合函数sum，汇总合并树的效率更高。

coder_szc
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ClickHouse常用表引擎
寒暄的博客
07-01 5134
表引擎 表引擎(表的类型)决定了： 数据的存储方式和位置，写到哪里以及从哪里读取数据 支持哪些查询以及如何支持 并发数据访问 索引的使用 是否可以多线程请求 数据复制参数 TinyLog 最简单的表引擎。存储在磁盘中，不支持索引，没有并发控制。 TinyLog支持并发读，但是不支持并发写与并发读写，所以TinyLog表适合存放一些静态信息或者变化缓慢的信息。有点像数仓中的缓慢变化维度。 创建TinyLog引擎表只需要在创建语句最后加上engine=TinyLog 这时在ClickHouse中保存def
ClickHouse引擎（你想要的都有）
qq_36803154的博客
06-10 1291
如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。该引擎继承了MergeTree引擎，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于group by的效果。当进行汇总数据操作时，可以通过改变查询方式，来过滤掉被删除的数据，只有相同分区内的数据才有可能被折叠。
ClickHouse引擎详解
最新发布
weixin_59295776的博客
03-12 1248
共同属性数据存储在磁盘上 写入时将数据追加在文件末尾 不支持突变(Mutation)操作 不支持索引（这意味着。
ClickHouse表引擎
robinhunan的博客
10-20 890
ClickHouse表引擎
【ClickHouse】表引擎详解
J小白的博客
05-19 4005
目录 1.日志引擎系列--Log系列 1.1TinyLog 1.2Log 1.3StripeLog 2.Special系列 2.1Memory 2.2Merge 2.3Distributed 3.MergeTree系列--合并树引擎系列 3.1MergeTree 3.2ReplacingMergeTree 3.3CollapsingMergeTree 3.4VersionedCollapsingMergeTree 3.5SummingMergeTree 3.6Aggregati
篇二|什么是ClickHouse的表引擎?
jmx_bigdata的博客
09-21 1966
在上一篇分享中，我们介绍了ClickHouse的安装部署和简单使用。本文将介绍ClickHouse中一个非常重要的概念—表引擎(table engine)。如果对MySQL熟悉的话，或许你应该听说过InnoDB和MyISAM存储引擎。不同的存储引擎提供不同的存储机制、索引方式、锁定水平等功能，也可以称之为表类型。ClickHouse提供了丰富的表引擎，这些不同的表引擎也代表着不同的表类型。比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。本文会对ClickHouse中常见的表引擎进行介绍，主要包括
ClickHouse表引擎详解看这篇就够了-基本讲解、处理逻辑、测试实例
weixin_43805705的博客
01-03 2037
表引擎在 ClickHouse 中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持 index、支持的 query 种类、是否支持主备复制等。 ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。
【基础篇】ClickHouse 表引擎详解
日拱一卒无有尽，功不唐捐终入海
09-17 1294
Clickhouse 表引擎（Table Engine）是一种数据库管理系统中的关键部分，负责存储数据、读取数据和处理数据查询。不同的表引擎具有不同的性能特性和适用范围。在某些数据库系统中，例如MySQL，你可以为每个表选择不同的表引擎，包括InnoDB，MyISAM，Memory等。这些表引擎在功能、性能、并发能力、恢复能力等方面都有所不同。在ClickHouse中，表引擎的选择将影响数据的存储格式、索引的使用、并发控制机制、是否支持数据复制等许多重要的方面。表引擎（即表的类型）决定了什么。
ClickHouse基础知识（四）：ClickHouse 引擎详解
zuodingquan666的博客
12-28 1754
（2）向表中插入数据。
Clickhouse表引擎
weixin_64910075的博客
09-11 70
Clickhouse
clickhouse学习笔记
09-14
clickhouse学习笔记，学习clickhouse学习心得和笔记
ClickHouse入门学习笔记
11-12
clickhouse入门学习笔记，包括以下内容： 1.基础知识入门 2.数据类型介绍 3.目录结构介绍 4.表引擎介绍 5.常用SQL 6.副本机制 7.分片集群机制 8.执行计划介绍 9.建表优化规则 10.语法优化规则 11.查询优化规则 12....
ClickHouse学习笔记总结
04-30
列式数据库ClickHouse
Clickhouse学习笔记
06-05
Clickhouse学习笔记
mysql学习笔记之数据引擎
12-15
查看当前数据库支持的引擎 show engines +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment...
Clickhouse表引擎介绍
Nortyr的博客
04-15 814
Clickhouse表引擎介绍 表引擎作用 数据如何存储,从哪写入,从哪读取 支持哪些查询，以及如何支持。 并发数据访问 如果存在,使用索引 是否支持多线程 数据复制 表引擎类型 MergeTree 适用于高负载,最强大的表引擎 同构后续的后台数据处理,快速插入数据,然后应用规则在后台合并这些部分 支持数据复制、分区、辅助数据跳过索引以及其他引擎不支持的其他功能 种类 MergeTree ReplacingMergeTree SummingMergeTree AggregatingMerg
【clickhouse】ClickHouse表引擎 MergeTree 建表方式与分区规则
九师兄
02-07 4830
1.概述 转载：ClickHouse表引擎 1.MergeTree 建表方式与分区规则 2.MergeTree 介绍 表引擎是 ClickHouse 设计实现中的一大特色。可以说，是表引擎决定了一张数据表最终的“性格”，比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。 ClickHouse拥有非常庞大的表引擎体系，其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎。而在这众多的表引擎中，又属合并树（MergeTree）表引擎及其家族系列最为强大，在生产环境的绝大部分场景中，都.
大数据之clickhouse各类引擎
大数据同盟会的博客
05-29 2288
一、基本语法 1、建表语句 目前只有MergeTree、Merge和Distributed这三类表引擎支持 alter 操作。 create table test_ddl1( id UInt16 , name String ) engine = MergeTree() order by id ; --MergeTree一定要指定主键和排序字段，order by 代表两个含义 查看建表语句 show create table test_ddl1 2、修改表结构 alter table
ClickHouse笔记
04-24
因此，如果您正在考虑使用 ClickHouse 进行数据存储和分析，我可以向您介绍一些相关的笔记，例如如何模拟数据并使用 ClickHouse 进行性能测试，如何设置 ClickHouse 集群等等。请告诉我您最感兴趣的方面或任何其他...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值