• Greenplum-表分区

    在前面Greenplum-表的分布策略文章中，我们学习了Greenplum的几种表分布的策略，包括HASH分布、随机分布、复制表三种分布策略。
    表的分布策略可以实现表的数据能否均匀的分布在不同的Segment实例中。除了表的分布策略以外，Greenplum还支持对表进行分区。
    分区是在每个Segment内部的行为，将表创建为分区表后，表会拆分为ROOT表和子分区表，数据都存放在子分区表中，ROOT表不存储数据。

    Greenplum中的分区有两种方式：list（列表）分区及range（范围）分区。这两个分区方式可以随意组合形成多级分区。

    range（范围）分区

    通过PARTITION BY RANGE语法来定义

    示例1：

    =# CREATE TABLE sales
( id int, 
date date, 
amt decimal(10,2)
) DISTRIBUTED BY (id) PARTITION BY RANGE (date) (
 START (date '2020-01-01') INCLUSIVE
 END (date '2021-01-01') EXCLUSIVE EVERY (INTERVAL '1' month') );
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"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_5" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_6" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_7" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_8" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_9" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_10" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_11" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_12" for table "sales"
CREATE TABLE
qianbase=# \d+ sales
Table "public.sales"
Column |     Type      | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
id     | integer       |           | plain   |              |
date   | date          |           | plain   |              |
amt    | numeric(10,2) |           | main    |              |
Child tables: sales_1_prt_1,
sales_1_prt_10,
sales_1_prt_11,
sales_1_prt_12,
sales_1_prt_2,
sales_1_prt_3,
sales_1_prt_4,
sales_1_prt_5,
sales_1_prt_6,
sales_1_prt_7,
sales_1_prt_8,
sales_1_prt_9
Distributed by: (id)
Partition by: (date)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    示例2：

    =# CREATE TABLE rank
 ( id int, 
 rank int, 
 year int,
 gender char(1),
 count int
 ) DISTRIBUTED BY (id) PARTITION BY RANGE (year) (
 START (2020) END (2021) EVERY (1),
 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
qianbase=# \d+ rank
Table "public.rank"
Column |     Type     | Modifiers | Storage  | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
id     | integer      |           | plain    |              |
rank   | integer      |           | plain    |              |
year   | integer      |           | plain    |              |
gender | character(1) |           | extended |              |
count  | integer      |           | plain    |              |
Child tables: rank_1_prt_2,
rank_1_prt_extra
Distributed by: (id)
Partition by: (year)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    list（列表）分区

    通过PARTITION BY LIST语法来定义
    注：列表分区可以使用多个 COLUMN组合起来作为分区字段，而范围分区只允许使用单独 COLUMN 作为分区字段
    示例：

    =# CREATE TABLE customer
( id int, name
 varchar(32), birthday
 date, gender char(1),
 viplevel int
 ) DISTRIBUTED BY (id) PARTITION BY LIST (gender,viplevel) (
 PARTITION girls1 VALUES (('F',1)),
 PARTITION girls2 VALUES (('F',2)),
 PARTITION boys1 VALUES (('M',1)),
 PARTITION boys2 VALUES (('M',2)),
 DEFAULT PARTITION other
 );
NOTICE:  CREATE TABLE will create partition "customer_1_prt_girls1" for table "customer"
NOTICE:  CREATE TABLE will create partition "customer_1_prt_girls2" for table "customer"
NOTICE:  CREATE TABLE will create partition "customer_1_prt_boys1" for table "customer"
NOTICE:  CREATE TABLE will create partition "customer_1_prt_boys2" for table "customer"
NOTICE:  CREATE TABLE will create partition "customer_1_prt_other" for table "customer"
CREATE TABLE
qianbase=# \d+ customer
Table "public.customer"
Column  |         Type          | Modifiers | Storage  | Stats target | Description
----------+-----------------------+-----------+----------+--------------+-------------
id       | integer               |           | plain    |              |
name     | character varying(32) |           | extended |              |
birthday | date                  |           | plain    |              |
gender   | character(1)          |           | extended |              |
viplevel | integer               |           | plain    |              |
Child tables: customer_1_prt_boys1,
customer_1_prt_boys2,
customer_1_prt_girls1,
customer_1_prt_girls2,
customer_1_prt_other
Distributed by: (id)
Partition by: (gender, viplevel)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    多级分区

    通过SUBPARTITION BY及SUBPARTITION TEMPLATE等关键字定义
    list分区和range分区可以组合形成多级分区（正常情况下几乎不需要使用）。

    示例：

    =# 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 '2020-01-01') INCLUSIVE
END (date '2021-01-01') EXCLUSIVE 
EVERY (INTERVAL '1 month'),  
DEFAULT PARTITION outlying_dates
);
NOTICE:  CREATE TABLE will create partition "sales_1_prt_outlying_dates" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_outlying_dates_2_prt_usa" for table "sales_1_prt_outlying_dates"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_outlying_dates_2_prt_asia" for table "sales_1_prt_outlying_dates"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_outlying_dates_2_prt_europe" for table "sales_1_prt_outlying_dates"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_outlying_dates_2_prt_other_regions" for table "sales_1_prt_outlying_dates"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_usa" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_asia" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_europe" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_regions" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_usa" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_asia" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_europe" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_other_regions" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_usa" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_asia" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_europe" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_other_regions" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_5" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_5_2_prt_usa" for table "sales_1_prt_5"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_5_2_prt_asia" for table "sales_1_prt_5"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_5_2_prt_europe" for table "sales_1_prt_5"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_5_2_prt_other_regions" for table "sales_1_prt_5"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_6" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_6_2_prt_usa" for table "sales_1_prt_6"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_6_2_prt_asia" for table "sales_1_prt_6"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_6_2_prt_europe" for table "sales_1_prt_6"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_6_2_prt_other_regions" for table "sales_1_prt_6"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_7" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_7_2_prt_usa" for table "sales_1_prt_7"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_7_2_prt_asia" for table "sales_1_prt_7"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_7_2_prt_europe" for table "sales_1_prt_7"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_7_2_prt_other_regions" for table "sales_1_prt_7"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_8" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_8_2_prt_usa" for table "sales_1_prt_8"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_8_2_prt_asia" for table "sales_1_prt_8"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_8_2_prt_europe" for table "sales_1_prt_8"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_8_2_prt_other_regions" for table "sales_1_prt_8"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_9" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_9_2_prt_usa" for table "sales_1_prt_9"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_9_2_prt_asia" for table "sales_1_prt_9"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_9_2_prt_europe" for table "sales_1_prt_9"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_9_2_prt_other_regions" for table "sales_1_prt_9"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_10" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_10_2_prt_usa" for table "sales_1_prt_10"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_10_2_prt_asia" for table "sales_1_prt_10"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_10_2_prt_europe" for table "sales_1_prt_10"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_10_2_prt_other_regions" for table "sales_1_prt_10"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_11" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_11_2_prt_usa" for table "sales_1_prt_11"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_11_2_prt_asia" for table "sales_1_prt_11"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_11_2_prt_europe" for table "sales_1_prt_11"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_11_2_prt_other_regions" for table "sales_1_prt_11"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_12" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_12_2_prt_usa" for table "sales_1_prt_12"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_12_2_prt_asia" for table "sales_1_prt_12"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_12_2_prt_europe" for table "sales_1_prt_12"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_12_2_prt_other_regions" for table "sales_1_prt_12"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_13" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_13_2_prt_usa" for table "sales_1_prt_13"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_13_2_prt_asia" for table "sales_1_prt_13"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_13_2_prt_europe" for table "sales_1_prt_13"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_13_2_prt_other_regions" for table "sales_1_prt_13"
CREATE TABLE
qianbase=# \d+ sales
Table "public.sales"
Column  |     Type     | Modifiers | Storage  | Stats target | Description
----------+--------------+-----------+----------+--------------+-------------
trans_id | integer      |           | plain    |              |
date     | date         |           | plain    |              |
amount   | numeric(9,2) |           | main     |              |
region   | text         |           | extended |              |
Child tables: sales_1_prt_10,
sales_1_prt_11,
sales_1_prt_12,
sales_1_prt_13,
sales_1_prt_2,
sales_1_prt_3,
sales_1_prt_4,
sales_1_prt_5,
sales_1_prt_6,
sales_1_prt_7,
sales_1_prt_8,
sales_1_prt_9,
sales_1_prt_outlying_dates
Distributed by: (trans_id)
Partition by: (date)
qianbase=# \d+ sales_1_prt_2
Table "public.sales_1_prt_2"
Column  |     Type     | Modifiers | Storage  | Stats target | Description
----------+--------------+-----------+----------+--------------+-------------
trans_id | integer      |           | plain    |              |
date     | date         |           | plain    |              |
amount   | numeric(9,2) |           | main     |              |
region   | text         |           | extended |              |
Check constraints:
"sales_1_prt_2_check" CHECK (date >= '2020-01-01'::date AND date < '2020-02-01'::date)
Inherits: sales
Child tables: sales_1_prt_2_2_prt_asia,
sales_1_prt_2_2_prt_europe,
sales_1_prt_2_2_prt_other_regions,
sales_1_prt_2_2_prt_usa
Distributed by: (trans_id)
Partition by: (region)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126

    注意事项

    1. 已经创建的单分区表不能修改为分区表
    2. 分区表中任何一个层次分区最多只能有32767个子分区
    3. 复制表不能进行分区
    4. 分区交换只能与普通表进行交换
  • 相关阅读:
    可靠性工程师的发展之路
    如何链接多个modbus_tcp设备，并将设备数据写入同一个modbusSlave，以便外部客户端获取所有链接设备的数据。
    Go的全新漏洞检测工具govulncheck来了
    OSPF高级配置——虚接口，NSSA
    记一次服务器异常掉电，导致HBase Master is initializing 问题处理
    国外LEAD赚钱，做个网站真的很简单
    [每周一更]-(第22期)：什么是gRPC?
    逻辑运算符
    Linux下jar包的运行、查看、终止
    闲杂篇（一）vs2017 c语言、c++从一个函数返回多个参数给主函数|c++函数之间结构体如何传递参数
  • 原文地址：https://blog.csdn.net/Post_Yuan/article/details/126870914