合并数家族

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 
) 
ENGINE = MergeTree() 
ORDER BY expr 
[PARTITION BY expr] 
[PRIMARY KEY expr] 
[SAMPLE BY expr] 
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...] 
[SETTINGS name=value, ...]

 说明：

• 分区键：PARTITION BY 指定分区键。数据存储时每个分区会有一个单独的目录。所以查询时，如果查询条件带有分区键，可以提高查询效率。
• 排序键：ORDER BY指定排序键，为必选参数：指定分区内的数据按照哪些字段排序进行有序保存。
• 主键：PRIMARY KEY指定主键，主键不要求数据的唯一性，可设置多个联合主键，更应该称之为主键索引。
• 一般而言，主键与排序键相同；也可指定与排序键不同的主键。

主键与排序键不同时：
    主键必须为排序键的前缀字段。
    排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。
    改场景多适用于： SummingMergeTree 和 AggregatingMergeTree 引擎。

• GRANULARITY :索引粒度

                默认参数：SETTINGS index_granularity = 8192，稀疏索引中索引对应数据范围。       

                设置二级索引时，也可指定索引粒度：

        INDEX xxxIndex xxx TYPE minmax GRANULARITY 10

• 数据老化

            可以为某个列字段或者整张表设置TTL
            当时间达到时，若列字段级别的TTL则会删除这一列的数据；
            若表级别的TTL则会删除对应记录数据；
            若同时设置了列级别的和表级别的TTL则以先到期的为准 

        列级老化

TTL time_column + interval 1 DAY

当前列存活至time_column时间的1天之后

    time_column :时间列
    DAY：单位，支持：second，minute，hour，day，week，month，quarter，year

说明：列式TTL不能用于主键

                表级老化

TTL createtime + INTERVAL 1 MINUTE 

样例：

建表：
CREATE TABLE ttl_test
(
    id UInt64,
    create_time DateTime,
    test1 String ,
    test2 UInt8
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id
TTL create_time + interval 1 MINUTE;

插入数据：
insert into ttl_test values
(1,now(),'111','111'),
(2,now(),'222','222'),
(3,now(),'333','333'),
(4,now(),'444','444'),
(5,now(),'555','555');



1分钟后再去查询，数据已被老化

                表级老化策略

	DELETE				移除过期的行（默认）
	TO DISK 'xxx'		将数据片段移动到指定的磁盘
	TO VOLUME 'xxx'     将数据片段移动到指定的卷
    Group by            聚合过期数据   

• 数据采样

                建表时指定采样规则

engine = MergeTree PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192;

                查询时可以指定采样效率

datasets> select UserID from hits_v1
[2022-01-28 18:50:42] 500 rows retrieved starting from 1 in 9 s 444 ms (execution: 9 s 415 ms, fetching: 29 ms)
datasets> select UserID from hits_v1 SAMPLE 0.1
[2022-01-28 18:51:44] 500 rows retrieved starting from 1 in 706 ms (execution: 681 ms, fetching: 25 ms)

综述

        根据合并规则的不同，产生了MergeTree的衍生引擎：

ReplacingMergeTree：    合并时，会删除相同的重复条目 
    判断重复的标准:order by 指定的排序键
    指定引擎时，可选择一个字段作为版本字段
    去重限定在一个分区中
    重复数据只保留版本最大的条目，版本相同或者无版本指定时以插入时间为准保留最新数据。
    同一批次插入的数据可保证直接去重，多次插入，须手动合并方能保证去重。   
SummingMergeTree：      合并时，聚合操作：以 SummingMergeTree（）中指定的列作为汇总数据列
    判断重复的标准:order by 指定的排序键，以此作为聚合维度列
    指定引擎时，须指定聚合字段，可指定多个，须为数字
    操作限定在一个分区中
    重复数据对聚合字段进行汇聚处理，其余字段保留第一次插入的数据。
    同一批次插入的数据可保证直接去重，多次插入，须手动合并方能保证去重。 
AggregatingMergeTree：  合并时，执行定制化逻辑

ReplacingMergeTree操练
--建表语句
create table t_replace_test
(
    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);

--首次插入重复数据（order by相同）
insert into t_replace_test values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(101,'sku_001',2000.00,'2020-06-01 13:00:00');

--再次插入重复数据（order by相同）
insert into t_replace_test values
(101,'sku_001',3000.00,'2020-06-01 14:00:00') ,
(101,'sku_001',4000.00,'2020-06-01 15:00:00');
--直查
select * from t_replace_test;
--合并后，再查寻
optimize table t_replace_test;
select * from t_replace_test;

SummingMergeTree操练
--建表语句
create table t_sum
(
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_sum values
(101,'sku_001',1000.00,'2020-06-01 10:00:00'),
(101,'sku_001',2000.00,'2020-06-01 11:00:00');
select * from t_sum;
--再次插入数据
insert into t_sum values
(101,'sku_001',3000.00,'2020-06-01 12:00:00'),
(101,'sku_001',4000.00,'2020-06-01 13:00:00');
select * from t_sum;
--手动合并再次查询
optimize table t_sum;
select * from t_sum;

        支持数据副本：

Replicated*MergeTree