参考文献
« Greenplum4.2.2管理员指南 »
http://www.oushu.io/docs/ch/SQL.html#create-table
以及其他大牛的文章(不记得了)
range
创建range分区表
create table test_partition_range(
id numeric,
name varchar(32),
dw_end_date date
)
partition by range(dw_end_date)
(
partition p20111230 start ('2011-12-30'::date) end ('2011-12-31'::date),
partition p20111231 start ('2011-12-31'::date) end ('2012-01-01'::date),
partition p20120101 start ('2012-01-01'::date) end ('2012-01-02'::date),
partition p20120102 start ('2012-01-02'::date) end ('2012-01-03'::date),
partition p20120103 start ('2012-01-03'::date) end ('2012-01-04'::date),
partition p20120104 start ('2012-01-04'::date) end ('2012-01-05'::date)
);
数据测试
test=# insert into test_partition_range values(1,'a','2011-12-31');
INSERT 0 1
test=# select * from test_partition_range_1_prt_p20111230;
id | name | dw_end_date
----+------+-------------
(0 rows)
test=# select * from test_partition_range_1_prt_p20111231;
id | name | dw_end_date
----+------+-------------
1 | a | 2011-12-31
(1 row)
test=# select * from test_partition_range;
id | name | dw_end_date
----+------+-------------
1 | a | 2011-12-31
新增分区
test=# alter table test_partition_range add partition p20120105_6 start ('2012-01-05'::date) end ('2012-01-07'::date);
test=# insert into test_partition_range values(1,'a','2012-01-05');
test=# insert into test_partition_range values(1,'a','2012-01-06');
拆分分区
test=#
alter table test_partition_range
split partition p20120105_6 at ( ('2012-01-06'::date) )
into (partition p20120105,partition p20120106);
--确认拆分分区后,数据是否保留
test=# select * from test_partition_range_1_prt_p20120105;
id | name | dw_end_date
----+------+-------------
1 | a | 2012-01-05
(1 row)
test=# select * from test_partition_range_1_prt_p20120106;
id | name | dw_end_date
----+------+-------------
1 | a | 2012-01-06
(1 row)
交换分区
1.建临时表
create table test_tmp(
id numeric,
name varchar(32),
dw_end_date date
);
2.交换分区
alter table test_partition_range
exchange partition p20120106
with table test_tmp;
添加默认分区
test=# alter table test_partition_range add default partition others;
NOTICE: CREATE TABLE will create partition "test_partition_range_1_prt_others" for table "test_partition_range"
ALTER TABLE
test=# insert into test_partition_range values(1,'a','2015-01-06');
INSERT 0 1
test=# select * from test_partition_range_1_prt_others;
id | name | dw_end_date
----+------+-------------
1 | a | 2015-01-06
(1 row)
拆分默认分区
alter table test_partition_range SPLIT DEFAULT PARTITION
start ('2015-01-06'::date) inclusive
end ('2015-01-07'::date) exclusive
into (partition p20150106 ,partition others);
NOTICE: exchanged partition "others" of relation "test_partition_range" with relation "pg_temp_17158"
NOTICE: dropped partition "others" for relation "test_partition_range"
NOTICE: CREATE TABLE will create partition "test_partition_range_1_prt_p20150106" for table "test_partition_range"
NOTICE: CREATE TABLE will create partition "test_partition_range_1_prt_others" for table "test_partition_range"
ALTER TABLE
test=# select * from test_partition_range_1_prt_p20150106;
id | name | dw_end_date
----+------+-------------
1 | a | 2015-01-06
(1 row)
test=# select * from test_partition_range_1_prt_others;
id | name | dw_end_date
----+------+-------------
1 | a | 2015-01-07
(1 row)
使用every创建分区
--使用every 创建20111201~20111231的分区：
create table test_partition_every(
id numeric,
name varchar(32),
dw_end_date date
)partition by range(dw_end_date)
(
partition p201112 start ('2011-12-01'::date) end ('2011-12-31'::date) every ('1 days'::interval)
);
list
创建list分区表
create table test_partition_list(
id numeric,
city varchar(32)
)
partition by list(city)
(
partition guangzhou values('guangzhou'),
partition hangzhou values('hangzhou'),
partition shanghai values('shanghai'),
partition beijing values('beijing')
);
insert into test_partition_list values(1,'guangzhou');
INSERT 0 1
insert into test_partition_list values(1,'GUANGZHOU');
ERROR: no partition for partitioning key (seg0 localhost:40000 pid=235197)
添加分区
test=# alter table test_partition_list add partition anhui values ('ANHUI','anhui');
NOTICE: CREATE TABLE will create partition "test_partition_list_1_prt_anhui" for table "test_partition_list"
ALTER TABLE
test=# insert into test_partition_list values(1,'ANHUI');
INSERT 0 1
test=# insert into test_partition_list values(1,'anhui');
INSERT 0 1
test=# select * from test_partition_list_1_prt_anhui;
id | city
----+-------
1 | ANHUI
1 | anhui
(2 rows)
拆分普通分区
test=# alter table test_partition_list SPLIT partition anhui at ('ANHUI') into (partition anhui_sy,partition anhui_xy);
test=# select * from test_partition_list_1_prt_anhui_sy;
id | city
----+-------
1 | anhui
(1 row)
test=# select * from test_partition_list_1_prt_anhui_xy;
id | city
----+-------
1 | ANHUI
(1 row)
清空普通分区
test=# select * from test_partition_list_1_prt_anhui_xy;
id | city
----+-------
1 | ANHUI
1 | anhui
(2 rows)
test=# alter table test_partition_list truncate partition anhui_xy;
ALTER TABLE
test=# select * from test_partition_list_1_prt_anhui_xy;
id | city
----+------
(0 rows)
删除普通分区
test=# alter table test_partition_list drop partition anhui_xy;
ALTER TABLE
test=# select * from test_partition_list_1_prt_anhui_xy;
ERROR: relation "test_partition_list_1_prt_anhui" does not exist
LINE 1: select * from test_partition_list_1_prt_anhui_xy;
添加默认分区
test=# alter table test_partition_list add default partition other_city;
NOTICE: CREATE TABLE will create partition "test_partition_list_1_prt_other_city" for table "test_partition_list"
ALTER TABLE
test=# insert into test_partition_list values(1,'guangzhou');
INSERT 0 1
test=# insert into test_partition_list values(1,'GUANGZHOU');
INSERT 0 1
test=# select * from test_partition_list_1_prt_guangzhou;
id | city
----+-----------
1 | guangzhou
1 | guangzhou
(2 rows)
test=# select * from test_partition_list_1_prt_other_city;
id | city
----+-----------
1 | GUANGZHOU
(1 row)
-- 存在默认分区 不允许添加分区
test=# alter table test_partition_list add partition hunan values ('hunan');
ERROR: cannot add LIST partition "hunan" to relation "test_partition_list" with DEFAULT partition "other_city"
HINT: need to SPLIT partition "other_city"
拆分默认分区
alter table test_partition_list
SPLIT DEFAULT PARTITION at ('hunan')
into (partition hunan,partition other_city);
test=# insert into test_partition_list values(1,'hunan');
INSERT 0 1
test=# insert into test_partition_list values(1,'HUNAN');
INSERT 0 1
test=# select * from test_partition_list_1_prt_other_city;
id | city
-----+-----------
1 | GUANGZHOU
1 | HUNAN
(2 rows)
test=# select * from test_partition_list_1_prt_hunan;
id | city
----+-------
1 | hunan
(1 row)
清空默认分区
test=# select * from test_partition_list_1_prt_other_city;
id | city
----+-------
1 | HUNAN
(1 row)
test=# alter table test_partition_list truncate default partition;
NOTICE: truncated partition "other_city" for relation "test_partition_list"
ALTER TABLE
test=# select * from test_partition_list_1_prt_other_city;
id | city
----+------
(0 rows)
删除默认分区
alter table test_partition_list drop default partition;
带压缩的分区表
创建带不同压缩级别的分区
create table test_partition_range(
id numeric,
name varchar(32),
dw_end_date date
)
WITH (appendonly=true, compresslevel=5, orientation=row, compresstype=zlib)
PARTITION BY RANGE(dw_end_date)
(
partition p20111230 start ('2011-12-30'::date) end ('2011-12-31'::date) WITH ( appendonly=true, compresslevel=9, orientation=row, compresstype=zlib ) ,
partition p20111231 start ('2011-12-31'::date) end ('2012-01-01'::date) WITH ( appendonly=true, compresslevel=9, orientation=row, compresstype=zlib ),
partition p20120101 start ('2012-01-01'::date) end ('2012-01-02'::date) WITH ( appendonly=true, compresslevel=8, orientation=row, compresstype=zlib ),
partition p20120102 start ('2012-01-02'::date) end ('2012-01-03'::date) WITH ( appendonly=true, compresslevel=7, orientation=row, compresstype=zlib ),
partition p20120103 start ('2012-01-03'::date) end ('2012-01-04'::date) WITH ( appendonly=true, compresslevel=6, orientation=row, compresstype=zlib ),
partition p20120104 start ('2012-01-04'::date) end ('2012-01-05'::date) WITH ( appendonly=true, compresslevel=5, orientation=row, compresstype=zlib )
);
--select partitiontablename,partitionboundary from pg_partitions where tablename='test_partition_range';
追加分区
alter table test_partition_range
add partition p20120105
start ('2012-01-05'::date) end ('2012-01-06'::date)
WITH ( appendonly=true, compresslevel=2, orientation=row, compresstype=zlib );
添加默认分区
alter table test_partition_range
add default partition others
WITH ( appendonly=true, compresslevel=3, orientation=row, compresstype=zlib );
拆分分区
alter table test_partition_range
SPLIT DEFAULT PARTITION
start ('2015-01-06'::date) inclusive
end ('2015-01-07'::date) exclusive
into (partition p20150106 ,partition others);
查看分区表
select
schemaname,
tablename,
partitionschemaname p_schema,
partitiontablename p_table,
partitionname p_name,
partitiontype p_type,
partitionrangestart p_rangestart,
partitionstartinclusive S,
partitionrangeend p_rangeend,
partitionendinclusive E
from pg_partitions;
文档信息
- 本文作者：fei
- 本文链接：https://ayee1616166.github.io/2020/09/08/HAWQ%E5%88%86%E5%8C%BA%E8%A1%A8/
- 版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）