clickhouse冷热数据分层配置

clikhouse冷热数据分层方案

---

简介

TTL策略可以结合业务特点，将数据生命周期与冷热数据存储关联起来。实现既保存历史数据，又能够降低存储成本的效果。比如将最近90天的高频查询数据放置在热数据存储中，而90天之前的低频查询数据自动转移到冷数据存储中

一、配置更改

在 config.xml 中加入如下配置
注意：
hot采用default后，会自动匹配 路径，21.X版本后支持hdfs存储方式。
storage_policy在建表时位置设置则会采用默认（default）形式，也可以自己指定：建表语句中加入SETTINGS storage_policy =‘XXX’。

<storage_configuration>
        <disks>
           <!-- <disk1>
                <path>/home/data/clickhouse/</path>
            </disk1>
           -->
            <disk2>
                <path>{{ clickhouse_cold_data_path }}</path>
            </disk2>
        </disks>
        <policies>
            <default>
                <volumes>
                    <hot>
                        <disk>default</disk>
                        <max_data_part_size_bytes>429496729600</max_data_part_size_bytes>
                    </hot>
                    <cold>
                        <disk>disk2</disk>
                    </cold>
                </volumes>
                <move_factor>0.1</move_factor>
            </default>
        </policies>
    </storage_configuration>

二、实际测试

--创建本地表
CREATE TABLE 
default.ttl_test_tbl on cluster test_ck_cluster
(
    `id` Int32,
    `name` String,
    `dateTime` DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/ttl_test_tbl', '{replica}')
PARTITION BY dateTime
ORDER BY id
TTL dateTime + INTERVAL 20 MINUTE TO VOLUME 'cold';

--创建分布式表
create table dis_ttl_test_tbl on cluster test_ck_cluster(`id` Int32,`name` String,`dateTime` DateTime) engine = Distributed(test_ck_cluster,default,ttl_test_tbl,rand());

--查看policy
SELECT 
    policy_name,
    volume_name,
    volume_priority,
    disks,
    formatReadableSize(max_data_part_size) AS max_data_part_size,
    move_factor
FROM system.storage_policies

┌─policy_name─┬─volume_name─┬─volume_priority─┬─disks───────┬─max_data_part_size─┬─move_factor─┐
│ default     │ hot         │               1 │ ['default'] │ 100.00 B           │         0.1 │
│ default     │ cold        │               2 │ ['disk2']   │ 0.00 B             │         0.1 │
└─────────────┴─────────────┴─────────────────┴─────────────┴────────────────────┴─────────────┘

--查看disk
SELECT 
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    formatReadableSize(keep_free_space) AS reserved
FROM system.disks

┌─name────┬─path───────────────────┬─free───────┬─total──────┬─reserved─┐
│ default │ /home/data/clickhouse/ │ 423.97 GiB │ 446.77 GiB │ 0.00 B   │
│ disk2   │ /clickhouse/           │ 43.78 GiB  │ 49.98 GiB  │ 0.00 B   │
└─────────┴────────────────────────┴────────────┴────────────┴──────────┘

--数据插入

insert into dis_ttl_test_tbl values(1,'zhangsan','2021-12-31 15:00:00'),(2,'lisi','2021-12-31 15:00:00'),(3,'wangwu','2021-12-31 15:00:00'),(4,'aaaaa','2021-12-31 14:00:00'),(5,'bbbbb','2021-12-31 14:00:00'),(6,'ccccc','2021-12-31 14:00:00'),(7,'ddddd','2021-12-31 14:00:00'),(8,'eeeee','2021-12-31 14:00:00'),(9,'fffff','2021-12-31 14:00:00'),(10,'ggggg','2021-12-31 14:00:00'),(11,'hhhhh','2021-12-31 14:00:00'),(12,'iiiii','2021-12-31 14:00:00'),(13,'jjjjj','2021-12-31 14:00:00'),(14,'kkkkk','2021-12-31 14:00:00'),(15,'lllll','2021-12-31 14:00:00'),(16,'mmmmm','2021-12-31 14:00:00'),(17,'nnnnn','2021-12-31 14:00:00'),(18,'ooooo','2021-12-31 14:00:00'),(19,'lllllllllllll','2022-01-04 09:56:00');

--数据验证，在分片1 测试冷热数据
SELECT count(*)
FROM ttl_test_tbl

┌─count()─┐
│      12 │
└─────────┘
--冷数据统计
[root@ansible ~]# cat /clickhouse_cold/data/default/ttl_test_tbl/1640930400_0_0_0/count.txt
9[root@ansible ~]# cat /clickhouse_cold/data/default/ttl_test_tbl/1640934000_0_0_0/count.txt
2[root@ansible ~]# cat /clickhouse_cold/data/default/ttl_test_tbl/1641261360_0_0_0/count.txt
1
9+2+1 = 12条
--热数据统计（无数据，因设定数据过期时间为datatime20分钟有效时间，以上所有数据均已过期，数据存于冷数据盘）
[root@ansible ~]# cat /home/data/clickhouse/data/default/ttl_test_tbl/
detached/           format_version.txt

三、数据过期方案

数据过期方案

• 列 TTL
  当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中删除此列。
  创建表时指定 TTL：

  CREATE TABLE example_table
  (
      d DateTime,
      a Int TTL d + INTERVAL 1 MONTH,
      b Int TTL d + INTERVAL 1 MONTH,
      c String
  )
  ENGINE = MergeTree
  PARTITION BY toYYYYMM(d)
  ORDER BY d;
  为表中已存在的列字段添加 TTL
  
  ALTER TABLE example_table
      MODIFY COLUMN
      c String TTL d + INTERVAL 1 DAY;
  修改列字段的 TTL
  
  ALTER TABLE example_table
      MODIFY COLUMN
      c String TTL d + INTERVAL 1 MONTH;
  

• 表 TTL
  表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。对于数据片段的转移特性，必须所有的行都满足转移条件。

TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'] ...
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]

TTL 规则的类型紧跟在每个 TTL 表达式后面，它会影响满足表达式时（到达指定时间时）应当执行的操作：

• DELETE - 删除过期的行（默认操作）;
• TO DISK ‘aaa’ - 将数据片段移动到磁盘 aaa;
• TO VOLUME ‘bbb’ - 将数据片段移动到卷 bbb.
• GROUP BY - 聚合过期的行
  使用WHERE从句，您可以指定哪些过期的行会被删除或聚合(不适用于移动)。GROUP BY表达式必须是表主键的前缀。如果某列不是GROUP BY表达式的一部分，也没有在SET从句显示引用，结果行中相应列的值是随机的(就好像使用了any函数)。
  创建时指定 TTL

-- 创建时指定 TTL

CREATE TABLE ttl_test_tbl
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';
    
-- 修改表的 TTL

ALTER TABLE example_table
    MODIFY TTL d + INTERVAL 1 DAY ;

-- 创建一张表，设置一个月后数据过期，这些过期的行中日期为星期一的删除：

CREATE TABLE table_with_where
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH DELETE WHERE toDayOfWeek(d) = 1;

--创建一张表，设置过期的列会被聚合。列x包含每组行中的最大值，y为最小值，d为可能任意值。

CREATE TABLE table_for_aggregation
(
    d DateTime,
    k1 Int,
    k2 Int,
    x Int,
    y Int
)
ENGINE = MergeTree
ORDER BY (k1, k2)
TTL d + INTERVAL 1 MONTH GROUP BY k1, k2 SET x = max(x), y = min(y);

删除数据
ClickHouse 在数据片段合并时会删除掉过期的数据。
当ClickHouse发现数据过期时, 它将会执行一个计划外的合并。要控制这类合并的频率,可以设置 merge_with_ttl_timeout(如建表语句中加入SETTINGS merge_with_ttl_timeout = 60 单位秒)。如果该值被设置的太低, 它将引发大量计划外的合并，这可能会消耗大量资源。
如果在合并的过程中执行 SELECT 查询, 则可能会得到过期的数据。为了避免这种情况，可以在 SELECT 之前使用 OPTIMIZE 。