greenplum表分布策略 greenplum分区表操作

转载

数据小香 2024-02-08 07:23:30

文章标签 greenplum表分布策略 greenplum分区表数据分区表表分区 文章分类 云原生云计算

Greenplum分区表的原理和PostgreSQL的原理相同，都是把一张大表按照适合的维度进行分割，通过表的继承，规则，约束实现。

与PostgreSQL分区表的区别：

在PostgreSQL中，通过一个父表，多个子表来实现分区表。插入数据时，需要手动向子表插入数据，如果向父表插入数据，则直接会被插入到父表中。在GPDB中，直接向父表插入数据，便可以根据约束直接自动插入到对应的子表中，当分区子表不存在时，插入失败。

分区与分布的区别：

1.分区，按照字段逻辑进行逻辑划分的区域，比如，按照时间、区域等进行划分。
2.分布，按照字段进行物理分区，会分散到每个segment。
分布是为了提高并行查询的效率，充分利用每个segment节点的资源。分区是为了减少查询时的数据扫描，对大表维护更加方便。

Greenplum数据库中的表分区

Greenplum数据库支持：

1.范围分区：基于一个数字型范围划分数据，例如按照日期或价格划分。

2.列表分区：基于一个值列表划分数据，例如按照销售范围或产品线划分。

3.两种类型的组合。

greenplum表分布策略 greenplum分区表操作_greenplum分区表

多层分区的例子

Greenplum数据库把表划分成部分（也称为分区）来启用大规模并行处理。表分区在使用CREATE TABLE时执行PARTITION BY（以及可选SUBPARTITION BY）子句来进行分区。分区操作会创建一个顶层（父）表以及一层或者多层子表。在内部，Greenplum数据库会在顶层表和它的底层分区之间创建继承关系，类似于PostgreSQL的INHERITS子句的功能。

Greenplum使用表创建时定义的分区标准来创建每一个分区及其上一个可区分的CHECK约束，这个约束限制了该表能含有的数据。查询优化器使用CHECK约束来决定要扫描哪些表分区来满足一个给定的查询谓词。

Greenplum系统目录存储了分区层次信息，这样插入到顶层父表的行会被正确地传播到子表分区。要更改分区设计或者表结构，可使用带有PARTITION子句的ALTER TABLE修改父表。

要把数据插入到一个分过区的表中，用户需要指定根分区表，也就是用CREATE TABLE命令创建的那个表。用户也可以在INSERT命令中指定分区表的一个叶子子表。如果该数据对于指定的叶子子表不合法，则会返回一个错误。不支持在DML命令中指定一个非叶子或者非根分区表。

什么时候适合使用分区表？

在设计使用分区表时，考虑以下问题：

表是否足够大？大型的事实表是进行表划分很好的候选。
用户是否体验到不满意的性能？正如任何性能调节的动机一样，只有针对一个表的查询产生比预期还要慢的响应时间时才应该对该表分区。
用户的查询谓词有没有可识别的访问模式？检查用户的查询负载的WHERE子句并且查找一直被用来访问数据的表列。例如，如果大部分查询都倾向于用日期查找记录，那么按月或者按周的日期分区设计可能会对用户有益。或者如果用户倾向于根据地区访问记录，可考虑一种列表分区设计来根据地区划分表。
用户的数据仓库是否维护了一个历史数据的窗口？另一个分区设计的考虑是用户的组织对维护历史数据的业务需求。例如，用户的数据仓库可能要求用户保留过去十二个月的数据。如果数据按月分区，用户可以轻易地从仓库中删除最旧的月份分区并且把当前数据载入到最近的月份分区中。
数据能否基于某种定义的原则被划分成差不多相等的部分？尽可能选择将把用户的数据均匀划分的分区原则。如果分区包含基本同等数量的记录，查询性能会基于创建的分区数量而提升。

不要创建超过所需数量的分区，创建过多的分区可能会拖慢管理和维护工作，例如清理、恢复Segment、扩展集群、检查磁盘用量等等。

除非查询优化器能基于查询谓词排除一些分区，分区技术就不能改进查询性能。每个分区都扫描的查询运行起来会比表没有分区时还慢，因此如果用户的查询中很少能实现分区排除，请避免进行分区。

警告： 请对多级分区格外谨慎，因为分区文件的数量可能会增长得非常快。例如，如果一个表被按照日和城市划分并且有1,000个日以及1,000个城市，那么分区的总数就是一百万。列存表会把每一列存在一个物理表中，因此如果这个表有100个列，系统就需要为该表管理一亿个文件。

创建分区表

要对一个表分区：
1.决定分区设计：日期范围、数字范围或者值的列表。
2.选择要按哪个（哪些）列对表分区。
3.决定用户需要多少个分区级别。例如，用户可以按月创建一个日期范围分区表，然后对每个月的分区按照销售地区划分子分区。

范围分区（range）

根据分区字段的值范围区间来分区，每一个分区就是一个子表。

一个按日期范围分区的表使用单个date或者timestamp列作为分区键列。如果需要，用户可以使用同一个分区键列来创建子分区，例如按月分区然后按日建子分区。请考虑使用最细的粒度分区。例如，对于一个用日期分区的表，用户可以按日分区并且得到365个每日的分区，而不是先按年分区然后按月建子分区再然后按日建子分区。一种多级设计可能会减少查询规划时间，但是一种平面的分区设计运行得更快。

用户可以通过给出一个START值、一个END值以及一个定义分区增量值的子句让Greenplum数据库自动产生分区。默认情况下，START值总是被包括在内而END值总是被排除在外。例如：

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

快速分区（every）

根据选定的范围，跨越基数，快速分区每一个子表。

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1), 
  DEFAULT PARTITION extra );

every：指定跨越基数。

列表分区（list）

根据值的分组，相同的数据归类到一组，也就一个分区中。
一个按列表分区的表可以使用任意允许等值比较的数据类型列作为它的分区键列。一个列表分区也可以用一个多列（组合）分区键，反之一个范围分区只允许单一列作为分区键。对于列表分区，用户必须为每一个用户想要创建的分区（列表值）声明一个分区说明。例如：

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int ) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'), 
  PARTITION boys VALUES ('M'), 
  DEFAULT PARTITION other );

定义多级分区

用户可以用分区的子分区创建一种多级分区设计。使用一个子分区模板可以确保每一个分区都有相同的子分区设计，包括用户后来增加的分区。例如：

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 '2011-01-01') INCLUSIVE
   END (date '2012-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month'), 
   DEFAULT PARTITION outlying_dates );

下面的例子展示了一个三级分区设计，其中 sales表被按照year分区，然后按照 month分区，再然后按照region分区。SUBPARTITION TEMPLATE子句保证每一个年度的分区都有相同的子分区结构。这个例子在该层次的每一个级别上都声明了一个DEFAULT分区。

CREATE TABLE p3_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 (2002) END (2012) EVERY (1), 
  DEFAULT PARTITION outlying_years );

对一个现有的表进行分区

表只能在创建时被分区。如果用户有一个表想要分区，用户必须创建一个分过区的表，把原始表的数据载入到新表，再删除原始表并且把分过区的表重命名为原始表的名称。用户还必须重新授权表上的权限。例如：

CREATE TABLE sales2 (LIKE sales) 
PARTITION BY RANGE (date)
( START (date 2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month') );
INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;

载入分区表

在用户创建了分区表结构之后，顶层父表为空。数据会被路由到底层的子表分区中。在一个多级分区设计中，只有层次底部的子分区能够包含数据。

不能被映射到一个子表分区的行会被拒绝并且载入会失败。为了避免无法映射的行在载入时被拒绝，可以为用户的分区层次定义一个 DEFAULT分区。任何不匹配一个分区的CHECK约束的行会被载入到DEFAULT分区。

在运行时，查询优化器扫描整个表继承层次并使用CHECK表约束来决定要扫描哪个子表分区来满足查询的条件。DEFAULT分区（如果用户的层次中有一个）总是会被扫描。包含数据的DEFAULT分区会拖慢总体扫描时间。

当用户使用COPY或者INSERT来载入数据到父表时，数据会被自动路由到正确的分区，这就像是向一个常规表中载入数据一样。

向分区表中载入数据的最佳方法是创建一个中间状态表，把数据载入其中，然后把它交换到用户的分区设计中。

验证分区策略

当一个表基于查询谓词被分区时，用户可以使用 EXPLAIN来验证查询优化器只扫描相关的数据来检查查询计划。

例如，假设一个sales表被按日期范围分区，先用月份分区然后用地区建立子分区。对于下列查询：

EXPLAIN SELECT * FROM sales WHERE date='01-07-12' AND region='usa';

这个查询的查询计划应该展示只涉及到下列表的表扫描：
返回0-1行的默认分区（如果用户的分区设计有一个默认分区）
返回0-1行的January 2012分区（sales_1_prt_1）
返回若干行的USA地区子分区（sales_1_2_prt_usa）。
下面的例子展示了相关的查询计划片段。

->  Seq Scan on sales_1_prt_1 sales (cost=0.00..0.00 rows=0 width=0)
Filter: "date"=01-07-12::date AND region='USA'::text
->  Seq Scan onsales_1_2_prt_usa sales (cost=0.00..9.87 
rows=20 width=40)

确保查询优化器不会扫描不必要的分区或者子分区（例如，扫描没有在查询谓词中指定的月份或者地区），以及顶层表的扫描返回0-1行。

查看用户的分区设计

用户可以使用pg_partitions视图查看有关分区设计的信息。例如，要查看sales表的分区设计：

SELECT partitionboundary, partitiontablename, partitionname, 
partitionlevel, partitionrank 
FROM pg_partitions 
WHERE tablename='sales';

下列表和视图展示了关于分区表的信息。

pg_partition - 跟踪分区表以及它们的继承层次关系。
pg_partition_templates - 展示使用一个子分区模板创建的子分区。
pg_partition_columns - 显示在一个分区设计中用到的分区键列。
有关Greenplum数据库系统目录表和视图的信息，请见 Greenplum数据库参考指南。

维护分区表

要维护一个分区表，对顶层父表使用ALTER TABLE命令。最常用的情景是删除旧的分区以及增加新的分区，以此在一种范围分区设计中维护数据的一个滚动窗口。**用户可以把旧的分区转换（交换）成追加优化的压缩存储格式来节省空间。**如果在用户的分区设计中有一个默认分区，用户可以通过分裂默认分区来增加一个分区。

增加一个分区

用户可以用ALTER TABLE命令为一个分区设计增加一个分区。如果原始分区设计包括由一个子分区模板定义的子分区，新增加的分区也会根据该模板划分子分区。例如：

ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE;

如果在创建表时没有使用一个子分区模板，用户可以在增加分区时定义子分区：

ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE
      ( SUBPARTITION usa VALUES ('usa'), 
        SUBPARTITION asia VALUES ('asia'), 
        SUBPARTITION europe VALUES ('europe') );

当用户为一个现有分区增加一个子分区时，用户可以指定要更改的分区。例如：

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
      ADD PARTITION africa VALUES ('africa');

注意： 用户不能向一个具有默认分区的分区设计中增加分区。用户必须分裂默认分区来增加分区。

重命名一个分区

分区表使用下列命名习惯。分区子表的名称服从唯一性要求和长度限制。

<parentname>_<level>_prt_<partition_name>

例如：

sales_1_prt_jan16

对于自动生成的范围分区，在没有给出名称时会分配一个数字：

sales_1_prt_1

要重命名一个已分区的子表，应重命名顶层父表。在所有相关的子表分区的表名中，<parentname>都会改变。例如下面的命令：

ALTER TABLE sales RENAME TO globalsales;

会修改相关的表名：

globalsales_1_prt_1

用户可以更改一个分区的名称让它更容易标识。例如：

ALTER TABLE sales RENAME PARTITION FOR ('2016-01-01') TO jan16;

会把相关的表名改为如下：

sales_1_prt_jan16

在使用ALTER TABLE命令修改分区表时，总是用它们的分区名（jan16）而不是它们的完整表名（sales_1_prt_jan16）引用表。

注意： 表名不能是一个ALTER TABLE语句中的分区名。例如，ALTER TABLE sales…是正确的。 ALTER TABLE sales_1_part_jan16…则不被允许。

增加一个默认分区

用户可以用ALTER TABLE命令为一个分区设计增加一个默认分区。

ALTER TABLE sales ADD DEFAULT PARTITION other;

如果用户的分区设计是多级的，该层次中每一级都必须有一个默认分区。例如：

ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT 
PARTITION other;

ALTER TABLE sales ALTER PARTITION FOR (RANK(2)) ADD DEFAULT 
PARTITION other;

ALTER TABLE sales ALTER PARTITION FOR (RANK(3)) ADD DEFAULT 
PARTITION other;

如果到来的数据不匹配一个分区的CHECK约束并且没有默认分区，该数据就会被拒绝。默认分区确保到来的不匹配一个分区的数据能被插入到默认分区中。

删除一个分区

用户可以使用ALTER TABLE命令从用户的分区设计中删除一个分区。当用户删除一个具有子分区的分区时，子分区（以及其中的所有数据）也会被自动删除。对于范围分区，从范围中删除较老的分区很常见，因为旧的数据会被滚出数据仓库。例如：

ALTER TABLE sales DROP PARTITION FOR (RANK(1));

截断一个分区

用户可以使用ALTER TABLE命令截断一个分区。当用户截断一个具有子分区的分区时，子分区也会被自动截断。

ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

交换一个分区

用户可以使用ALTER TABLE命令交换一个分区。交换一个分区用一个表换掉一个现有的分区。用户只能在分区层次的最底层交换分区（只有包含数据的分区才可以被交换）。

分区交换对数据装载有用。例如，装载一个分段表并且把装载好的表换入到用户的分区设计中去。用户可以使用分区交换来把较老分区的存储类型改为追加优化表。例如：

CREATE TABLE jan12 (LIKE sales) WITH (appendonly=true);
INSERT INTO jan12 SELECT * FROM sales_1_prt_1 ;
ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2012-01-01') WITH TABLE jan12;

Greenplum数据库服务器配置参数 gp_enable_exchange_default_partition控制 EXCHANGE DEFAULT PARTITION子句的可用性。该参数的默认值是 off，表示该子句不可用，如果在ALTER TABLE命令中指定了该子句，Greenplum数据库会返回一个错误。

分裂一个分区

分裂一个分区会把一个分区划分成两个分区。用户可以使用ALTER TABLE命令分裂分区。用户只能在用户的分区层次的最底层分裂分区：只有包含数据的分区能被分裂。用户指定的分裂值会分在后一个分区中。

例如，把一个月度分区分裂成两个，第一个分区包含日期January 1-15而第二个分区包含日期January 16-31：

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

如果用户的分区设计有一个默认分区，用户必须分裂该默认分区来增加分区。

在使用INTO子句时，指定当前的默认分区为第二个分区名。例如，要分裂一个默认的范围分区来为January 2017增加一个新的月度分区：

ALTER TABLE sales SPLIT DEFAULT PARTITION 
START ('2017-01-01') INCLUSIVE 
END ('2017-02-01') EXCLUSIVE 
INTO (PARTITION jan17, default partition);

修改一个子分区模板

使用ALTER TABLE SET 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 '2014-01-01') INCLUSIVE
    END (date '2014-04-01') EXCLUSIVE
    EVERY (INTERVAL '1 month') );

这个ALTER TABLE命令修改子分区模板。

ALTER TABLE sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), 
  SUBPARTITION asia VALUES ('asia'), 
  SUBPARTITION europe VALUES ('europe'),
  SUBPARTITION africa VALUES ('africa'), 
  DEFAULT SUBPARTITION regions );

当用户为表sales增加一个日期范围分区时，它包括非洲的新地区列表子分区。例如，下面的命令创建子分区 usa、asia、europe、 africa以及一个名为other的默认分区：

ALTER TABLE sales ADD PARTITION "4"
  START ('2014-04-01') INCLUSIVE 
  END ('2014-05-01') EXCLUSIVE ;

要移除一个子分区模板，使用带有空圆括号的SET SUBPARTITION TEMPLATE。例如，要清除sales表的子分区模板：

ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

用一个外部表交换一个叶子子分区

用户可以用一个可读的外部表交换一个分区表中的一个叶子子分区。外部表数据可以位于一个主机文件系统、一个NFS挂载或者一个Hadoop文件系统（HDFS）。

例如，如果用户有一个分区表，它按月划分并且对该表的大部分查询值访问较新的数据，用户可以把较旧的、较少访问的数据拷贝到外部表并且把较旧的分区与这些外部表交换。对于其访问较新数据的查询，用户可以创建使用分区排除的查询来防止扫描较旧的、不需要的分区。

用一个外部表交换一个叶子子分区在这些情况下不被支持：
1.分区表用SUBPARTITION子句创建或者如果一个分区有一个子分区。
2.分区表含有一个带检查约束或者NOT NULL约束的列。
关于交换和修改一个叶子子分区的信息，请见 Greenplum数据库命令参考中的ALTER TABLE命令。

具体关于用一个外部表交换一个叶子子分区的例子参考：
https://gp-docs-cn.github.io/docs/admin_guide/ddl/ddl-partition.html#top

参考：
https://gp-docs-cn.github.io/docs/admin_guide/ddl/ddl-partition.html#top

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：drools整合springboot springboot整合oss

下一篇：python模拟socket客户端 python中socket模块

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯