clickhouse--常用表引擎

最新推荐文章于 2023-10-31 19:31:03 发布

囊萤映雪的萤

最新推荐文章于 2023-10-31 19:31:03 发布

阅读量577

点赞数

分类专栏： clickhouse 大数据文章标签：大数据 spark sql

本文链接：https://blog.youkuaiyun.com/liuyingying0418/article/details/121547844

版权

大数据同时被 2 个专栏收录

21 篇文章

订阅专栏

clickhouse

7 篇文章

订阅专栏

表引擎

MergeTree Family

MergeTree

特点

用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2,
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]

order by(必选)

分区内按照指定字段顺序排序，主键必须是order by字段的前缀字段。
可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate)。
如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。
如果不需要排序，可以使用 ORDER BY tuple()。

partition by分区

分目录，不设置分区的时候所有数据均在all分区。分区后，以分区为单位进行并行处理。
要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

primary key主键(可选)

如果要选择与排序键不同的主键，在这里指定，可选项。默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。
因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

index granularity

索引粒度，相邻索引数据间隔，MergeTree默认是8192，索引列重复值较多时可以修改该值。

分区合并

任何批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后某个时刻(大概10-15分钟)，clickhouse会自动执行合并操作，把临时分区数据合并到已有分区内。也可手动通过optimize执行。
optimize table xxx [partition partitionId] final;

数据TTL

使用场景：实时数据，用户画像

列级别TTL

要求：不可是主键列，必须是日期类型
建表语句中指定列后添加，设置该列数据10秒后过期：

TTL create_time+interval 10 SECOND;

或者建表后新增列TTL：

alter table t_table modify column col1 String TTL create_time+interval 10 SECOND;

表级别TTL

设置10秒后数据丢失：

alter table t_table MODIFY TTL create_time + INTERVAL 10 SECOND;

完整示例如下，可用的时间级别：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。

create table t_table
(
	d DateTime,
	a Int
)ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
	d + INTERVAL 1 WEEK TO VOLUMNE 'aaa',
	d + INTERVAL 2 WEEK TO DISK 'bbb'

案例实操

建表及导入数据

create table t_order_mt(
	id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    crete_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 13:00:00'),
(103, 'sku_003', 3000.00, '2020-06-01 14:00:00')

ReplacingMergeTree

特点

会删除排序键值相同的重复项，依据order by字段

去重时机：去重只会在合并或者同一批插入时进行

去重范围：在分区内部去重，不能跨分区去重

建表语法

ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的，不填的情况下默认保留最新一条。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

案例实操

建表及数据导入

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

insert into test.t_order_rmt values
(101,'sku_001',1000.00,'apple','2020-06-01 13:00:00'),
(102,'sku_002',2000.00,'orange','2020-06-01 16:00:00'),
(103,'sku_003',3000.00,'banana','2020-06-01 15:00:00'),
(101,'sku_001',2000.00,'cherry','2020-06-01 14:00:00'),
(102,'sku_002',3000.00,'pear','2020-06-01 13:00:00'),
(103,'sku_003',4000.00,'tomato','2020-06-01 12:00:00'),
(101,'sku_001',1000.00,'peach','2020-06-02 12:00:00'),
(102,'sku_002',2000.00,'grape','2020-06-02 13:00:00'),
(103,'sku_003',3000.00,'mango','2020-06-02 14:00:00');

查询该表，结果如下：

┌──id─┬─sku_id──┬─total_amount─┬─goods─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ peach │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ grape │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      3000.00 │ mango │ 2020-06-02 14:00:00 │
└─────┴─────────┴──────────────┴───────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ cherry │ 2020-06-01 14:00:00 │
│ 102 │ sku_002 │      2000.00 │ orange │ 2020-06-01 16:00:00 │
│ 103 │ sku_003 │      3000.00 │ banana │ 2020-06-01 15:00:00 │
└─────┴─────────┴──────────────┴────────┴─────────────────────┘

结果表明，在分区内，按照 id 和 sku_id 字段为唯一值，其他列仅保留create_time最新(即最大)的数据。

再次插入数据

insert into test.t_order_rmt values
(101,'sku_001',1000.00,'lemon','2020-06-02 17:00:00'),
(102,'sku_002',2000.00,'watermelon','2020-06-02 13:00:00'),
(103,'sku_003',3000.00,'pineapple','2020-06-02 12:00:00');

数据暂未合并，结果：

┌──id─┬─sku_id──┬─total_amount─┬─goods──────┬─────────create_time─┐│ 101 │ sku_001 │      1000.00 │ lemon      │ 2020-06-02 17:00:00 ││ 102 │ sku_002 │      2000.00 │ watermelon │ 2020-06-02 13:00:00 ││ 103 │ sku_003 │      3000.00 │ pineapple  │ 2020-06-02 12:00:00 │└─────┴─────────┴──────────────┴────────────┴─────────────────────┘┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐│ 101 │ sku_001 │      2000.00 │ cherry │ 2020-06-01 14:00:00 ││ 102 │ sku_002 │      2000.00 │ orange │ 2020-06-01 16:00:00 ││ 103 │ sku_003 │      3000.00 │ banana │ 2020-06-01 15:00:00 │└─────┴─────────┴──────────────┴────────┴─────────────────────┘┌──id─┬─sku_id──┬─total_amount─┬─goods─┬─────────create_time─┐│ 101 │ sku_001 │      1000.00 │ peach │ 2020-06-02 12:00:00 ││ 102 │ sku_002 │      2000.00 │ grape │ 2020-06-02 13:00:00 ││ 103 │ sku_003 │      3000.00 │ mango │ 2020-06-02 14:00:00 │└─────┴─────────┴──────────────┴───────┴─────────────────────┘

可执行合并语句去重：

optimize table test.t_order_rmt final;

合并后结果如下，2020-06-02分区的数据仍然是依据create_time保留了最新一条数据，对于create_time相同的数据，保留最新插入的一条。

┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ cherry │ 2020-06-01 14:00:00 │
│ 102 │ sku_002 │      2000.00 │ orange │ 2020-06-01 16:00:00 │
│ 103 │ sku_003 │      3000.00 │ banana │ 2020-06-01 15:00:00 │
└─────┴─────────┴──────────────┴────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods──────┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ lemon      │ 2020-06-02 17:00:00 │
│ 102 │ sku_002 │      2000.00 │ watermelon │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      3000.00 │ mango      │ 2020-06-02 14:00:00 │
└─────┴─────────┴──────────────┴────────────┴─────────────────────┘

SummingMergeTree

特点

把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

预聚合，分区内聚合，分片合并时聚合，按照order by字段聚合，取最早一条数据。

SummingMergeTree括号内指定汇总列，如果不指定，则所有非维度列且为数字列的字段为汇总数据列，order by的列作为维度列，其他列按照顺序保留第一行，同一批次插入数据或者分片合并时才会进行聚合。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

案例实操

建表并导入数据

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

insert into test.t_order_smt values
(101,'sku_001',1000.00,'apple','2020-06-01 17:00:00'),
(102,'sku_002',2000.00,'orange','2020-06-01 16:00:00'),
(103,'sku_003',3000.00,'banana','2020-06-01 15:00:00'),
(101,'sku_001',1000.00,'cherry','2020-06-01 14:00:00'),
(102,'sku_002',2000.00,'pear','2020-06-01 13:00:00'),
(103,'sku_003',3000.00,'tomato','2020-06-01 12:00:00'),
(101,'sku_001',1000.00,'peach','2020-06-02 12:00:00'),
(102,'sku_002',2000.00,'grape','2020-06-02 13:00:00'),
(103,'sku_003',3000.00,'mango','2020-06-02 14:00:00');

查询数据表

select * from test.t_order_smt;

结果如下所示，我们发现，原本应该有9条数据插入，但实际查询出来只有6条。这是因为SummingMergeTree引擎的表自动进行了合并。合并以分区为单位进行，按照order by字段 id 和 sku_id 进行合并，建表时指定了total_amount作为合并列，因此该列在合并时进行了求和。而剩下的列则按照数据插入顺序获取了最早的一条数据。

┌──id─┬─sku_id──┬─total_amount─┬─goods───┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ apple   │ 2020-06-01 17:00:00 │
│ 102 │ sku_002 │      4000.00 │ orange  │ 2020-06-01 16:00:00 │
│ 103 │ sku_003 │      6000.00 │ banana  │ 2020-06-01 15:00:00 │
└─────┴─────────┴──────────────┴─────── ─┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ peach  │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ grape  │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      3000.00 │ mango  │ 2020-06-02 14:00:00 │
└─────┴─────────┴──────────────┴─────── ┴─────────────────────┘

再次插入数据

insert into test.t_order_smt values
(101,'sku_001',1000.00,'lemon','2020-06-02 17:00:00'),
(102,'sku_002',2000.00,'watermelon','2020-06-02 13:00:00'),
(103,'sku_003',3000.00,'pineapple','2020-06-02 12:00:00');

数据未自动合并：

┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ peach  │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ grape  │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      3000.00 │ mango  │ 2020-06-02 14:00:00 │
└─────┴─────────┴──────────────┴────── ─┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods───┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ apple   │ 2020-06-01 17:00:00 │
│ 102 │ sku_002 │      4000.00 │ orange  │ 2020-06-01 16:00:00 │
│ 103 │ sku_003 │      6000.00 │ banana  │ 2020-06-01 15:00:00 │
└─────┴─────────┴──────────────┴──────── ┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods───────┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ lemon       │ 2020-06-02 17:00:00 │
│ 102 │ sku_002 │      2000.00 │ watermelon  │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      3000.00 │ pineapple   │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────── ─┴─────────────────────┘

执行合并语句：

optimize table test.t_order_smt final;

合并后查询结果如下，对比可以看出，2020-06-02分区内的数据也是直接依据 id 和 sku_id 进行聚合，对 total_amount 求和，而 goods 和 create_time 则是直接选择最早插入的数据。

┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ peach  │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │      4000.00 │ grape  │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      6000.00 │ mango  │ 2020-06-02 14:00:00 │
└─────┴─────────┴──────────────┴─────── ┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods───┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ apple   │ 2020-06-01 17:00:00 │
│ 102 │ sku_002 │      4000.00 │ orange  │ 2020-06-01 16:00:00 │
│ 103 │ sku_003 │      6000.00 │ banana  │ 2020-06-01 15:00:00 │
└─────┴─────────┴──────────────┴──────── ┴─────────────────────┘

AggregatingMergeTree

特点

将一个数据片段内所有具有相同主键（准确的说是排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。可以使用 AggregatingMergeTree 表来做增量数据的聚合统计，包括物化视图的数据聚合。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

案例实操

普通表

先建一个常规列的表。

create table test.t_order_amt(
	id UInt32,
	sku_id String,
	total_amount Decimal(16,2),
	goods String,
	create_time Datetime
)engine=AggregatingMergeTree()
partition by toYYYYMMDD(create_time)
order by (id, sku_id);

insert into test.t_order_amt values
(101,'sku_001',1000.00,'apple','2020-06-01 17:00:00'),
(102,'sku_002',2000.00,'orange','2020-06-01 16:00:00'),
(103,'sku_003',3000.00,'banana','2020-06-01 15:00:00'),
(101,'sku_001',1000.00,'cherry','2020-06-01 14:00:00'),
(102,'sku_002',2000.00,'pear','2020-06-01 13:00:00'),
(103,'sku_003',3000.00,'tomato','2020-06-01 12:00:00'),
(101,'sku_001',1000.00,'peach','2020-06-02 12:00:00'),
(102,'sku_002',2000.00,'grape','2020-06-02 13:00:00'),
(103,'sku_003',3000.00,'mango','2020-06-02 14:00:00'),
(101,'sku_001',1000.00,'lemon','2020-06-02 17:00:00'),
(102,'sku_002',2000.00,'watermelon','2020-06-02 13:00:00'),
(103,'sku_003',3000.00,'pineapple','2020-06-02 12:00:00');

查询该表发现，AggregatingMergeTree引擎会自动依据order by字段，按照插入顺序保留最早的一条数据。很显然这并没有发挥该引擎自动聚合数据的功能。

┌──id─┬─sku_id──┬─total_amount─┬─goods─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ peach │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ grape │ 2020-06-02 13:00:00 │
│ 103 │ sku_003 │      3000.00 │ mango │ 2020-06-02 14:00:00 │
└─────┴─────────┴──────────────┴───────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─goods──┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ apple  │ 2020-06-01 17:00:00 │
│ 102 │ sku_002 │      2000.00 │ orange │ 2020-06-01 16:00:00 │
│ 103 │ sku_003 │      3000.00 │ banana │ 2020-06-01 15:00:00 │
└─────┴─────────┴──────────────┴────────┴─────────────────────┘

聚合表

参考：https://blog.youkuaiyun.com/u012551524/article/details/112300519

create table test.t_order_amt2(
	id UInt32,
	sku_id String,
	create_time Datetime,
    total_amount AggregateFunction(sum, Float32),
    count_id AggregateFunction(count, Float32),
	uniq_goods AggregateFunction(uniq, String),
    max_amount AggregateFunction(max, Float32)
)engine=AggregatingMergeTree()
partition by toYYYYMMDD(create_time)
order by (id, sku_id);

insert into test.t_order_amt2 select 
101,'sku_001','2020-06-01 17:00:00',sumState(toFloat32(1000)),countState(toFloat32(1000)),uniqState('apple'),maxState(toFloat32(1000));
insert into test.t_order_amt2 select 
101,'sku_001','2020-06-01 17:00:00',sumState(toFloat32(2000)),countState(toFloat32(2000)),uniqState('orange'),maxState(toFloat32(2000));
insert into test.t_order_amt2 select 
101,'sku_001','2020-06-01 17:00:00',sumState(toFloat32(3000)),countState(toFloat32(3000)),uniqState('apple'),maxState(toFloat32(3000));

直接查询该表，结果聚合状态列因为是二进制数据，无法正常显示：

┌──id─┬─sku_id──┬─────────create_time─┬─total_amount─┬─count_id─┬─uniq_goods─┬─max_amount─┐
│ 101 │ sku_001 │ 2020-06-01 17:00:00 │ @▒@           │          │ ▒ި           │ zD         │
└─────┴─────────┴─────────────────────┴──────────────┴──────────┴────────────┴────────────┘
┌──id─┬─sku_id──┬─────────create_time─┬─total_amount─┬─count_id─┬─uniq_goods─┬─max_amount─┐
│ 101 │ sku_001 │ 2020-06-01 17:00:00 │ p▒@           │          │ ▒ި           │ ▒;E         │
└─────┴─────────┴─────────────────────┴──────────────┴──────────┴────────────┴────────────┘
┌──id─┬─sku_id──┬─────────create_time─┬─total_amount─┬─count_id─┬─uniq_goods─┬─max_amount─┐
│ 101 │ sku_001 │ 2020-06-01 17:00:00 │ @▒@           │          │ ▒o▒          │ ▒D          │
└─────┴─────────┴─────────────────────┴──────────────┴──────────┴────────────┴────────────┘

正确的查询方式如下：

select 
	id, 
	sku_id, 
	toStartOfDay(create_time) as day,
	sumMerge(total_amount) as total_amount,
	countMerge(count_id) as count_id,
	uniqMerge(uniq_goods) as uniq_goods,
	maxMerge(max_amount) as max_amount
from test.t_order_amt2
group by id, sku_id, day;

结果如下：

┌──id─┬─sku_id──┬─────────────────day─┬─total_amount─┬─count_id─┬─uniq_goods─┬─max_amount─┐
│ 101 │ sku_001 │ 2020-06-01 00:00:00 │         6000 │        3 │          2 │       3000 │
└─────┴─────────┴─────────────────────┴──────────────┴──────────┴────────────┴────────────┘

原始数据如下，可以看到聚合结果是正确的。

id	sku_id	create_time	total_amount	goods
101	‘sku_001’	‘2020-06-01 17:00:00’	1000	‘apple’
101	‘sku_001’	‘2020-06-01 17:00:00’	2000	‘orange’
101	‘sku_001’	‘2020-06-01 17:00:00’	3000	‘apple’

CollapsingMergeTree

特点

在数据块合并算法中添加了折叠行的逻辑。CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

案例实操

参考：https://blog.youkuaiyun.com/u010711495/article/details/112568753

建表并导入数据

在示例中，我们建一个员工薪资表，按照部分进行分区。针对每个员工，一开始我们插入一条薪资数据，且将sign赋值为1。之后由于薪资调整，我们需要更新每个员工数据。因此将前述薪资数据的sign赋值为-1，并重新插入一条薪资数据，将sign赋值为1。简要的说，就是每个员工的旧薪资数据凑成一对sign分别为1和-1的数据，之后可以相互抵消。而新插入的数据sign为1，单个出现才是有效的数据。

CREATE TABLE test.t_order_cmt
(
    work_id UInt32,
    name String,
    dept String,
    wage Decimal64(2),
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY work_id PARTITION BY dept;

INSERT INTO test.t_order_cmt VALUES (1, 'Alice', 'software', 10000.00, 1), (2, 'Bob', 'market', 13000.00, 1);
INSERT INTO test.t_order_cmt VALUES (1, 'Alice', 'software', 10000.00, -1),(1, 'Alice', 'software', 12000.00, 1);
INSERT INTO test.t_order_cmt VALUES (2, 'Bob', 'market', 13000.00, -1),(2, 'Bob', 'market', 15000.00, 1);

查询数据

查询数据得到：

 select * from test.t_order_cmt;
 
┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┐
│       2 │ Bob  │ market │ 13000.00 │   -1 │
│       2 │ Bob  │ market │ 15000.00 │    1 │
└─────────┴──────┴────────┴──────────┴──────┘
┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┐
│       2 │ Bob  │ market │ 13000.00 │    1 │
└─────────┴──────┴────────┴──────────┴──────┘
┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┐
│       1 │ Alice │ software │ 10000.00 │    1 │
└─────────┴───────┴──────────┴──────────┴──────┘
┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┐
│       1 │ Alice │ software │ 10000.00 │   -1 │
│       1 │ Alice │ software │ 12000.00 │    1 │
└─────────┴───────┴──────────┴──────────┴──────┘

当前数据尚未自动合并，因此所有的数据都显示了出来，我们换个查询方式看看：

SELECT 
	dept, 
	work_id, 
	name, 
	sum(wage * sign) as wage,
	sum(sign) as sign_
FROM test.t_order_cmt 
GROUP BY dept, work_id, name 
HAVING sum(sign) > 0;

结果如下，刚好是我们想要的最新数据。通过这种方式查询，避免了数据尚未合并导致的错误。

┌─dept─────┬─work_id─┬─name──┬─────wage─┬─sign_─┐
│ software │       1 │ Alice │ 12000.00 │     1 │
│ market   │       2 │ Bob   │ 15000.00 │     1 │
└──────────┴─────────┴───────┴──────────┴───────┘

当然啦，我们也可以采用如下方式查询得到最新结果，但是使用final非常的低效，在生产环境中应当避免使用这种方式。

SELECT * FROM test.t_order_cmt FINAL;

┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┐
│       1 │ Alice │ software │ 12000.00 │    1 │
└─────────┴───────┴──────────┴──────────┴──────┘
┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┐
│       2 │ Bob  │ market │ 15000.00 │    1 │
└─────────┴──────┴────────┴──────────┴──────┘

合并数据

数据在某个时间其实是会自动合并的，我们也可以手动执行合并来看看结果：

optimize table test.t_order_cmt final;

再次查询看看结果：

SELECT * FROM test.t_order_cmt;

┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┐
│       2 │ Bob  │ market │ 15000.00 │    1 │
└─────────┴──────┴────────┴──────────┴──────┘
┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┐
│       1 │ Alice │ software │ 12000.00 │    1 │
└─────────┴───────┴──────────┴──────────┴──────┘

改变数据写入顺序

打乱数据插入的顺序：

INSERT INTO test.t_order_cmt VALUES (3, 'Cherry', 'hardware', 13000.00, 1);
INSERT INTO test.t_order_cmt VALUES (3, 'Cherry', 'hardware', 10000.00, -1);
INSERT INTO test.t_order_cmt VALUES (3, 'Cherry', 'hardware', 10000.00, 1);

查询新插入的数据结果为：

┌─work_id─┬─name───┬─dept─────┬─────wage─┬─sign─┐
│       3 │ Cherry │ hardware │ 10000.00 │    1 │
└─────────┴────────┴──────────┴──────────┴──────┘
┌─work_id─┬─name───┬─dept─────┬─────wage─┬─sign─┐
│       3 │ Cherry │ hardware │ 13000.00 │    1 │
└─────────┴────────┴──────────┴──────────┴──────┘
┌─work_id─┬─name───┬─dept─────┬─────wage─┬─sign─┐
│       3 │ Cherry │ hardware │ 10000.00 │   -1 │
└─────────┴────────┴──────────┴──────────┴──────┘

再次执行合并数据 optimize table test.t_order_cmt final; 查询结果为：

┌─work_id─┬─name───┬─dept─────┬─────wage─┬─sign─┐
│       3 │ Cherry │ hardware │ 10000.00 │    1 │
└─────────┴────────┴──────────┴──────────┴──────┘

从结果得知，数据插入的顺序很重要，如果我们将最新一条数据先插入，随后插入了一条与之能成对(sign分别为1和-1)的数据，那么结果就是最新数据被抵消掉，而旧数据还留着。很显然这不是我们想要的结果。

此时可以采用VersionedCollapsingMergeTree引擎。

VersionedCollapsingMergeTree

特点

该表引擎与CollapsingMergeTree基本一样，通过一个新增的version列来控制版本，从而实现数据插入乱序，而仍然可以正确进行数据折叠。适用与数据变化较快的场景。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

案例实操

建表并导入数据

CREATE TABLE test.t_order_vcmt
(
    work_id UInt32,
    name String,
    dept String,
    wage Decimal64(2),
    sign Int8,
    version Int8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY work_id PARTITION BY dept;

INSERT INTO test.t_order_vcmt VALUES (1, 'Alice', 'software', 10000.00, 1, 1), (2, 'Bob', 'market', 13000.00, 1, 1);
INSERT INTO test.t_order_vcmt VALUES (1, 'Alice', 'software', 10000.00, -1, 1),(1, 'Alice', 'software', 12000.00, 1, 2);
INSERT INTO test.t_order_vcmt VALUES (2, 'Bob', 'market', 13000.00, -1, 1),(2, 'Bob', 'market', 15000.00, 1, 2);

查询数据

select * from test.t_order_vcmt;

┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┬─version─┐
│       1 │ Alice │ software │ 10000.00 │   -1 │       1 │
│       1 │ Alice │ software │ 12000.00 │    1 │       2 │
└─────────┴───────┴──────────┴──────────┴──────┴─────────┘
┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┬─version─┐
│       2 │ Bob  │ market │ 13000.00 │   -1 │       1 │
│       2 │ Bob  │ market │ 15000.00 │    1 │       2 │
└─────────┴──────┴────────┴──────────┴──────┴─────────┘
┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┬─version─┐
│       2 │ Bob  │ market │ 13000.00 │    1 │       1 │
└─────────┴──────┴────────┴──────────┴──────┴─────────┘
┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┬─version─┐
│       1 │ Alice │ software │ 10000.00 │    1 │       1 │
└─────────┴───────┴──────────┴──────────┴──────┴─────────┘

同样的，在数据不确定是否合并时，通常我们采用如下查询方式得到正确的结果：

--生产环境中推荐该方式
SELECT 
	dept, 
	work_id, 
	name, 
	sum(wage * sign) as wage,
	version,
	sum(sign) as sign_
FROM test.t_order_vcmt 
GROUP BY dept, work_id, name, version
HAVING sum(sign) > 0;

┌─dept─────┬─work_id─┬─name──┬─────wage─┬─version─┬─sign_─┐
│ software │       1 │ Alice │ 12000.00 │       2 │     1 │
│ market   │       2 │ Bob   │ 15000.00 │       2 │     1 │
└──────────┴─────────┴───────┴──────────┴─────────┴───────┘

合并数据

合并数据并查看结果：

optimize table test.t_order_vcmt final;

select * from test.t_order_vcmt;

┌─work_id─┬─name─┬─dept───┬─────wage─┬─sign─┬─version─┐
│       2 │ Bob  │ market │ 15000.00 │    1 │       2 │
└─────────┴──────┴────────┴──────────┴──────┴─────────┘
┌─work_id─┬─name──┬─dept─────┬─────wage─┬─sign─┬─version─┐
│       1 │ Alice │ software │ 12000.00 │    1 │       2 │
└─────────┴───────┴──────────┴──────────┴──────┴─────────┘