GreenPlum--分区维护需求（1）

最新推荐文章于 2021-07-10 08:37:51 发布

微笑、只是一个表情

最新推荐文章于 2021-07-10 08:37:51 发布

阅读量280

点赞数

分类专栏： # GreenPlum

本文链接：https://blog.csdn.net/weixin_43120559/article/details/105736273

版权

GreenPlum 专栏收录该内容

27 篇文章 3 订阅

订阅专栏

通过编写存储过程，完成如下功能：
1. 对于5年以前（以当年1月1日往前推导）的数据，进入历史分区（单一分区）。
2. 对于1年以上，5年以内的数据，使用年分区。
3. 1个月以上，1年以内的数据，按月分区。
4. 1个月以内的数据，按天分区。

-- Table: daw.employee

DROP TABLE daw.employee;

CREATE TABLE daw.employee
(
  id integer,
  name character varying(20),
  crt_date date,
  amount integer
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (id)
PARTITION BY RANGE(crt_date) 
          (
          PARTITION pn_five_years_ago  END ('2015-01-01'::date) EXCLUSIVE WITH (appendonly=true, compresstype=zlib, compresslevel=5), 
          PARTITION pn_2015 START ('2015-01-01'::date) INCLUSIVE END ('2016-01-01'::date) EXCLUSIVE , 
          PARTITION pn_2016 START ('2016-01-01'::date) INCLUSIVE END ('2017-01-01'::date) EXCLUSIVE , 
          PARTITION pn_2017 START ('2017-01-01'::date) INCLUSIVE END ('2018-01-01'::date) EXCLUSIVE , 
          PARTITION pn_2018 START ('2018-01-01'::date) INCLUSIVE END ('2019-01-01'::date) EXCLUSIVE , 
          PARTITION pn_2019 START ('2019-01-01'::date) INCLUSIVE END ('2020-01-01'::date) EXCLUSIVE ,          
          PARTITION pn_202001 START ('2020-01-01'::date) INCLUSIVE END ('2020-02-01'::date) EXCLUSIVE ,
          PARTITION pn_202002 START ('2020-02-01'::date) INCLUSIVE END ('2020-03-01'::date) EXCLUSIVE ,
          PARTITION pn_202003 START ('2020-03-01'::date) INCLUSIVE END ('2020-04-01'::date) EXCLUSIVE ,
          PARTITION pn_20200401 START ('2020-04-01'::date) INCLUSIVE END ('2020-04-02'::date) EXCLUSIVE ,
          PARTITION pn_20200402 START ('2020-04-02'::date) INCLUSIVE END ('2020-04-03'::date) EXCLUSIVE ,
          PARTITION pn_20200403 START ('2020-04-03'::date) INCLUSIVE END ('2020-04-04'::date) EXCLUSIVE ,
          PARTITION pn_20200404 START ('2020-04-04'::date) INCLUSIVE END ('2020-04-05'::date) EXCLUSIVE ,
          PARTITION pn_20200405 START ('2020-04-05'::date) INCLUSIVE END ('2020-04-06'::date) EXCLUSIVE ,
          PARTITION pn_20200406 START ('2020-04-06'::date) INCLUSIVE END ('2020-04-07'::date) EXCLUSIVE ,
          PARTITION pn_20200407 START ('2020-04-07'::date) INCLUSIVE END ('2020-04-08'::date) EXCLUSIVE ,
          PARTITION pn_20200408 START ('2020-04-08'::date) INCLUSIVE END ('2020-04-09'::date) EXCLUSIVE ,
          PARTITION pn_20200409 START ('2020-04-09'::date) INCLUSIVE END ('2020-04-10'::date) EXCLUSIVE ,
          PARTITION pn_20200410 START ('2020-04-10'::date) INCLUSIVE END ('2020-04-11'::date) EXCLUSIVE ,
          PARTITION pn_20200411 START ('2020-04-11'::date) INCLUSIVE END ('2020-04-12'::date) EXCLUSIVE ,
          PARTITION pn_20200412 START ('2020-04-12'::date) INCLUSIVE END ('2020-04-13'::date) EXCLUSIVE ,
          PARTITION pn_20200413 START ('2020-04-13'::date) INCLUSIVE END ('2020-04-14'::date) EXCLUSIVE ,
          PARTITION pn_20200414 START ('2020-04-14'::date) INCLUSIVE END ('2020-04-15'::date) EXCLUSIVE ,
          PARTITION pn_20200415 START ('2020-04-15'::date) INCLUSIVE END ('2020-04-16'::date) EXCLUSIVE ,
          PARTITION pn_20200416 START ('2020-04-16'::date) INCLUSIVE END ('2020-04-17'::date) EXCLUSIVE ,
          PARTITION pn_20200417 START ('2020-04-17'::date) INCLUSIVE END ('2020-04-18'::date) EXCLUSIVE ,
          PARTITION pn_20200418 START ('2020-04-18'::date) INCLUSIVE END ('2020-04-19'::date) EXCLUSIVE ,
          PARTITION pn_20200419 START ('2020-04-19'::date) INCLUSIVE END ('2020-04-20'::date) EXCLUSIVE ,
          PARTITION pn_20200420 START ('2020-04-20'::date) INCLUSIVE END ('2020-04-21'::date) EXCLUSIVE ,
          PARTITION pn_20200421 START ('2020-04-21'::date) INCLUSIVE END ('2020-04-22'::date) EXCLUSIVE ,
          PARTITION pn_20200422 START ('2020-04-22'::date) INCLUSIVE END ('2020-04-23'::date) EXCLUSIVE ,
          PARTITION pn_20200423 START ('2020-04-23'::date) INCLUSIVE END ('2020-04-24'::date) EXCLUSIVE ,
          PARTITION pn_20200424 START ('2020-04-24'::date) INCLUSIVE END ('2020-04-25'::date) EXCLUSIVE ,
          PARTITION pn_20200425 START ('2020-04-25'::date) INCLUSIVE END ('2020-04-26'::date) EXCLUSIVE ,
          PARTITION pn_20200426 START ('2020-04-26'::date) INCLUSIVE END ('2020-04-27'::date) EXCLUSIVE ,
          PARTITION pn_20200427 START ('2020-04-27'::date) INCLUSIVE END ('2020-04-28'::date) EXCLUSIVE ,
          PARTITION pn_20200428 START ('2020-04-28'::date) INCLUSIVE END ('2020-04-29'::date) EXCLUSIVE ,
          PARTITION pn_20200429 START ('2020-04-29'::date) INCLUSIVE END ('2020-04-30'::date) EXCLUSIVE ,
          PARTITION pn_20200430 START ('2020-04-30'::date) INCLUSIVE END ('2020-05-01'::date) EXCLUSIVE 
          )
;


drop function daw.sp_test_0423_1("ip_input" varchar);

CREATE OR REPLACE FUNCTION "daw"."sp_test_0423_1"
(ip_input varchar )
  RETURNS void AS 
  $BODY$
/* *******************************************************
*功能名称--%@NAME:sp_test_0423_1
*功能描述--%@COMMENT:功能测试
*功能层次---%@LEVEL:daw
*执行周期--%@PERIOD:随时
*参数　　--%@ PARAM:username,schemaname
*备注　　--%@REMARK:select  daw.sp_test_0423_1()
*创建作者--%@CREATOR:
*创建时间--%@CREATED_TIME: 2020-04-23

*修改作者--%@MODIFIED BY  : 
*修改时间--%@MODIFIED_TIME: 
*修改记录--%@MODIFY       : 

*来源表　--%@FROM: 
*来源表　--%@FROM: 
*目标表　--%@TO:   
**********************************************************/
DECLARE
	partition_name varchar;
	v_sql 	   text;
	tb_options record;
	tb_options_one record;
	date_dt date;
	v_num NUMeric ;
	start_num varchar;
	end_num varchar;
	check_num varchar;
	real_num varchar;
	last_month_num varchar;
	last_month_day_num varchar;
	this_day_num varchar;
	last_year_day_num varchar;
	last_year varchar;
	last_year_num varchar;
	last_five_year_num varchar;
	last_six_num varchar;

	
BEGIN
	date_dt  := ip_input::DATE;
	
	select to_char(date_dt,'yyyymm') into real_num;
	select to_char(date_dt::date-interval'1 month','yyyymm') into last_month_num;
	select to_char(date_dt::date-interval'1 month','yyyymmdd') into last_month_day_num;
	select to_char(date_dt::date,'mmdd') into this_day_num;
	select to_char(date_dt::date-interval'1 year','mmdd') into last_year_day_num;
	select to_char(date_dt::date-interval'1 year','yyyy') into last_year;
	select to_char(date_dt::date-interval'1 year','yyyymmdd') into last_year_num;
	select to_char(date_dt::date-interval'5 year','yyyymmdd') into last_five_year_num;
	select to_char(date_dt::date-interval'6 year','yyyy') into last_six_num;
	
	--创建日分区
	for v_num in 0..32 loop
		select to_char(date_dt+v_num,'yyyymmdd') into start_num;
		select to_char(date_dt+v_num+1,'yyyymmdd') into end_num;
		select to_char(date_dt+v_num,'yyyymm') into check_num;
		
		if check_num=real_num then
		execute 'ALTER TABLE daw.employee ADD PARTITION pn_'||start_num||' START ('||chr(39)||start_num||chr(39)||'::date) INCLUSIVE END ('||chr(39)||end_num||chr(39)||'::date) EXCLUSIVE' ;
		end if;
		END LOOP;

--上月日分区交换为月分区	
DROP TABLE IF EXISTS daw.employee_exchange;
CREATE TABLE daw.employee_exchange (LIKE daw.employee) ;
execute 'INSERT INTO daw.employee_exchange SELECT * FROM daw.employee where crt_date between '||chr(39)||ip_input||chr(39)||'::date and '||chr(39)||ip_input||chr(39)||'::date -1';

drop table if exists daw.tmp_tb_options;
execute 'create table daw.tmp_tb_options as select partitionname from pg_partitions 
	WHERE tablename=''employee''
	and partitionname like '||chr(39)||'%'||last_month_num||'%'||chr(39);
	
for tb_options in 
	select partitionname from daw.tmp_tb_options 
	loop
		execute 'ALTER TABLE daw.employee DROP PARTITION '||tb_options.partitionname ;

	end loop;

execute 'ALTER TABLE daw.employee ADD PARTITION pn_'||last_month_num ||' START ('||chr(39)||last_month_day_num||chr(39)||'::date) INCLUSIVE END ('||chr(39)||ip_input||chr(39)||'::date) EXCLUSIVE' ;	
execute 'ALTER TABLE daw.employee EXCHANGE PARTITION pn_'||last_month_num||' WITH TABLE daw.employee_exchange';




if this_day_num='0101' then 
	--去年月分区交换为年分区	
	DROP TABLE IF EXISTS daw.employee_exchange;
	CREATE TABLE daw.employee_exchange (LIKE daw.employee) ;
	execute 'INSERT INTO daw.employee_exchange SELECT * FROM daw.employee where crt_date between '||chr(39)||last_year_num||chr(39)||'::date and '||chr(39)||ip_input||chr(39)||'::date -1';
	
	drop table if exists daw.tmp_last_tb_options;
	execute 'create table daw.tmp_last_tb_options as select partitionname from pg_partitions 
		WHERE tablename=''employee''
		and partitionname like '||chr(39)||'%'||last_year||'%'||chr(39);
	
	for tb_options_one in 
		select partitionname from daw.tmp_last_tb_options 
		loop
		execute 'ALTER TABLE daw.employee DROP PARTITION '||tb_options_one.partitionname ;

	end loop;

	execute 'ALTER TABLE daw.employee ADD PARTITION pn_'||last_year ||' START ('||chr(39)||last_year_num||chr(39)||'::date) INCLUSIVE END ('||chr(39)||date_dt||chr(39)||'::date) EXCLUSIVE' ;
	execute 'ALTER TABLE daw.employee EXCHANGE PARTITION pn_'||last_year||' WITH TABLE daw.employee_exchange';
	
	--压缩五年前数据
	DROP TABLE IF EXISTS daw.employee_exchange;
	CREATE TABLE daw.employee_exchange (LIKE daw.employee) WITH (appendonly=true, compresstype=zlib, compresslevel=5) ;
	execute 'INSERT INTO daw.employee_exchange SELECT * FROM daw.employee where crt_date <'||chr(39)||last_five_year_num||chr(39)||'::date';		
	execute 'ALTER TABLE daw.employee DROP PARTITION pn_'||last_six_num ;
	execute 'ALTER TABLE daw.employee DROP PARTITION pn_five_years_ago' ;	
	execute 'ALTER TABLE daw.employee ADD PARTITION pn_five_years_ago END ('||chr(39)||last_five_year_num||chr(39)||'::date) EXCLUSIVE WITH (appendonly=true, compresstype=zlib, compresslevel=5)' ;	
	execute 'ALTER TABLE daw.employee EXCHANGE PARTITION pn_five_years_ago WITH TABLE daw.employee_exchange';
end if;


--v_sql:='';
--EXECUTE v_sql;
DROP TABLE IF EXISTS daw.employee_exchange;

RETURN; 

END;

$B O D Y$
LANGUAGE plpgsql VOLATILE;

微笑、只是一个表情

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
GreenPlum--分区维护需求（1）

通过编写存储过程，完成如下功能：1. 对于5年以前（以当年1月1日往前推导）的数据，进入历史分区（单一分区）。2. 对于1年以上，5年以内的数据，使用年分区。3. 1个月以上，1年以内的数据，按月分区。4. 1个月以内的数据，按天分区。-- Table: daw.employeeDROP TABLE daw.employee;CREATE TABLE daw.employee(...
复制链接

扫一扫