HAWQ分区表

2020/09/08 HAWQ 共 8134 字,约 24 分钟

参考文献

« 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;

文档信息

Search

    Table of Contents