big data olap database nosql clickhouse冷热数据分层配置

clikhouse冷热数据分层方案

文章目录

clikhouse冷热数据分层方案简介一、配置更改二、实际测试三、数据过期方案

简介

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

一、配置更改

在 config.xml 中加入如下配置 注意： hot采用default后，会自动匹配

路径，21.X版本后支持hdfs存储方式。

storage_policy在建表时位置设置则会采用默认（default）形式，也可以自己指定：建表语句中加入SETTINGS storage_policy =‘XXX’。

{{ clickhouse_cold_data_path }}

default

429496729600

disk2

0.1

二、实际测试

--创建本地表

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 。

推荐链接

 您阅读本篇文章共花了：