HAWQ技术解析（八） —— 大表分区
一、HAWQ中的分区表
与大多数关系数据库一样，HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表，外部分区表在后面“外部数据”篇讨论。
在CREATE TABLE命令中使用PARTITION BY或可选的SUBPARTITION BY子句建立分区。上级分区能够包括一个或多个下级分区。HAWQ内部创建上下级分区之间的层次关系。分区条件定义一个分区内能够包括的数据。在建立分区表时，HAWQ为每一个分区条件创建一个唯一的CHECK约束，限制一个分区所能含有的数据，保证各个分区中数据的相互排斥性。
2. 定义数字范围分区表
列表分区能够使用不论什么同意等值比較数据类型的列作为分区键。列表分区表必须显式定义每一个分区。
4. 定义多级分区
能够在分区中定义子分区。使用subpartition template子句保证每一个分区都有同样的子分区定义。包括以后加入的分区。
以下的样例显示了一个树形分区设计。sales表按年、月、地区的层级三级分区。
6. 查看分区表定义
查询pg_partitions视图能够获取分区相关信息。
使用EXPLAIN能够检查查询运行计划，验证查询优化器是否仅仅扫描了相关分区的数据。以下以sales表上的年、月、地区三级分区为例进行说明。
1. 插入一条数据，如图2所看到的。
3. 以年为条件查询。查询计划如图4所看到的。
5. 以DEFAULT条件查询，查询计划如图7所看到的。
分区消除有以下限制：
ALTER TABLE命令维护分区表。虽然能够通过引用分区相应的表对象的名子进行查询和装载数据，但改动分区表结构时，仅仅能使用ALTER TABLE...PARTITION引用分区的名字。也能够使用PARTITION FOR (value)或PARTITION FOR(RANK(number))指示分区。注意。HAWQ不支持在多级分区上的例如以下操作：
为一个分区表添加子分区时，能够指定须要改动的分区。
2. 添加缺省分区
改动顶级父表的名称。会重命名全部分区子表名，比如：
5. 清空分区
使用ALTER TABLE命令清空一个分区及其全部子分区的数据。
6. 分区交换
分区交换指的是用一个表的数据与一个分区的数据交换。
7. 分裂分区
分裂分区指的是将一个分区分裂成两个分区，HAWQ仅仅能分裂单级分区表。
8. 改动子分区模板
ALTER TABLE SET SUBPARTITION TEMPLATE改动一个分区表的子分区模板。新模板仅仅影响后面加入的数据，不改动现有的分区数据。
9. 分区滚动窗体维护
在对数据表进行范围分区处理的基础上，能够进一步设计滚动数据的策略。通过维护一个数据滚动窗体，删除老分区，加入新分区。将老分区的数据迁移到数据仓库以外的次级存储，以节省系统开销。以下以一个常见的应用场景说明分区自己主动滚动的实现。假设一个数据仓库保留近期一年的销售记录，按日期每天一个分区。初始建立一年的分区，并装载近一年的数据，然后每天装载前一天的销售数据。
（1）建立分区表
（2）创建动态滚动分区的函数
HAWQ从PostgreSQL继承了过程化编程。并使用多种语言。在我自己使用过的SQL-on-Hadoop产品中。HAWQ是唯一支持过程化编程的。而且，其内建函数、操作符和语法与Oracle极为接近，这对于传统数据库的开发管理人员及广大DBA是非常有吸引力的。在转到大数据平台时，他们能够复用原来积淀的知识与经验。仅凭这一点。HAWQ就能够在众多SQL-on-Hadoop解决方式中体现出独有的优势和价值。下一篇将具体描写叙述HAWQ的过程化语言编程。
（3）在cron中添加作业。比如，从下个月開始的每月一日两点运行分区滚动。
与大多数关系数据库一样，HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表，外部分区表在后面“外部数据”篇讨论。
在数据仓库应用中，事实表通常有非常多的记录，分区能够将这样的大表在逻辑上分为小的、更易管理的数据片段。HAWQ的优化器支持分区消除以提高查询性能。
仅仅要查询条件中能够使用分区键作为过滤条件。那么HAWQ仅仅须要扫描满足查询条件的分区，而不必进行全表扫描。
分区并不改变表数据在segment间的物理分布。表的分布是物理的，不管是分区表还是非分区表，HAWQ都会在segment上物理地分布数据，而且并行处理查询。而表的分区是逻辑上的，HAWQ逻辑分隔大表以提高查询性能和数据仓库应用的可维护性。比如，将老的分区数据从数据仓库转储或移除。并建立新的数据分区等。HAWQ支持以下分区类型：
- 范围分区：基于数字范围分区。如日期、价格等。
- 列表分区：基于列表值分区，如销售区域、产品分类等。
- 两者混合的分区类型。
最好在建表之前就规划好分区方式和维护方法，由于当一个非分区表已经存在大量数据后再改作分区表的操作，时间和空间消耗上都是非常棘手的问题。
图1
在CREATE TABLE命令中使用PARTITION BY或可选的SUBPARTITION BY子句建立分区。上级分区能够包括一个或多个下级分区。HAWQ内部创建上下级分区之间的层次关系。分区条件定义一个分区内能够包括的数据。在建立分区表时，HAWQ为每一个分区条件创建一个唯一的CHECK约束，限制一个分区所能含有的数据，保证各个分区中数据的相互排斥性。
查询优化器利用该CHECK约束，决定扫描哪些分区以满足查询谓词条件。
HAWQ在系统文件夹中存储分区的层次信息，因此插入到分区表中的行能够正确传递到子分区中。ALTER TABLE命令的PARTITION子句用于改动分区表结构。
在向分区表插入数据时，能够在INSERT命令中指定表的根分区或叶分区（适用于从主表继承方式创建的分区表）。
假设数据对于指定的叶分区无效，将返回错误。
INSERT命令不支持向非叶分区的子分区中插入数据。
二、确定分区策略
并非全部表都适合分区，须要进行实測以保证所期望的性能提升。以下是一些通用的分区指南。假设对以下问题的大部分答案是肯定的，分区表对于提高性能是可行的数据库设计。否则。表不适合分区。
- 表是否足够大？依照一般的经验，至少千万记录以上的表才算大表。数据仓库中的事实表适合作为分区表。对于小于这个数量级的表通常不须要分区。由于系统管理与维护分区的开销会抵消掉分区带来的可见的性能优势。
- 性能是否不可接受？仅仅有当实施了其他优化手段后，响应时间仍然不可接受时，再考虑使用分区。
- 查询谓词条件中是否包括适合的分区键？检查查询的WHERE子句中是否包括适合作为分区的条件。
比如，假设大部分查询都通过日期检索数据。那么依照月或周做范围分区可能是故意的。
- 是否须要维护一个数据仓库的历史数据窗体？比如，组织中的数据仓库仅仅须要保持过去12个月的数据，那么按月分区，就能够非常easy地删除最老月份的分区，并向最新的月分区中装载当前数据。
- 依据分区定义条件。是否每一个分区的数据量比較平均？分区条件应尽可能使数据平均划分。
假设每一个分区包括基本同样的记录数。性能会有所提升。
比如，将一个大表分成10个相等的分区，假设查询条件中带有分区键，那么理论上查询应该比非分区表快将近10倍。
首先，不要创建多余的分区。太多的分区将会减慢管理和维护任务。如检查磁盘使用、集群扩展、释放剩余空间等。其次。仅仅有在查询条件能够利用分区消除时，性能才会得到提升。否则，一个须要扫描全部分区的查询会比非分区表还慢。能够通过查看一个查询的运行计划（explain plan）确认是否用到了分区消除。最后是关于多级分区的问题。多级分区会使分区文件的数量高速增长。比如。假设一个表按日期和城市做分区。1000天的1000个城市的数据，就会形成100万个分区。
假设表有100列。而且假设表使用面向列的物理存储格式，那么系统为此表须要管理1亿个文件。
三、创建分区表
如前所述。创建分区表须要定义分区键、分区类型、分区层次。以下是几个创建分区表的样例。
1. 定义日期范围分区表
在定义日期分区表时，能够考虑以可接受的细节粒度做分区。
比如，相对于以月份做主分区，日期做子分区的分区策略，每一个日期一个分区，一年365个分区的方案可能更好。
多级分区能够降低生成查询计划的时间，但平面化的分区设计运行地更快。
create table sales (id int, date date, amt decimal(10,2)) distributed by (id) partition by range (date) ( start (date '2017-01-01') inclusive end (date '2017-02-01') exclusive every (interval '1 day') );上面的语句以date列作为分区键，从2017年1月1月到2017年2月1日，每天一个分区。将建立31个分区。分区相应表对象的名称各自是sales_1_prt_1 ... sales_1_prt_31。注意inclusive表示分区中包括定义的分区键值，exclusive表示不包括。比如。sales_1_prt_1包括date >= (date '2017-01-01') and date < (date '2017-01-02')的数据，sales_1_prt_31包括date >= (date '2017-01-31') and date < (date '2017-02-01')的数据，即这个语句定义的分区是左闭右开的数据区间。
db1=# insert into sales values (1, (date '2016-12-31'),100); ERROR: no partition for partitioning key (seg21 hdp4:40000 pid=60186) db1=# insert into sales values (1, (date '2017-01-01'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-02-01'),100); ERROR: no partition for partitioning key (seg23 hdp4:40000 pid=60190) db1=# insert into sales values (1, (date '2017-01-31'),100); INSERT 0 1同样能够定义左开右闭的分区。
create table sales (id int, date date, amt decimal(10,2)) distributed by (id) partition by range (date) ( start (date '2017-01-01') exclusive end (date '2017-02-01') inclusive every (interval '1 day') ); db1=# insert into sales values (1, (date '2017-01-01'),100); ERROR: no partition for partitioning key (seg19 hdp4:40000 pid=60182) db1=# insert into sales values (1, (date '2017-01-02'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-01-31'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-02-01'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-02-02'),100); ERROR: no partition for partitioning key (seg23 hdp4:40000 pid=60269)也能够显式定义每一个分区。
create table sales (id int, date date, amt decimal(10,2))
distributed by (id)
partition by range (date)
( partition p201701 start (date '2017-01-01') inclusive ,
partition p201702 start (date '2017-02-01') inclusive ,
partition p201703 start (date '2017-03-01') inclusive ,
partition p201704 start (date '2017-04-01') inclusive ,
partition p201705 start (date '2017-05-01') inclusive ,
partition p201706 start (date '2017-06-01') inclusive ,
partition p201707 start (date '2017-07-01') inclusive ,
partition p201708 start (date '2017-08-01') inclusive ,
partition p201709 start (date '2017-09-01') inclusive ,
partition p201710 start (date '2017-10-01') inclusive ,
partition p201711 start (date '2017-11-01') inclusive ,
partition p201712 start (date '2017-12-01') inclusive
end (date '2018-01-01') exclusive ); 以上语句为2017年每一个月建立一个分区。注意，不须要问每一个分区指定END值，仅仅要在最后一个分区（本例中的p201712）指定END值就可以。2. 定义数字范围分区表
db1=# create table rank (id int, rank int, year int, gender
db1(# char(1), count int)
db1-# distributed by (id)
db1-# partition by range (year)
db1-# ( start (2017) end (2018) every (1),
db1(# default partition extra );
NOTICE: CREATE TABLE will create partition "rank_1_prt_extra" for table "rank"
NOTICE: CREATE TABLE will create partition "rank_1_prt_2" for table "rank"
CREATE TABLE
db1=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------------+-------+---------+-------------
public | rank | table | gpadmin | append only
public | rank_1_prt_2 | table | gpadmin | append only
public | rank_1_prt_extra | table | gpadmin | append only
(3 rows)
db1=# insert into rank values (1,1,2016,'M',100);
INSERT 0 1
db1=# insert into rank values (1,1,2017,'M',100);
INSERT 0 1
db1=# insert into rank values (1,1,2018,'M',100);
INSERT 0 1
db1=# insert into rank values (1,1,2019,'M',100);
INSERT 0 1
db1=# select * from rank;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2016 | M | 100
1 | 1 | 2018 | M | 100
1 | 1 | 2019 | M | 100
1 | 1 | 2017 | M | 100
(4 rows)
db1=# select * from rank_1_prt_2;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2017 | M | 100
(1 row)
db1=# select * from rank_1_prt_extra;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2016 | M | 100
1 | 1 | 2018 | M | 100
1 | 1 | 2019 | M | 100
(3 rows)
db1=# drop table rank;
DROP TABLE
db1=# \dt
No relations found. 从上面的样例看到：- HAWQ缺省的分区范围是左闭右开。
- 能够使用default partition子句添加一个缺省分区。当数据不被包括在不论什么明白定义的分区时。能够被包括在缺省分区中。
- HAWQ在查询时能够将分区当做表看待，但删除主表后。分区被一并删除。
列表分区能够使用不论什么同意等值比較数据类型的列作为分区键。列表分区表必须显式定义每一个分区。
注意列表中的字符比較区分大写和小写。
db1=# create table rank (id int, rank int, year int, gender
db1(# char(1), count int )
db1-# distributed by (id)
db1-# partition by list (gender)
db1-# ( partition girls values ('f'),
db1(# partition boys values ('m'),
db1(# default partition other );
NOTICE: CREATE TABLE will create partition "rank_1_prt_girls" for table "rank"
NOTICE: CREATE TABLE will create partition "rank_1_prt_boys" for table "rank"
NOTICE: CREATE TABLE will create partition "rank_1_prt_other" for table "rank"
CREATE TABLE
db1=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------------+-------+---------+-------------
public | rank | table | gpadmin | append only
public | rank_1_prt_boys | table | gpadmin | append only
public | rank_1_prt_girls | table | gpadmin | append only
public | rank_1_prt_other | table | gpadmin | append only
(4 rows)
db1=# insert into rank values (1,1,2016,'M',100);
INSERT 0 1
db1=# insert into rank values (1,1,2016,'m',100);
INSERT 0 1
db1=# insert into rank values (1,1,2016,'f',100);
INSERT 0 1
db1=# insert into rank values (1,1,2016,'F',100);
INSERT 0 1
db1=# insert into rank values (1,1,2016,'A',100);
INSERT 0 1
db1=# select * from rank;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2016 | f | 100
1 | 1 | 2016 | m | 100
1 | 1 | 2016 | M | 100
1 | 1 | 2016 | F | 100
1 | 1 | 2016 | A | 100
(5 rows)
db1=# select * from rank_1_prt_boys;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2016 | m | 100
(1 row)
db1=# select * from rank_1_prt_girls;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2016 | f | 100
(1 row)
db1=# select * from rank_1_prt_other;
id | rank | year | gender | count
----+------+------+--------+-------
1 | 1 | 2016 | M | 100
1 | 1 | 2016 | F | 100
1 | 1 | 2016 | A | 100
(3 rows) HAWQ不支持多分区键列复合比較，分区键仅仅能是单列。db1=# create table rank (id int, rank int, year int, gender
db1(# char(1), count int )
db1-# distributed by (id)
db1-# partition by list (gender,year)
db1-# ( partition girls values ('f',2017),
db1(# partition boys values ('m',2018),
db1(# default partition other );
ERROR: Composite partition keys are not allowed4. 定义多级分区
能够在分区中定义子分区。使用subpartition template子句保证每一个分区都有同样的子分区定义。包括以后加入的分区。
create table sales (trans_id int, date date, amount
decimal(9,2), region text)
distributed by (trans_id)
partition by range (date)
subpartition by list (region)
subpartition template
( subpartition usa values ('usa'),
subpartition asia values ('asia'),
subpartition europe values ('europe'),
default subpartition other_regions)
(start (date '2017-01-01') inclusive
end (date '2018-01-01') exclusive
every (interval '1 month'),
default partition outlying_dates ); 以上语句建立了一共65个分区。一级分区13个。每一个一级分区包括4个子分区。以下的样例显示了一个树形分区设计。sales表按年、月、地区的层级三级分区。
SUBPARTITION TEMPLATE子句保证每一个分区都有同样的子分区结构。样例中的每一层级都指定了缺省的分区。
create table sales (id int, year int, month int, day int,
region text)
distributed by (id)
partition by range (year)
subpartition by range (month)
subpartition template (
start (1) end (13) every (1),
default subpartition other_months )
subpartition by list (region)
subpartition template (
subpartition usa values ('usa'),
subpartition europe values ('europe'),
subpartition asia values ('asia'),
default subpartition other_regions )
( start (2017) end (2018) every (1),
default partition outlying_years ); 注意，范围分区上的多级分区非常easy建立大量的分区。当中有些分区可能仅仅有非常少的数据（甚至没有数据）。随着分区数量的添加，系统表的记录不断增长。查询优化和运行时所需的内存也会添加。加大范围分区的范围或者选择不同的分区策略有助于降低分区的数量。
5. 对一个已经存在的表进行分区
正如开篇提到的，HAWQ仅仅能使用CREATE TABLE命令创建分区表。
假设想对一个已经存在的表进行分区，仅仅能这样做：新建分区表->将数据原表导入分区表->删除原表->分区表改名->分析分区表->对新建的分区表又一次授权。
比如：
create table sales2 (like sales) partition by range (date) ( start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month') ); insert into sales2 select * from sales; drop table sales; alter table sales2 rename to sales; analyze sales; grant all privileges on sales to admin; grant select on sales to guest;
6. 查看分区表定义
查询pg_partitions视图能够获取分区相关信息。
select partitionboundary, partitiontablename, partitionname,partitionlevel, partitionrank from pg_partitions where tablename='sales';以下表和视图提供了分区表的信息。
- pg_partition：分区表及其层级关系。
- pg_partition_templates：子分区使用的模板。
- pg_partition_columns：分区键列。
使用EXPLAIN能够检查查询运行计划，验证查询优化器是否仅仅扫描了相关分区的数据。以下以sales表上的年、月、地区三级分区为例进行说明。
create table sales (id int, year int, month int, day int,
region text)
distributed by (id)
partition by range (year)
subpartition by range (month)
subpartition template (
start (1) end (13) every (1),
default subpartition other_months )
subpartition by list (region)
subpartition template (
subpartition usa values ('北京'),
subpartition europe values ('上海'),
subpartition asia values ('广州'),
default subpartition other_regions )
( start (2017) end (2020) every (1),
default partition outlying_years ); sales表最底层存储数据的分区共同拥有 4 * 13 * 4 = 208个;1. 插入一条数据，如图2所看到的。
图2
2. 无条件查询，查询计划如图3所看到的。图3
能够看到，该查询扫描了全部208个分区，没有分区消除。3. 以年为条件查询。查询计划如图4所看到的。
图4
能够看到，该查询扫描了全部208个分区的一半。104个分区。顶级年份分区有四个。为什么where year='2017'要扫描104而不是52个分区呢？在运行时。查询优化器会扫描这个表的层级关系（系统表）。并使用CHECK表约束确定扫描哪些满足查询条件的分区。假设存在DEFAULT分区。则它总是被扫描，因此该查询或扫描year=2017和default两个分区。这就是扫描的分区数是104而不是52的原因。可见。包括DEFAULT分区会添加总体扫描时间。按理说DEFAULT与其他全部分区的数据都是相互排斥的，全然不必在能够确定分区的条件下再去扫描它，这是不是HAWQ查询优化器的一个问题也未可知。
3. 以年、月为条件查询，查询计划如图5所看到的。
图5
能够看到，这次仅仅扫描了16个分区。同样道理本应仅仅扫描4个底层分区。由于DEFAULT的存在，须要扫描16个分区。
4. 以年、月、地区为条件查询。查询计划如图6所看到的。
图6
这次仅仅需扫描一个分区。当查询中包括全部层级的谓词条件时，没有扫描DEFAULT，而是唯一确定了一个分区。5. 以DEFAULT条件查询，查询计划如图7所看到的。
图7
这次仅仅要扫描年份DEFAULT分区下的52个子分区。分区消除有以下限制：
- 查询优化器仅仅有在查询条件中包括=、<、<=、>、>=、<>等比較运算符是才可能应用分区消除。
- 对于稳定的函数会应用分区消除。对于易变函数不会应用分区消除。
比如，WHERE date > CURRENT_DATE会应用分区消除，而time > TIMEOFDAY则不会。
ALTER TABLE命令维护分区表。虽然能够通过引用分区相应的表对象的名子进行查询和装载数据，但改动分区表结构时，仅仅能使用ALTER TABLE...PARTITION引用分区的名字。也能够使用PARTITION FOR (value)或PARTITION FOR(RANK(number))指示分区。注意。HAWQ不支持在多级分区上的例如以下操作：
- 添加缺省分区
- 添加分区
- 删除缺省分区
- 删除分区
- 切割分区
- 全部改动子分区的操作
create table sales (id int, year int, month int, day int,
region text)
distributed by (id)
partition by range (year)
subpartition by range (month)
subpartition template (
start (1) end (13) every (1),
default subpartition other_months )
subpartition by list (region)
subpartition template (
subpartition usa values ('北京'),
subpartition europe values ('上海'),
subpartition asia values ('广州'),
default subpartition other_regions )
( start (2017) end (2020) every (1));
alter table sales add partition
start (2016) inclusive
end (2017) exclusive; 使用add partition添加分区时不能存在DEFAULT分区，否则会报相似以下的错误：ERROR: cannot add RANGE partition to relation "sales" with DEFAULT partition "outlying_years" HINT: need to SPLIT partition "outlying_years"这时须要使用split partition添加分区。
为一个分区表添加子分区时，能够指定须要改动的分区。
alter table sales alter partition for (rank(12))
add partition africa values ('africa');
alter table sales alter partition for (rank(1))
add partition africa values ('africa');2. 添加缺省分区
alter table sales add default partition other;假设没有DEFAULT分区，不能匹配分区CHECK约束的数据行将被拒绝入库。而且数据转载失败。为了避免这样的情况，指定DEFAULT分区。不论什么不能与分区匹配的行都被装载进DEFAULT分区。
3. 分区改名
每一个子分区相应一个表对象，能够用\dt元命令查看到。假设是自己主动生成的范围分区，在没有指定名称的分区被赋予一个数字。
分区相应表对象的命名规则例如以下：
<parentname>_<level>_prt_<partition_name>比如：
sales_1_prt_1_2_prt_11_3_prt_other_regions上面的名称表示该分区名为'other_regions'，是sales表的一个第三级分区，隶属第一级的1号分区下的第二级的11号分区下。
改动顶级父表的名称。会重命名全部分区子表名，比如：
alter table sales rename to globalsales;相关的分区子表名变为：
globalsales_1_prt_1_2_prt_11_3_prt_other_regions也能够将顶级分区名改为自己定义的名称，比如：
alter table sales rename partition for (2017) to y2017;表对象名的最大长度为64字节，超长会报错：
db1=# alter table globalsales rename partition for (2017) to year2017; ERROR: relation name "globalsales_1_prt_year2017_2_prt_other_months_3_prt_other_regions" for child partition is too long当使用ALTER TABLE...PARTITION 命令改动分区表时。总是用分区名称（如y2017）而不是分区相应的表对象全名（globalsales_1_prt_y2017）。
4. 删除分区
ALTER TABLE命令也可用来删除分区，假设被删除的分区有子分区。则这些子分区及其数据也都被一起删除。
alter table globalsales drop partition for (2017); alter table globalsales drop partition for (2018);不能删除最后一个分区：
db1=# alter table globalsales drop partition for (2019); ERROR: cannot drop partition for value (2019) of relation "globalsales" -- only one remains HINT: Use DROP TABLE "globalsales" to remove the table and the final partition
5. 清空分区
使用ALTER TABLE命令清空一个分区及其全部子分区的数据。
不能单独清空一个子分区。
alter table globalsales truncate partition for (2018);
6. 分区交换
分区交换指的是用一个表的数据与一个分区的数据交换。
HAWQ仅仅支持单级分区表的分区交换。
db1=# alter table sales exchange partition for (2017) db1-# with table stage_sales; ERROR: cannot EXCHANGE PARTITION for relation "sales" -- partition has children常常使用分区交换向分区表装载数据。当然也能使用COPY或INSERT命令向分区表装载数据，此时数据被自己主动路由到正确的底层分区，就像普通表一样。可是。这样的装载数据的方法会依据数据遍历整个分区层次结构，因此数据装载的性能非常差。在前面208个分区的样例中，插入一条记录居然用时16秒多，如图8所看到的。
图8
向分区表装载数据的推荐方法创建一个中间过渡表，装载过渡表，然后用过渡表与分区做交换。db1=# create table sales (id int, year int, month int, day int, region varchar(10))
db1-# distributed by (id)
db1-# partition by range (year)
db1-# ( start (2017) end (2020) every (1));
NOTICE: CREATE TABLE will create partition "sales_1_prt_1" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_3" for table "sales"
CREATE TABLE
Time: 497.864 ms
db1=# insert into sales values (1,2017,1,1,'北京');
INSERT 0 1
Time: 463.546 ms
db1=# insert into sales values (2,2018,2,2,'上海');
INSERT 0 1
Time: 133.454 ms
db1=# insert into sales values (3,2019,3,3,'广州');
INSERT 0 1
Time: 109.118 ms
db1=# create table stage_sales (like sales);
NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table
CREATE TABLE
Time: 130.794 ms
db1=# \dt;
List of relations
Schema | Name | Type | Owner | Storage
--------+---------------+-------+---------+-------------
public | sales | table | gpadmin | append only
public | sales_1_prt_1 | table | gpadmin | append only
public | sales_1_prt_2 | table | gpadmin | append only
public | sales_1_prt_3 | table | gpadmin | append only
public | stage_sales | table | gpadmin | append only
(5 rows)
db1=# insert into stage_sales values (4,2017,4,4,'深圳');
INSERT 0 1
Time: 1559.465 ms
db1=# alter table sales exchange partition for (2017) with table stage_sales;
ALTER TABLE
Time: 61.744 ms
db1=# select * from sales;
id | year | month | day | region
----+------+-------+-----+--------
2 | 2018 | 2 | 2 | 上海
3 | 2019 | 3 | 3 | 广州
4 | 2017 | 4 | 4 | 深圳
(3 rows)
Time: 91.150 ms
db1=# select * from stage_sales;
id | year | month | day | region
----+------+-------+-----+--------
1 | 2017 | 1 | 1 | 北京
(1 row)
Time: 82.853 ms7. 分裂分区
分裂分区指的是将一个分区分裂成两个分区，HAWQ仅仅能分裂单级分区表。
db1=# alter table sales split partition for (2017) db1-# at (2016) db1-# into (partition y016, partition y2017); ERROR: cannot split partition with child partitions HINT: Try splitting the child partitions.以下的样例将2017年1月的分区，切割成2017年1月1日到2017年1月15日、2017年1月16日到2017年1月31日两个分区，切割值包括在后一个分区中。
db1=# create table sales (id int, date date, amt decimal(10,2))
db1-# distributed by (id)
db1-# partition by range (date)
db1-# ( partition p201701 start (date '2017-01-01') inclusive ,
db1(# partition p201702 start (date '2017-02-01') inclusive
db1(# end (date '2017-03-01') exclusive );
NOTICE: CREATE TABLE will create partition "sales_1_prt_p201701" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_p201702" for table "sales"
CREATE TABLE
Time: 274.237 ms
db1=# insert into sales values (1, date '2017-01-15', 100);
INSERT 0 1
Time: 386.221 ms
db1=# insert into sales values (1, date '2017-01-16', 100);
INSERT 0 1
Time: 146.437 ms
db1=# select * from sales_1_prt_p201701;
id | date | amt
----+------------+--------
1 | 2017-01-15 | 100.00
1 | 2017-01-16 | 100.00
(2 rows)
Time: 117.187 ms
db1=# alter table sales split partition for ('2017-01-01') at ('2017-01-16')
db1-# into (partition p20170101to0115, partition p20170116to0131);
NOTICE: exchanged partition "p201701" of relation "sales" with relation "pg_temp_68011"
NOTICE: dropped partition "p201701" for relation "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_p20170101to0115" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_p20170116to0131" for table "sales"
ALTER TABLE
Time: 446.998 ms
db1=# select * from sales_1_prt_p20170101to0115;
id | date | amt
----+------------+--------
1 | 2017-01-15 | 100.00
(1 row)
Time: 132.169 ms
db1=# select * from sales_1_prt_p20170116to0131;
id | date | amt
----+------------+--------
1 | 2017-01-16 | 100.00
(1 row)
Time: 86.589 ms 假设表有DEFAULT分区，必须使用分裂分区的方法加入分区。使用INTO子句的第二个分区为DEFAULT分区。db1=# alter table sales add default partition other;
NOTICE: CREATE TABLE will create partition "sales_1_prt_other" for table "sales"
ALTER TABLE
Time: 134.470 ms
db1=# insert into sales values (3, date '2017-03-01', 100);
INSERT 0 1
Time: 242.053 ms
db1=# insert into sales values (4, date '2017-04-01', 100);
INSERT 0 1
Time: 147.235 ms
db1=# select * from sales_1_prt_other;
id | date | amt
----+------------+--------
4 | 2017-04-01 | 100.00
3 | 2017-03-01 | 100.00
(2 rows)
Time: 79.584 ms
db1=# alter table sales split default partition
db1-# start ('2017-03-01') inclusive
db1-# end ('2017-04-01') exclusive
db1-# into (partition p201703, default partition);
NOTICE: exchanged partition "other" of relation "sales" with relation "pg_temp_68051"
NOTICE: dropped partition "other" for relation "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_p201703" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_other" for table "sales"
ALTER TABLE
Time: 756.526 ms
db1=# select * from sales_1_prt_p201703;
id | date | amt
----+------------+--------
3 | 2017-03-01 | 100.00
(1 row)
Time: 89.353 ms
db1=# select * from sales_1_prt_other;
id | date | amt
----+------------+--------
4 | 2017-04-01 | 100.00
(1 row)
Time: 69.030 ms8. 改动子分区模板
ALTER TABLE SET SUBPARTITION TEMPLATE改动一个分区表的子分区模板。新模板仅仅影响后面加入的数据，不改动现有的分区数据。
db1=# create table sales (trans_id int, date date, amount decimal(9,2), region text)
db1-# distributed by (trans_id)
db1-# partition by range (date)
db1-# subpartition by list (region)
db1-# subpartition template
db1-# ( subpartition usa values ('usa'),
db1(# subpartition asia values ('asia'),
db1(# subpartition europe values ('europe'),
db1(# default subpartition other_regions )
db1-# ( start (date '2017-01-01') inclusive
db1(# end (date '2017-04-01') exclusive
db1(# every (interval '1 month') );
NOTICE: CREATE TABLE will create partition "sales_1_prt_1" for table "sales"
...
CREATE TABLE
Time: 623.565 ms
db1=# alter table sales set subpartition template
db1-# ( subpartition usa values ('usa'),
db1(# subpartition asia values ('asia'),
db1(# subpartition europe values ('europe'),
db1(# subpartition africa values ('africa'),
db1(# default subpartition regions );
NOTICE: replacing level 1 subpartition template specification for relation "sales"
ALTER TABLE
Time: 49.767 ms
当加入一个分区时。使用新的子分区模板。db1=# alter table sales add partition "4"
db1-# start ('2017-04-01') inclusive
db1-# end ('2017-05-01') exclusive ;
NOTICE: CREATE TABLE will create partition "sales_1_prt_4" for table "sales"
...
ALTER TABLE
Time: 414.251 ms
db1=# \dt sales*
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------------------------------+-------+---------+-------------
public | sales | table | gpadmin | append only
public | sales_1_prt_1 | table | gpadmin | append only
public | sales_1_prt_1_2_prt_asia | table | gpadmin | append only
public | sales_1_prt_1_2_prt_europe | table | gpadmin | append only
public | sales_1_prt_1_2_prt_other_regions | table | gpadmin | append only
public | sales_1_prt_1_2_prt_usa | table | gpadmin | append only
public | sales_1_prt_2 | table | gpadmin | append only
public | sales_1_prt_2_2_prt_asia | table | gpadmin | append only
public | sales_1_prt_2_2_prt_europe | table | gpadmin | append only
public | sales_1_prt_2_2_prt_other_regions | table | gpadmin | append only
public | sales_1_prt_2_2_prt_usa | table | gpadmin | append only
public | sales_1_prt_3 | table | gpadmin | append only
public | sales_1_prt_3_2_prt_asia | table | gpadmin | append only
public | sales_1_prt_3_2_prt_europe | table | gpadmin | append only
public | sales_1_prt_3_2_prt_other_regions | table | gpadmin | append only
public | sales_1_prt_3_2_prt_usa | table | gpadmin | append only
public | sales_1_prt_4 | table | gpadmin | append only
public | sales_1_prt_4_2_prt_africa | table | gpadmin | append only
public | sales_1_prt_4_2_prt_asia | table | gpadmin | append only
public | sales_1_prt_4_2_prt_europe | table | gpadmin | append only
public | sales_1_prt_4_2_prt_regions | table | gpadmin | append only
public | sales_1_prt_4_2_prt_usa | table | gpadmin | append only
(22 rows) 以下的命令移除子分区模板：alter table sales set subpartition template ();
9. 分区滚动窗体维护
在对数据表进行范围分区处理的基础上，能够进一步设计滚动数据的策略。通过维护一个数据滚动窗体，删除老分区，加入新分区。将老分区的数据迁移到数据仓库以外的次级存储，以节省系统开销。以下以一个常见的应用场景说明分区自己主动滚动的实现。假设一个数据仓库保留近期一年的销售记录，按日期每天一个分区。初始建立一年的分区，并装载近一年的数据，然后每天装载前一天的销售数据。
（1）建立分区表
create table sales (id int, date date, amt decimal(10,2)) distributed by (id) partition by range (date) ( start (date '2016-03-01') inclusive end (date '2017-05-01') exclusive every (interval '1 day') );该语句建立了从2016-03-01至2017-04-30的每天一个分区。
（2）创建动态滚动分区的函数
HAWQ从PostgreSQL继承了过程化编程。并使用多种语言。在我自己使用过的SQL-on-Hadoop产品中。HAWQ是唯一支持过程化编程的。而且，其内建函数、操作符和语法与Oracle极为接近，这对于传统数据库的开发管理人员及广大DBA是非常有吸引力的。在转到大数据平台时，他们能够复用原来积淀的知识与经验。仅凭这一点。HAWQ就能够在众多SQL-on-Hadoop解决方式中体现出独有的优势和价值。下一篇将具体描写叙述HAWQ的过程化语言编程。
create or replace function fn_rolling_partition() returns int
as $body$
declare
oldest_month_first_day date := date(date_trunc('month',current_date) + interval '-13 month');
oldest_month_last_day date := date(date_trunc('month',current_date) + interval '-12 month - 1 day');
newest_month_first_day date := date(date_trunc('month',current_date) + interval '1 month');
newest_month_last_day date := date(date_trunc('month',current_date) + interval '2 month - 1 day');
i int;
j int;
sqlstring varchar(1000);
begin
-- 转储最早一个月的数据，
sqlstring = 'copy (select * from sales where date >= date(''' || oldest_month_first_day || ''') and date <= date(''' || oldest_month_last_day || ''')) to ''/home/gpadmin/sales_' || to_char(oldest_month_first_day,'YYYYMM') || '.txt'' with delimiter ''|'';';
execute sqlstring;
-- raise notice '%', sqlstring;
-- 删除最早月份相应的分区
i := 1;
j := oldest_month_last_day - oldest_month_first_day + 1;
for i in 1 .. j loop
sqlstring := 'alter table sales drop partition for (rank('|| i ||'));';
execute sqlstring;
end loop;
-- 添加下一个月份的新分区
while newest_month_first_day <= newest_month_last_day loop
sqlstring := 'alter table sales add partition start (date '''|| newest_month_first_day ||''') inclusive end (date '''|| (newest_month_first_day + 1) ||''') exclusive;';
execute sqlstring;
-- raise notice '%', sqlstring;
newest_month_first_day = newest_month_first_day + 1;
end loop;
-- 正常返回1
return 1;
-- 异常返回0
exception when others then
raise exception '%: %', sqlstate, sqlerrm;
return 0;
end
$body$ language plpgsql;（3）在cron中添加作业。比如，从下个月開始的每月一日两点运行分区滚动。
0 2 1 * * psql -d db1 -c "select fn_rolling_partition();" > rolling_partition.log 2>&1
