clickhouse 视图和物化视图

塞上江南o

已于 2024-03-19 23:16:16 修改

阅读量1.9k

点赞数 1

分类专栏： ck 文章标签： clickhouse

于 2019-09-20 22:46:46 首次发布

本文链接：https://blog.csdn.net/qq_43192537/article/details/101082032

版权

ck 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

视图

视图只是封装的一层简单的查询代理

物化视图

1）物化视图拥有独立的存储
2）实时同步

写入ck为明细数据

1）创建订单明细表

-- 1、创建明细表
drop table tb_orders_detail_base;
create table tb_orders_detail_base
(
    uid   UInt32,
    money UInt64,
    ctime Date
) engine = MergeTree()
      partition by ctime
      order by uid;

-- 2、往明细表中插入数据
insert into tb_orders_detail_base
values (1, 100, toDate(now()));
insert into tb_orders_detail_base
values (1, 100, toDate(now()));
insert into tb_orders_detail_base
values (2, 200, toDate(now()));
insert into tb_orders_detail_base
values (2, 200, toDate(now()));

optimize table tb_orders_detail_base final;

select *
from tb_orders_detail_base;

在这里插入图片描述

2）创建物化视图（引擎指定为聚合引擎），并向物化视图中同步数据


-- 3、创建物化视图，并向物化视图中同步数据
drop table tb_orders_detail_view;
create materialized view tb_orders_detail_view
            engine = AggregatingMergeTree()
                partition by ctime
                order by uid
            populate
as
select uid,
       ctime,
       sumState(money) as sum_money
from tb_orders_detail_base
group by uid, ctime;


-- 4、查询物化视图
select uid,
       ctime,
       sumMerge(sum_money) as sum_money
from tb_orders_detail_view
group by uid, ctime;

在这里插入图片描述

3）查看同步聚合效果

-- 5、向明细表中插入明细数据
insert into tb_orders_detail_base
values (2, 200, toDate(now()));


-- 6、查询物化视图
select uid,
       ctime,
       sumMerge(sum_money) as sum_money
from tb_orders_detail_view
group by uid, ctime;

在这里插入图片描述

写入ck为聚合数据

-- 1、创建订单宽表
drop table if exists tb_orders_base;
create table tb_orders_base
(
    uid          UInt32,
    total_amount UInt64,
    dt           Date,
    ctime        Datetime
) engine = MergeTree()
      partition by toDate(dt)
      order by uid;

-- 2、往订单宽表中插入聚合数据
insert into tb_orders_base
values (1, 100, toDate(now()), now());
insert into tb_orders_base
values (1, 200, toDate(now()), dateAdd(now(), INTERVAL 10 SECOND));
insert into tb_orders_base
values (2, 400, toDate(now()), dateAdd(now(), INTERVAL 10 SECOND));
insert into tb_orders_base
values (2, 200, toDate(now()), now());

optimize table tb_orders_base final;

select *
from tb_orders_base;

在这里插入图片描述

-- 3、创建物化视图，并向物化视图中同步数据
drop table if exists tb_orders_view;
create materialized view tb_orders_view
            engine = AggregatingMergeTree()
                partition by toDate(dt)
                order by uid
            populate
as
select uid,
       dt,
       argMaxState(total_amount, ctime) as total_amount
from tb_orders_base
group by uid, dt;


show create table tb_orders_view;

-- 4、查询物化视图
select uid,
       dt,
       argMaxMerge(total_amount) as flinal_total_amount
from tb_orders_view
group by uid, dt;

在这里插入图片描述

-- 5、向订单宽表中插入聚合数据
insert into tb_orders_base
values (2, 801, toDate(now()),dateAdd(now(), INTERVAL 10 SECOND));


-- 6、查询物化视图
select uid,
       dt,
       argMaxMerge(total_amount) as flinal_total_amount
from tb_orders_view
group by uid, dt;