clickhouse表引擎使用

需要你跟着这些名词，查询这些表引擎的特性，在实际工作中根据需要选择合适的引擎进行数据收集和查询

 ads：

关注以下公众号查看更多文章

目录

mysql

TinyLog

ReplaceingMergeTree

CollapsingMergeTree

VersionedCollapsingMergeTree

SummingMergeTree

AggregatingMergeTree

---

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;