Greenplum 生成加分区语句
在使用greenplum中会使用分区表，但同时分区表需要维护分区；比如加分区，这个过程比较痛苦，查询相关资料以后有了相应的解决办法，但是该办法也不是万能的，有诸多限制，关于限制有兴趣的同学可以查看我文章最后提到的参考资料。
创建2个表（range分区表和list分区表）
CREATE TABLE tb1_partition_list_yyyymmdd ( id numeric, yyyymmdd character varying(128) ) WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (id) PARTITION BY LIST(yyyymmdd) ( PARTITION p20120811 VALUES('20120811') WITH (tablename='tb1_partition_list_yyyymmdd_1_prt_p20120811', orientation=row , appendonly=true, compresslevel=5 ), PARTITION p20120812 VALUES('20120812') WITH (tablename='tb1_partition_list_yyyymmdd_1_prt_p20120812', orientation=row , appendonly=true, compresslevel=5 ) );
CREATE TABLE tb1_partition_range_yyyymmdd ( id numeric, yyyymmdd date ) WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (id) PARTITION BY RANGE(yyyymmdd) ( PARTITION p20120811 START ('2012-08-11'::date) END ('2012-08-12'::date) WITH (tablename='tb1_partition_range_yyyymmdd_1_prt_p20120811', orientation=row , appendonly=true, compresslevel=5 ), PARTITION p20120812 START ('2012-08-12'::date) END ('2012-08-13'::date) WITH (tablename='tb1_partition_range_yyyymmdd_1_prt_p20120812', orientation=row , appendonly=true, compresslevel=5 ) );
上面分别创建了list分区表和range分区表。
下面创建一个辅助视图：
CREATE VIEW v_pg_add_partitions AS SELECT pp.parrelid AS tableoid, prl.parchildrelid, prl.parname AS partitionname, CASE WHEN (pp.parkind = 'h'::"char") THEN 'hash'::text WHEN (pp.parkind = 'r'::"char") THEN 'range'::text WHEN (pp.parkind = ' l'::"char") THEN 'list'::text ELSE NULL::text END AS partitiontype, translate(pg_get_expr(prl.parlistvalues, prl.parchildrelid), '-'':date character varying bpchar numeric double precision timestamp without time zone'::text, ''::text) AS partitionlistvalue, "substring"(translate(pg_get_expr(prl.parrangestart, prl.parchildrelid), '-'':date character varying bpchar numeric double precision timestamp without time zone'::t ext, ''::text), 1, 8) AS partitionrangestart, "substring"(translate(pg_get_expr(prl.parrangeend, prl.parchildrelid), '-'':date character varying bpchar numeric double precision timestamp without time zone'::text , ''::text), 1, 8) AS partitionrangeend, prl.parruleord AS partitionposition, (("substring"(prl.parlistvalues, 'consttype ([0-9]+)'::text))::integer)::regtype AS listtype, (("substring"(prl.parrangeend, 'constty pe ([0-9]+)'::text))::integer)::regtype AS rangetype FROM pg_partition pp, pg_partition_rule prl WHERE ((pp.paristemplate = false) AND (prl.paroid = pp.oid));
接下来创建生成添加分区表的命令的函数：
create or replace function add_partition_info(tableoid oid,days_from_now integer) returns setof text as $$ import datetime def now(): d=datetime.datetime.now() format='%Y%m%d' return datetime.datetime.strftime(d,format) def add_day(d,n): format='%Y%m%d' d2=datetime.datetime.strptime(d,format) d3=d2+datetime.timedelta(days=n) return datetime.datetime.strftime(d3,format) def add_month(d,n): format='%Y%m%d' formatymd='%Y%m01' if d.__len__() == 6: format='%Y%m' formatymd='%Y%m' d2=datetime.datetime.strptime(d,format) d3=d2+datetime.timedelta(days=31*n) return datetime.datetime.strftime(d3,formatymd) relist=[] sql=""" select * from (select *,tableoid::regclass tablename,lead(case when partitionrangeend <> '' then partitionrangeend else partitionlistvalue end) over (partition by tableoid order by partitionposition desc) as pre_value,row_number() over (partition by tableoid order by partitionposition desc) rn from v_pg_add_partitions where substr(partitionname,1,3)='p20' and tableoid=%s) t where rn=1;""" % (tableoid); rv=plpy.execute(sql); sql_relation="select array_to_string(reloptions,',') reloptions from pg_class where oid=%s" % (tableoid) rv_relation=plpy.execute(sql_relation) if rv.nrows()!=1: return None else: reloptions = rv_relation[0]['reloptions'] tablename=rv[0]['tablename'] partitiontype=rv[0]['partitiontype'] partitionname=rv[0]['partitionname'] pre_value=rv[0]['pre_value'] now_add_7days=add_day(now(),days_from_now) if partitiontype=='range': rangetype=rv[0]['rangetype'] partitionrangestart=rv[0]['partitionrangestart'] partitionrangeend=rv[0]['partitionrangeend'] interval = int(partitionrangeend) - int(pre_value) if partitionname.__len__()==7: func_add=add_month interval=int(partitionrangeend[0:6]) - int(pre_value[0:6]) elif partitionname.__len__()==9: func_add=add_day else: return None while partitionrangestart < now_add_7days: partitionrangestart = func_add(partitionrangestart,interval) partitionrangeend = func_add(partitionrangeend,interval) partitionname="p"+func_add(partitionname[1:],interval) add_sql = "alter table %s add partition %s start ('%s'::%s) end ('%s'::%s) " % (tablename,partitionname,partitionrangestart,rangetype,partitionrangeend,rangetype) if reloptions != None and reloptions != '': add_sql+='with (%s);' % (reloptions) else: add_sql+=";" relist.append(add_sql) if partitiontype=='list': listtype=rv[0]['listtype'] partitionlistvalue=rv[0]['partitionlistvalue'] interval=int(partitionlistvalue) - int(pre_value) if partitionname.__len__()==7: func_add = add_month elif partitionname.__len__()==9: func_add=add_day else: return None while partitionlistvalue < now_add_7days: partitionlistvalue=func_add(partitionlistvalue,interval) partitionname="p"+func_add(partitionname[1:],interval) add_sql="alter table %s add partition %s values ('%s'::%s)" % (tablename,partitionname,partitionlistvalue,listtype) if reloptions != None and reloptions != '': add_sql +='with (%s);' % (reloptions) else: add_sql+=";" relist.append(add_sql) return relist $$ LANGUAGE plpythonu;
最后我们进行测试（增加当前时间3天内的分区）
test_db=# SELECT add_partition_info('tb1_partition_range_yyyymmdd'::regclass,3); add_partition_info ---------------------------------------------------------------------------------------------------------------------------------------------------------- alter table tb1_partition_range_yyyymmdd add partition p20120813 start ('20120813'::date) end ('20120814'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120814 start ('20120814'::date) end ('20120815'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120815 start ('20120815'::date) end ('20120816'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120816 start ('20120816'::date) end ('20120817'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120817 start ('20120817'::date) end ('20120818'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120818 start ('20120818'::date) end ('20120819'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120819 start ('20120819'::date) end ('20120820'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120820 start ('20120820'::date) end ('20120821'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120821 start ('20120821'::date) end ('20120822'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120822 start ('20120822'::date) end ('20120823'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120823 start ('20120823'::date) end ('20120824'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120824 start ('20120824'::date) end ('20120825'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120825 start ('20120825'::date) end ('20120826'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120826 start ('20120826'::date) end ('20120827'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120827 start ('20120827'::date) end ('20120828'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120828 start ('20120828'::date) end ('20120829'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120829 start ('20120829'::date) end ('20120830'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120830 start ('20120830'::date) end ('20120831'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120831 start ('20120831'::date) end ('20120901'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120901 start ('20120901'::date) end ('20120902'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120902 start ('20120902'::date) end ('20120903'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120903 start ('20120903'::date) end ('20120904'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120904 start ('20120904'::date) end ('20120905'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120905 start ('20120905'::date) end ('20120906'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120906 start ('20120906'::date) end ('20120907'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120907 start ('20120907'::date) end ('20120908'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120908 start ('20120908'::date) end ('20120909'::date) with (appendonly=true,compresslevel=5); alter table tb1_partition_range_yyyymmdd add partition p20120909 start ('20120909'::date) end ('20120910'::date) with (appendonly=true,compresslevel=5);
再创建一个range分区且是按月分区的表
CREATE TABLE tb1( id numeric, yyyymmdd date )DISTRIBUTED BY (id) PARTITION BY RANGE(yyyymmdd) ( PARTITION p201208 START ('2012-08-01'::date) END ('2012-09-01'::date) , PARTITION p201209 START ('2012-09-01'::date) END ('2012-10-01'::date) );
test_db=# SELECT add_partition_info('tb1'::regclass,1); add_partition_info ----------------------------------------------------------------------------------------- alter table tb1 add partition p201210 start ('20121001'::date) end ('20121101'::date) ; alter table tb1 add partition p201211 start ('20121101'::date) end ('20121201'::date) ; alter table tb1 add partition p201212 start ('20121201'::date) end ('20130101'::date) ; alter table tb1 add partition p201301 start ('20130101'::date) end ('20130201'::date) ; alter table tb1 add partition p201302 start ('20130201'::date) end ('20130301'::date) ; alter table tb1 add partition p201303 start ('20130301'::date) end ('20130401'::date) ; alter table tb1 add partition p201304 start ('20130401'::date) end ('20130501'::date) ; alter table tb1 add partition p201305 start ('20130501'::date) end ('20130601'::date) ; alter table tb1 add partition p201306 start ('20130601'::date) end ('20130701'::date) ; alter table tb1 add partition p201307 start ('20130701'::date) end ('20130801'::date) ; alter table tb1 add partition p201308 start ('20130801'::date) end ('20130901'::date) ; alter table tb1 add partition p201309 start ('20130901'::date) end ('20131001'::date) ; alter table tb1 add partition p201310 start ('20131001'::date) end ('20131101'::date) ; alter table tb1 add partition p201311 start ('20131101'::date) end ('20131201'::date) ; alter table tb1 add partition p201312 start ('20131201'::date) end ('20140101'::date) ; alter table tb1 add partition p201401 start ('20140101'::date) end ('20140201'::date) ; alter table tb1 add partition p201402 start ('20140201'::date) end ('20140301'::date) ; alter table tb1 add partition p201403 start ('20140301'::date) end ('20140401'::date) ; alter table tb1 add partition p201404 start ('20140401'::date) end ('20140501'::date) ; alter table tb1 add partition p201405 start ('20140501'::date) end ('20140601'::date) ; alter table tb1 add partition p201406 start ('20140601'::date) end ('20140701'::date) ; alter table tb1 add partition p201407 start ('20140701'::date) end ('20140801'::date) ; alter table tb1 add partition p201408 start ('20140801'::date) end ('20140901'::date) ; alter table tb1 add partition p201409 start ('20140901'::date) end ('20141001'::date) ; alter table tb1 add partition p201410 start ('20141001'::date) end ('20141101'::date) ; alter table tb1 add partition p201411 start ('20141101'::date) end ('20141201'::date) ; alter table tb1 add partition p201412 start ('20141201'::date) end ('20150101'::date) ;
可以看见也是没有问题的。
参考资料：
《greenplum企业应用实战》
