GaussDB(DWS)实践系列-分区表存储时间管理实现

举报
四叶草 发表于 2021/08/18 10:34:37 2021/08/18
【摘要】 在项目交付中,客户经常会提出一些关于分区表TTL管理的需求,例如保留最近30天的分区数据,并实现分区自增和过期分区删除的场景,当前GaussDB(DWS)不支持,针对该场景可参考本文方法实现GaussDB(DWS)分区表的TTL管理,针对分区字段为date和timestamp类型,通过DGC或其他周期性调度任务执行管理函数,实现增加分区和删除lifecycle有效期外的分区,实际使用过程中可按照注

GaussDB(DWS)分区表存储时间管理实现

        在项目交付中,客户经常会提出一些关于分区表TTL管理的需求,例如保留最近30天的分区数据,并实现分区自增和过期分区删除的场景,当前GaussDB(DWS)不支持,针对该场景可参考本文方法实现GaussDB(DWS)分区表的TTL管理,针对分区字段为date和timestamp类型,通过DGC或其他周期性调度任务执行管理函数,实现增加分区和删除lifecycle有效期外的分区,实际使用过程中可按照注释内容按需调整。

一、   方案介绍

       创建分区策略管理表partition_manage_policies,保存分区表信息及数据有效天数,其中schemaname表示模式名,tablename表示表名,lifecycle表示该表保留数据的天数。

----创建分区策略管理表。

create table partition_manage_policies(

schemaname varchar,

tablename varchar,

lifecycle int

)distribute by replication ;

----查看当前数据库中的分区表

select n.nspname as schemaname,c.relname as tablename from pg_class c

left join pg_namespace n on n.oid = c.relnamespace

where c.parttype = 'p';

----给某张指定分区表插入分区策略,例如public. day_manager_test保留30天的数据。

insert into partition_manage_policies values('public','day_manager_test',30);

insert into partition_manage_policies values('public','hour_manager_test',30);

select * from partition_manage_policies;

----创建测试分区表

----1)按日分区测试表

CREATE TABLE public.day_manager_test(

  guid varchar ,

  user_id varchar ,

  name varchar ,

  dt timestamp)distribute by hash(guid)

  partition by range(dt)

 (partition p1 start(timestamp '2021-07-10') end (timestamp '2021-08-11') every(interval '1 days'));

----2)按小时分区测试表

CREATE TABLE public.hour_manager_test(

 guid varchar,

 user_id varchar,

 name varchar,

dt timestamp) with(orientation = column) distribute by hash(guid)

 partition by range(dt)

 (partition p1 start(timestamp '2021-07-10 01:00:00') end (timestamp '2021-08-11 00:00:00') every(interval '1 hours') );

--查看指定表(public. day_manager_test)的分区信息,以及分区的上边界。

select n.nspname as schemaname,c.relname as tablename,

p.relname as partname,p.boundaries::varchar as boundaries

from PG_PARTITION p

left join pg_class c on p.parentid = c.oid

left join pg_namespace n on c.relnamespace = n.oid 

where c.relname = 'day_manager_test'

and n.nspname = 'public'

and p.relname <> 'day_manager_test'

order by p.boundaries desc;

 

日分区管理函数及调用方法如下,小时分区管理函数及调用方法类似,支持通过gtmdate一次添加并清理多个日期分区:

---按日分区管理函数,根据gtmdate新增当前日期及之前的分区。例如gtmdate'2021-08-17',会新建分区保存当天数据,并且会补充gtmdate前缺省的分区。

CREATE OR REPLACE FUNCTION partition_day_manage(schema_name varchar, table_name varchar, gmtdate varchar) RETURNS void AS

$$

DECLARE

r partition_manage_policies%rowtype;

lifecycle int;

sql_str varchar;

drop_date varchar;

drop_partition_name varchar;

add_partition_name varchar;

partition_max_num int;

partition_max_value timestamp; 

partition_perfix varchar;

loop_num int;

err_message varchar;

 

BEGIN

SELECT * into r FROM public.partition_manage_policies where schemaname = schema_name and tablename = table_name;        

drop_date := gmtdate::date - r.lifecycle + 1;

--按照lifecycle信息计算删除日期并打印

dbms_output.put ('------ Delete partition ');

dbms_output.put(drop_date);

dbms_output.put_line(' and earlier! ------');

 

dbms_output.put_line('------ Prints the delete partition command! ------');

--打印删除分区的命令

--删除所有小于或等于drop_date的日期的分区

for drop_partition_name in select p.relname from PG_PARTITION p left join pg_class c on p.parentid = c.oid left join pg_namespace n on c.relnamespace = n.oid  where c.relname = table_name and n.nspname = schema_name and p.relname <> table_name and substr(p.boundaries::varchar,instr(p.boundaries::varchar,'20', 1),10) <= drop_date loop

    sql_str := 'alter table '||schema_name||'.'||table_name||' drop partition '||drop_partition_name;

    dbms_output.put_line(sql_str); --打印删除分区sql

         EXECUTE IMMEDIATE sql_str; --执行删除分区

end loop;

--取当前分区的最大value值、分区名的最大值、分区名称的前缀,其中instr中的'20'代表'2020'中的20

select

substr(p.boundaries::varchar,instr(p.boundaries::varchar,'20', 1),10),substr(p.relname,instr(p.relname,'_',1)+1),substr(p.relname,0,instr(p.relname,'_',1)) into partition_max_value,partition_max_num,partition_perfix from PG_PARTITION p left join pg_class c on p.parentid = c.oid left join pg_namespace n on c.relnamespace = n.oid 

where c.relname = table_name

and n.nspname = schema_name

and p.relname <> table_name

order by p.boundaries desc limit 1;

 

--计算需要生成多少个分区,当输入的日期小于最大分区的日期时,该值为负数,则如下for循环不会执行

loop_num = substr((gmtdate::timestamp - partition_max_value::timestamp)::varchar,0,instr((gmtdate::timestamp - partition_max_value::timestamp)::varchar,' ',1)) + 1;

 

--自当前最大分区值开始添加分区

--打印添加分区的sql

dbms_output.put_line('------ Prints the add partition command! ------');

for i in 1..loop_num loop

    partition_max_num = partition_max_num + 1; --分区名为自增序列

    add_partition_name = partition_perfix||partition_max_num; --拼接分区名称

    partition_max_value = partition_max_value + interval '1 day'; --计算分区值,每个分区间隔1

    sql_str := 'alter table '||schema_name||'.'||table_name||' add partition '||add_partition_name||' values less than (''' ||partition_max_value||''')';

         dbms_output.put_line(sql_str);

         EXECUTE IMMEDIATE sql_str; --执行添加分区sql

end loop;

 

    RETURN;

--如果分区表策略管理表中没有表信息,报错提示

   EXCEPTION when NO_DATA_FOUND THEN

    err_message := 'table '||schema_name||'.'||table_name || ' not found in public.partition_manage_policies.';

    RAISE EXCEPTION '%', err_message; 

 

END $$ LANGUAGE 'plpgsql' ;

 

二、   场景测试

调用分区管理函数,并根据实际需求传入schema_nametable_name以及日期信息进行场景测试,以日分区表管理为例,小时分区管理相同。

1、测试场景一,输入当前不存在的日期分区,例如current_date’ 2021-08-17’

call partition_day_manage('public','day_manager_test',current_date);


提供日志打印如下信息:

(1)       删除了’2021-07-19’及之前的分区。

(2)       执行的删除分区表语句。

(3)       执行的添加分区表语句。

--查看更新后的分区信息,以及分区的上边界。

2、测试场景二,输入当前已存在的日期分区

call partition_day_manage('public','day_manager_test','2021-08-13');

--不执行任何删除和添加分区命令

3、测试场景三,输入分区策略管理表partition_manage_policies不存在的分区信息。

call partition_day_manage('public','day_manager_test_notexist','2021-08-29');

--报错,分区策略管理表中不存在该表

 

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。