用HAWQ轻松取代传统数据仓库八大表分区资料.docx
《用HAWQ轻松取代传统数据仓库八大表分区资料.docx》由会员分享，可在线阅读，更多相关《用HAWQ轻松取代传统数据仓库八大表分区资料.docx（33页珍藏版）》请在冰豆网上搜索。
用HAWQ轻松取代传统数据仓库八大表分区资料
用HAWQ轻松取代传统数据仓库（八）——大表分区
一、HAWQ中的分区表
与大多数关系数据库一样，HAWQ也支持分区表。
这里所说的分区表是指HAWQ的内部分区表，外部分区表在后面“外部数据”篇讨论。
在数据仓库应用中，事实表通常有非常多的记录，分区可以将这样的大表在逻辑上分为小的、更易管理的数据片段。
HAWQ的优化器支持分区消除以提高查询性能。
只要查询条件中可以使用分区键作为过滤条件，那么HAWQ只需要扫描满足查询条件的分区，而不必进行全表扫描。
分区并不改变表数据在segment间的物理分布。
表的分布是物理的，无论是分区表还是非分区表，HAWQ都会在segment上物理地分布数据，并且并行处理查询。
而表的分区是逻辑上的，HAWQ逻辑分隔大表以提高查询性能和数据仓库应用的可维护性。
例如，将老的分区数据从数据仓库转储或移除，并建立新的数据分区等。
HAWQ支持以下分区类型：
范围分区：
基于数字范围分区，如日期、价格等。
列表分区：
基于列表值分区，如销售区域、产品分类等。
两者混合的分区类型。
图1是一个混合类型分区表的例子，sales表以销售日期范围作为主分区，而以销售区域作为一个日期分区中的列表子分区键。
注意，HAWQ并没提供类似Oracle的在线重定义功能，它只能使用CREATETABLE命令创建分区表，而没有简单的命令能够将一个非分区表转化成分区表。
最好在建表之前就规划好分区方式和维护方法，因为当一个非分区表已经存在大量数据后再改作分区表的操作，时间和空间消耗上都是很棘手的问题。
在CREATETABLE命令中使用PARTITIONBY或可选的SUBPARTITIONBY子句建立分区。
上级分区可以包含一个或多个下级分区。
HAWQ内部创建上下级分区之间的层次关系。
分区条件定义一个分区内可以包含的数据。
在建立分区表时，HAWQ为每个分区条件创建一个唯一的CHECK约束，限制一个分区所能含有的数据，保证各个分区中数据的互斥性。
查询优化器利用该CHECK约束，决定扫描哪些分区以满足查询谓词条件。
HAWQ在系统目录中存储分区的层次信息，因此插入到分区表中的行可以正确传递到子分区中。
ALTERTABLE命令的PARTITION子句用于修改分区表结构。
在向分区表插入数据时，可以在INSERT命令中指定表的根分区或叶分区。
如果数据对于指定的叶分区无效，将返回错误。
INSERT命令不支持向非叶分区的子分区中插入数据。
二、确定分区策略
并不是所有表都适合分区，需要进行实测以保证所期望的性能提升。
下面是一些通用的分区指南，如果对以下问题的大部分答案是肯定的，分区表对于提高性能是可行的数据库设计。
否则，表不适合分区。
表是否足够大？
按照一般的经验，至少千万记录以上的表才算大表。
数据仓库中的事实表适合作为分区表。
对于小于这个数量级的表通常不需要分区。
因为系统管理与维护分区的开销会抵消掉分区带来的可见的性能优势。
性能是否不可接受？
只有当实施了其它优化手段后，响应时间仍然不可接受时，再考虑使用分区。
查询谓词条件中是否包含适合的分区键？
检查查询的WHERE子句中是否包含适合作为分区的条件。
例如，如果大部分查询都通过日期检索数据，那么按照月或周做范围分区可能是有益的。
是否需要维护一个数据仓库的历史数据窗口？
例如，组织中的数据仓库只需要保持过去12个月的数据，那么按月分区，就可以很容易地删除最老月份的分区，并向最新的月分区中装载当前数据。
根据分区定义条件，是否每个分区的数据量比较平均？
分区条件应尽可能使数据平均划分。
如果每个分区包含基本相同的记录数，性能会有所提升。
例如，将一个大表分成10个相等的分区，如果查询条件中带有分区键，那么理论上查询应该比非分区表快将近10倍。
使用分区还要注意以下问题。
首先，不要创建多余的分区。
太多的分区将会减慢管理和维护任务，如检查磁盘使用、集群扩展、释放剩余空间等。
其次，只有在查询条件可以利用分区消除时，性能才会得到提升。
否则，一个需要扫描所有分区的查询会比非分区表还慢。
可以通过查看一个查询的执行计划（explainplan）确认是否用到了分区消除。
最后是关于多级分区的问题。
多级分区会使分区文件的数量快速增长。
例如，如果一个表按日期和城市做分区，1000天的1000个城市的数据，就会形成100万个分区。
假设表有100列，并且假设表使用面向列的物理存储格式，那么系统为此表需要管理1亿个文件。
三、创建分区表
如前所述，创建分区表需要定义分区键、分区类型、分区层次。
下面是几个创建分区表的例子。
1.定义日期范围分区表
在定义日期分区表时，可以考虑以可接受的细节粒度做分区。
例如，相对于以月份做主分区，日期做子分区的分区策略，每个日期一个分区，一年365个分区的方案可能更好。
多级分区可以降低生成查询计划的时间，但平面化的分区设计运行地更快。
[sql]viewplaincopy在CODE上查看代码片派生到我的代码片
createtablesales（idint,datedate,amtdecimal（10,2））
distributedby（id）
partitionbyrange（date）
（start（date'2017-01-01'）inclusive
end（date'2017-02-01'）exclusive
every（interval'1day'））;
上面的语句以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'）anddate<（date'2017-01-02'）的数据，sales_1_prt_31包含date>=（date'2017-01-31'）anddate<（date'2017-02-01'）的数据，即这个语句定义的分区是左闭右开的数据区间。
[sql]viewplaincopy在CODE上查看代码片派生到我的代码片
db1=#insertintosalesvalues（1,（date'2016-12-31'）,100）;
ERROR:
nopartitionforpartitioningkey（seg21hdp4:
40000pid=60186）
db1=#insertintosalesvalues（1,（date'2017-01-01'）,100）;
INSERT01
db1=#insertintosalesvalues（1,（date'2017-02-01'）,100）;
ERROR:
nopartitionforpartitioningkey（seg23hdp4:
40000pid=60190）
db1=#insertintosalesvalues（1,（date'2017-01-31'）,100）;
INSERT01
同样可以定义左开右闭的分区。
[sql]viewplaincopy在CODE上查看代码片派生到我的代码片
createtablesales（idint,datedate,amtdecimal（10,2））
distributedby（id）
partitionbyrange（date）
（start（date'2017-01-01'）exclusive
end（date'2017-02-01'）inclusive
every（interval'1day'））;
db1=#insertintosalesvalues（1,（date'2017-01-01'）,100）;
ERROR:
nopartitionforpartitioningkey（seg19hdp4:
40000pid=60182）
db1=#insertintosalesvalues（1,（date'2017-01-02'）,100）;
INSERT01
db1=#insertintosalesvalues（1,（date'2017-01-31'）,100）;
INSERT01
db1=#insertintosalesvalues（1,（date'2017-02-01'）,100）;
INSERT01
db1=#insertintosalesvalues（1,（date'2017-02-02'）,100）;
ERROR:
nopartitionforpartitioningkey（seg23hdp4:
40000pid=60269）
也可以显式定义每个分区。
[sql]viewplaincopy在CODE上查看代码片派生到我的代码片
createtablesales（idint,datedate,amtdecimal（10,2））
distributedby（id）
partitionbyrange（date）
（partitionp201701start（date'2017-01-01'）inclusive,
partitionp201702start（date'2017-02-01'）inclusive,
partitionp201703start（date'2017-03-01'）inclusive,
partitionp201704start（date'2017-04-01'）inclusive,
partitionp201705start（date'2017-05-01'）inclusive,
partitionp201706start（date'2017-06-01'）inclusive,
partitionp201707start（date'2017-07-01'）inclusive,
partitionp201708start（date'2017-08-01'）inclusive,
partitionp201709start（date'2017-09-01'）inclusive,
partitionp201710start（date'2017-10-01'）inclusive,
partitionp201711start（date'2017-11-01'）inclusive,
partitionp201712start（date'2017-12-01'）inclusive
end（date'2018-01-01'）exclusive）;
以上语句为2017年每个月建立一个分区。
注意，不需要问每个分区指定END值，只要在最后一个分区（本例中的p201712）指定END值即可。
2.定义数字范围分区表
[plain]viewplaincopy在CODE上查看代码片派生到我的代码片
db1=#createtablerank（idint,rankint,yearint,gender
db1（#char
（1）,countint）
db1-#distributedby（id）
db1-#partitionbyrange（year）
db1-#（start（2017）end（2018）every
（1）,
db1（#defaultpartitionextra）;
NOTICE:
CREATETABLEwillcreatepartition"rank_1_prt_extra"fortable"rank"
NOTICE:
CREATETABLEwillcreatepartition"rank_1_prt_2"fortable"rank"
CREATETABLE
db1=#\dt
Listofrelations
Schema|Name|Type|Owner|Storage
--------+------------------+-------+---------+-------------
public|rank|table|gpadmin|appendonly
public|rank_1_prt_2|table|gpadmin|appendonly
public|rank_1_prt_extra|table|gpadmin|appendonly
（3rows）
db1=#insertintorankvalues（1,1,2016,'M',100）;
INSERT01
db1=#insertintorankvalues（1,1,2017,'M',100）;
INSERT01
db1=#insertintorankvalues（1,1,2018,'M',100）;
INSERT01
db1=#insertintorankvalues（1,1,2019,'M',100）;
INSERT01
db1=#select*fromrank;
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
（4rows）
db1=#select*fromrank_1_prt_2;
id|rank|year|gender|count
----+------+------+--------+-------
1|1|2017|M|100
（1row）
db1=#select*fromrank_1_prt_extra;
id|rank|year|gender|count
----+------+------+--------+-------
1|1|2016|M|100
1|1|2018|M|100
1|1|2019|M|100
（3rows）
db1=#droptablerank;
DROPTABLE
db1=#\dt
Norelationsfound.
从上面的例子看到：
HAWQ缺省的分区范围是左闭右开。
可以使用defaultpartition子句增加一个缺省分区，当数据不被包含在任何明确定义的分区时，可以被包含在缺省分区中。
HAWQ在查询时可以将分区当做表看待，但删除主表后，分区被一并删除。
3.定义列表分区表
列表分区可以使用任何允许等值比较数据类型的列作为分区键。
列表分区表必须显式定义每个分区。
注意列表中的字符比较区分大小写。
[plain]viewplaincopy在CODE上查看代码片派生到我的代码片
db1=#createtablerank（idint,rankint,yearint,gender
db1（#char
（1）,countint）
db1-#distributedby（id）
db1-#partitionbylist（gender）
db1-#（partitiongirlsvalues（'f'）,
db1（#partitionboysvalues（'m'）,
db1（#defaultpartitionother）;
NOTICE:
CREATETABLEwillcreatepartition"rank_1_prt_girls"fortable"rank"
NOTICE:
CREATETABLEwillcreatepartition"rank_1_prt_boys"fortable"rank"
NOTICE:
CREATETABLEwillcreatepartition"rank_1_prt_other"fortable"rank"
CREATETABLE
db1=#\dt
Listofrelations
Schema|Name|Type|Owner|Storage
--------+------------------+-------+---------+-------------
public|rank|table|gpadmin|appendonly
public|rank_1_prt_boys|table|gpadmin|appendonly
public|rank_1_prt_girls|table|gpadmin|appendonly
public|rank_1_prt_other|table|gpadmin|appendonly
（4rows）
db1=#insertintorankvalues（1,1,2016,'M',100）;
INSERT01
db1=#insertintorankvalues（1,1,2016,'m',100）;
INSERT01
db1=#insertintorankvalues（1,1,2016,'f',100）;
INSERT01
db1=#insertintorankvalues（1,1,2016,'F',100）;
INSERT01
db1=#insertintorankvalues（1,1,2016,'A',100）;
INSERT01
db1=#select*fromrank;
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
（5rows）
db1=#select*fromrank_1_prt_boys;
id|rank|year|gender|count
----+------+------+--------+-------
1|1|2016|m|100
（1row）
db1=#select*fromrank_1_prt_girls;
id|rank|year|gender|count
----+------+------+--------+-------
1|1|2016|f|100
（1row）
db1=#select*fromrank_1_prt_other;
id|rank|year|gender|count
----+------+------+--------+-------
1|1|2016|M|100
1|1|2016|F|100
1|1|2016|A|100
（3rows）
HAWQ不支持多分区键列复合比较，分区键只能是单列。
[plain]viewplaincopy在CODE上查看代码片派生到我的代码片
db1=#createtablerank（idint,rankint,yearint,gender
db1（#char
（1）,countint）
db1-#distributedby（id）
db1-#partitionbylist（gender,year）
db1-#（partitiongirlsvalues（'f',2017）,
db1（#partitionboysvalues（'m',2018）,
db1（#defaultpartitionother）;
ERROR:
Compositepartitionkeysarenotallowed
4.定义多级分区
可以在分区中定义子分区。
使用subpartitiontemplate子句保证每个分区都有相同的子分区定义，包括以后添加的分区。
[sql]viewplaincopy在CODE上查看代码片派生到我的代码片
createtablesales（trans_idint,datedate,amount
decimal（9,2）,regiontext）
distributedby（trans_id）
partitionbyrange（date）
subpartitionbylist（region）
subpartitiontemplate
（subpartitionusavalues（'usa'）,
subpartitionasiavalues（'asia'）,
subpartitioneuropevalues（'europe'）,
defaultsubpartitionother_regions）
（start（date'2017-01-01'）inclusive
end（date'2018-01-01'）exclusive
every（interval'1month'）,
defaultpartitionoutlying_dates）;
以上语句建立了一共65个分区。
一级分区13个，每个一级分区包含4个子分区。
下面的例子显示了一个树形分区设计。
sales表按年、月、地区的层级三级分区。
SUBPARTITIONTEMPLATE子句保证每个分区都有相同的子分区结构。
例子中的每一层级都指定了缺省的分区。
[sql]viewpl
升级会员 