关于clickhouse:用-ClickHouse-的-SummingMergeTree-实现准实时看板qbit

前言

• 本文测试环境 ClickHouse 版本为 22.3 LTS
• SummingMergeTree 官网文档：https://clickhouse.com/docs/e...

初始化

测试数据生成

• 创立库

  CREATE DATABASE qbit;

• 创立表

  CREATE TABLE download (  userid UInt32,  when DateTime,  size Float32) ENGINE = MergeTree PARTITION BY toYYYYMM(when)ORDER BY(userid, when);

• 插入数据

  INSERT INTO qbit.downloadSELECT  rand() % 5,  now() + number * 60 as when,  rand() % 100000000FROM system.numbersLIMIT 5000;

• 查看数据

  SELECT * FROM qbit.download LIMIT 10;

• 查看每天的下载量（扫描 10000 条数据）

  SELECT  userid,  toYYYYMMDD(when) AS day,  count() AS cnt,  sum(size) AS sizeFROM qbit.downloadGROUP BY  userid,  dayORDER BY cnt DESC

SummingMergeTree 准实时看板

形式一（view1）

• 创立视图 view1

  CREATE MATERIALIZED VIEW qbit.view1ENGINE = SummingMergeTree((cnt, size))ORDER BY (userid, day) POPULATE ASSELECT  userid,  toYYYYMMDD(when) AS day,  toInt64(1) AS cnt,  toFloat64(size) AS sizeFROM qbit.download

• 查问视图 view1（能够看到跟后面的查问后果是统一的）

  SELECT *FROM qbit.view1ORDER BY cnt DESCLIMIT 10

形式二（view2）

• 创立视图 view2

  CREATE MATERIALIZED VIEW qbit.view2ENGINE = SummingMergeTreePARTITION BY dayORDER BY (userid, day) POPULATE ASSELECT  userid,  toYYYYMMDD(when) AS day,  count() AS cnt,  sum(size) AS sizeFROM qbit.downloadGROUP BY  userid,  day

• 查问视图 view2（能够看到跟后面的查问后果是统一的）

  SELECT *FROM qbit.view2ORDER BY cnt DESCLIMIT 10

AggregatingMergeTree 准实时看板

• 创立视图（view3）

  CREATE MATERIALIZED VIEW qbit.view3ENGINE = AggregatingMergeTreePARTITION BY dayORDER BY (userid, day) POPULATE ASSELECT  userid,  toYYYYMMDD(when) AS day,  countState() AS cnt,  sumState(size) AS sizeFROM qbit.downloadGROUP BY  userid,  day

• 查问视图 view3（能够看到跟后面的查问后果是统一的）

  SELECT  userid,  day,  countMerge(cnt) AS cnt,  sumMerge(size) AS sizeFROM qbit.view3GROUP BY  userid,  dayORDER BY cnt DESCLIMIT 10

插入更新数据

本文出自 qbit snap