需要你跟着这些名词,查询这些表引擎的特性,在实际工作中根据需要选择合适的引擎进行数据收集和查询
ads:
关注以下公众号查看更多文章
目录
mysql
create database test_mysql_maindb
engine = MySQL('192.168.31.103:3306', 'maindb', 'root', 'loveclub123');
TinyLog
create table test_tinyLog (
id UInt16,
name String,
birthday Date
) engine=TinyLog;
ReplaceingMergeTree
create table t_r_mt2 (
id Int32,
name String,
age UInt8,
city String
)engine=ReplacingMergeTree(age)
partition by city
order by id;
CollapsingMergeTree
create table t_c_mt (
id Int32,
name String,
age UInt8,
city String,
sign Int8
)engine = CollapsingMergeTree(sign)
partition by city
order by (id);
VersionedCollapsingMergeTree
create table t_v_mt (
id Int8,
name String,
age UInt8,
city String,
sign Int8,
cdate Date
)engine=VersionedCollapsingMergeTree(sign, cdate)
partition by city
primary key(id)
order by (id, age);
SummingMergeTree
create table tb_sum(
uid Int8,
name String,
aget UInt8,
oid String,
money Float64,
ctime Date
)engine=SummingMergeTree(money)
partition by toYYYYMM(ctime)
order by (uid, name);
insert into tb_sum values (1, 'zss', 23, 'o001', 100, '2021-01-02'),
(1, 'zss', 23, 'o002', 200, '2021-01-01'),
(1, 'zss', 23, 'o003', 400, '2021-01-03'),
(2, 'lss', 33, 'o004', 99, '2021-01-04');
AggregatingMergeTree
使用物化视图的方式,从源表生成物化视图,再从物化视图进行查询
create table agg_source(
id Int8,
name String,
city String,
money Float64
)engine=MergeTree()
partition by city
order by id;
insert into agg_source values
(1, 'zss', 'BJ', 100),
(2, 'bss', 'NJ', 100),
(3, 'css', 'NJ', 100),
(4, 'dss', 'BJ', 100);
insert into agg_source values
(5, 'ess', 'BJ', 100),
(6, 'fss', 'NJ', 100),
(7, 'gss', 'NJ', 100),
(8, 'hss', 'BJ', 100);
create materialized view agg_view_m
engine = AggregatingMergeTree()
partition by city
order by city
populate
as
select city, uniqState(name) name, sumState(money) cost
from agg_source group by city;
select city, sumMerge(cost), uniqMerge(name)
from agg_view_m group by city;