【无标题】clickhouse 物化视图 materialized view

最新推荐文章于 2024-11-27 08:00:00 发布

原创最新推荐文章于 2024-11-27 08:00:00 发布 · 259 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#big data #数据仓库 #大数据

clickhouse 专栏收录该内容

2 篇文章

订阅专栏

本文通过实战演示了如何在ClickHouse中创建明细表与物化视图，并实现数据的实时聚合。从创建表结构到插入数据，再到构建物化视图进行数据聚合展示，全过程详细介绍了ClickHouse在数据聚合方面的高效应用。

-- 创建一个明细表
use test1;
drop table if exists orders;
create table orders
(
    uid UInt64,
    money UInt64,
    ctime Date,
    Sign Int8
)
engine = MergeTree()
order by uid;

insert into orders values(1, 100, toDate(now()), 1);
insert into orders values(1, 100, toDate(now()), 1);
insert into orders values(1, 100, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);

select * from orders;

--创建物化视图(聚合同步)

drop view if exists orders_agg_view;
create materialized view orders_agg_view
engine = AggregatingMergeTree()
partition by toDate(ctime)
order by uid
populate
as select uid,
          ctime,
          sumState(money) as ms
from orders
group by uid, ctime;


desc orders_agg_view;

select uid, ctime, sumMerge(ms) from orders_agg_view group by uid, ctime;

--向明细表中插入数据,物化视图会同步数据 提前聚合

insert into orders values(1, 700, toDate(now()), 1);
--再次查询聚合数据
select uid, ctime, sumMerge(ms) from orders_agg_view group by uid, ctime;