简单的demo

  1. create table if not exists tb_test
  2. (
  3.     id Int64,
  4.     datetime DateTime,
  5.     content Nullable(String),
  6.     value Nullable(Float64),
  7.     date Date
  8. )
  9. engine = MergeTree                  --使用mergeTree引擎，ch主要引擎
  10. partition by toYYYYMM(datetime)     --按照datetime这个字段的月进行分区
  11. order by id                         --按照id进行排序
  12. TTL datetime + INTERVAL 3 DAY ;     --三天过期
  14. --修改表中数据过期时间，到期后数据会在merge时被删除
  15. ALTER TABLE tb_test MODIFY TTL datetime + INTERVAL 1 DAY;
  17. --查询
  18. select * from tb_test order by id;
  20. --删除分区，可用于定时任务删除旧数据
  21. alter table tb_test drop partition '202005';
  23. --插入数据
  24. insert into tb_test values (5, '2020-02-29 12:38:37', 'abcde', 12.553, '2020-04-25');
  26. --修改数据，不推荐使用
  27. alter table tb_test update content = 'hello click' where id=52;
  29. --删除数据，不推荐使用
  30. alter table tb_test delete WHERE id=56;
  1. CREATE TABLE IF NOT EXISTS tb_test ( id Int64, datetime DateTime, content Nullable ( String ), VALUE Nullable ( Float64 ), date Date ) ENGINE = MergeTree PARTITION BY toYYYYMM ( datetime ) ORDER BY id TTL datetime + INTERVAL 3 DAY;
  3. create table if not exists  tb_stat ( regionId String, groupId String, IN INT, OUT INT, statDate DateTime ) ENGINE = SummingMergeTree PARTITION BY ( toYYYYMM ( statDate ), regionId ) ORDER BY( toStartOfHour ( statDate ), regionId, groupId );

高级用法

  1. -- 1.求和引擎SummingMergeTree 这种引擎可以自动聚合非主键数字列，可以用于事件统计
  2. --自动求和聚合表
  3. CREATE TABLE IF NOT EXISTS tb_stat
  4. (
  5.     regionId String,    --门店id
  6.     groupId String,     --统计组id
  7.     in int,             --进客流
  8.     out int,            --出客流
  9.     statDate DateTime   --统计时间
  10. )
  11. ENGINE = SummingMergeTree
  12. partition by (toYYYYMM(statDate), regionId)
  13. ORDER BY (toStartOfHour(statDate), regionId, groupId);
  15. insert into tb_stat values ('1232364', '111',  32, 2,  '2020-03-25 12:56:00');
  16. insert into tb_stat values ('1232364', '111',  34, 44, '2020-03-25 12:21:00');
  17. insert into tb_stat values ('1232364', '111',  54, 12, '2020-03-25 12:20:00');
  18. insert into tb_stat values ('1232364', '222',  45, 11, '2020-03-25 12:13:00');
  19. insert into tb_stat values ('1232364', '222',  32, 33, '2020-03-25 12:44:00');
  20. insert into tb_stat values ('1232364', '222',  12, 23, '2020-03-25 12:22:00');
  21. insert into tb_stat values ('1232364', '333',  54, 54, '2020-03-25 12:11:00');
  22. insert into tb_stat values ('1232364', '333',  22, 74, '2020-03-25 12:55:00');
  23. insert into tb_stat values ('1232364', '333',  12, 15, '2020-03-25 12:34:00');
  26. select toStartOfHour(statDate), regionId, groupId, sum(in), sum(out)from tb_stat group by toStartOfHour(statDate), regionId, groupId;
  27. -- 数据插入后，大概过1分钟，在此查询该表可以发现，只剩下3调数据：
  30. select * from tb_stat;
  32. 1232364 111 480 232 2020-03-25 04:56:00
  33. 1232364 222 356 268 2020-03-25 04:13:00
  34. 1232364 333 352 572 2020-03-25 04:11:00