【GreenPlum】记录GreenPlum的分区表

最新推荐文章于 2024-09-23 10:35:55 发布

lsr40

最新推荐文章于 2024-09-23 10:35:55 发布

阅读量5.4k

点赞数 1

文章标签： greenplum

本文链接：https://blog.csdn.net/lsr40/article/details/90642873

版权

继上一篇GreenPlum的文章之后，过了挺长的一段时间（快1年了）

上一篇文章其实有点像杂记，有点散，没什么主题，都是一些测试和工作中的实际运用的记录

后面打算，稍微整理下第一篇文章，把一些基础知识和性能优化剥离开来，专门写一篇（工作中用到的）性能优化

GreenPlum系列：

（一）关于GreenPlum的一些整理：https://blog.csdn.net/lsr40/article/details/81118242

（二）【GreenPlum】记录GreenPlum的分区表：https://blog.csdn.net/lsr40/article/details/90642873

这篇文章来说说两个东西，一个是分区表，一个是外部表

分区表：

分区其实是很多数据库的一种数据放置的优化。

就好像你将一个张桌子上的文件按类别堆叠起来一样，分区就是将数据按照某个字段的值，放到不同的子表里面（或者放到不同的路径下），这样你要查询的时候，就可以加快速度，比如分区按照月份分割，那要查12月份数据的时候，只要到12月份的那个子表（或者路径）上去查询就可以，并不需要全表的扫描，这在大数据领域是一个非常好的优化方式。

GreenPlum的分区，其实就是自动创建相关联的字表！

1、范围分区（range）

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

一般情况下，我们会按照月份来做一个分区，但是还是要根据自己每个分区中的数据量来决定！（我们的集群）大概五千万以内的数据量，查询起来是比较快的，如果大于这个值，上到了亿级别，查询就很影响性能了。所以每个分区的数据要在千万级别。

使用方式：

用户可以通过给出一个START值、一个END值以及一个定义分区增量值的子句让Greenplum数据库自动产生分区。

默认情况下，START值总是被包括在内而END值总是被排除在外。

例如（结果如图）：

--创建月分区表，date_id格式为201901,201902
CREATE TABLE "数据库"."table_test" (
  "user_id" VARCHAR(52),
	"key" VARCHAR(52),
  "date_id" int4,
  "gmt_modified" timestamptz(6) DEFAULT now()
)WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=rle_type)
DISTRIBUTED by(user_id)
partition by range (date_id) 
(
    partition "2019_" start (201901) end (201913) every (1),
    default partition def
)

--如下是运行成功的返回信息
> NOTICE:  CREATE TABLE will create partition "table_test_1_prt_def" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__1" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__2" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__3" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__4" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__5" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__6" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__7" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__8" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__9" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__10" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__11" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__12" for table "table_test"
  

--创建月分区表，date_id格式为20190101--20190131会放到同一个分区里，以此类推
--注意我的end写的是20191299，
--如果写的是20191231，那么20191231的数据就会被放到def的分区里面

CREATE TABLE "数据库"."table_test" (
  "user_id" VARCHAR(52),
	"key" VARCHAR(52),
  "date_id" int4,
  "gmt_modified" timestamptz(6) DEFAULT now()
)WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=rle_type)
DISTRIBUTED by(user_id)
partition by range (date_id) 
(
    partition "2019_" start (20190101) end (20191299) every (100),
    default partition def
)

也可以将 every(1) 改成every(2) 来实现将2个月份放入同一个分区

2、列表分区（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','D'), 
  PARTITION boys VALUES ('M'), 
  DEFAULT PARTITION other );

3、对分区的增删（一般不会修改分区，但是其实好像是有split的操作的，大家自行百度）

-1、split我百度了下，好像用法如下：

如下这段sql皆为引用，具体我还未测试，因为这种操作我们好像基本不用

原文地址：https://blog.51cto.com/13126942/2053712

create table test_partition_list
(
id int,
name varchar(64),
fdate varchar(10)
)
distributed by (id)
partition by list (fdate)
(
partition p1 values ('2017-01-01', '2017-01-02'),
partition p2 values ('2017-01-03'),
default partition pd
);

将分区p2 在 '2017-02-20' 左右切分成两块
alter table test_partition_range split partition p2 at ('2017-02-20') into (partition p2, partition p3);

切割默认分区：
alter table test_partition_range split default partition start ('2017-03-01') end ('2017-03-31') into (partition p4, default partition);

-2、增加分区

如果存在default partition，则不能add分区，否则会报错

ALTER TABLE "rank" ADD PARTITION aa VALUES('B')
> ERROR:  cannot add LIST partition "aa" to relation "rank" with DEFAULT partition "other"
  HINT:  need to SPLIT partition "default"

只能split default partition

比如2020年了，我想要增加2020年的月份的分区


alter table 表名 split default partition start (202001) end (202002) into (partition "2020_1", default partition);

-3、删除分区

alter table 表名 DROP partition 分区名称;

-4、清空分区数据

清空分区表数据，相当于删除分区，然后再新建一个

alter table 表名 truncate partition 分区名称;

4、多级分区

-1、创建

例子：在date的基础上，再多做一级分区是归属地（region），并且设置了开始1月，结束4月，every跨度为1个月


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') );