关于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.download
  SELECT
    rand() % 5,
    now() + number * 60 as when,
    rand() % 100000000
  FROM system.numbers
  LIMIT 5000;

• 查看数据

  SELECT * FROM qbit.download LIMIT 10;

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

  SELECT
    userid,
    toYYYYMMDD(when) AS day,
    count() AS cnt,
    sum(size) AS size
  FROM qbit.download
  GROUP BY
    userid,
    day
  ORDER BY cnt DESC

SummingMergeTree 准实时看板

形式一（view1）

• 创立视图 view1

  CREATE MATERIALIZED VIEW qbit.view1
  ENGINE = SummingMergeTree((cnt, size))
  ORDER BY (userid, day) POPULATE AS
  SELECT
    userid,
    toYYYYMMDD(when) AS day,
    toInt64(1) AS cnt,
    toFloat64(size) AS size
  FROM qbit.download

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

  SELECT *
  FROM qbit.view1
  ORDER BY cnt DESC
  LIMIT 10

形式二（view2）

• 创立视图 view2

  CREATE MATERIALIZED VIEW qbit.view2
  ENGINE = SummingMergeTree
  PARTITION BY day
  ORDER BY (userid, day) POPULATE AS
  SELECT
    userid,
    toYYYYMMDD(when) AS day,
    count() AS cnt,
    sum(size) AS size
  FROM qbit.download
  GROUP BY
    userid,
    day

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

  SELECT *
  FROM qbit.view2
  ORDER BY cnt DESC
  LIMIT 10

AggregatingMergeTree 准实时看板

• 创立视图（view3）

  CREATE MATERIALIZED VIEW qbit.view3
  ENGINE = AggregatingMergeTree
  PARTITION BY day
  ORDER BY (userid, day) POPULATE AS
  SELECT
    userid,
    toYYYYMMDD(when) AS day,
    countState() AS cnt,
    sumState(size) AS size
  FROM qbit.download
  GROUP BY
    userid,
    day

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

  SELECT
    userid,
    day,
    countMerge(cnt) AS cnt,
    sumMerge(size) AS size
  FROM qbit.view3
  GROUP BY
    userid,
    day
  ORDER BY cnt DESC
  LIMIT 10

插入更新数据

本文出自 qbit snap